[程式交易DIY] 第一章：用 Excel/VBA 爬取交易所 API 資料
本文作者：Woodrow K. Deng
原文出處
網路爬蟲、程式交易，不管你有沒有聽過，這些都是早已擴散到業餘者生活圈多年的老伎倆。在這個年頭，交易界更前沿的機器人/人工智慧交易才是值得討論的技術議題。然而，即便在專業者眼中已經如呼吸一般的簡單小工具，對一些人來說還是有些進入門檻(例如我)。
說實在話，時下許多投資平台的軟體、APP，已經能滿足大部分人的操作需求。加上因為配合使用者習慣、精簡軟體介面跟避險一些法規責任，市面上具有客製化的自動追蹤、運算甚至運行交易決策功能的投資軟體仍然不普及，大部分仍要收取可觀的費用才能購得。也因此，「全民胖手指」[1]的時代仍然沒有臨到人間。對於散戶來說，自動交易的妄想也只在付出大額資金購買小程式(還不保證勝利)與獨自奮戰一竅不通的程式碼來實現。
這種對專業者簡單到不屑一顧、對外行人卻不得其門而入的技術，不消說，肯定是非常適合我這半吊子來介紹。我預計分三個部份來簡單分享：以Excel/VBA 爬取資料(第一章)、資料的應用與有目的地擷取(第二章)、使用Excel/VBA 進行掛單動作(第三章)。
由於個人最近開發 API[2] 小客戶端工具的目的就是為了使用數位貨幣交易所Cobinhood[3] 來自動交易，所以本系列自然也都用 Cobinhood 的接口環境來示範。不過別擔心，這玩意兒就是這樣，一訣通萬萬通，其他網站的資料基本上只要有使用類似傳輸協定皆大同小異。有什麼問題也歡迎提出討論！
這一章大致會分以下標題討論：
(一) 為什麼使用 Excel/VBA？
(二) 簡介架構與目標設定
(三) 引用資料解析工具
(四) 安排工作表/表格
(五) 取用線上資料與解析
(六) 簡單安排巨集啟動按鈕
(一) 為什麼使用 Excel/VBA？
(這段純粹是選擇工具的心路歷程跟建議，趕時間的話請直接前進下段。)
想我某次在討論 API 設計的群組(羞恥地)發了我的程式碼做討論，立刻有人發聲:”Oh god, is that visual basic?”, ”looks like msxml sucks(big surprise)”。由於先天帶了個Basic又基礎好上手，VB/VBA 一直是程式設計師鄙視鍊(Excel 的 VBA 現在還算是辦公利器嗎？)的最底層，我們請Scratch先不要說話。可是，附著在微軟辦公軟體霸權 Office 上，VBA 對於非專科學生跟上班族的可接近性非常地高，加上 Excel 在資料處理上眾多能力神器、難度小兒的功能內建，因此一般人藉由兩者的結合，不用經過太多學習上的磨難即可完成許多自建功能設計。
在 Excel上使用 VBA 來搭建個人化資料處理應用，除了資料結構安排直覺-工作表、格要擺哪就擺哪比較不用預先設計，實在想不出該怎麼用程式語言做時，掛個”.application”還能偷用 Excel 裡原生的函數集。我相信對門外漢而言，在這種環境學習基礎運作，比引用高手們含辛茹苦創作的 API 指令/函數庫好上許多。啊就不會寫程式了，函式集建再好、效率再高，即便各種防呆、註解、除錯訊息、說明文件都帶上，但不好意思，只知道這函式怎麼用，前後該安排哪些程式碼，還是沒個概念啊！這世上很少有這樣好心耐煩的高手肯免費分解動作送佛送到西還附贈圖形介面教學。用 Excel 的話，哪個環節失敗了，好歹仍能建個表格來手動剪剪貼貼，電腦能做的咱人也行，抬頭挺胸當阿 Q。
然而用這工具還是些侷限的：
1.運作效率較低落，畢竟軟體龐大，沒辦法精簡到只裝備自己需要的功能。2.目前難以擴展到網路、行動應用上，或產出單一執行檔帶著走。
3.官方說明文件爛到令人切腹，自學入門容易但進階困難。(微軟不 EY)
所以如果你有其他擴展應用的打算，Excel/VBA 現階段就不太適合你。
至於可能有些人會問，「為什麼不用 Power Query？」，因篇幅關係我回應在註解[4]。
(二) 架構與目標設定
這整個系列的示範架構目標是：
1.使用 Excel/VBA 經向 COBINHOOD 交易所取得交易報價與交易對資訊。這裡說的資訊主要包含買賣掛單跟交易的最小量資料。其方法為以 RESTful 方式[5]取得 Json 資料[6]並解析成適合在 Excel 上處理的狀態。
2.將該 API 應用設計成使用者可以輕鬆在 Excel 工作表上增刪追蹤交易對而不用修改程式碼的狀態。
3.使用該 API 應用執行原本需要進行帳號登入的查詢與掛/刪單作業。
※本章僅示範擷取交易對資訊。
RESTful 這個關鍵字可能就麻煩大家先記在心上，在查找類似文件比較方便。至於 RESTful 是什麼？簡單來說，你只要知道它不是 Websocket 就好。順便給大家科普一下，Websocket 的定義就是”不是 RESTful”。好，這樣很清楚。就像你買了比特幣以外的數位貨幣，只要知道它不是比特幣不要講錯就好，雖然不知道好像也沒什麼問題。此段請自帶刪除線。(參考連結：什麼是RESTful?)
(三) 準備工具、引用資料解析工具
(三)-1 準備 Excel 環境，啟用程式碼撰寫功能：
預設上 Excel 不會直接亮出這樣的功能讓大家使用，按照圖一到”選項/自訂功能區/”把開發人員打勾，就能看到圖二的頁面標籤出現，點擊”Visual Basic”就能看到圖三的 VBA 程式碼畫面。
這系列文章僅就操作面示範，不會深入介紹各個功能，有興趣的人可以自行Google 到更多學習資源。另外還有一點非常重要的，這些有編輯程式碼的檔案，需要被儲存為”.xlsm”，也就是”啟用巨集的活頁簿”。
如果你往後有遇到無法執行/啟用巨集的狀況，可以參考官方說明文件解決：變更 Excel 中的巨集安全性設定。
(三)-2 準備 VBA 環境-引用會用到的工具
很少人會作任何事情都把工具箱裡的所有東西攤開排好在地上，畢竟不太可能每次都用到所有工具。這裡也是。我們只把會用到的一些工具打開，避免佔用太多電腦資源，微軟預設的引用中一般沒有太多網路相關的或是可以執行個人創作模組的東西。按照圖四在 VBA 巨集編輯區上方點選，確定”設定引用項目”中，紅框的東西都有勾選。他們會按照字母排序，往下拉找就能看到，上兩個是處理網路文件的，下面那個”Runtime”結尾的東西則是為了讓我們上網載下來的東西可以運作的指令集。
(三)-3 下載並且載入 JsonConverter
Json 檔案格式通常帶有樹狀結構，並不是那種直接貼到 Excel 工作表上每次都能直接切割排好的玩意兒。你載到的很可能是像下面這串開頭的大便，對於程式設計師很日常，但普通人可能會一陣暈眩。
Json 內容字串舉例：
“{“success”:true,”result”:{“orderbook”:{“sequence”:0,”bids”:[[“0.00000906”,”2",”19932"],[“0.000009”,”3",”1646.84286812"], ….
應付這組字串資料有很多種方法，不過感謝網路上一位叫 timhall 的善心人士早寫好一組程式工具，幫助我們把這串字很有規律地儲存好同時處理許多例外狀況。所以咱們二話不說，先到這個網頁下載程式碼[7](使用圖文教學：https://codingislove.com/excel-json/)，載完拜謝一下，就按照圖五顯示的方式把模組放上我們的 VBA 編輯畫面。
基本上到這裡為止，如果你有些 VB 基礎，把這個威猛工具載進來時，工作已經完成大半。因為最煩的資料整理程序已被克服，接著你勢如千軍萬馬。
(四) 安排工作表/表格
接著我們可以開始把網路資料載進 Excel 了嗎？先稍待，我們必須安排一下工作表跟表格，建立一些連接，讓程式直接取用 Excel 的資料。這樣萬一網址有變，我們就不用每次都進去修改程式碼，這樣也可以避免重複複製貼上造成不必要的錯誤。
(四)-1 為工作表還有取資料的網址取名稱
Excel/VBA 因為兩者要一起搭配，在程式碼中也會常常呼叫工作表中的表格跟儲存格資料。所以首先我建議把所有網址啊設定什麼的值都放在一個叫”Settings”的工作表方便管理。再來去你想要用 API 抓取資料的網站，看他們有沒有給予API參數。
這裡由於要示範Cobinhood的交易對資料，所以我們到 Cobinhood 的 API 參數文件找尋”Get All Trading Pairs”，並且把它的網址複製下來到 Excel，用名稱管理員命名成”URL_TradingPairs”，代表要查 Trading pairs 的網址都寫在這，以後要改直接改Excel就好。
然後再新增一個工作表”TradingPairs”。由於我們看到 Cobinhood 在這個網址回傳的資料長圖六這樣。最中間那個框框有六行元素，分別是： id, base_currency_id, quote_currency_id, base_min_size, base_max_size, quote_increment. 所以我們就新增了六個同名欄位並且好好命名表格來準備接收資料，如圖八。
在 VBA 中若想引用 Excel 裡定義的名稱或表格清單，只要加上中括號就能呼叫成功喔！這部分詳情請看本文給予的影片(0:45 開始)。
(五)取用線上資料與解析
把網路資料抓進 VBA 處理並不需要太大的工夫，剛才我們勾選的http, XML等工具很快會派上用場。
(五)-1 按照圖六新增一個模組，它會自動命名為”Module1”。名字隨便你怎麼改，反正以後用到別記錯就好。這裡就用 Module1。
(五)-2 將以下程式碼貼上 Module1。
這裡示範取用 Cobinhood 上所有 TradingPairs 資料，單引號後面是註解。
Sub Load_Trading_Pairs()' 載入請求網址
Dim strUrl As String
strUrl = Worksheets("Settings").[URL_TradingPairs]
' 準備接收網路資料之物件
Dim httpsource As Object
Set httpsource = CreateObject("MSXML2.XMLHTTP")
' 準備擷取 JSON 資料之物件
Dim jsonObject As Object
' 對目標網址執行 GET 命令
httpsource.Open "GET", strUrl, False
' 送出命令並接收資料
httpsource.send
Set jsonObject = JsonConverter.ParseJson(httpsource.responseText)
'使用 i 計數以將遞迴資料寫入表格列
Dim i As Integer
i = 1
Worksheets("TradingPairs").[List_TradingPairs].Clear
For Each Item In jsonObject("result")("trading_pairs")
With Worksheets("TradingPairs").[List_TradingPairs]
.Cells(i, 1).Value = Item("id")
.Cells(i, 2).Value = Item("base_currency_id")
.Cells(i, 3).Value = Item("quote_currency_id")
.Cells(i, 4).Value = Item("base_min_size")
.Cells(i, 5).Value = Item("base_max_size")
.Cells(i, 6).Value = Item("quote_increment")
End With
i = i + 1
NextEnd Sub
程式碼解釋：
其實這裡面大部分都只是宣告變數，真正關鍵的只有三行
Set httpsource = CreateObject(“MSXML2.XMLHTTP”)
httpsource.Open “GET”, strUrl, False
httpsource.send
把 httpsource 變成可以接收網頁資料的形態=>對 RESTful 網址執行指令”GET”=>傳送
就這樣，結束，我們拿到我們要的資料了。
接著把資料字串丟到 JsonConverter 再把整理後的資料傳到 jsonObject 中，我們就按照他們的樹狀階層取出各個標籤下的資料存到表格中。(中間如果有資料不要大可跳過沒關係。)
(六) 簡單安排巨集啟動按鈕
應用上我們當然不會每次每個動作都去 VBA 視窗執行，通常放個按鈕在工作表上，可以依照情況執行不同動作的組合。在這裡我安排一個按鈕執行剛才我們寫出來的小程式。
如果對於按鈕的顏色跟顯示的字有其他想法，可以點擊上方”設計模式”，再在按鈕按右鍵點擊內容即可一一修改。當然，如果要執行程式的話，要先把”設計模式”按掉。
以上，就是本章之分享，其他部分留待下篇分曉。
有興趣的話可以先自行摸摸看其他 API 的網址跟指令，這其中仍有不少操作空間，自己先玩過比較不會被我的邏輯順序所影響，也更能有不同的體悟。
其實，台灣市面上不少股票老師跟操作群組的自動交易系統仍然使用Excel/VBA 這樣的工具來製作，其中有些甚至還要收取高額費用。雖然自己從無到有要建到那些收費工具的水準可能有些不符時間效益，但有自己稍微順過流程跟原理，遇到使用上的困難才有機會排除，甚至站在其他人分享的資源上實現自己想執行卻無人協助創建的自動交易策略。
後記：本系列文章之架構皆因應個人需求、按照個人學習緣分所蒐集到的資料所拼湊，僅供作範例參考，遠不及真正科班的教學範本。其後許多程式作業程序安排，皆為求速成而侷限，沒有完整的結構安排、沒有留下足夠的擴展性、沒有留下除錯訊息與方法，有非常大幅度的優化空間。大家可以帶著懷疑的態度分享討論，未來有更多改善的意見也歡迎提出，謝謝！
[1] 註：胖手指與程式交易之關聯僅為本筆者個人意見，因持續自動操作，難免有沒被發現考慮的低機率事件跟程式 bug，可能導致更多錯單事件。但這沒有太多證據，畢竟有言說：系統最大的 bug 就是人類。 胖手指相關參考：” 隨著網路下單、程式交易的風行，倫敦、華爾街股市也偶聞發生重大錯帳事件，…”, 烏龍錯帳 台股「胖手指」事件一籮筐, 2017, 經濟日報.
[2]API 就是應用程式介面（英語：Application Programming Interface，簡稱：API），又稱為應用編程介面，銜接軟體系統不同組成部分的約定。, wiki. 狹義一點來說，在這裡的情境下我們是要跟交易系統的伺服器作溝通，它可能是由任何軟體組成，這裡的 API 就是我們跟交易伺服器取得資料甚至直接溝通交易的介面工具。
[3]Cobinhood 是立足於台灣成立於去年(2017)的零手續費數位資產交易所.[4]Power Query 是微軟為 Excel 發展的應用程式增益集，可以從多種資料來源輕鬆取得資料並且批次建立自訂查詢，是強大的資料分析插件工具。自Excel 2010 開始支援，可以在微軟官網免費下載。不過由於舊版 Excel 不支援，在本系列所應用中對 json 的解析與使用不夠智能、更新速度也遠低於使用 VBA 方法載入且嚴重拖慢 Excel 開啟速度，所以本系列文章並不使用這個工具。
[5]”什麼是 REST 跟 RESTful?”, ihower, 2006. https://ihower.tw/blog/archives/1542
[6]” JSON 資料格式是一種輕量級的資料交換語言，脫胎於 JavaScript。”, wiki.
[7] JSON conversion and parsing for VBA, timhall, https://github.com/VBA-tools/VBA-JSON/releases
[8] Import Json to excel and export excel to Json, RANJITH KUMAR, 2016
