I realised that Yahoo Finance has removed the Download function.
I can replace the tickerURL line 'download' with 'chart' to extract data from Yahoo Finance. However, this is in JSON.
How do I adjust my VBA code for JSON converter?
I can replace the tickerURL line 'download' with 'chart' to extract data from Yahoo Finance. However, this is in JSON.
VBA Code:
tickerURL = "https://query1.finance.yahoo.com/v7/finance/download/"
VBA Code:
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
'Write results into worksheet for ticker
Worksheets("Port").Range("$B$4").Offset(ticker - firstTickerRow, 0) _
.Resize(UBound(resultArray, 1) + 1, UBound(resultArray, 2) + 1).Value = resultArray
