OpenOffice Calc BASIC Macro位址、名稱轉換

在OOo Calc中寫OOo BASIC時，會遇到個問題是，
要取得Cell時，常使用getCellByPosition() Method，例如：
oCell = oSheet.getCellByPosition(0,0)
oCell.String = now 'This function returns the current date and time
這樣可以取得A1這個Cell，並修改它的內容。

但getCellByPosition() Method使用的是0 Base，且X、Y都是數值，
可是在Calc當中，
我們一般操作都使用A1這樣的字母+數值，且是1 Base，
為了解決這個麻煩的小問題，我寫了個簡單的轉換函式，如下：
Function CellPositionToName(X As Integer, Y As Integer) As String
Dim LETTER_ARRAY As Variant
LETTER_ARRAY = Array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE","BF","BG","BH","BI","BJ","BK","BL","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV","BW","BX","BY","BZ","CA","CB","CC","CD","CE","CF","CG","CH","CI","CJ","CK","CL","CM","CN","CO","CP","CQ","CR","CS","CT","CU","CV","CW","CX","CY","CZ","DA","DB","DC","DD","DE","DF","DG","DH","DI","DJ","DK","DL","DM","DN","DO","DP","DQ","DR","DS","DT","DU","DV","DW","DX","DY","DZ","EA","EB","EC","ED","EE","EF","EG","EH","EI","EJ","EK","EL","EM","EN","EO","EP","EQ","ER","ES","ET","EU","EV","EW","EX","EY","EZ","FA","FB","FC","FD","FE","FF","FG","FH","FI","FJ","FK","FL","FM","FN","FO","FP","FQ","FR","FS","FT","FU","FV","FW","FX","FY","FZ","GA","GB","GC","GD","GE","GF","GG","GH","GI","GJ","GK","GL","GM","GN","GO","GP","GQ","GR","GS","GT","GU","GV","GW","GX","GY","GZ","HA","HB","HC","HD","HE","HF","HG","HH","HI","HJ","HK","HL","HM","HN","HO","HP","HQ","HR","HS","HT","HU","HV","HW","HX","HY","HZ","IA","IB","IC","ID","IE","IF","IG","IH","II","IJ","IK","IL","IM","IN","IO","IP","IQ","IR","IS","IT","IU","IV","IW","IX","IY","IZ","JA","JB","JC","JD","JE","JF","JG","JH","JI","JJ","JK","JL","JM","JN","JO","JP","JQ","JR","JS","JT","JU","JV","JW","JX","JY","JZ","KA","KB","KC","KD","KE","KF","KG","KH","KI","KJ","KK","KL","KM","KN","KO","KP","KQ","KR","KS","KT","KU","KV","KW","KX","KY","KZ","LA","LB","LC","LD","LE","LF","LG","LH","LI","LJ","LK","LL","LM","LN","LO","LP","LQ","LR","LS","LT","LU","LV","LW","LX","LY","LZ","MA","MB","MC","MD","ME","MF","MG","MH","MI","MJ","MK","ML","MM","MN","MO","MP","MQ","MR","MS","MT","MU","MV","MW","MX","MY","MZ","NA","NB","NC","ND","NE","NF","NG","NH","NI","NJ","NK","NL","NM","NN","NO","NP","NQ","NR","NS","NT","NU","NV","NW","NX","NY","NZ","OA","OB","OC","OD","OE","OF","OG","OH","OI","OJ","OK","OL","OM","ON","OO","OP","OQ","OR","OS","OT","OU","OV","OW","OX","OY","OZ","PA","PB","PC","PD","PE","PF","PG","PH","PI","PJ","PK","PL","PM","PN","PO","PP","PQ","PR","PS","PT","PU","PV","PW","PX","PY","PZ","QA","QB","QC","QD","QE","QF","QG","QH","QI","QJ","QK","QL","QM","QN","QO","QP","QQ","QR","QS","QT","QU","QV","QW","QX","QY","QZ","RA","RB","RC","RD","RE","RF","RG","RH","RI","RJ","RK","RL","RM","RN","RO","RP","RQ","RR","RS","RT","RU","RV","RW","RX","RY","RZ","SA","SB","SC","SD","SE","SF","SG","SH","SI","SJ","SK","SL","SM","SN","SO","SP","SQ","SR","SS","ST","SU","SV","SW","SX","SY","SZ","TA","TB","TC","TD","TE","TF","TG","TH","TI","TJ","TK","TL","TM","TN","TO","TP","TQ","TR","TS","TT","TU","TV","TW","TX","TY","TZ","UA","UB","UC","UD","UE","UF","UG","UH","UI","UJ","UK","UL","UM","UN","UO","UP","UQ","UR","US","UT","UU","UV","UW","UX","UY","UZ","VA","VB","VC","VD","VE","VF","VG","VH","VI","VJ","VK","VL","VM","VN","VO","VP","VQ","VR","VS","VT","VU","VV","VW","VX","VY","VZ","WA","WB","WC","WD","WE","WF","WG","WH","WI","WJ","WK","WL","WM","WN","WO","WP","WQ","WR","WS","WT","WU","WV","WW","WX","WY","WZ","XA","XB","XC","XD","XE","XF","XG","XH","XI","XJ","XK","XL","XM","XN","XO","XP","XQ","XR","XS","XT","XU","XV","XW","XX","XY","XZ","YA","YB","YC","YD","YE","YF","YG","YH","YI","YJ","YK","YL","YM","YN","YO","YP","YQ","YR","YS","YT","YU","YV","YW","YX","YY","YZ","ZA","ZB","ZC","ZD","ZE","ZF","ZG","ZH","ZI","ZJ","ZK","ZL","ZM","ZN","ZO","ZP","ZQ","ZR","ZS","ZT","ZU","ZV","ZW","ZX","ZY","ZZ","AAA","AAB","AAC","AAD","AAE","AAF","AAG","AAH","AAI","AAJ","AAK","AAL","AAM","AAN","AAO","AAP","AAQ","AAR","AAS","AAT","AAU","AAV","AAW","AAX","AAY","AAZ","ABA","ABB","ABC","ABD","ABE","ABF","ABG","ABH","ABI","ABJ","ABK","ABL","ABM","ABN","ABO","ABP","ABQ","ABR","ABS","ABT","ABU","ABV","ABW","ABX","ABY","ABZ","ACA","ACB","ACC","ACD","ACE","ACF","ACG","ACH","ACI","ACJ","ACK","ACL","ACM","ACN","ACO","ACP","ACQ","ACR","ACS","ACT","ACU","ACV","ACW","ACX","ACY","ACZ","ADA","ADB","ADC","ADD","ADE","ADF","ADG","ADH","ADI","ADJ","ADK","ADL","ADM","ADN","ADO","ADP","ADQ","ADR","ADS","ADT","ADU","ADV","ADW","ADX","ADY","ADZ","AEA","AEB","AEC","AED","AEE","AEF","AEG","AEH","AEI","AEJ","AEK","AEL","AEM","AEN","AEO","AEP","AEQ","AER","AES","AET","AEU","AEV","AEW","AEX","AEY","AEZ","AFA","AFB","AFC","AFD","AFE","AFF","AFG","AFH","AFI","AFJ","AFK","AFL","AFM","AFN","AFO","AFP","AFQ","AFR","AFS","AFT","AFU","AFV","AFW","AFX","AFY","AFZ","AGA","AGB","AGC","AGD","AGE","AGF","AGG","AGH","AGI","AGJ","AGK","AGL","AGM","AGN","AGO","AGP","AGQ","AGR","AGS","AGT","AGU","AGV","AGW","AGX","AGY","AGZ","AHA","AHB","AHC","AHD","AHE","AHF","AHG","AHH","AHI","AHJ","AHK","AHL","AHM","AHN","AHO","AHP","AHQ","AHR","AHS","AHT","AHU","AHV","AHW","AHX","AHY","AHZ","AIA","AIB","AIC","AID","AIE","AIF","AIG","AIH","AII","AIJ","AIK","AIL","AIM","AIN","AIO","AIP","AIQ","AIR","AIS","AIT","AIU","AIV","AIW","AIX","AIY","AIZ","AJA","AJB","AJC","AJD","AJE","AJF","AJG","AJH","AJI","AJJ","AJK","AJL","AJM","AJN","AJO","AJP","AJQ","AJR","AJS","AJT","AJU","AJV","AJW","AJX","AJY","AJZ","AKA","AKB","AKC","AKD","AKE","AKF","AKG","AKH","AKI","AKJ","AKK","AKL","AKM","AKN","AKO","AKP","AKQ","AKR","AKS","AKT","AKU","AKV","AKW","AKX","AKY","AKZ","ALA","ALB","ALC","ALD","ALE","ALF","ALG","ALH","ALI","ALJ","ALK","ALL","ALM","ALN","ALO","ALP","ALQ","ALR","ALS","ALT","ALU","ALV","ALW","ALX","ALY","ALZ","AMA","AMB","AMC","AMD","AME","AMF","AMG","AMH","AMI","AMJ")

If X < 1024 Then CellPositionToName = LETTER_ARRAY(X) + (Y + 1) Else MsgBox "Out Of Range" End If End Function
傳入X、Y 2個數值，回傳A3這樣的名稱，
因為X最大是AMJ(0~1023)，因為數值並不大，
我直接定義成變數查表即可。

注意：已經自動轉換Base 0為Base 1，傳入時不用刻意加1。

007先生的交易歷程 的 讀後回文

本來在找匯率表之類的，就找到這個有趣的Blog，
007先生看來年紀跟我差不多，不是同年就是小個1~2歲，
整個Blog看下來後，我認為007先生實際的以金錢去做了個實驗，
實驗證明了，程式交易並不是神，
僅僅依靠程式交易搭配特定演算法就要套利，
結果就是直接被市場KO，
我後來想了想，覺得007先生這麼做，並以日記形式寫成Blog，
應該可以作為不錯的反向教材，
而在倒數第二篇文章中，因為007先生的不甘心，
提到「一定是哪裡出了問題」，
因此我把我這段時間的想法寫成回文，
這段回文，算是目前我對交易市場和程式交易的想法。

---

沒做過外匯，只有最近(算前陣子)台幣升值時有換一些美金玩玩。
我是看到「我知道必定是哪裡出了錯...」這句話，才想回應的。

