Google Sheets Yahoo Finance

GOOGLEFINANCE from time to time is broken 🤷‍♂️

Did not found any good and free service to get historic data except the Yahoo finance which seems to be used so widely

Here is my attempt to move it into Google Sheets so it can be used out of the box

Here is how it looks like

Note that even in this example - somehow GOOGLEFINANCE does not give us friday numbers 🤔 Where as YAHOOFINANCE workds great 🎉

Here is the code for function

/**
 * Fetches current or historical securities information from Yahoo Finance.
 * @param {string} ticker The ticker symbol for the security to consider.
 * @param {Date} start The start date when fetching historical data.
 * @param {Date} end The end date when fetching historical data.
 * @customfunction
*/
function YAHOOFINANCE(ticker, start, end) {
  var now = new Date()

  if (start) {
    start = new Date(start).setHours(0, 0, 0, 0)
  } else { // if no start date use start of current day
    start = new Date().setHours(0, 0, 0, 0)
  }

  if (end) { // edge case at morning we need to use current time rather than end of day
    end = new Date(end).setHours(23,59,59,0) > now.getTime() ? now.getTime() : new Date(end).setHours(23,59,59,0)
  } else {
    end = now.getTime()
  }

  start = Math.ceil(start / 1000)
  end = Math.ceil(end / 1000)

  var url = "https://query1.finance.yahoo.com/v8/finance/chart/"+ticker+"?period1="+start+"&period2="+end+"&interval=1d"

  var cache = CacheService.getDocumentCache()
  var txt = cache.get(url)
  if (!txt) { // just in case we are going to cache results for 5min
    var res = UrlFetchApp.fetch(url, { muteHttpExceptions: true })
    txt = res.getContentText()
    if (res.getResponseCode() === 200) {
      cache.put(url, txt, 5*60)
    }
  }

  var json = JSON.parse(txt)
  if (json?.chart?.error?.description) {
    throw new Error(`${json?.chart?.error?.description} ${url}`)
  }

  var data = json.chart.result[0]

  var rows = [["Date", "Close", "Open", "High", "Low", "Volume"]]
  for(var i = 0; i < data.timestamp.length; i++) {
    rows.push([
      new Date(data.timestamp[i]*1000),
      data.indicators.quote[0].open[i],
      data.indicators.quote[0].high[i],
      data.indicators.quote[0].low[i],
      data.indicators.quote[0].close[i],
      data.indicators.quote[0].volume[i],
    ])
  }

  return rows
}

To add it to your spread sheet go to Extensions \ App Scripts, paste the code, at upper right corner choose deploy.

And if everything is done correct, in your sheets you should see your brand new function

Here is an example