genex_test.gs

//***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"));
}