Excel VBA : How can I extract yahoo finance historical data in a defined date range

Question

first time posting here. I need help with the following issue.

I'm extracting historical prices from the yahoo finance website url in Excel VBA. I got a variable cell for the ticker, start date and end date of the data. I converted the url to take the value of those cells to get my data.

Problem : The macro extracts the data from the "ticker" variable cell but it does not give me the date range that I have defined in the "start/end date" variable cells. Instead it gives me the whole historical data.

Original link

http://chart.finance.yahoo.com/table.csv?s=NVDA&a=3&b=15&c=2012&d=3&e=15&f=2017&g=m&ignore=.csv

Converted link

http://chart.finance.yahoo.com/table.csv?s= " & Tick1 & " &a= " & smonth & " &b= " & sday & " &c= " & syear & " &d= " & emonth & " &e= " & eday & " &f= " & eyear & " &g=m&ignore=.csv

Sub book1()

'Macro Sheet 1

'Variables

Dim Tick1 As String
Dim Tick2 As String
Dim Tick3 As String

Dim sday As Long
Dim smonth As Long
Dim syear As Long
Dim eday As Long
Dim emonth As Long
Dim eyear As Long

Dim newsheet As Object

'Delete content
With Sheets("Sheet1")
.Range("A12:D200").Clear
End With

'Variable cells

Tick1 = Range("b1")
Tick2 = Range("c1")
Tick3 = Range("d1")

sday = Day(Range("b2"))
smonth = Month(Range("b2")) - 1
syear = Year(Range("b2"))

eday = Day(Range("b3"))
emonth = Month(Range("b3")) - 1
eyear = Year(Range("b3"))

'Extract data
 'Ticker 1

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;http://chart.finance.yahoo.com/table.csv?s= " & Tick1 & " &a= " & smonth & " &b= " & sday & " &c= " & syear & " &d= " & emonth & " &e= " & eday & " &f= " & eyear & " &g=m&ignore=.csv", _
    Destination:=Range("$A$12"))
    .Name = Tick1
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    '.RefreshStyle = xlInsertDeleteCells
    .RefreshStyle = xlOverwriteDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 9, 9, 9, 9, 9, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

End With

 'Ticker 2

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;http://chart.finance.yahoo.com/table.csv?s= " & Tick2 & " &a= " & smonth & " &b= " & sday & " &c= " & syear & " &d= " & emonth & " &e= " & eday & " &f= " & eyear & " &g=m&ignore=.csv", _
    Destination:=Range("$C$12"))
    .Name = Tick2
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    '.RefreshStyle = xlInsertDeleteCells
    .RefreshStyle = xlOverwriteDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 9, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

 End With

 'Ticker 3

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;http://chart.finance.yahoo.com/table.csv?s= " & Tick3 & " &a= " & smonth & " &b= " & sday & " &c= " & syear & " &d= " & emonth & " &e= " & eday & " &f= " & eyear & " &g=m&ignore=.csv", _
    Destination:=Range("$D$12"))
    .Name = Tick3
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    '.RefreshStyle = xlInsertDeleteCells
    .RefreshStyle = xlOverwriteDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(9, 9, 9, 9, 9, 9, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

With Sheets("Sheet1")
.Rows(62 & ":" & .Rows.Count).Delete
End With

'Copy on newsheet

Set newsheet = ThisWorkbook.Sheets.Add
    newsheet.Name = "Copie"

ThisWorkbook.Sheets("Sheet1").Range("A12:D62").Copy
    ThisWorkbook.Sheets("Copie").Range("A1").Select
        ThisWorkbook.Sheets("Copie").Paste

End With

End Sub

Could be your B2 and B3 cell formatting. If these cells are 'General' and the strings you enter into them are mm/dd/yyyy it should work. Also excel is saying that 'xlOverwriteDeleteCells' isn't defined. — Amorpheuses
The date cells formatting is in date mm/dd/yyyy and it still does not work. I've also edited my post to include the whole program, sorry for that. — Home Wrecker
Why are you including asterisks in both URLs? Removing them and any spaces renders first link fine. — Parfait
@Parfait my bad they we're put there by mistake while editing the original post. — Home Wrecker
Anybody know what the solution to xlOverwriteDeleteCells not defined is. A reference library missing? — Chas

vlayausa vlayausa · Accepted Answer · 2017-04-16T00:15:51

You should check what URLs your macro uses to make connection to Yahoo finance.

You can do that by adding this line right before With ActiveSheet.QueryTables.Add

MsgBox ("http://chart.finance.yahoo.com/table.csv?s= " & Tick1 & " &a= " & smonth & " &b= " & sday & " &c= " & syear & " &d= " & emonth & " &e= " & eday & " &f= " & eyear & " &g=m&ignore=.csv")

When you see the actual link you are trying to open, you will realize that you have a lot of space characters that you actually don't need. You have a blank character before and after each quotation mark and you don't need them. Change that and your problem should be solved.

Correct line should be:

"TEXT;http://chart.finance.yahoo.com/table.csv?s=" & Tick1 & "&a=" & smonth & "&b=" & sday & "&c=" & syear & "&d=" & emonth & "&e=" & eday & "&f=" & eyear & "&g=m&ignore=.csv", _

Also, I am not sure why you take month minus one for your start/end date?

smonth = Month(Range("b2")) - 1

It will not work properly if you use date in January.

Excel VBA : How can I extract yahoo finance historical data in a defined date range

1 Answers