Knowledge Base | How to Load Historical Stock Prices from Yahoo Finance to Excel

How to Load Historical Stock Prices from Yahoo Finance to Excel

Updated: August 30, 2019

Contents

Overview

Yahoo Finance was the most popular source of historical prices for Microsoft Excel.

Formerly, everyone could load data from a lot of exchanges using URLs like this

http://ichart.finance.yahoo.com/table.csv?s=YHOO&ignore=.csv

Yahoo Finance disabled this feature in May 2017.

However, Yahoo Finance has opened URLs of its web service that returns historical and intraday prices in JSON.

URLs for daily, weekly, and monthly historical prices:

https://query1.finance.yahoo.com/v7/finance/chart/YHOO?range=2y&interval=1d&indicators=quote&includeTimestamps=true
https://query1.finance.yahoo.com/v7/finance/chart/YHOO?range=5y&interval=1wk&indicators=quote&includeTimestamps=true
https://query1.finance.yahoo.com/v7/finance/chart/YHOO?range=max&interval=1mo&indicators=quote&includeTimestamps=true

URLs for 1, 5, 15, and 60-minute intraday prices:

https://query1.finance.yahoo.com/v7/finance/chart/YHOO?range=1d&interval=1m&indicators=quote&includeTimestamps=true
https://query1.finance.yahoo.com/v7/finance/chart/YHOO?range=5d&interval=5m&indicators=quote&includeTimestamps=true
https://query1.finance.yahoo.com/v7/finance/chart/YHOO?range=5d&interval=15m&indicators=quote&includeTimestamps=true
https://query1.finance.yahoo.com/v7/finance/chart/YHOO?range=1mo&interval=60m&indicators=quote&includeTimestamps=true

Use tickers like GBPUSD=X for currencies and like CK18.CBT for futures.

You can use our several products to load these data into Microsoft Excel in several ways.

Loading Historical Prices from Yahoo Finance into Microsoft Excel using SaveToDB Add-In

The SaveToDB add-in allows opening XML, JSON, HTML, and CSV from the web and converting data to refreshable Excel tables.

Moreover, you can customize URLs to change symbols using the ribbon:

Load Data from Web

Also, you can create and apply table views to filter data using various conditions directly in Excel:

Create Different Table Views

To load historical prices, click Wizards, Data Connection Wizard, select Web Data Provider and select an URL to connect.

URLs of historical prices from Yahoo Finance look this:

https://query1.finance.yahoo.com/v7/finance/chart/{Symbol=AAPL}?range=2y&interval=1d&indicators=quote&includeTimestamps=true;rootpath=*.*.timestamp,*.*.*.quote,*.*.*.unadjquote

You can see the defined URL parameter with the default value like {Symbol=AAPL} and the parser parameters like rootpath.

The SaveToDB add-in is a cool tool to load data from the web. It's available in a free version. Try it.

Loading Historical Prices from Yahoo Finance into Microsoft Excel using MARKET.RTD

MARKET.RTD is a specialized RTD server for loading data from Yahoo Finance and MSN Money.

You can create and copy formulas using Formula Builder that contains predefined data providers:

MARKET.RTD Formula Builder - Providers

You can select how to organize formulas, by rows or by columns, and paste them into Microsoft Excel spreadsheets.

Then you can format your model and get refreshable results like this:

Example of Yahoo Finance Quotes

You can get a lot of data using MARKET.RTD with the regular Microsoft Excel RTD formulas like these:

=RTD("market.rtd",,"YahooFinanceHistoricalPrices","YHOO","2016-12-30","Open")
=RTD("market.rtd",,"YahooFinanceHistoricalPrices","YHOO","2016-12-30","Close")
=RTD("market.rtd",,"YahooFinanceHistoricalPrices","YHOO","2016-12-30","PrevClose")

You can customize the refresh interval using the formula like:

=RTD("market.rtd",,"YahooFinanceHistoricalPrices","RTD_REFRESHINTERVAL",24*60*60)

Moreover, MARKET.RTD includes a lot of examples for available providers. So, you can adapt them to your needs easily.

Additional Notes

As described above, you can load historical prices into Microsoft Excel directly.

However, Yahoo Finance publishes intraday prices for the last trading days only.

If you need an intraday history for a larger period, you can download data daily and create the history yourself.

See KB: How to Load Historical Stock Prices from Yahoo Finance to CSV or
KB: How to Load Historical Stock Prices from Yahoo Finance to Databases

Downloads

Download SaveToDB Download MARKET.RTD

Disclaimer

Yahoo Finance is a logo and registered trademark of Yahoo! Inc.

Microsoft Excel is a logo and registered trademark of Microsoft Corporation.