Calculating a worksheet

[newguy]

I have the following code which retrieves the current market quote of
a company based on the ticker symbol from Google finance. I am looking
for a more efficient way to recalc the function/update it. Currently I
have a button with a macro that just calculates the whole worksheet.
Which has 40+ ticker symbols so it takes 30-45 sec's to calc. Is there
a more efficient way to do this. Below is the code I use to retrieve
the stock price.

Public Function googleQuote(symbol As String) As Variant

'Source is google.com/finance

Dim xmlhttp As Object
Dim strURL As String
Dim CompanyID As String
Dim x As String
Dim sSearch As String

strURL = "http://www.google.com/finance?q=" & symbol
Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
..Open "get", strURL, False
..send
x = .responsetext
End With

Set xmlhttp = Nothing

'Find "setCompanyID(" that google assigns to each ticker symbol
sSearch = "setCompanyId("
CompanyID = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
CompanyID = Trim(Mid(CompanyID, 1, InStr(1, CompanyID, ")") - 1))

'Use the company ID to retrieve data needed
sSearch = "ref_" & CompanyID & "_l"">"
googleQuote = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
googleQuote = Left(googleQuote, InStr(1, googleQuote, "<") - 1)

End Function

Thanks

 

[joeu2004]

I have a button with a macro that just calculates
the whole worksheet. Which has 40+ ticker symbols
so it takes 30-45 sec's to calc. Is there a more
efficient way to do this. [....]
strURL = "http://www.google.com/finance?q=" & symbol
Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
.Open "get", strURL, False
.send
x = .responsetext
End With
Set xmlhttp = Nothing

I'm not an expert on this type of coding. But I note that on my
computer, the excerpted code usually consumes 99.6% to 99.9% of the
time to execute the function -- typically 0.3 to 0.6 sec, but
occassionally 2 to 4 sec.

And since the excerpted code appears to be what you need to do to
acquire the web page (but I don't know that for sure), I would say
there is nothing you can to improve the performance significantly.

30-45 sec for 40 or so function calls sounds about right -- using my
computer and cable ISP.

However, there might be faster websites that provide stock quotes.

 

[newguy]

I have a button with a macro that just calculates
the whole worksheet.  Which has 40+ ticker symbols
so it takes 30-45 sec's to calc. Is there a more
efficient way to do this. [....]
strURL = "http://www.google.com/finance?q=" & symbol
Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
.Open "get", strURL, False
.send
x = .responsetext
End With
Set xmlhttp = Nothing

I'm not an expert on this type of coding.  But I note that on my
computer, the excerpted code usually consumes 99.6% to 99.9% of the
time to execute the function -- typically 0.3 to 0.6 sec, but
occassionally 2 to 4 sec.

And since the excerpted code appears to be what you need to do to
acquire the web page (but I don't know that for sure), I would say
there is nothing you can to improve the performance significantly.

30-45 sec for 40 or so function calls sounds about right -- using my
computer and cable ISP.

However, there might be faster websites that provide stock quotes.

What would be the best way to break up the recalculations? The goal of
the worksheet is not provide real-time information so how could I have
the calculation command run on one cell within the range wait a
certain amount of seconds and the update the other one. Would this be
more resource intensive then waiting for the whole sheet to
recalculate?

 

[joeu2004]

What would be the best way to break up the recalculations?
The goal of the worksheet is not provide real-time
information so how could I have the calculation command
run on one cell within the range wait a certain amount
of seconds and the update the other one.

See the example file "stock quote Ontime.xls" at http://www.box.net/shared/ep0ok55hfm
..

As-is, you can start the updates manually by running the macro
"startit". You can stop the updates by running the macro "stopit".

If you would like the updates to start automatically, see the comments
in the Workbook_Open event macro in the ThisWorkbook object. As-is,
the functionality is disabled.

When "startit" is run, the stock prices are updated every 2 seconds
(t1) in round-robin fashion. After the first cycle, they are updated
every 5 seconds (t2), again round-robin. All the real work is in the
private sub "doit".

Those frequencies are chosen for demo purposes. I would change t2 to
something less frequent, perhaps every minute.

For demo purposes, I record timestamps for each update. You can have
multiple columns (nc) of timestamps; the last column is reused
continuously. I choose 3 columns so that we can see timestamps for
the first set of updates and the second set of updates, which the
third column changes with each subsequent update.

Would this be more resource intensive then waiting
for the whole sheet to recalculate?

Certainly not. However, it might be __intrusive__ for the Excel user.

While the update procedure ("doit") is running, the Excel user will be
blocked -- paused. In my experience, usually that is for less than
0.6 sec -- enough to be noticable, but not too much of an encumberance
if it happens infrequently.

However, I discovered that the Excel user is blocked for all of the
time that "doit" is waiting for an HTTP response. As I noted
previously, in my experience that can be 2-4 sec occassionally. And
of course, it can be a lot longer if the website is not responding.

I don't know if you can avoid that. It would be ideal if VBA provided
an asynchronous network API. There is WinSock -- the MSWin sockets
interface similar to Berkeley sockets. But I don't know how to call
those kernel functions from VBA. And I don't know if they are
asynchronous, like Berkeley sockets.

TMI?

-----

One additional comment about the OnTime frequencies -- t1 and t2.

I said they are 2 sec and 5 sec respectively. Really, they are 1-2
sec and 4-5 sec.

The reason is: the VBA Now function has a resolution of 1 sec, but
the process clock updates every 15.625 msec. So if we scheduled an
event for Now plus 1 sec just before the process clock tick that
increments Now, the event would occur almost immediately, not 1 sec
later.

For that reason, I suggest a __minimum__ frequency of 2 sec. And for
any frequency of N sec, the event might occur in as little as N-1 sec.

Not a big deal if you choose a frequency like every 1 min.

-----

The macros below demonstrate the essential mechanism for scheduling
the events with two different frequencies.

Note: The uploaded file "stock quote Ontime.xls" has more
functionality, incorporating your HTTP logic to acquire stock quotes
for a list of stock symbols. It should be a good starting point for
your purposes.

Private Const t1 As String = "00:00:02"
Private Const t2 As String = "00:00:05"
Private Const nr As Long = 5
Private t As String, r As Long, c As Long, f As Long

Sub startit()
t = t1: r = 0: c = 0: f = 0
Sheets("test").Range("a:z").Delete
Application.OnTime Now + TimeValue(t), "doit"
End Sub

Sub stopit()
f = 1
End Sub

Private Sub doit()
With Sheets("test").Range("a1").Offset(r, c)
.Value = Now
.NumberFormat = "hh:mm:ss"
.EntireColumn.AutoFit
End With
r = (r + 1) Mod nr
If r = 0 Then
If c = 0 Then t = t2
c = c + 1
End If
If f = 0 Then Application.OnTime Now + TimeValue(t), "doit"
End Sub