[Google Sheets] Import data from Yahoo Finance

DISCLAIMER: This is not a stock picking recommendation, the tickers shown are used as example and not provided as financial or investment advice.

Google Sheets offers the GOOGLEFINANCE function which can be used to import market data.

Usage is simple, for example to get the price of a stock, first search for it on Google Finance, then look at the quote URL it generates and copy the exchange:ticker value into the function.

In this example we try to get the quote for A200 ETF traded on the Australian ASX exchange. The URL shows ASX:A200 after the quote part, therefore we should use:

=GOOGLEFINANCE("ASX:A200")

So far, so good. Sometimes however Google does not show data for a particular ticker, or it shows data only in another currency or from another exchange. 

For example, another ETF XESC shows quotes from many exchanges but is also traded on the German XETRA (XESC.DE), which is not sourced by Google. Or the CHSPI ETF traded on the Swiss Six exchange (CHSPI.SW) is simply not found at all.

In this case, we can use a different provider to retrieve the information we are looking for.

To import data from Yahoo Finance we first need to add a custom function to be used in our Google Sheet. Give it a name, then insert the following code and save it:

function getYahooFinanceData(path) {
  var result = UrlFetchApp.fetch("https://query1.finance.yahoo.com/v8/finance/chart/" + path);
  var response = result.getContentText();
  var json = JSON.parse(response);
  return json.chart.result[0].meta.regularMarketPrice;
}

Now we search for our tickers in Yahoo Finance, in our two examples XESC.DE and CHSPI.SW which show up correctly.

Finally we can use the function in Google Sheets as:

=getYahooFinanceData("XESC.DE")