2014年12月18日 星期四

利用Google試算表(Spreadsheet)取得台股(上市/上櫃)及美股股票報價(2017-05-14 更新)

有網友反應,透過奇摩財經取得台股報價的方法已經失效,這篇文章提供了新的方法,也趁這個機會重新整理了一下。

想要使用Google的試算表(Google Spreadsheet),必須先申請Google Account,並連結到Google Drive雲端硬碟,點擊新增->Google試算表


本文範例請到這個連結下載。(開啟後,左上角選擇檔案->建立副本,就可以到自己的雲端試算表首頁進行編輯)


使用微軟Office Excel的朋友,取得方式比較麻煩,因此建議嘗試用看看Google試算表囉!


若要取得美股的即時報價,請用這個公式:

  • =GOOGLEFINANCE("VTI", "price")
而如果想要用某個欄位的股票代碼來取得報價則是利用這個公式:
  • =GOOGLEFINANCE(A2, "price")
A2表示某欄位的值,需輸入美股的股票代號,範例如下圖:

台股有些股票代號不支援GOOGLEFINANCE公式(像是上櫃公司代號),就要透過其他網站提供的資料,取得報價請使用此公式(請直接複製,在試算表貼上即可):
  • =IFERROR(GoogleFinance(CONCATENATE(A5, ".TW"),"price"), IFERROR(ARRAY_CONSTRAIN(importXML(CONCATENATE("http://m.wantgoo.com/s/",A5),"//*/div[2]/div/div[1]"),1,1))) 
A5表示某欄位的值,需輸入股票代號:例如"0056"、"2412"。

此公式共支援台股上市、上櫃、ETF之股票代號。

若有任何問題,可留言大家討論看看。

延伸閱讀:


喜歡本文的朋友,歡迎到Facebook粉絲專頁HC愛筆記財經部落格按讚,隨時得知最新文章的消息。
愛心做公益活動,本站廣告收入的一部分,將於每個月初撥款至值得信賴的慈善機構,回饋於社會。還請讀者朋友們,不吝發揮你的愛心,謝謝你的幫忙與支持。

