Download Historical Price Data into Excel using VBA macros

102 Comments

1. Awesome! I looked at switching to Google data which is also free, but it didn’t include adjusted close or dividends/splits, so not much use to me. I was ready to switch to a paid subscription, then I found this page. Its great, I don’t even have to change the URLs I was using or the symbols. Thank you so much Andrew.

2. Was working initially. Genius effort. Much appreciated.

   New version not working for me though.

   • Neil,

     Thanks for the kind remark. It wouldn’t work for me either this morning, the URL wasn’t responding properly, but now it seems to be back. Maybe you could try again. You could also up the URL retry count in the VBA code.

3. Seems fine now. Magic.

   Thanks again

4. Thank you for providing this. Much appreciated, however I can not get it to deliver the correct prices for e.g. VWS.CO. The prices I get are a factor 10 ^ 6 too big! Don’t know how to adjust the code or format the columns to get the right prices. What to do? Any ideas? On beforehand thank you.

   • This is interesting. I get the same data with EmulateURL as I get with the Yahoo website (around $600) so I don’t think it has to do with the data. I wonder if it has to do with localization for your Excel or operating system. Try formatting your columns as a number instead of currency.

5. I get de message: Unexpected error

   • Hi Nagini:
     It is working for me. Can you tell me a little bit more about the data you are requesting? Symbol, frequency, dates etc. Thanks, Andrew

   • I think this is a language issue. I received the same error message (German Win7 + Office 2010). As soon as I switched date and figure formats to Englisch (USA) it worked fine. You can do this in Windows Settings, Region and Langue, Formats.

     I was wondering if there is a simple way to duplicate the sheet including Macros. I copied the sheet but unfortunately the Click Button does not work in the copied sheet, but only in the original one.

     • Hi Nagini,
       I’m looking into the localization issues. On the click button issue, you just have to insert a shape and assign a macro with it (right click the shape and select Assign Macro). However, my example spreadsheet also has named cells which are referred to in the code. You can see them all if you go to the Formula tab and click on Name Manager. You can duplicate the names in your sheet, or refer to the cells using “$A$1” notation in the code.
       Andrew

     • I fixed the copy issue, now you can copy the sheet and it works for each copy.
       Andrew

6. On the volume differences, it could be that one includes off hours trading and the other does not…just a thought.
   Thank you for all you do.
   Kind regards,
   Rob

7. Hi Andrew, Thanks for working to solve this frustrating new problem with the Yahoo Historical Data API. I converted your VBA logic to Java using Apache Http Connection package. Everything seems to be working great. I will continue testing…

   Once it is fully tested, I will add it in my application.

   Thanks again, let me know what you think?

   https://github.com/bigttrott/thebubbleindex/blob/master/application/thebubbleindex-standalone/src/test/java/org/thebubbleindex/data/test/YahooCrumbTest.java

   • That gives me a lot of pleasure. Nice job, Bigttrott. You’ll soon be back in action.
     Andrew

8. I’ve noticed that mutual fund daily prices are not current as are regular stock prices. However, they are current when viewing the prices directly on the Yahoo Web site.

   • As of today (Sat July 8th 2017) the daily data for mutual funds from EmulateURL only appears to go up to July 3rd. If you click on “historic data” on the Yahoo website, that data table also goes to July 3rd. So that is consistent, however Yahoo also give a current price at the top of the page for the July 7th close. This price does not appear in the query so you would need to do a separate query for the current price. I don’t know why there is no data for the 5th and 6th (4th was a holiday). Anyone know?

9. Hi

   I’ve noticed a problem with UK listed stocks (share codes ending in “.L”). It seems like a similar problem you had previously which you fixed with the newer version.

   Once again, much appreciated.

   • Thanks for spotting this Neil.
     Actually, the problem seems to be with all stocks, not just .L . On the EmulateURL output and on the Yahoo web page, the Close column is now showing the Adjusted Close price, and the Adjusted Close column is showing the actual Close price. I would expect Yahoo will fix this soon, otherwise I will need to issue a temporary fix.

