2013年2月26日 星期二

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)。


下載後,遵從畫面上四個步驟的指示即可獲得資料:
  1. 鍵入Yahoo Tags,也就是上面提到的欄位屬性。
  2. 輸入欄位名稱,依據上述的欄位屬性,給定自己看得懂的名稱。
  3. 輸入Yahoo代碼,說明就如同上頭的股票代碼。
  4. 按下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函數過濾代碼和中文資料,拿到中文的名稱。不過,這些就不寫在這裡了。

沒有留言 :

張貼留言

Related Posts Plugin for WordPress, Blogger...