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.

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.

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,
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!

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

1. Herb D'Argenio says:

Hi Samir,
I use the same parameters everyday. Some days it works and some days it doesn’t. I mostly fail because all stock tickers show up in ‘Failed’ but I use the same tickers everytime. Any clues?

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?

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.

1. Rick says:

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

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

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.

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

1. dompak says:

Hi Samir, excellent work you’ve done here
http://www.bloomberg.com/apps/data?pid=webpxta&Securities=TICKER&TimePeriod=6Y&Outfields=HDATE,PR005-H,PR006-H,PR007-H,PR008-H,PR013-H
Replace the TICKER with bloomberg stock code
Example : http://www.bloomberg.com/apps/data?pid=webpxta&Securities=TLKM:IJ&TimePeriod=6Y&Outfields=HDATE,PR005-H,PR006-H,PR007-H,PR008-H,PR013-H

It would be great if this data can be transfer to excel

1. ignatius says:

Manually, you can supply the URL in Excel: Choose “Data”, “From Web” and supply the URL stated, press go and you will see the data. Then press the import button at the bottom of the window. Then data will be imported to excel. But you need to separate the data into columns. You can goto “Data” and select “Text to Columns” to separate the column with delimiter “. I am trying to see if I can do the program as I haven’t written VBA for a long time and retrieve data from the web is new to me. By the way, is there a simple instruction to download the data of ALL stocks from a particular exchange for a particular day? Can you explain the coding in your URL in details? (I think 6Y stands for 6 years, right). How about 52 high, 52 low…etc

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

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

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

2. David says:

hi

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

3. Daniel says:

Could you give some more information where to add the calculation code? For example, I would like to display the Tickers and 10-month SMA on the Parameters sheet. I’m not clear where in the code to add this calculation. Maybe in a separate sub routine?

Daniel

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

1. Hi Tom

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

Samir

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

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

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

2. I would like to +1 that request.

An example of how this might be used:
– 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:)!

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?

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

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

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

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

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

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

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

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

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

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

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

Samir

44. Pedro Mendez says:

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.

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.

1. Francisco Ribeiro says:

Don´t worry Samir.

I got the answer… The ticker was wrong.

ETER3.SA
CRUZ3.SA

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!

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

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

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

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

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

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

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

1. K.T. says:

Hi Samir,

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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 ?

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:
“=vlookup(A2,” & ws.Name & “!A$2:G$” & maxRow & “,7,0)”
to:
“=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,

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

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

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

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

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

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

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

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

1. bill page says:

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

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

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

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

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

1. avbi says:

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

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

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

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

Julie

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

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!

99. Abhishek says:

Hi Samir,
Great Work.

I am trying to use this downloader to analyse BSE stocks but unable to do so as its not fetching the data for the BSE tickers.

Regards
Abhishek

1. Vinod says:

Hi Abhishek

It is working for me. You may give the ticker like WELSPUNIND.BO for BSE stocks and VOLTAS.NS for NSE stocks.

Vinod

100. Vinod says:

Hi Samir,
Excellent Work.
I would like to download these data in rows for further analysis especially to use filters.
Is there any way to convert the downloaded data to another file other than using cut and paste with transpose option
Vinod

101. Ron says:

Hi
I am trying to use your sheet “Multiple Stock Quote Downloader for Exce” to get quotes for 2148 stocks, the excel hungs because so many sheets are created. I don’t need the sheets to be created, I only need CSV exported, .. could you please help me add a LOOP to the VBA to process the long list 10 at a time, cleanup/delete created sheets the continue the loop.
I know I should be fine doing this manually with smaller lists, but if I could automate it it would be great

Thank you

Ron

1. Scott says:

I suggest
after “for ticker = 12″

If (ticker – 11) Mod 40 = 0 Then
Shell “RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 1″
Else
End If

If it runs fine for 180 then maybe you could do “mod 180″ but a smaller number might be safer.

102. Parijat Luthra says:

hi samir

cannot work on NSE data .
how can i get data from NSE : SBIN

its available on google finance though. and can get it on google docs

103. lokesh says:

Hi sameer,
You have did an excellent work, bit how could i use this to get data from indian stock exchange BSE and NSE. Kindly let me know thanks alot.

104. Martin Murphy says:

Hi Samir,
Wonderful work on the bulk stock data downloader. I just ran it and it was unable to return data for ^DJI and TMST (Timkensteel). All the others worked seamlessly, any idea on the 2 that didnt?

Thank You

-Martin

1. TMST – I successfully downloaded data for this ticker for the dates 19th June 2014 to 14 October 2014. Historical data for TMST doesn’t seem to be available further back than 19th June 2014.
^DJI – Yahoo does not provide programmatic access to historical data for this ticker.

105. Edward YEUNG says:

Samir,

Thanks for your great worksheet. Grateful if you could suggest how to get from SSE A share index (000002.SS) and SSE B share index(000003.SS). Thanks

1. Yahoo do not provide historical data for those tickers (nor does Google Finance, for that matter)

106. Peter says:

Hi Samir

I have been looking for something like this for a while. Ideally I would like to add certain stock details such as Dividend Yield, Dividend Payout Ratio, P/E ratio,

Is there a way to add these sort of items? It would be really great.

Thank you.

107. Mahendra says:

hello samir,
sir i am small trader and looking for hedging strategy “SPREAD TRADING” I use your google 1 minute excel. is there any chance u can create intraday google data file with two stocks since currently im using two different file and third excel for deviation and average for line chart in excel…. plz help me with this to get through…

regards & Respect
Mak

108. David M says:

Thanks for a very useful expertly crafted spreadheet. One bothersome detail that I noticed and I think I fixed: The number of connections in this workbook is currently 581. There is no need for any connections to be stored. Every time a stock symbol is requested that yahoo can’t retrieve a new connection is created. I added the following code to the “DownloadStockQuotes” subroutine to remove these connections and to prevent this from occurring:
at the top of the subroutine:
Dim C As WorkbookConnection
and below the error handler line label:
For Each C In ThisWorkbook.Connections
C.Delete
Next

1. Thank you for this tip. I’ve just added that code to the worksheet. Very helpful, thank you.

109. Andrew says:

Never mind, you just use the “.TO” ticker. The spreadsheet is great. Many thanks!

110. Martin Murphy says:

Hi Samir,
I have been using your excel down loader since early October, to retrieve data for a 67 stock portfolio. Everything was working perfectly until 11.12.14, then for some reason the data retrieved for GS (Goldman sachs) is not correct. It is pulling data values in the mid 20’s? I have removed the ticker from the list, run the retrieval process, saved the file, and then went back and added the ticker again. However it is still pulling the wrong values. I have also went on to Yahoo.com and ran a historical quote retrieval for the period in question for GS and it generates the correct values. Any ideas what the issue could be? The other 66 stocks on the list are working fine.

Thanks

-Martin

111. Partha says:

Very nice application. The only issue is that the yearly frequency option (y) doesn’t work.

112. Hello Samir,
The Get the bulk button doesn’t show up at all instead a string of words. Could you help me through this?

1. You’re out of luck. I’m not familiar with Open Office, but I don’t think it’ll run the VBA. You’ll need Microsoft Excel

113. Dear Samir
following tickers are failing –
MOTILALOFS-EQ.NS
SHASUNPHAR-EQ.NS

Is there any specific reason. Can you resolve this? It will be a great help.

thanks

1. Yahoo does not provide historical data for those tickers

114. Martin says:

Hi Samir,

Good job!! Your code is simple and clean

I am willing to pay for the following modification (for metastock import program). The output required is:
,,,,,,,,,
AAPL,D,20000103,000000,26.2200,28.1300,25.4200,27.9800,0,0

2000-01-03,104.88,112.5,101.69,111.94,133949200,3.79

Possible?

1. Since this is a popular request, I’ll do this for free (but please donate a few dollars instead – whatever you think it’s worth).

Could you help me understand how you go from

2000-01-03,104.88,112.5,101.69,111.94,133949200,3.79

to

AAPL,D,20000103,000000,26.2200,28.1300,25.4200,27.9800,0,0

It’s not entirely clear.

115. Martin says:

Hi Samir,
I set up the sheet to retrieve daily prices for the current day for 166 securities. The process works perfectly creating individual sheets for every security on the list. However on the collate function, it only returns values for the first 135 securities on the list. I have tried multiple times and get the same results each time. The collate sheet is the most important to me as I have links to that sheet to pull current pricing into a risk management sheet. Any suggestions or insight would be much appreciated. Thank you for providing such a valuable tool.

-Martin

1. Email me the spreadsheet with the 166 ticker symbols. The code is designed to bail out if it sees something out of the ordinary

116. Greg says:

Very nice piece of VBA programming….crossplatform no less!

