Export Yahoo Finance Data to Excel on a Schedule [2024] | Coupler.io Blog (2024)

Tracking portfolio performance regularly is a good investment practice. Online platforms such as Yahoo Finance help do this in real-time. But, it may not be enough for sophisticated investors and market analysts looking for a more organized manner to keep track of portfolios. Connecting Yahoo Finance to Excel can bridge the stock market live data with a powerful analysis tool.

In this article, learn different ways to link Yahoo Finance to Excel and synchronize stock prices with Excel.

Different ways to link Yahoo Finance to Excel

Yahoo Finance is a reliable source for live market prices and historical data. There is no native method to connect Yahoo Finance to Excel and export the data in real-time. Therefore, we need to call its API (Application Programming Interface) to get the required information and update it to Excel. Basically, the API acts as a software intermediary between Excel and Yahoo Finance for information exchange.

The official Yahoo Finance API has been discontinued since 2017. However, its unofficial version came out in 2019. We can use this unofficial API to connect Yahoo Finance to Excel.

You can do this in three ways:

• Coupler.io – The easiest method to set up a Yahoo Finance export to Excel using Coupler.io. It provides an ETL tool to integrate the JSON API with Excel yourself. Or you can submit a request to the Coupler.io data service to have a Yahoo Finance integration setup by their data experts.
• Power Query – Use Power Query in Excel to pull information from the unofficial Yahoo Finance API.
• Python Program – Build your program in Python to retrieve data from Yahoo Finance into Excel using the yfinance library.

Read this article until the end to learn how to get Yahoo Finance data into Excel.

How to sync Yahoo Finance with Excel Using ETL Tool by Coupler.io?

Coupler.io is a no-code data automation and analytics platform. It provides an ETL tool to connect a wide range of sources and destinations. You can call Yahoo Finance’s unofficial API in the JSON client as a source and integrate your data easily into Excel using it.

Let’s say you want to get the Apple stock price in Excel, which gets updated daily. You can do it by following the steps provided below.

1. Collect data

• To link Yahoo Finance to Excel via the Coupler.io platform, click Proceed in the form below. We’ve already preselected JSON as the source and Microsoft Excel as the destination, so you only need to create a Coupler.io account for free and configure the connection

• Enter the Yahoo Finance API endpoint URL in the JSON URL field. For example:

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

• Select GET in the HTTP method.
• In the URL query parameters, enter the parameters related to the data you need. For example, to retrieve the price information:

modules: price

Learn more about the URL parameters to get data from Yahoo Finance to Excel.

2. Organize data

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

After that, connect your Microsoft OneDrive account and select the Workbook and Sheet where you want to export the Yahoo Finance data.

3. Schedule data refresh

Enable the schedule feature to auto-refresh the data at regular intervals. Switch on the Automatic data refresh option and configure the data refresh schedule.

Finally, click on the Run importer button to launch the Yahoo Finance export to Excel.

You can see below, Coupler.io exported the entire JSON response from Yahoo Finance API into Excel.

Now, let’s say you only want the regular market price of the stock, i.e., the column named quoteSummary.result.0.price.regularMarketPrice.raw in the response.

You can do that by hiding/unhiding columns during the preview. Now, click on the Run importer button again to get the required data into Excel. This time, Couper.io pulled only the required column!

Furthermore, as we configured, Coupler.io will refresh the stock price automatically as per the schedule!

How to use Yahoo Finance API in Excel Using Power Query?

Power Query is a robust ETL tool that lets you connect external data sources to Excel. You can use it to export Yahoo Finance data to Excel via the API as a web source. Since Excel 2016, Power Query has been fully integrated and can be found as Get & Transform Data in the Data tab.

The stepwise methods to export stock prices and tables from Yahoo Finance to Excel via Power Query are provided below.

How to export a table from Yahoo Finance to Excel?

Let’s say you want to get the historical prices of Apple stock for February with a weekly frequency. You need to make an API call to Yahoo Finance, including the date range and other parameters. Here’s a clever way to do that.

• Go to finance.yahoo.com and search for the required stock.
• Click on the Historical Data tab, select the appropriate time period and frequency for the historical prices, and click on Apply.
• Now, right-click on the Download option available in the top-right corner of the table, and click on Copy link address.

Now, we’ll use the copied link as a web source to get the required table from Yahoo Finance to Excel. Just follow these steps to export Yahoo Finance data to Excel:

• Open the Excel file, and select the cell where you want to import the table.
• Go to Data > Get & Transform Data > From Web.
• Now, paste the copied URL into the respective field and click on OK.

• Now, the popup will show a preview of the table. Review it and click the Load button to run the Yahoo Finance export to Excel.

