Returning array of historical prices from yahoo finance API in apps script

Question

I'm trying to get historical prices for a number of tickers in my google sheets by creating a custom function. The below code seems to run fine in apps script, but it doesn't output anything in my spreadsheet, just giving the error #NUM! The result was not a number. Obviously I need to turn this into an array, but I'm not sure how to do it. Any help would be greatly appreciated.

 /**
 * Fetch historical prices from Yahoo Finance
 * 
 * @param ticker The ticker to fetch historical prices for, e.g. "AAPL"
 * @param range The range to return (e.g. "1y")
 * @param interval e.g. "1d"
 * @param metrics e.g. "Close"
 * @customfunction
 */

function YFHistorical(ticker, range, interval, metrics) {
  var ticker = ticker || "GOOG";
  var range = range || "1y";
  var interval = interval || "1d";
  var metrics = metrics || "close";
  var uri = ("https://query1.finance.yahoo.com/v8/finance/chart/" + ticker + "?metrics=" + metrics + "&interval=" + interval + "&range=" + range);
 
  var response = UrlFetchApp.fetch(uri);
  var json = response.getContentText();
  var data = JSON.parse(json);
  var data2 = data.chart.result[0].indicators.quote;
  return parseFloat(data2);
}

user_14265469 · Answer

As a guess... try to change the lines:

var data2 = data.chart.result[0].indicators.quote;
return parseFloat(data2);

to:

var data2 = data.chart.result[0].indicators.quote[0].high;
return data2;

But actually the object data.chart.result[0].indicators.quote[0] has five 'keys':

{'low':[], 'high':[], 'open':[], 'close':[], 'volume':[]}

Each 'key' contains an array of numbers. It's not clear which of the arrays you want to get. Or do you want to get all the arrays (five columns? five rows?)?

Update

Next guess:

function YFHistorical(ticker, range, interval, metrics) {
  var ticker = ticker || "GOOG";
  var range = range || "1y";
  var interval = interval || "1d";
  var metrics = metrics || "close";
  var uri = ("https://query1.finance.yahoo.com/v8/finance/chart/" + ticker + "?metrics=" + metrics + "&interval=" + interval + "&range=" + range);
 
  var response = UrlFetchApp.fetch(uri);
  var json = response.getContentText();
  var data = JSON.parse(json);
  var high = data.chart.result[0].indicators.quote[0].high;
  var low = data.chart.result[0].indicators.quote[0].low;
  var timestamps = data.chart.result[0].timestamp.map(t => new Date(t * 1000));
  var header = ['Timestamps', 'High', 'Low']
  var table = [header, ...timestamps.map((t, i) => [t, high[i], low[i]])];
  return table;
}

It returns two of the arrays (the keys 'high' and 'low') with timestamps, makes the header, and return it as an array.

Result looks like this:

Returning array of historical prices from yahoo finance API in apps script

1 Answers