How to Import Yahoo Finance Data into Google Sheets? | Coupler.io blog (2024)

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 different methods to do this and help you choose the hassle-free method.

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. Connect Yahoo Finance to Google Sheets with Coupler.io

If you neither have the time to import data multiple times in a week manually nor want to rely on formulas, then use Coupler.io. It’s a reporting automation solution to automate Yahoo Finance data import into Google Sheets. You can use it as a web app or a Google Sheets add-on, so you can perform the import process directly in spreadsheets.

Alternative third-party connectors are Stock Connector, MarketXLS, and Import.io.

2. Import 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.

3. Extract 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 use Yahoo Finance API for Google Sheets?

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 Coupler.io’s no-code connector to import Yahoo Finance data into Google Sheets using Yahoo Finance API.It does not require any technical knowledge and is perfect for business users.

How to import Yahoo Finance data into Google Sheets with Coupler.io

Below is a step-by-step guide to import Yahoo Finance into Google Sheets automatically on a schedule via API using Coupler.io.

1. Collect data from Yahoo Finance

Click Proceed in the form below to create a connector. You’ll also be offered to get started with Coupler.io for free with no credit card required.

Then configure the following parameters

• JSON URL – insert the JSON URL of Yahoo Finance:

https://query1.finance.yahoo.com/v8/finance/chart/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 or Google, replace AMZN with TSLA or GOOG in the above link, which will look like this:

https://query1.finance.yahoo.com/v8/finance/chart/GOOG

• HTTP method – use GET as the HTTP method.
• Request headers – leave blank
• URL query parameters – The query string will differ depending on the data you want to retrieve.Here we are retrieving price data of Google stock so that the query string will be:

modules: price

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.

2. Preview and transform data from Yahoo Finance

Before loading Yahoo Finance data, you can filter it, hide unnecessary columns, add new ones, and so on.

3. Load data and schedule refresh

Connect your Google account, then select the spreadsheet and sheet in Google Sheets. Enter a new sheet name to create one. Optionally, you can select the cell address/ range, change the import mode, and use other features.

To keep your data fresh, it makes sense to toggle on the Automatic data refresh and configure the schedule.

Save and run the importer to load Yahoo Finance data into Google Sheets.

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 on the right pane in 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’.

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

• Step 4: Now your Yahoo Finance data is ready to be exported. Click Download to start exporting.

• Step 5: Now go to Google Sheets and create a blank spreadsheet. Click on File => Import.

• Step 7: On the import dialog box, click 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 Import data.

The Yahoo Finance data is imported into Google Sheets with all the historical financial details.

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

Julia Starun

Product Manager at Railsware. I consider myself a 4 P's manager, focusing on Product, Process, Project, and People. In any of those fields, the main driver for me is the ability to make people's lives easier. That's also why I like to share my experience via blog. I have worked in consultancy, R&D, support, marketing, and PMO, which helps me look at any question from diverse points of view. Besides work, I really enjoy traveling and playing board games🎲