How to Export Yahoo Finance Data to Excel on Schedule – The Ultimate Guide
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.
Note: In this tutorial, you need to write a custom API request to get the required data from Yahoo Finance API to Excel. Coupler.io is not responsible for incorrect information or connection errors because of wrong details. Contact us to request a setup for custom Yahoo Finance integration.
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.
To link Yahoo Finance to Excel via the Coupler.io platform, create a free account. Log into your account dashboard and create a new importer with JSON as the source and Microsoft Excel as the destination. This will enable you to fetch data from Yahoo Finance to Excel by calling the API.
Source
- Enter the Yahoo Finance API endpoint URL in the JSON URL field. For example:
https://query1.finance.yahoo.com/v10/finance/quoteSummary/AAPL
- 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
- Click on Jump to Destination Settings.
Learn more about the URL parameters to get data from Yahoo Finance to Excel.
Optionally, you can also specify the required Columns and Path of required data.
Destination
- In the Account, connect your Microsoft OneDrive account.
- Select the Workbook and Sheet where you want to export the Yahoo Finance data.
- Click Jump to Schedule Settings to proceed further.
Optionally, you can also configure the following additional settings for the destination:
- Cell Address: Specify the target cell address of the Excel sheet.
- Import Mode: Choose to replace or append the new data in the Excel sheet.
Schedule
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 Save and Run 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 specifying the same in the Columns field in the Source settings. Edit the importer and add the required column name in the respective field.
Now, click on the Save and Run 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.
| Data entity | 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 |
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:
| URL query string parameter | Description |
|---|---|
| symbol: {ticker} | Provide the ticker symbol. |
| period1: {unix-timestamp} | Start date in the Unix timestamp format. For example, 1617179455 is the unix timestamp for Mar 31 2021 11:30:55t. |
| period2: {unix-timestamp} | End date in the Unix timestamp format. |
| interval: {interval} | Specify the time interval, for example:– 3mo denotes 3 months– 1d denotes one-day |
| includePrePost: true | Add data before and after the market. |
| events: div%7Csplit | Add dividends and splits. |
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 yf
start_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 file
data.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.
