I wrote a short GScript for people tracking their portfolio using Google Sheets and the GoogleFinance function. If you write this as a project in GSuite, and create a trigger that runs every minute, this script will automatically fill in the daily low, open, close, and high, and the table is filled in to take advantage of the built-in Google Sheets candlestick chart functionality. https://i.imgur.com/NQMDNBr.png is what it'll look like after about a week of running (just flip the columns if you want the chart to go left –> right). Let me know if you have any questions

function portfolio_change() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet1 = spreadsheet.getSheetByName("Portfolio History"); // Where portfolio data is located var sheet2 = spreadsheet.getSheetByName("Tracker"); // The current spreadsheet var datarange = sheet1.getDataRange(); var numRows = datarange.getNumRows(); var d = new Date(); var a = Number(sheet1.getRange(numRows,6).getValue()); // Total investment profit (change location as needed) var b = Number(sheet1.getRange(numRows,4).getValue()); // Total investment value (change location as needed) if (d.getDay() > 0 && d.getDay() < 6) { // Disclude weekends if(d.getMinutes() == 0 && d.getHours() == 10){ // Value at 10am market open sheet2.insertRowsBefore(2,1); sheet2.getRange(2,1).setValue(date2str(d, 'MM-dd')); // This can be set to YYYY-MM-dd etc., see the regex code below sheet2.getRange(2,3).setValue(a); // Open price sheet2.getRange(2,2).setValue(a); sheet2.getRange(2,5).setValue(a); } if(d.getHours() >= 10 && d.getHours() <= 17){ sheet2.getRange(2,8).setValue(Number(a)); // These three lines go around a bug in GScripts that fail to execute nested if statements sheet2.getRange(2,9).setValue(Number(sheet2.getRange(2,5).getValue())); sheet2.getRange(2,10).setValue(Number(sheet2.getRange(2,2).getValue())); if(a >= Number(sheet2.getRange(2,5).getValue())){sheet2.getRange(2,5).setValue(a);} // Updates daily high if(a <= Number(sheet2.getRange(2,2).getValue())){sheet2.getRange(2,2).setValue(a);} // Updates daily low } if(d.getMinutes() == 0 && d.getHours() == 18){ // Value at 10am market open sheet2.getRange(2,4).setValue(a); // Close price sheet2.getRange(2,6).setValue(percentage(sheet2.getRange(2,4).getValue()-sheet2.getRange(3,4).getValue(),b)+"%"); // Previous day close to current close price sheet2.getRange(2,7).setValue(percentage(sheet2.getRange(2,5).getValue()-sheet2.getRange(2,2).getValue(),b)+"%"); // High to low daily value sheet2.getRange(2,8).setValue(''); // Clear those extra values sheet2.getRange(2,9).setValue(''); sheet2.getRange(2,10).setValue(''); } } } function percentage(partialValue, totalValue) { return (100 * partialValue) / totalValue; } function date2str(x, y) { var z = { M: x.getMonth() + 1, d: x.getDate(), h: x.getHours(), m: x.getMinutes(), s: x.getSeconds() }; y = y.replace(/(M+|d+|h+|m+|s+)/g, function(v) { return ((v.length > 1 ? "0" : "") + eval('z.' + v.slice(-1))).slice(-2) }); return y.replace(/(y+)/g, function(v) { return x.getFullYear().toString().slice(-v.length) }); } 

submitted by /u/WuxiBoy
[comments]



Source link

قالب وردپرس

LEAVE A REPLY

Please enter your comment!
Please enter your name here