Historical Yahoo Finance API On Fritz Again

0 votes

I've had a lot of success obtaining historical stock quotes using the Yahoo Finance API cookie/crumb method. It broke on April 28th, 2022, and using too many words to try to fix it hasn't worked.

As part of the retrieved cookie can be utilized to obtain actual historical data on subsequent usage of the historical Yahoo API, the strategy is to query an unreadable stock symbol. I've tested it with both real and unreadable stock symbols. I'm using Excel 2019 VBA, and the ".waitForResponse (10)" instruction seems to be where the debug hangs. It hangs on the initial instance and is a part of a loop. Below is a display of the code. It writes a zero on ws1.S10 correctly, but it is unable to carry out 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?

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

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"
        .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
Nov 17, 2022 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

It appears that Yahoo updated its finance API. The code appears to function as previously when I delete out the line cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0).

Additionally, when you send the request, comment out ".setRequestHeader "Cookie", Cookie." remainder of my code:

'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

    With WebRequest
        .Open "GET", WebRequestURL, False
        '.setRequestHeader "Cookie", Cookie
        .waitForResponse (10)
    End With
answered Nov 17, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Where is the documentation to refer for coinbase api integration of Etherium coin currency in php?

Hey there! Please refer to the following ...READ MORE

answered Jan 25, 2019 in Others by Omkar
• 69,230 points
0 votes
1 answer

How do I install Ruby on Rails?

Hi @Anvi, what @Pratibha has mentioned is ...READ MORE

answered Mar 1, 2019 in Others by Abha
• 28,140 points
0 votes
1 answer

Installling PHP on windows

As @Abha and @Abhi suggested, its best ...READ MORE

answered Feb 28, 2019 in Others by Anvi
• 14,150 points
0 votes
1 answer

How to do Installation of Ruby on Rails on Linux (Ubuntu)?

Hi Pratibha, installing ROR on ubuntu is ...READ MORE

answered Mar 1, 2019 in Others by Anvi
• 14,150 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

Project ID printed for every task on the API problem

Try this: def teste(id): listID = ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Enabling SEO Optimizer in Magento 1.7 returns 404 error page on frontend links

Modified Magento's stock .htaccess to fit my ...READ MORE

answered Feb 10, 2022 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP