Hi Don:
Thanks for your posting. I appreciate your help:
Below is my test routine. Using Excel 2003, it fails and restart is
required. Adding a resume next does not solve it either.
The reason I use “table” instead “ifinance” is because a few years ago
(2002 or so), “table” no longer worked. It apparently is now.
Using non-adjusted versus adjusted-closing price: None of my programs
use shares but if I were, I would still use non-adjusted prices and
adjust the number of shares through time. Here is why: I’ve uploaded a
picture of what CSCO looks like the first few days of trading back in
1990. From 3/26 to 4/11, Cisco lost 9.3% of its value but when using
adjusting close, because of the two-decimal restriction, the share
price was flat. Calculating volatility using adjusted prices is also
misleading. The picture is at
http://www.gingins.info/csco.gif.
Here is my test routine:
Dim symbol As Range
Dim strurl As String
Sub atest()
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Delete
Range("A1") = "ibm" 'valid symbol
Set symbol = Range("A1")
strurl = "http://table.finance.yahoo.com/table.csv?s=" & symbol
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strurl,
Destination:=Range("A1"))
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(4, 9, 9, 9, 1, 9, 9)
.Refresh BackgroundQuery:=False
End With
ActiveSheet.UsedRange.Delete
Range("A1") = "blablabla" 'invalid symbol
Set symbol = Range("A1")
strurl = "http://table.finance.yahoo.com/table.csv?s=" & symbol
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strurl,
Destination:=Range("A1"))
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(4, 9, 9, 9, 1, 9, 9)
.Refresh BackgroundQuery:=False
End With
ActiveSheet.UsedRange.Delete
Range("A1") = "ge" 'valid symbol
Set symbol = Range("A1")
strurl = "http://table.finance.yahoo.com/table.csv?s=" & symbol
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strurl,
Destination:=Range("A1"))
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(4, 9, 9, 9, 1, 9, 9)
.Refresh BackgroundQuery:=False
End With
End Sub