我對程式交易有興趣，但我只打算用程式交易做股市中長期(月為單位、長期持有定存股)，代替人工下單(上班沒時間)。
我目前認為，除非遇到系統風險或異常超跌，否則在交易市場要套利，應該很難和大戶或法人玩。

我目前認為，程式交易會失敗有幾個關鍵因素：
1. 常見的程式交易，都是用EasyLanguage去寫的，你仔細想一下，事實上它能做到的條件很死板，它的條件幾乎都是軟體寫好提供的，感覺有點像是說，給你玩個沒規則的遊戲，但只給你幾個條件去搭配判斷，這聽起來就很受限，只要我知道你能夠使用的條件，我想贏你的機會應該很大

2. 目前沒有任何聖杯，可以保證獲利，沒有任何條件套用在金融市場是百分百正確的，我們透過一連串無法保證的條件去判斷，並期望它要有6成勝率，那可能用亂數感覺還快些

3. 去年發生過一次美股異常交易的事件有上新聞，大家都知道，有人說可能是高頻交易引發的連鎖效應，我查了高頻交易，才知道國外很多大型投資公司用高頻交易套利，它們利用電腦，以「每秒400次成交」的速度，高速的進行買跟賣，利用利差來套利，甚至我認為，能以此掌握最短期的市場變化，在設備、環境、程式、資金大小、消息面都不對等的情況，我不認為我的程式有辦法跟這樣的程式作對

4. 程式是人寫的，換言之，它只能代替人去按照特定模式交易，既然你我都沒找到一個勝率達6成的模式，又怎麼能要求程式能有6成勝率？

最後，我想我可以把最近幾個月的想法說一下，我認為，憑空希望能透過投機的方式來獲利，不如注意系統風險的發生。
你買了這麼多書，相信有提到一點，交易市場會有系統性風險跟非系統性風險，系統性風險不可預期，且影響巨大，像金融風暴、次級房貸...等。
事實上你稍微注意一下會發現，當遇到系統風險時，該投資標的很有機會跌到非常不錯的價格，只要不破產，此時買入並長期持有(預期至少1~2年)，那麼我認為績效應該都不會太差。
你前面常提到，有錢人一開始也是從無到有的，這點我認同，但不完全認同，一些人它可能父母有庇蔭，一開始就有一筆不少的資金，當他剛好遇到某個系統風險時，大膽的投入，運氣不好可能破產，但此時只要沒破產，勝率很高，且因為資金大，獲得的獲利非常可觀，原因是，在交易市場，你玩了這麼久就知道，它的獲利是以%數計算的，他有資金，又在系統風險最低點投入，上來時，那種以%數計算的獲利非常驚人。
最後，我們生在台灣，天生就不平等，你想想，我們辛辛苦苦一個月不過3,4萬，一個月有辦法也不過存2萬，美國人薪資是我們的2~3倍，扣除開銷和稅金，還是比我們多很多，他們的資金水平和我們不是同一個水平，對賠率的容忍度比我們高，這是沒辦法的事，但好消息是，跟對岸或東南亞國家相比，薪資水準、物價水平我們又好一些。

