I use VBA to pull stock prices into excel. It stopped working yesterday (Sep 24, 2024).

The method required a CRUM that does not seem to exist anymore. When requesting CRUM, I get “e html> <ht”, which isn’t garbage. The code does not create error until later when it does not have any data to work with.

Does anyone now that the solution is? The code is below. Much appreciated!!

Max

Sub getCookieCrumb(crumb As String, cookie As String, validCookieCrumb As Boolean)

    Dim i As Integer
    Dim str As String
    Dim crumbStartPos As Long
    Dim crumbEndPos As Long
    Dim objRequest
 
    validCookieCrumb = False
    
    'ask for a valid crumb 5 times

    For i = 0 To 5  'ask for a valid crumb 5 times
        Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
        With objRequest
            .Open "GET", "https://finance.yahoo.com/quote/AAPL/history?period1=1503558000&period2=1535094000&interval=1wk&filter=history&frequency=1wk", False
            .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
            .send
            .WaitForResponse (10)
            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
            validCookieCrumb = True
            Exit For
        End If
    Next i
    
End Sub