Excel VBA :如何在定义的日期范围内提取雅虎财经历史数据

Question

>第一次在这里发帖。我需要以下问题的帮助。

我正在从Excel VBA中的雅虎财经网站网址中提取历史价格。我得到了一个用于数据的股票代码、开始日期和结束日期的变量单元格。我转换了 url 以获取这些单元格的值来获取我的数据。

问题：宏从"股票代码"变量单元格中提取数据，但它没有给我在"开始/结束日期"变量单元格中定义的日期范围。相反，它给了我整个历史数据。

原始链接

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

转换后的链接

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

Answer 1

您应该检查宏使用哪些URL连接到雅虎财经。

您可以通过在 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")

当您看到尝试打开的实际链接时，您会意识到您有很多实际上不需要的空格字符。每个引号前后都有一个空白字符，不需要它们。改变这一点，你的问题应该得到解决。

正确的行应该是：

"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", _

另外，我不确定为什么您将月份减去一作为开始/结束日期？

smonth = 月份(范围("b2"(( - 1

如果您使用一月份的日期，它将无法正常工作。