Don’t forget to share this post

There are multiple websites that offer interesting financial data, but it’s not always easy to get the data into Google Sheets. Google Finance and Yahoo Finance are among the most popular site for up-to-date financial data, but Yahoo Finance has data that Google Finance doesn’t. Google Finance data can be imported using the GOOGLEFINANCE formula, but how do you get Yahoo Finance data into Sheets?

In this article, you will learn how to import data from Yahoo Finance into Google Sheets, using the IMPORTXML formula. You will learn how to get the base URL and the XPaths for the elements you want to import so that you can set up a template in Google Sheets. Once you’ve set it up, you only have to change the ticker symbol to get info on other stocks, currencies, etc.

How to Import Yahoo Finance Data into Google Sheets?

There are various methods that can be used to import data from Yahoo Finance. If you’re familiar with modern Javascript and Google Apps Script, you can write a custom function to import the data you need. However, if you’re not comfortable working with code, there are other ways to do this.

Google Sheets has a variety of import functions, depending on the source of the data. In this case, even though Yahoo Finance is a website, IMPORTXML is more appropriate than IMPORTHTML. Yahoo Finance pages have a fixed structure: each page has the same elements - price, closing date, volume, market cap, and many, many more. This means you can choose one specific page as an example and use it to create a template.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

How to Import Yahoo Finance Data with IMPORTXML?

Since I will be using IMPORTXML, there are two parameters I need to set up my template: a URL, and the XPaths for the different elements we want to import. To learn more about the formula, check out this article on How to Use IMPORTXML in Google Sheets.

The formula you will need to use is:

=IMPORTXML(url, xpath_query)

Fortunately, Yahoo Finance pages are identically structured. This means you can create a template based on a sample page by getting the XPaths of specific elements. Then, all you need to do is change the ticker to get data from different sources. For this example, I will be using Google Chrome, as the Inspect feature makes it very easy to copy the XPaths.

Step 1: Get URL

In order to find the base URL, we first need to look at an example. Below, I will be using the Amazon.com, Inc. (AMZN) webpage in Yahoo Finance.

  1. 1. Copy the URL from your browser’s address bar.
How To Import Yahoo Finance Data Into Google Sheets Copy URL
How To Import Yahoo Finance Data Into Google Sheets - Copy URL
  1. 2. Paste the URL into Google Sheets. As you can see in the URL, the ticker symbol (AMZN) is the last part of the URL.
How To Import Yahoo Finance Data Into Google Sheets Paste URL
How To Import Yahoo Finance Data Into Google Sheets - Paste URL
  1. 3. Copy the URL without the ticker symbol and paste it into a new cell.
How To Import Yahoo Finance Data Into Google Sheets Base URL
How To Import Yahoo Finance Data Into Google Sheets - Base URL
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

Step 2: Get XPaths

Once you find the base URL, you need to find the XPaths for the elements you want to import. I will be using ‘price’, ‘volume’, ‘market cap’, and ‘earnings date’, but you can choose whatever elements you want.

  1. 1. Add the element labels to your spreadsheet to keep track of which XPath belongs to which element.
How To Import Yahoo Finance Data Into Google Sheets X Path Labels
How To Import Yahoo Finance Data Into Google Sheets - XPath Labels
  1. 2. Go back to the webpage and select the price. Right-click to get the menu and click “Inspect”.
How To Import Yahoo Finance Data Into Google Sheets Inspect Element
How To Import Yahoo Finance Data Into Google Sheets - Inspect Element
  1. 3. In the Inspect console, you can see the code corresponding to the selection. In this case, the code is highlighted correctly, as you can see below.
How To Import Yahoo Finance Data Into Google Sheets Element Selection
How To Import Yahoo Finance Data Into Google Sheets - Element Selection
  1. 4. Right-click on the selected code, then Copy > Copy full XPath.
How To Import Yahoo Finance Data Into Google Sheets Copy Full X Path
How To Import Yahoo Finance Data Into Google Sheets - Copy Full XPath
  1. 5. Paste the XPath into the corresponding cell on Sheets. In this case, it’s the one for ‘price’, so I’ll paste it next to its label.
How To Import Yahoo Finance Data Into Google Sheets Paste X Path
How To Import Yahoo Finance Data Into Google Sheets - Paste XPath
  1. 6. To get the XPath for ‘volume’, go back to the webpage and find the data. Select the ‘volume’ value, right-click and select “Inspect”.
How To Import Yahoo Finance Data Into Google Sheets Inspect Volume Element
How To Import Yahoo Finance Data Into Google Sheets - Inspect Volume Element
  1. 7. Just like for the ‘price’, copy the full XPath.
How To Import Yahoo Finance Data Into Google Sheets Copy Full X Path 2
How To Import Yahoo Finance Data Into Google Sheets - Copy Full XPath
  1. 8. Paste the ‘volume’ XPath to the corresponding cell in Sheets.
How To Import Yahoo Finance Data Into Google Sheets Paste Volume X Path
How To Import Yahoo Finance Data Into Google Sheets - Paste Volume XPath
  1. 9. Continue with the process until you have all the XPaths for the elements you want to import.
How To Import Yahoo Finance Data Into Google Sheets Get Additional X Paths
How To Import Yahoo Finance Data Into Google Sheets - Get Additional XPaths

How to Use IMPORTXML in Google Sheets (Examples)

Google offers multiple options to import data from external sources. Here’s how to use IMPORTXML to import structured data from a URL into Google Sheets.

READ MORE
How to Use IMPORTXML in Google Sheets Examples

Step 3: Set Up Template

Now that you have the base URL and the XPaths, you can set up a template in Sheets.

  1. 1. Set up your table, including fields for the ‘ticker’ and ‘tickerURL’. I’ll use ‘AMZN’ as the placeholder while I set up the template.
How To Import Yahoo Finance Data Into Google Sheets Set Up Template
How To Import Yahoo Finance Data Into Google Sheets - Set Up Template
  1. 2. For the ‘tickerURL’, concatenate the ‘baseURL’ and the ‘ticker’, using the “&” symbol as shown below.
How To Import Yahoo Finance Data Into Google Sheets Create Ticker URL
How To Import Yahoo Finance Data Into Google Sheets - Create Ticker URL
  1. 3. As you can see, the ‘tickerURL’ includes the ticker symbol at the end, just as it should.
How To Import Yahoo Finance Data Into Google Sheets Ticker URL
How To Import Yahoo Finance Data Into Google Sheets - Ticker URL
  1. 4. Next to the ‘price’ label, type in the IMPORTXML formula.
How To Import Yahoo Finance Data Into Google Sheets Add IMPORTXML Formula
How To Import Yahoo Finance Data Into Google Sheets - Add IMPORTXML Formula
  1. 5. Select the ‘tickerURL’ as the URL parameter in the formula, using absolute referencing ($), so you can copy the formula down. Select the XPath corresponding to price as the second parameter. Close the parenthesis and press Enter on your keyboard.
How To Import Yahoo Finance Data Into Google Sheets Add Cell References
How To Import Yahoo Finance Data Into Google Sheets - Add Cell References
  1. 6. Drag the formula down to the other elements to retrieve the rest. As you can see below, the data has been successfully imported.
How To Import Yahoo Finance Data Into Google Sheets Drag Formula Down
How To Import Yahoo Finance Data Into Google Sheets - Drag Formula Down

Step 4: Import Data

By simply changing the ‘ticker’, you can import data from different companies. For example, let’s try ‘MSFT’.

How To Import Yahoo Finance Data Into Google Sheets Change Ticker Symbol
How To Import Yahoo Finance Data Into Google Sheets - Change Ticker Symbol

There are some limitations to IMPORTXML, of course, such as the number of times you can use the IMPORTXML formula in your Google Sheets file. Theoretically, the limit is 50, but you may experience problems before getting to that number. However, the previous example only uses the formula 4 times since it retrieves 4 data elements.

You can easily get data for multiple companies at the same time by adding more columns for each ticker symbol. You can also add/remove elements to ensure you only import what you need.

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

Importing data from Yahoo Finance to Google Sheets is easy using IMPORTXML. You can get the XPaths quickly using Chrome’s Inspect tool and save them in your Google Sheets file to be reused. Once the template is set up, all you need is to add the ticker symbol you’re interested in, and you’ll have the data available in your Google Sheets file.

You now know how to use Google Sheet’s IMPORTXML to retrieve data from Yahoo Finance pages. You know how to find the base URL, as well as how to use Google Chrome’s Inspect to get the XPaths for all the elements you want to import.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
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 Nov 26 2022