The required table has been successfully exported from Yahoo Finance to Excel.

You can use this method to export various tables from Yahoo Finance, such as historical prices, dividends, stock splits, and capital gains.

How to import stock prices into Excel from Yahoo Finance?

You can directly call the Yahoo Finance API in Power Query to import stock prices in Excel. Let’s understand this through an example of Apple stock.

• Open the Excel sheet, and select the cell where you want to get the stock price.
• Go to Data > Get & Transform Data > From Web.
• Enter the following in the URL field:

 https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=price

Learn more about the URL parameters to get data from Yahoo Finance to Excel.

The Power Query Editor popup will appear on your screen.

• Now, double-click on the fields and navigate to Quote Summary > Result > Price > regularMarketPrice.
• Now, right-click the raw value and click Add as New Query.

• This will create a new query for the raw value (stock price). Keep the raw query selected and click Into Table.
• Finally, import the stock price into Excel by clicking on the Close & Load button.

The stock price will be successfully imported to Excel from Yahoo Finance.

You can modify the ticker in the above URL to get the price of the desired stock. Furthermore, you can also configure Excel to auto-refresh the connection at regular intervals to keep the stock price updated.

Follow these steps to set up stock price auto-refresh in Excel.

• Keep the stock price column selected, and go to Data > Queries & Connections > Refresh All > Connection Properties.

• In the Query Properties menu, enable the refresh feature, and set the interval in minutes. Finally, click OK to save changes.

Now, Excel will refresh the connection every minute and update the stock price! 😀

URL parameters to get data from Yahoo Finance to Excel

The above two methods use the unofficial API to link Yahoo Finance to Excel. A proper understanding of the API endpoints and available features can help you get the most out of the integration.

There are two different API endpoints to fetch data from Yahoo Finance:

• For Major Data Entities:

https://query1.finance.yahoo.com/v10/finance/quoteSummary/{ticker}

• For Historical Data:

https://query1.finance.yahoo.com/v8/finance/chart/{ticker}

The {ticker} is the unique abbreviation of the stock used to identify the stock. For example, AAPL for Apple stock.

JSON URL parameters for major entities

You need to specify the parameters in the Yahoo Finance API to get the required data. Let’s say you want to get price details of Apple stock; then you need to make the following API request:

https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL?modules=price

A complete list of supported URL parameters in Yahoo Finance API is provided below.

JSON URL parameters for historical data

The Yahoo Finance API requires additional parameters to get historical data about stocks, splits, and dividends.

Let’s say you want to get weekly historical data of the Apple stock price for the entire month of February; you need to make the following API request:

https://query1.finance.yahoo.com/v8/finance/chart/AAPL?period1=1643673600&period2=1677542400&interval=1wk&events=history&includeAdjustedClose=true

Explanation of each of the URL parameters is tabulated below:

How to link Yahoo Finance to Excel with Python?

You can also export Yahoo Finance data to Excel using a Python program. It is a highly technical method to access the Yahoo Finance API and export the data to Excel.

Follow these steps to export Yahoo Finance data to Excel with Python.

• First of all, you need to install the yfinance library to use the Yahoo Finance API in Python. Run the following command to install it:

pip install yfinance

• Now, open a notepad or any other text editor, and create script.py file with the following code:
  

import yfinance as yfstart_date = '2022-01-01'end_date = '2023-01-01'ticker = 'GOOGL'data = yf.download(ticker, start_date, end_date)print(data.tail()) # the most recent prices# Export data to a CSV filedata.to_csv(f"{ticker }.csv")

• The above code will generate a CSV file containing the historical data of the Google stock.

Ta-da!! 🎉

Similarly, you can use other functions of the yfinance library to pull data from Yahoo Finance to Excel. You can learn more at yfinance – PyPI.

Which is the best method to link Yahoo Finance to Excel?

Connecting Yahoo Finance to Excel can make your portfolio tracking more powerful. You can leverage the powerful analytics features of the spreadsheet tool and make better decisions for successful investment strategies.

There are multiple ways you can connect Yahoo Finance to Excel and automate the data sync. We have discussed each of them in detail above. The Python method may demand considerable technical skills and coding knowledge. You would also need an additional setup to run it on a schedule. Therefore, it may not be feasible for a non-technical person. The Power Query method may seem like an option here, but you may need to keep the Excel application running for real-time data updates using that.

In conclusion, the best method to export data from Yahoo Finance to Excel is using the Coupler.io platform. It is easy, feasible, and can automate the process through scheduling. It can also be used to transfer data from many other data sources – for example, take a look at our article on ClickUp export to Excel to learn how you can analyse project data more efficiently.

Zakhar Yung

A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