I revised the URL as follow so that download the CSV file from Yahoo Finance history data, but there are occur error 1004 after "End With". Anyone solve it? Thank...
Code:
Sub GetStock(ByVal stockSymbol As String, ByVal StartDate As String, _
ByVal EndDate As String, ByVal Kbar As String)
'Get and update price data from Yahoo
Dim DownloadURL As String
Dim StartPeriod As String, EndPeriod As String
Dim s As Integer, r As Integer, r0 As Integer
Dim chk As AutoFilter
'clear filter and old data
Set chk = dat.AutoFilter
If Not chk Is Nothing Then
chk.Range.AutoFilter
End If
Range("A:G").ClearContents
StartPeriod = 946656000 + DateDiff("d", "2000/01/01", StartDate) * 86400
EndPeriod = 946656000 + DateDiff("d", "2000/01/01", EndDate) * 86400
DownloadURL = "URL;https://query1.finance.yahoo.com/v7/finance/download/" _
+ stockSymbol + "?period1=" + StartPeriod + "&period2=" + EndPeriod _
+ "&interval=1" + Kbar + "&events=history&crumb=NVyiH1kN4FA"
Range("k1").value = DownloadURL
With ActiveSheet.QueryTables.Add(Connection:=DownloadURL, Destination:= _
Range("$A$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.refresh BackgroundQuery:=False
End With
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, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1))
Columns("A:F").EntireColumn.AutoFit
Set chk = Nothing
End Sub
