Excel VBA 如何使用Excel 金融工具加XQ DDE快速取得歷史資料

 

檔案下載

腳本

如何使用Excel VBA 加XQ全球贏家DDE做出如影片所展示一樣，快速取得歷史日線資料的功能，影片最後會提供檔案下載的方式，請記得要把影片看到最後哦!

 

HI!歡迎來到Excel 金融分析，手把手教你建立量化交易 系統的頻道，我是豐島(Excel股票分析的站長)

如果你也喜歡這樣的內容，記得訂閱我的頻道，打開旁邊的小鈴鐺，這樣子你就不會錯過任何新影片通知了

 

在前幾天的影片內，介紹了Excel 金融工具系列，如何使用XQ全球贏家的DDE伺服器串接Excel收取盤中即時報價，以及如何使用Excel VBA來快速修改XQ DDE公式，那如果今天我們想要取得歷史的日線資料，以建立量化交易 系統的分析數據，該如何實現呢?

 

第一個方法是開啟XQ全球贏家，在走勢圖上點擊右鍵，輸出到Excel，這樣子就可以取得了，不過這個方法並不方便，每次更換股票時，就要再度重新操作一次。

第二個方法是可以像上方資訊欄的影片一樣，自已設計一個Excel 金融爬蟲，並且建立一個股市 資料庫，以快速取得歷史資料，但是這樣子必須要學習眾多的網路爬蟲及資料庫的相關知識，困難度也不低。

 

不過XQ全球贏家的DDE伺股器也有提供歷史收盤資料的公式，雖然使用上也是有點麻煩，每次取得都要重新輸入一次陣列公式，但我們一樣可以使用Excel VBA來簡化切換股票時的操作。

 

我們先打開瀏覽器，搜尋”XQ DDE”，找到”DDE功能_使用公式輸入-XQ全球贏家”這個網頁，往下拉到「k線資訊」這邊，我們可以看到XQ DDE有提供歷史的開盤價，最高價、最低價、收盤價及成交量的資料，並且他有註明，必須先選取五列六行的空白儲存格來當陣列範圍，並同時按下[Ctrl]+[Shift]+[Enter]輸入陣列，下面這邊有它的公式範例。

 

我們先按[Ctrl]+[C]復制公式，打開Excel，先從[A1]開始輸入日期、開盤、最高、最低、收盤、成交量，再來選取[A2]到[F6]的範圍，把指標移到上方的資料編輯列，[Ctrl]+[V]貼上公式，再同時按[Ctrl]+[Shift]+[Enter]輸入陣列。

這個時候會看到無法開啟XQ全球贏家的錯誤，原因是網頁上的公式是舊的格式，那我們先把錯誤的公式按[Del]刪掉，從新貼上公式，並在公式內XQ的後面，加入”LITE”，重新輸入陣列，這樣子就可以收到歷史資料了。

 

那我們如果想更換標的股票，就必須在股票代號的位置修改新的代號，例如1101，並且重新按一次[Ctrl]+[Shift]+[Enter]輸入陣列。

假如還要修改不同的天期，陣列的範圍還要重新選取，再改天期，並且重新按一次[Ctrl]+[Shift]+[Enter]輸入陣列。

 

這樣子的操作是不是也很麻煩，那接下來我們就用Excel VBA寫出可以取代這些操作的程式吧。

 

先把日期這些標題，移到下面，並在[A1]輸入”股票代號”，[B1]輸入”天期”，在開發人員選項、插入、Button，建立按鈕，打開設計模式，打開VBA，先把Button的顯示改為”取得日k資料”

那我們開始來寫程式

Sub DDE取得日k資料()

'先宣告2個變數

    Dim strCode As String'宣告股票代號的變數為字串

    Dim intDay As Integer'宣告天期的變數為整數

'再來指定變數的值

    strCode = Cells(2, "A").Value'strCode=儲存格[A2]的值

    intDay = Cells(2, "B").Value'intDay=儲存格[B2]的值

'選取陣列範圍

    Range(Cells(5, "A"), Cells(intDay + 4, "F")).Select

'在選取的範圍內輸入陣列公式

    Selection.FormulaArray = "=XQLITE|Kline!'" & strCode & ".TW-Day-" & intDay & "'"

End Sub

 

然後在取得日k資料的Button上點2下

 

Private Sub CommandButton1_Click()

‘呼叫DDE取得日k資料程式

    DDE取得日k資料

End Sub

把設計模式關起來，輸入股票代號及天期，點擊取得日k資料，這樣子就可以一鍵取得資料了，要換股票的話，只要把股票代號修改一下，就可以重新取得新股票的資料，天期也可以做修改，但這邊會有一個問題，就是如果先取得20天的資料，再取10天，那就會出現錯誤訊息，因為剛剛取得20行資料，現在只要10行，必須把資料全部刪掉，才能正常執行。

那這邊我們只要再加一行程式碼就可以解決了，

Private Sub CommandButton1_Click()

‘取得新資料前，先把[A5]~[F5]往下到最後一列的資料清除

    Range([A5], [F5].End(xlDown)).ClearContents

‘呼叫DDE取得日k資料程式

    DDE取得日k資料

End Sub

這樣子的話，任意的修改天期也不會出現錯誤訊息了。

 

以上就是今天的影片內容，你可以在下方留言給我，告訴我今天的影片對你有沒有幫助，影片內所展示的Excel VBA檔案，我會放在Excel股票分析的網站給需要的人下載，網站的網址會放在下方的資訊欄。

 

如果大家想了解，更多關於Excel 金融應用的方法，歡迎訂閱我的頻道，打開旁邊的小鈴鐺，我會每週更新影片，那我們下次見