10. Hi Andrew,
    Once I had discovered XL Automation, and from there the link from their website to yours I realised I had (at least for the time being) resolved the problem of how to download stock prices from Yahoo finance. It mostly works beautifully and I am very happy that the results compare closely with the data I was getting prior to the URL change. So many thanks for that.
    I just have these comments:
    The end-Date mostly works but if the end date is not today’s date it seems to prefer the latter.
    The start-Date is not honoured – it allows quite a cushion of dates prior to my given start-Date. The best I can say is that it somehow seems to end up switching start_day and start_month values. Is this possible? By the way are they string or integer?
    It has correctly applied the adjustment to ‘adjusted close’ prices, which is great. The values are very close but not exactly the same as the adjusted prices used to be.
    Most curious of all, I get access all the tickers I am looking for, except YHOO itself. Do you know why this is so??

    Best regards, Clive.

    • Hi Clive
      Are you using the EmulateURL VBA function, the GetHistoricPrices.xlsm or the EmulateURL-V1.0b.xlsm spreadsheet? The start_day vs start_month issue may be explained by the fact that in the UK the date is expressed dd/mm/yyyy, but here in the USA we use the less logical mm/dd/yyyy . Could that localization explain this behavior? EmulateURL takes the month, day and year as part of the URL strings. In the GetHistoricPrices.xlsm spreadsheet I convert the spreadsheet date cell d to strings within the URL using:

      qurl = qurl & “&a=” & Format(Month(d) – 1, “00”) & _
      “&b=” & Format(Day(d), “00”) & _
      “&c=” & Format(Year(d), “0000”)

      It works OK for me here in the USA.

      The adjusted close price and the actual close price are currently interchanged, a major bug in the Yahoo data. I’m waiting to see if this is corrected soon. See note at the top of the web page. I don’t know if this explains the discrepancy you are seeing.

      Yes, its true, you can’t get historical data for YHOO from Yahoo. Its a mysterious thing.

      Andrew

      • The date issue is now fixed. It version 1.0e works in the USA and Europe. Thanks again to Dr. Volker Wendel for testing this in Germany.
        Andrew

11. Thanks Andrew, I’m using the EmulateURL function inserted as a module in my macro by copying and paste-ing from the text version of EmulateURL-V1.0b.xlsm.
    I now realise that the YHOO ticker has become AABA since about 18th June.
    Regards, Clive

12. Dear Andrew,
    Really appreciate for your excellent VBA function which really did me a VERY BIG favor.
    This function/tool has helped me to fix the Yahoo Finance Historical data query in recent days and you also provide the solution for the Close/AdjClose problem timely.

13. Dear Andrew,
    I notice that the “Close” data seemed to be “adjusted” when a stock’s split happened. I thought your V1.0c already fixed. However, I can even find O-H-L-C numbers like 30,31,29,26(adjusted Close even lower than the low value in a day). Is there any way to bypass the “adjusted Close” and just get the current actual “Close” data? Thanks!

    • Hi, could you please provide the symbol or symbols of the faulty stocks so I can take a look and try to figure out what is going on?
      Thanks,

      Andrew

14. Hi Andrew,
    I can now only find such as symbol “1264.TWO” split on 7/4 and the “Close” data before day 2017/7/3 were adjusted which are not the original data. But seems many cases become normal again. Really don’t know why. Thanks!

15. I do not seem to be able to get ETF prices. Is that a limitation, or am I doing something wrong?

    • Hi Cecil,
      Sorry for the late response. I tested it on many ETFs (SPY, ERX, sector ETFs) and it works fine. Can you tell me which ones are not working?
      Thanks,
      Andrew

      • Actually, it seems more general. For example, the list below does not show up (MKTX works sometimes, sometimes not):
        BCR
        AEIS
        MKTX

        Thanks….
        Cecil

        • Hi Cecil
          They all work for me, I tried a few times with no failures. I wonder if there is something different about your code or environment. If you want to send me your excel file I can take a look.
          Andrew

