I have a Google Sheet in which I'm gathering some stock-related data. I'm using the googlefinance()
function, among others:
Some formula's I use:
// current price
=if($D$1=true,googlefinance(_ticker(A3),"price"),D3)
// 5 year low
=if($D$1=true, min(index(googlefinance(_ticker(A3), "price", date(year(today()) - 5, month(today()), day(today())), today()), 0, 2)),E3)
// 5 year trend
=if($D$1=true, sparkline(googlefinance(_ticker(A3), "price", today()-1825, today(), "weekly"), {"charttype","line";"linewidth",1;"color","#5f88cc"}), J3)
Since the list is over 1'000 tickers, I used the checkbox in D1
to purposefully activate the formula's using googlefinance()
to not get rate limited or have too many Internal Error: xx retuned no result
-errors that googlefinance()
just happend to randomly generate.
This measure is not enough though. I still get many errors when I'm checking D1
, because the sheet is firing somewhat of 1'000 (tickers) x 5 (columns using googlefinance()
) = 5'000 queries.
I was wondering if there is no better way. Ideally, I would:
googlefinance()
for only, say, 10 tickers at a timeK
I would note the date when for that ticker the data was retrievedK
, nothing is done.The problem is that I have no clue on how to do this. In a test, I tried invoking googlefinance()
from a Google Apps script underlaying the sheet, but you can only invoke the function from within a cell directly.
I am without ideas not. Does someone know how to do this?
Sample sheet: https://docs.google.com/spreadsheets/d/1_Tl0LK2hvc3GzBLXUtomI-cAuFdOQCj7HxfdnkrStJg/edit#gid=0
Unfortunately, you can not call sheets function inside apps script. You either have to get data from a web API or set formulas to the columns. However, Google's finance API no longer works. The only option is to scrap this site, https://www.google.com/finance/quote, which is difficult. Though you can also use another API like Alpha Vantage.
I will provide an example in the latter option. To counteract rate limitation, as you said, we will update only ten cells. To do that, we will store an index to a cell, I chose C1, don't forget to enter an initial value manually. I assume it is set to 1. When the last cell is updated, it starts from 1 again.
For the clock, set a time based trigger. You can choose the function to run and how frequent it runs. Here are some helpful resources,
You can customize the code as you like. I hope my answer is useful.
function updateTickerData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// read index
var indexCell = sheet.getRange("C1");
var index = indexCell.getValue();
// calculate range
var startRow = index;
var endRow = startRow + 9;
// get cells
var tickerRange = sheet.getRange("A" + startRow + ":A" + endRow);
var tickerValues = tickerRange.getValues();
var formulaRange = sheet.getRange("B" + startRow + ":B" + endRow);
// set formulas
for (var i = 1; i < tickerValues.length; i++) {
var ticker = tickerValues[i][0];
var formula = "=GOOGLEFINANCE(\"" + ticker + "\")";
formulaRange.getCell(i, 1).setFormula(formula);
}
// update index
indexCell.setValue(startRow + 10);
// if reached end, reset index
if (startRow + 10 > sheet.getLastRow()) {
indexCell.setValue(1);
}
}
Edit:
Code:
//@OnlyCurrentDoc
function upgradeTickerPrices() {
// get sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// read index
var index=null;
index = PropertiesService.getUserProperties().getProperty("index");
// if doesn't exist in properties, initiate
if (index === null) {
index = 1;
}
// convert to int from floating point because we can't have, for example, 5.0 cells
index = index | 0;
// set number of cells to update in each run
var cellsToUpdateNum = 15; // you can customize the number here
// calculate range of cells
var startRow = index;
var endRow = startRow + cellsToUpdateNum - 1;
// get cells
// A is ticker names, B is values, C is ticker update date
var tickerRange = sheet.getRange("A" + startRow + ":A" + endRow);
var tickerValues = tickerRange.getValues();
var formulaRange = sheet.getRange("B" + startRow + ":B" + endRow);
var dateRange = sheet.getRange("C" + startRow + ":C" + endRow);
var dateValues = dateRange.getValues();
// get today's date
var timezoneString = "GMT+3"; //you can format timezone as you like
var dateFormatString = "dd/MM/yyyy"; //you can format date as you like
var today = Utilities.formatDate(new Date(), timezoneString, dateFormatString);
// update ticker prices if conditions match
for (var i = 0; i < tickerValues.length; i++) {
var dateCellValue = dateValues[i][0];
// reminder that google sheets localizes date in the sheets with your locale, if your locale doesn't match dateFormatString errors will probably occur.
dateCellValue = Utilities.formatDate(new Date(dateCellValue), timezoneString, dateFormatString);
var ticker = tickerValues[i][0];
if (ticker!="") { //to not write to empty rows
if (today!=dateCellValue) { //don't update and use quota if we updated the prices today
var formula = "=GOOGLEFINANCE(\"" + ticker + "\")";
formulaRange.getCell(i+1, 1).setFormula(formula); //update ticker prices
dateRange.getCell(i+1,1).setValue(today); //update date
}
}
}
// update index
PropertiesService.getUserProperties().setProperty("index", index+cellsToUpdateNum);
// if reached end, reset index
if (startRow + cellsToUpdateNum > sheet.getLastRow()) {
PropertiesService.getUserProperties().setProperty("index", 1);
}
}