Why do I have to restart Excel when ActiveSheet.QueryTables.Addfails? Options

[Frank]

I had posted this question a few weeks ago but got no answer so I am
reposting it again.

Original post:

So that you know, I’ve looked on various fora but except for the fact
that Excel 2007 seems not to have this problem, I could not find an
answer for previous versions of Excel.

Here is my query:


With ActiveSheet.QueryTables.Add(Connection:="TEXT;http://
ichart.finance.yahoo.com/table.csv?s=BLABLABLA&ignore=.csv",
Destination:=Range("A1"))
.RefreshStyle = xlOverwriteCells
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(1, 9, 9, 9, 1, 9, 9)
.Refresh BackgroundQuery:=False
End With


Obviously BLABLABLA is not a valid ticker symbol but occasionally,
even with a valid ticker symbol, the query fails, for example, if
Yahoo! Finance website or the internet connection is down.


Once a query fails, I need to shut Excel down to start a valid query.


I cannot verify that the problem does not occur with Excel 2007 (I
still use 2003).


My questions are:
Does having 2007 solve the problem?
Is there a workaround for previous versions of Excel?


Thanks

 

[Don Guillett Excel MVP]

I had posted this question a few weeks ago but got no answer so I am
reposting it again.

Original post:

So that you know, I’ve looked on various fora but except for the fact
that Excel 2007 seems not to have this problem, I could not find an
answer for previous versions of Excel.

Here is my query:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;http://
ichart.finance.yahoo.com/table.csv?s=BLABLABLA&ignore=.csv",
Destination:=Range("A1"))
    .RefreshStyle = xlOverwriteCells
    .TextFileCommaDelimiter = True
    .TextFileColumnDataTypes = Array(1, 9, 9, 9, 1, 9, 9)
    .Refresh BackgroundQuery:=False
End With

Obviously BLABLABLA is not a valid ticker symbol but occasionally,
even with a valid ticker symbol, the query fails, for example, if
Yahoo! Finance website or the internet connection is down.

Once a query fails, I need to shut Excel down to start a valid query.

I cannot verify that the problem does not occur with Excel 2007 (I
still use 2003).

My questions are:
Does having 2007 solve the problem?
Is there a workaround for previous versions of Excel?

Thanks

I tested your macro in xl2003 with no problem and see no reason to
think that xl2007 would be different.
What is the error msg? You could try using an on error resume next

Why are you getting the actual close instead of the adjusted close?
Date Open High Low Close Volume Adj Close
1/2/1962 578.5 578.5 572 572 387200 2.62

 

[Frank]

Hi Don:

Thanks for your message.

It looks like you used IBM as the ticker instead of blablabla. While I
have built workarounds to insure the ticker symbol is valid,
occasionally, a valid symbol will still fail. In this case, I have to
restart Excel to make a new query. Resume next does not solve the
problem.

I’ve uploaded the error message I get. I first used blablabla and then
IBM. The gif file is the IBM error.

http://www.gingins.info/error1.gif

To answer your question about why I do not use adjusted price, I’ve
uploaded another gif file which explain my logic: http://www.gingins.info/ibm.gif

Regards,

 

[Don Guillett Excel MVP]

Hi Don:

Thanks for your message.

It looks like you used IBM as the ticker instead of blablabla. While I
have built workarounds to insure the ticker symbol is valid,
occasionally, a valid symbol will still fail. In this case, I have to
restart Excel to make a new query. Resume next does not solve the
problem.

I’ve uploaded the error message I get. I first used blablabla and then
IBM. The gif file is the IBM error.

http://www.gingins.info/error1.gif

To answer your question about why I do not use adjusted price, I’ve
uploaded another gif file which explain my logic:http://www.gingins.info/ibm.gif

Regards,

I use a different method with an an on error that doesn't have that
problem
strurl = "http://table.finance.yahoo.com/table.csv?s=" & symbol

As to the adjusted price, you may find these useful
Yahoo uses proportionate adjustments meaning they calculate the
dividend as a percentage and removes the percentage for adjusting data
prior to ex-dividend. Yahoo's method is the industry standard.
http://luminouslogic.com/how-to-normalize-historical-data-for-splits-dividends-etc.htm
If I use the "actual" close, then I *will* need to adjust the number
of owned shares for splits as they happened.
http://help.yahoo.com/l/us/yahoo/finance/quotes/quote-12.html
(e-mail address removed)

 

[Frank]

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

 

[Don Guillett Excel MVP]

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 athttp://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

My suggestion was to not use text and not use the array. use as shown
with text to columns. Send your email adddy to (e-mail address removed) and
I'll send you a free file that does this for as many symbols as
desired, with graphs.