Skip to content

Instantly share code, notes, and snippets.

@MEN8v

MEN8v/iex_msg_ctrl.gs

Last active May 31, 2020 00:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save MEN8v/440ce2a50ca3f4348eb55692f8b1fdbc to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save MEN8v/440ce2a50ca3f4348eb55692f8b1fdbc to your computer and use it in GitHub Desktop.
Download ZIP
Creates custom google sheet menu to pull dividend info from IEX and then replace the formulas with the fetched values
Raw
iex_msg_ctrl.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 lastRow = getLastRowSpecial(columnToCheck);
/**
* Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){
try{
// /rates/EUR
var res = UrlFetchApp.fetch(url);
var content = res.getContentText();
var json = JSON.parse(content);
var patharray = xpath.split("/");
//Logger.log(patharray);
for(var i=0;i<patharray.length;i++){
json = json[patharray[i]];
}
//Logger.log(typeof(json));
if(typeof(json) === "undefined"){
return "Node Not Available";
} else if(typeof(json) === "object"){
var tempArr = [];
for(var obj in json){
tempArr.push([obj,json[obj]]);
}
return tempArr;
} else if(typeof(json) !== "object") {
return json;
}
}
catch(err){
return "Error getting data";
}
}
/**
* A special function that runs when the spreadsheet is open, used to add a
* custom menu to the spreadsheet.
*/
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'a1 - Fetch Dividend Amount (IEX)', functionName: 'fetchDivAmt'},
{name: 'a2 - Replace Div Amount IEX Call', functionName: 'replaceDivAmt'},
{name: 'b1 - Fetch Ex-Dividend Date (IEX)', functionName: 'fetchExDate'},
{name: 'b2 - Replace Ex Date IEX Call', functionName: 'replaceExDate'},
{name: 'c1 - Fetch Dividend Payout Date (IEX)', functionName: 'fetchPayDate'},
{name: 'c2 - Replace Payout Date IEX Call', functionName: 'replacePayDate'},
{name: 'd1 - Fetch Year Sparkline (Google)', functionName: 'fetchYrSpark'},
{name: 'd2 - Replace Year Sparkline Call', functionName: 'replaceYrSpark'},
{name: 'e1 - Fetch Fwd P/E (Finviz)', functionName: 'fetchFwdPE'},
{name: 'e2 - Replace Fwd P/E Call', functionName: 'replaceFwdPE'}
];
spreadsheet.addMenu('Dividend Tools', menuItems);
}
/**
* make IEX call for Dividend Amount
*/
function fetchDivAmt() {
var cell = portfolioSheet.getRange("AO1");
var divAmtCol = 41; //replace with column number that holds dividend amount
portfolioSheet.getRange(2, divAmtCol, lastRow - 1, 1).setFormulaR1C1('=IF(ISBLANK(R[0]C[-17]),\"-\", IMPORTDATA(CONCATENATE(iex_data,R[0]C[-39],div_amt,iex_token)))'); //production
//portfolioSheet.getRange(2, divAmtCol, lastRow - 1, 1).setFormulaR1C1('=IMPORTDATA(CONCATENATE(t_data,R[0]C[-39],div_amt,t_token))'); //sandbox
cell.setValue(("Dividend Amount (IEX)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss")));
SpreadsheetApp.flush();
}
/**
* make IEX call for Ex-Dividend Date
*/
function fetchExDate() {
var cell = portfolioSheet.getRange("AS1");
var exDateCol = 45;
portfolioSheet.getRange(2, exDateCol, lastRow - 1, 1).setFormulaR1C1('=IF(ISBLANK(R[0]C[-21]),\"-\",IMPORTDATA(CONCATENATE(iex_data,R[0]C[-43],ex_date,iex_token)))'); //production
//portfolioSheet.getRange(2, exDateCol, lastRow - 1, 1).setFormulaR1C1('=IMPORTDATA(CONCATENATE(t_data,R[0]C[-7],ex_date,t_token))'); //sandbox
cell.setValue("Ex-Dividend Date (IEX)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
SpreadsheetApp.flush();
}
/**
* make IEX call for Dividend Payout Date
*/
function fetchPayDate() {
var cell = portfolioSheet.getRange("AQ1");
var payDateCol = 43;
portfolioSheet.getRange(2, payDateCol, lastRow - 1, 1).setFormulaR1C1('=IF(ISBLANK(R[0]C[-19]),\"-\",IMPORTDATA(CONCATENATE(iex_data,R[0]C[-41],pay_date,iex_token)))'); //production
//portfolioSheet.getRange(2, payDateCol, lastRow - 1, 1).setFormulaR1C1('=IMPORTDATA(CONCATENATE(t_data,R[0]C[-8],pay_date,t_token))'); //sandbox
cell.setValue("Payout Date (IEX)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
SpreadsheetApp.flush();
}
/**
* make Google Finance call for price to build sparkline
*/
function fetchYrSpark() {
var sparkCol = 9; //change this to match the column # that contains your Sparkline
//=SPARKLINE(GOOGLEFINANCE($A2,"price",TODAY()-365,TODAY(),"weekly"),{"charttype","line";"linewidth",3;"color",IF(GOOGLEFINANCE($A2,"price")<INDEX(GOOGLEFINANCE($A2,"price",TODAY()-365),2,2),"red","green")})
var sparkFormula = '=SPARKLINE(GOOGLEFINANCE(R[0]C[-7],\"price\",TODAY()-365,TODAY(),\"weekly\"), {\"charttype\",\"line\";\"linewidth\",3;\"color\",IF(GOOGLEFINANCE(R[0]C[-7],\"price\")<INDEX(GOOGLEFINANCE(R[0]C[-7],\"price\",TODAY()-365),2,2),\"red\",\"green\")})';
portfolioSheet.getRange(2, sparkCol, lastRow - 1, 1).setFormulaR1C1(sparkFormula);
SpreadsheetApp.flush();
}
/**
* make finviz call for forward pe
*/
function fetchFwdPE() {
var cell = portfolioSheet.getRange("AU1");
var fwdPECol = 47;
portfolioSheet.getRange(2, fwdPECol, lastRow - 1, 1).setFormulaR1C1('=ABS(REGEXEXTRACT(index(IMPORTHTML(\"http://finviz.com/quote.ashx?t=\"&R[0]C[-45], \"table\", 11), 2, 4), \"/*[0-9.]+/*\"))');
cell.setValue("FWD P/E (Finviz)\n" + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yyyy HH:mm:ss"));
}
/**
*Replace Div Amount IEX call
*/
function replaceDivAmt(){
var range = portfolioSheet.getRange("AO2:AO"); //replace with A1 column that holds dividend amount on your portfolio sheet
range.copyTo(range, {contentsOnly:true});
}
/**
*Replace Ex Date IEX call
*/
function replaceExDate(){
var range = portfolioSheet.getRange("AS2:AS"); //replace with A1 column that holds ex date on your portfolio sheet
range.copyTo(range, {contentsOnly:true});
}
/**
*Replace Pay Date IEX call
*/
function replacePayDate(){
var range = portfolioSheet.getRange("AQ2:AQ"); //replace with A1 column that holds dividend payout date on your portfolio sheet
range.copyTo(range, {contentsOnly:true});
}
/**
*Replace Sparkline call
*/
function replaceYrSpark(){
var range = portfolioSheet.getRange("I2:I"); //replace with column letter that holds your Sparkline formula
range.copyTo(range, {contentsOnly:true});
}
/**
*Replace FWD P/E call
*/
function replaceFwdPE(){
var range = portfolioSheet.getRange("AU2:AU"); //replace with column letter that holds your fwd p/e formula
range.copyTo(range, {contentsOnly:true});
}
/************************************************************************
*
* 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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment