Problem with Yahoo Finance download

  1. 6 months ago

    For many years I have directly accessed historical data for stock market tickers through Yahoo Finance. Recently they went to a Secure url, which I was able to handle. The critical statements are as follows:

    //construct the url
    addr="https://chart.finance.yahoo.com/table.csv?s="+symb+"&a="+StM+"&b="+StD+"&c="+StY+"&d="+EdM+"&e="+EdD+"&f="+EdY+"&g=d&ignore=.csv"

    //Get file
    https.Secure=True
    https.ConnectionType=3
    https.Get addr

    Apparently earlier in May they have made further changes, so that the above does not work; I believe they now require some sort of connection to my computer as with a Cookie.

    Does anyone know how to work around their most recent changes?

    I would appreciate your help,
    brigs

  2. Edited 6 months ago

    I'd look at https://developer.yahoo.com/yql/

    Just going to the finance page (from here it's at ca.finance.yahoo.com ) I can click on a symbol and download using a URL like
    https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1492967582&period2=1495559582&interval=1d&events=history&crumb=jIa1MqK//LA

    Wonder if with all the changes they've made they have just removed or replaced the URL you're trying ?

  3. Cho S

    May 23 1.282150° N, 103.863861° E

    Hi Jonathan,

    Yahoo Finance has shut down their Finance API. Read Nixon's post (official Yahoo staff) at 05-17-2017 02:59 PM:
    https://forums.yahoo.net/t5/Yahoo-Finance-help/Is-Yahoo-Finance-API-broken/td-p/250503/page/3

    The historical download now requires a cookie their finance server creates for your browser. The "&crumb=" part of the URL is the cookie. I have tested with different browser on the same computer and the "&crumb=" part is different. Also on different computers. So there is no longer a standard fixed URL.

    Bascially they now want a visit to their website (for the eyballs), instead of software to download.

  4. Cho S

    May 23 1.282150° N, 103.863861° E

    @Norman P I'd look at https://developer.yahoo.com/yql/

    Just going to the finance page (from here it's at ca.finance.yahoo.com ) I can click on a symbol and download using a URL like
    https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1492967582&period2=1495559582&interval=1d&events=history&crumb=jIa1MqK//LA

    Wonder if with all the changes they've made they have just removed or replaced the URL you're trying ?

    Clicking on the link on your post, which will be like downloading using a software (instead of actually going to the webpage where your get that link and where you click the link) I get:

    {
        "finance": {
            "error": {
                "code": "Unauthorized",
                "description": "Invalid cookie"
            }
        }
    }
  5. Greg O

    May 23 Xojo Inc Somewhere near Raleigh, NC

    You likely need to update your connection type property. Hardly anyone uses SSLv3 any more. Try TLSv1, 1.1 or 1.2.

  6. Cho S

    May 26 1.282150° N, 103.863861° E
    Edited 6 months ago

    Hi Jonathan,

    I too have software than download data from Yahoo and was like you affected by the new direction Yahoo has taken with shutting down their Finance API. Even their community YQL do not work any more. I have been trying and searching for a work around for past 2 weeks, trying how to learn how to get an in-memory cookie (still not knowing how) to test whether it would work.

    I use Chrome as the default browser and something I found out today. My affected software are still VB6 and using Wininet.dll API to download and this morning suddenly had a spark that tell me why not use IE to get the cookie and the crumb since IE also uses Wininet.dll and with Wininet.dll in my software it might look for things that is IE's.

    So I launch IE (no need to be default browser), went to Yahoo Finance symbol historical page so to get the cookie created into the IE cache, right-click on the "Download Data" link and "Copy Shortcut". Then extract the "&crumb=whatEverHere" part and append it to the download URL and the download works.

    I also tested the Xojo's HTMLviewer and VB6's WebBrowser controls after the cookie was created with IE and all download works. Tested on Win 7 x64.

    Hope this help a bit and you can get this to work in Xojo. Haven't test on macOS though.

  7. Cho S

    May 27 1.282150° N, 103.863861° E

    Update:

    Unfortunately, this method not working in Windows 10 x64. Not with IE or Edge. Don't know what has changed in Win 10.

  8. Cho Sing Kum and others,

    Thank you for your attention and responses. Let me give an update.

    For the last several days I have been trying the url as in Norman's post, where I get the Crumb by going to the Yahoo site for a generic download thru the browser and hovering (or right clicking) over the "Download" button and I get the Cookie from the header that comes back from https.Get (generic url). Note over several days the Crumb has not changed, whereas the Cookie has changed at least once. If I solve the stupid problem below, I know how I can insert code at the top of my programs so the the Cookie can be updated each time I run a program.

    However, when I insert the Crumb and Cookie into the url (as with Norman above) and execute:
    //Get file
    https.Secure=True
    https.ConnectionType=3
    https.Get addr

    (This is with RealStudio, now Xojo, where I am using an HTTPSecureSocket)
    I do not get the file. Normally in the old days before the recent troubles (and for many many years before) the file of historical data would be found at https(my name for the SecureSocket) / Page Received / Content (a string of all the data). Now when I do the same, the Content is only: "?</body></html>?"

    Obviously I am close, but am doing something dumb; please if you can respond for a dummy. The url as in Norman's post is the same format as the url generated when you do a download from a browser. Whereas the old url's always ended with something like: "ignore=csv" Do we have to modify the url, so that the data comes back as a csv file, not dependent on a browser? Also, Greg above commented on the ConnectionType. Could that be the problem?

    Any suggestions are appreciated; thank you for your help.
    Jonathan

  9. Norman P

    May 27 Xojo Inc

    @Greg OLone You likely need to update your connection type property. Hardly anyone uses SSLv3 any more. Try TLSv1, 1.1 or 1.2.

    Did you try the other TLS versions ?

      Socket1.ConnectionType = SSLSocket.TLSv11

    or

      Socket1.ConnectionType = SSLSocket.TLSv12
  10. Yes I have tried all: TLSv1, 1.1 and 1.2 with the same result, all with HTTPSecureSocket.

    Norman, with the url as in your first post, have you been able to access a csv file directly into a Real Studio (Xojo) program?

    Jonathan

  11. Cho S

    Jun 2 1.282150° N, 103.863861° E
    Edited 6 months ago

    Hi Jonathan,

    I got it to work in VB6, using winHTTP.dll

    Period1 and Period2 are Unix time format. In VB 6, I use the following. Not sure in Xojo.

    UnixStartDate = (StartDate - DateValue("January 1, 1970")) * 86400
    UnixEndDate = (EndDate - DateValue("January 1, 1970")) * 86400

    You get the Crumb string by going to Yahoo Finance site, for example:
    https://finance.yahoo.com/lookup?s=AAPL

    From the page source (or .ReponseText), parse for "CrumbStore":{"crumb":" and the 11 (or so) characters that follow up to "} is the required crumb to append to the download URL. Sometime I get error with crumb length of 16 so I filter that. You can get the Cookie from the .GetResponseHeader. Provide the Cookie in the .SetRequestHeader when you download. I don't know how to do this in Xojo.

    This way, I get fresh Cookie and Crumb everytime and it is not stored so everything stay with the software.

    -image-

    This is my test output downloading last 7 calender days:

    -image-

  12. Cho S

    Jun 2 1.282150° N, 103.863861° E

    Hi Jonathan,

    It seems quite normal that I may not get the Crumb and Cookie on the first attempt. The second attempt is quite normal, although I did get them on the first and third. So it is better to use a For Next loop and loop a few times and Exit For when the Crumb and Cookie are returned successfully.

    As for the data, I usually get them the first attempt but I think safer to also use a For Next loop.

    In this screenshot, I have some tracking code and it shows second attempt for Crumb Cookie and first attempt for data.

    -image-

  13. Greg O

    Jun 4 Xojo Inc Somewhere near Raleigh, NC

    Have you looked at the httpstatus to see if they're now redirecting you? Winhttp and our new framework HTTPSocket handle that for you, but HTTPSecureSocket does not.

  14. Cho S

    Jun 4 1.282150° N, 103.863861° E
    Edited 6 months ago

    For the symbol lookup url:
    .Status = 200
    .StatusText = OK

    For the data download url in the new format,

    with good Crumb and Cookie:
    .Status = 200
    .StatusText = OK

    without good Crumb and Cookie:
    .Status = 401
    .StatusText = Unauthorized

  15. 4 months ago

    Yahoo finance API is not available anymore. I have moved to MarketXLS after this change, much more reliable data.

  16. Steven T

    Jul 21 Pre-Release Testers, Xojo Pro Melbourne, Australia.

    I've been watching this thread as I will be attempting to incorporate such a feature into my personal accounts application I'm currently developing. I'm not claiming to understand every aspect of this feature just yet, as I'm attending to other parts of the system. Anyway, I also visit various Microsoft Access forums, still using Access for existing projects, where I came across a similar issue. It seems like a fix was found as of 5 Apr 2017.

    This is the VBA line that fixed the issue:

      URLtoSend = "http://download.finance.yahoo.com/d/quotes.csv?s=" & Me.tbxTickSymbols & "&f=" & Me.mySelections

    Apparently, all that was required was to prefix the URL with, "download". The thread in question can be found here .

    If I'm not off the mark, all that is needed is to convert to the Xojo way of doing things.

  17. Steven T

    Jul 21 Pre-Release Testers, Xojo Pro Melbourne, Australia.

    Further, I should have done this first before posting above, I downloaded the sample Access 2010 database, modified the click event as per instructions in the thread, ran the form, and presto, it worked. All the requested stock ticker codes and information were downloaded and entered into the table.

    All that is required is to convert to the Xojo way of doing things.

  18. Cho S

    Jul 21 1.282150° N, 103.863861° E

    Yahoo has two sources of data:

    1. latest price, and
    2. historical data

    There is no problem with latest prices. Yahoo will change the URL now and then so just follow the change. It is the historical data that is the problem of the first post of this thread.

or Sign Up to reply!