##title##

2016年2月17日

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

1 則留言:

  1. 相關:臺企銀
    【時報-台北電】臺企銀(2834)自結10月合併各項提存前稅前淨利7億5886萬元,於提列各項提存239萬元後,合併自結稅前淨利7億5647萬元(繼續營業單位合併自結稅前淨利7億5647萬元),合併自結稅後淨利6億934萬元。
    前10月合併累計自結各項提存前稅前淨利86億1814萬元,於提列各項提存6億7669萬元後,合併累計自結稅前淨利79億4144萬元(繼續營業單位合併累計自結稅前淨利79億4144萬元),合併累計自結稅後淨利66億2657萬元,合併每股稅前盈餘1.24元,合併每股稅後盈餘1.04元,合併每股淨值13.15元。(編輯整理:張嘉倚)

    回覆刪除