16. I had to fix unixdate formula to make it work worldwide.

    UnixStartDate = (DateSerial(StartDateYr, StartDateMo, StartDateDay) – #1/1/1970#) * 86400
    UnixEndDate = (DateSerial(EndDateYr, EndDateMo, EndDateDay) – #1/1/1970#) * 86400

    • Hi Vincent,

      Thanks so much for pointing this out. My use of the DateValue(“Jan 1, 1970”) is really only good where “Jan” is understood by the date system localization. I will change this, however I will use DateSerial (1970,1,1) instead of date literals, not that #1/1/1970# is ambiguous, but I believe its best practice to use DateSerial since date literals can be ambiguous (e.g. #4/5/1970# could mean April 5 or May 4 depending where you are in the world, correct?).

      Andrew

17. Dear Andrew,

    how to do this step by step:
    If you also want the output in CSV format, you can add the spreadsheet location:
    EmulateURL MyURL, Sheet1.Range(“A1”), Sheet1.Range(“J1”)

    • Hi Marzuki,
      Yes, your code snippet should work. You can also overwrite the data at A1 if you only need the CSV data. Are you having any issues?
      Andrew

18. Andrew — They also now work for me. I suspect intermittent internet was the problem. Thanks for your patience.

    Cecil

19. File doesn’t work. It appears thi error message “EmulateURL: Unexpected error”
    Why?

    • Hi sam,

      Probably the date format in your locale. Try Vincent’s fix (see above).

      Andrew

      • Now it works but the numbers are without commas. For example it appears 4223896 rather than 42,23896. Why?

        • Hi Sam,
          I think you may need to change your Excel decimal separator from comma to period, because Yahoo uses period as the decimal separator in its data. Here’s an article on how to do it: http://www.solveyourtech.com/change-decimal-separator-excel-2013/ . Let me know if you are able to solve it.
          Andrew

          • Hi Andrew, I’ve tried like you suggested, but nothing. Any period appears.. what else could I do?

          • Hi Andrew I solved using another way. This is my solution:

            To replace:

            If IsNumeric(OutCol(ThisColumn)) Then D(ThisColumn) = CDbl(OutCol(ThisColumn)) Else D(ThisColumn) = 0

            with:

            If IsNumeric(OutCol(ThisColumn)) Then D(ThisColumn) = CDbl(Replace(OutCol(ThisColumn), “.”, “,”)) Else D(ThisColumn) = 0

            And to replace:

            If ThisColumn > ColumnMax Then Comma = “” Else Comma = “,”

            with

            If ThisColumn > ColumnMax Then Comma = “” Else Comma = “;”

20. Hi,
    Works great but I get the number “5” instead of “.” on all the prices, in TABLE mod .

    • Hi Ran,
      Thanks for testing this for me. I’ve put up a new version (1.0e) which I hope will fix this issue. If you get a chance, please try it out.
      Thanks,
      Andrew

21. Looks fine.
    Thanks Andrew.
    Anything similar based on GOOG Finance? Recently they eliminated the historical download option

22. Is there a way to change the stock symbol to run for multiple sotcks at once? perhaps a + or a comma?

    • Hello Matt. The webquery only accepts one symbol. But, you could code it up in VBA to call EmulateURL several times.

23. How can we use data from another stock market (i.e. Australia) i am having a bit of trouble thanks

    • Hello Alex,

      You have to use an exchange suffix for non-US stocks. The list of Yahoo exchanges is at https://help.yahoo.com/kb/SLN2310.html . For Australia, the suffix you use is .AX. So, for example, Super Retail Group Limited with symbol SUL would be SUL.AX.
      Hope this helps,

      Andrew

24. This is awesome! You’re a true public benefactor, my friend Andrew!!

    Tony

25. Thank you very much sir ,,,,,
    if would allow me , I am new to excel , and i saw your answer to Matt : Hello Matt. The webquery only accepts one symbol. But, you could code it up in VBA to call EmulateURL several times.
    could you please elaborate more on how to that ?
    much thanks !

    • Saleh,

      I’m not encouraging the use of the code for bulk downloads of many symbols at this point. I think it could put a strain on the servers and lead Yahoo! to obscure the service even more.

      Andrew.

26. There is a problem when using ending date for a Monday. Your URL seems to be correct but yahoo will only return data for a Friday and drop request for Monday. Is this a problem with code at Yahoo?????

    • Thanks Don,
      I will check this out. Meanwhile, folks should be aware of this issue.

27. I use MarketXLS for this.
    Hope it helps.
    They have regular updates and customer support.

    • Thank you Jonathon, I’m sure MarketXLS is a fine product. Current pricing for the version with historical prices and quotes is $240 per year. If folks just want to get live quotes (one minute delay for most US stocks, kudos to Yahoo!) and historical data into Excel they should also consider using the JSON fetch spreadsheet. It currently costs zero dollars a year and is supported by Algorithm Science.

28. Andrew
    This is great, and seems like the best solution to the issue I had since yahoo stopped their service. Also it helps that the codes including currencies & indices are all the same.

    I do have a question which I’m hoping you can give me some guidance on: I’m looking at pulling in the price (either 20 min delay or last close price) for ~150 stocks across different exchanges. Is it possible, and where best to put the loop that would churn through the codes and just keep on adding to the bottom of the table?

    • Hello Deepak,
      Glad you like it. If you are going to loop, use the JSON interface https://www.signalsolver.com/JSON as it is faster (only uses one query) and prices are only delayed by a minute or so. However, I do discourage looping because it chews up server bandwidth. I’m looking for alternatives.
      Andrew

29. Awesome! It’s work in HKEX as well.
    Thank you very much!

30. I see the Aug 8 note from Vincent and your Aug 14 reply.
    I’m in Australia using dates dd/mm/yyyy and getting an error when having a Start Date of 2/1/2018 and End Date 14/1/2018. The error message ends with “Invalid input – start date cannot be after end date. startDate = 1517443200, endDate = 1515888000′)))”
    Please advise of changes to code that I should make.
    Version 1.0f

    • Hi Kevin,
      Try changing the format of the date cells (right click->Format cells) to use a Date format Which is preceded by an asterisk. These formats respond to changes in regional date settings.
      Andrew

      • Hi Andrew
        Thanks, however it has been that way during the tests.
        Kevin

      • Hi Andrew
        I’ve interpreted Vincents’ info above and the following at Ln 172 & 173 appears to be working.

        UnixStartDate = CStr((DateValue(StartDateDay & “/” & StartDateMo & “/” & StartDateYr) – #1/1/1970#) * 86400)
        UnixEndDate = CStr((DateValue(EndDateDay & “/” & EndDateMo & “/” & EndDateYr) – #1/1/1970#) * 86400)

        You may recognize other problems with this and if they are likely, I would appreciate your further advice.
        I shall continue on to investigate whether this suits me long term.
        A contribution will be forthcoming in the event that I make permanent use of your methods.
        Thanks for the inspiration to continue to find a solution for my intended use.
        Kevin

        • Hi Kevin,
          Not sure why #1/1/1970# would work better than DateSerial(1970,1,1), but I guess it does. I plan to change it back to date literals in the next release.
          Andrew

31. what is url for daily prices, i only get monthly?

    • Steve

      use &g=d in the URL instead of &g=m

32. The worksheet is named “Historical” and in the VBA Module 1 the worksheet object is named “HistoricalSheet” in line 4. It works, but why?
    Very nice programming, thanks

    Dennis Loeffler

    • Hi Dennis,
      Its a bit obscure 🙂 but each worksheet object actually has two names, the name on the worksheet tab and a “codename” which is the permanent name of the object inside of VBA. If a user changes the tab name and the VBA code refers to it, everything breaks, so a coder will generally use the “codename” instead as it is less likely to be changed by the user. See here for more info.

33. Thank bro, it helped really alot! =)

    • Jeff,
      Glad you found it useful!
      Andrew

34. Getting the message “No valid crumb found after 7 times”

35. Now getting the message “No valid crumb found after 5 retries” I noticed that on April 5, 2018, not sure if it started earlier.

36. I keep getting No valid crumbs found. Solutions for this?

37. Hi Andrew,

    Thank you very much for your sharing. I downloaded your VBA code and it had worked very well until March 29,2018. I am wandering if Yahoo changed something again. Have you noticed the change?

    Thank you.

    Peter

38. Andrew

    The code had worked very well until March 29,2018. I am wandering if Yahoo changed something again. Have you notice it?

    Thank you for sharing.

    Peter

    • Peter (and others):
      It looks as though there is a problem. Unfortunately I am away from the office and can’t look at it for a few days. Please bear with me for a little while. Meanwhile, suggest using the JSON interface if you are able to make the switch.
      Andrew MacLean

39. Just replace

    “https://finance.yahoo.com/lookup?s=rubbish”

    with

    “https://finance.yahoo.com/lookup?q=” & StockSymbol

    to get the crumb. Don’t know why but the dummy one is no more working. Opening the ticker page then downloading the historical will help you disguise as a normal user.

40. The string “CrumbStore”:{“crumb” is no longer part of the http response.

    • I was able to get it working after changing the search pattern from: “CrumbStore”:{“crumb”

      to be based off this part of the http response:

      …”bkt”:”finance-US-en-US-def”,”crumb”:”wbgfBtCFUkT”,”device”:”featurephone”…

      i.e., in Java: final String crumbSearchString = “bkt\”:\”finance-US-en-US-def\”,\”crumb\”:\””;

41. Andrew – When will you have a JSON version of SignalSolver running, Cooke and Crumb not working

    • I’m away from the office and I’ll be back on April 24th. I’m hoping I’ll get a chance to look at it before then. It could cause significant issues if EmulateURL is no longer working because I use it not only for prices, but dividend data and I would need to find a way to replace that functionality. Sorry I can’t be more specific.

42. Thank you DiegoVega! Your solution works great!

43. Implemented DiegoVega worked Thanks
    SignalSolver Still not working, can you do same ““https://finance.yahoo.com/lookup?q=” & StockSymbol” to solve Crumb issue?
    If so what module?

    • Paul
      I have published a new version of SignalSolver (1.3k) which should solve the issue. Please let me know if you have any problems running it.
      Andrew

44. I have purchased SignalSolver today after running for several days. 3 question and 1 suggestion
    Question 1 – Do you have a comprehensive guide / definitions of all tabs and settings on tabs in PDF?
    Question 2 – On Scan View Page – do you have more detail on the uses of this page and suggestions on optimize type, I am currently using Figure of Merit?
    Question 3 – I live in center time zone and my 15 min. delay data does not appear in your sheets until 9:45 am when market open is at 8:30 am central time. I have changed setting on the Trades tab to have local time at 8:30 am, what other setting do I need to change?
    Suggestion 1 – Use JSON download that you describe in excel downloads ( good sheets ) to be able to get 1 minute downloads during the day, and at open, so that you do not have to go to other sources to get opening or closing data to take suggested actions on report tab.

    • Hello Paul, Thank you for purchasing SignalSolver. In answer to your questions–
      1. No, I don’t have a PDF guide at this point, but if you click on the blue cells, it takes you to the web help pages which I think are pretty comprehensive.
      2. You can set the scans up to show how changing the buy and sell points influence various things (return, risk-reward, etc). The Figure of Merit is a Weighted combination of Return, Time in Mkt, Drawdown, Minimum quartus return etc. You will need to set up the weights as to what is important to you. For example, you may be more concerned about total return than consistency of return, or reward-risk. It depends on your own style of investing.
      3. Set the Open Time to the market open time in the timezone the market is in (9:30AM for NYSE) and set the close time the same way (4:00PM for NYSE). Set the local time offset to the offset between your local time and market time. Set the UTC Offset to the offset between the market and the UTC Time Code (GMT). This is -5 hours for NYSE. I had to add UTC when Yahoo started using it. Sorry this isn’t clearer, I will improve the comments on the cells and add it into the Help files. I will probably move these settings to the Settings page in the future.

      Your suggestion to use JSON data to get the price updates is already in place. EmulateURL is used for historic data, JSON is used for updates. I also use other sources for dividend data.

      Andrew MacLean

45. Is there a way to pull data for more than 1 stock symbol at a time using the excel version of signalsolver? Thanks,

    • Ryan,
      I’m afraid SignalSolver pulls data for only one stock at a time.
      Andrew

46. This is great. But can someone tell me how to use it to get the S&P500 or the Russell indeces???
    Thank you in advance.

    • Russell 2000 symbol is ^RUT S&P 500 Symbol is ^GSPC . Use the JSON interface to get 1 minute granularity.
      Andrew

47. Is it just me or is all the data for August 13th missing?

    • Steve: Its not just you. I’m seeing the same problem, Yahoo are not reporting data for yesterday, Aug 13th 2019. I’m hoping its just a glitch, but we’ll see…

      • OK, I see data for the 13th now, so, yes, it was just a glitch.

48. Hi Andrew,
    Just checking in, are there any updates to SignalSolver?
    At top of spreadsheet it shows version 1.3v

    Thanks,
    Steve

    • Hi Steve,

      Thanks for your recent purchase. A new version of SignalSolver 1.3w is currently being tested. I hope to release it this month. It expands on the traffic light features to better gauge if a security sentiment is currently bullish or bearish based on aggregate opinions of several algorithms. You can also trace how well aggregation worked in the past under different conditions (no. of algorithms, optimization methods and other things). Plus, you can see how well a given algorithm or set of algorithms did when re-optimized every x periods. The results have been quite interesting. Plus it adds a few smaller features.

      Andrew

49. Hi, I’m wondering if Yahoo have changed their authentication rules as I’m not getting any stock price results

    • Hi Peter:
      I’m not seeing any problems, either on the CSV interface or the JSON data.
      I’ll contact you by email and try to resolve…
      Andrew MacLean

50. Hi, I’m having a problem trying to pull daily values in table format from the S&P 500. Everything works except I’m not getting the closing price for the end date I enter in. It’s like it’s stopping a day short. It only works if I add 1 to the “&e=” Day format in the qurl variable, but I’m not sure if this is how it is supposed to work since it always worked previously without adding anything. Why is this occurring?

    • Its because Yahoo changed the way the query works. You need to add a day to the end date.

      Andrew

      • Thanks for all your work on this. The end date seems almost random now. Sometimes I have to add 1 day to the end date. Sometimes I have to add up to 4 or 5 days to the end date for certain symbols. Even for the same symbol, the end date of the data changes randomly. Any suggestions on how to handle this? Currently, I keep hitting the button till I get the end date I need.

        • Hi Steve
          I haven’t seen this particular problem myself, but Yahoo have been making changes to the response format of the EmulateURL query, I’ve seen changes in both the splits and the dividends data and I am working on new versions of SignalSolver, PriceHistory and JeX to address these issues, in fact the latest SignalSolver release addresses the split format change. For prices the JSON interface is not giving any problems as far as I know, so you should probably use that for now.

          Regards,
          Andrew MacLean

      • I was using the end date +1 myself since first started using this macro about two years ago. It used to update putting the latest quote in for today. But now I can not get today quote at all. It simply ends at yesterday’s date. I have to manually enter a price for today to try to make a trade.

        • Matthias
          Yahoo changed the query response for the CSV interface. The fix is to edit this line in EmulateURL:

          OutRow = Split(WebRequest.ResponseText, vbLf): RowMax = UBound(OutRow) – 1

          Remove or comment out the “-1” like this:

          OutRow = Split(WebRequest.ResponseText, vbLf): RowMax = UBound(OutRow) ‘ – 1 FIX

          This does not affect the JSON query.
          Andrew

Comments are closed.