Excel：用yahoo api抓股價Excel巨集

之前有用PTT板上大大寫的excel巨集，不過他用的方式是去抓網頁。

缺點是網頁有修改的話版位就走樣。而且如果要讀取的股票數量多的話，速度會很慢。
（那個巨集板上大大也沒有要繼續維護了...可惜）

昨天研究了一下，最後找到了api的這個巨集方法。參考了：
http://kuanming-style.blogspot.tw/2013/02/excelvbayahoo-finance-apis.html
和巨集作者M.Kishinevsky and M. Higgs的檔案：
http://www.pythonclub.org/project/yahoo-data-api


yahoo提供的api應該比較不會有甚麼異動才是，而且抓資料速度快超多。

我改了一個平常在用的Excel範本，提供各位參考：

https://sites.google.com/site/kslman/file/Stock%2Byahoo_sample.xlsm?attredirects=0&d=1

md5:8d17c8b6af281aa7bea464c39ef5acc0


使用說明：

1.打開後記得啟用巨集

2.「即時」分頁中的按鈕「Download Data」點下去，就會把A欄有Symbols的股票更新。

3.「即時」分頁中的「C2」裡的值「l1c1vhgd1rm4n」可以自定義。右邊Yahoo Tag那張圖裡面有說明填入甚麼樣的代碼會回饋甚麼值。不過表頭不會跟著更新，如果有興趣DIY修改的，自己要搞清楚是動到哪一欄。

4.如果要新增要查詢的股票，在「參照」分頁中自行在A、B、C增加編號、名稱和類別，D和E是公式，只要往下拉就可以。然後把得到的Yahoo Symbols貼到「即時」分頁A欄。

5.分頁「關注」和「對帳單sample」就只是兩個範本而已。提供參考如果抓api資訊後，Excel後續可以幫助你做甚麼事情的範本。


如果對巨集有甚麼疑問的話，可以自己打開來看。

內容跟原作者M.Kishinevsky and M. Higgs的內容是一樣的，我只是加上中文註解。

巨集如下。

Sub GetData()

'宣告變數

    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


End Sub

PS：
原作者網誌中也有附上完整的api代碼說明
http://www.pythonclub.org/project/yahoo-data-api

不過有些代碼就算抓出來，並對照它的英文敘述我也還是看不懂是甚麼。

如知道代碼意思且有興趣幫忙解釋的大大，可在下面推文，我有空會持續完善這張表。


目前我已知代碼解釋如下。

d1      Last Trade Date          最後交易日期
l1      Last Trade (Price Only)  成交價
c1      Change                   漲跌（值）
c       Change & Percent Change  漲跌值與百分比
v       Volume                   成交量（這裡顯示股數非張數）
h       Day's High               當天最高價
g       Day's Low                當天最低價
r       P/E Ratio                本益比
n       Name                     股票英文名稱


--
上文同步有貼在 PTT Stock 板。

其他參考資訊：
http://lovecoding.logdown.com/posts/257928-use-yahoo-api-to-obtain-historical-stock-price-data