OpenOffice BASIC Macro資料庫操作－2

在上集中，我們完成了現在要使用的資料庫，
接著我們就可以透過OpenOffice BASIC來操作剛剛產生的資料庫。

在OOo BASIC中要操作資料庫，整個過程需要6個動作：

1. 連結DataSource
2. 取得查詢
3. 連結SQL伺服器
4. 從查詢中取得SQL Code，並查詢SQL伺服器
5. 查詢完成，取出查詢結果
6. 關閉SQL伺服器連線

整個程式碼如下：
Sub Main

REM 連結 DataSource
Dim DatabaseContext As Object
Dim DataSource As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("OOoTEST")

REM 取得查詢
Dim QueryDefinitions As Object
QueryDefinitions = DataSource.getQueryDefinitions()
QueryDefinition = QueryDefinitions("ProductQuery")

REM 連結SQL伺服器
Dim InteractionHandler As Object
Dim Connection As Object
InteractionHandler = createUnoService("com.sun.star.task.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)

REM 從查詢中取得SQL Code，並查詢SQL伺服器
Dim Statement As Object
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery(QueryDefinition.Command)

REM 查詢完成，取出查詢結果
Dim DATA_ID As String
Dim DATA_NAME As String
Dim DATA_PRICE As String
If Not IsNull(ResultSet) Then
While ResultSet.next
DATA_ID = ResultSet.getString(1)
DATA_NAME = ResultSet.getString(2)
DATA_PRICE = ResultSet.getString(3)
MsgBox "ID:"+ DATA_ID + " 名稱:" + DATA_NAME + " 價格:" + DATA_PRICE
Wend
End If

REM 關閉SQL伺服器連線
Connection.Close()
End Sub

接著我們分段來解說每個部份：

1. 連結 DataSource

在OpenOffice中，所有要被Calc使用的資料庫都需要先被「註冊」，
在建立資料庫時，預設都會自動幫你把它註冊到OpenOffice當中，
回憶一下，如下圖：

當我們將資料庫註冊到OpenOffice後，
我們就可以將它當成DataSource進行連結，
這裡要注意到，註冊的名稱可以和DataBase名稱不同，
要以「註冊名稱」為「DataSource名稱」，
不過預設，兩者名稱相同。

在這裡，我們指定DataSource為剛剛建立好的資料庫OOoTEST，
分段程式碼如下：
Dim DatabaseContext As Object
Dim DataSource As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("OOoTEST")
我們先定義DatabaseContext和DataSource這兩個變數，
接著建立DatabaseContext服務，
透過DatabaseContext服務來取得OOoTEST這個DataSource。

延伸：
這裡有個延伸的程式碼可以參考，如下：
Dim DatabaseContext As Object
Dim DataSource As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
Names = DatabaseContext.getElementNames()
For I = 0 To UBound(Names())
MsgBox Names(I)
Next I
我們可以透過這段程式碼，將所有註冊的資料庫全部列出。

2. 取得查詢

取得DataSource後，還記得剛剛我們在OOo Base裡面，
有建立好一個叫做ProductQuery的查詢項目。
接著，我們取得這個查詢，分段程式碼如下：
Dim QueryDefinitions As Object
QueryDefinitions = DataSource.getQueryDefinitions()
QueryDefinition = QueryDefinitions("ProductQuery")
定義QueryDefinitions這個變數，
透過DataSource的getQueryDefinitions() Method，
我們取得全部的Query，
並從裡面取出我們要使用的Query - ProductQuery。

延伸：
如果我們不清楚有哪些Query，希望透過Macro動態查詢，如下：
Dim QueryDefinitions As Object
QueryDefinitions = DataSource.getQueryDefinitions()
REM 顯示所有查詢
For I = 0 To QueryDefinitions.Count() - 1
QueryDefinition = QueryDefinitions(I)
MsgBox QueryDefinition.Name
Next I
我們可以用這段程式碼，將所有已經儲存的Query全部列出。

如果我們想要用Macro建立新的Query，我們可以使用下面的程式碼：
Dim QueryDefinitions As Object
QueryDefinitions = DataSource.getQueryDefinitions()
QueryDefinition = createUnoService("com.sun.star.sdb.QueryDefinition")
QueryDefinition.Command = "SELECT Name FROM ProductTable"
QueryDefinitions.insertByName("OurNewQuery", QueryDefinition)
透過這段程式碼，我們可以建立一個新的Query叫做OurNewQuery，
這個Query的SQL Code為「SELECT Name FROM ProductTable」。

3. 連結SQL伺服器

再來我們要正式連結SQL伺服器，分段程式碼如下：
Dim InteractionHandler As Object
Dim Connection As Object
InteractionHandler = createUnoService("com.sun.star.task.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
這裡我們要建立InteractionHandler服務，
接著使用DataSource的ConnectWithCompletion()來連結SQL伺服器。

4. 從查詢中取得SQL Code，並查詢SQL伺服器

SQL伺服器連結完成後，我們要查詢SQL伺服器，如下：
Dim Statement As Object
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery(QueryDefinition.Command)
我們先定義Statement變數，
接著透過Statement的executeQuery()查詢SQL伺服器。

這邊注意到，事實上executeQuery()使用的是SQL Code，
因此我們要透過QueryDefinition.Command，
將剛剛我們取得的Query - ProductQuery內的SQL Code取出。

延伸：

因為executeQuery()使用的是SQL Code，
因此我們可以不透過Query，直接用SQL Code進行資料庫的查詢，
程式碼如下：
Dim Statement As Object
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("SELECT Name FROM ProductTable")

5. 查詢完成，取出查詢結果

如果資料庫是INSERT或者UPDATE的操作，不需要取得資料，
那麼這個步驟是完全不需要的，這個步驟只有在查詢資料，
需要從資料庫中取得資料才需要。

分段程式碼如下：
Dim DATA_ID As String
Dim DATA_NAME As String
Dim DATA_PRICE As String
If Not IsNull(ResultSet) Then
While ResultSet.next
DATA_ID = ResultSet.getString(1)
DATA_NAME = ResultSet.getString(2)
DATA_PRICE = ResultSet.getString(3)
MsgBox "ID:"+ DATA_ID + " 名稱:" + DATA_NAME + " 價格:" + DATA_PRICE
Wend
End If
這裡很簡單，在前面步驟4的最後，
我們透過executeQuery()查詢資料庫後，
結果會儲存在變數ResultSet當中，
這裡我們先檢查ResultSet，確定它是有東西的，
接著使用ResultSet.next取出每條結果，
再來我們使用ResultSet.getString(1)取出第1個欄位資料，
使用ResultSet.getString(2)取出第2個欄位資料，
再利用MsgBox將資料用訊息視窗顯示出來。

6. 關閉SQL伺服器連線

最後，我們要有好習慣，將SQL伺服器斷線，分段程式碼如下：
Connection.Close()
到這裡，整個資料庫的查詢全部完成。

這裡延續前面的慣例，可以出個作業，
這裡我們完整的取得了資料庫的資料，
那要如何填入OpenOffice的Calc呢？

OpenOffice BASIC Macro資料庫操作－1(資料庫建立)

要操作資料，或者進行歷史資料處理，資料庫的操作都是不可避免的，
在前面入門課題結束了，我們就開始直奔主題，而第一個po的心得，
就是頗有難度，但在資料處理上很重要的，資料庫操作，這裡是操作讀取，
但寫過資料庫程式的都知道，讀取比寫入麻煩，會讀取，要會寫入都不難。
因為篇幅可能會比較長，我分上下兩集介紹，上集是建立資料庫。

開始工作前，首先需要個資料庫，在這裡我們使用OpenOffice Base的資料庫來操作。

1.建立資料庫，並存檔為OOoTEST.odb放置於桌面

2. 建立Table、輸入資料

3. 建立查詢，查詢ProductTable內全部項目

如果偏好直接用SQL Code，也可以用下列SQL Code直接建立：
SELECT "ID", "Name", "Price" FROM "ProductTable";

4. 至此，資料庫和查詢建立完成，可以開始透過Macro操作了