Your 'Go To' for Custom Excel Solutions

Yahoo Historical Price Extract

This free spreadsheet is a solution for share traders who relied on the Yahoo Finance historical price extract feature that was suddenly discontinued.  You can use this new method in Excel to extract the historical price for any security from the Yahoo Finance website.

Yahoo Historical Price Extract

Yahoo Finance has always been a great source of information for share traders. Some recent changes in the format of the Yahoo website have resulted in errors for many of the extract and data scraping methods that many Excel users have relied upon. This post will describe a new method that can be used within Excel to extract the historical price for any security from the Yahoo Finance website. In a future post this will be extended to extract dividend information for any security.

A quick search of the Yahoo forum reports the advice from "Official 'Hoo Staff" that the Finance API has been discontinued by the Finance team and that they do not intend to reintroduce that functionality.


The revised finance website from Yahoo offers the same information but with many more options. The ability to download historical prices has significantly changed but with some new routines you can easily replace your existing historical price extract routines in your own Excel workbook. Here are the steps that we took to create this revised method.

The URL https://au.finance.yahoo.com/quote/BHP.AX/history?p=BHP.AX provides the historical list of prices to your browser. The default is to display daily prices for the the previous year.

If you hover over the "Download Data" link you see the following URL

(note: the URL above worked when the post was first created, but due to the validity periods for the crumb parameter it may no longer work)


Historical Price URL

The download URL is the made up of the following components that we have replicated below to automate the retrieval of the historical price data into an Excel workbook.

The new starting query URL string and includes the security code. Only one security at a time for this request.


The start and end date for the data download. These are in a unix date format that is calculated from 1st January, 1970. In this case the date range was 25 May 2016 - 25 May 2017.


The interval for the extract is daily with "1d" and the historical price is returned via the events parameter.

Finally the crumb which was the most complicated part of the download URL to replicate. We found in our testing that this could sometimes be re-used and other times needed to be regenerated. It was paired with a cookie that was not contained in the URL request that also needed to be supplied for the download to succeed.

Key Code Components

We provide a sample Excel workbook at the bottom of this post with all the required code for you to replicate the extract of historical security prices from Yahoo. The key code components to achieve the extract are shown below with some explanation. If you have questions or comments on our approach please let us know in the comments at the end of the post.

Set the Unix Date for Start and End Date

Private Function strGetUnixDate(dteSetDate As Date) As String
'This function will set the Date required in the URL to the Unix date format
    strGetUnixDate = (dteSetDate - DateValue("01/01/1970")) * 86400
End Function

Get the Yahoo Request to set the Crumb and Cookie values

Sub GetYahooRequest(strCrumb As String, strCookie As String)
'This routine will use a sample request to Yahoo to obtain a valid Cookie and Crumb

Dim strUrl                      As String: strUrl = "https://finance.yahoo.com/lookup?s=rubbish"    
Dim objRequest                  As WinHttp.WinHttpRequest
    Set objRequest = New WinHttp.WinHttpRequest
    With objRequest
        .Open "GET", strUrl, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .waitForResponse (10)
        strCrumb = strExtractCrumb(.responseText)
        strCookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
    End With
End Sub

The sample URL was found by trial an error to return a valid Crumb and Cookie that could then be used for the later call to the Yahoo Finance page to extract the historical prices. The Crumb value is extracted from the responseText returned using a string match. The Cookie is found in the responseHeader.

New Features - Historical Price Extract v8 - Released 3rd January, 2018

In response to your feedback we have updated the sample workbook to include features that make the process of extracting price data simpler, quicker and more accurate. Download an updated copy below if any of the new features will be useful for you.

  • Security Code is included to each row of the extracted data
  • Fix to the sorting of data so that it will now respect the sort in Ascending or Descending order
  • Error checking has been enhanced to report on errors returned from Yahoo for the security code supplied. These can include errors within the data or simply that the security code cannot be found
  • When an error is found for a security code it will be listed in a separate table of errors and include a URL to the Yahoo website for you to investigate the cause of the error
  • A Debug log has been provided that records the sending URL that has been used along with the returned response. This will be helpful for those attempting to track errors in data
  • The web calls to Yahoo can be made by WinHTTP or ServerHTTP. These are very similar methods but with slightly different classes. In some cases users of the worksheet may find that only one of these methods works in their environment
  • Data warnings, once the data is returned for a workbook a new scan has been included to check if the data contains the expected dates and numbers for extracted data. If it does not then the data is still returned but will include a warning.
  • Fix to Unix Date function, in the previous version some  users reported issues with the Unix Date function a different date format has been set for this version
  • Update to include formatting of the Dates and Numbers included to the extracted data. Those formats now set for the data returned and then copied to the worksheet or CSV files

Tell us what other features you think this workbook should have and we will include it to the enhancement list. Click on the button below to jump to the page comments

Another Free Spreadsheet by XLAutomation.

If you found this spreadsheet helpful please make a donation.

Free Sample Excel Workbook

See below an image to demo the sample workbook that is provided. Enter the security code and required date into the highlighted cells then click "Update Prices". The historical price will then be returned for each security. The date returned will be the closest possible prior to the supplied date.

Note: This workbook has been tested on Windows running Office 2013. No testing on MAC has been performed. It may work for a MAC environment but will require Windows virtualisation.

Download Your Free Historical Price Extract Spreadsheet

We will use the details entered below to add you to our mailing list to receive the free download file and latest news about free spreadsheets and products from the XLAutomation team. You will have the option to unsubscribe at any time. 


Note: If you previously downloaded Historical Price Extract, enter your email address again to receive the updated version

UPDATE 4th April, 2018: A recent change to the Yahoo Finance website has affected this Historical Download routine. It may initially download historical prices but could stop after a small number of securities or prices are returned. We are investigating this now and will advise all those on this download list when it is resolved and we have a new version that will continue to provide access to historical prices. Download the workbook to get yourself added to this list and receive notification when it is resolved.

Another Free Spreadsheet by XLAutomation.

If you found this spreadsheet helpful please make a donation.

Suggestions for next version of Yahoo Price Extract

1. Include Splits and Adjust Prices Overtime

Update the extract options to include an option that takes into account "splits" reported by Yahoo and then an adjusted the share price to account for the split overtime. This would include additional columns to the extract to list when a split occurs for a security and then an "adjusted share price" column to reflect how the share price has been affected for a single share over the time of the extract period.

2. Option for Output Directory and File Format

Provide an option to specify or select a directory that will be destination for the output files. As part of that include an option to specify a File Format for the output files.

Beta Testers needed for the next version

The next version of Yahoo Price Extract will deliver the most requested feature Splits and Adjusted Prices. We plan to offer the next version as a paid product for purchase.

To ensure our team hits the mark we need "beta" testers to participate in the development of the next version. All "beta" testers that participate and provide feedback during the development phase will receive a free copy of the next version. If you are interested in becoming a "beta" tester please click on the link below and send us your contact details along with the message "Add me to the Historical Price Extract "beta" testers".


As voted by our website visitors the following Yahoo Price Extract suggestions that will be next on our list are ranked below. Have your say and use the voting form below to promote your favourite suggestion to the top. For details on each of the suggestions listed in the rankings refer to the short descriptions.

Vote for your preferred suggested improvement to the Yahoo Price Extract workbook

Tell us what you think...

