QueryTables bug for Excel 2019 and Excel 2021
I wrote a VBA code to download stock data from Yahoo Finance. To explain the bug that I discovered, I wrote the following VBA program. The VBA program downloads data for stock symbols SPY, XXX, and QQQ from the Yahoo Finance website and displays the downloaded data on sheet 1. Because the stock symbol XXX does not exist, the QueryTables to download the data of Stock XXX will generate an error. I used “On Error Resume Next” to ignore the error and move to the next line of the program to download data for stock QQQ. For Excel 2010, if the query to download data of a stock fails because the Yahoo Finance web server does not have the data., the next downloads for other stocks can still work. But, for Excel 2019 and Excel 2021. maybe other Excel of newer versions too, if a query for a stock fails, all next queries for other stocks will fail unless I restart Excel. I tested the problem in Windows 10 and 11, and the results are the same. Do any people know why new versions of Excel have this problem while Excel 2010 does not? I would appreciate it if someone could help me to solve this problem.
I need to download data for over a hundred stocks. Data for some stock symbols are sometimes missing in Yahoo Finance. Because of the bug, if the download for one stock ticker fails, I have to restart Excel and download the data from the beginning again.
************************************************************
Private Sub Download_From_Yahoo_Click()
On Error Resume Next
Dim web_Link As String
‘Download Data for Stock Symbol SPY
web_Link = “https://query1.finance.yahoo.com/v7/finance/download/SPY?period1=1687851663&period2=1719474063&interval=1d&events=history&includeAdjustedClose=true“
With Sheet1.QueryTables.Add(Connection:=”TEXT;” & web_Link, _
Destination:=Sheet1.Range(“A1”))
.Name = “import_1”
.FieldNames = True ‘ field names in source data appear as column headers
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1 ‘ Start data from row 2 to exclude headings
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
‘ Download Data for Stock Symobol XXX
web_Link = “https://query1.finance.yahoo.com/v7/finance/download/XXX?period1=1687851663&period2=1719474063&interval=1d&events=history&includeAdjustedClose=true“
With Sheet1.QueryTables.Add(Connection:=”TEXT;” & web_Link, _
Destination:=Sheet1.Range(“I1”))
.Name = “import_2”
.FieldNames = True ‘ field names in source data appear as column headers
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1 ‘ Start data from row 2 to exclude headings
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
‘ Download Data for Stock Symobol QQQ
web_Link = “https://query1.finance.yahoo.com/v7/finance/download/QQQ?period1=1687851663&period2=1719474063&interval=1d&events=history&includeAdjustedClose=true“
With Sheet1.QueryTables.Add(Connection:=”TEXT;” & web_Link, _
Destination:=Sheet1.Range(“Q1”))
.Name = “import_3”
.FieldNames = True ‘ field names in source data appear as column headers
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1 ‘ Start data from row 2 to exclude headings
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
I wrote a VBA code to download stock data from Yahoo Finance. To explain the bug that I discovered, I wrote the following VBA program. The VBA program downloads data for stock symbols SPY, XXX, and QQQ from the Yahoo Finance website and displays the downloaded data on sheet 1. Because the stock symbol XXX does not exist, the QueryTables to download the data of Stock XXX will generate an error. I used “On Error Resume Next” to ignore the error and move to the next line of the program to download data for stock QQQ. For Excel 2010, if the query to download data of a stock fails because the Yahoo Finance web server does not have the data., the next downloads for other stocks can still work. But, for Excel 2019 and Excel 2021. maybe other Excel of newer versions too, if a query for a stock fails, all next queries for other stocks will fail unless I restart Excel. I tested the problem in Windows 10 and 11, and the results are the same. Do any people know why new versions of Excel have this problem while Excel 2010 does not? I would appreciate it if someone could help me to solve this problem.I need to download data for over a hundred stocks. Data for some stock symbols are sometimes missing in Yahoo Finance. Because of the bug, if the download for one stock ticker fails, I have to restart Excel and download the data from the beginning again.************************************************************Private Sub Download_From_Yahoo_Click()On Error Resume NextDim web_Link As String’Download Data for Stock Symbol SPYweb_Link = “https://query1.finance.yahoo.com/v7/finance/download/SPY?period1=1687851663&period2=1719474063&interval=1d&events=history&includeAdjustedClose=true”With Sheet1.QueryTables.Add(Connection:=”TEXT;” & web_Link, _Destination:=Sheet1.Range(“A1”)).Name = “import_1”.FieldNames = True ‘ field names in source data appear as column headers.RowNumbers = False.FillAdjacentFormulas = False.PreserveFormatting = True.RefreshOnFileOpen = False.RefreshStyle = xlOverwriteCells.SavePassword = False.SaveData = True.AdjustColumnWidth = True.RefreshPeriod = 0.TextFilePromptOnRefresh = False.TextFilePlatform = 1252.TextFileStartRow = 1 ‘ Start data from row 2 to exclude headings.TextFileParseType = xlDelimited.TextFileTextQualifier = xlTextQualifierDoubleQuote.TextFileConsecutiveDelimiter = False.TextFileTabDelimiter = True.TextFileSemicolonDelimiter = False.TextFileCommaDelimiter = True.TextFileSpaceDelimiter = False.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1).TextFileTrailingMinusNumbers = True.Refresh BackgroundQuery:=FalseEnd With’ Download Data for Stock Symobol XXXweb_Link = “https://query1.finance.yahoo.com/v7/finance/download/XXX?period1=1687851663&period2=1719474063&interval=1d&events=history&includeAdjustedClose=true”With Sheet1.QueryTables.Add(Connection:=”TEXT;” & web_Link, _Destination:=Sheet1.Range(“I1”)).Name = “import_2”.FieldNames = True ‘ field names in source data appear as column headers.RowNumbers = False.FillAdjacentFormulas = False.PreserveFormatting = True.RefreshOnFileOpen = False.RefreshStyle = xlOverwriteCells.SavePassword = False.SaveData = True.AdjustColumnWidth = True.RefreshPeriod = 0.TextFilePromptOnRefresh = False.TextFilePlatform = 1252.TextFileStartRow = 1 ‘ Start data from row 2 to exclude headings.TextFileParseType = xlDelimited.TextFileTextQualifier = xlTextQualifierDoubleQuote.TextFileConsecutiveDelimiter = False.TextFileTabDelimiter = True.TextFileSemicolonDelimiter = False.TextFileCommaDelimiter = True.TextFileSpaceDelimiter = False.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1).TextFileTrailingMinusNumbers = True.Refresh BackgroundQuery:=FalseEnd With’ Download Data for Stock Symobol QQQweb_Link = “https://query1.finance.yahoo.com/v7/finance/download/QQQ?period1=1687851663&period2=1719474063&interval=1d&events=history&includeAdjustedClose=true”With Sheet1.QueryTables.Add(Connection:=”TEXT;” & web_Link, _Destination:=Sheet1.Range(“Q1”)).Name = “import_3”.FieldNames = True ‘ field names in source data appear as column headers.RowNumbers = False.FillAdjacentFormulas = False.PreserveFormatting = True.RefreshOnFileOpen = False.RefreshStyle = xlOverwriteCells.SavePassword = False.SaveData = True.AdjustColumnWidth = True.RefreshPeriod = 0.TextFilePromptOnRefresh = False.TextFilePlatform = 1252.TextFileStartRow = 1 ‘ Start data from row 2 to exclude headings.TextFileParseType = xlDelimited.TextFileTextQualifier = xlTextQualifierDoubleQuote.TextFileConsecutiveDelimiter = False.TextFileTabDelimiter = True.TextFileSemicolonDelimiter = False.TextFileCommaDelimiter = True.TextFileSpaceDelimiter = False.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1).TextFileTrailingMinusNumbers = True.Refresh BackgroundQuery:=FalseEnd WithEnd Sub Read More
