[心得] 用yahoo api抓股價Excel巨集

[kslman ()]

網誌好讀版：
http://kslman.blogspot.tw/2016/02/excelyahoo-apiexcel.html

------以下是內文------

之前有用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

縮網址：
https://goo.gl/Od1fpe

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.cc), 來自: 218.187.103.222
※ 文章網址: https://www.ptt.cc/bbs/Stock/M.1455634243.A.909.html
※ 編輯: kslman (218.187.103.222), 02/16/2016 22:53:47
修正了一下連結
推 becareful   : 推
→ Justisaac   : 這有即時嘛~?還是只是當日?
→ kslman      : 問得好，明天開盤的時候如果有想到，我來研究一下
推 kaka642000  : 分享給推
→ Altair      : yahoo finance給的台股資料不是即時
→ Altair      : Yahoo!奇摩股市(凱基)的才是即時
→ Altair      : 印象中 yahoo finance的是延遲15 or 20分鐘
剛抓了其中一支股票研究了一下，價格跟我手機看盤工具大概差了30分鐘左右。

所以雖然盤中會更新，但相對沒有這麼及時喔，如果有更即時資訊需求的用戶請注意。
推 verylovemary: 推!
修正錯字
※ 編輯: kslman (218.187.103.222), 02/17/2016 00:31:56
推 raa1        : 強
推 ReiTukisima : 推 實用技術心得
→ f204137     : 高手
推 Mtcat       :
推 applethree  : 猛
推 k29571159   : 推高手+實用
推 isky20123   : 推!!多點技術文
推 a199111222  : 收下了 感謝
推 chialin39   : 感謝分享
推 wibblypig   : 推
※ 編輯: kslman (118.163.68.57), 02/17/2016 11:11:12
→ hjgx        : 可以佛心做一個動畫放youtube 教學一下怎麼用嗎
推 yoluofgiprts: 推
推 michaelxxx  : 想問一下有沒有歷史股價的api作法
→ michaelxxx  : 用下網頁+迴圈去抓，往往會抓到當掉
就像我提及的參考網站中有提到：

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

所以yahoo api看起來是可以抓歷史資料的，只是在這邊我們只抓最後交易日股價。

然後我也有查過其他文章，裡面除了yahoo也還有提及google的api，有興趣可參考：
http://lovecoding.logdown.com/posts/257928-use-yahoo-api-to-obtain-historical-stock-price-data

縮網址：
http://goo.gl/6VBKlf
※ 編輯: kslman (118.163.68.57), 02/17/2016 13:41:34
補充了一下檔案的MD5碼。
新增網誌好讀版。
※ 編輯: kslman (118.163.68.57), 02/17/2016 13:44:18
推 iixiii5130  : 推