How to Import Yahoo Finance Data into Google Sheets?
Whether you are a stock market enthusiast or a serious investment banker, having access to accurate financial data is essential.
That’s where Yahoo Finance comes in.
Along with being a trusted source for financial data, it offers various features to analyze and visualize your investments. To start analyzing your portfolio in-depth, you can also import data from Yahoo Finance into Google Sheets in just a few clicks.
In this article, we will discuss the different methods to import Yahoo Finance data into Google Sheets and help you choose the hassle-free method.
Let’s get started!
Ways to import Yahoo Finance data into Google Sheets
You can import Yahoo Finance data into Google Sheets in three different ways, and below is a quick introduction to every method.
1. Importing data with a CSV file
The simplest way to import data from Yahoo Finance to Google Sheets is with a CSV file. All you need to do is just export the Yahoo Finance data in the CSV format and then upload it into Google Sheets.
Even though the process is quite straightforward and requires zero technical skills, the data is not updated automatically, so you won’t be able to monitor it in real-time. If you want to use this way to import Yahoo Finance data into Google Sheets, be ready to do the manual export and import at regular intervals.
2. Importing automatically with third-party tools
If you neither have the time to import data multiple times in a week manually nor want to rely on formulas, then using third-party tools can be the right choice for you.
Third-party solutions can help you streamline and automate the process of Yahoo Finance data import into Google Sheets. A few of these tools also come as a Google Sheets add-on, so you can perform the import process directly in Google Sheets.
Examples of such solutions are Coupler.io, Stock Connector, MarketXLS, and Import.io.
3. Extracting data with formulas
You can use various Google Sheets formulas to import the data you need. Some of the most common formulas are IMPORTHTML, IMPORTXML, and IMPORTFEED. They allow you to easily pull data from Yahoo Finance into Google Sheets.
The advantage of the formulas is that they can update the imported information automatically. You’ll just need to go to the cell with the formula and press Enter every time you want to refresh the financial data in the spreadsheet. The downside is that sometimes formulas might work incorrectly or return errors depending on the structure of the web page they fetch data from.
Now, let’s have a closer look at each of these methods.
How to import data from Yahoo Finance to Google Sheets with a CSV file?
Step 1: Go to Yahoo Finance and search for the company and the stock of your choice.
Step 2: You can now see important financial parameters of the company stock like Previous close, Market cap, PE ratio, EPS, etc. Now, click on the ‘historical data’ tab.
Step 3: To conduct the financial analysis using Yahoo Finance data, you must select both the time period and frequency of the required data. By selecting a frequency, you will receive financial data that includes daily, weekly, or monthly variations, based on your preference.
Click on ‘Apply’
Step 4: Now your Yahoo Finance data is ready to be exported. Click on ‘download’ to start exporting.
Step 5: Now go to Google Sheets and create a ‘Blank Spreadsheet’.
Step 6: Click on ‘File’ and select ‘Import’.
Step 7: On the import dialog box, click on ‘Upload’ to browse or drag the exported Yahoo Finance CSV file from your computer.
Step 8: Select the import location as Insert new sheet and Separator type as ‘Detect automatically.’
To avoid incorrect calculations during data analysis, make sure to check the box for ‘convert text to numbers, dates, and formulas.’
Now click on ‘Import data’
Step 9: The Yahoo Finance data is imported into Google Sheets with all the historical financial details.
How to use Yahoo Finance API for Google Sheets?
The instructions provided in this article are sufficient for creating an automated integration. But please note that we are not responsible for the outcome if you use the instructions incorrectly, specify incorrect settings, or if there are changes in the app’s API. If you need help with the setup, we can offer this premium integration at an additional fee.
Yahoo Finance API helps you to access financial data like stock prices, market summaries, and other financial information.
It’s worth mentioning that the official Yahoo Finance API was discontinued in 2017 due to extensive violations of their terms of service. However, an unofficial version came out in 2019. It can be used in different ways, such as developing custom applications, automating trading reports, etc. For example, you can do so by using a Python program that sends requests to the API and processes response data, which is highly technical and would require coding skills.
Alternatively, you can use a third-party solution to import Yahoo Finance data into Google Sheets using Yahoo Finance API.
In the next section, we will give a practical example of how to use a third-party solution using Yahoo Finance API and automatically import data into Google Sheets. This method does not require any technical knowledge and is perfect for business users.
How to import Yahoo Finance data into Google Sheets automatically on a schedule via API?
Manual importing of the CSV file and using formulas are good methods to import Yahoo Finance data into Google Sheets. But both have their downsides:
- With manual importing, you have to repeat the whole data importing process whenever you need updated data.
- Extracting Yahoo Finance data using formulas can be tricky as they do not work properly at times for different types of data on Yahoo Finance due to complex page structures.
To overcome these limitations of the other methods, you can use a third-party solution like Coupler.io, an all-in-one data analytics and automation platform that empowers organizations to gain the most from their data. You can automatically import data to Google Sheets, Excel, or BigQuery from 70+ different sources like Airtable, Pipedrive, HubSpot, etc.
To import Yahoo Finance data into Google Sheets automatically via API, you can request Yahoo Finance as a premium source on Coupler.io. Alternatively, you can also connect it yourself with Coupler.io’s JSON importer.
Below is a step-by-step guide to import Yahoo Finance into Google Sheets automatically on a schedule via API using Coupler.io.
Step 1: Sign up for Coupler.io by using your email address. If you already have a Coupler.io account, you can just log in and click on ‘Add importer’.
Step 2: Now select the source as ‘JSON’ and the destination as ‘Google Sheets’. Click on ‘Proceed.’
Step 3: Add other details of the source like
- JSON URL – insert the JSON URL of Yahoo Finance and click ‘Continue’
The JSON URL for major data entities:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/AMZN
AMZN is the stock symbol for Amazon, but you can easily replace it with the symbol for any other company to pull their data.
For example, if you want to change the company from Amazon to Tesla, replace AMZN with TSLA in the above link, which will look like this:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/TSLA
HTTP method – use GET as the HTTP method.
- URL query string – The query string will differ depending on the data you want to retrieve.
Here we are retrieving price data of AMZN stock so that the query string will be ‘modules: prices’.
Query parameters help you narrow down the imported information by filtering and modifying the data on the company stock page. If you leave it empty, it will give the default data of the stock summary, like current stock price, volume, and other data points.
Now you can click on ‘Jump to Destination settings’.
Step 4: Select your destination Google Sheets account from the drop-down menu. If you signed up for Coupler.io with a gmail address, your Google account will be already connected. If not, you can connect it at this step and grant the requested permissions.
Step 5: Select the spreadsheet and sheet in Google Sheets. Hit Continue.
Step 6: Input the cell address/ range and import mode as shown below.
Then, select the import mode.
Replace: Fully replaces all previously imported data with the latest information available in your data source. Select this if you want to only have the latest version of your dataset in Google Sheets. Append: Places your newly imported data under previously imported entries; is suitable for tracking historical data changes.
Step 7: Now Configure an automatic data refresh by defining the interval, days, time, and time zone of the scheduled execution for the automated data refresh.
The importer dashboard now displays the details, and the data will be refreshed at regular intervals based on your settings.
Now, we’ve imported Yahoo Finance data into Google Sheets with the help of Coupler.io. Here’s what the result looks like:
If using a JSON importer seems confusing and too technical, the Coupler.io team can set up the importer for you. Yahoo Finance is available as a premium source, and you can include it in your Coupler.io plan for an additional fee.
Coupler.io can also be used as an add-on in Google Sheets, where you can skip entering the destination details and import Yahoo Finance data seamlessly.
The Coupler.io add-on will be connected to your Coupler account, and all your active importers will be shown in the Coupler dashboard on Google Sheets.
Data parameters to use to import Yahoo Finance data into Google Sheets
Yahoo Finance has different types of data that can be imported. And every data type needs a ULR query string to be individually addressed during the import process.
Query parameters help Coupler.io importer pull the desired data as per your choice instead of juggling with a huge spreadsheet filled with unnecessary data.
Below are the data parameters and their URL query parameters that are used for importing data from Yahoo Finance.
They need to be pasted while filling the ‘Source’ details in the importer for the field ‘URL query parameters’.
| Data parameter | URL query string |
| Price | modules: price |
| Key statistical data including (YTD Daily Total Return, profit margins, float shares, etc.) | modules: defaultKeyStatistics |
| Expense Ratio | modules: fundProfile |
| Profile Data | modules: assetProfile |
| Income statements history | modules: incomeStatementHistory |
| Quarterly income statements history | modules: incomeStatementHistoryQuarterly |
| Balance Sheet history | modules: balanceSheetHistory |
| Quarterly balance Sheet history | modules: balanceSheetHistoryQuarterly |
| Cash flow statement history | modules: cashflowStatementHistory |
| Quarterly cash flow statement history | modules: cashflowStatementHistoryQuarterly |
| Financial data | modules: financialData |
| Calendar events | modules: calendarEvents |
| Securities filings | modules: secFilings |
| Recommendation trend | modules: recommendationTrend |
| Upgrade & downgrade history | modules: upgradeDowngradeHistory |
| Institution ownership | modules: institutionOwnership |
| Fund ownership | modules: fundOwnership |
| Major direct holders | modules: majorDirectHolders |
| Major holders breakdown | modules: majorHoldersBreakdown |
| Insider transactions | modules: insiderTransactions |
| Insider holders | modules: insiderHolders |
| Net share purchase activity | modules: netSharePurchaseActivity |
| Earnings | modules: earnings |
| Earnings history | modules: earningsHistory |
| Earnings trend | modules: earningsTrend |
| Industry trend | modules: industryTrend |
| Index trend | modules: indexTrend |
| Sector trend | modules: sectorTrend |
How to import Yahoo Finance data using Google Sheets formulas?
We have looked at different formulas that can be used to import Yahoo Finance data into Google Sheets at the beginning of the article.
In this section, we will go through a step-by-step guide using each of these formulas.
How to use the IMPORTHTML formula to import Yahoo Finance data into Google Sheets?
IMPORTHTML is used to extract data from HTML tables on the Yahoo Finance webpage.
Step 1: Open a new or existing Google spreadsheet and select a cell where the imported Yahoo Finance data will be displayed.
Step 2: Now use the IMPORTHTML formula.
Here’s the basic syntax: IMPORTHTML(url, query, index, locale) where
- Url – the link to the Yahoo Finance web page. In our example, it’s https://finance.yahoo.com/quote/AMZN (AMZN is the stock symbol for Amazon). You can change the symbol for the company of your choice.
- Query – table (as we are importing tables).
- Index – the table number on the webpage.
- Locale – the stock region used to specify the language and format for numbers, dates, and times in the imported data.
The final formula is
=IMPORTHTML("https://finance.yahoo.com/quote/AMZN","table",1,"en_US")
Step 3: Click ‘Enter’ to import the table from Yahoo Finance into Google Sheets.
As you can see in the below images, the first table on the Amazon Yahoo Finance page is imported into Google Sheets.
Here is the data on Yahoo Finance.
And here is the imported data in Google Sheets transferred with the help of IMPORTHTML formula.
The IMPORTHTML formula is handy for importing Yahoo Finance data, but it does not update the data automatically when the spreadsheet is reopened. To refresh the data, you must navigate to the formula cell and click ‘Enter.’
How to use the IMPORTFEED formula to import Yahoo Finance feed into Google Sheets?
IMPORTFEED is used to import finance news from Yahoo Finance into Google Sheets to keep track of stock market fluctuations and trends.
Step 1: Open a new or existing Google spreadsheet and select a cell where the imported Yahoo Finance feed will be displayed.
Step 2: Now use the IMPORTFEED formula.
The syntax is: IMPORTFEED(url, query, headers, num_items) where
- Url – This is the URL of the RSS feed you want to import. Here it is – https://finance.yahoo.com/rss.
- Query – It can take 3 values (entry, feed, all). If omitted, it selects ‘entry’ by default resulting in individual news items.
- Headers – It takes 2 values (True or False) to understand if your headers are to be included or not in the import.
- Num_items – The number of news items to be displayed.
The final formula is
=IMPORTFEED("https://finance.yahoo.com/rss/", "items", FALSE, 10)
Step 3: Click ‘Enter’ to import the Yahoo Finance newsfeed into Google Sheets.
How to use Google Sheets IMPORTXML formula for Yahoo Finance?
IMPORTXML is used to import specific values of financial data from Yahoo Finance, such as the closing stock price of a company. Let’s go through the step-by-step guide about using the IMPORTXML formula for Yahoo Finance.
Step 1: First, open a Google spreadsheet, either new or existing, and then select the particular cell where you would like to display the Yahoo Finance feed that you’re importing.
Step 2: Enter the IMPORTXML formula in the selected cell.
The syntax is: IMPORTXML(url, xpath_query, locale), where;
- Url – indicates the URL of the website page that you need to import data from. Here it is – https://finance.yahoo.com/quote/AMZN
- Xpath_query – It is a string of text that describes the location of an element in an HTML or XML document. To identify the Xpath, go to Yahoo Finance and select the parameter you want and click on ‘Inspect’.
The HTML code will be selected. Click on Copy -> Copy full X-path. Now that is your X-path query.
- Locale – the region of the stock, used to specify the language and format for numbers, dates, and times in the imported data.
The final formula is
=IMPORTXML("https://finance.yahoo.com/quote/AMZN","/html/body/div[1]/div/div/div[1]/div/div[2]/div/div/div[6]/div/div/div/div[3]/div[1]/div[1]/fin-streamer[1]”,”en_US")
Step 3: Click ‘Enter’ to import the Yahoo Finance data into Google Sheets.
The stock price data of AMZN is successfully imported into Google Sheets. Similarly, you can use IMPORTXML multiple times to import different types of Yahoo Finance data into Google Sheets.
Note that IMPORTXML might not work on all Yahoo Finance pages because of different page structures. Moreover, the imported Yahoo Finance data should be manually refreshed every time by clicking on the formula cell and pressing ‘Enter.’
What is the best way to pull Yahoo Finance data into Google Sheets?
In this article, we have discussed multiple ways to import Yahoo Finance data into Google Sheets. The simplest method is the manual method, where you export Yahoo Finance data into a CSV file and upload it to Google Sheets. But the data cannot be automatically updated, requiring multiple iterations of downloading and uploading manually.
Using formulas to extract Yahoo Finance data is also a viable option, only if you can deal with the technicalities and occasionally incorrect data.
Automated import using third-party tools is the most reliable option for business users to seamlessly pull Yahoo Finance data into Google Sheets. Anyone can set it up with zero technical knowledge and let the data refresh automatically. For example, you can achieve this result by using Coupler.io. In this case, setting up an automated integration is relatively simple, and financial data in your spreadsheet will be updated on a custom schedule without any manual effort.
Take a look at other Google Sheets integrations offered by Coupler.io.
