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
Hey Samir,
I’m wondering, is there an addition to your existing code that could be made up to run the “Get Bulk Quotes” function again for the tickers that ended up in the “Failed” list the first time, only for the next time to add, for example, “NYSE:” before the ticker symbol in the list of stocks to run (in the Google spreadsheet)?
I have discovered (using ticker AAN as an example) that if I just put AAN in the included list of ticker symbols to run, it does not gather the data from Google and ends up in the Failed list, however if I enter “NYSE:AAN” as the entry, it does collect it and gets added to the Success list.
This would help tremendously if it could somehow loop at the end, using the Failed list as the new list of stocks to collect, and each time add all of the index prefixes that these stocks might be under, like NYSE:, NYSEARCA: or whatever other ones that there might be. Any ideas? I am adding something of a stock screener to your existing book and this is really the last thing I would want it to do in order to get as much data on as many stocks as possible.
Thanks a lot and love the sheets!
Any way to get real time or delayed stock quotes?
There’s this thing: https://investexcel.net/free-intraday-stock-data-excel/
I Samir. I get an error message when I try to use the Yahoo quote downloader. The dialog box says: “Microsoft visual basic”. ” se ha producido el error 13 en tiempo de ejecución. no coinciden los tipos.
And in the visual basic editor is in yellow:
tartDate = (Sheets(“Parameters”).Range(“startDate”) – DateValue(“January 1, 1970”)) * 86400
I had the same problem and solved it by substituting “1” for “January”. My excel language pack probably doesn´t support english in formulas.
I had also problem in Sub CopyToCSV with undefined variables, so I added the following lines:
Dim dateFrom As Date
Dim dateTo As Date
Dim frequency As String
Dim ws As Worksheet
Hi all,
I want to download financial statement from yahoo. Is there is way to construct URL to do that?
Hey Samir, and thank you for the excel 1
When I choose the option to save in the CSV file, and press the get quotes button , it gives me a Compile error: Variable not defined ?
do you know what can be the problem ?
Thanks
Samir, I thank you for fine work! Also I wish success! 🙂
I have the same mistake: “Variable not defined” at the same time is highlighted in the yellow Sub CopyToCSV color (). The mistake arises only in that case if to note “write to csv”.
Help to solve this problem please.
I’ve just resolved the CopyToCSV() error. Download the updated spreadsheet
I thank you Samir! Everything works! 🙂
The new Yahoo spreadsheet keeps giving me “run-time error ‘429’: ActiveX component can’t create object.” How do I fix this?
Are you using a mac?
I have the same error with PC
It’s runtime error 13, sorry. If it works, your sheet is really awesome!
Does this work with Excel Mac?
Other people have reported that the spreadsheet does not work on Macs
Any chance it can be developed to work on Macs? It used to. Thanks!
I just love this file. It really helps. I was using the other Yahoo download until it was rendered inoperable.
Q: When I run the import, the Locale on the Date format tab shows Afrikaans. How do I change the file so it is English on all the tabs without having to change all of them individually?
I would like to edit the code to pull different date ranges for each stock tickers but I am having trouble, could someone help me out on how to adjust the code?
error 13:
startDate = (Sheets(“Parameters”).Range(“startDate”) – DateValue(“January 1, 1970”)) * 86400
That bit works fine for me on Windows 10 and Excel 2010/2013
I’m getting the same mistake, error 13.
Is it possible that it might be related to the system date? So for example in some languages you right dd/mm/yyyy in others maybe it is mm/dd/yyyy.
So therefore if your date is not the same as the system it gives you an error? If this si possible what could be done to solve this error 13 issue? It only happens with the yahoo sheet as is the one that has this date modification.
I want to share it on my blog but I feel uncomfortable sharing things that are not working 100% for myself. Thanks.
Thanks
Hi Samir,
Great tool. It works in separate sheet within the spreadsheet but, if I click the ‘Write to CSV’ with proper folder to get separate file and then click on ‘Get Bulk Quotes’, I get an error under Sub CopyToCSV() – Compiler Error – Variable Not Defined at DataFrom = …..
Any recommendations?
Thanks
I’ve fixed that problem (didn’t declare some variables)
Hi Samir.
With new Spreadsheet to Download from Yahoo i have all time Microsoft visual basic error 13, can you help me?
Microsoft office 2013
Hi Samir
Can your sheet pickup stock prices for e.g. Danish stocks, like Vestas, ticker VWS, it’s available on Google as CPH:VWS, but I can’t get your sheet to read it, it goes in the Failed for These Tickers?
Thanks
Mikael
Hey Samir,
Thanks for updating the spreadsheet for the new Yahoo. I think you have the close and adjusted closing sheets swapped. I downloaded from Yahoo and compared to Multiple Stock Downloader Sheet. Please verify.
Thanks,
Terry
Samir,
thanks for this awesome spreadsheet.
how can i add one sheet to file and not erase it when i run the macro?
temiz
You need to modify the VBA
Hello Samir,
fantastic job. One question : I don’t arrive to add all french tickers
Google finance give me :
Compagnie de Saint Gobain SA => EPA:SGO
Air Liquide SA => EPA:AI
Sanofi SA => EPA:SAN
L’Oreal SA => EPA:OR
..
But all french tickers are failed. Do you have an idea ?
Thank’s
Hello Samir,
fantastic job.
Just 2 questions :
I don’t arrive to use french cotation EPA:SAN, EPA:SGO, EPA:MC, can you help me ?
How is it possible to download cotation of today without waiting midnight ?
Thank’s a lot
Hi Samir. I don’t want anything. I just want to say a big thanks for this program. I’ve been picking up a bit of VB knowledge along the way so I look forward to the occasional tweak if personal preferences get in the way of your great work. Thanks again. David
GREAT PROGRAM, HOW EVER I WOULD LIKE TO ADD, 50 DAY SMA, 200 DAY SMA AND CALCULATE A BUY OR SELL BASED ON THE MOVING AVERAGES.
Get comfortable with VBA and start coding 🙂
Hi Samir, I am running for the new Yahoo Downloader spreadsheet for first time today and I am getting a VB Compile Error “Variable not defined”. When I enter ok and it highlights ticker=ws.name. It captured all the individual data but did not generate the summary worksheets for Open/High/Low/Close.
Thanks for updating the spreadsheet for Yahoo, I had been enjoying the old one.
Sorry, I’ve fixed that error. Please redownload the spreadsheet.
Microsoft Excel for Mac (161215) upon request gives me:
“Run-time error ‘429’:
ActiveX component can’t create object”
What’s interesting is Apple built this into the latest Numbers with built in function to call historical closing price data.
Can you modify the excel so that first column is the name of the ticker ?
Not a big deal, but I found out the script errors out if you accidentally include two of the same symbol because it tries to create a new tab with the symbol. It was my fault, but just wanted to point it out.
Do you have any Excel spread sheet that downloads historical exchange rate data, which is still working? All the ones I found so far are not working.
Hi Samir, I tested with some code : Z74.SI, 5CP.SI …. got some problems with received data with NULL
I tried those tickers, yes – some data is returned as null in the spreadsheet
The actual Yahoo Finance website lists values of 0 for those values returned as null. The Yahoo Finance data is obviously screwed up somewhere
Hi Samir, Thank you for the excel. Is there any possibility to provide BULK stock quote downloading for intraday data from google finance.
I saw your file for downloading intraday data from google finance
and downloading bulk historical data from google finance. Why dont you provide bulk intraday data download?
In fact you could club all those files in to one single file where we can select the duration
fyi
in the bulk historical data download, there is a frequency selection option shown in your screenshot but in the file i downloaded that option is not there. Could you please check? thanks
Hi Samir: I have a question, does this spreadsheet (Google) work only with Excel or would it work with Open Office? Please let me know when you get a chance. Thank you.
I doubt if it works with open office
I also asked for a solution which provides historical stock data. I think the excel approach is powerful in that it is possible to manipulate the code behind. However, I found Stock Data Downloader helpful which is highly configurable but doesn’t require too much excel insight. So, it might be a helpful alternative to those who are not excel exercised so much.
http://www.insight-things.com/download-historical-share-stock-trust-index-quotations
Hi Samir,
A BIG Thanks from my side.
Is there a provision for downloading historical dividends for a given ticker symbol.
Sambasivan Amarnath
Look for the bulk dividend downloader on this website
Hi Samir,
great work and thank you,
how can I sort the historical data in ascending or descending date order?
also, is there a way to get only the close for every ticker on the same sheet without having a separate sheet for every ticker?
Again thank you very much
Samir,
Thank you for adding the Yahoo! sort by date feature! I downloaded the latest version on 8/14/2017 and it appears to me that the adjusted closing prices end up in the “Closing Prices” tab and vice-versa. Can you please verify this? The data in the “Adjusted Closing Prices” tab are too pristine – that is, they are not being adjusted for the dividends being paid.
Thanks,
Steve
I like the application you have. It is almost exactly what I’m looking for. One thing that would be nice, for me, is to have a simple stock name for the filename. Instead of “AMD 15-08-2016 – 15-08-2017 d.csv”, have an option to name it “AMD.csv”. Sure I can write a script to change the file names automatically, but if it was in your code, then I don’t have to :).
But in any case, nice j ob.
It’s the VBA, just modify the CopyToCSV subroutine
Hi Samir,
Great work on all your tools and spreadsheets!
I’m having an issue with YahooStockQuotes on my Mac (previously used to work 3-4 months ago).
When I run the macro I get: “ActiveX component can’t create object”
I redownloaded your sheet. and also downloaded and installed latest MS Office for Mac updates.
Do you have a new Mac friendly version of this tool (or a Google alternative tool that similarly downloads historical prices?).
Apologies if someone already asked this on your forum (I read through comments but couldn’t find the exact same problem with your solution).
Thanks in advance for your help.
Soujit
Hi Samir,
Thanks for the new excel sheet. I think there is a mistake in coding. The last date’s format is always wrong. For example, download daily from 7/30/2017 to 8/25/2017, 8/25/2017 date is not formatted correctly. I’ve tried a second time today, from 7/30/2017 to 8/29/2017, 8/29/2017 date is not formatted correctly but 8/25/2017 is correct. Anyone else experiencing this? Thanks.
Samir,
Thank you for this tool. I really do appreciate it.
One point: I think you have the Closing Price and Adjusted Closing Price Columns reversed. At least that’s how I’m getting the data.
fixed in the latest version
Hi Samir,
Really appreciate this spreadsheet, it’s a great time saver! I’ve come across a bug when the stock symbol LOW (for Lowes) is used. This will prevent the Close, Volume and Adjusted Close tabs from being generated, probably since you are already using the name ‘Low’. A temporary work around this problem is to use the stock exchange & symbol (NYSE:LOW).
I’ve tried LOW with the latest version. Appears to work fine – the close, volume, adjusted close tabs are all generated as expected.
Hi I have do both Google and Yahoo versions and have the same issue:. It won’t update today 9/6/17… They both only update until yesterday; however, Yahoo does have 9/17 on its manual del… Any ideas? It’s worked fine the past month.
Erick
Hey Samir,
I looks that the Google API also stopped working.
Is it an idea to integrate AlphaVantage in your sheets instead of Yahoo or Google ?
https://www.alphavantage.co/
KR,
Pascal
Yes – Samir – awesome job on the Excel sheet. I tried modifying your code to get it to work with alphavantage.co. But haven’t found a way yet. With Google broken also, could you make it work with alphavantage?
Hi Samir
I am using EURUSD=X as ticker, however the Open, high, close & low values are not accurate. How is this possible. Can you pease check the Yahoo Finance Excel sheet?
Hi, is it possible to change the output file format from .csv to .txt files and the data format to yyyyMMdd;open price;high price;low price;close price;volume
Hi Samir,
I am using the Google query for historical stock data and it seems that it stops at 9/5/2017?
Any ideas?
Thanks,
Dov
Hi!
Questions on slight mods to this excellent product.
I would want to create a portfolio worksheet that “persisted”. I would also want to append new data to an existing worksheet rather that “re-fetch” data I already downloaded. Lastly, it would be good if it were possible to merge all of the stocks into one worksheet, adding a column to the left edge and putting the ticker into that column.
Thanks! Appreciate the great work and your thoughts on this.
Rod
P.S. if you can extend the documentation to your code i could work on that myself. Again, love this tool.
I am getting the error “Run-time error ‘-2147012889 (80072ee7)’: The server name or address could not be resolved” when i run the Yahoo version.
Any chance someone knows the solution? When I debug, “.send” is highlighted in the “Sub getCookieCrumb” macro
Thanks!
Hai samir… your file very help me.
but how i can automaticly copy and paste the historical price to the exist sheet without make many new sheet. because at my exist sheet there are formula. thank you
Hello Samir,
Fantastic support e work, can you give some tip to capture European quotes?
Example ELI:BCP is Null
Any tip, any name adaptation
Hope to get your contact
Thanks
Hi Samir, I would like to bring it to your notice that it stops the below line
ErrorHandler:
Worksheets(“Parameters”).Select.
Could you please look in to it. And do you have the same file ditto for google intraday for bulk stocks?
I got the error code 429, which states that it can show Active-X components on my mac, when I try to run the macro. I am working with Excel 2011.
Can somebody help?
Hi Samir,
when i run the code it works perfectly with most of the tickers. but with some of them it just stops and returns 1004 error. for example, when i choose these parameters:
Start Date 2016-10-06
End Date 2017-10-06
Frequency d
the code stops when it copies data for CO from 25/10/2016, second column (“High”). i.e. it copies the data from 06/10/2016 to 24/10/2016 and then stops on the row “25/10/2016”
When i delete this ticker, all works fine
could you please check what can be the problem? I would really appreciate if you can fix it or at least let me know what can go wrong here
thank you!
Hi. I’ve dl your most recent bulk downloader using excel, also the hourly data downloader.
I recently got rid of a Dell xps15 and replaced it with a Maingear laptop. Dell kept blue-screening.
Now, excel continuously crashes. BTW it only crashes when I have your software running, especially the hourly. I believe it to be an Excel issue – I’m using 64-bit 2016 MSO Excel. Any ideas?
Unfortunately the VBA script stops after a few seconds as Yahoo changed something on their side. This goes for the file I have in use for years but also for the blank template. It’s documented on Github but way above my head. Hope you’re able to fix it yet again! Tnx Samir…
Check out the updated version
Hello Samir
I use this spreadsheet every month to download closing prices of a 50 stocks for the US and Canadian markets.
Today, for the first time, it stopped working and a Window appeared on screen:
When I run the “Debug”, it shows me programming stuff that I do not understand.
I did email you the same message with pictures of the Windows that appear.
I am not a programmer, so this stuff is beyond my comprehension!
Is it fixable? Let me know please.
Truly appreciate this tool.
Kindest regards, Erwin
The latest version fixes the changes made by YAhoo
Hi,
In the WB for Yahoo Since last week the routine returns a “runtime error” at this line “cookie = Split(.getResponseHeader(“Set-Cookie”), “;”)(0)” requesting a valid CRUMB.
I suspect something changed in the WinHTTP.dll. What’s exactly happened?
Please download the latest version