利用 Google 試算表及 Google Finance 函數取得股價資料 (含範例下載)
透過 IMPORTXML 函數整合外網資料,以及如何使用 Python 讀取 Google 試算表
近年來各種 Low-Code / No-Code 網路服務相當流行,其目的是讓沒有程式設計背景的一般知識工作者,透過 UI 設定一些函數及參數後,自行完成客製化的計算流程及服務。Google Sheet 就提供了這樣的功能,本文介紹 (1) 如何利用 Google 試算表及 Google Finance 函數取得股價資料; (2) 透過 IMPORTXML 函數整合外網資料進試算表; 以及 (3) 如何使用 Python 及 pandas 讀取 Google 試算表。
(連結至本文範例試算表及 Colab Notebook)
取得歷史資料
Google Finance 函數的基本用法如下:
- [代號]:股票代號,如 TPE:2330 (台積電)
- [屬性]:可以是歷史屬性(如指定日期的最高價或最低價)、即時屬性(如即時報價或今日成交量)、或共同基金屬性(如最近的現金配息金額)
- [間隔]:回傳資料的頻率,只能是 daily 或 weekly
例如 GOOGLEFINANCE("TPE:0050", "all", "2022-01-01", "2022-12-31", "weekly")
會回傳 2022 一整年 0050 ETF 的開盤價、收盤價、最高價、最低價、及當日成交量(以週為單位)
而 GOOGLEFINANCE("TPE:2330", "all", Today()-30, Today(), "daily")
會回傳台積電最近一個月的每日價量資訊
取得即時資料
接著我們使用 GoogleFinace 函數的不同屬性 (price, high, tradetime 等)取得數支股票的即時資訊。只要準備好表格欄位及屬性值,就可以很輕易地複製產出所有資料欄位
Google Sheet 預設是每天自動更新一次,可以在左上角選單「檔案」、「設定」中選擇每小時或每分鐘自動更新一次
利用 IMPORTXML() 取得外網資料
如果想取得 Google Finance 函數沒有支援的股票資料,可以利用 IMPORTXML() 作簡單的網頁資料擷取。其基本格式為:
假設我們對 Yahoo 股票網頁提供的「近 5 年平均現金殖利率」有興趣,其網址是:
例如 https://tw.stock.yahoo.com/quote/2330.TW/dividend 就是台積電的資料
這個例子中,我們有興趣的是 2.69% 這個數字,透過開發者工具可以取得其 XPath 為
而函數 IMPORTXML("https://tw.stock.yahoo.com/quote/2330.TW/dividend", "//*[@id='main-2-QuoteDividend-Proxy']/div/section[2]/p/span[2]/span[2]")
就會回傳 2.69%。因此,透過簡單的字串組合 (Concatenate) 就可以取得不同股票的殖利率資料
使用 Python 及 pandas 讀取 Google 試算表
在某些情況下你可能還是想寫程式讀入 Google 試算表(例如完成更複雜的表單整合及處理流程)。使用 Python 及 pandas 讀取 Google Sheet 的步驟相當容易,首先設定試算表允許公開讀取,再從試算表網址取得文件 ID,如以下粗體部份:
https://docs.google.com/spreadsheets/d/16FC5yB93qLkv_w8aLV5G5gF4CLS8n5VgH4-Msj1_YXI/edit?usp=sharing
接著記下表單名稱,以上面的試算表為例,表單名稱就是 History 或 RealTime. 最後,Google 試算表支援以下的 CSV 下載格式:
以讀取上面試算表的 RealTime 表單為例: