My VBA code to extract stock price data from Yahoo Finance now shows an error.
The bug is at the following line ReDim resultArray(0 To UBound(csv_rows), 0 To nColumns) As Variant
Is this due to Yahoo changing its website structure? How should I edit my code to continue extracting data from Yahoo Finance?
Sub getYahooFinanceData(tickerSymbol As String, startDate As String, endDate As String, frequency As String, _
cookie As String, crumb As String, ByVal ticker As Long)
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
'Construct URL
'***************************************************
tickerURL = "https://query1.finance.yahoo.com/v7/finance/download/" & tickerSymbol & _
"?period1=" & startDate & _
"&period2=" & endDate & _
"&interval=" & frequency & "&events=history" & "&crumb=" & crumb
'Sheets("Parameters").Range("K" & ticker - 1) = tickerURL
'***************************************************
'Get data from Yahoo
'***************************************************
Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
With objRequest
.Open "GET", tickerURL, False
'.setRequestHeader "Cookie", cookie
.send
.waitForResponse
resultFromYahoo = .ResponseText
End With
'***************************************************
'Parse returned string into an array
'***************************************************
nColumns = 6 'number of columns minus 1 (date, open, high, low, close, adj close, volume)
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
4
I noticed on 9/7/2024 that Yahoo Finance no longer provides free downloads (spreadsheet format). That option requires a $50/month subscription. So our scripts don’t work. We can still get the data in HTML form, but i haven’t figured out how to parse that yet. Perhaps someone here has…
1