Thanks to the trading community who have downloaded our spreadsheet. We would love to know how useful you've found this solution, and if you think we could include anything else to make it even better! Leave a comment and let us know.


  1. Joe on May 8, 2018 at 4:00 am

    I have tried to run the Yahoo Historical Extract v. 8 and also older v. 5, no success, “Invalid cookie” message for all stocks. Any advice appreciated.Thanks a lot.

    • Scott Lindsay on May 8, 2018 at 4:55 am

      Joe, we are still investigating this problem. A change by Yahoo has “broken” the Yahoo Historical Price Extract in early April, 2018.

      • Robert H Pfeiffer on June 9, 2018 at 5:50 pm

        Thank you very much! For me it worked until 30 May 2018 but now it does no longer. Would very much appreciate if you can develop a fix and very happy to make another donation. I love your code!

        • Scott Lindsay on June 10, 2018 at 7:27 am

          We are still investigating a fix for the latest change by Yahoo.

          This time they have made it very difficult to extract the historical prices. If we are able to solve it we may advise that this spreadsheet needs to become a paid product or subscription that we can then provide support for. Stay in touch with this page for updates…

  2. Wei on April 20, 2018 at 11:43 am

    My fIx for unable to get crumb and “invalid cookie” bug:
    1. For the “unable to get crumb” problem, go to GetYahooRequest and GetYahooRequestServerXMLHTTP function,
    replace “https://finance.yahoo.com/lookup?s=rubbish” by “https://finance.yahoo.com/quote/AAPL/history?p=AAPL”
    Please note that even in a browser, https://finance.yahoo.com/lookup?s=AAPL is no longer working as expected.
    2. For the invalid cookie bug:
    Sometimes strExtractCrumb will contain string “\u002F”. It should be replaced by “/”.
    The solution is:
    (1) Add the following code to the end of strExtractCrumb function
    If InStr(strExtractCrumb, “\u002F”) 0 Then
    strExtractCrumb = FixCrumb(strExtractCrumb)
    End If
    (2) Write the following function in any of the Modules:
    Private Function FixCrumb(strExtractCrumb As String) As String
    strExtractCrumb = Replace(strExtractCrumb, “\u002F”, “/”)
    FixCrumb = strExtractCrumb
    ‘Debug.Print strExtractCrumb
    End Function

    • Wei on April 20, 2018 at 11:46 am

      Oh, there is a typo:

      If InStr(strExtractCrumb, “\u002F”) 0 Then
      strExtractCrumb = FixCrumb(strExtractCrumb)
      End If

    • Wei on April 20, 2018 at 11:49 am

      Due to the restriction of this site (maybe it is designed to prevent XSS?), I cannot type in the “not equal” sign here. Add that sign before 0.

    • Wei on April 20, 2018 at 12:18 pm

      Well, rather than using a seperate function, three-lines code is enough:
      If InStr(strExtractCrumb, “\u002F”) 0 Then
      strExtractCrumb = Replace(strExtractCrumb, “\u002F”, “/”)
      End If

      • Pfeiffer Robert on June 9, 2018 at 6:05 pm

        Hi Wei – Thanks a lot for proposing a solution but it appears that this no longer works. Any other idea?

  3. KJ on April 7, 2018 at 3:56 am

    It’s not working as of April 4, 2018. It can’t obtain crumb.

    • Scott Lindsay on April 7, 2018 at 6:13 am

      KJ, yes I can see that it is failing again. But it is not directly failing there may be some new protection that Yahoo has included to it’s data format that is impacting our extract process. Any pre-saved cookie and crumb pairs seem to be working still but only for a short period. After a small set of securities (eg approximately 20) it reports an invalid cookie. We will investigate to see if this can be solved again and a further change made to address it..

    • MP on April 8, 2018 at 12:16 pm

      Yes, it blanks out after a little over 100 requests. Will test here if looping sessions or blanking the stored cookie and crumb more often will help.

    • MP on April 9, 2018 at 3:11 am

      The extract seems to be working again for now, with no limitation on number of queries. Previously, it looked like the crumb was not being resolved. It started showing up blank during extracts. Yahoo is likely tweeking their protocol, but in any case it seems to work now as before.

  4. Jer01 on March 4, 2018 at 6:47 am

    3/3/18 This program does not give correct hi lo cl data for either the past week or the current month for my symbols today. When I go to the Yahoo page, the data there appear to be bad also. I did not try to download the data and look into the downloaded data files. I have been using this program for about two months now [ in a limited manner], and this is the first time I have not been able to get useable data. I have had to work around some irregularities though. I assume you are scraping, and therefore we will get what is on the web page — not in the downloaded data files.

    • Scott Lindsay on March 6, 2018 at 6:48 am

      Jer01, thanks for reporting the issue you have experienced with Yahoo data. This is not a new problem and one that many have reported when using Yahoo data in the past. The routine provided in this workbook does scrape the data returned from the various URL calls to the website for historical data…

  5. Dilshod on March 1, 2018 at 2:17 am

    Is there any chance to add intraday real time data with intervals like 1m, 2m, 5m, 30min with data extending several days backwards?
    I’m happy to pay for upgrade if it is going to be within affordable limits.

    • Scott Lindsay on March 2, 2018 at 3:22 pm

      The historical price extract provides EOD prices only through the source websites that we have available. It is possible to get intraday prices through the other feeds but I don’t think it provides the intervals that you are looking for….https://xlautomation.com.au/free-spreadsheets/share-trading-portfolio

  6. Pete on February 6, 2018 at 5:41 am

    I downloaded and tried but it doesn’t work. Are you aware of it?

    Thank you

    • Scott Lindsay on February 6, 2018 at 6:58 am

      Not sure why it is not working for you…I have just tried the workbook again and it is functional and still pulling download historical prices. You will need to give us some more information to investigate why it is not working for you….are you receiving any error messages…?

      • Pete on February 6, 2018 at 1:38 pm

        I don’t see anywhere to attach snapshot.

        I copy Microsoft Visual Basic error window:

        Run-time error ‘-2146893018 (80090326)’:
        The message received was unexpected or badly formatted.

        I clicked on debug button. Error stops below:

        With objRequest
        .Open “GET”, strURL, False
        .setRequestHeader “Cookie”, strCookie
        ‘.setRequestHeader “Cache-Control”, “no-cache, no-store, must-revalidate” ‘Tried and did not make any difference
        error stops –> .send
        .waitForResponse (10)

        Call WriteResponseDetails(“WinHTTP”, “History”, strURL, .ResponseText)
        strResult = .ResponseText
        End With

        • Scott Lindsay on February 7, 2018 at 5:34 am

          Ok that looks like the attempt to send the request is failing….are you behind a proxy server or using a computer behind a firewall ? In some cases the attempt to call out for the data is stopped by network protections. Can you try some of our other free spreadsheets that also attempt to retrieve data from the internet….Share Trading Portfolio. It uses a different method to call out that does not use a cookie and crumb….let’s see if that works….if it does then you have something to investigate on your end. If it does not work then both of the spreadsheets are failing and I suspect you have a network issue preventing this type of communication from reaching out…

          The workbooks are still working from this end and we have not other reports of issues….hope this helps you get to a solution.

          • Pete on February 7, 2018 at 12:35 pm

            my network has no proxy server, firewall and antivirus. I also set Excel’s trust to low. I will try Share Trading Portfolio and will report any problem in here.

            Thank you, Pete

            • Pete on February 9, 2018 at 2:38 pm

              Well, it has the same error. I give up. Thank for helping.

            • Scott Lindsay on February 12, 2018 at 6:13 am

              Pete, for these free spreadsheets the source code is provided and if you strike problems in your own environment the resolution is for you to progress. If you want to get consulting support to investigate and get it working we can provide that service but will charge for that work. Sorry that the free spreadsheet is not working for you…

            • Pete on February 14, 2018 at 4:54 pm

              Thank you for offering help.

              The spreadsheet I have many years ago worked well; but now, Yahoo changes without letting users know. I don’t know what next step they will do unexpected changes. It’s not worth effort and money to invest. In addition, it’s slow. I don’t like Yahoo Finance anymore.

              I found other sites having data. so move on!


            • Pete on February 14, 2018 at 4:57 pm

              I forget one thing. Yahoo’s data randomly is unreliable. I use the download option directly under historical data. Just want you be aware of it.

  7. Jer01 on January 4, 2018 at 9:05 am

    Here is what I found with my first look using version 8:

    1/3/2018 Settings: daily, desc, 30
    — got only 22 days of data
    — the response tab is now updated with my symbols
    — asc/desc option now works

    1/3/2018 weekly, desc, 30
    — got 31 weeks of data
    — the response tab is now updated with my symbols
    — asc/desc option now works

    1/3/2018 monthly, desc, 30
    — got 31 months of data
    — the response tab is now updated with my symbols
    — asc/desc option now works
    — 6/30/17 gives null data
    — I do not get the prices displaced by one month that Wei got
    — 6/02/17 does not give null data — it gives full month data for June 2017

    1/3/2018 monthly, asc, 30 and monthly, desc,30
    — got 31 months of data
    — the response tab is now updated with my symbols
    — asc/desc option now works
    — 6/30/17 gives null data
    — I do not get the prices displaced by one month that Wei got
    — 6/02/17 does not give null data — it gives full month data for June 2017

    Other: Cols C & D give first data with ascending, give last data with descending

    • Scott Lindsay on January 4, 2018 at 2:31 pm

      Thanks for the review again….the issue with the data is not one that can be solved via the workbook it is from the source website.
      The other issue for the different order of the data returned in Cols C & D I will resolve in a future version….

  8. Jer01 on January 4, 2018 at 7:01 am

    I made MONTHLY extrs for two more end dates on 1/3/18.

    12/01/17 end date gave nov 2017 data. No dec 2017 data
    12/02/17 end date gave data for the full month of dec 2017
    So this confirms what I said earlier. Any end date in Dec 2017 [after Dec 01 ] will yield all the Dec data in the Yanoo database for Dec 2017.

    Thanks for fixing the ascending/descending issue. I will dl the latest version.

  9. Jer01 on January 3, 2018 at 9:18 am

    1/2/2018 I tried out some MONTHLY extractions.
    Since I did not have the same problems that Wei reported, I thought I would share my experience here.
    I did all these extrs on Mon 1/1/2018 a market Holiday. The full month of Dec was in the Yahoo database at the time of the extrs.
    I set the no of months to extract to 40
    Then I made five different extrs — based on five different dates: Thur Dec 28, Fri Dec 29, Sat Dec 30. Sun Dec 31, Mon Jan 01
    —–Note Mon was a market Holiday.
    In all cases I got back 41 entries [months], even though I asked for 40.
    In all cases the dates for the months were the first day of the month. — e. g. Dec 01 2017. Not end dates.
    All data appeared to be good except the very first entry, which was null. The data was in ascending order.
    —– But since this was data for one month before the requested dates, the extraction was good. Just throw away the first entry.
    So it looks like I got all the data that was there for Dec 2017, no matter which date I entered as the end date.

    Because of the Null data I got error mssgs and urls to check the data. So I went to yahoo and checked the data shown on the website.
    It appears Yahoo is having lots of problems displaying their data since the changes they made.
    They can’t make up their mind which way they want to display dates — some were Month beginning, some wore Month end.
    None of the November dates were there. They were all missing.
    Some months appeared with two entries. One with Month beginning, one with Month ending dates. And with different price data in each.
    SHAME ON YAHOO!!!!!!!!

    Note: I did not try to dl any of the files. The Yahoo data was website — not csv.

    Note: Later [1/02/2018] I tried an extr using decending order. The data still came in ascending order, but the early Null data entry was not there. I got the 40 months I asked for — not the 41 entries I got earlier.
    —– The extr was good. Except tor the order of dates.

    Overall not bad.

    • Scott Lindsay on January 3, 2018 at 5:19 pm


      I have just released a version of the historical price extract that includes a fix to the Ascending / Descending order for the data that is exported. If you download again you should see this working. The new version is Historical Price Extract v8.

      Thanks for your feedback…

  10. Jer01 on January 1, 2018 at 7:49 am

    12/31/17 OK, I looked at my WEEKLY data in more detail. It appears that I/We can’t get Weekly historical data to extract properly.

    Let’s start by stating that Yahoo uses Monday dates — and NOT Friday dates to specify which week the data represents.
    I started by using Sat 12/30/17 as my end date. Per your instructions this should give data thru Fri 12/29/17. I used 30 as the number of weeks
    –This results in an extraction which looks good for all data except the last entry.
    –All dates in the extraction are Mon dates except the last entry — which was a Fri date 12/29/17
    ——The data was not the hi lo cl for the week. I don’t know what is was
    –However the second last entry in the data extraction did start with a Mon date 12/25/17 — and did have the correct weekly data for that full week.
    –So we need to throw out the last entry in the data set for this case.

    –Next I tried using Tue 12/26/17 as the end date [since 12/25 was a holiday].
    ——The result was NO data for the current week. The last entry was the week of Mon 12/18/17

    –Next I tried using Fri 12/29/17 as the end date.
    ——This gave data for the current week thru Thurs 12/28/17

    I believe the problem lies in the fact that Yahoo uses Monday dates to designate the week of the data extraction.
    Somehow your program does not seem to be able to handle that — in its current form.
    Most other data sources use Friday dates for this purpose. I actually prefer to see the data use Friday dates.
    But most yahoo data fans would probably like to keep it at Monday dates. Maybe our choice.

    Wei, on 11/22/17 noted similar problems with MONTHLY data extractions.

    The response tab in your program has a list of your symbols plus several other columns with entries related to each symbol.
    I have no idea what the purpose of that tab is.
    I live in the U. S. So the aussie symbols in your program are of little use to me. I added my own list of symbols.
    These symbols do not show up in the Response tab. But your program seems to work for them anyway.
    Am I going to have a problem with my extractions because of this?

    • Scott Lindsay on January 1, 2018 at 3:26 pm


      As per my previous comment you are free to update and use the code provided to enhance and improve the routine provided.
      To answer your questions…
      The Response Tab shows each call made to website and for what purpose. It returns the data in Response and the URL is listed in the URL column. You can use this data to test outside of the workbook routine the extraction of data into a browser
      For you last question you will have to send me an example workbook with your US data to explain the problem…..you can send it to us via our Support Request form located here on our website…

  11. Jer01 on December 31, 2017 at 9:17 am

    This was the first week I tried to update my weekly data using your program. Here are the problems I encountered.
    *** You said: “Set the End Date for the extract. The data will be retrieved prior to this date”
    Can’t get full weeks data on Friday night — have to wait till Saturday
    Can’t get an update with the current night’s data included any day. Data is always one day shy of being up to date.
    You can’t use a Saturday date on Friday night bc you get an error mssg stating you can’t do that.
    Recommend you change the program to retrieve the data including data from the specified end date. This would solve the above problems.

    *** The sort order feature does not work — data is always ascending

    *** I tried to add a tab so I could put the data in a form of my choice. This does not work. It gets wiped out by your Clear worksheets macro.

    • Scott Lindsay on January 1, 2018 at 3:21 pm


      You are free to update and modify the supplied code to suit your own needs and if needed fix any issues that are not working.
      If you do fix any issues or make updates you could send those back to me and I will look to include them in a future version…

  12. Jer01 on December 25, 2017 at 2:15 pm

    I read your instructions more carefully and noted this.

    “”Set the End Date for the extract. The data will be retrieved prior to this date””

    You said you would provide data PRIOR to the End Date we enter. So the program does what you said it would. Sorry for my comments in the previous post.

    With regards to adjusted data, I have the following comments. Yahoo already appears to provide adjusted data for the CLOSING price. It used to adjust the data BOTH for splits and dividends. I am not sure what it is doing now. You indicated you are going to adjust data for splits in your new update. Well, if you want to provide data that will provide GOOD charts, you will need to adjust for BOTH splits and dividends. Also, for GOOD [HI Lo Close] charts, you will need to adjust all of the data [ open, hi, lo, close] — not just the close. This can get kind of tricky, especially in weekly and monthly data, bc the split will often be effective in the middle of the week, month. If you are going to do it this way, I may be interested in being a beta tester.

    With regard to your current version, it might be nice to include a summary of all the data, with just the last date, last close in columns format for each symbol.. That would be useful to a lot of people who want a list of the latest close for their stock portfolio and certain indices. If you want you could add additional columns for: change, % change, hi, lo, close, pe. It would also make it easier for us to check out our current download with last price data from other sources to make sure the dates/prices line up ok.

    • Scott Lindsay on January 1, 2018 at 3:19 pm


      Thanks for those suggestions we will look to include them in a future version of the Historical Price Extract…

  13. Jer01 on December 25, 2017 at 10:27 am

    I tried out your program in the U S. It seems to work fine for weekly historical prices for the last 52 weeks. I tried it on 5 U S stocks and 2 U S indices. Only one problem. I had to enter a Saturday date [12/23/2017] to get it to include Friday data. When I tried the Friday date [12/22/2017]. it gave me the Thursday closing prices. Maybe it has something to do with the fact that we are in such different time zones???

    Thanks for making this available to us.

  14. Wei on November 22, 2017 at 12:42 am

    BUG REPORT: Monthly historical data
    To get monthly historical data, the Extract End Date must be set as the first day of a month. Otherwise, it will trigger a hidden (but critical) bug on the Yahoo Finance server and cause the historical data to be incorrect.
    For example, if you set extract end date as 6/30/2017 and number of periods as 30, the earliest monthly data retrieved from Yahoo Finance will become “null”, and the price of month T shown in the retrieved table is actually the price of month (T-1), which is incorrect. This bug will never occur in a normal browser because the values of period1 and period2 are set correctly in a browser. You may compare the results from VBA and from browser to see what I am saying.

    • Scott Lindsay on January 1, 2018 at 3:18 pm

      Thanks for the comment we will investigate and see if we can re-produce this issue

  15. Michael Parrett on November 11, 2017 at 7:21 pm

    Is there still a way to request intraday quotes showing the indicated change from the previous close?
    The “historical” output now includes current trade day OHLCV, but the day’s change data is helpful for spotting splits and ex-dividends.

  16. Robert on November 7, 2017 at 5:22 am

    Do you know of a way to get a real-time or delayed (not historical) quote from Yahoo (or anyone else) programmatically?

  17. Tom on November 6, 2017 at 8:34 pm

    Am trying v7A
    Found two issues.

    1. The last price/date for each Symbol in Col C and D is not the latest. It is always the earliest in the data set, as opposed to the latest.

    2. the Date Sort Order field does not seem to be working.

    Any update on fixes?



  18. Jack on October 21, 2017 at 8:14 pm

    v.7A is very impressive work with great potential. Kudos, guys.

    One apparent problem: not all requested dates are supplied. On Sat. Oct 21 I requested 5 days of data for 7 ETFs. Four days of data were provided for each ETF, i.e. Mon.-Thurs. but no data for Fri. Oct. 20. I then tried 10 days of data, and received 8 for all 7 ETFs. In both cases the data for Friday was missing.Lastly I requested one day of data, and received data labeled Oct. 19th. I am using the program in the U.S. Pacific time zone, and since it was requested on a Saturday, it should have been available. I tested this manually on Yahoo! and found Friday’s data available.

    Hope this helps, and again congrats. on a very fine product

    • Scott Lindsay on October 26, 2017 at 8:06 pm

      Jack, thanks for the feedback. Let me know what ETF’s you were searching for and I will investigate.

  19. Hannah on October 13, 2017 at 12:47 pm

    Thank you for all the work you have put in Scott, I have found many of your products very useful and always recommend them. Keep up the good work!

  20. Scott Lindsay on October 3, 2017 at 8:42 am

    Hi….an update to V7 now to include formatting of the Dates and Numbers on the worksheet and also the CSV files that are produced from the routine that extracts the data…in response to Jade’s request below
    Download V7A via the link above to access that update

  21. jadewinter on October 1, 2017 at 10:40 am

    Hope you moderate my comments on v7. Just deleted it and returned to v6. The entire output in v7 is text. I need dates and numbers! 🙂

    • Scott Lindsay on October 1, 2017 at 11:06 pm

      Thanks for the feedback I can see that now….the previous version much slower was writing the formats for each of the values into the range and setting the formats to Dates and Numbers. I can change the new version to do the same. Will update that shortly and release a new version.

    • Scott Lindsay on October 3, 2017 at 8:44 am

      Jade, have now updated the version to include formatting of the Date and Numbers in the returned data.

  22. jadewinter on October 1, 2017 at 10:24 am

    You’ve opened my eyes to excel automation, thank you. I see to change the output date column to dates I apply the formula =DATEVALUE(A1) was that your intention? Thanks

  23. jadewinter on October 1, 2017 at 10:17 am

    Right now I have to sit down to find a way to use the dates in the downloaded date column as dates. Looks like text to me. Can’t reformat it. Is it possible to have actual dates in the date column? Thanks.

    Also – a considerable and repeated time save would be the option to have just the date and the last price of a security listed, call it simple listing on something. Thanks

  24. Scott Lindsay on October 1, 2017 at 6:51 am

    I can advise now that v7 of the Historical Price Extract has been released and is available for download on this page…
    Please leave a comment and let us know if you think the improvements useful and what else we could do to improve it..

  25. Victor on September 25, 2017 at 8:03 pm


    Would you please advise where to download the updated workbook? Tried to download by the email address and got the v6 version, is v6 still the latest version? Thanks.

    • Scott Lindsay on September 29, 2017 at 1:02 am

      I can confirm that v6 is the latest version. I am currently working on a revised version that will include more validation that should be ready in the next few days. All those that have previously downloaded will receive notification of the update

  26. Scott Lindsay on August 28, 2017 at 1:50 am

    We are investigating some fixes to issues that have been identified. This one is related to the method that we use to populate the worksheet with results. In the updated version we will be changing this to be a memory based stored before it is transferred to the worksheet as a result. This may solve this problem…

  27. Tiffany on August 28, 2017 at 1:50 am

    Sometimes (not every time) I could not extract the data and had the following error message.
    Error Message:
    “Run-time error ‘1004’: PasteSpecial method of Range class failed.”
    And the line: “myRange.PasteSpecial” was highlighted
    I had to reboot my computer to get it work again, please advise what’s the root cause and how to solve it. Thank you.

  28. Scott Lindsay on August 28, 2017 at 1:40 am

    I am not sure what the problem is in this case….the dates used in our routine are set by the user for the Extract End Date (“B10”) and then that date is referenced back to January 1, 1970…..I don’t see how the location you are would impact this date calculation….I might be mis-understanding your explanation. The workbook we have provided is being used in a number of locations and no others have reported this issue. Maybe you can explain it further with an email to support@xlautomation.com.au and screen shots if it is still occuring

  29. Joe on August 28, 2017 at 1:40 am

    Hi, First great work, your code has been very helpful to me in resolving Yahoo’s change. When I first observed Yahoo’s change on 8/19/17 (my end date) and manually went to their web site to download data, Yahoo set the Unix period 2 time stamp to 1503126000 which translates to 8/19/17 Plus 7 hrs. It appears(?) as if Yahoo accessed my PCs regional settings (PST UTC-8 adjusted for daylight savings time) to offset the time stamp. Not sure why they did this but if the end date time stamp is not offset by the regional setting the data I get back is one day short of the end date in your spreadsheet. As a temporary fix to your code I have offset the time stamp by 8 hrs (PST):Private Function strGetUnixDate(dteSetDate As Date) As String’This function will set the Date required in the URL to the Unix date format ‘strGetUnixDate = (dteSetDate – DateValue(“January 1, 1970”)) * 86400’Modification of Unix date format to offset by a fixed value of 8 hrs (PST, my location). Note added not subtracted strGetUnixDate = (dteSetDate – DateValue(“January 1, 1970”)) * 86400 + 8 * 60 * 60’Modification of Unix date format to offset by a value based on the user’s regional setting ‘TBDEnd FunctionAs you can see, it may be more universal to offset based on the users regional settings but this is beyond my programming capability. Any thoughts or suggestions?

  30. Scott Lindsay on August 18, 2017 at 4:30 pm

    New website launch on the comments page for Yahoo Price Extract

  31. Victor on August 10, 2017 at 4:24 am

    Hi, I have an issue with the VBA code below. This code runs well on my notebook at my home, however when trying to run it at another place (same notebook) I get the following error message right when the code tries to execute the .Send line (see below): Error Message:Run-time error ‘-2147012867 (80072efd)’A connection with the server could not be established
    Not sure if this is an routing or proxy issue, and please advise if this issue could be solved by using other protocol like XMLHTTP or something else. Thanks.

    • Dennert Lim on August 16, 2017 at 12:07 am

      Hi You can set the proxy:Const HTTPREQUEST_PROXYSETTING_PROXY = 2 Set objRequest = New WinHttp.WinHttpRequest With objRequest .SetProxy HTTPREQUEST_PROXYSETTING_PROXY, “proxy:port”, “intranet ip” .Open “GET”, strUrl, False .setRequestHeader “Cookie”, strCookie .send .waitForResponse (10) strResult = .responseText End With

      • Victor on August 17, 2017 at 12:09 am

        Hi Dennert,
        Thanks for your feedback, but I got new Error Message: Run-time error ‘-2147024809 (80070057)’ The parameter is incorrect.
        And the line “.Open “GET”, strUrl, False” was highlighted.
        Please kindly advise how to solve it.
        Thanks and regards.

  32. Dravo on July 1, 2017 at 12:10 am

    The title of this site is ”Yahoo Historical Price Extract”. My experience with the algorithms given here is very positive. Once you have a valid Cookie-Crumb pair, you can use it over multiple sessions, on different pc’s, by different users, you name it. The Crumb is stored in the Registry, the Cookie somewhere else. I have not been able to find reports on the changes made in the Historical Price Extracts on the Yahoo site, let alone in finding reports on the new Cookie and Crumb policy. After May 20 until roughly June 2, I extracted data where the Open-High-Low- extracts were exchange data adjusted for splits together with the reported adjusted share prices, and the closes and volumes were unadjusted exchange data. Last Friday, a new change was introduced where the Closes are now adjusted for dividends and splits, the Volumes appear still to be unadjusted, and the Open-High-Low-AdjCloses all appear to be adjusted for splits. The rationale behind those changes is not clear to me. It surprises me that only a handful of people appear to be engaged on this subject.

  33. croyt on June 28, 2017 at 12:11 am

    Scott — Very nice work, thanks. Running yesterday’s downloaded version, however, I notice that Yahoo itself has a few problems. Comparing their webpage and downloaded data:
    o Downloaded spreadsheet swaps raw and adjusted close values
    o Monthly data always begins on first of the month, while the actual trading calendar does not, cf Oct 16
    So, obviously, your results reflect the same bugs.

    • Dravo on June 29, 2017 at 12:13 am

      croyt, do you really think it was a swap? The only change they made was in the Close column. The other ones (Date-Open-High-Low-…-AdjClose-Volume plus the dividends and splits) appear to have stayed the same. Hence, we know now the AdjClose and AdjDiv but not, what you call the “raw” closes and what other people may call the historical closes or unadjusted closes. Hence, it gets trickier to back-engineer the unadjusted data.

      • croyt on June 30, 2017 at 12:14 am

        Dravo — Indeed, historical and adjusted closes are swapped, at least in the few spot-check cases I compared with StockCharts unadjusted data. As you subsequently suggest, however, this format may yet remain a moving target. Maybe Yahoo’s teenage programmers just need some adult supervison.

  34. Clive Simpson on June 27, 2017 at 12:30 am

    Oh I see now, its AABA

  35. Clive Simpson on June 27, 2017 at 12:17 am

    Do you know why the only ticker I am unable to download from all the ones I regularly check is YHOO itself. Is there any way around this?
    Thanks and regards

  36. Scott Lindsay on June 26, 2017 at 12:19 am

    See below that others have solved this problem…we will be including a fix to it in a new version before the end of July…
    to solve date format problem, correct this function:
    Private Function strGetUnixDate(dteSetDate As Date) As String
    ‘This function will set the Date required in the URL to the Unix date format
    ‘strGetUnixDate = (dteSetDate – DateValue(“January 1, 1970”)) * 86400
    strGetUnixDate = (dteSetDate – DateSerial(1970, 1, 1)) * 86400

    End Function

  37. Lars van Bilsen on June 25, 2017 at 12:20 am

    the workbook looks great. But after hit The extract button I got the follow error
    Types do not match in the following function
    Private Function strGetUnixDate(dteSetDate As Date) As String
    ‘This function will set the Date required in the URL to the Unix date format
    strGetUnixDate = (dteSetDate – DateValue(“January 1, 1970”)) * 86400
    End Function
    How can this be solved

  38. Rick on June 24, 2017 at 12:22 am

    Thanks for the workbook. Great features and hopefully the method to extract data doesn’t get changed again! Is there a way to get BETA too?

  39. Robert on June 23, 2017 at 12:23 am

    Thank you for the 6-25 fix and detailed explanation of change.

  40. Jerry Sullivan on June 23, 2017 at 12:22 am

    Thanks to the XLAutomation Team for this nice workbook!I wanted to make you aware of a bug in the handling of the retries…The function strGetYahooFinanceData takes the parameter strUrl, then appends the crumb string.This works as intended for the call from strGetYahooFinanceDataRetry() in the first iteration of the loop. On subsequent calls, the strUrl parameter includes the previously appended crumb.Adding a Debug.Print statement in the strGetYahooFinanceData function will show the effect… Call GetCrumbCookie(strCrumb, strCookie, blnForceRefresh) strUrl = strUrl + “&crumb=” + strCrumb ‘–add this line, then test strGetYahooFinanceDataRetry when query fails. Debug.Print strUrlExample Output:(Try 1)https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1465948800&period2=1497484800&interval=1d&events=history&crumb=JI9eAl.WqRkx(Try 2)https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1465948800&period2=1497484800&interval=1d&events=history&crumb=JI9eAl.WqRkx&crumb=Ps\u002Fts1w4pkv(Try 3)https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1465948800&period2=1497484800&interval=1d&events=history&crumb=JI9eAl.WqRkx&crumb=Ps\u002Fts1w4pkv&crumb=BwgDwk2cxVII fixed this on my copy of the workbook by using the base URL (without crumb) as the strUrl argument passed to strGetYahooFinanceData. Another, perhaps more robust, approach would be to have the strGetYahooFinanceData strip the crumb off strUrl if it exists.

  41. Paul on June 22, 2017 at 12:24 am

    Very nice stuff, thank you very much for the work.

  42. Scott Lindsay on June 21, 2017 at 12:25 am

    I have now updated this page with a new version of the Historical Price Extract that has a minor change to the code that sets the cookie and crumb. The change involved the type of call made in the WinHTTPRequest to be “synchronous”. See the update details above and also download again if you want the working sample file.

    • devank on June 20, 2017 at 12:26 am

      Thanks. It works well now.

  43. Paul on June 19, 2017 at 12:29 am

    Sorry that was not correct, download url still same format including crumb parameter. And historical price download is working also from their us site…

  44. Paul on June 18, 2017 at 12:30 am

    Looks like they changed url removing the crumb. I could download 3y msft from Yahoo site ,the download link showed: https://de.finance.yahoo.com/quote/MSFT/history?period1=1372024800&period2=1498255200&interval=1d&filter=history&frequency=1d .- I tried “au.finance.yahoo.com” download worked as well.

  45. David Rossitter on June 17, 2017 at 12:33 am

    Crikey yahoo do know how to pee off the marketplace don’t they. Appreciate its free but either charge for this or remove it – all this cloak and dagger stuff can’t be good for their reputation . In the mean time thanks to this site I have managed to load the history I need short term

  46. Scott Lindsay on June 16, 2017 at 12:34 am

    It looks like something has changed again on the Yahoo site for this workbook extract routine. We will need to investigate this for a resolution. Even using the Yahoo site directly and attempting to download daily prices is now failing with an “Unauthorized” / “Invalid Cookie” message. I suspect that they are in the process of updating their approach and we will need to adjust this extract once their new method is in place…..keep checking back to this page and we will post a resolution once one is developed.

  47. Peter Wi on June 15, 2017 at 12:35 am

    PeterWi,Hello everyone it looks like Yahoo has done something because it looks like the sheet trips up over the crumb and cookie, it was working great till today and I have not been overdoing it with data requests.Anyone else have the same problem with the sheet?

  48. devank on June 10, 2017 at 12:37 am

    I tried to run the macro without making any changes. But getting an error message popping up saying “The attempt to retrieve the historical data for the Security Code – BHP.AX has failed …”

  49. duduchao on June 8, 2017 at 12:38 am

    Dear Scott,
    Would you please let us know when can we expect the next updated version? Many thanks!

    • Scott Lindsay on June 8, 2017 at 12:43 am

      I will be releasing a new version of this spreadsheet in the next few weeks. It will include some or all of the suggestions that others have provided and those that have been voted above for inclusion..

  50. Nate S on June 1, 2017 at 12:40 am

    Hey all. is there a way to copy paste the dates. I have an excel sheet that calculates return 5 days after and before an earnings but the problem is these dates are in CSV so when I copy paste into excel sheet with macro the date is not correct? Any Solution to copy paste CSV to Excel?

  51. Peter Wi on May 31, 2017 at 12:41 am

    Very nice work but …..Please make it work on earlier versions of excel !

    • Scott Lindsay on May 31, 2017 at 12:44 am

      Peter, the current version works on all versions of Excel from 2007 and above. We will not be providing any support for versions of Excel prior to 2007.

  52. Dan Carroll on May 29, 2017 at 2:36 am

    Awesome lifesaver! While I might have been able to work this out, I am not a programmer and would have lost a lot of time. A couple of minor issues – I’ve changed these on my copy but if you are issuing updates you may want to put these in. Allow the user to specify output directories and file name format (I have a lot of spreadsheets that link to these file). And the sorting command gets lost (my spreadsheet links assume date descending) .

    • Scott Lindsay on May 30, 2017 at 2:38 am

      Dan, great to hear that you have found this spreadsheet helpful. Can you give me more details on what you would expect for the File name format option you mention above. What would be the options or variables to that file name ? Would it include the ticker symbol ?

  53. Scott Lindsay on May 27, 2017 at 2:40 am

    Thanks for all the feedback and contributions on this workbook. Great to see this has been of some help to others. I will work through some of the suggestions and the fix for the GetUnixDate function and release a new version of the workbook in the next few days.

    • Dravo on May 28, 2017 at 2:40 am

      Scott, While you are at it, speed is important for a number of applications. Would you be willing to consider the following changes: (1) Separate the use of cells or formatting those from the algorithm to validate the calls? (2) When you work with a list of validated security codes, #30 on the list always gives an extract error and the rest will go fine. I am sure there is a solution to this issue. (3) When your stock list has a few security codes that recently became inactive or are faulty, the validation of the other ones gives sometimes erratically unjustified Extract Errors. You don’t solve that by increasing the number of retries to above one. Programmers often use the Sleep function to wait a few milliseconds at the proper place. It appears to be a small glitch in the WinHTTP-driver and in VBA we need to work around it. I am looking forward to your next version.

  54. Bernd on May 27, 2017 at 2:38 am

    Works fine with “DateSerial”!Well done, Antonio! Many thanks!

  55. Antonio on May 26, 2017 at 2:44 am

    Hi all,
    to solve date format problem, correct this function:
    Private Function strGetUnixDate(dteSetDate As Date) As String
    ‘This function will set the Date required in the URL to the Unix date format
    ‘strGetUnixDate = (dteSetDate – DateValue(“January 1, 1970”)) * 86400
    strGetUnixDate = (dteSetDate – DateSerial(1970, 1, 1)) * 86400

    End Function

  56. duduchao on May 25, 2017 at 2:45 am

    Dear Scott,
    Will there be an updated version released soon? Many thanks for your help here.

  57. Bernd on May 24, 2017 at 2:46 am

    Hi All,Here comes another solution regarding the Unix date string in the code: For German settings (i.e. region) the tool works one modifies the date from “January 1, 1970” to “1. Januar 1970”. No need then to change regional settings to US.

  58. Alexander on May 23, 2017 at 3:16 am

    many thanks! very nice solution!

  59. Jon on May 23, 2017 at 2:10 am

    Excellent solution!!! Found it while looking through Stackoverflow forum.
    I would like to ask you if is not too much trouble if you can add to the workbook the option to get the Splits in the same way Dividends or Historic Price. I tried myself but did not work as expected. I modified the code to include the Splits in the dropdown menu, no issues there. when trying to get the split for Apple the latest was 1/7 but the result returned is Jul-01 (so excel reads it as a date instead as a fraction).Also tried to check in the code how to solve the problem of the unsorted dates when requesting the historic dividends, but i miserably failed. If you can help with this that will be awesome too.Cheers!

    • Dravo on May 23, 2017 at 2:15 am

      Jon,The code that I sent Scott solved that problem. In addition to that, you align the dates of the splits and dividends such as to get get Date-O-H-L-C-A-V-Div-Split. The trick for the splits is that you have to program: arrSpl=Split(“Value of Split reported”,”/”). Hence, use the split function with the delimiter “/”. You get an array of two elements. Calculate CSng(arrSplit(0))/CSng(arrSplit(1)) and you get the split factor. Maybe because I am in a different timezone, but I never get the latest historical data of the day that I input in the sheet. For instance, when I input at this moment 7/9/2017, I get the data up to 7/8/2017. Are you seeing that too?

      • Jon on May 24, 2017 at 2:22 am

        Thanks for your Reply DravoI look into your past comments about using a Winhttprequest with the URL https://finance.yahoo.com/quote/” & SecurityCode & “/history?period1=” & StartDate & “&period2=” & EndDate & “&interval=1d&filter=history&frequency=1d&Crumb=” & CrumbI tried using it in Scott’s code but got an error. My VBA skills are not too advance hehe.Dont know if is too much to ask but if you can share the workbook you share with Scott that will be really awesome. In the mean time I will give it a go and see where it fit the advise on using arrSplit for the Split factor.I am 7 hours ahead of the US East Coast. I haven check yet if I have the same problem as you when getting the data for the latest trading day.Thanks in advance for your help.

        • Scott Lindsay on May 24, 2017 at 2:34 am

          Jon and Dravo, I am reviewing the changes that Dravo has made with a view to including them into another version of this workbook to share later this week.

          • Dravo on May 24, 2017 at 2:41 am

            Jon, we are about in the same time zone. I cannot post source codes here. That can only be done by the owner of this website, and that is Scott. I uploaded my code to him. This is not really code for redistribution as I am not a professional programmer. When he sees the need for posting, he hopefully will clean it up with his professional skills and include it in his own application. As to your question on the URL and the error you receive when using it in a WinHTTPrequest is that you indeed should get an error. Like in Scott’s own source code, you need a reference site (URL) that can be called with a WinHTTPrequest for scraping the proper Cookie-Crumb pair. Perhaps Scott can help us to find such a site.

            • Jon on May 24, 2017 at 2:48 am

              Big Thank you guys, very much appreciated!! Scott thanks in advance for the work you are doing, you have no idea how big the headache I had when the yahoo API stopped working.
              The same as Dravo, I am not a professional programmer, I am a VBA self learner 🙂 Thanks for sharing your neat solution!Looking forward to that updated version

  60. Bernd on May 22, 2017 at 3:17 am

    Hi Scott,problem solved!! I have changed the regionals settings to US / date format as well and the tool runs w/o problems. Time to play …

    • Scott Lindsay on May 22, 2017 at 3:22 am

      Bernd, Ok that is good to hear. It looks like that date format is an important part for the later call.

  61. Bernd on May 21, 2017 at 3:20 am

    Hi Scott!I just noticed that Robby is facing the same problem. In my references “WinHttp” is available. Still the runtime error “13”! pops up. I am using Excel version 365 (German version, German configuration, which shouldn’t cause the error since VBA for excel is all English). Thank you!

  62. Bernd on May 20, 2017 at 3:21 am

    Hi,I get an error message in connection with the conversion of the input date to the unix date (run time error ’34) “incompatible types … Any suggestions?Kind regards,

  63. jj on May 19, 2017 at 3:23 am

    hello, wud it b possible to make a macro to convert ascii data into metastock? thnx!

  64. Dravo on May 18, 2017 at 3:30 am

    This is great. I have changed the software a bit so that I get the Dates-O-H-L-C-adjV-nonAdjC-adjDiv-Splits. These are nine columns from which a trader can calculate his closes adjusted for dividends and splits. The downloaded closes only seem to be adjusted for splits. Note that liquidity (daily dollar volumes) is calculated from C*adjV as the volumes are adjusted for splits. You can scrape these nine columns in one Winhttprequest using the URL https://finance.yahoo.com/quote/” & SecurityCode & “/history?period1=” & StartDate & “&period2=” & EndDate & “&interval=1d&filter=history&frequency=1d&Crumb=” & Crumb. This single scrape is as simple as the Crumb scrape.

    • Scott Lindsay on May 17, 2017 at 3:36 am

      Dravo, would like to see that reworked version of the workbook if you would like to share it with others. Please upload to our file upload contact form @ https://xlautomation.com.au/free-spreadsheets/suggestion-file-upload

      • Dravo on May 17, 2017 at 3:37 am

        Scott, I discovered that you are about a dozen time zones away. What I learned from you is that Crumbs and Cookies are paired and unique for each type of download. Hence, the Crumbs and Cookies you find in Browser downloads (for instance, IE-downloads) are different from the ones you need for Browser-independent downloads (for instance, WinHttp-downloads). Yahoo apparently designed their downloads such that Browser-dependent downloads automatically are authorized to download, whereas Browser-independent downloads need to be authorized by specific Crumbs and Cookies. It is remarkable that you found a site where you can scrape the relevant pair using a browser-independent download. Would you be willing to explain how you find such sites? I am not a professional programmer, but I work closely with them. I uploaded what you requested.

        • Scott Lindsay on May 17, 2017 at 3:40 am

          Dravo, we found a URL that allowed us to store the Cookie and Crumb that can then be re-used for the download of the historical data. The URL was a standard security lookup from the Yahoo Finance page. https://finance.yahoo.com/lookup?s=%7B0%7D.
          Did not see your upload to our “suggestion-file-upload” page…

          • Dravo on May 17, 2017 at 3:41 am

            Scott, I uploaded the file already twice. I did get the message back that the upload was successful. If this doesn’t work for you, you can use my e-mail and sent me a note. I ‘ll respond with the attachment that I already tried to upload twice. I will not further use your e-mail address.

            • Scott Lindsay on May 17, 2017 at 3:42 am

              Yes I now have the file, will review the changes you have made. Thanks

  65. duduchao on May 16, 2017 at 3:21 am

    Dear Scott,
    What I suggest is
    1. to have a new column called “Ticker” before the column “date”. Currently, you put the Ticker information (For example AAPL) only at the column name of “date”. The date would be helpful to order descend by “date”.
    2. eliminate those “null” data.
    Ticker date Open High Low Close Adj Close Volume
    AAPL 2016/11/21 109.1939 111.0482 109.0848 111.73 110.7904 29264600
    AAPL 2016/11/22 111.0085 111.4746 110.4632 111.8 110.8598 25965500
    AAPL 2016/11/23 110.4235 110.5722 109.4022 111.23 110.2946 27426400
    AAPL 2016/11/25 110.1954 110.9292 110.0169 111.79 110.8499 11475900
    2330.TW Open High Low Close Adj Close Volume
    2017/2/16 190 190.5 188 189 189 27018000
    2017/2/17 190 190.5 189 189.5 189.5 20389000
    2017/2/18 null null null null null null <== dont' show this kind of null record 2017/2/20 190 190.5 189.5 190 190 13254000 2017/2/21 190 190 188.5 190 190 31159000 2017/2/22 190.5 191 188.5 188.5 188.5 25677000

  66. Scott Lindsay on May 15, 2017 at 3:53 am

    Robby, what is the error when you get to the “send” statement ? Or is it just a timeout. If a timeout then you may have firewall / network issues in making the call. You will also need to check that the references in your workbook for WinHttp are available. Lastly does this work for you in other versions of Excel

    • Robbby on May 15, 2017 at 3:55 am

      Hi Scott
      You’re right is a server and firwall problem

  67. Murray on May 14, 2017 at 3:56 am

    Thank you guys. It’s a cracker! Excel 2010
    I would like to be able to enter the start date eg Jan 4 2016 to give me sufficient data to model
    But I like it!
    Big +1 and kudos

    • Scott Lindsay on May 14, 2017 at 3:59 am

      Murray, you can do this by specifying the end date of Dec 31 2016 and then setting the number of periods to 365. It will return one years data

  68. George on May 13, 2017 at 4:01 am

    Cheers dude, thanks for the help! Found you over @ Stackoverflow

  69. duduchao on May 13, 2017 at 3:02 am

    Thank you for helping us out of the poor situation.

  70. jimmymc99 on May 13, 2017 at 2:03 am

    Great product, very helpful. One change would be helpful. The Dividend data is copied to the worksheets in two columns, Date and Value. Unfortunately the rows are not in date order. Have to use a Data Sort command to make the data usable in each of the worksheets. Thanks for your help.

  71. Ian on May 12, 2017 at 4:05 am

    Hi possible to have an option to export to csv & arrange the column order to Date, Volume, Open, High, Low, Close (adj close not needed) ?and with option to remove the data rows with “null” values. Thank you

    • duduchao on May 12, 2017 at 4:06 am

      Dear Scott,
      What I suggest is
      1. to have a new column called “Ticker” before the column “date”. Currently, you put the Ticker information (For example AAPL) only at the column name of “date”. The date would be helpful to order descend by “date”.
      2. eliminate those “null” data.
      Ticker date Open High Low Close Adj Close Volume AAPL 2016/11/21 109.1939 111.0482 109.0848 111.73 110.7904 29264600 AAPL 2016/11/22 111.0085 111.4746 110.4632 111.8 110.8598 25965500 AAPL 2016/11/23 110.4235 110.5722 109.4022 111.23 110.2946 27426400 AAPL 2016/11/25 110.1954 110.9292 110.0169 111.79 110.8499 11475900
      2330.TW Open High Low Close Adj Close Volume 2017/2/16 190 190.5 188 189 189 27018000 2017/2/17 190 190.5 189 189.5 189.5 20389000 2017/2/18 null null null null null null 2017/2/20 190 190.5 189.5 190 190 13254000 2017/2/21 190 190 188.5 190 190 31159000 2017/2/22 190.5 191 188.5 188.5 188.5 25677000

      • Scott Lindsay on May 12, 2017 at 4:07 am

        So the request is to add a column of data for the ticker that is repeated for each row of price data rather than just having the ticker listed in the top left column of each output ? The removal of “null” values could be included in another version

        • duduchao on May 12, 2017 at 4:08 am

          It’s a very good suggestion to remove “null” rows.
          BTW, How about adding one more column with “ticker” so we can load into database for further use. Thanks!

  72. Robbby on May 11, 2017 at 4:10 am

    I have excel 2010
    It gives me the error message in the string indicated with the arrow
    The whole string becomes yellow
    How can I fix it?
    “Private Function strGetUnixDate(dteSetDate As Date) As String
    ‘This function will set the Date required in the URL to the Unix date format
    —> —> strGetUnixDate = (dteSetDate – DateValue(“January 1, 1970”)) * 86400
    End Function
    How can I fix it?
    Thank You

    • Scott Lindsay on May 11, 2017 at 4:11 am

      Robby, this problem was reported by others you could try changing the date format for that routine and see if that makes a difference. For example try DateValue(“01/01/1970”) or any other variation of the same date to get past that line…let us know if that does not resolve the issue.

  73. Scott Lindsay on May 10, 2017 at 4:13 am

    A new update to this spreadsheet has been posted on this page. New features following suggestions and feedback
    – extract historical prices or dividends
    – single extract end date for all securities
    – output to a separate worksheet or CSV file
    Let us know if you have any other suggestions for improvement

    • David on May 10, 2017 at 4:14 am

      Hi, can you reinstate all the history on the one spreadsheet for all codes as per the last version in the same format (sent across last week and implemented in v2) – as I modded my code to take this and it’s not there now. Many thanks David

      • Scott Lindsay on May 10, 2017 at 4:16 am

        David, the new update did not change the method used to access the Yahoo data. If you really need that previous method combined with the current version. You could copy across the code from v2 to this version and create the “Results” worksheet, then include another Output option.

  74. danoXp on May 9, 2017 at 4:17 am

    Running nicely on Excel 2000 on Win7 64x home.thanx dan

  75. David Rossitter on May 9, 2017 at 2:18 am

    Hi, a bit of feedback having used the routine a little more. If you try and run it for a few more tickers it will often throw up a message saying the stock cant be found (even though the data is there when I look at the website and when this happens I see the crumb and cookie value changing so looks like it is failing on obtaining these. If I accept the message it carries on loading again presumably with the next ticker. It seems to be happy doing up to 20-30 between errors but will sometimes error on the next one too. I dont think it is the specific symbol causing it to error so having the routine skip on error or maybe even retrying it on the failed one might be worth looking at?

    • Scott Lindsay on May 9, 2017 at 3:19 am

      David, thanks for the feedback I will investigate and see what can be improved. Given some of the suggestions so far I might create an update and share it back to everyone has downloaded so far.

  76. Paul on May 8, 2017 at 4:27 am

    Hi Scott, Thanks a lot for this code. Would you mind adding an option to save as csv file? For example i need to have a file containing 3 years (1100 days) price data of MSFT or what ever symbol. When I click the button, the extracted data is saved as MSFT.csv which I want process further.

    • Scott Lindsay on May 8, 2017 at 4:29 am

      Paul, thanks for the suggestion will include to the list for a future update

  77. Gary Liang on May 7, 2017 at 4:29 am

    Hi Scott, Codes are great! Thanks a lot.

  78. David Rossitter on May 6, 2017 at 4:30 am

    Thanks Scott for the enhancements – works great. I have tried 500 days and that seems to work so that’s more than enough for me. Am going to try and replace my old code with this over the weekend ! Just one thing if its easy if you are still enhancing it – if a ticker errors as it cant be found or is just wrong can you have the program skip it rather than error ie if 1 of the 10 security codes is junk it would return 9 sets of data? Thanks once again for the help and rapid response on this

    • Scott Lindsay on May 6, 2017 at 4:44 am

      David, thanks for the feedback good to hear it is working for you. I will include that additional feature in a future version sometime next week.

  79. Steve on May 5, 2017 at 4:34 am

    I created something about 10 years ago to retrieve the historical data by month(as far back as Yahoo has it) for 500 stocks. I’ve downloaded your spreadsheet, but I’m having trouble changing it to report on the month interval instead of day. I changed “1d” to “1mo” in the URL string and messed with a few of the variables, but I’m not getting it. Would you mind helping? Thanks in advance

    • Scott Lindsay on May 5, 2017 at 4:35 am

      Steve, changing the URL string to include “1mo” will work, but you will need to modify the date range that is supplied in the URL. Look into the routine “GetSecurityHistoricalPrice”, change the dteStartDate = DateAdd line to “m” instead of “d”. Then change the number 10 to however many months prior you want to extract and with those changes it should then extract the monthly prices….I have just posted an update to the sample with some new features. I will include a feature to select (daily, weekly, monthly) to the next update we make.

      • Steve on May 5, 2017 at 4:39 am

        Never mind. I got it figured out. Thanks

        • Steve on May 5, 2017 at 4:41 am

          Sorry about the reply after yours. I didn’t update the page to see that you had responded. Thanks for the help.

  80. Elli on May 4, 2017 at 4:38 am

    To be more precise. When I press the button I receive an error message:
    Run Time error 13 type mismach.
    The debug screen points to the following line:
    strGetUnixDate = (dteSetDate – DateValue(“January 1, 1970”)) * 86400
    Any idea what is the reason for that?

    • Elli on May 4, 2017 at 4:41 am

      I find the solution myself.
      I had to change DateValue(“January 1, 1970”) to DateValue(“1/1/1970”) and then it worked Fine.

  81. Elli on May 3, 2017 at 4:40 am

    I get an error code 13 and the macro stops working

  82. Anonymous on May 3, 2017 at 2:41 am

    Fantastic, thank you so much. There are a couple of issues I’m working on when trying to adapt it for my purposes. One of the things I’ve noticed is that strURL should be made ByVal in strGetYahooFinanceDataPrivate Function strGetYahooFinanceData(ByVal strUrl As String, Optional blnForceRefresh As Boolean) As StringOtherwise, when the request fails, strUrl keeps having crumbs attached to it… strUrl&crumb=xxxx1&crumb=xxxx2&crumb=… etc.Cheers!

    • Scott Lindsay on May 3, 2017 at 3:42 am

      Thanks for that feedback. If you need specific help on getting this working for your purposes, you can share a workbook via our https://xlautomation.com.au/contact-us form and I will look to assist if possible.

  83. Anonymous on May 2, 2017 at 4:43 am

    Hi -this looks very promising having too suffered from the sudden loss of yahoos iChart function. I’ll download and try it in the morning. Can it/Does it also do the daily volume like the old method? Thanks for doing this and providing a solution! David

    • Anonymous on May 2, 2017 at 4:44 am

      Just downloaded it and can see the volume. Can the retained data be kept in different columns to the data above it as if I ask for more than about 10days it overwrites the data below it and hangs the routine. Nice though to see Yahoo historic data coming through again! Cheers, David

      • Scott Lindsay on May 2, 2017 at 4:46 am

        David, the sample workbook is just a starting point really. If you have a specific need to keep the volume data the output could go to another worksheet that does not get cleared. You could also set the range of dates to be returned to be greater than the 7 days I have set the extract to. Changes to the VBA code should achieve that. Give me a sample of what you are trying to achieve via our Contact Us form and I will look into it for you…

        • David Rossitter on May 2, 2017 at 4:50 am

          Thanks Scott, I’ve sent across a simple workbook as requested. Many thanks David

          • Scott Lindsay on May 2, 2017 at 4:56 am

            David, Have just updated the sample workbook to include those features you were looking for. They were a great enhancement for others to share.

  84. Scott Lindsay on May 1, 2017 at 8:56 pm

    An update to the workbook has been posted to include a complete list of extracted data for each security code for each available trade date in the range. Let us know if there are any other suggestions for improvement to this workbook.

    • Peter Wi on May 1, 2017 at 8:58 pm

      Hello Scott,As of this morning the sheet does not work again, do you find the same?

  85. MR on April 30, 2017 at 9:54 pm

    Hi – is there a way to copy paste the historical data for each ticker to cells which are adjacent? right now it just copies and pastes over and over the same space, meaning you can only get the historical data for the last ticker. Thanks again!

    • Scott Lindsay on April 30, 2017 at 9:59 pm

      MR…sure I could modify the workbook to display all values for each ticker in a list that does not override. Thanks for the suggestion, I will post another version that presents the list of prices in a continuing list.

  86. MR on April 29, 2017 at 9:50 pm

    Thank you v much for this!

Leave a Comment