筆者使用 Google Spredsheet 抓 TXF 台指未平倉量與台指次月契約開倉成本 的資料來進行畫圖。
在Google雲端硬碟中建立一個專案，新增Google Apps Script檔案Code.gs，輸入以下程式碼。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function doGet() { | |
| return HtmlService.createTemplateFromFile("index.html") | |
| .evaluate() | |
| .setSandboxMode(HtmlService.SandboxMode.IFRAME); | |
| } | |
| function include(filename) { | |
| return HtmlService.createHtmlOutputFromFile(filename) | |
| .setSandboxMode(HtmlService.SandboxMode.IFRAME) | |
| .getContent(); | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?!= include('getGraphicData'); ?> | |
| <div id="dashboard_div" style="border: 1px"> | |
| <div id="chart1_div" style="height: 290px;"></div><br/> | |
| <div id="chart2_div" style="height: 180px;"></div><br/> | |
| <div id="chart3_div" style="height: 180px;"></div><br/> | |
| <div id="control_div" style="height: 50px;"></div><br/> | |
| </div> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <script src="https://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script> | |
| <script type="text/javascript" src="https://www.google.com/jsapi"></script> | |
| <script type="text/javascript"> | |
| google.load('visualization', '1', {packages: ['corechart', 'controls']}); | |
| google.setOnLoadCallback(drawDashboard); | |
| function drawDashboard() { | |
| var query = new google.visualization.Query('你的Google Sheet的共用URL'); | |
| query.send(handleQueryResponse); | |
| } | |
| function handleQueryResponse(response) { | |
| var dataTable = new google.visualization.DataTable(); | |
| dataTable.addColumn('date', "日期"); //日期 | |
| dataTable.addColumn('number'); //開盤價 | |
| dataTable.addColumn('number'); //最高價 | |
| dataTable.addColumn('number'); //最低價 | |
| dataTable.addColumn('number'); //收盤價 | |
| dataTable.addColumn('number', '成交量'); | |
| dataTable.addColumn('number', '未平倉量'); | |
| var symbol, trade, low, open, close, high, vol, OI, day; | |
| var data = response.getDataTable(); | |
| var rows = data.getNumberOfRows(); | |
| for (var i = 1; i < rows; i++) | |
| { | |
| trade = new Date(data.getValue(i, 0)); | |
| low = parseFloat(data.getValue(i, 3)); | |
| open = parseFloat(data.getValue(i, 1)); | |
| close = parseFloat(data.getValue(i, 4)); | |
| high = parseFloat(data.getValue(i, 2)); | |
| vol = parseInt(data.getValue(i, 6)); | |
| OI = parseInt(data.getValue(i, 7)); | |
| dataTable.addRow([trade, low, open, close, high, vol, OI]); | |
| } | |
| symbol = data.getValue(0, 24).substring(0, 6); | |
| var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div')); | |
| var CandlestickChart = new google.visualization.ChartWrapper({ | |
| chartType: 'CandlestickChart', | |
| containerId: 'chart1_div', | |
| options: { | |
| chartArea: {'height': '80%', 'width': '90%'}, | |
| title: symbol + '月 台指契約', | |
| legend: {position: 'none'}, | |
| hAxis: {'slantedText': false}, | |
| candlestick: { | |
| fallingColor: { stroke : '#0f9d58', strokeWidth: 0, fill: '#0f9d58' }, // green | |
| risingColor: { stroke : '#a52714', strokeWidth: 0, fill: '#a52714' } // red | |
| } | |
| }, | |
| 'view': { | |
| 'columns': [ | |
| { | |
| 'calc': function(dataTable1, rowIndex) { | |
| return dataTable1.getFormattedValue(rowIndex, 0); | |
| }, | |
| 'type': 'string' | |
| }, 1, 2, 3, 4] | |
| } | |
| }); | |
| var columnVolChart = new google.visualization.ChartWrapper({ | |
| 'chartType': 'ColumnChart', | |
| 'containerId': 'chart2_div', | |
| 'options': { | |
| chartArea: {'height': '80%', 'width': '90%'}, | |
| title: symbol + '月 契約成交量', | |
| legend: {position: 'none'}, | |
| hAxis: {'slantedText': false}, | |
| colors: ['rgb(153, 102, 255)'] | |
| }, | |
| 'view': { | |
| 'columns': [ | |
| { | |
| 'calc': function(dataTable1, rowIndex) { | |
| return dataTable1.getFormattedValue(rowIndex, 0); | |
| }, | |
| 'type': 'string' | |
| }, 5] | |
| } | |
| }); | |
| var columnOIChart = new google.visualization.ChartWrapper({ | |
| 'chartType': 'ColumnChart', | |
| 'containerId': 'chart3_div', | |
| 'options': { | |
| chartArea: {'height': '80%', 'width': '90%'}, | |
| title: symbol + '月 契約未平倉量', | |
| legend: {position: 'none'}, | |
| hAxis: {'slantedText': false}, | |
| colors: ['rgb(255, 206, 86)'] | |
| }, | |
| 'view': { | |
| 'columns': [ | |
| { | |
| 'calc': function(dataTable1, rowIndex) { | |
| return dataTable1.getFormattedValue(rowIndex, 0); | |
| }, | |
| 'type': 'string' | |
| }, 6] | |
| } | |
| }); | |
| var control = new google.visualization.ControlWrapper({ | |
| 'controlType': 'ChartRangeFilter', | |
| 'containerId': 'control_div', | |
| 'options': { | |
| 'filterColumnIndex': 0, | |
| 'ui': { | |
| 'chartType': 'LineChart', | |
| 'chartView': { | |
| 'columns': [0, 4] | |
| }, | |
| 'chartOptions': { | |
| 'chartArea': {'width': '90%'}, | |
| 'hAxis': {'baselineColor': 'none'} | |
| }, | |
| 'minRangeSize': 86400000 | |
| } | |
| }, | |
| 'state': {'range': {'start': new Date(data.getValue(rows-15, 0)), 'end': new Date(data.getValue(rows-1, 0))}} | |
| }); | |
| dashboard.bind(control, [CandlestickChart, columnVolChart, columnOIChart]); | |
| dashboard.draw(dataTable); | |
| } | |
| </script> |
移動圖片中最下面的顯示範圍後。
範例網頁連結。
參考資料：
留言版