Excel 2003 and Querys

[Craig Brandt]

I need several questions answered and the answer might be the same for both
questions.

I have a routine that goes out and pulls in data (historical data for a
number of stocks) from the Yahoo Financial page. I wrote the routine in
Excell 2003 on windows 2000. When it is run on an Excell 2007 on Vista. The
routine works fairly well on Excel 2003 and horribly on Vista.

The routine places querys on sheet "QUERYS" and works from the resultant
data. When the activesheet is "QUERYS" the machine seems to be spending a
lot of time in a mode that precludes any other activity on the excel sheet
or the macro editor. I striped out all the calculation data, leaving just
the query setup section. I then remove the loops and simply duplicated the
instructions four times to bring in four different tickers.

So slow motion.
I then added the line to select another sheet prior to populating the querys
and it seems to run a lot faster (on 2003). What is going on and how do I
solve the problem. On 2003 it takes over 6 seconds to manually change the
active cell, on vista, it is more like 24 seconds.

I have included the striped-down code.

If I cannot solve this issue, I just lost about a months worth of work and
that hurts.

Craig



Public Sub DoQuery()
Dim urlRequest
Sheets("Querys").Select
Cells.ClearContents
Sheets("Param").Select
Set SymbolDPtr = Sheets("Querys").Cells(5, 4)
' IBM
SymbolDPtr.Offset(-1, 0) = "IBM"
URL =
"http://ichart.finance.yahoo.com/table.csv?s=IBM&d=3&e=15&f=2009&g=d&a=3&b=1
8&c=2007&ignore=.csv"
Set urlRequest = Sheets("Querys").QueryTables.Add(Connection:="URL;"
+ URL, Destination:=SymbolDPtr)
With urlRequest
.BackgroundQuery = True
.WebSingleBlockTextImport = True
.WebFormatting = xlNone
.RefreshStyle = xlOverwriteCells
.Refresh
End With
' SPY
Set SymbolDPtr = Sheets("Querys").Cells(5, 5)
SymbolDPtr.Offset(-1, 0) = "SPY"
URL =
"http://ichart.finance.yahoo.com/table.csv?s=SPY&d=3&e=15&f=2009&g=d&a=3&b=1
8&c=2007&ignore=.csv"
Set urlRequest = Sheets("Querys").QueryTables.Add(Connection:="URL;"
+ URL, Destination:=SymbolDPtr)
With urlRequest
.BackgroundQuery = True
.WebSingleBlockTextImport = True
.WebFormatting = xlNone
.RefreshStyle = xlOverwriteCells
.Refresh
End With
' GLD
Set SymbolDPtr = Sheets("Querys").Cells(5, 6)
SymbolDPtr.Offset(-1, 0) = "GLD"
URL =
"http://ichart.finance.yahoo.com/table.csv?s=GLD&d=3&e=15&f=2009&g=d&a=3&b=1
8&c=2007&ignore=.csv"
Sheets("Querys").Cells(6, 1) = Timer - Start
Set urlRequest = Sheets("Querys").QueryTables.Add(Connection:="URL;"
+ URL, Destination:=SymbolDPtr)
With urlRequest
.BackgroundQuery = True
.WebSingleBlockTextImport = True
.WebFormatting = xlNone
.RefreshStyle = xlOverwriteCells
.Refresh
End With
' QQQQ
Set SymbolDPtr = Sheets("Querys").Cells(5, 7)
SymbolDPtr.Offset(-1, 0) = "QQQQ"
URL =
"http://ichart.finance.yahoo.com/table.csv?s=QQQQ&d=3&e=15&f=2009&g=d&a=3&b=
18&c=2007&ignore=.csv"
Set urlRequest = Sheets("Querys").QueryTables.Add(Connection:="URL;"
+ URL, Destination:=SymbolDPtr)
With urlRequest
.BackgroundQuery = True
.WebSingleBlockTextImport = True
.WebFormatting = xlNone
.RefreshStyle = xlOverwriteCells
.Refresh
End With
End Sub

 

[Don Guillett]

I do this sort of thing a lot. If desired, send your workbook and clear
instructions and this msg copied into a new sheet. I can look on 2003 and
2007.

 

[Don Guillett]

I did re-create yours. Worked rapidly in both versions of excel on Vista. It
is NOT necessary to have a query for each. You need to do within a loop and
clear the names created.

 

[Craig Brandt]

Don,

Thanks for the review. My actual worksheet has the query setup built into
a loop. I was attempting to remove all possiblity of error with this
routine.

When you said that it ran quickly, did you actually copy my code and use
it in your test? I have run it with very similair results on four
machines.after it has run and I am looking at the QUERY sheet, it takes 5
seconds to select a different cell on that sheet.

When Sheet PARAM is selected and I check the task manager-processes, the
system is 99% in idle. When I select Querys sheet, it switches to 99%
Excel.exe. It looks like the querys are constantly refreshing, even though I
told it not to auto refresh when I opened the workbook. What is the proper
procedure to load and execute the query just the once?

Don, your help is greatly appreciated.

Thanks,

Craig

 

[Craig Brandt]

Don,

Also, Try and remove the "Sheets("Param").Select" line from the example
that I sent. Around line 5.

Let me know if you have similair results.

Thanks,

Craig

 

[Don Guillett]

I usually only do queries from a macro unless a customer desires automatic.
Send your wb to my address below and I'll take a look.

 

[Don Guillett]

That's one of the first things I did. Your problem may?? be this simple.
'yours
'.Refresh

'use this instead
..Refresh BackgroundQuery:=False