三步驟將台股即時股價寫入Google試算表

本文說明如何把即時股價記錄到Google試算表 (Google Spreadsheet)，也可以利用其他plugin將股價畫成圖表方便分析。

• 2017.09.14更新，證交所又改動Cookie認證方式，請抓文中的新版程式就可以正常執行。
• 2017.02.21更新，證交所現在對於API的時間規範更嚴格了，目前一定要帶參數 _={timestamp_ms}。我已經修改完範例程式加入Failover機制。

目前台股的股價即時資訊可以由Google Finance API、Yahoo Finance API取得，但是Google需要OAuth2且目前已經deprecated，隨時可能終止服務。Yahoo YQL 則是剖析起來很麻煩，所以還是使用官方的台灣證券交易所 – 基本市況報導網當作資料來源。 基本的request如下，其中上市股票為 tse_{股票代號}.tw ，上櫃股票為 otc_{股票代號}.tw ，一次查詢多個股票則可以用”|”將代號隔開，在此例中我請求台泥及台灣50報價 。

Request：

http://mis.twse.com.tw/stock/api/getStockInfo.jsp?json=1&delay=0&ex_ch= tse_1101.tw|tse_0050.tw

回傳的Response如下：
我們要的資料都在 msgArray ， 裡面包含了台泥跟台灣50的股票即時資訊，相關欄位請參考 證交所即時資訊API欄位分析 。

{
    "msgArray": [{
        "ts": "0",
        "fv": "3",
        "tk0": "1101.tw_tse_20160412_B_9999153601",
        "tk1": "1101.tw_tse_20160412_B_9999133707",
        "oa": "30.45",
        "ob": "30.35",
        "tlong": "1460442600000",
        "ot": "14:30:00",
        "f": "111_300_700_319_534_",
        "ex": "tse",
        "g": "213_7_465_334_1952_",
        "ov": "2663",
        "d": "20160412",
        "it": "12",
        "b": "30.40_30.35_30.30_30.25_30.20_",
        "c": "1101",
        "mt": "000000",
        "a": "30.50_30.55_30.60_30.65_30.70_",
        "n": "台泥",
        "o": "30.20",
        "l": "29.90",
        "oz": "30.35",
        "h": "30.60",
        "ip": "0",
        "i": "01",
        "w": "27.20",
        "v": "5674",
        "u": "33.20",
        "t": "13:30:00",
        "s": "320",
        "pz": "30.50",
        "tv": "320",
        "p": "0",
        "nf": "台灣水泥股份有限公司",
        "ch": "1101.tw",
        "z": "30.50",
        "y": "30.20",
        "ps": "320"
    }, {
        "nu": "http://www.yuantaetfs.com/#/RtNav/Index",
        "ts": "0",
        "fv": "3",
        "tk0": "0050.tw_tse_20160412_B_9999154819",
        "tk1": "0050.tw_tse_20160412_B_9999133794",
        "oa": "63.25",
        "ob": "63.20",
        "tlong": "1460442600000",
        "f": "47_390_630_101_148_",
        "ot": "14:30:00",
        "ex": "tse",
        "g": "36_36_382_392_477_",
        "ov": "3865",
        "d": "20160412",
        "it": "02",
        "b": "62.95_62.90_62.85_62.80_62.75_",
        "c": "0050",
        "mt": "000000",
        "a": "63.00_63.05_63.10_63.15_63.20_",
        "n": "台灣50",
        "o": "63.05",
        "l": "62.75",
        "oz": "63.20",
        "h": "63.50",
        "ip": "0",
        "w": "56.70",
        "v": "2747",
        "u": "69.30",
        "t": "13:30:00",
        "s": "72",
        "pz": "62.95",
        "tv": "72",
        "p": "0",
        "nf": "元大寶來台灣卓越50證券投資信託基金",
        "ch": "0050.tw",
        "z": "62.95",
        "y": "63.00",
        "ps": "72"
    }],
    "userDelay": 5000,
    "rtmessage": "OK",
    "referer": "",
    "queryTime": {
        "sysTime": "22:34:08",
        "sessionLatestTime": -1,
        "sysDate": "20160412",
        "sessionKey": "tse_1101.tw|tse_0050.tw|",
        "sessionFromTime": -1,
        "stockInfoItem": 1109,
        "showChart": false,
        "sessionStr": "UserSession",
        "stockInfo": 197210
    },
    "rtcode": "0000"
}

詳細步驟

1. 打開一張新的Google試算表，點選”工具” -> “指令碼編輯器”

2. 把下面的程式碼貼到編輯框中按”儲存”
   這個API常常改變(畢竟非官方管道XD)，去年忽然不能連了，有一種解法是在request加上參數 _={timestamp_ms}，經過我實測這個方法依然常失敗，最正確的做法是先打一下證交所網站拿到session cookie，然後將此cookie送給API才能順利取得資料。
   另外建議直接用IP連線，因為 mis.twse.com.tw 的DNS常常解譯失敗。

