製作動態更新股票資訊的google sheets

為了要方便觀察股票的價格漲跌、本益比等變化，又不想要每天自己key資料，所以就打算利用google sheets製作動態更新的表格，這邊有兩個函式可以使用：

1. GoogleFinance
2. importXML

以下就分別進行說明

GoogleFinance

用法可以參考Google的說明

舉個例子，如果要知道台積電現在的股價，可以用以下的函式：

1. = GoogleFinance( CONCATENATE("TPE:",A3),"PRICE")
2. 或是
3. = GoogleFinance("TPE:2330","PRICE")
4. CONCATENATE這個函式可以把多個字串組合起來

成果如下：

但是GoogleFinance有ㄧ個缺點，他只能觀察上市股票，而無法觀察上櫃或是興櫃的股票，這時候就需要用到另一個函式：importXML

importXML

為了觀察上櫃跟興櫃的股票，我們需要用到importXML這個爬蟲函式，從某些公開資訊網站截取個股相關的訊息，這邊使用玩股網做為抓取資料的網站(原本想使用證交所的基本市況報導網站進行抓取，但是因為這個網站上的資料是利用javascript進行更新，不像玩股網一樣是直接用文字顯示，要重新整理網頁才會更新資料，這部份我還沒研究出來怎麼抓)

用法如下：

1. =importXML(網址, XPath_查詢)

邏輯是從某個網頁的原始碼中(按ctrl+u可以看到的)，抓出某一個tag(目錄)裡面的資料

網址就是要抓取資料的網頁，如玩股網的台積電頁面：

1. https://www.wantgoo.com/stock/2330

X Path的取得：

選取網站資料 → 點右鍵 → 檢查 → 在右邊標註的html程式點右鍵 → 選擇copy → 選擇 copy xpath

複製下來的x path會镸這個樣子：

1. //*[@id="container"]/div[3]/div[2]/ul/li[4]/b

接著把ㄧ些標示id等標籤的部分去掉，結果如下：

1. //*/div[3]/div[2]/ul/li[4]/b

不過有些複製出來的x path會長這個樣子：

1. //*[@id="ChangeInfoDiv"]/span[3]

就必需自己做ㄧ些處理，把雙引號轉成單引號：

1. //*[@id='ChangeInfoDiv']/span[3]

以下是一個範例，有做部分資料格式的處理：

股票現價：

1. =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))
2. or =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//div[@id='ChangeInfoDiv']/span[1]")

漲跌：

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]")

漲跌幅(%)：

1. =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]")

昨收：

1. =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[3]/div[2]/ul/li[4]/b")

本益比：

1. =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[3]/div[2]/ul/li[8]/b")

股價淨值比：

1. =importXML(CONCATENATE("http://www.wantgoo.com/stock/",A3),"//*/div[3]/div[2]/ul/li[7]/b")

結果如下圖：