How to import Yahoo Finance data into Google Sheets

Photo by homajob on Unsplash

Google Sheets has been my spreadsheet app of choice for tracking my budget and the performance of the stocks in my portfolio. Google sheets include the GOOGLEFINANCE formula that enables you to track the prices of stocks in an automated way, without the need for manual data entry.

For instance, you can get the price of Apple’s stock like so: =GOOGLEFINANCE("AAPL")

Unfortunately, the GOOGLEFINANCE formula does not return valid data for some tickers. It appears that the underlying data set for the GOOGLEFINANCE formula is not a comprehensive one. The formula is missing data for tickers like ES3 (SPDR Straits Times Index ETF) and CPI:L (Capita PLC), to name a few.

Yahoo Finance contains a much more comprehensive data set for stock tickers. And we can use a Google Apps script to import data from Yahoo Finance into a Google sheet.

First, let’s create a new Google Apps script. In your Google sheet, navigate to Extensions -> App Scripts in the toolbar:

This should bring you to a code editor for Apps Scripts:

Google Apps Scripts allows you to define Javascript functions which you can use inside your Google sheet. We can test this out by logging a string to the console:

function myFunction() {
  console.log('Hello')
}

Save your script and click on Run . You should see the logged string in your execution log:

Now let us define a yahooF function that will allow us to pull data from Yahoo Finance:

function yahooF() {
  const ticker = 'AAPL';
  const url = `https://finance.yahoo.com/quote/${ticker}`;
  const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  const contentText = res.getContentText();
  const price = contentText.match(/<span(?:.*?)data-testid="qsp-price">(\d+[,]?[\d\.]+?\s?)<\/span>/);
  console.log(price[1]);