title: Excel VBA : How can I extract yahoo finance historical data in a defined date range
body: <p>first time posting here. I need help with the following issue. </p>
  
  <p>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. </p>
  
  <p><strong>Problem :</strong> 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.</p>
  
  <p>Original link</p>
  
  <pre><code>http://chart.finance.yahoo.com/table.csv?s=NVDA&amp;a=3&amp;b=15&amp;c=2012&amp;d=3&amp;e=15&amp;f=2017&amp;g=m&amp;ignore=.csv
  </code></pre>
  
  <p>Converted link</p>
  
  <pre><code>http://chart.finance.yahoo.com/table.csv?s= " &amp; Tick1 &amp; " &amp;a= " &amp; smonth &amp; " &amp;b= " &amp; sday &amp; " &amp;c= " &amp; syear &amp; " &amp;d= " &amp; emonth &amp; " &amp;e= " &amp; eday &amp; " &amp;f= " &amp; eyear &amp; " &amp;g=m&amp;ignore=.csv
  </code></pre>
  
  <hr>
  
  <pre><code>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= " &amp; Tick1 &amp; " &amp;a= " &amp; smonth &amp; " &amp;b= " &amp; sday &amp; " &amp;c= " &amp; syear &amp; " &amp;d= " &amp; emonth &amp; " &amp;e= " &amp; eday &amp; " &amp;f= " &amp; eyear &amp; " &amp;g=m&amp;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= " &amp; Tick2 &amp; " &amp;a= " &amp; smonth &amp; " &amp;b= " &amp; sday &amp; " &amp;c= " &amp; syear &amp; " &amp;d= " &amp; emonth &amp; " &amp;e= " &amp; eday &amp; " &amp;f= " &amp; eyear &amp; " &amp;g=m&amp;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= " &amp; Tick3 &amp; " &amp;a= " &amp; smonth &amp; " &amp;b= " &amp; sday &amp; " &amp;c= " &amp; syear &amp; " &amp;d= " &amp; emonth &amp; " &amp;e= " &amp; eday &amp; " &amp;f= " &amp; eyear &amp; " &amp;g=m&amp;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 &amp; ":" &amp; .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
  </code></pre>