Your 'Go To' for Custom Excel Solutions
excel-logo-white-175

Yahoo Historical Price Extract

This free spreadsheet is a solution we developed 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.

yahoo-finance-api-broken2

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
https://query1.finance.yahoo.com/v7/finance/download/BHP.AX?period1=1493123204&period2=1495715204&interval=1d&events=history&crumb=WxrLhK9KSAq

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

https://query1.finance.yahoo.com/v7/finance/download/BHP.AX?
The new starting query URL string and includes the security code. Only one security at a time for this request.

 

period1=1493123204&period2=1495715204
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.

 

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

&crumb=WxrLhK9KSAq
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"
        .send
        .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 v7 - Released 30th September, 2017

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 accurately. 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
  • The extracted data can be sorted 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 need to be verified by others.

Download Your Free Historical Price Extract Spreadsheet

Enter your details below to receive your copy of Historical Price Extract 

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

Another Free Spreadsheet by XLAutomation.

If you found this spreadsheet helpful please make a donation.

promise

 

Our Promise: "We will only use your contact details to share the latest news, free spreadsheets and product information from the XLAutomation team. We will never share your contact details with anyone else.

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.

Leave a Comment





115 Comments

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

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

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

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

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

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

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

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

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

    Hi,

    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

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

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

    Tiffany,
    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…

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

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

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

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

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

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

    New website launch on the comments page for Yahoo Price Extract

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

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

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

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

    Oh I see now, its AABA

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

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

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

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

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

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

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

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

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

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

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

  26. 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…

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

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

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

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

  31. 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 …”

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

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

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

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

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

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

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

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

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

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

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

    many thanks! very nice solution!

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



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

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

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

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

  47. 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://www.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



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

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

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

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

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

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

    Thank you for helping us out of the poor situation.

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

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

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

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

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

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

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

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

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

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

    Hi Scott, Codes are great! Thanks a lot.

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

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

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

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

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

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

    Hi
    I get an error code 13 and the macro stops working
    Elli

  65. 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://www.xlautomation.com.au/contact-us form and I will look to assist if possible.

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

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

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

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

    Thank you v much for this!