91 則留言:

  1. 關於台股,我都是用"TPE:股票代號"的方式來取得,例如王品的股票可用
    =GOOGLEFINANCE("TPE:2727")

    PS: 我看了你的文章學會了用 Google spreadsheet XIRR,對我而言真的是一大突破,台股,美股,匯率,XIRR都可以在 google spreadsheet 一覽無遺,感謝 :)

    回覆刪除
    回覆
    1. Cody你好:
      我試著用你提供的方法輸入:
      =GOOGLEFINANCE("TPE:2727")
      或是
      =GOOGLEFINANCE("TPE:2727", "price")
      都失敗耶!無法回傳股價資料回來,不知道你能不能分享實際範例?

      謝謝你的鼓勵,還希望彼此教學相長^^

      刪除
    2. try 2727.TW please

      刪除
    3. 不能衹放這樣 指令應該是

      GOOGLEFINANCE("TPE:2330","all",DATE(2009,1,1),DATE(2013,1,1),"DAILY
      ")

      刪除
  2. HC大,

    感謝你無私的分享, 特別是寫法非常 elegant, 令人非常激賞。
    受用無窮, 大力給推! :)

    回覆刪除
    回覆
    1. 還謝謝你的鼓勵,希望有空多回來逛逛啦~

      刪除
  3. 大大您好,請問有什麼方法可以輸入上櫃的股票資料嗎??yahoo的那方法我key上櫃的不行耶!

    回覆刪除
    回覆
    1. 剛做了一下修改,你再試一下這個公式:
      =IFERROR(GoogleFinance(CONCATENATE(A5, ".TW"),"price"), IFERROR(importXML(CONCATENATE("http://finance.yahoo.com/webservice/v1/symbols/", A5,".tw/quote"),"//field[@name='price']"), importXML(CONCATENATE("http://finance.yahoo.com/webservice/v1/symbols/", A5,".two/quote"),"//field[@name='price']")))

      刪除
    2. 本篇文章內容跟Google試算表範例都已經更新了,你再試看看,有問題再提出囉!

      刪除
    3. 請問大大,如果要指定日期的收盤價,該如何寫呢?

      刪除
    4. =GoogleFinance("股票代號","price",DATE(2015,11,20), DATE(2015,12,1))
      範例檔已做更新,附上你的需求,請點擊本文開頭提供的範例檔連結觀看。

      刪除
    5. 謝謝大大,請問,上櫃的指定日期的收盤價該如何寫?

      大大效率好快,感謝您。

      刪除
    6. 上櫃是拿yahoo的資料,目前好像是沒辦法查其他日期的資料。

      刪除
    7. 對了,我有看到.TWII 跟 ^TWII的說法,但是測試後好像沒結果,您可以參考看看。

      刪除
    8. 我想要買價, 賣價, 成交價, 不知道能不能捉到 ?

      刪除
  4. 不錯用~有辦法加入美股報價時間嗎?
    美股的報價是即時的~還是有所延遲

    回覆刪除
    回覆
    1. 基本上是即時的喔!!不過有可能需要按重新整理,這部份要再實測看看。

      刪除
  5. 剛試了幾個不錯的功能,本益比和EPS不太種,VTI本益比顯示只有5.25顯然不正確
    漲跌=googlefinance(A2,"change")
    漲跌幅=googlefinance(A2,"changepct")&" %"
    最後成交時間=googlefinance(A2,"tradetime")
    開盤價=googlefinance(A2,"priceopen")
    高價=googlefinance(A2,"high")
    低價=
    交易量=
    延遲時間=googlefinance(A2,"datadelay")
    前一天收盤價=googlefinance(A2,"closeyest")
    本益比=googlefinance(A2,"pe")
    EPS=googlefinance(A2,"eps")

    回覆刪除
    回覆
    1. 謝謝狗狗的測試與分享,
      這裡有更多的用法,也可以參考看看:
      https://support.google.com/docs/answer/3093281?hl=en

      刪除
  6. 另外回報一下~
    我使用=googlefinance("TPE:2727")
    是可以讀到值的~感恩~~

    回覆刪除
    回覆
    1. 我照樣輸入會顯示"錯誤: 不支援 TPE 交易所。",還是說你們可以share一下你們的Google Spredsheet讓我參考看看問題出在哪@@

      刪除
  7. 我試看看可不可以share~

    https://docs.google.com/spreadsheets/d/1xknkT4LuSVrokMJldvKCHw2k_Lso-KGFyXp8Ost9vJ4/edit?usp=sharing

    回覆刪除
    回覆
    1. 這個公式=googlefinance("TPE:2727")
      試了你的範例以後竟然就可以了,
      之前一直出現錯誤訊息XDD 真是怪了~
      謝謝分享^^

      刪除
  8. 推一個,自從台灣奇摩不給抓之後,終於可以用了!

    回覆刪除
    回覆
    1. 山不轉路轉,路不轉人轉,人不轉心轉。

      刪除
  9. 這個函數是連結個股的報價,請問有辦法連結台股大盤指數和美股三大指數嗎?這樣就可以知道今天台股大盤是幾點、道瓊、s&p 500、費城半導體是幾點。謝謝。

    回覆刪除
    回覆
    1. 這部份已經更新在範例檔,請點擊文章提到的範例就可以看到如何使用了。

      刪除
    2. 請看本文章開頭有提供範例檔連結

      刪除
    3. 您好請問,那上證指數、深圳指數及日本、韓國、香港等指數抓的到嗎??

      是否有地方有辦法查詢代號呢??謝謝您

      刪除
  10. 請問用這個函數查的到黃金、白銀或一些原物料的價格嗎?

    回覆刪除
  11. 我找了好久都查不到代號,應該是要從別的地方做連結。

    回覆刪除
  12. 請問有辦法針對中國的個股作追蹤嗎?我試著以GOOGLE FINANCE的函數套入,發現試算表無法抓到個股價格

    回覆刪除
    回覆
    1. 不包括市值低于 10亿人民币 公司的股票。了解详情
      https://www.google.com.hk/finance?hl=zh-CN&ei=nyhKVcWcNZWGuQTF2YHIBQ


      注意事項
      GOOGLEFINANCE 只有英文版,而且不支援大部分國家的證券交易資料。

      刪除
    2. 請問:有啥方式可以取得雅虎股市上的這些資料? 多謝

      http://www.programtrading.tw/viewtopic.php?f=3&t=22855

      刪除
    3. 太複雜的建議就是By Excel

      刪除
  13. 請問:這些分享的程式碼適用於 google 試算表嗎? 擷取雅虎股市資料


    感恩

    http://blog.xuite.net/smile1000mile/blog/210628980#message_header
    http://blog.xuite.net/smile1000mile/blog/95033546#message_header



    回覆刪除
    回覆
    1. 太複雜的建議就是By Excel

      刪除
  14. 請問:
    google Spreadsheet,如何自動產生流水編號?
    =text(row()-1,""0000""), ms excel 上面的方式,google 無法使用......

    https://www.ptt.cc/bbs/Office/M.1401173400.A.F03.html


    多謝





    多謝


    回覆刪除
    回覆
    1. 在A1輸入純文字"0000",並在A2欄輸入"=TEXT(A1+1, "0000")"並複製,直接貼上整欄,他就會從0000~9999,這個方法不知道是不是你要的?

      刪除
  15. 請問有自動抓取台股名稱的公式嗎~懶得自己輸入XDD~

    回覆刪除
    回覆
    1. 來人啊,拖出去斬了(大誤)

      刪除
  16. 報告我的可轉換債不能抓耶 35141昱晶一

    回覆刪除
  17. 第一次逛到你的BLOG,真是個好地方!廣告一定要點給支持。謝謝分享許多有用訊息。

    回覆刪除
  18. 今天發現他無法抓上櫃跟權證的價格了~ 上市公司沒問題~ 不曉得什麼原因~

    回覆刪除
    回覆
    1. 我也發現了~上櫃無法更新@@

      刪除
    2. 我也遇到了..希望版主有新的解法..

      刪除
    3. 上櫃股價無法更新了.....=.=

      刪除
    4. 現在好像又正常了,好怪@@

      刪除
    5. 好像是隨機的~ 時好時壞~ 剛重新建立副本~ 一度正常~ 但過幾秒 又自己跳成NA~ 懷疑是google的問題~

      刪除
  19. 回覆
    1. 我自己是編輯一下股票代號(例如是0050,我就修改成為005給他重新讀取,再改回去0050,這樣就好了,只是有點麻煩就是了)

      刪除
  20. 我一直都有在使用這個功能,不過跟樓上的Vincent 一樣,發現他壞掉了。照著Vincent 的方式作了一次,還是不行。我覺得是Google的問題,如果有人有解法的話麻煩不吝分享一下,感謝!

    回覆刪除
    回覆
    1. 我自己是編輯一下股票代號(例如是0050,我就修改成為005給他重新讀取,再改回去0050,這樣就好了,只是有點麻煩就是了)

      刪除
    2. 版主你的方式我試過沒用喔~ 原本NA的 還是會跳成NA~ 例如2345原本顯示NA~ 改成其他的~ 會loading~ 然後顯示(或也是NA)~ 改回2345 還是NA~ 但放著不動有時候又正常出現~ 過一陣子又NA~ QQ 我後來只好不用公式~ 自己盤後手Key股價了...(美股抓取都一切正常)

      刪除
    3. 只要建立複本 就變正常

      刪除
  21. 先感謝版主的文章提供了很有用的資訊。Google Sheets的importxml一直有問題,GoogleFinance讀不到的股票可以自已建立函式去讀Yahoo Finance的股價,步驟如下:

    1. open up a new, or existing sheet.
    2. Tools menu -> Script editor
    3. Delete the text which it shows (the function myFunction() { } stuff. )
    4. 貼上以下程式碼並儲存(隨便幫project取個名字):

    function readStockPriceFromYahoo(stock_id){
    var url = "http://finance.yahoo.com/webservice/v1/symbols/"+stock_id+".TW/quote";
    var parameters = {method : "get", payload : ""};
    var xmlFeed = UrlFetchApp.fetch(url, parameters).getContentText();
    var xml = XmlService.parse(xmlFeed);
    var rows=xml.getRootElement().getChild("resources").getChild("resource").getChildren("field");
    return rows[1].getValue();
    }

    function readStockPriceFromYahooOTC(stock_id){
    var url = "http://finance.yahoo.com/webservice/v1/symbols/"+stock_id+".TWO/quote";
    var parameters = {method : "get", payload : ""};
    var xmlFeed = UrlFetchApp.fetch(url, parameters).getContentText();
    var xml = XmlService.parse(xmlFeed);
    var rows=xml.getRootElement().getChild("resources").getChild("resource").getChildren("field");
    return rows[1].getValue();
    }
    function onEdit(e)
    {
    SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
    }

    5. 接下來就可用自建的函式去抓取股價了,兩點說明:一、目前試算表的A1格不能使用!二、按CTRL-R更新(編輯資料也會更新)。
    上市股票的範例:
    =readStockPriceFromYahoo("0056",$A$1)
    上櫃股票的範例:
    =readStockPriceFromYahooOTC("8942",$A$1)

    (參考資料來源1:https://www.fuzzwork.co.uk/2014/09/12/google-docs-xml-api-nightmare-importxml/)
    (參考資料來源2:http://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-spreadsheet)

    回覆刪除
    回覆
    1. 請問這樣的方式股價會有很多小數點...要如何調整?有時後會呈現像69.699997 這樣的小數

      刪除
    2. 上方工具列在百分比%的旁邊,有一個.0(<-)的按鈕,那個就是縮小數字的位數的按鈕。

      刪除
  22. 由於 Google Finance 有不少台股股價查不到
    若用 yahoo 的查詢方式,要查前幾天的台股股價該怎麼查?

    回覆刪除
  23. 版主你好:
    公司網路會擋google drive所以不能使用google試算表,但可以使用google finacial,不知您有無解決方法,謝謝你

    回覆刪除
    回覆
    1. 公司網路會擋的問題,這不是我能解決的唷@@

      刪除
  24. 請問可以用google試算表報出美股股票選擇權的即時權利金嗎?謝謝。

    回覆刪除
  25. 請教各位前輩如何抓Yahoo 資料庫的前一日的收盤價呢? field 應該填入什麼?
    //field[@name='???']")

    謝謝

    回覆刪除
  26. 請問...目前上櫃都無法抓取股價.....是哪裡出問題嗎....還是GOOGLE本身的問題...謝謝

    回覆刪除
    回覆
    1. 您好,是yahoo的網址有問題,你可以試試以下的公式,其中B5是儲存格,表示股號的值:
      =IFERROR(GoogleFinance(CONCATENATE(B5, ".TW"),"price"), IFERROR(importXML(CONCATENATE("https://finance.yahoo.com/quote/", B5,".TWO?p="),"//*/div[2]/div[1]/div/span[1]"), importXML(CONCATENATE("https://finance.yahoo.com/quote/", B5,".TWO?p="),"//*/div[2]/div[1]/div/span[1]")))

      刪除
    2. 太感謝你了....問題解決了

      刪除
    3. 有些上櫃因上述網址好像還有問題,如果改成以下會更適用:
      =IFERROR(GoogleFinance(CONCATENATE(B5, ".TW"),"price"), IFERROR(importXML(CONCATENATE("https://finance.yahoo.com/quote/", B5,".TWO?p=",B5,".TWO"),"//*/div[2]/div[1]/div/span[1]"), importXML(CONCATENATE("https://finance.yahoo.com/quote/", B5,".TWO?p=",B5,".TWO"),"//*/div[2]/div[1]/div/span[1]")))

      刪除
    4. 上櫃方面還是會出現 #NA

      刪除
    5. 大師你好......本來兩個程式都試著好好的....下午兩個程式又開始出問題了......麻煩了...謝謝

      刪除
    6. 跪求大師解決,我也一樣跑不出股價

      刪除
  27. 換個網址,以下是土法練鋼,B3是股號:=importXML(CONCATENATE("http://www.wantgoo.com/stock/",B3),"//*/div[3]/div[2]/ul/li[4]/b") +if(importXML(CONCATENATE("http://www.wantgoo.com/stock/",B3),"//*/div[1]/span[3]")<0, - right(importXML(CONCATENATE("http://www.wantgoo.com/stock/",B3),"//*/div[1]/span[2]"),len(importXML(CONCATENATE("http://www.wantgoo.com/stock/",B3),"//*/div[1]/span[2]"))-1), right(importXML(CONCATENATE("http://www.wantgoo.com/stock/",B3),"//*/div[1]/span[2]"),len(importXML(CONCATENATE("http://www.wantgoo.com/stock/",B3),"//*/div[1]/span[2]"))-1))

    回覆刪除
    回覆
    1. 可以用耶,可是可轉債如37082上緯投控二 便掛了

      刪除
    2. 請問要抓上櫃股票的價差公式要怎麼改?

      刪除
  28. 我要抓3689的股價沒辦法抓~~

    回覆刪除
    回覆
    1. IFERROR(GoogleFinance(CONCATENATE(B17, ".TW"),"price"), IFERROR(ARRAY_CONSTRAIN(importXML(CONCATENATE("http://m.wantgoo.com/s/",B17),"//*/div[2]/div/div[1]"),1,1), ARRAY_CONSTRAIN(importXML(CONCATENATE("http://m.wantgoo.com/s/",B17),"//*/div[2]/div/div[1]"),1,1)))

      刪除
  29. 上櫃指數又無法抓取了,請問該如何修改呢?

    回覆刪除
  30. CONCATENATE 的部分建議都用雙引號
    Ex:
    =IFERROR(ARRAY_CONSTRAIN(importXML(CONCATENATE("http://m.wantgoo.com/s/","00679b"),"//*/div[2]/div/div[1]"),1,1))

    回覆刪除
  31. 現在google finance還可以用嗎
    https://support.google.com/finance/?hl=zh-Hant

    回覆刪除
    回覆
    1. 他是說停止投資組合功能,但我們只是要報價而已,並沒有受到影響。

      刪除
  32. 你好 想請問一下
    我測試了一下想用函數直接抓大盤收盤指數

    =GOOGLEFINANCE("TWII" ,"close","2017/12/26")
    若我參數這樣打會出現異常抓不到資料 但把日期改成25號 就可以正常 但抓出來是26號的資料 不知道這日期的部分是怎麼算的?

    另外想請問一下通常台股收盤之後 GOOGLEFINANCE抓當天收盤指數會延遲多久?
    測試1430左右還是抓不到當天的收盤價資訊

    回覆刪除
  33. 請問要怎麼抓上櫃的昨天收盤價格,謝謝

    回覆刪除
  34. 請問怎麼抓上櫃股票的一段時間歷史股價?
    謝謝...

    回覆刪除
  35. 請問可抓到日股的股價嗎?

    回覆刪除