How To Import Yahoo Finance Data Into Google Sheets

GOOGLEFINANCE function in Google Sheets allows you to track budget and stock performance. It helps you track the prices of stocks automatically, without having to copy and paste data from external sources.

As useful as GOOGLEFINANCE is, it fails to return valid data for specific tickers, such as ETF, ES3, or CPI:L. The YAHOOFINANCE formula extends the functionality of GOOGLEFINANCE as it offers a much more comprehensive data set for stock tickers. You can easily pull Yahoo Finance data into your Google Sheets automatically using the Google Apps Script.

In this article, you’ll learn how to import Yahoo Finance data into your Google Sheets through the Google Apps Script feature, and then use the YahooF Apps Script function to add the necessary tickers.

How to import Yahoo Finance data into Google Sheets?

If you want to track budget and stock performance without having to copy and paste data, you can use GOOGLEFINANCE. To complete your dataset by accessing ticker prices that aren’t available in Google Finance, you’ll first need to create a function in the Google Apps Script.

How to create a function in Google Apps Script?

Google Apps Script is a feature where you can define JavaScript functions inside your Google Sheets. Think of it as the equivalent of Visual Basic for Applications (VBA) in Excel. This is how to create a function in Google Apps Script.

1. 1. Open the Google Sheets where you’d like to import data and go to Extensions > App Script.

1. 2. Google might prompt you to “Request Access” if you are logged in with a different account to where your Google Sheets is saved. Click on the account you are currently signed in as.

1. 3. Select the right account to proceed.

1. 4. The code editor for Apps Scripts should automatically open in a new tab in your browser. To test how the functions work in the Apps Script editor, paste the following code into the editor, as shown below:

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

1. 5. Click on the “Save” button at the top, in the main toolbar.

1. 6. Click on the “Run” button, to the right of “Save”. As soon as Apps Script executes the function, you should see the string ‘Hello’ appear in the “Execution log”.

Now that you’ve seen how the Apps Script code editor works, you can create a function to import Yahoo Finance Data into your Google Sheets.

How to use the YahooF function in Google Apps Script?

The function used to pull Yahoo Finance Data is already defined. You simply need to specify the ticker you’re interested in. In this example, you’ll see how this function returns the ticker price for Apple, Inc.

1. 1. Repeat steps 1-4 from the previous section.
2. 2. Paste the following script in the Apps Script code editor.

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

1. 3. Click on “Save”.

1. 4. As soon as you click on “Run” to execute the function, you might see a popup window saying that “This project requires your permission to access your data”. If so, click on “Review permissions”.

1. 5. Choose the account from where you’d like to grant access.

1. 6. Click on “Allow” to start executing the function.

1. 7. In your “Execution log” at the bottom, you should now see the ticker price logged. Here, “137.51”.

You can pull data on Yahoo Finance on any ticker. You simply need to specify in the const ticker = line of the script.

How to specify a ticker name in YahooF function in Google Apps Script?

So, “How do I get Yahoo Finance data into Google Sheets?” To use this function in your Google Sheets, you need to specify a ticker name.

1. 1. Repeat steps 1-4 from the first section.
2. 2. Include the following script in your Apps Script code editor and click “Save”.

function yahooF(ticker) {
  const url = `https://finance.yahoo.com/quot...;{ticker}?p=${ticker}`;
  const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  const contentText = res.getContentText();
  const price = contentText.match(/<fin-streamer(?:.*?)data-test="qsp-price"(?:.*)>(\d+\.\d+)<\/fin-streamer>/);
  console.log(price[1]);
  return price[1];
}

1. 3. Return to your Google Sheets, and type in the formula =yahooF("<ticker name>") including the ticker symbol between double quotation marks, as shown below. Press “Enter” to apply the formula.

1. 4. Google Sheets has now returned the ticker price you included.

And, that’s how easy it is to import Yahoo Finance data into your Google Sheets!

How to manage your Google Sheets with Layer?

Layer adds productivity features to your Google Sheets. Share parts of your spreadsheet, request input, and accept or reject changes to make collaboration seamless and more efficient while keeping full control over your data.

Using Layer, you can:

• Manage Access: Give spreadsheet access to relevant stakeholders on a tab or cell level.
• Review & Track: Consolidate input, and easily track changes.
• Collaborate: Define, assign, and automate tasks and set deadlines.

Sign up for early access and start automating your Google Sheets workflows with Layer!

Conclusion

Using Google Sheets for financial analysis has become a more common practice since it included its own GOOGLEFINANCE formula to import automatically without any manual work. However, as more users opted for Google Sheets as a database for international stock prices, they realized that some data was missing or not available. Although there are other finance databases available, Yahoo Finance seems to be the best complement to Google Finance. What’s more, you can import the data automatically as well.

By now, you should feel comfortable importing Yahoo Finance data into your Google Sheets. You’ve seen how to use the Google Apps Script code editor to create functions using predefined scripts, such as the YahooF function. This is a necessary step before you can use the YahooF formula to import data without having to leave your Google Sheets.