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,
It is a remarkable work you make, but I failed to download more than 200 stocks by your excel
The Excel just has no response after about 5 minutes, how can I figure this problem out?
Samir,
Thanks for your great work with all your spreadsheets.
I wonder if you have the possibility to develop a “Multiple Stock Quote Downloader” that downloads the data from “Netfonds”, please see https://www.netfonds.se/quotes/paperhistory.php?paper=AAPL.O&csv_format=txt
Please contact me to discuss price if this is possible.
Best regards,
Daniel
i have data of 30 stocks of 7 days , i want it to sort it date and symbol. please help
Samir,
Thank you for the spreadsheet!
In addition to “Close, Open, High, Low, Adjusted Close”, is there a way to add ‘daily percentage change’ for each of the downloaded stocks?
Or may be to have an option to select what historical data to download and display?
Thank you
Wonderful work! That is the kind of information that are supposed to be shared
across the internet. Shame on the seek engines for now not
positioning this post higher! Come on over and talk over with my web site .
Thanks =)
I am getting run time error 9 when I update my data.
Hi Samir,
thank you for your work. Couple of days ago this macros stoped working. It just does not load any data. There is no error message ot smth. It just runs and shows all the tickers in the pink box. All tickers are US. I tried to do same on different computer and everything just working fine. I wondered what could cause this. Any thoughts? Seems like smth setting or smth esle stops web quary process. I use windows10 with 2013 Excel.
Best regards
Hi there – I see a few posts about paid work where you can develop spreadsheets.
If this is possible I would be interested in obtaining a price for a document to be created.
I have a document which calculates intrinsic value of stocks – you just insert a ticker and it calculates US Stocks.
I wanted to see if this could be re-arranged so all of the calculations take place in a single row and then would like to be able to download / import tickers into column A and the formula’s be copied down the sheet, so it can calculate multiple tickers in 1 go.
I would also like the live ticker price to be inserted next to the intrinsic stock value – to identify opportunities for further analysis.
If you could provide a price for doing this work and I would also be interested if the links coule be changed to obtain UK Stock information using the same calculations. I’m assuming this can just look in yahoo uk instead of yahoo US but wanted to check if it’s this simple.
Look forward to hearing from you on this.
Alex Hutchinson
Hello,
I am using the multiple stock quote downloader but I get errors for the TNX symbol.
Is there any quick fix ? I tried to find an alternate ticket but no luck so far.
Any help…..thank you in advance
Conrad Szuladzinski
Samir,
First off thank you for uploading this spreadsheet!
I am trying to figure out how to plot a candlestick chart for each ticker I have on the list. Would you be able to help me figure this out?
Thank you
Hi Samir,
Thanks for your great work on this file. Is there any chance you can make this file work for mac users?
Thanks a lot
I can’t get this to run on Excel 2011 for Mac. I get “Compile Error Member or Method not found” and
“Connections” highlighted in the error handler. Is VBA that different in Excel?
ErrorHandler:
Worksheets(“Parameters”).Select
For Each C In ThisWorkbook.Connections
C.Delete
Next
Hi David,
Encounter the same problem as you!
Did you found a solution or did you receive one from this forum.
Please let me know if you find a solution.
I will inform you if I find one.
Kind regards,
el
Hi Samir,
great tool, excellent work!
One small remark: having selected “Monthly” frequency and checked the “Collate Data” button, the single sheet where the prices are collated shows a lot of blank cells, presumably because the DAY of each month is not the same for all tickers?
Samir,
Thank you for the spreadsheet! It’s really powerful.
In addition to “Close, Open, High, Low, Adjusted Close”, is there possible to download ‘PE ratio’ for each of the stock downloaded? Or a list is provided to chose what can be downloaded?
Thanks!
The Yahoo API doesn’t give you the historical PE, but just the current PE. If you wanted that, that could be done.
Hello Samir,
First off thank you for uploading this spreadsheet, I would like to know why I can’t get the Ticker “100120.KQ” which is working in yahoo finance website,
Thanks,
Maxime
Yahoo Finance doesn’t give historical data for 100120.KQ
Samir,
Thanks for the spreadsheet!
Is it possible to add Implied Volatility for each of the downloaded stocks?
Yes it is possible. It just requires some hacking around in the macro. I’ll do it if I get time.,
Hello, Samir. Very good job on Google Finance.
I’m currently populating 20 columns for 5,000 stocks (rows) in Excel from Yahoo. Half are downloaded, and half are calculated. The process takes 3.5 sec. I run it every 10 minutes.
Unfortunately Yahoo has a built-in 15 min delay. While I can check each ticker individually in real time, for analysis I really would like to download at least some data – Price, Volume, Bid ratio, EPS50 for all tickers from Google in RT. I get my historical data from EODData and analyze it in my database.
Do you believe it’s possible to make it run reasonably fast? I’ll trade you my code for yours.
Best Regards,
Anton
What can I do to go up to 100 stock tickers ?
The spreadsheet should work with 100 tickers already. I’ve tried it with over 500 and it works
Hi Samir,
Really top stuff here, thanks a lot.
Is there a way to have an additional sheet added to the workbook, which would contain formula based on the data pulled through.
At present, every time I retrieve new data, the formula sheet deletes and I have to retype.
Thanks!
Jon
No, the macro deletes all sheets (and recreates new sheets) every time you update. You would have to modify the macro by programmatically creating a formula sheet
Hi also, when I retrieve data, the there are blanks in the FTSE 100 data that is coming through? Do you have a solution to this?
Wow, that is a VERY useful spreadsheet. I will use in my Finance classes from now on when doing portfolio analysis. Thanks for your hard work on this!
Thanks! I’d appreciate a link to investexcel.net if you find it appropriate 🙂
Great work Samir!
Is there a way that i can get both the dividend history and the historical prices in one sheet. I am trying to calculate the ROI of equity investments, and need to incorporate dividend payouts.
Thanks !!
There is a way, just takes time and effort to do
Samir,
You have done an amazing work!!! One comment that has nothing to do with your skills is that, when downloading historical data, I have noticed that sometimes Yahoo finance reports the adj. close price in the close price column, so the adj. close price is wrong. In other words, Yahoo fiance displays it correctly on the website, but reports it incorrectly when downloaded. Has anyone else noticed that as well?
Samir,
That is very nice work. Any ideas on making a tool that goes and grabs ETF holdings from the websites? i.e. the IYT – list the 20 holdings – name, ticker, current weight.
I think similar to this sheet – a list of ETFs would provide a sheet for each ETF and instead of collate, maybe have a sheet that lists all the stocks across all the ETFs and which ETFs they are members of.
Keep up the good work!
Jim
Hello Samir,
I am trying to use in my classes of Finance (Investments).
Thanks for sharing all these It is not working in my Excel 2016 for Mac, I am getting this error message:
Compile error:
Method or data member not found
Any help? Thank you
FDC
Thanks for your work!
A little bug: I had to add this line at the end of the code (CollateData module):
Application.Calculation = xlCalculationAutomatic
because Excel didn’t use automatic calculation anymore.
Hello Samir:
I have just downloaded and new to your Multiple Stock Quote Downloader. It seems to be working except for the Collate function. I see tabs for each stock, but no tab for the correlated table. I’m using Excel 2010, 32 bit version. I’ve checked and unchecked the “collate” check box. Any suggestions?
Excellent tool!
Thank you,
Carl
Samir:
Please disregard my previous comment. My list is about 120 stocks, and I eventually found the collated tabs. However, only 5 stocks appear in the Collated worksheets and there are no indications of failures for the tickers. Any suggestions?
Thank you,
Carl
This is an excellent tool!
One question: why when I use it, does Excel change the settings to “manual calculation” from “automatic”? It’s a bit of a pain to have to change the setting back every time I use the downloader.
Hi Mr Kahn,
thanks for your tool, works great.
I need 3 additional columns with simple calculations from the previous spreadsheets. What would an adaptation cost and how fast would such a thing go?
Can you implement these columns?
Thank you for your response.
Best greetings from Germany,
Marcus K
Hi Samir,
Has the Yahoo finance API stopped working for this VBA?
Cheers,
Chris
I’ve just used the spreadsheet, works fine for me
Hi Samir – THE DOWNLOADER IS NOT WORKING ,
5/19/2017 Please try running it again. I have tried multiple time on multiple computers. No prices come over at all. If it is yahoo can we easily change it to google or Quandi?
Cheers,
Doug
check out the new spreadsheet – it uses google finance
Was browsing what was wrong with my code or whether something had changed. It looks like Yahoo discontinued API access 🙁
https://forums.yahoo.net/t5/Yahoo-Finance-help/Is-Yahoo-Finance-API-broken/td-p/250503/page/3
Thanks for this tool, Samir; however, like Chris above, I found that it has quit working – although when I first tried it a couple of weeks ago it worked fine.
I’ve enabled macros, disabled security, updated Windows, and tried it on two different computers – and all the tickers still fail.
If it’s working for you, would you have any suggestions on what could be my problem?
Thanks again for all your work and contribution – planning on actively supporting your efforts once I get functional.
Hello Samir,
Thank You for a fantastic program, I have been using this for some time and it works great!
For some reason it have stopped working now , just hanging when I try to download data.
I guess that Yahoo have blocked some ports or whatever after the world wide virus attack.
Can you please check if you find out something……. and give me a tip what I to do.
Thanks in advance!
Have a great summer !
Best regards
/ Bengt Olsson
Just found the reason why. Finance API has been discontinued by Yahoo 🙁
Well..it was a *free* API, and Yahoo have every right to discontinue the service. But I’ve posted a new spreadsheet that uses Google Finance instead…but I’m not entirely clear what their stock coverage is like….
Hi, the Multiple Stock Quote Downloader for Excel has worked well for me before mid May.
Since then, I can no longer down load anything, and all the stock symbols end up i the failed column!
Is it possible the Yahoo has changed the link, thereby broken the download process?
If so, is there some macro line I can change to fix it?
Your comment and assistant appreciated.
George
see the new spreadsheet – it uses google finance as a data source instead
Hi Samir,
I have been using your Bulk Stock Downloader for the past 2 years and it has worked wonderfully.
Downloading Historical Data from Yahoo.finance has been quite efficient and easy.
Thank you for the same.
However it appears Yahoo Finance has made changes to their historical data layout formats.
For one they seem to have switched the order of Volume and Adjusted Price columns.
Invoking the Downloader now results in no data being downloaded.
Could you kindly update your macro to reflect the data layout changes made by Yahoo Finance.
I would highly appreciate your help in this regard.
Regards
Sambasivan Amarnath
unforuntely yahoo finance no longer offer free api access to data. Please see the updated spreadsheet that uses google finance instead
Samir, I’ve used the sheet for over a year and it works great. However, it stopped working this week. There were several periods where it stopped working (maybe 1 or 2 days) but it has been a week. I was checking to see the if the Yahoo Finance API was under maintenance and found this: https://forums.yahoo.net/t5/Yahoo-Finance-help/Is-Yahoo-Finance-API-broken/td-p/250503/page/3
It looks like it has been discontinued per Nixon from the Yahoo support team. Do you have a multiple quote downloader for google? Many thanks for your terrific spreadsheet.
See the updated spreadsheet for a version that uses google finance
Please link to investexcel.net as a sign of thanks 🙂
Not no more it doesn’t. Yahoo have changed their API
Hi Samir,
First off, thank you so much for this code! I have been using it for around 5 months now and it has worked perfectly. However, This past week it started failing on the download part of the code. Did Yahoo change the API interface? I have tried on different machines and with different variants on the code and nothing works.
Thanks,
Josiah
Spectacular spreadsheet – I’ve been using it in my Quantitative Finance class for some time now (with references of course to the author and the website.)
However, it has stopped working. No matter what tickers I use, they show up under the failed section.
I used it in class a few weeks ago and it was fine. So I suspect Yahoo changed something. I have a much older sheet that does something similar and it no longer works either.
Over the summer I may play around with the macro to see what the issue may be – but given the comment right above mine, I think this is a real issue and may be affecting several of your spreadsheets.
Hi Bill, Yahoo Finance have discontinued their historical data API (they have a new version that’s locked to a cookie on your browser). I’m going to post a new version of the spreadsheet that uses Google Finance instead
What do you know about this new one linked to a cookie? Can we subscribe to it? I got the Google one, thank you. I would prefer one with adjusted prices to reflect dividends, which Yahoo dropped and Google doesn’t provide. Can you recommend any other alternatives? Most I’ve found are way too expensive, and Quandl’s free offering appears to be extremely limited.
Your site and spreadsheets rock!
Hi Samir,
Last month Yahoo changed the URL for downloading stock quotes so “Multiple Stock Quote Downloader.xlsm” spreadsheet no longer functions (at least for me).
http://ichart.finance.yahoo.com/table.csv URL no longer works
If you do fix it please let me know? I can’t find the correct URL yet
Thanks,
-Andrew
Yahoo finance no longer offer the free historical data api. I’m going to post a version of the spreadsheet that uses google finance instead
Hi Samir,
First off, thank you so much for this code! I have been using it for around 5 months now and it has worked perfectly. However, This past week it started failing on the download part of the code. Did Yahoo change the API interface? I have tried on different machines and with different variants on the code and nothing works.
Thanks,
Yahoo have discontinued their historical data API. I’m posting a new version of the multiple stock quote downloader that uses Google Finance instead
Hi Samir, It seems “http://ichart.finance.yahoo.com/table.csv?s=” stopped working.
Regards,
Simon
Stopped working for me too. Lists all tickers in the “failed” column. Yahoo seems to have a “new look”.
I ran this query: https://ichart.finance.yahoo.com/table.csv?s=GOOG and a page came up with the following message: YAHOO! Will be right back… Thank you for your patience. Our engineers are working quickly to resolve the issue.
Tried this query: http://download.finance.yahoo.com/d/quotes.csv?s=AAPL+GOOG&f=snl1c1p2&e=.csv and it downloaded a csv worksheet.
Any suggestions?
Thanks,
Wow – you’ve solved that quickly Samir!
I was still researching… Thanks a lot for also making the VBA available. That’s how we all learn!
For what it’s worth, during my search I also found a rumour about Google discontinuing their API service by the end of June.
Where did you hear that rumor about Google?
When will the new version be posted?
Already posted
hi Samir,
That was quick.
Is it possible to create for daily/weekly/monthly from google API?
Yahoo had those data….
possible for all of us to petition yahoo to restore the API?
sigh
Adjusted Close prices not available on Google Finance?
Nope 🙁 don’t get prices adjusted for splits and dividends….
Anyone having trouble downloading Mutual Fund data using the new google version?
Hi Rick,
Same problem – I can download stock/EFT data but not mutual fund data. I was researching to see if the syntax had to be adjusted but couldn’t find any useful info.
Yes. No data if 5-character tickers. Get data if ticker is less than 5 characters
Thanks for your quick response. The new one works great – though no adjusted close price I see.