[投資] 試算表台股上市/櫃資訊取得

9 min readMay 4, 2024

GOOGLEFINANCE 的限制

在投資的旅程中，追蹤台灣股市的交易紀錄是一個關鍵的步驟。對許多投資者來說，Google 試算表是一個方便而強大的工具，可以幫助我們有效地管理和追蹤股票投資。然而，即使 Google 試算表提供了許多有用的函數和功能，但它仍然存在一些限制，尤其是在抓取台灣股市資訊方面。

一個常見的問題是，Google 試算表的 GOOGLEFINANCE 函數僅限於抓取台灣上市股票的資訊，而無法抓取台灣上櫃股票的相關資料。這對於那些對台灣整個股市進行全面追蹤的投資者來說是一個挑戰。

在尋找解決方案的過程中，Google Apps Script 這個強大的工具，它可以幫助我們自動化從台灣證券交易所和櫃檯買賣中心獲取每日交易資訊並記錄到 Google 試算表中。在這篇文章中，我將分享如何使用 Google Apps Script 解決這個問題，讓每個人可以輕鬆地追蹤台灣整個股市的交易情況。

使用 Google Apps Script 自動化台股紀錄

在面對 Google 試算表無法抓取台灣上櫃股票資訊的挑戰時，其中一個常見的解決方案是透過安裝使用 IMPORTXML 函數來從財經資訊網等網站抓取個股報價，但這個方案須仰賴財經網站。另一個強大的解決方案則是可以考慮使用：Google Apps Script。這是一個基於 JavaScript 的平台，可以讓你在 Google Workspace 的各種應用程式中自動化任務和創建自定義功能。下面我將逐步解釋如何使用我提供的程式碼，使用 Google Apps Script 自動化台股交易紀錄。

首先，讓我們來了解一下程式碼的結構和功能：

fetchUrl() 函數：這個函數負責從指定的 URL 獲取資料。它使用了 UrlFetchApp.fetch() 方法來發送 HTTP 請求，並會嘗試多次後返回網站的響應。
parseStockData() 函數：這個函數將從網站獲取的資料進行解析處理並寫入到指定的 Google 試算表的指定工作表中。
importTSEstockinfo() 和 importOTCstockinfo() 函數：這兩個函數分別用於從台灣證券交易所和櫃檯買賣中心獲取每日交易資訊。它們使用了之前定義的 fetchUrl() 和 parseStockData() 函數來完成任務，並將資料寫入到名為「每日上市成交資訊」和「每日上櫃成交資訊」的工作表中。而要如何指定是哪個試算表則透過使用 PropertiesService.getScriptProperties().getProperty(“sheetID”) 來指定我們的試算表。

function fetchUrl(url) {
  // 嘗試最多三次來獲取URL資料
  for (var count = 0; count < 3; count++) {
    try {
      var response = UrlFetchApp.fetch(url, {
        method: "GET",
        muteHttpExceptions: true
      });
      
      // 如果響應代碼為200，則返回響應內容
      if (response.getResponseCode() == 200) {
        return response;
      }
      
      // 如果伺服器錯誤，則等待3秒後重試
      Utilities.sleep(300);
    } catch (error) {
      // 記錄任何捕獲的錯誤和相關的URL
      Logger.log("Error fetching data from URL: " + url);
      Logger.log("Error message: " + error.message);
    }
  }
  
  // 如果三次嘗試後仍然失敗，則拋出錯誤
  throw new Error("Failed to fetch data from URL after 3 attempts: " + url);
}

function parseStockData(data, sheetName) {
  // 使用正則表達式移除所有引號
  const regex = /"/g;
  var dataset = [];
  // 將數據分割成行
  var rows = data.getContentText().split('\n');
  for (var position = 0; position < rows.length && rows[position] != ""; position++) {
    line = rows[position].replace(regex, '').split(',');
    dataset.push(line);
  }
  
  // 打開指定ID的工作表並獲取指定名稱的子表
  var sheet = SpreadsheetApp.openById(PropertiesService.getScriptProperties().getProperty("sheetID")).getSheetByName(sheetName);
  // 清除子表的現有內容
  sheet.clearContents();
  // 設置範圍，準備寫入新數據
  var range = sheet.getRange(sheet.getLastRow()+1, 1, dataset.length, line.length);
  // 設定單元格格式為文本並填充數據
  range.setNumberFormat("@").setValues(dataset);
}

function importTSEstockinfo() {
  // 指定台灣證券交易所每日交易資訊的URL
  const url = "https://www.twse.com.tw/exchangeReport/STOCK_DAY_ALL?response=open_data";
  // 獲取網站資料
  var response = fetchUrl(url);
  // 解析資料並寫入指定的Google Sheets工作表
  parseStockData(response, '每日上市成交資訊');
}

function importOTCstockinfo() {
  // 指定台灣櫃檯買賣中心每日交易資訊的URL
  const url = "https://www.tpex.org.tw/web/stock/aftertrading/DAILY_CLOSE_quotes/stk_quote_result.php?l=zh-tw&o=data";
  // 獲取網站資料
  var response = fetchUrl(url);
  // 解析資料並寫入指定的Google Sheets工作表
  parseStockData(response, '每日上櫃成交資訊');
}

實際應用

步驟 1: 打開 Apps Script 編輯器

新增工作表 “每日上市成交資訊” 和 “每日上櫃成交資訊”。
在您的 Google 試算表中，點擊上方工具列的「擴充功能」。
選擇「Apps Script」。

步驟 2: 編寫腳本

在 Apps Script 編輯器中將前面提供的程式碼貼上。
點選左側下方設定將試算表ID填入到屬性 (Properties) 中。

Property 填入 sheetID。
Value 填入試算表ID (試算表ID 可以從試算表網址中取得)。

步驟 3: 設定觸發器

在 Apps Script 編輯器中，點擊左邊的「觸發器」圖示（看起來像是一個時鐘）。
點擊頁面底部的「+ 新增觸發器」。
在「選擇要執行的函數」選擇剛建立的函數。
在「選擇事件來源」選擇「從試算表」。
在「選擇事件類型」選擇「打開時」。
設定其他選項（如錯誤通知設定），然後點擊「儲存」。

設定好觸發器後，每次文件被打開時，就會自動執行您設定的函數。另外，也可以透過設定固定時間觸發來保持工作表內的資料定期刷新。

註記: 第一次設定時會要求給予權限。

步驟四: 設定試算表

實際應用我們可以結合試算表函式取用我們呼叫APPs Script取得的資料。

比如下面試算表的股票欄位我們可以用

=IF(ISBLANK(C2), "",
  IFERROR(
    (VLOOKUP(C2,'每日上櫃成交資訊'!B:C,2,FALSE)),
    IFERROR(
      (VLOOKUP(C2,'每日上市成交資訊'!A:B,2,FALSE)),
      "找不到資訊"
    )
  )
)

而現價則可以變成如下透過IFERROR判斷當無法從GOOGLEFINANCE拿到資料時由工作表內的內容去取得

=IF(ISBLANK(C2), "",
  IFERROR(
    GOOGLEFINANCE("TPE:"&C2),
        IFERROR(
          VALUE(VLOOKUP(C2,'每日上櫃成交資訊'!B:D,3,FALSE)),
          IFERROR(
            VALUE(VLOOKUP(C2,'每日上市成交資訊'!A:J,8,FALSE)),
        "找不到資訊"
          )
        )
  )
)