Excel運用VBA抓取Yahoo Finance APIs股票資料

Yahoo Finance APIs提供了多樣的應用程式接口，讓使用者能夠獲取Yahoo Finance的資料。這篇文章要介紹的，是多數人會用到的股票資料。實作的例子來自於http://www.gummy-stuff.org/Yahoo-data.htm，我只是將內容稍微解釋，並且換成台灣股票的例子。

熟悉Excel的人會運用「匯入外部資料」的方法，直接抓取Yahoo奇摩的股票頁面，然後再從裡頭選取股價、漲跌資料的表格匯入，再做一個總覽的表格，將需要的內容彙整。這個方法操作簡單，不過每一支股票都得連結一次；數量一多，不但連線的時間變成，維護總覽表格也頗麻煩的。在這裡，我們就多做一些工作，寫一點點VBA的程式，讓後續變得比較簡單。

基本的流程包括下面兩項，一個是建立Yahoo Finance APIs所需的參數，第二個步驟則是將獲得的CSV資料存在活頁簿裡頭。至於後面的排序，或是其他的應用，就留給你們自己研究。

首先讓來看看Yahoo Finance APIs。我們並不會用到所有的API，只會應用到其中的CSV API裡頭的Quotes。官方的說明是Downloading quotes of stocks, indices or exchange rates，因為這個例子並不需要歷史資料，只要最後交易日的股價而已。

CSV（Comma-separated values）傳回的資料是用逗號分隔，文字內容由雙引號夾住，一行代表一筆資料的格式。一個實際CSV API的例子，輸入下列的網址後，結果就如同下面那三行文字所表示的。

http://finance.yahoo.com/d/quotes.csv?s=2201.TW+2498.TW+9921.TW&f=snd1l1

"2201.TW","YULON MOTOR TWD10","2/26/2013",53.80
"2498.TW","HTC CORPORATION T","2/26/2013",276.50
"9921.TW","GIANT MFG CO TWD1","2/26/2013",162.00

這便是Yahoo Finance實際應用所傳回的CSV資料。該例子中，查詢了「裕隆2201」、「宏達電2498」、以及「巨大9921」，並且顯示股票代碼、名稱、最後交易日期、以及股價四個欄位。把網址拆成三個部分，會比較容易操作。

起始

http://finance.yahoo.com/d/quotes.csv?s=

股票代碼

這裡的股票代碼是Yahoo的股票代碼，而不是台股新聞中常見的四位數字。然而，台股的數字代碼對應到Yahoo代碼，只要加上.TW（上市）或.TWO（上櫃）即可。上面的例子中，三家公司都是上市公司，所以在四位數字的台股代碼後加上.TW即可。最後，運用「+」連結不同的股票代碼。
http://finance.yahoo.com/d/quotes.csv?s=2201.TW+2498.TW+9921.TW

欄位屬性

完整的屬性代碼請參考官方的屬性表。常用的賣出（a）、買入（b）、最後交易日期（d1）、最後交易股價（l1）、漲跌（c1）、代碼（s）、股票名稱（n）幫你們列出來，其他的稍微對照試一下也就知道了。這部分寫在網址的最後，在股票代碼後加上&f=後，再加上欄位屬性。
http://finance.yahoo.com/d/quotes.csv?s=2201.TW+2498.TW+9921.TW&f=snd1l1

了解Yahoo Finance API的構成後，要開始用懶人的方法，把上頭那些步驟變得更簡單。但為了要更簡單，請各位直接去下載原始範例的Excel檔案（感謝M. Kishinevsky和M. Higgs）。

下載後，遵從畫面上四個步驟的指示即可獲得資料：

鍵入Yahoo Tags，也就是上面提到的欄位屬性。
輸入欄位名稱，依據上述的欄位屬性，給定自己看得懂的名稱。
輸入Yahoo代碼，說明就如同上頭的股票代碼。
按下Download Data的按鈕下載資料。

如果你只要下載多筆股價相關資料的話，你已經做完了。想要更新，只要再按4: Download Data那個按鈕，如果網路沒出錯的話，內容就會自動更新了。但如果你想要進行細部的調整，重頭戲現在才開始。

首先，找到「巨集」（Macro），選取GetData後按下編輯（Edit）。

跳出的視窗中，從Sub GetData()一直到End Sub中間的這一大塊，就是按下4: Download Data所執行的VBA程式。我們一個一個部分來看。

'宣告變數

    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
    Dim i As Integer

'告訴Excel不要每更新一格就重新計算
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

'將現在的工作表設為資料表

    Set DataSheet = ActiveSheet

'從第七列開始，只要第一欄（A欄）內容不是空的，就把代碼加到Yahoo Finance API的網址中
    Range("C7").CurrentRegion.ClearContents
    i = 7
    qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
    i = i + 1
    While Cells(i, 1) <> ""
        qurl = qurl + "+" + Cells(i, 1)
        i = i + 1
    Wend
    qurl = qurl + "&f=" + Range("C2")

'將網址放到C1的欄位中
    Range("c1") = qurl

'抓取資料
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With

'將CSV的格式塞到Excel的表格當中
            Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False

'讓Excel重新活回來，讓資料能夠顯示
'turn calculation back on
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
'    Range("C7:H2000").Select
'    Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
'        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Columns("C:C").ColumnWidth = 12
    Columns("J:J").ColumnWidth = 25.43
    Range("h2").Select

如果要更改的話，大部份只涉及C7那個位置會改到其他地方，或是讓網址不要顯示在儲存格裡面，讓欄位屬性變成固定的。改完之後，你的Excel就能好好下載Yahoo上的股票資料了。唯一的缺點，是沒有辦法顯示中文的股票名稱。

我私底下用了台灣證券交易所的上市上櫃公司名稱表，再用一個VLOOKUP函數過濾代碼和中文資料，拿到中文的名稱。不過，這些就不寫在這裡了。

影。像。生。活

搜尋此網誌