ShaunC
01-11-2012, 04:35 AM
Hi everyone
I am getting an error when running some VBA code:
The Internet address 'h t t p ://ichart.finance.yahoo.com/table.csv?s=abm.ax&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=.csv' is not valid
(I had to space the http: due to low post count)
The VBA code generates a series of worksheets and then imports the historical data from yahoo finance. The share list was downloaded directly from the Australian Stock Exchange and adapted to my needs. The code works well until there is a share query generated which is not available on Yahoo finance, in this case abm.ax.
What would be the best way to handle this error?
I have tried simply to add:
On Error Resume Next
which works but only after I click the OK button in the error message box.This is clearly not the way to handle the error as I would either:
1. Avoid the error being generated; or
2. provide a mechanism for the error message to be dealt with and the code continues on running until the list is complete.
The code (at this stage) is broken into two parts:
The first, when executed:
adds the worksheet
renames the worksheet to the yahoo finance stock code
"calls" the second part of the code which generates the query to obtain historical data
when data download is complete, moves on to generating the next worksheet on the list.The code which generates the query (the second)(again I had to space the "http:" reference)
Sub GetHistoricalData()
'
' This code will get historical closing trade data from yahoo
' finance using the worksheet name to complete the query URL
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;h t t p ://ichart.finance.yahoo.com/table.csv?s=" & ActiveSheet.Name & "&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=.csv" _
, Destination:=Range("$A$1"))
.Name = "table.csv?s=" & ActiveSheet.Name & "&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.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, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
I am getting an error when running some VBA code:
The Internet address 'h t t p ://ichart.finance.yahoo.com/table.csv?s=abm.ax&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=.csv' is not valid
(I had to space the http: due to low post count)
The VBA code generates a series of worksheets and then imports the historical data from yahoo finance. The share list was downloaded directly from the Australian Stock Exchange and adapted to my needs. The code works well until there is a share query generated which is not available on Yahoo finance, in this case abm.ax.
What would be the best way to handle this error?
I have tried simply to add:
On Error Resume Next
which works but only after I click the OK button in the error message box.This is clearly not the way to handle the error as I would either:
1. Avoid the error being generated; or
2. provide a mechanism for the error message to be dealt with and the code continues on running until the list is complete.
The code (at this stage) is broken into two parts:
The first, when executed:
adds the worksheet
renames the worksheet to the yahoo finance stock code
"calls" the second part of the code which generates the query to obtain historical data
when data download is complete, moves on to generating the next worksheet on the list.The code which generates the query (the second)(again I had to space the "http:" reference)
Sub GetHistoricalData()
'
' This code will get historical closing trade data from yahoo
' finance using the worksheet name to complete the query URL
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;h t t p ://ichart.finance.yahoo.com/table.csv?s=" & ActiveSheet.Name & "&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=.csv" _
, Destination:=Range("$A$1"))
.Name = "table.csv?s=" & ActiveSheet.Name & "&d=11&e=20&f=2011&g=d&a=0&b=1&c=1900&ignore=_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.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, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub