Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why do I have to restart Excel when ActiveSheet.QueryTables.Addfails? Options
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 a Does having 2007 solve the problem? Is there a workaround for previous versions of Excel? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why do I have to restart Excel when ActiveSheet.QueryTables.Addfails? Options
On Dec 18, 3:07*pm, Frank wrote:
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 a 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why do I have to restart Excel when ActiveSheet.QueryTables.Addfails? Options
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why do I have to restart Excel when ActiveSheet.QueryTables.Addfails? Options
On Dec 18, 5:26*pm, Frank wrote:
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-norm...idends-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/fin.../quote-12.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why do I have to restart Excel when ActiveSheet.QueryTables.Addfails? Options
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why do I have to restart Excel when ActiveSheet.QueryTables.Addfails? Options
On Dec 19, 2:56*pm, Frank wrote:
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 and I'll send you a free file that does this for as many symbols as desired, with graphs. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why do I have to restart Excel when ActiveSheet.QueryTables.Add fails? | Excel Programming | |||
ActiveSheet.QueryTables.Add | Excel Programming | |||
ActiveSheet.QueryTables.Add | Excel Programming | |||
ActiveSheet.QueryTables.Add | Excel Programming | |||
With ActiveSheet.QueryTables.Add | Excel Programming |