Don’t forget to share this post

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.

Layer Google Sheets Add on Waitlist 2
Do you use Google Sheets?

Share parts of your spreadsheet and make your data collection 10X better with the new Layer add-on!

GET EARLY ACCESS

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.
How To Import Yahoo Finance Data Into Google Sheets Extensions App Scripts
How To Import Yahoo Finance Data Into Google Sheets - Extensions > App Scripts
  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.
How To Import Yahoo Finance Data Into Google Sheets Change account
How To Import Yahoo Finance Data Into Google Sheets - Change account
  1. 3. Select the right account to proceed.
How To Import Yahoo Finance Data Into Google Sheets Select account
How To Import Yahoo Finance Data Into Google Sheets - Select account
  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')
}
How To Import Yahoo Finance Data Into Google Sheets Paste new function
How To Import Yahoo Finance Data Into Google Sheets - Paste new function
  1. 5. Click on the “Save” button at the top, in the main toolbar.
How To Import Yahoo Finance Data Into Google Sheets Click Save
How To Import Yahoo Finance Data Into Google Sheets - Click Save
  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”.
How To Import Yahoo Finance Data Into Google Sheets Click Run
How To Import Yahoo Finance Data Into Google Sheets - Click Run

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 GOOGLEFINANCE Function in Google Sheets
How to Use GOOGLEFINANCE Function in Google Sheets?

Import current or historical financial market data from Google Finance & monitor real-time. Here's how to use the GOOGLEFINANCE function in Google Sheets

READ MORE

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];
}
How To Import Yahoo Finance Data Into Google Sheets Yahoo F function
How To Import Yahoo Finance Data Into Google Sheets - YahooF function
  1. 3. Click on “Save”.
How To Import Yahoo Finance Data Into Google Sheets Save Yahoo F function
How To Import Yahoo Finance Data Into Google Sheets - Save YahooF function
  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”.
How To Import Yahoo Finance Data Into Google Sheets Review permissions
How To Import Yahoo Finance Data Into Google Sheets - Review permissions
  1. 5. Choose the account from where you’d like to grant access.
How To Import Yahoo Finance Data Into Google Sheets Choose account to grant permissions
How To Import Yahoo Finance Data Into Google Sheets - Choose account to grant permissions
  1. 6. Click on “Allow” to start executing the function.
How To Import Yahoo Finance Data Into Google Sheets Click Allow
How To Import Yahoo Finance Data Into Google Sheets - Click Allow
  1. 7. In your “Execution log” at the bottom, you should now see the ticker price logged. Here, “137.51”.
How To Import Yahoo Finance Data Into Google Sheets Logged price
How To Import Yahoo Finance Data Into Google Sheets - Logged price

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

Google Sheets Macros: Enable, Create & Use Them

Google Sheets macros are a great way to automate repetitive tasks. Here’s how to enable, create and use macros in Google Sheets.

READ MORE
Google Sheets Macros Enable Create Use Them

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];
}
How To Import Yahoo Finance Data Into Google Sheets Yahoo F function 2
How To Import Yahoo Finance Data Into Google Sheets - YahooF function
  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.
How To Import Yahoo Finance Data Into Google Sheets Type Yahoo Formula
How To Import Yahoo Finance Data Into Google Sheets - Type Yahoo Formula
  1. 4. Google Sheets has now returned the ticker price you included.
How To Import Yahoo Finance Data Into Google Sheets Ticker price returned
How To Import Yahoo Finance Data Into Google Sheets - Ticker price returned

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jul 5 2022, Updated Jul 19 2022