2021年3月31日 星期三

Google Sheets 抓上櫃股票股價

在 Google Sheets 試算表中,上市股票股價可以使用 GOOGLEFINANCE() 取得股價。 
上櫃OTC股票則需另外用指令碼處理。
  1. 新增一個工作表,假設名稱為「上櫃股票」,並填入股票代碼。
    如下圖:


  2. 「工具」 -> 「指令碼編輯器」


  3. 填入以下程式碼,選擇執行「mainOTC()」
    function getDataOTC(sheet_name, col_code, col_stock_name, col_price, col_updtime, col_err, row_start, row_end) {
      var spreadsheet = SpreadsheetApp.getActive();
      var rows = spreadsheet.getRange(sheet_name + "!" + col_code + row_start + ":" + col_code + row_end);
      var numRows = rows.getNumRows();
      var data = rows.getValues();
      var options = {
        //若來源網址SSL不安全(或過期)出現Exception: SSL Error,且確定來源資料安全時,可關閉validateHttpsCertificates
        //'validateHttpsCertificates': false
      };
      for (var i = 0; i < numRows; i++) {
        var code = data[i][0];
        //上市
        //https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=tse_2353.tw&json=1&delay=0
        //上櫃
        //https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=otc_6488.tw&json=1&delay=0
        var url = "https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=otc_" + code + ".tw&json=1&delay=0"
        //舊的
        //var url = "https://mis.tse.com.tw/stock/api/getStock.jsp?ch=" + code + ".tw&json=1&_=";
        try {
          var response = UrlFetchApp.fetch(url);
          var json = response.getContentText("UTF-8");
          var data_arr = JSON.parse(json);
          spreadsheet.getRange((sheet_name + "!" + col_stock_name) + (row_start + i)).setValue(data_arr.msgArray[0].n);//股票名稱
          // data_arr.msgArray[0].y 股價(昨天成交價)
          // data_arr.msgArray[0].z 股價(最近成交價)
          var stock_price = data_arr.msgArray[0].z.trim();
          if (isNaN(parseFloat(stock_price))) {
            throw ("股價數值異常:" + stock_price);//盤中可能抓不到股價,抓到"-"
          }
          spreadsheet.getRange((sheet_name + "!" + col_price) + (row_start + i)).setValue(stock_price);
          spreadsheet.getRange((sheet_name + "!" + col_updtime) + (row_start + i)).setValue("股價更新:" + Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss"));
          spreadsheet.getRange((sheet_name + "!" + col_err) + (row_start + i)).setValue("");
        } catch (e) {
          spreadsheet.getRange((sheet_name + "!" + col_err) + (row_start + i)).setValue("Err:" + Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss") + " " + e);
        }
        Utilities.sleep(1000);//等1秒再繼續下一個
      }
    }
    
    function mainOTC() {
      getDataOTC(
        "上櫃股票", //工作表名稱
        "A",//股票代碼在工作表的欄位
        "B",//股票名稱在工作表的欄位
        "C",//股價在工作表的欄位
        "D",//更新時間在工作表的欄位
        "E",//錯誤訊息在工作表的欄位
        2,  //資料從第幾列開始
        21  //資料在第幾列結束
      );
    }



  4. 因為要在工作表自動填入資料,所以須授權程式可存取 Google Docs
    若之後要取消授權,可於 https://myaccount.google.com/permissions?pli=1 取消。














  5. 抓取結果


  6. 可設定「觸發條件」,讓程式每隔一段時間自動執行,避免每次都要手動執行。











參考:

沒有留言:

張貼留言