by Hady ElHady | Mar 13, 2024
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. Copy the URL from your browser’s address bar.
How To Import Yahoo Finance Data Into Google Sheets – Copy URL
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
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
You want to analyze Yahoo Finance data in Google Sheets but are still using copy-paste? Here’s how to import Yahoo Finance data into Google Sheets instead.
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. 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 – XPath Labels
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
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
4. Right-click on the selected code, then Copy > Copy full XPath.
How To Import Yahoo Finance Data Into Google Sheets – Copy Full XPath
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 XPath
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
7. Just like for the ‘price’, copy the full XPath.
How To Import Yahoo Finance Data Into Google Sheets – Copy Full XPath
8. Paste the ‘volume’ XPath to the corresponding cell in Sheets.
How To Import Yahoo Finance Data Into Google Sheets – Paste Volume XPath
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 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 →
Step 3: Set Up Template
Now that you have the base URL and the XPaths, you can set up a template in Sheets.
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
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
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
4. Next to the ‘price’ label, type in the IMPORTXML formula.
How To Import Yahoo Finance Data Into Google Sheets – Add IMPORTXML Formula
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
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
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
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.
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.
