本文說明如何把即時股價記錄到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" }
詳細步驟
- 打開一張新的Google試算表,點選”工具” -> “指令碼編輯器”
-
把下面的程式碼貼到編輯框中按”儲存”
這個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; } }
- 點選”資源” -> “現有專案的啟動程序” -> “尚未建立觸發程序”,按一下這裡來新增觸發程序依下圖設定選項,以後打開試算表就可以自動把最新股價拉回來了
在Col A輸入上市公司的代號,程式執行的結果如下,可以延伸到其他欄位的資料,也可以處理上下五檔股價。
參考資料
- 台灣證券交易所 – 基本市況報導網站
- 證交所即時資訊API欄位分析
- Taiwan stock Real Time Crawler
- Google Apps Script Development Document