198

# Multiple Stock Quote Downloader for Excel

This Excel spreadsheet downloads multiple historical stock quotes from Yahoo Finance into Excel. Just enter a series of ticker symbols, two dates, and click a button.

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, and your desired quote frequency (d for daily, m for monthly, y for yearly).

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

Filed in: Portfolio Analysis

### 198 Responses to "Multiple Stock Quote Downloader for Excel"

1. Kanu Bhana says:

Hi Samir -thank you for kindly providing your excel spreadsheet for downloading multiple tickers. I would like to use this for screening stocks and displaying the price on a chart but my programming skill is not very good.

If you have spare time could you add two more sheets to your spreadsheet which allows me to select the ticker from a drop downlist which I can perform calculations on and plot these on a graph on a second sheet? Thank you again for providing your spreadsheets and support with this enquiry.

kind regards,

Kanu

2. John Heineman says:

Could the code be modified so that;

1. a sheet named Charts could be created and not deleted,
2. the data only appends new data if existing data exists instead of always downloading entire date range every time.
3.Existing sheets not be deleted, just append new data to the specific cells needed so that user created formulas would be saved

Thanks!

3. REsearch Junkie says:

Hi – Samir…your site is amazing! thanks for all you do…

One question on this downloader, can you post a code that will download the data sorted from newest to oldest based on the date? I went into the code but since I am not that savvy I couldn’t figure it out..

Thanks!

• Samir says:

In the VBA, change Order:=xlAscending to Order:=xlDescending

4. REsearch Junkie says:

I was able to modify it to descending. I tried adding some other calculations but I am getting an error.

How Do I contact you for custom work?

• Samir says:

Go to the privacy policy page, and use the email address there

5. Jake says:

Samir thanks so much for the code.

I am looking to do the following:

Instead of having all of the entered tickers appear in separate tabs, I would like to have the tickers appear with the respective information in the same tab…. For example: If I have 50 stocks entered on the “Parameters” tab – What code must be entered to enable me to view those 50 stocks in the same new tab (within the same workbook)..

I essentially want to be able to have a spreadsheet that I can enter as many stocks as possible into and have real-time data within the spreadsheet that I can view on one tab…

If you could let me know how to execute this I’d be forever grateful!

Also, if you have time… How do I change the criteria that refreshes and is visible for each ticker? For example, if I want to add Short Interest and Average Daily Volume and Sub-sector or Industry, how can I do this? I am fully subscribed to FinViz and if you could explain to me how to change the criteria within VBA or the spreadsheet that would be so great.

Thanks so much.

• Rick says:

I too would love to see all quotes on the same tab if I was looking at one day worth of data.

• Karan says:

Hi Samir – like Jake and Rick, I too would like to see all data for all dates entered on the same tab (usually entering one week).

E-mail: karan.desai@td.com

6. Gordon says:

Hi Samir, lot’s of good stuff on this site. I’m looking to do some specific live quote stock analysis. I’ve browsed through the Excel import spreadsheets you have on here and am not sure if I found what I’m looking for. Here’s what I hope to do:

Import live quotes and associated data (delayed is ok) from Google or Yahoo for a selection of some 50 companies. I want to be able to choose the companies at the outset, as many are either quite small (and not on the list of 6800 you have on one particular spreadsheet) or in foreign markets .

Did you already do something like this? If not, can I modify one of your current spreadsheets relatively easily? I have some limited VBA skill.

Thanks very much. Again, great work.

• Jandro says:

Hola Samir,

Muchas gracias por ofrecer la hoja de cálculo. Cuando pongo el ticker de un valor del Ibex35, la hoja de cálculo me dice que no los reconoce. Podrías, por favor, decirme como tengo que hacer para poder importar los valores historicos de acciones del Ibex35.

(Translated)

Thank you very much for providing the spreadsheet. When I put the ticker of a value of Ibex 35, the spreadsheet tells me it does not recognize. Could you please tell me how I have to do to import the historical values ​​of the Ibex 35 shares.

In advance, thank you very much.

7. Marcus says:

Great job Samir. This spreadsheet is obviously perfect. Can I possibly get the one to Download Bloomberg Stock Quotes into Excel? The reason being that the Tickers I am looking for only exist on bloomberg.

Thanks a lot.

8. Russ says:

Samir,
Great job.
Here’s an idea for added functionality.
I plan to use this to update prices for the same tickers on a regular basis. I would like to add columns to the right of the Yahoo price output, in order to calculate certain items (high minus low, moving averages, etc.). But currently, these formulas would be written over each time I update the prices. Is there a way to have the range limited to a certain number of columns, so that calculations I add to the right of the output on each worksheet do not get erased?
Best,
Russ

• Samir Khan says:

Hi Russ – yes, you can modify the VBA so that your calculations (MAs, high minus low etc) are added to the right of the Yahoo prices.

Look into the FormulaR1C1 syntax in VBA. It’s pretty simple.

Samir

• Pari says:

Hi Samir,

Good job done!

Can you please tell me how to update only certain columns and retain balance while updating everytime.
Because I have made some calculations inserted in columns which should be retained after updating the fields also. In addition, how to update only few spreadsheets out of many.

Thanks,
Pari

• David says:

hi

thanks a ton for the great utility, untill now i had to download through http://ichart.finance.yahoo.com/table.csv?s=

individually each stock , so you can understand the pain

am i able to import data from this sheet or create csv from sheet automatically according to mt4 format ?

regards

• Samir Khan says:

I don’t know what mt4 format is

Samir

9. Tom K says:

Hi Samir,

Is it possible to insert the ticker symbol in the Column A on each worksheet? Also, is it possible to have all results inserted into one sheet, instead of individual sheets for each stock?

Thanks,
Tom

• Samir Khan says:

Hi Tom

Yes, it’s possible to do all this. Are you asking that I do this?

Samir

• Michael H says:

Having the option to download all of the data to a single worksheet would be a huge help. I’m working with a few other tools that really need to be able to reference the data side-by-side and while I can make a sheet consolidating all of the reference cells onto one page, this really slows down the processing and makes the document almost unworkable.

• Edward H says:

Hi there,

I would like to second Michael H’s request. That will be really helpful if the data can be combined into a single worksheet. Furthermore, if the data can even contain historical fundamental information that will be even better. Thank you so much.

• Keith says:

Samir,

Thank you so much for your work on this. It really is a phenomenal tool. Could you please make this go into one spreadsheet as the gentleman above mentioned? If not, could you point me in the right direction. Thanks so much again!

• Monte says:

I would like to +1 that request.

An example of how this might be used:
Comparing prospective buy opportunities.
- run a stock screener
(some fields are not available in results, and some needed filtering is unavailable)
- paste all the symbols from screener output
- limit date range to 1 day
- run, filter and sort

Thanks for your excellent work Samir. Also, may I add a link to your site from my resources page? http://nFol.io/resources
Monte
nFol.io

10. REsearch Junkie says:

Hi Smair, great work as always…I am not a VBA specialist like yourself. I was hoping you could walk me through how to have each ticker create its own csv file in a specified folder; if this is even possible.

Thanks and I do have some custom work to be done. I’ll be reaching out when I finish a few things.
As always thanks for your help,
RJ

• Samir Khan says:

Hi Research Junkie

I’ve updated the spreadsheet so you have an option to export the quotes for each ticker to separate CSV files.

Samir

11. Ed says:

Hi Smair,

Great work.

I am new to VBA. I don’t know why this works fine with my PC running Office 2010 but it does not work in Mac Office 2011 prompted with error in the syntax StartMonth = Format(Month(StartDate) – 1, “00″)

I have changed the date format on the Parameter sheet but still could not solve the problem.

Could you let me know how to solve this? Thanks!

• Samir Khan says:

Long time in the coming, but the new version I’ve just uploaded should work on a Mac

12. MOHIT says:

Hi, Samir
I am not able to download the data for nifty and other futures stocks in India market
Basically I want to determine the volatility of any particular stock in Indian market and use it in black scholes formula to find the value of option and check its variance from the original data
Can u help me out in that regard

• Samir Khan says:

The spreadsheet connects to finance.yahoo.com. If finance.yahoo.com doesn’t have historical prices for your Indian stock ticker, then the spreadsheet won’t work for you.

13. Epul says:

Hello Samir.

Im very appreciated your big work here…Extremely usefull to me…
Before this im using this link “http://table.finance.yahoo.com/table.csv?s=^KLSE&a=01&b=01&c=1998&d=3&e18&f=2013&g=d&ignore=.csv” to download 200 stocks data every day… need to change the stock name and the date for every single stocks…Lot of time…

Today, i found ur work may save a lot of my time.. Thanks to you.. and thumbs up..
seperate tab of every stock, can i export it into seperate .csv?

• Samir Khan says:

Hey Epul!

I’ve added an “Export to CSV” option. It exports data for each ticker to its own CSV file, in a folder that you specify

Samir

• epul says:

Thanks a lot Samir…

Big thumbs up for you for your effort…
Really appreciate it mate..
Recomended to all… =)
Regards.

14. Ken says:

Thanks a lot, but it does not work on my Mac. I got Office 2011, but it won’t even run. What to do?

• Samir Khan says:

Don’t know why it doesn’t work on a Mac. I only have a Windows 7 laptop. Not rich enough to buy a Mac

• Karl7 says:

The first thing you will need to do is enter you stock symbols in Row A10 not A11
The program begins looking for stock symbols in line 10 when it finds the
Enter Tickers Below comment it tries to look up the symbol and bombs out.
(this may not be the big error see below but it won’t hurt)

Also

In the “Sub DownloadStockQuotes” portion of the code comment out these lines
‘ .PreserveFormatting = True
‘ .RefreshPeriod = 0
‘ .WebSelectionType = xlSpecifiedTables
‘ .WebFormatting = xlWebFormattingNone
‘ .WebTables = “20″
‘ .WebPreFormattedTextToColumns = True
‘ .WebConsecutiveDelimitersAsOne = True
‘ .WebSingleBlockTextImport = False
‘ .WebDisableDateRecognition = False
‘ .WebDisableRedirections = FalseL

After that it ran fine for me.

This was a machine specific error no reflection on Samir Khan.

Samir Khan you did a good job, thanks for the code.

Good luck.

• Samir Khan says:

Thanks for spotting the issue with row 10 vs row 11. I’ve fixed it!

• Amitesh says:

Karl7,

If data for particular stock symbol is not available then the code is getting stuck at refresh.backgroundquery:=False

After that it is not continuing with the loop.

This is happening only with mac. With windows the loop continues even-if data is not available for any stock ticker.

• Smkbash says:

Dim ticker As Integer

(I did this based on an error i got on the version of code that was provided that downloads all quotes to a single sheet.)

Then made the other changes Karl7 submitted

It worked, Mac changeover has been a headache

Thanks Samir, way farther than i would ever be if it wasn’t for you. Karl7 thanks for the Mac translation

• Samir Khan says:

I’ve just added your Max fix to the most recent version of the spreadsheet. Many thanks for looking into this.

• DLB20720 says:

Ken, I’ve been doing a lot of research on pulling Yahoo info into Excel and I remember reading something along the way that some macro functions are not supported on Mac / Office 2010 products. I’ve read 1,000 websites over the last few weeks so I can’t remember where I saw that but you may want to look in the Microsoft Developers Network information.

15. john k says:

Hi would it be possible to merge this spreadsheet with the one compiling the correlation matrix? it would be very handy to just specify a stock symbol list and get directly the correlation matrix in the same worksheet..

• Samir Khan says:

Yes, that’s what I’m aiming for!

16. A.B. says:

How do you get rid of the portion of code that removes worksheets without getting a Error 1004? I’d like to add this to an existing workbook. Thanks!

17. Steve says:

Hi Samir,

Thank you so much for creating this… this is exactly what I’ve been looking for… however, do you know if there is a way we can edit the code so that it does not delete all other prior tabs? I have my own Summary tab, along with others for Charts that I’ve created, but each time I run the macro it deletes all other tabs so I have to keep my work in a different file and then try to merge the two after it gets done running. I’ve tried altering the code to not delete the other tabs, but I keep receiving errors. Any help would be greatly appreciated!

Thanks again and regards,
Steve

• Samir Khan says:

Any data in columns to the right of H is preserved. You HAVE to make sure that the tab for every ticker already exists before you update the sheet (i.e. there has to be a tab whose name is the ticker symbol)

• Jeff M says:

Hello Samir. First, this spreadsheet is exactly what I was looking for!! Thanks. I am not a expert in VB but I am learning and this is a time saver. I tried the version that preserves data and it didnt download any new price data.

I got it to write the data in decending order by changing the Sort function. (Order:=xlDescending).

I need help with one small change to it. for every ticker, it clears the sheet that the data is written to.
My plan is to use this to weekly download ticker data into columns A:G and I will have formulas in Columns H,I,J,K, etc…. and I want these to be preserved.

It would be great if there was a “check box” on the Paramerters page that, if checked, wouldn’t erase anyting to the right of column G for “existing” tabs. I may add new tickers from time to time.

Can you help me with adding this? I attached a modified copy of your spreadsheet that has two changes. 1. the end date on Parameters is Today(). 2. the sort order is decending on each tab so the newest data is at the top.

18. David C says:

Hi Samir,
Your multiple stock downloader has been very helpful. It seems to work well except for the monthly frequency option. The start quote date is correct as input but the remaining quote dates are all at the first day of the month. I have been trying to find out how to correct this but I am not experienced with VBA. Can you help see if and how to correct this?

• David C says:

Hi Samir,
I worked on the m frequency problem a little more and found that yahoo finance is actually downloading the data for the end of each of the months. I would like to get data at monthly or weekly intervals for a spreadsheet tracking portfolio yields at the specified intervals. Like starting on 9/12/12 with data on 9/12/12, 10/12/12, 11/12/12, 12/12/12, 1/12/13, 2/12/13, 3/12/13, 4/12/13. Looks like I would have to get the data for the d frequency and parse it for monthly or weekly intervals? Thanks for the start though.

19. Sam says:

Hi Samir,
Great work – was looking for a multiple historical quotes downloader!
Are you interested in adding other calculations to the tab sheets?
Example: Standard Deviation, Beta, Volatility?

20. Kevin says:

Hi Samir,

To follow up on Tom K’s request above, how should I modify the code to insert the ticker into column “A” of the output? I use these files to dump data into some back testing software, and it needs the ticker in one of the columns in order to process the data

Also, is there any way to allow this script to overwrite any .csv files that are already in the output folder? (As it is right now, we just have to manually click “yes/no” for each of the folders that are already in the folder.

Many thanks for your hard work, this is HUGE help to me!

Hi Samir,

Thanks for sharing your work with us.

I am relatively new with VBA and am planning to build a Excel sheet to download historical quotes from Yahoo and Quotemedia, because certain ticker symbols are not supported by Yahoo anymore (for example ^DJI, ^DJT, ^DJU, ^DJC).

Do you if there is of you have a solution for this problem?

I am a Metastock user and would like to have a input file in csv format containing the price data from the last 20 day for a list of tickers symbols.

The Quotemedia VBA code I am planning to use is:
&startYear=2002&endDay=02&endMonth=07&endYear=2009&isRanged=false&symbol=IBM

Thanks and have a nica day,

22. Martin Galparsoro says:

Excellent work Samir!!

Quick question: is there any way to keep a formula that uses the database you pull from Yahoo. I mean, if I’m calculating the daily return on any symbol, I’d like to automatically re-calculate the return, without having to recreate the formula…right now, everything I putt the data from Yahoo using VBA, my formulas go “REF!”.

Thanks,

Martin

• Samir Khan says:

With VBA, you can programatically enter a formula (automatically after you update the sheets with new quotes). It’s pretty simple

23. Jason says:

Hey Samir,

Thanks for the GREAT post!

I just wanted to point out one thing.
In the code, you have:

Dim StartMonth, StartDay, StartYear, EndMonth, EndDay, EndYear As String

When you do that, EndYear is actually the only variable defined as a String.
The rest of the variables are actually initially defined as Variants.

So if you put:

Dim StartMonth, StartDay, StartYear, EndMonth, EndDay, EndYear As String
MsgBox TypeName(EndYear)
MsgBox TypeName(StartMonth)

If it is before you assign StartMonth to a string, it will return two different data types.

It doesnt matter much since you almost instantly assign StartMonth but if you have more complex scripts, you may run into problems.

24. Doug says:

Samir-

Love the worksheet. I’m not very good with VBAs and would love a summary of closing prices for each ticker on the Parameters page.
My issue is that the VBA runs so slowly that 25 tickers took 15 min to populate. Not sure if it is the indirect function or something else. Any thoughts on what to add to your VBA to automate this? I’m on a win7 64bit machine with 6GB ram and using Excel 2010

Example: starting in column J,

column J K L M N O P

Date ASCA BYD BYI DDE IGT ISLE Row 1
12/31/2010 15.63 10.6 42.19 3.4 17.69 10.22 Row 2
1/3/2011 15.52 10.81 43.02 3.46 17.99 10.45 Row 3
1/4/2011 15.5 10.72 42.59 3.45 17.96 10.08 Row 4
1/5/2011 15.79 11.65 42.9 3.48 18.61 10.4 Row 5

to get the dates I used: =IF(INDIRECT(“‘”&$A$11&”‘!A”&ROW(C3))=””,””,INDIRECT(“‘”&$A$11&”‘!A”&ROW(C3)))

and for the prices for ASCA in K1 I used:
=IFERROR(VLOOKUP($J2,INDIRECT(“‘”&K$1&”‘!$A$3:$E$2500″),5,0),””)

Thanks

25. Amitesh Kapoor says:

Dear Samir,

It’s such a great work. If it is possible to have the same thing for multiple tickers for google finance data. You have it for single ticker for google finance but not as this one.

Amitesh

26. Jose M Vilchez says:

Samir, the worksheet is great, exactly what we all need, in my case I am an amateur investor but as I am an engineer I really enjoy calculating ratios to compare possible investment options and different portfolios. In fact for matrix and correlation calculation your tool is incredible, it has solved all my problems to get data.

QUESTION:
I have searched some spanish stocks with the Stock ticker, i.e. TRE.MC and it works, however I did not have good luck with ETFs or funds, it simply might be due to the nomenclature used by yahoo finance or due to the funds I search for not being in their data base.

Could you give us some advise on how searching other products with their ticker, ISIN or in other ways if possible? If it were not possible, I suggest you could update your tool or create a new one for ISINs it would be really interesting for a full set of investors.

27. Tahleel says:

Hi Samir,

I was looking for a tool to download historic data and came across your site and I am impressed. Great work! I was trying to modify the code, but I’ve been unable to do so. Like others have said, is there a way to have tickers in Column A for a single date, rather than dates in Column A for a single ticker (as it is now)? Ideally, I would like to have the following format:

http://oi39.tinypic.com/2zr2mhe.jpg

Please let me know if you can help.

Thanks!

Tahleel

28. Amitesh Kapoor says:

Just amazing work….Samir….Can I have the same thing. i.e. multiple stock downloader for google finance.

Amitesh

29. Erik says:

Hi Samir,

I have just one question:
How can I change this code, that I become date format like this: yyyy-mm-dd and the rows are sorted newest to oldest?

thank you

30. Erik says:

Hi Samir,

it’s everything very good, but I have just one question:
I want to become date formate like this: yyyy-mm-dd.
How can change this code?

thank you

Erik

• Samir Khan says:

31. ramanattar says:

I would like to third Michael H adn Edward H’s request. That will be really helpful if the data can be combined into a single worksheet. I’m using Amibroker and this feature will be time saving.

Thank you for this awesome worksheet.

32. Dennis says:

Hello Samir, I was hopeing you could help, I have no programing experiance and am looking for a way to download just the closing prices for a number of stocks everyday. I would like to be able to update the list the next day while saving the previous days and weeks information. All the while having it be one sheet.

thanks so much
Dennis

33. Mike C says:

HI

Great work on this Samir. Can you tell how to amend the VBA code to look at UK shares please? I’m entering PFD for Premier Foods and I’m getting back Flaherty & Crumrine Income Fund from the NYSE.

34. Andrew Z says:

This was excellent and very well done.
I wondered if you had code that puts the data into a single csv worksheet.
That is, instead of a sheet for each company, the data on one worksheet.

Or perhaps you have a tip for me to modify the code.

I want to download the entire S&P 500 and then suck the data into some analysis.

Anyway, you are great!

• Samir Khan says:

Try the updated worksheet at the bottom of the post, and the new “Collate” checkbox

35. Johnson says:

The macro was just exactly what i have been looking for.

But i have encounter with a slight problem on this part

Sheets(stockTicker).Sort.SortFields.Add Key:=Range(“A3:A” & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

It keep showing runtime error “438″.

I’m using MS Excel 2003.

Thank you

• Samir Khan says:

The spreadsheet runs fine on Excel 2010. Do you have access to this version?

• StanGrayson says:

It’s not working for me, once I put the stocks in, all of the other tabs disappear and none for the new stocks reappear

• Samir Khan says:

I’ve just tried the spreadsheet in Excel 2010 and it’s works for me. What version of Excel are you using?

• After downloading the “Multiple Stock Quote Downloader with CSV Export and Collate Data.xlsm” file and opening it in Excel 2011 Mac, when I click on the “Get Bulk Quotes” button, it processes for a few seconds and then reports:
No Data for these Tickers
WMT
KKR
KO

Your “YahooStockQuotes.xlsm” works fine.
I’m not a VBA whiz, but have debugged C/C++ & Perl. So I’d be willing to do a couple of tests if it would help find the cause. Searching for “Error” got me noplace.
Do you have any suggestions?

Mac OSX 10.7.5, Excel.Mac 2011 v14.3.2

Thanks, Tony

PS If it works, I’ll donate:)!

• Samir Khan says:

Tell me if it works for you.

36. JamesW says:

In an attempt to understand the VBA I’ve been comparing the original and the modified version (where worksheet data<Column H are preserved). I keep getting a recovery error when opening the file:

Removed Records: Sorting from /xl/worksheets/sheet2.xml part
Removed Records: Sorting from /xl/worksheets/sheet3.xml part
Removed Records: Sorting from /xl/worksheets/sheet4.xml part
Removed Records: Sorting from /xl/worksheets/sheet5.xml part
Removed Records: Sorting from /xl/worksheets/sheet6.xml part
Removed Records: Sorting from /xl/worksheets/sheet7.xml part
Removed Records: Sorting from /xl/worksheets/sheet8.xml part
Removed Records: Sorting from /xl/worksheets/sheet9.xml part

It doesn't appear to affect the workings if the repair is done, but I'm curious as to why this is occurring every time the file is opened (Excel 2010&2013). Any ideas please?

• Alex says:

Hello Samir,
I have the same problem as JamesW. Is there a way to fix this? This error even doesn’t allow to save Excel file by clicking CTRL+S. I need to overwrite a files first and only then I am able to make savings.

Hope you can point how to fix this error.

Thanks

• Samir Khan says:

I think this is an Excel 2010 bug.

In the VBA, try changing….
=================
With Sheets(stockTicker).Sort
.SetRange Range(“A2:G” & lastRow)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
==================

…to…
==================
With Sheets(stockTicker).Sort
.SetRange Range(“A2:G” & lastRow)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
.SortFields.Clear
End With
=================

37. Renju says:

Samir,

I tried this for the Indian markets and it throws me an error.
Is there anything we need to change for the Indian markets?

Example : BHEL, Reliance, Infosys

Thanks,
Renju.S

38. Enrique says:

Samir,

The spreadsheet worked OK over the last week; however, it suddenly stopped working Today (7/11/2013). It deletes all the tickers and does not recreates them only leaving the parameter sheet. I re-downloaded the spreadsheet from the website and ran it as you have it setup and it still deletes all the tickers and only leaves the parameter sheet. Please help.

• Samir Khan says:

I’ve just tried the spreadsheet, and it works fine for me. It also works with a new set of tickers.

Could you try again?

39. Ashok Shah says:

Dear Samir,

You have really done wonderful thing with such a depth of effort. I wish if you can make one more file where latest price of multiple companies can be downloaded into one sheet only based on the stock symbol in one column with various parameteres like LTP, Opening, Closing, Day high, Day low, 52 week high, 52 week low, All time high, all time low etc. etc.

40. Cali Investor says:

Thanks Samir for coding such a gangster macro . . . . this is EXACTLY, I MEAN EXACTLY what I was looking for.

Karl7 – thanks for the MAC debugs! Equally appreciated.

Love it!

• Samir Khan says:

Thanks!

I have a major update in the works. The new version will calculate all sorts of technical trading indicators for your chosen ticker symbols. As ever, it’ll be free

41. Joe says:

Great job. Is there a way to remove Open, High, Low, etc. and only pull in the close price + Date? Thanks.

42. Adiya Singh says:

Dear Samir,
I am a regular user of your Multiple Stock Downloader which saves me a lot of time everyday. Congratulations for pulling out this awesome code, its has been a great companion!!

Well, It was working fine till today morning when i realized that the data is not showing up! I downloaded a fresh copy of your downloader and tried it on a different computer (I thought i might have screwed the Perfect VBA or I might have changed some Settings on My PC). But much to my surprise, it just dint work.

I tried compiling the VBA and it looked fyn until it comes back to the downloading part from Yahoo Finance where the data from yahoo is not getting downloaded into the new adjacent sheet, which then results in deletion of the sheet later leaving only the parameter sheet behind. I looked at the variable values too in the compiler and it was all fyn.

I tried your other Yahoo sheets as well and surprisingly none of them actually is able to download data from Yahoo. Something seems to have changed.

I request you to please look into the matter and give your valuable suggestions and solutions to the problem.

Thanks & Regards

43. Aditya Singh says:

Dear Samir,
Happy to say that there was no problem with the VBA…The fact that my Internet Explorer was set to offline was not allowing me to access web through web links in Excel.

Thanks & Regards

• Samir Khan says:

Good to know you got the problem sorted out. It’s always the simplest things

• Steve Losre says:

Dear Samir:

First and foremost, you do absolutely marvelous work on this site. You should be very proud of your achievements in this discipline. Being able to connect to these historical stock price databases allows the individual investor/trader to customize their own tech analysis indicators and to perform correlation studies on only the variables that interest that particular investor/trader. Excel gives us a lot of options and the freedom to personalize the data mining techniques that make the most sense to the user. GREAT WORK, SAMIR!!!!!! THANK YOU!!

Before I conclude, I do have a specific question. When I run the data downloader xlsm, I have no problems at all when I DO NOT select “csv” option. If, however, I do use the CSV option, I always get an error message that reads like this — msft 15-04-2012 – 04-09-2013 d.csv’ cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only. MSFT is the first stock on the list being downloaded. All of the separate worksheets are perfect, but the individual CSV files stop running after creating the first one, which is MSFT in this case. I’m not sure what to do next, Samir. I have tested this at least 15 times already and I get the same type of answer each and every time. I don’t know VBA, so if I can’t get this downloader xlsm to provide individual CSV files, I’ll just live with the data in separate worksheets.

Thank you very much, Samir. If you can’t easily provide me with something to try, please don’t worry about it. You do enough work already and I don’t want to create any more for you. Any thoughts or ideas would be greatly appreciated.

Take care, Samir.

Steve

• Samir Khan says:

Steve

I’ve just tried the spreadsheet on two laptops. CSV export works on both with no error.

I don’t know why you get that error. Permissions, or a virus checker preventing Excel from writing to the hard disk?

Samir

• Steve Losre says:

Samir:

Thanks for your suggestion. I will take a look at it. One final question. When you choose the CSV Export option, do you also get the data exported in individual worksheets as well? I tried 20 symbols and all 20 exported perfectly to the worksheets within your xlsm workbook. Then the first CSV file is created and then it stopped with the aforementioned “Error message” (see above).

Again, Samir, I thank you for the OUTSTANDING Excel work that you do. I sincerely appreciate the resulting files that you build so smartly.

Take care

Steve

• Samir Khan says:

Yes, I get the all the CSV files written to the appropriate location.

Samir

44. Pedro Mendez says:

Amazing spreadsheet. Thanks a bunch.

45. Chris Banick says:

Hi Samir,
Great spreadsheet. Thanks for making the VGA available to modify, which I managed to do in order to remove the dates from the filenames when saving. One thing I’m having a problem with is that it puts a blank line at the bottom of the file when it writes the CSV. If you are at the top of the file and hit END+DOWN ARROW, it goes to the line one past the last data item, which is a problem when I go to read it into another program. Is there a way to eliminate the extra blank line that is going in at the bottom of each data file? You can see this by comparing it with the Yahoo file that you save from the Historical Price download button on Yahoo, and hitting the END+DOWN ARROW. The file saved directly from Yahoo (Right click and SAVE AS) has no blank lines beneath the data. If you could tell me how to modify that I’d greatly appreciate it.
Thanks Samir, and great job!
Chris

46. Chris Banick says:

Hey Samir,
If you do manage to look at my previous request, could you also take out the first line that says what the stock name is, since the filename identifies it, and my other program which needs to import the data needs the data to start on line 2 actually below the column heading titles.
Thanks so much!
Chris

47. Rod says:

Awesome work and thanks for the free version.

of the 300+ quotes I’ve used it for I found two that I couldn’t get the spreadsheet to retrieve

and these do appear to be correct as per finance.yahoo.com

an enhancement might be to notify the user that a ticker wasn’t found.

• Samir Khan says:

That’s a great idea. I’ll code this in

• Samir Khan says:

Download the latest version of the spreadsheet. I’ve just modified the VBA so you get a list of tickers for which no data was found.

48. Frulo Drulovic says:

Hi Samir!
Great job! I’m not smart with VBA, but I’d like to submit you a question. I’m trying to download some fundamental data from yahoo (i.e. “EPS Revisions – Up Last 7 Days”) but with scarce results. Do you think in your experience is it possible to reach this goal in a similar way you downloaded the quotes in this terrific spreadsheet? I mean a list of symbols in a column with correspondent data requested in the subsequent column? (I.e. AMD and then “2″ for example…)

regards,

Frulo

49. Francisco Ribeiro says:

Hi Samir.

Amazing spreadsheet, really a great job!

Dou you know if is it possible to work with brazilians stocks?

For instance, i have tried to get cotes from ETER3 and CRUZ 3 but it didn´t work.

Do you have any idea to help me?

Regards,

Francisco.

• Francisco Ribeiro says:

Don´t worry Samir.

I got the answer… The ticker was wrong.

ETER3.SA
CRUZ3.SA

and the spreadsheet works great!

Tks.

50. dan says:

Great spreadsheet Samir! I use it almost everyday! I do have one questions for you. Is there a way to enter multiple stocks and specific date ranges for each stock? I would like to back test a handful of stocks with specific date ranges for each stock. Any help would be greatly appreciated!

• Samir Khan says:

Yes, there is a way. I don’t have the cycles to modify the VBA this right now (unfortunately InvestExcel.net doesn’t pay my bills and I have a job) but maybe in the future

51. Jack Reacher says:

Hi Samir

Thanks for your excellent file. I just started to use VBA and I want to include other variables such as P/E ratio. Which part of your coding do I need to adjust?

52. Dan P says:

Hi Samir. Thanks for sharing your excellent work. Question – why do my new tabs that I create disappear each time I rerun the quotes? Is there a way to prevent this? Thanks in advance.

• Samir Khan says:

It’s the way I programmed it. The new sheets (with the historical data) are deleted overtime you update, and replaced with new sheets. You could dive into the VBA and alter the behavior

53. curtis says:

Great job Samir. This spread sheet is Awesome. I am using the spread sheet that preserves the data after column H. The only problem I am having is the spread sheet will not download today’s information even many hrs after the closing. It will download everything up to todays date. If I type in an ending date that is not on the current day it works fine, but it wont give the current days info. I am using the day frequency. I am using Excel 2010

Any help would be much appreciated.

54. Jim says:

I am very familiar with VBA. My question is this: how would I find the yearly average for the DOW? I’m trying to get the values from 1900 onwards. Anyone’s help would be greatly appreciated.

55. Alex says:

Hi Samir, great macro, however i tried a french stock, air liquide, and i can’t get any reading. Any clue, or is it programmed just for US stocks?

56. Nate says:

Hey Samir, I’m new to VBA and this macro has been awesome for a project I’m working on! I noticed after reading through the thread that a few people posted requests for a file where you could download historical quotes for multiple stocks and have them show up on the same tab rather than separate tabs. Is this available on investexcel site or have you created something with this capability?

I’m trying to download 5 year historical prices for all the stocks in the S&P 500, but need them aggregated so I can drop in an MS Access database.

Any assistance would be greatly appreciated. Thanks!

• Samir Khan says:

Download the updated file at the bottom of the post. There’s a new (experimental) collate function that collects all the close prices for each ticker on one sheet together with the date, all the open prices for each ticker on one sheet together with the date etc.

If you like the spreadsheet, then share a link to http://investexcel.net – that’s all I ask!

• Aida says:

Hi Samir,

is it possible to get only the close, open, volume etc. sheets?
Because I need 1000 stocks to compare them and the sheet need very long time to calculate every sheet for the ticker.
How can I just get only the close, volume etc. data, without every single ticker sheet?

Thank you
Regards

• Samir Khan says:

Aida

You need to create the individual ticker sheets – can’t get around that. However, you can delete them afterwards with VBA.

Samir

57. david says:

is it possible to get stock prices from other sites with this excelsheet

• Samir Khan says:

If you can find the right web site and data feed, then yes

58. Stan says:

Hi -

I’ve found an interesting problem with the Collate function: as far as I can tell, it uses the dates retrieved for the first stock entered in the parameter list and assumes that there’s historical data available for all of stocks for the period selected and so creates the collated tabs with the first date in the range in the first row, the second date in the range in the second row and so on. This creates a problem when some of the stocks in the list don’t have any historical data for the beginning of the period, e.g. they didn’t start trading until sometime later in the range. For example, if I select the range 1/1/2012 to today and use GOOG and PSX (PSX didn’t start trading until 4/12/2012), the collate tab places the data for PSX on 4/12/2012 in the first row which is 1/3/2012. Similarly, if I list PSX first and then GOOG, the first row of the Collate tabs is 4/12/2012 but the value for GOOG on that row is actually the data for 1/3/2012.

I suppose the only way around this would be to add some code to actually look at the date values on each stock’s tab and then copy the data to the correct row on the collate tabs. Samir, is this something you could add?

Thanks for making all these spreadsheets available – they’re really great!

• Samir Khan says:

Stan

I’ve updated the spreadsheet to reconcile time series of differing lengths. Now values and dates are correctly associated on the “collate” sheets.

Consider donating or linking to http://InvestExcel.net if you like the spreadsheets that much

Samir

59. K.T. says:

First of all; absolutely amazing work Samir.

It would be great if I could add some additional sheets without them getting deleted when I run the macro in the parameters sheet.

I have tried to modify the VBA, but I am new to this so I had no luck. Can I add a few names of sheets that should be deleted?

K.T.

• Samir Khan says:

I think I can add this option. Bear with me.

• K.T. says:

Hi Samir,

Thanks for your reply. It would be absolute amazing if you could add that feature.

I have tried to add “Newsheet” to the line below from the VBA and it seemed to work. However, when I open the spreadsheet a week later and run the macro the new sheet is deleted again.
If ws.Name “Parameters” And ws.Name “About” And ws.Name “Newsheet” Then ws.Delete

I am clearly doing something wrong, but I cannot figure out exactly what it is.

K.T.

60. MAURICIO says:

Hi Samir

For any search I get “no data for theses tickers” and it gives me back all the tickers i posted, how can I correct this?

• MAURICIO says:

actually it worked with another post from above..

But I have another question. It is not finding 2 stock quotes I’m trying to retrieve, one is FEMSAUBD.MX and the other one PE&OLES.MX. Could you help me with this?

Thanks.

• Samir Khan says:

FEMSAUBD.MX and PE&OLES.MX work for me.

61. Jason says:

Samir,

I have been using your spreadsheet for several months now to download 200 quotes at a time and it has worked perfectly. But when I use it now, it randomly skips several symbols and puts them into the no data column. However, when I look them up on yahoo finance, they all have data. Any help would be greatly appreciated.

Thanks
Jason

• Samir Khan says:

Jason

This is a function of the load on the Yahoo servers and how willing they are to play nicely. I can think of a workaround that may work, but it’ll take time to develop.

Donate a suitable amount (given the “several months” you’ve used the spreadsheet) and I’ll develop it (I have other demands on my time, including bills to pay)

Samir

• Jason says:

Thanks for the reply, but I just got it working. It was something wrong with microsoft excel. I uninstalled and then re-installed office and now it is working fine. I do plan on making a donation after the New year, I’m tapped out right now from Christmas and bills. Thanks again for an excellent spreadsheet.

• Jason says:

Samir,

Since your moderator decided to delete my last comment I will not be donating now or ever. I am also going on every excel forum I am a member of to tell others not to donate to your site. Thanks for nothing.

• Samir Khan says:

Jason

I moderate and approve the comments myself. You posted the comment while I was sleeping. Get a grip, son.

Samir

62. Joe says:

Samir,

This is the first time I stumbled upon your work. Great job and thank you.

One note: I successfully broke the app! Here’s how: When loading a portfolio with the symbol for Lowe’s Corp, the app “broke” and would not collate. Can you guess why?

The symbol for Lowe’s is “LOW” and once its worksheet was formed, it interfered with the collate function attempting to create another Low tab. lol.

The only enhancement I could possibly suggest would be a field for dividends.

Joe

63. kan says:

Dear Mr Khan,

I would like to take this opportunities to thank you for developing such as
handy tools.

However I have a question for the collate data. If I would just like to
output only the top five rolls of the collate data sheet (i.e. Volume) rather than
the entire peiod to minimize the calculation.

I don’t want to create more work for you. Any thoughts or ideas would be greatly appreciated.
Thanks!

Regards,
Kan

64. Steve says:

The multi stock down loader sheet does not work now. Maybe Yahoo have changed something.

• Samir Khan says:

Just fixed it. Redownload the new version

• Steve says:

Wow….that was quick. I have been looking at sites on the internet for a multiple stock quote downloader into Excel these past few days and came across this site and was amazed at how simple and therefore how good your multiple stock quote downloader was. After trying it out yesterday and realising how good it was I was a little disappointed to find that it did not work today and left a comment saying it does not work now. Within a few hours you replied that it had been fixed. Excellent stuff. I now intend to thoroughly read through your entire website over the next week. Well done Samir and a happy New Year to you.

• Samir Khan says:

Thanks! Please consider linking to investexcel.net or perhaps donating!

65. Jasmin says:

Please help me how to extract below field value from Yahoo finance Key statistic thru macro :

Debt To Equity,Share Outstanding,Profit Margin and Return on Equity

• Jasmin says:

Samir,

Thanks for providing the above link .I have already written my macro since i am able to get the yahoo tag . But for Return on Equity,Profit Margin,Debt To Equity i am unable to find the tag. For share outstanding i am using j1 but its not working

“http://in.finance.yahoo.com/d/quotes.csv?s=” & Symbols & “&f=snl1hgvj1b4ers6p5p68″

I have checked your excel but didn’t find the my above fields .Could you please help me to provide the tag for the above fields

• Jasmin says:

*Small correction – using j2 for sharing outstanding

66. javijaaob says:

Samir,

Is it possible to change the macro to allow repetitions in the ticker section? for example, have the Apple ticker symbol be repeated in the list more than once.

right now, if there’s a repeated stock ticker, the macro asks me to delete any repetitions before it is able to produce the daily stock prices for the listed tickers.

thanks a lot.

67. DavidS says:

Samir,

I am having an issue with your spreadsheet and I was hoping you would have an idea why. I just downloaded your file and tried to run it keeping all default locations and tickers but checking the “Write to CSV” check box.

I get the following error:
Run-time error ’9′:
Subscript out of range

Thank you!

Dave

68. Zack says:

Great work Samir!

I appreciate your generosity and have recognized your work by presenting your website to my Financial Markets class.

I do have ONE question though, it is likely a quick fix…

As far as the COLLATE command goes, when I use only stock tickers the macro runs properly. However, when I try to get data for indices that have a ” ^ ” before the letters (example: S&P 500′s ticker ^GSPC) the data does not collate properly.

I am wondering

1) How can I fix this?
2) Is there a way I can edit the macro such that it collates the data in Excel’s “table” format?

Thank you again!

• Samir Khan says:

Zack

I’ll look into the Collate data issue.

Thanks for presenting investexcel.net to your class

Samir

69. JSG says:

I have three questions:

1) Is there a way for the date to be formated as yyyy-mm-dd? (Someone else asked the same question but it was left unanswered)

2) Can the first line not have “Stock Quote for….” , but keep Date, Open, etc the same on Row 2

3) Remove ,,,,,, at the last row?

Donation made for this wondeful tool!

70. JL says:

dear Samir,

Brilliant site and great excel VBA power. I’m using ur stock quotes downloader for Yahoo. Too bad you delete all sheets. If one creates extra sheets the code deletes all sheets except the parameter sheet. Would be nice that only the ones right of the parameter sheet are deleted then one can add sheets to the left of the parameter sheet to do analysis without the sheets being deleted.

Just my two cents other then that Keep up the good work, very impressive

regards
JL

• Samir Khan says:

Many people have requested this feature. I’ll program it into the VBA.

71. JohnB says:

All,

Firstly thank you tons Samir. This is awesome. The one issue that I have is the same as DavidS above.

When I run export to csv it comes through with a Run-time error ’9′:
Subscript out of range

I am running Office 2013 and Windows 8.1, I’m guessing something may have gone awry there. Does anybody have an answer what might be causing the runtime error?

It will write 1 symbol and stop after that. Thanks for any help.

• Samir Khan says:

Hmmm….I use Office 2010 64 bit and Windows 7 64 bit. Export to CSV works fine on my machine…I might download a trial of Office 2013 to see if I can reproduce the issue

72. Vasan says:

AS OF TODAY THE HISTORICAL QUOTES DOWNLOADER IS NOT WORKING.I THINK YAHOO HAS CHANGED THE URL.PLS UPDATE YOUR FILE AND PLS PROVIDE A DOWNLINK TOUR GREAT UTILITY.REGARDS

73. Swesak says:

Hello Samir

This is a really nice tool that I have tried before. However, now when I downloaded it it no longer seems to work. Do u have any idea why?

What happens it nothing is downloaded and all specified tickers returns no data. And they all turn up in the “No data for these tickers” box.

Thanks for all your great work.

74. JK says:

Hi Samir,
Very nice sheet.
However, I have list of 600 tickers and I can’t process it in a nice way with the sheet.
So, is there a possibility to make a customized sheet where there are no separate sheets made for each ticker?

Warmest regards,

Jay

• Samir Khan says:

Jay

You could write some VBA that deletes the separate sheets after the worksheet is run (if the collate button is checked).

Not sure if you can download quotes for 600 tickers with the sheet though. I’ve retrieved information for 150 tickers, but haven’t tried any more than than.

Samir

75. Josh says:

Samir,
Great work. Thank you for this tool. I noticed that the last row of data in your collated worksheets does not populate across all columns (only the first symbol/stock). Why is this?

76. Michela says:

Hello Samir,
I seem to be having problems using the file on a Mac… all of the spreadsheets are empty. Any tips?

77. Hello Samir says:

Hello Samir ,
You have created a nice spreadsheet but i am trying to get the same amount of data in a different way i want to the data in form of date , Volume and Adj CLose in one sheet how can i do can you please help me asap.?

• Samir Khan says:

I customize spreadsheets on a paid-for (consultancy) basis. Write to me at the email address at the bottom of the Privacy Policy page if you’re interested.

78. John Ryder says:

Samir,

First of all, really good spreadsheet.

For some reason, today the spreadsheet is not working. The success for the tickers is very low.

Thanks,
John

79. james says:

HI Samir,
today the Multiple Stock Quote Downloader for Excel get problem.I have 10 stocks on list. When I changed the start date, normally all last day quotes of the 10 stocks in same row in thier own worksheets. but today, they are located in different row, for example some in rwo 200, some in 206, some in 211.
This is first time I met such problem since I use your program from Feb. Need your help. Thanks a lot.

80. Ian says:

Hi Samir,

I can’t get this to work with Excel 2003, how to modify it?

81. John says:

Hi Samir,
Great work.
I would like to know if you have the same kind of excel file available for downloading historical data from google finance
Please also let me know if you have the same kind of excel file to download bhavcopy from nse for the selected date ranges. Its bit urgent. Please reply as soon as possible. Thanks

• Samir Khan says:

“Its bit urgent. Please reply as soon as possible”.

Why should I do this?

82. Paul says:

Hi Samir,

The xls doesn’t seem to work when I try to call certain names, ie EXMT. Instead of generating summary worksheets with all the names in my as expected, only 1 name populates.

Also, the most current requested day does not populate ANY time (even outside the above example)

Any suggestions?

Thanks,

Paul

83. yjlhuissier says:

Hi,

This is really excellent : thanks a lot for sharing that with us !

If I may, I’d like to make 2 remarks and 1 question :

- I think there is a small bug : for all the “Open”, “High”, … tabs, there are missing data on the last line (the line of the “End Date”)
- So, if “Start Date” = “End Date” (when we want only the today quote), the tables in the “Open”, “High”, … tabs are wrong : the column header is no more there

- It’s working for some stock tickers like : F000000DR5.PA or FR0010532101.PA
but It’s not working for some stock tickers like : FR0010217141.PA or FR0010914994.PA
which is very strange for me because for all of them the basic Yahoo requests :
http://fr.finance.yahoo.com/q?s=F000000DR5.PA&ql=0
http://fr.finance.yahoo.com/q?s=FR0010532101.PA&ql=0
http://fr.finance.yahoo.com/q?s=FR0010217141.PA&ql=0
http://fr.finance.yahoo.com/q?s=FR0010914994.PA&ql=0
are giving correct results

Any idea ?

84. Paulf says:

Samir hi, thanks for providing these spreadsheets for everybody to use. Would it be possible to get a spreadsheet to download that combines the ability to download multiple stocks (this is the spreadsheet on this page) AND include the functionality to calculate multiple EMA’s at the same time, please?

There is a separate spreadsheet that can be used to calculate one EMA at a time that I saw, but I couldn’t find a spreadsheet that allowed for what I am requesting in this post. Of course, there is no reason why you should provide this, but if you would consider it, it would be a massive time saver. For me, personally, anyways.

Thanks for the valuable contributions, it is appreciated.

Paul

85. H.Roark says:

Hi,
First of all I want to thank you for sharing this!

I had some problem with tickers including a hyphen, e.g. as in HM-B.ST. The spreadsheet works very well, except when I try to Collate data. The stock tickers containing a hyphen has empty columns in the collated data sheets. I have no experience in VBA, but i managed to fix it by change the code a bit.

I changed:
Sheets(“Adjusted Close”).Range(Sheets(“Adjusted Close”).Cells(2, i), Sheets(“Adjusted Close”).Cells(maxRow – 1, i)).Formula = _
“=vlookup(A2,” & ws.Name & “!A$2:G$” & maxRow & “,7,0)”
to:
Sheets(“Adjusted Close”).Range(Sheets(“Adjusted Close”).Cells(2, i), Sheets(“Adjusted Close”).Cells(maxRow – 1, i)).Formula = _
“=vlookup(A2,” & “‘” & ws.Name & “‘” & “!A$2:G$” & maxRow & “,7,0)”

(and implemented the same change for all sheets)

However, i expect that there is a better/alternative solution and that it may be something you want to implement in a future release.

Thanks again!

86. Mr T says:

Hi Samir,

What a great spreadsheet! Thanks very much for sticking it up and allowing those of us who are willing to muck about with the VBA adjust it to suit our needs. My problem is I know as much about where to go for the data as I do on how to write the VBA code to go and get it! If you could find time to point me in the right direction on both counts I would be very grateful.

My query is this: how do i ensure that it is picking up the data for stocks listed on LSE:AIM? One of the quotes I’ve already run returns answers that are too big and must be in cents for US markets, i assume? Also, I am looking for mid prices, not trade prices. The scheme I am trying to work on needs historic data for open, high and close prices, to allow some prediction of trends (or at least, that’s the theory!).

Look forward to hearing from you if you have the time – in turn, I will publish my own notes on how I got on, here, to assist others.

Thanks,

Mr T

87. Viper says:

Hello Samir,

Why i cannot download data for KLSE?

Tq

88. AkG says:

Hi Samir,

Thanks, very helpful idea for small retail investors like me. I couldn’t use the spreadsheet as such, as it returned only blank sheets. I noted few errors, yet trying to fix.

Seems yahoo has updated the link from,
http://ichart.finance.yahoo.com/table.csv? to http://real-chart.finance.yahoo.com/table.csv?

Also, there’s an issue with Excel2013 Querytables / BackgroundRefresh part. Yet to identify any solution to this. Have you encountered this at your end? Thanks in advance.

Thanks,
Ak

• Samir Khan says:

The spreadsheet still works for me with “http://ichart.finance.yahoo.com/table.csv?”.

I don’t have Excel 2013 so I can’t test the spreadsheet on that platform.

• AkG says:

Hmm.. I changed the link to:
qurl = “URL;http://real-chart.finance.yahoo.com/table.csv?
and ran it in Excel2010, and it’s working now. But thanks anyway for checking!
Thanks, great stuff! Your script has made the complex task so easy..

• Samir Khan says:

Cool. Be sure to share investexcel.net with your friends! I need more visitors!

89. Jim Haygood says:

Thank you, Samir, for this excellent tool. It is a huge time saver.

90. Hi Samir,

Great work! Would it be possible to have the sheet names remain the same, instead of re-naming themselves to each ticker symbol?

• Samir Khan says:

Yes, it’s possible but it requires some reworking of the VBA. The current code relies on the renaming of the sheet names to the ticker symbols

91. Anastasia says:

Dear Samir,
thank you SO much! I am using the spreadsheet for my bachelor’s thesis and it is really helpful.
I have one question though: As I try to run the regression analysis, it shows me an error because of non-numerical data, as some of the cells seem to be imported as dates? Is there a way to change this by any chance?
Thanks for your help,
Ana

92. Bestin says:

The ticker for Open Table (OPEN) causes a crash in the VBA code, just as the ticker for Lowes Corp (LOW) does, as Joe pointed out above. The creation of each respective tab would conflict with the existing Open-High-Low-Close naming convention already in place. Similarly, the potential tickers HIGH and CLOSE, if ever adopted by a company, would cause a similar issue with the VBA code as well.

Great work, Samir. Cheers!

• Samir Khan says:

Good points. I’ll add a suffix to the Open, High, Low, Close tabs (something like OpenPrices etc). That should work around the problem

• Samir Khan says:

I’ve renamed the Open and Low sheets to Open Price and Low Price (with similar changes for the other collated sheets).

There should be no conflict with the tickers OPEN and LOW

93. bill page says:

The cvs file option does not work for me although the spreadsheet works fine

If i select the cvs option no files are written and i get –

error 9:
subscript out of range

The debug pop up shows

ErrorHandler:

Worksheets(“Parameters”).Select

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

I am using excel 2010 on windows 7

• Samir Khan says:

Works fine for me (on Windows 7, Excel 2010 and Excel 2013). I get the files written out

• bill page says:

Works OK now.
Apparently the download folder ( c:\temp\ ) has t be created first.

• Samir Khan says:

Thank you for the kind comment!

94. BG says:

This is AWESOME! I modified it a bit to my personal preferences, and it works beautifully.
Thanks so much for sharing!

95. avbi says:

Samir,
your VBA development seems to be a “must” with so many happy users.
Before beginning could you confirm that ISIN codes will be recognized as well as tickers.
in France ISIN is the common pointer to OPCVM’s
in excel 2007 Stock Quotes are uploaded from MSN Money using ISIN lists via “MSN MoneyCentral Investor Stock Quote connection ( but smart tags do not seem to apply to isin codes only to tickers such as “MSFT”

Obviously i’m a full beginner.

RV

• avbi says:

Dear Samir,
I just tested Multiple Stock Quote Downloader.xlsm under Excle 2003 &t did not work error 438
but under 2010 it works fine with the tickers demo list

I attempted my list of isin codes :
FR0000292278
LU0594300096
FR0010636399
LU0594300096
LU0048580855
LU0368678339
LU0099574567
FR0010923375
AAPL
but only the last code (ticker) was regognized.

However such data seem to be accessible i;e.
http://finance.yahoo.com/q/hp?s=FR0000292278.PA&a=03&b=29&c=2010&d=07&e=16&f=2014&g=d

Should i modify some settings?
Best & respect for your ergonomic design.
RV

• avbi says:

In addition to formper comment :related to ISIN identification codes somth like

http://ichart.finance.yahoo.com/table.csv?s=FR0000292278.PA&a=03&b=29&c=2010&d=07&e=16&f=2014&g=d
seem to work & download a filled yahoo.csv Table

2014-08-01,18.12,18.12,18.12,18.12,000,18.12
2014-07-31,18.18,18.18,18.18,18.18,000,18.18
2014-07-30,18.46,18.46,18.46,18.46,000,18.46
………………………………………….

suggestion to modify the code ?
is it related to numerical values in the string

• avbi says:

Maybe isin codes need to have a suffix indicating the quatation place
example:
FR0000292278.PA
Confirm ?

• Samir Khan says:

For FR0000292278, I think you need to enter FR0000292278.PA
LU0594300096 isn’t recognized, either with or without the .PA suffix
FR0010636399 is recognized with the .PA suffix.

I haven’t tried the other ISIN codes.

The spreadsheet works fine for me on Excel 2010 and 2013.

• Samir Khan says:

I don’t have any experience with ISIN codes, so I can’t confirm whether they will or won’t be recognized

96. Gowtham Prabhu says:

Mr Samir Khan,
I saw all your links in investexcel.net. Its very useful for share holders and I like those xls files. Actually I’m doing in Indian share markets nifty and other futures stocks in India market
Basically I want to determine the volatility of any particular stock in Indian market and use it in black scholes formula to find the value of option and check its variance from the original data.
Here is the link to take datas for all your queries.

http://www.nseindia.com/products/content/equities/equities/eq_security.htm

Can u create VB spreadsheet for that.
Can u help me out in that regards.

97. Julie says:

Hi samir,
I am trying to do a project for a finance class and I just started with VBA so I do not know a lot.
I saw your webpage on how to get stock quotes updated on an excel spread sheet and I would like to know what you did so the tickers you enter in the first page appear in the name of the files. Here is the code i think you use (but im not sure) I would like to understand so I can make my own thanks.

Sub CopyToCSV()

Dim MyPath As String
Dim MyFileName As String

dateFrom = Worksheets(“Parameters”).Range(“$b$5″)
dateTo = Worksheets(“Parameters”).Range(“$b$6″)
frequency = Worksheets(“Parameters”).Range(“$b$7″)
MyPath = Worksheets(“Parameters”).Range(“$b$8″)

For Each ws In Worksheets
If ws.Name “Parameters” And ws.Name “About” Then
ticker = ws.Name
MyFileName = ticker & ” ” & Format(dateFrom, “dd-mm-yyyy”) & ” – ” & Format(dateTo, “dd-mm-yyyy”) & ” ” & frequency
If Not Right(MyPath, 1) = “\” Then MyPath = MyPath & “\”
If Not Right(MyFileName, 4) = “.csv” Then MyFileName = MyFileName & “.csv”
Sheets(ticker).Copy
With ActiveWorkbook
.SaveAs Filename:= _
MyPath & MyFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False
.Close False
End With
End If
Next

End Sub

Thanks for your time,
Julie

• Samir Khan says:

The ticker names appear in the file names because of this code

ticker = ws.Name
MyFileName = ticker & ” ” & Format(dateFrom, “dd-mm-yyyy”) & ” – ” & Format(dateTo, “dd-mm-yyyy”) & ” ” & frequency

Remember to tell your classmates about investexcel.net

98. Joey says:

Hi Samir,

Thank you very much for this downloader. It has saved me a bunch of time already! I am having an issue I was hoping you could help me with. I am mostly interested in the adjusted closing price worksheet. I input my ticker symbols on the parameters page, but only 4 of my rickets are being populated on the adjusting closing price worksheet. Each individual tab for all of the stocks are being created, I just can’t get them all to show on the adjusted sheet. Any ideas?

Thanks!