Much appreciated.

117. Greg says:

BTW in case it helps, Excel 2011 for Mac produced an error for (in the DownloadData routine):

For Each C In ThisWorkbook.Connections
C.Delete
Next

I remarked out the loop and everything was smooth from there on in.
THanks again!

1. Majed says:

Hi Greg,
Had the same problem but I just made it work. I removed these lines from the code and it worked.

Scroll up and remove the line of code “Dim C as WorkbookConnection” and remove the lines you mentioned in your question as well then try running it again.

118. Tuan says:

Hi Samir,
First, off, I’d like to commend you on this. Great job! I like to look at seasonal pattern of a stock. Would you be willing to add codes/formula that ranks stocks based on average gain and then another sheet showing average winning pct for all 12 months over the past X number of years? I’d be willing to pay for this. The raw data is nice, but I would love to see it presented in a performance format. Could you please contact me if this is possible.

Thanks!

119. shekhar says:

Hi Samir, can we have prices in four decimals?

120. Ricardo says:

Hello Samir,
I use the excel Bulk Stock Quote Downloader, and it works very well. But yesterday and today, I can not downlad anything.
Do you know if there is a problem with yahoo…? or something has change..?

Thank you.!

121. Bill Dwyer says:

Thanks for all you do!

A quick heads up. Yahoo has a data error on 11/24/14 for closing and adj close of DJIA in the download source data; however, when I look at data on Yahoo Finance, data shows correctly.
Nov 24, 2014 17,812.63 17,855.27 17,793.19 17,817.90 85,510,000 17,817.90 – from Yahoo Finance website – is there a way to correct your source dataset? I will try to contact Yahoo.

Also, I am suspecting that Yahoo historical data (several years back) has changed for djia since one of my trading systems that uses the historical data is now showing different results for prior years… I am still researching but wondering if anyone has seen these types of changes. FWIW, I use adjusted data values in all studies.

1. Bill Dwyer says:

Update – I left a comment on Yahoo finance asking them to fix 11/14/14 bad data on DJIA.

Re: second issue, further analysis showed Yahoo Finance does not list data for 11/23/14 for the ^XAU index and that was skewing my calculations.. not sure why became market was open that day. I’ll leave them another request to fix this.

Thanks

122. Bill Dwyer says:

correction, should read 12/23/14 for missing ^XAU data

123. Harsh says:

Hi,

I am trying to run the downloader today and all of my tickers are failing Is it because it’s the weekend? Will this work tomorrow morning?

Thanks,
HS

124. John Henby says:

Help. I used to use “existing data connections” in an excel spreadsheet to connect to MSN Money Central Investor Stock Quotes and download current stock prices to a list. This service is no longer available. Where can I find a replacement for this service? I am currently using google finance, but the spreadsheet there is really inadequate.

1. For anything serious, I now use an add-in called MarketXLS:

(affiliate link, but that hasn’t affected my opinion of the product; use coupon code investexcel for a 20% discount). It does a lot, and is well worth the money.

It works well! (now I’m trying to find out HOW it works.)

Hi Samir,

What you have done here is awesome. Thank you so much.

I went into the code to try to delete the first line (name of stock) saved into each CSV file. My coding skills are quite limited, unfortunately, and have been having trouble changing the code. I got real close but for some reason the first row now contains the most recent date (second row and beyond are all correct). Thought this would be an easy fix from here but I can’t quite get it down. Some help would be appreciated!

Regards,

127. David Upton says:

Seems just what I’ve been looking for, but doesn’t work on Office 2000. Any chance of an older version and unprotected VBA code so I can amend to work in the UK.

Great job by the way.

Thank you

128. Freddy says:

I am running this sheet off of excel 2011 on my mac. Is there a reason I keep getting a window that says:

Compile Error

1. Perhaps that version of Excel doesn’t run VBA? I don’t have a Mac (let alone that version of Excel) so I can’t troubleshoot

129. mark Duffy says:

Hi Samir

Stunning – The world needs more people like you. Radiators always giving. Thanks
Your generosity drives me to do the same.

i have been using your program for a few days. Like everyone else I have been looking for something like this. I have not started my investments as yet and would have been delayed a lot longer had I not found this. It is wonderful. If the lord is kind and I make some serious cash I will donate to your site heavily.
I don’t know much VBA but sufficient with the help of forums to add to the tables. I am now wondering how I am going to add the 50 day Simple moving average. My criteria is quite simple. I sell if the stock goes below 5% from its highest. So i use your highest and add more bits but i would also like a 50 SMA and am not sure how to add this. Clearly where you add the other bits I will start this

