How To Import Yahoo Finance Data Into Google Sheets

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.

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.

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.

1. 3. Copy the URL without the ticker symbol and paste it into a new cell.

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.

1. 2. Go back to the webpage and select the price. Right-click to get the menu and click “Inspect”.

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.

1. 4. Right-click on the selected code, then Copy > 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.

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”.

1. 7. Just like for the ‘price’, copy the full XPath.

1. 8. Paste the ‘volume’ XPath to the corresponding cell in Sheets.

1. 9. Continue with the process until you have all the XPaths for the elements you want to import.

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.

1. 2. For the ‘tickerURL’, concatenate the ‘baseURL’ and the ‘ticker’, using the “&” symbol as shown below.

1. 3. As you can see, the ‘tickerURL’ includes the ticker symbol at the end, just as it should.

1. 4. Next to the ‘price’ label, type in the 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.

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.

Step 4: Import Data

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

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.