Historical Yahoo Finance API On Fritz Again?-CodePudding

I've been successfully using the Yahoo Finance API cookie/crumb technique to get historical stock quotes for many years. On April 28th, 2022, it broke, and excessive exercise of vocabulary has failed to fix it.

The technique is to lookup an illegible stock symbol, because part of the returned cookie can be used to obtain real historical data on successive uses of the historical Yahoo API. I've tried it with illegible and legitimate stock symbols. I'm using Excel 2019 VBA, and the debug seems to hang on the ".waitForResponse (10)" instruction. It's part of a loop, and it hangs on the first instance. The code is shown below. It successfully writes a zero on ws1.S10, but it fails to do anything after the "Next cook" instruction. Did Yahoo intentionally break the Finance API again, or did Microsoft "improve" Excel? Or, more likely, did I do something stump stupid, like turn on the computer? Thanks!

Sub HistUp()

Dim resultFromYahoo, csv_rows() As String
Dim objRequest
Dim resultArray As Variant
Dim eagle, nColumns, cook, iRows, iCols As Integer
Dim CSV_Fields As Variant
Dim ticker, tickerURL, cookie, crumb As String
Dim HistQuote, HistDiv, DefaultKey As String
Dim Curr, StartPer As String
Dim fox, sheep, bear, elk, wolf, raccoon, snake As Integer
Dim julian, ricky, bubbles As Double
Dim crumbStartPos, crumbEndPos, Lastrow1, Lastrow2 As Long

Set wb = ThisWorkbook
Set ws1 = wb.Worksheets(1)
Set ws2 = wb.Worksheets(2)
Set ws3 = wb.Worksheets(3)
Set ws4 = wb.Worksheets(4)
Set ws5 = wb.Worksheets(5)

Application.EnableEvents = False
Application.DisplayAlerts = False
eagle = ActiveSheet.Index
wb.Worksheets("Warn").Select
wb.Worksheets("Warn").Range("A1").Select
DoEvents

'getCookieCrumb
For cook = 0 To 5  'ask for a valid crumb 6 times
    ws1.Range("S10") = cook
    Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    With objRequest
        .Open "GET", "https://finance.yahoo.com/lookup?s=turpitude", False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send
        .waitForResponse (10)
        cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
        crumbStartPos = InStrRev(.ResponseText, """crumb"":""")   9
        crumbEndPos = crumbStartPos   11
        crumb = Mid(.ResponseText, crumbStartPos, crumbEndPos - crumbStartPos)
    End With
    
    If Len(crumb) = 11 Then 'a valid crumb is 11 characters long
        Exit For
    End If
Next cook

CodePudding user response：

I have recently experienced the same problem, after trying the VBA on a different laptop the issue is also the same. The macro I have been using is similar and only recently stopped working.. I'm unsure how to fix the problem using the posts above

CodePudding user response：

I have the same problem. Looks like Yahoo changed the Yahoo finance API. When I comment out the line: cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0) the code seems to work as before.

Then also comment out '.setRequestHeader "Cookie", Cookie when you send the request. Rest of my code:

'CONSTRUCT THE URL:
'interval=1d or 1wk or 1mo, events=dividends or events=history (prices) or events=splits
    WebRequestURL = "https://query1.finance.yahoo.com/v7/finance/download/" & StockSymbol & _
        "?period1=" & UnixStartDate & "&period2=" & UnixEndDate & _
        "&interval=" & UrlInterval & "&events=" & UrlEvents & "&crumb=" & Crumb

'FETCH THE DATA:
    With WebRequest
        .Open "GET", WebRequestURL, False
        '.setRequestHeader "Cookie", Cookie
        .Send
        .waitForResponse (10)
    End With

CodePudding user response：

jp90025, thanks - commenting out the 2 line has worked for me!