Here is an example I found lately on the web. It is in VBA for Excel
I do not know why this "CookieCrumb" procedure is called, seems to be important.
Sub GetData()
Dim InputControls As Worksheet
Dim OutputData As Worksheet
Dim Symbol As String
Dim startDate As String
Dim endDate As String
Dim period As String
Dim last As Double
Dim OffsetCounter As Double
Dim crumb As String
Dim cookie As String
Dim validCookieCrumb As Boolean
Dim Result As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set InputControls = Sheets("Sheet1")
Set OutputData = Sheets("HistoricalData")
With InputControls
last = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
' Arguments
startDate = (InputControls.Range("B4") - DateValue("1970-01-01")) * 86400
endDate = (InputControls.Range("B5") - DateValue("1970-01-01")) * 86400
period = "1d"
Dateminus = -InputControls.Range("B4") + InputControls.Range("B5")
If InputControls.Range("B5") > Date Then
Result = MsgBox("EndDate seems greater than today's date. Okay to you?", vbYesNo, "Validate End Date")
If Result = vbNo Then
Exit Sub
End If
End If
If Dateminus < 1 Then
MsgBox ("Date difference must be atleast one. Since EndDate is not inclusive of the date, you can have one day difference between start and end Date to fetch latest price")
Exit Sub
End If
' Period
If InputControls.Range("B3") = "Daily" Then
period = "1d"
ElseIf InputControls.Range("B3") = "Weekly" Then
period = "1wk"
ElseIf InputControls.Range("B3") = "Monthly" Then
period = "1mo"
End If
Call getCookieCrumb(crumb, cookie, validCookieCrumb)
OutputData.Range("A2:H1000000").ClearContents
'Loop over multiple symbols
For i = 8 To last
Symbol = InputControls.Range("A" & i).Value
OffsetCounter = 1
Call ExtractData(Symbol, startDate, endDate, period, cookie, crumb, OffsetCounter)
Next i
OutputData.Columns("A:H").AutoFit
Application.Calculation = xlCalculationAutomatic
InputControls.Select
MsgBox ("Task Accomplished. See HistoricalData Tab")
End Sub
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
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/lookup?s=bananas", False
.send
.waitForResponse (10)
End With
If Len(crumb) = 11 Then 'a valid crumb is 11 characters long
validCookieCrumb = True
Exit For
End If:
Next i
End Sub
Sub ExtractData(Symbols As String, startDate As String, endDate As String, period As String, cookie As String, crumb As String, OffsetCounter As Double)
Dim resultFromYahoo As String
Dim objRequest
Dim csv_rows() As String
Dim resultArray As Variant
Dim nColumns As Integer
Dim iRows As Integer
Dim CSV_Fields As Variant
Dim iCols As Integer
Dim tickerURL As String
tickerURL = "
https://query1.finance.yahoo.com/v7/finance/download/" & Symbols & _
"?period1=" & startDate & _
"&period2=" & endDate & _
"&interval=" & period & "&events=history" & "&crumb=" & crumb
Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
With objRequest
.Open "GET", tickerURL, False
.send
.waitForResponse
resultFromYahoo = .responseText
MsgBox (" ASK: " & tickerURL)
MsgBox (" resp: " & .responseText)
End With
nColumns = 8
csv_rows() = Split(resultFromYahoo, Chr(10))
csv_rows = Filter(csv_rows, csv_rows(0), False)
ReDim resultArray(0 To UBound(csv_rows), 0 To nColumns) As Variant
For iRows = LBound(csv_rows) To UBound(csv_rows)
CSV_Fields = Split(csv_rows(iRows), ",")
If UBound(CSV_Fields) > nColumns Then
nColumns = UBound(CSV_Fields)
ReDim Preserve resultArray(0 To UBound(csv_rows), 0 To nColumns) As Variant
End If
For iCols = LBound(CSV_Fields) To UBound(CSV_Fields)
If IsNumeric(CSV_Fields(iCols)) Then
resultArray(iRows, iCols) = Val(CSV_Fields(iCols))
ElseIf IsDate(CSV_Fields(iCols)) Then
resultArray(iRows, iCols) = CDate(CSV_Fields(iCols))
Else
resultArray(iRows, iCols) = CStr(CSV_Fields(iCols))
End If
Next
Next
Sheets("HistoricalData").Select
Range("A1000000").End(xlUp).Offset(OffsetCounter, 0).Select
Selection.Resize(UBound(resultArray, 1) + 1, UBound(resultArray, 2) + 1).Value = resultArray
Range("H1000000").End(xlUp).Offset(OffsetCounter, 0).Select
Selection.Resize(UBound(resultArray, 1) + 1, 1).Value = Symbols
End Sub
EDIT: This seems to be a new security feature;
https://stackoverflow.com/questions/56698011/consistently-retrieving-cookie-and-crumb-from-yahoo-finance-for-historical-data
Recent