/**  
    The example code is used to illustrate the method of updating stock price on Google Spreadsheet
    See the post for details - https://www.lightblue.asia/realtime-tw-stockprice-in-google-spreadsheet
**/

function STOCK_() {
    this.ticker = "";
    this.name = "";
    this.curPrice = 0.0;
    this.high = 0.0;
    this.low = 0.0;
    this.volume = 0;
}

function main() {
  /* Get ActiveSheet */
  var sheet = SpreadsheetApp.getActiveSheet();
  var aryData = sheet.getDataRange().getValues();

  var host = "mis.twse.com.tw";
  //var host = "163.29.17.179";

  /* The TAIEX API is not stable so I need failover */
  var max_retry = 10
  var ua = "Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:47.0) Gecko/20100101 Firefox/47.0"
  
  /* Poke TWSE homepage to get session id, e.g.JSESSIONID=5B050F7AF3A3CD64091F772D7D589A82; Path=/stock */
  var options = {
    "medthod": "get",
    "headers": {
        "User-Agent": ua
     },
    "validateHttpsCertificates": false,
    "followRedirects": false
  };
  var respForSession = UrlFetchApp.fetch("http://"+host+"/stock/index.jsp?lang=zh-tw", options);  
  Logger.log(respForSession.getHeaders());
  
  var server_cookie = respForSession.getHeaders()["Set-Cookie"];
  var cookie = server_cookie.substring(0, server_cookie.indexOf(";"));  
  var headers = {
    "Cookie" : cookie,
    "User-Agent": ua
  }
  var options = {
    "method" : "get",
    "escaping": true,
    "headers" : headers
  };  
  /* Row0 can be used for title of table, so we start read stock from Row1 */  
  var timestamp = Date.now()+1800;
  // stockInfoUrl - http://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=tse_1102.tw&json=1&delay=0&_=1505059448088
  var stockInfoUrl = "http://"+host+"/stock/api/getStockInfo.jsp?json=1&delay=0&_="+timestamp+"&ex_ch="  
  for (var i = 1; i < aryData.length; i++) {
    if (aryData[i][0]) {
      if (i > 1) {
        //stockInfoUrl += "%7C"
        stockInfoUrl += "|"
      }
      stockInfoUrl += "tse_"+aryData[i][0]+".tw"
    }
  } 
  for (var retry = 0; retry < max_retry; retry++) {
    var stocks = getStock(stockInfoUrl, options);
    var updateTime = Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm");
    if (stocks == null || stocks.length == 0) {    
      sheet.getRange("H1").setValue("Fail to sync "+updateTime+" CST");
      continue;
    } else {      
      for (var i = 0; i < stocks.length; i++) {
        var stock = stocks[i]      
        /* getRange() starts from 1 */
        sheet.getRange(i+2, 2).setValue(stock.name);
        sheet.getRange(i+2, 3).setValue(stock.curPrice);
        sheet.getRange(i+2, 4).setValue(stock.volume);
        sheet.getRange(i+2, 5).setValue(stock.high);
        sheet.getRange(i+2, 6).setValue(stock.low);
      }
      sheet.getRange("H1").setValue("Last Update "+updateTime+" CST");
    }    
    return;
  }
}



function getStock(stockInfoUrl, options) {
  var stocks = [];
  var sheet = SpreadsheetApp.getActiveSheet();
  //sheet.getRange("D11").setValue(stockInfoUrl);
  //sheet.getRange("D12").setValue(options);
  try {            
    var response = UrlFetchApp.fetch(stockInfoUrl, options);
    Logger.log(response.getContentText());    
    var jsonData = JSON.parse(response.getContentText());
    if (jsonData.rtmessage === undefined || jsonData.rtmessage !== "OK") {
      Logger.log("Fail to fetch response");
      return null;
    }
    for (var i = 0; i < jsonData.msgArray.length; i++) {
      var stock = new STOCK_()
      var respStock = jsonData.msgArray[i];
      
      stock.ticker   = respStock.c;
      stock.curPrice = respStock.z;
      stock.name     = respStock.n;
      stock.high     = respStock.h;
      stock.low      = respStock.l;
      stock.volume   = respStock.v;
      stocks[i] = stock;
    }
    return stocks;
  } catch (error) {
    Logger.log(error)
    return null;  
  }
}

1. 點選”資源” -> “現有專案的啟動程序” -> “尚未建立觸發程序”，按一下這裡來新增觸發程序依下圖設定選項，以後打開試算表就可以自動把最新股價拉回來了

在Col A輸入上市公司的代號，程式執行的結果如下，可以延伸到其他欄位的資料，也可以處理上下五檔股價。

參考資料

• 台灣證券交易所 – 基本市況報導網站
• 證交所即時資訊API欄位分析
• Taiwan stock Real Time Crawler
• Google Apps Script Development Document