QueryTables bug. If one query fails, all subsequent queries will fail unless restart Excel

[sheepmob]

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 subsequent 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 subsequent queries for all 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=1687899850&period2=1719522250&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 Symbol XXX
web_Link = "https://query1.finance.yahoo.com/v7/finance/download/XXX?period1=1687899850&period2=1719522250&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 Symbol QQQ
web_Link = "https://query1.finance.yahoo.com/v7/finance/download/QQQ?period1=1687899850&period2=1719522250&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

 

[Micron]

I can't comment on why some versions have the issue and some don't, but what I might try is adding this line right after the connection string
application.WorksheetFunction.WebService(web_Link)
and if the url is invalid, error 1004 would be raised. In that case, skip the offending block of code. If there was a way to validate some property of that function I'd rather skip the code instead of raising an error, but at this point I don't know what that would be. I tried wrapping your With block in

If Application.WorksheetFunction.WebService(web_Link) <> "" Then

End If
but testing for an empty string is ignored. In the immediate window this test
?application.WorksheetFunction.WebService(web_Link)=""

raises the error though. Can't understand why one does and not the other. Anyway, I think some form of that worksheet function may be the way to go, even it it's just by raising the error then skipping the With block.

Using On Error Resume Next is never the way to go unless it involves things like setting properties or using automation such as when trying to access a program that may not be open.

 

[Micron]

Because I deleted Sheet1, referencing worksheets via index as you are was an issue for me. I modified your code to declare a sheet variable to deal with sheet name instead so watch out for that. I added/changed at the top:
Dim errNum As Long
On Error GoTo errHandler
Here's an approach using the last web inquiry. Seems to work.

' Download Data for Stock Symbol QQQ
errNum = 0
web_Link = "https://query1.finance.yahoo.com/v7/finance/download/QQQ?period1=1687899850&period2=1719522250&interval=1d&events=history&includeAdjustedClose=true"
Application.WorksheetFunction.WebService (web_Link)
If errNum = 0 Then
    With ws.QueryTables.Add(Connection:="TEXT;" & web_Link, _
    Destination:=ws.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 If

exitHere:
Set ws = Nothing
Exit Sub

errHandler:
If Err.Number = 1004 Then
    errNum = 1004
    Resume Next
End If

I'm not real enthused about trapping error 1004 because it is used for so many unrelated things (actually, that is quite stupid IMO). I might try to make use of the specific error message text as well/instead.

 

[sheepmob]

Hi, Micron
Thank you very much for your help. Problem solved.

 

[Micron]

Glad I could help & thanks for the recognition. On second thought, this error handler probably makes more sense:

errHandler:
If Err.Number = 1004 Then
    errNum = 1004
    Resume Next
Else
    msgbox "Error " & Err.Number & ": " & Err.Description
    Resume exitHere
End If