Invoke googlefinance from a script and only run it for a small batch of tickers at a time

Question

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:

1. Call on googlefinance() for only, say, 10 tickers at a time
2. This call would be, for instance, every 5 minutes. So the first 10 tickers at 1PM, the second 10 at 1:05PM, the next 1:10PM, etc.
3. In an extra column K I would note the date when for that ticker the data was retrieved
4. I would only want to retrieve data once per day. I am not interested in intraday changes
5. So some script would work its way down the list, filling the next 10 tickers every 5 minutes. Once it's done, it starts from the top, but if the current date equals the "last retrieved" date from column K, 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

Answer 1

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,

• Official doc
• A tutorial
  

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:

1. That was my error, fixed below.
2. You can edit the cellsToUpdateNum variable below
3. Yes, I also remembered that you only want to get today's prices, included that feature.
4. Yes, it works.

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);
  }
}