Low-Code Python

利用 Google 試算表及 Google Finance 函數取得股價資料 (含範例下載)

(本文同步發表於計算思維學院

近年來各種 Low-Code / No-Code 網路服務相當流行,其目的是讓沒有程式設計背景的一般知識工作者,透過 UI 設定一些函數及參數後,自行完成客製化的計算流程及服務。Google Sheet 就提供了這樣的功能,本文介紹 (1) 如何利用 Google 試算表及 Google Finance 函數取得股價資料; (2) 透過 IMPORTXML 函數整合外網資料進試算表; 以及 (3) 如何使用 Python 及 pandas 讀取 Google 試算表。

(連結至本文範例試算表Colab Notebook

取得歷史資料

Google Finance 函數的基本用法如下:

GOOGLEFINANCE(代號, [屬性], [開始日期], [結束日期|天數], [間隔])
  • [代號]:股票代號,如 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() 作簡單的網頁資料擷取。其基本格式為:

IMPORTXML([網址], [XPath])

假設我們對 Yahoo 股票網頁提供的「近 5 年平均現金殖利率」有興趣,其網址是:

https://tw.stock.yahoo.com/quote/[股票代號].TW/dividend

例如 https://tw.stock.yahoo.com/quote/2330.TW/dividend 就是台積電的資料

這個例子中,我們有興趣的是 2.69% 這個數字,透過開發者工具可以取得其 XPath 為

//*[@id="main-2-QuoteDividend-Proxy"]/div/section[2]/p/span[2]/span[2]

而函數 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 下載格式:

https://docs.google.com/spreadsheets/d/{SHEET_ID}/gviz/tq?tqx=out:csv&sheet={SHEET_NAME}

以讀取上面試算表的 RealTime 表單為例:

import pandas as pd
SHEET_ID = '16FC5yB93qLkv_w8aLV5G5gF4CLS8n5VgH4-Msj1_YXI'
SHEET_NAME = 'RealTime'
url = f'https://docs.google.com/spreadsheets/d/{SHEET_ID}/gviz/tq?tqx=out:csv&sheet={SHEET_NAME}'
df = pd.read_csv(url)
df.head()