Thanks
All the bet
Mark

130. Joop van Dijk says:

Anyone having problems with the Adjusted Close tab after this week’s Windows 8.1 update? The macros seem to stop at some point while converting points and decimals. When using file version that worked previously I get the same errors, meaning that something in the environment has changed.

Any feedback would be highly welcome!

131. Home says:

Hi, Samir.

You are doing a great job, this is amazing.
Just wanted to say thank you.

132. Frank says:

Hi Samir

Many thanks for this useful spreadsheet!

I noticed that the collated data (open, high, low, etc.) is only calculated for the first few tickers. Would you let me know when and if there will be a new version of the worksheet available that collates data for many tickers, e.g., 20 and more?

Thanks!

1. There’s no limit on the number of collated tickers. There must be some other issue.

133. Joop says:

Hello Samir,

Could you please help me with a problem apparently nobody has that is posted above on April 17th. I tried to narrow down the problem but it already occurs with one single ticker (CURE) using one single day (April 30). Once I click “Get bulk quotes” and look at CURE’s low price I see 13.800.999 instead of 130.80. Similar things happen with other quotes as will but not all on the same day and not only with the low price. Examples are SHY, UST, UBT. But other tickers are no problem, ever. By the way, when I download the Yahoo data straight away I can’t see any problem within their data.

In the example above if I take an other date, let’s say April 29, things are working just fine. What could possibly be wrong???

Thanks and best regards,
Joop

134. Vicente says:

Bravo !!!

Excellent workbook.

However, it fails when downloading some tickers, for instance SPY. The problem is with formatting, the decimal separator is misplaced !

For example

The open for 28 April 2015 is 21,074,001 usd
The adjusted close for 29 April 2015 is 21,057,001 usd

In both cases the quote shown is 21 Million usd !

The problem probably comes from a confusion in the decimal separator. In my computer I use the Spanish international settings where the decimal separator is the “comma” and the thousands separator is the “point”.

Is there a way to fix this problem?

Best regards and thank you

Stock Quotes for SPY
Date Open High Low Close Volume Adj Close
2015-04-27 212.33 212.48 21.053.999 210.77 78605500 210.77
2015-04-28 21.074.001 211.50 209.33 211.44 84482200 211.44
2015-04-29 211.44 211.44 20.960.001 21.057.001 121653600 21.057.001
2015-04-30 209.88 21.035.001 207.62 20.846.001 148619200 20.846.001
2015-05-01 20.939.999 210.77 209.28 210.72 96722000 210.72
2015-05-04 211.23 212.02 21.110.001 21.132.001 68949200 21.132.001

135. Vicente says:

Hello Samir,

My problem, just posted, and the one from “joop” (posted just above) are exactly the same.

Best regards

136. Vicente says:

Hello again Samir,

Please have a look at the CSV generated with the download. You can see the quotes at 21 million usd. Hope this helps

Stock Quotes for SPY,,,,,,
2015-04-27,212.33,212.48,”21,053,999″,210.77,78605500,210.77
2015-04-28,”21,074,001″,211.50,209.33,211.44,84482200,211.44
2015-04-29,211.44,211.44,”20,960,001″,”21,057,001″,121653600,”21,057,001″
2015-04-30,209.88,”21,035,001″,207.62,”20,846,001″,148619200,”20,846,001″
2015-05-01,”20,939,999″,210.77,209.28,210.72,96722000,210.72
2015-05-04,211.23,212.02,”21,110,001″,”21,132,001″,68949200,”21,132,001″

Thank you

137. Matt says:

Hello Samir,

I have the same problem like Joop for several Symbols.

Just try “SPY”
for 1st May I get Close Price 210.72 as text
for 4th May I get Close Price 21132001 as number.

The problem I have for several symbols random in a colume. Data are not usable.

Thanks for ideas to fix it.

best regards,
Matt

138. Vicente says:

Hi Samir,

There seems to be a problem with the TextToColumns property in VBA. Because of this problem, a quote that should read 130.80 appears as 13.800.999.

After some tests, I think I have solved the problem.

DecimalSeparator:=”.”, ThousandsSeparator :=” ”

A programmer will know how and where to put it.

With this change, everything seems to work.

Best regards

1. Matt says:

Hi Vicente,

I put it in in row 98 and it doesnt work.
May your can specify how you put it in.

Matt

2. Matt says:

Vicente, I tried this:

Columns(“a:a”).TextToColumns Destination:=Range(“a1″), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
DecimalSeparator:=”.”, ThousandsSeparator :=” ”

3. Thanks for the fix. I’ve added it to the worksheet. Very helpful, Vicente

139. joseph n says:

Wonderful script …

140. Joop van Dijk says:

Great work Vicente!

However, I have no clue where and how to modify the script. And maybe Samir doesn’t have the time to update the original version. Is there a way you can lead us through the modifications? Or is it too difficult?

Thanks and best regards,

Joop.

141. Joop says:

And again it’s working like a charm…

Thank you so much Samir!!!

Joop.

142. Joop says:

Samir,

Can it be that there’s a small error due to the last update? Today it’s the 19th and the historical prices on Yahoo Finance already show prices for the 18th. However, while End Date (parameter sheet) says 2015-05-19 the result sheets all show 2015-05-15 as the latest date. I would have thought the data of 2015-05-18 was included. I haven’t noticed this in the past.

Thanks again!

Joop.

143. Joop says:

Thanks Samir.

When I checked at 05:44 PM it didn’t work. However, I tried again after the close of the markets and now it DOES work. So either something changed directly after the market’s close (but a working day late) or something strange is going on. I’m in the Netherlands so the difference between the 2 moments can’t be time zones, correct?

144. Joop says:

I wished I had added the question at what moment the updates daily closes should be available in the stock quote downloader. I assumed as soon as the info is available on Yahoo but maybe you do a date and/or time check?

Joop.

145. Keith says:

Can this format be used to download commodity data, i.e. corn, soybeans, etc.?

146. steph says:

HI Samir,
your Excel file is excellent !
However I have tested some quotes for Paris stock place and I also noticed a difference of date.
this issue is nearly the same as Joop one
For example let s say for ERA.PA closing is at 74.09 € the 28th of may (17h30 PM , local time)
Yahoo.com give the correct closing price at this time, but when I run the stock downloader with a closing date at 2015-05-28 : the last downloaded date is the 27th.
I don’t understandwhy the macro ends one day before.
Did anyone notice this ?

Jamir, thanks for your works !
steph

147. Frank says:

Hi Samir,
Thanks again for this excellent VBA. “Normal” stocks collate fine, but when I try to download the Singapore stocks below, the code only collates six stocks and leaves the others out. This is the case in Excel 2007 (32 bit), Excel 2010 (64 bit) and Excel 2013 (64 bit). Does anyone else have the same problem?

A17U.SI
A68U.SI
ACV.SI
AU8U.SI
AW9U.SI
C2PU.SI
C38U.SI
C61U.SI
D5IU.SI
F25U.SI
J69U.SI
J85.SI
J91U.SI
K2LU.SI
AJBU.SI
K71U.SI
M1GU.SI
M44U.SI
N2IU.SI
ME8U.SI
ND8U.SI
O5RU.SI
P40U.SI
Q1P.SI
Q5T.SI
RW0U.SI
SK6U.SI
SK7.SI
SV3U.SI
T82U.SI
T8B.SI
T8JU.SI
TS0U.SI
UD1U.SI

148. Tom Bahder says:

Samir,

I applaud you for making great and useful software. I have tried your Multiple Stock Quote Downloader, and it works wonderfully. It is a very useful tool.

I have observed a strange thing that happens. When I put in the ticker symbol ^HSI for the Hang Seng Index, it is automatically changed to ^HIS. I do not understand why this is happening. Of course, when the ticker symbol is changed automatically by excel, then excel cannot download the data from Yahoo (because there is no data for the changed symbol).

Can you comment why this is happening?

Thanks,
Tom Bahder

149. Wiro says:

Hi Samir,

It is a remarkable work you make.

Can you change to download data from Yahoo finance or other that have indonesian stocks?

Thank you,
Wiro

1. Wiro says:

Sorry, my bad it succed. After I tried again and change some.

150. Jeremy says:

Samir,

You’ve done an amazing job. Kudos to you my friend.

One favor: can you modify the code a little bit to only display the summary sheets (Open Price, High Price, … etc.) instead of creating new sheets for each stock? I would like to do this for hundreds of stocks at a time and it’s not practical to create so many individual sheets.
One solution is to simply delete all the stock ticker sheets after the fact but that’s highly inefficient.

Many thanks,
Jeremy