Option Explicit
Public Const firstTickerRow As Integer = 5
Sub GET_DATA()
Dim frequency As String
Dim lastRow As Integer
Dim stockTicker As String
Dim startDate As String
Dim endDate As String
Dim ticker As Integer
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lastRow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row
frequency = Worksheets("Parameters").Range("b3")
'Convert user-specified calendar dates to Unix time
'***************************************************
startDate = (Sheets("Parameters").Range("startDate") - DateValue("January 1, 1970")) * 86400
endDate = (Sheets("Parameters").Range("endDate") - DateValue("January 1, 1970")) * 86400
'Set date retrieval frequency
'***************************************************
If Worksheets("Parameters").Range("frequency") = "d" Then
frequency = "1d"
ElseIf Worksheets("Parameters").Range("frequency") = "w" Then
frequency = "1wk"
ElseIf Worksheets("Parameters").Range("frequency") = "m" Then
frequency = "1mo"
End If
'***************************************************
'Delete all sheets apart from Parameters sheet
'***************************************************
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Parameters" And ws.Name <> "About" Then ws.Delete
Next
'Loop through all tickers
For ticker = firstTickerRow To lastRow
stockTicker = Worksheets("Parameters").Range("$a$" & ticker)
If stockTicker = "" Then
GoTo NextIteration
End If
'Create a sheet for each ticker
'***************************************************
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = stockTicker
Cells(1, 1) = "Stock Quotes for " & stockTicker
'***************************************************
'Get financial data from Yahoo and write into each sheet
'getCookieCrumb() must be run before running getYahooFinanceData()
'***************************************************
Call getYahooFinanceData(stockTicker, startDate, endDate, frequency)
ActiveSheet.Range("B3:B3").Select
ActiveSheet.Range("C3:C3").Select
ActiveSheet.Range("D3:D3").Select
ActiveSheet.Range("E3:E3").Select
ActiveSheet.Range("F3:F3").Select
ActiveSheet.Range("G3:G3").Select
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.HasAxis(xlValue, xlSecondary) = True
ActiveChart.HasLegend = False
ActiveChart.Parent.Width = 600
ActiveChart.Parent.Height = 300
NextIteration:
Next ticker
ErrorHandler:
Worksheets("Parameters").Select
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Sub DELT()
Dim ws As Worksheet
Application.DisplayAlerts = False
'for deleting sheets except sheet named Parameter
'************************************************
For Each ws In Worksheets
If ws.Name <> "Parameters" And ws.Name <> "About" Then ws.Delete
Next
'************************************************
End Sub
Sub getYahooFinanceData(stockTicker As String, startDate As String, endDate As String, frequency As String)
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/" & stockTicker & _
"?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
.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))
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
Sheets(stockTicker).Range("A2").Resize(UBound(resultArray, 1) + 1, UBound(resultArray, 2) + 1).Value = resultArray
'***************************************************
End Sub