VBA help - yahoo finance downloader

[seller62]

I am not an expert VBA programmer at all.... I found some time ago a macro for downloading historical data from Yahoo finance.
It has always worked great until 2 days ago, now I keep getting a run time error (see image attached). If I hit "debug" the error points to the code line highlighted in the other image.

This is the macro I created using the Sub GetStock (found online). The value of Cells(2,3) is today's date:

Sub GetStock(ByVal stockSymbol As String, ByVal StartDate As Date, ByVal EndDate As Date)

Dim crumb As String
Dim cookie As String
Dim response As String
Dim strUrl As String
Dim DownloadURL As String
Dim period1, period2 As String
Dim httpReq As WinHttp.WinHttpRequest
Set httpReq = New WinHttp.WinHttpRequest

Application.ScreenUpdating = False

DownloadURL = "Symbol Lookup from Yahoo Finance" & stockSymbol
With httpReq
.Open "GET", DownloadURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
.send
.waitForResponse
response = .responseText
cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
End With

period1 = (StartDate - DateValue("January 1, 1970")) * 86400
period2 = (EndDate - DateValue("January 1, 1970")) * 86400

Dim counter As Long
Dim startCounter As Long
Dim result As String

crumb = Chr(34) & "CrumbStore" & Chr(34) & ":{" & Chr(34) & "crumb" & Chr(34) & ":" & Chr(34)
startCounter = InStr(response, crumb) + Len(crumb)
While Mid(response, startCounter, 1) <> Chr(34)
result = result & Mid(response, startCounter, 1)
startCounter = startCounter + 1
Wend

crumb = result
DownloadURL = "https://query1.finance.yahoo.com/v7/finance/download/" & stockSymbol & "?period1=" & period1 & "&period2=" & period2 & "&interval=1d&events=history&crumb=" & crumb
With httpReq
.Open "GET", DownloadURL, False
.setRequestHeader "Cookie", cookie
.send
.waitForResponse
dataResult = .responseText
End With

dataResult = Replace(dataResult, ",", vbTab)

Dim dataObj As New DataObject
dataObj.SetText dataResult
dataObj.PutInClipboard

Set currentWorksheet = ThisWorkbook.ActiveSheet
Set currentRange = currentWorksheet.Range("A1")
dataObj.GetFromClipboard
currentRange.PasteSpecial

ActiveWindow.SmallScroll Down:=-12
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
Columns("A:F").EntireColumn.AutoFit
Range("A1").Select

Application.ScreenUpdating = True

End Sub


Sub Download()

Sheet3.Activate
T = Cells(2, 3).Value + 1
B = T - 548

Sheet2.Activate
Cells.ClearContents
Call GetStock("SPY", B, T)

Sheet5.Activate
Call GetStock("SPXL", B, T)

Sheet1.Activate
Cells.ClearContents
Call GetStock("^VIX", B, T)

Sheet4.Activate
Cells.ClearContents
Call GetStock("^GSPC", B, T)


Sheet3.Activate

End Sub

 

[pab49162]

I ran into the same problem today and believe the issue is that Yahoo is no longer returning a Set-Cookie HTTP response header.

The solution that worked for me was to simply comment out this line. To do so, simply add two single quote marks like this

'' cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)

I also needed to comment out one other line where the cookie variable was used later in the program. Here is that line after being commented out

'' .setRequestHeader "Cookie", cookie

If your are code similar to what I have, you will probably need to make this change as well.

Hope this helps ... Paul

 

[sumdumgai]

Hello. Did the above change fix your problem? I have the exact problem. Thanks.

 

[seller62]

Yes it worked!

Thanks a lot very much

 

[pab49162]

seller62 - Glad to hear my suggested solution fixed your issue.

 

[sumdumgai]

Can I please ask what version of Excel you are using and what reference library do you use? Thanks.

 

[pab49162]

sumdumgai - I have used both Excel 2013 and Excel 2021 with the VBA code. The library code doesn't really have a name but I found this in the file header

' Samir Khan, simulationconsultant@gmail.com
' http://investexcel.net/multiple-stock-quote-downloader-for-excel/
' Modified by Tony Alquiza, aka.tonyyy@gmail.com, 180910

 

[sverrealbech]

Does anyone have an excel-sheet with this code? Thx!