This Excel spreadsheet downloads multiple historical stock quotes into Excel. Just enter a series of ticker symbols, two dates, and click a button.
Update on 15th September 2024. Yahoo Finance made getting at the data a little trickier, but nothing that a little time and effort can’t resolve. Get the new version below!
Update on 2nd May 2022: Fixed to work past some Yahoo changes.
Update on 14th April 2018: Yahoo changed a few things, so I’ve updated the downloader to work again
Update on 11th July 2017: You can now sort the historical data in ascending or descending date order
Update on 6th July 2017: I’ve uploaded a new version of the Yahoo spreadsheet that now works again. Download the new version at the bottom of this article. Comments welcome.
Update on 20th May 2017: Yahoo Finance have discontinued their free historical data API. As a workaround, I’ve posted a new version of the spreadsheet that uses Google Finance.
This data can be used for correlation analysis, technical analysis with RSI and ATR, historical back-testing, portfolio optimization and much more.
A previous spreadsheet let you download data for a single ticker symbol. Sometimes, however, data for just one company isn’t enough – you need to compare the relative performance of several companies. That’s when you need this free, user-friendly spreadsheet for bulk data download.
The spreadsheet is simple to use. Start by entering a start and end date, your desired quote frequency (d for daily, m for monthly, y for yearly), and your desired sorting (oldest data first or newest data first).
You can also specify if you want the data for each ticker written to separate CSV files. If so, specify an export folder for the files.
Then enter a list of ticker symbols in cell A11 and below (one tick per cell). After you click “Get Bulk Quotes”, the spreadsheet downloads the historical stock quotes into individual sheets. The sheet name is the ticker symbol.
The spreadsheet downloads the date, open price, high price, low price, closing price, volume and adjusted close price.
If you add or remove tickers , or refresh the data, the spreadsheet deletes the existing quote sheets, and inserts new sheets with the new data.
If you asked the spreadsheet to export the data, you’ll find a CSV file for each ticker in the folder you specified.
The file name is constructed from the ticker, start date, end date, and the download frequency.
If you misspell a ticker or leave a blank, the VBA is clever enough to skip over or ignore the error. You won’t get any nasty error messages.
You’ll also get a list of tickers for which no data was found. This list is dynamically updated by the VBA.
I’ve tested the spreadsheet by downloading historical quotes for 180 ticker symbols. Everything worked perfectly, with 180 new sheets added to the workbook, each filled with historical data.
You can also collate the open, high, low, close, adjusted close and volumes for every ticker on the same sheet. That is, you can collect all the open prices on one sheet (named “Open”) together with the corresponding dates, all the high prices on one sheet (named “High”) together with the corresponding dates etc. Simply check the “Collate” button.
The collate feature correctly reconciles tickers that return data with differing time series – dates and values are correctly associated.
The VBA is not protected; you can view and modify the code. Please let me know if you have any suggestions for improvements or additions to the functionality.
Get Excel Spreadsheet to Download Bulk Historical Stock Data from Yahoo
Get Excel Spreadsheet to Download Bulk Historical Stock Data from Google Finance
Hi Samir,
after downloading your latest version the problem was solved!
Regards and thank you.
Thank you for the latest update to align with Yahoo’s changes.
Thanks for the nice spreadsheet Samir.
This sheet has been referenced from a GitHub discussion of YLOADER that ran into the same Yahoo change (Adjusted Close addition) that you had to address. In that thread, someone mentioned that the last (or first, depending on the sort) date is unformatted in the worksheets. I believe that this is caused by a miscalculation in determining the last row just prior to the format set in the DownloadData sub. Specifically:
lastRow = Sheets(stockTicker).UsedRange.Row – 2 + Sheets(stockTicker).UsedRange.Rows.Count
should be
lastRow = Sheets(stockTicker).UsedRange.Row – 1 + Sheets(stockTicker).UsedRange.Rows.Count
Really, lastrow for this purposes could be simply equal to Sheets(stockTicker).UsedRange.Rows.Count, but I didn’t want to trace down all possible usages and why you wanted to include the starting row, so I made the simpler change.
Samir, and other users, just wondering if anyone is having issues downloading data since last night? While there is no apparent error, it seems like the spreadsheet cannot find any of the tickers.
Hi Samir,
The latest version worked just fine up to now.
Today morning it says :
finance”:{“result”:null,”error”:{“code”:”unauthorized”,”description”:”User is not logged in”}}}
Can you solve this error?
Regards, T
HI
Thank you for your kindly sharing,
it really helps a lot!
Unfortunately, this excel seems cannot use since today
Have Yahoo changed something?
Hi Samir,
Hope you are doing well. I see it’s been about 2 years since your last edit to the Multiple Stock Quote Downloader for Excel spreadsheet. Yahoo did some changes over the weekend and I now get errors that login is needed when using this.
When the getYahooFinanceData Sub is run, the following error is generated.
{“finance”:{“result”:null “error”:{“code”:”unauthorized” “description”:”User is not logged in”}}}
Is there a way to either bypass user login or pass Yahoo Finance credentials through?
New Links on the issue:
https://www.reddit.com/r/sheets/comments/1farvxr/broken_yahoo_finance_url/
https://www.reddit.com/r/googlesheets/comments/1fb38t3/script_stop_working/
Hi.
This is one of my favorite spreadsheets.
Unfortunately, some time in the last month it has “broken” and is no longer downloading quotes. I am sure a simply fix. . . .for someone that knows what they are doing 🙂
Thanks again!
This file stopped working last week with Yahoo Historical.
Hi Samir,
I tried downloading on sept. 12 2024 the:
Multiple Stock Quote Downloader for Excel and I still get no data.
Hey Samir:
yahoo must’ve made some changes again and the historical data is no longer downloading. Is there a workaround or fix to this situation? I’ve come to rely upon the downloader for my analytics/modeling. Thanks for the help!
Rick
Hi Samir,
Our version of the file has stopped working. All tickers fail suddenly. Can you suggest any corrections?
I’ve uploaded an update. If it works for you, please consider donating or linking to investexcel.net 🙂
Hi Samir, regarding the downloader .xlsm that is not working anymore for downloading data, I was able to capture the message from Yahoo:
{“finance”:{“result”:null “error”:{“code”:”unauthorized” “description”:”User is not logged in”}}}
Hi.
Multiple stock downloader yahoo is not working.
Maybe some update in yahoo finance.
Hi Samir,
I’ve noticed that the file hasn’t been working for the past few days. When I run it, the prices aren’t downloading as expected. Do you have any idea what might be causing this?
Thanks so much for your help.
This is an awesome product!
For what it’s worth, even the Yahoo website shows that the historical data is locked
from downloading. Clicking on the lock takes us to a list of premium services. I wonder if they are thinking about charging for historical data.
I was able to do a workaround by using the Yahoo Finance API in python to generate a CSV file for stock prices for a list of tickers. That still seems to work at least, but it requires the use of an IDE or Google Colab. Not as elegant as your spreadsheet
Thank you for the quick fix. Multiple Stock Quote Downloader has been a valuable tool for me. In the new update, the tab names for adjusted close price and volume are reversed, but this should be a trivial fix.
Hi Samir,
Thanks for updating.
Upon using, the ‘Adjusted Close Price’ page is not updated.
Please take a look.
Many thanks
As a general FYI, I took Samir’s original Yahoo Downloader application and modified it to provide the data I specifically wanted to see and in my preferred format.
With this new update (which is GREAT, by the way – THANKS!) I could not get my customized version to run with the new “getYahooFinanceData” sub.
I found out that I also had to import the VBA Dictionary from the same guy as authored the Json Converter. The Dictionary is located here – https://github.com/VBA-tools/VBA-Dictionary.
Just Download the latest release, unzip, and import Dictionary.cls into your VBA project.
Hope this helps!
Hi Samir,
Thanks for the Sept. 15 update.
Thought that you might like to know that I encountered a problem with the update when I had .IXIC and .DJI as items in my symbol list. They used to be handled previously.
Best Regards
I’ll look into this