製作動態更新股票資訊的google sheets
為了要方便觀察股票的價格漲跌、本益比等變化,又不想要每天自己key資料,所以就打算利用google sheets製作動態更新的表格,這邊有兩個函式可以使用:
- GoogleFinance
- importXML
以下就分別進行說明
GoogleFinance
用法可以參考Google的說明
舉個例子,如果要知道台積電現在的股價,可以用以下的函式:
- = GoogleFinance( CONCATENATE("TPE:",A3),"PRICE")
- 或是
- = GoogleFinance("TPE:2330","PRICE")
- CONCATENATE這個函式可以把多個字串組合起來
成果如下:
但是GoogleFinance有ㄧ個缺點,他只能觀察上市股票,而無法觀察上櫃或是興櫃的股票,這時候就需要用到另一個函式:importXML
importXML
為了觀察上櫃跟興櫃的股票,我們需要用到importXML這個爬蟲函式,從某些公開資訊網站截取個股相關的訊息,這邊使用玩股網做為抓取資料的網站(原本想使用證交所的基本市況報導網站進行抓取,但是因為這個網站上的資料是利用javascript進行更新,不像玩股網一樣是直接用文字顯示,要重新整理網頁才會更新資料,這部份我還沒研究出來怎麼抓)
用法如下:
- =importXML(網址, XPath_查詢)
邏輯是從某個網頁的原始碼中(按ctrl+u可以看到的),抓出某一個tag(目錄)裡面的資料
網址就是要抓取資料的網頁,如玩股網的台積電頁面:
X Path的取得:
選取網站資料 → 點右鍵 → 檢查 → 在右邊標註的html程式點右鍵 → 選擇copy → 選擇 copy xpath
複製下來的x path會镸這個樣子:
- //*[@id="container"]/div[3]/div[2]/ul/li[4]/b
接著把ㄧ些標示id等標籤的部分去掉,結果如下:
- //*/div[3]/div[2]/ul/li[4]/b
不過有些複製出來的x path會長這個樣子:
- //*[@id="ChangeInfoDiv"]/span[3]
就必需自己做ㄧ些處理,把雙引號轉成單引號:
- //*[@id='ChangeInfoDiv']/span[3]
以下是一個範例,有做部分資料格式的處理:
股票現價:
- =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[3]/div[2]/ul/li[4]/b") +if(importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[3]")<0, - right(importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[2]"),len(importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[2]"))-1), right(importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[2]"),len(importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[2]"))-1))
- or =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//div[@id='ChangeInfoDiv']/span[1]")
漲跌:
- =right(importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[2]"),len(importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[2]"))-1) or =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[2]") or =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*[@id='ChangeInfoDiv']/span[2]")
漲跌幅(%):
- =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[1]/span[3]") or = importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*[@id='ChangeInfoDiv']/span[3]")
昨收:
- =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[3]/div[2]/ul/li[4]/b")
本益比:
- =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[3]/div[2]/ul/li[8]/b")
股價淨值比:
- =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[3]/div[2]/ul/li[7]/b")
結果如下圖:
非常感謝 分享
回覆刪除找了好久謝謝版主!
回覆刪除願樓主一生平安
回覆刪除不知道樓主在哪裡學到這些?