Last active
August 15, 2023 09:08
-
-
Save MEN8v/593eb6d38e4983bca6ea389a8aae9913 to your computer and use it in GitHub Desktop.
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
//***GLOBALS***// | |
var ss = SpreadsheetApp.getActive(); | |
var portfolioSheet = ss.getSheetByName("master"); //replace master with name of the tab that has your portfolio data | |
var columnToCheck = portfolioSheet.getRange("A:A").getValues(); | |
// Get the last row based on the data range of a single column. | |
var tickerCount = getLastRowSpecial(columnToCheck); | |
/************************************************************************ | |
* | |
* Gets the last row number based on a selected column range values | |
* | |
* @param {array} range : takes a 2d array of a single column's values | |
* | |
* @returns {number} : the last row number with a value. | |
* | |
*/ | |
function getLastRowSpecial(range){ | |
var rowNum = 0; | |
var blank = false; | |
for(var row = 0; row < range.length; row++){ | |
if(range[row][0] === "" && !blank){ | |
rowNum = row; | |
blank = true; | |
}else if(range[row][0] !== ""){ | |
blank = false; | |
}; | |
}; | |
return rowNum; | |
} | |
function updatePortfolio () { | |
"use strict"; | |
var sparkCol, sparkRange,sparkHeader,peCol, peRange, peHeader, fwdPECol, fwdPERange, fwdPEHeader, payoutRatioCol, payoutRatioRange, payoutRatioHeader, betaCol, betaRange, betaHeader, mktCapCol, mktCapRange, mktCapHeader; | |
//set variables for columns of portfolio data | |
sparkCol = 7; | |
peCol = 8; | |
fwdPECol = 9; | |
payoutRatioCol = 26; | |
betaCol = 34; | |
mktCapCol = 36; | |
//set variables for portfolio data ranges | |
sparkRange = portfolioSheet.getRange(2, sparkCol, tickerCount - 1, 1); | |
peRange = portfolioSheet.getRange(2, peCol, tickerCount - 1, 1); | |
fwdPERange = portfolioSheet.getRange(2, fwdPECol, tickerCount - 1, 1); | |
payoutRatioRange = portfolioSheet.getRange(2, payoutRatioCol, tickerCount - 1, 1); | |
betaRange = portfolioSheet.getRange(2, betaCol, tickerCount - 1, 1); | |
mktCapRange = portfolioSheet.getRange(2, mktCapCol, tickerCount - 1, 1); | |
//set variables for headers to update with timestamp | |
sparkHeader = portfolioSheet.getRange("G1"); | |
peHeader = portfolioSheet.getRange("H1"); | |
fwdPEHeader = portfolioSheet.getRange("I1"); | |
payoutRatioHeader = portfolioSheet.getRange("Z1"); | |
betaHeader = portfolioSheet.getRange("AH1"); | |
mktCapHeader = portfolioSheet.getRange("AJ1"); | |
//insert formulas into the ranges to get current portfolio data | |
sparkRange.setFormulaR1C1('=IFERROR(SPARKLINE(GOOGLEFINANCE(R[0]C[-5],\"price\",TODAY()-365,TODAY(),\"weekly\"), {\"charttype\",\"line\";\"linewidth\",3;\"color\",IF(GOOGLEFINANCE(R[0]C[-5],\"price\")<INDEX(GOOGLEFINANCE(R[0]C[-5],\"price\",TODAY()-365),2,2),\"red\",\"green\")}),\"\")'); | |
peRange.setFormulaR1C1('=GOOGLEFINANCE(R[0]C[-6], "pe")'); | |
fwdPERange.setFormulaR1C1('=ABS(REGEXEXTRACT(index(IMPORTHTML(\"http://finviz.com/quote.ashx?t=\"&R[0]C[-7], \"table\", 11), 2, 4), \"/*[0-9.]+/*\"))'); | |
payoutRatioRange.setFormulaR1C1('=ABS(REGEXEXTRACT(INDEX(IMPORTHTML(\"http://finviz.com/quote.ashx?t=\"&R[0]C[-24], \"table\", 11), 11, 8), \"/*[0-9.]+/*\"))/100'); | |
betaRange.setFormulaR1C1('=GOOGLEFINANCE(R[0]C[-33], "beta")'); | |
mktCapRange.setFormulaR1C1('=GOOGLEFINANCE(R[0]C[-35], "marketcap")/1000000000'); | |
//force the spreadsheet to update and sleep while data is fetched | |
SpreadsheetApp.flush(); | |
Utilities.sleep(7500); //30 second delay | |
//replace formulas with values | |
sparkRange.copyTo(sparkRange, {contentsOnly:true}); | |
peRange.copyTo(peRange, {contentsOnly:true}); | |
fwdPERange.copyTo(fwdPERange, {contentsOnly:true}); | |
payoutRatioRange.copyTo(payoutRatioRange, {contentsOnly:true}); | |
betaRange.copyTo(betaRange, {contentsOnly:true}); | |
mktCapRange.copyTo(mktCapRange, {contentsOnly:true}); | |
//update headers to reflect timestamp of last update | |
sparkHeader.setValue("Last 365 Days\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
peHeader.setValue("PE (TTM)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
fwdPEHeader.setValue("FWD P/E (Finviz)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
payoutRatioHeader.setValue("Payout Ratio\n(< 70%)\n(Finviz)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
betaHeader.setValue("Beta\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
mktCapHeader.setValue("Market Cap ($B)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
updateDivAmts(); | |
} | |
function updateDivAmts () { | |
"use strict"; | |
var divAmtCol, divAmtRange, divAmtHeader, oldDivAmts, newDivAmts; | |
//set variables for dividend amounts | |
divAmtCol = 40; | |
divAmtRange = portfolioSheet.getRange(2, divAmtCol, tickerCount - 1, 1); | |
divAmtHeader = portfolioSheet.getRange("AN1"); | |
oldDivAmts = divAmtRange.getValues(); | |
//insert formulas to get current dividend amounts | |
divAmtRange.setFormulaR1C1('=IF(ISBLANK(R[0]C[-23]),\"-\", IFERROR(IMPORTDATA(CONCATENATE(t_data,R[0]C[-38],div_amt,t_token)),\"#N/A\"))'); //sandbox | |
//divAmtRange.setFormulaR1C1('=IF(ISBLANK(R[0]C[-23]),\"-\", IFERROR(IMPORTDATA(CONCATENATE(iex_data,R[0]C[-38],div_amt,iex_token)),\"#N/A\"))'); //production | |
SpreadsheetApp.flush(); | |
Utilities.sleep(7500); | |
// load current values from dividend amount range into an array and replace any errors with old data | |
newDivAmts = divAmtRange.getValues(); | |
for (var i = 0; i<newDivAmts.length; i++) { | |
if (newDivAmts[i] == "#N/A") { | |
newDivAmts[i] = oldDivAmts[i]; | |
} | |
} | |
portfolioSheet.getRange(2, divAmtCol, tickerCount -1, 1).setValues(newDivAmts); | |
divAmtHeader.setValue("Dividend Amount (IEX)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
updateExDate(); | |
} | |
/** | |
* make IEX call for Ex-Dividend Date and then replace IEX call with fetched results or old values for any IEX failures | |
*/ | |
function updateExDate() { | |
"use strict"; | |
var exDateCol, exDateRange, exDateHeader, oldExDates, newExDates; | |
//set variables for ex-dividend date | |
exDateCol = 44; | |
exDateRange = portfolioSheet.getRange(2, exDateCol, tickerCount - 1, 1); | |
exDateHeader = portfolioSheet.getRange("AR1"); | |
oldExDates = exDateRange.getValues(); | |
//since ex-dividend date is in column 44 and payout frequency is in column 17, the payout frequency column is 27 rows before the ex-dividend date column or C[-27] | |
//this formula first checks for a blank value in payout frequency and if there is one it does not fetch ex-dividend date from IEX for that ticker (row) | |
exDateRange.setFormulaR1C1('=IF(ISBLANK(R[0]C[-27]),\"N/A\", IFERROR(IMPORTDATA(CONCATENATE(t_data,R[0]C[-42],ex_date,t_token)),\"#N/A\"))'); //sandbox | |
//exDateRange.setFormulaR1C1('=IF(ISBLANK(R[0]C[-27]),\"N/A\", IFERROR(IMPORTDATA(CONCATENATE(iex_data,R[0]C[-42],ex_date,iex_token)),\"#N/A\"))'); //production | |
SpreadsheetApp.flush(); | |
Utilities.sleep(7500); | |
var newExDates = exDateRange.getValues(); | |
for (var i = 0; i<newExDates.length; i++) { | |
if (newExDates[i] == "#N/A") { | |
newExDates[i] = oldExDates[i]; | |
} | |
} | |
portfolioSheet.getRange(2, exDateCol, tickerCount -1, 1).setValues(newExDates); | |
exDateHeader.setValue("Ex-Dividend Date (IEX)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
updatePayDate(); | |
} | |
function updatePayDate() { | |
"use strict"; | |
var payDateCol, payDateRange, payDateHeader, oldPayDates, newPayDates; | |
//set payout date variables | |
payDateCol = 42; | |
payDateRange = portfolioSheet.getRange(2, payDateCol, tickerCount - 1, 1); | |
payDateHeader = portfolioSheet.getRange("AP1"); | |
oldPayDates = payDateRange.getValues(); | |
payDateRange.setFormulaR1C1('=IF(ISBLANK(R[0]C[-25]),\"N/A\", IFERROR(IMPORTDATA(CONCATENATE(t_data,R[0]C[-40],pay_date,t_token)),\"#N/A\"))'); //sandbox | |
//payDateRange.setFormulaR1C1('=IF(ISBLANK(R[0]C[-25]),\"N/A\", IFERROR(IMPORTDATA(CONCATENATE(iex_data,R[0]C[-40],pay_date,iex_token)),\"#N/A\"))'); //production | |
SpreadsheetApp.flush(); | |
Utilities.sleep(7500); | |
newPayDates = payDateRange.getValues(); | |
for (var i = 0; i<newPayDates.length; i++) { | |
if (newPayDates[i] == "#N/A") { | |
newPayDates[i] = oldPayDates[i]; | |
} | |
} | |
portfolioSheet.getRange(2, payDateCol, tickerCount -1, 1).setValues(newPayDates); | |
payDateHeader.setValue("Payout Date (IEX)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment