Multiple Stock Quote Downloader for Excel

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 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).

Excel spreadsheet to download multiple stock quotes from Yahoo Finance

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.

Yahoo Finance in Excel

The spreadsheet downloads the date, open price, high price, low price, closing price, volume and adjusted close price.

AAPL Yahoo Finance

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.

Yahoo Finance Stock Quotes Exported to CSV Files

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.

Yahoo Finance VBA

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.

stock quotes for multiple tickers collected onto one Excel spreadsheet

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


492 thoughts on “Multiple Stock Quote Downloader for Excel

  1. 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!

  2. 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

    1. 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

  3. 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

    1. 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.

  4. The new Yahoo spreadsheet keeps giving me “run-time error ‘429’: ActiveX component can’t create object.” How do I fix this?

  5. 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?

  6. 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?

  7. error 13:

    startDate = (Sheets(“Parameters”).Range(“startDate”) – DateValue(“January 1, 1970”)) * 86400

  8. 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

  9. 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

  10. 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

  11. 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

  12. Samir,
    thanks for this awesome spreadsheet.
    how can i add one sheet to file and not erase it when i run the macro?

    temiz

  13. 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

  14. 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

  15. 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

  16. 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.

  17. 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.

  18. 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

Leave a Reply to Samir Khan Cancel reply

Your email address will not be published. Required fields are marked *

What is 10 + 6 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)