Refreshable Stock Watcher

[Iker]

Environment: Excel 2003:

I am attempting to make an automatically updating (timed) stock tracking
worksheet, but have had alot of issues coming up with a clean solution.

1. Refreshable Web query link - works, but really cumbersome and flakey. Not
suited to the job.
a) Does not always find data to return
b) Started out retrieving the correct data frames from the Web links, now
it is necessary to select 2 frames back to get the right data frame from the
web page
c) Some links never refresh. The refresh time is set at 5 minutes for all
links.
d) The granularity of what can be returned is too large to make nice
clean tables

2. Attempted RTD Server.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlrtdbuild.asp

Added this function: =RTD("RTDTime.RTD",,"Now")

This creates a nice clock in a cell if it is formatted as a time format.

a) This does not trigger a sheet change event, so accomplishes little.
b) Excel complains that it cannot save this function correctly, on exit,
and restore it, even though it does.

3. Added CCRP Timer: http://ccrp.mvps.org/index.html?controls/ccrptimer6.htm

and the following VB code:

Private WithEvents Timer1 As ccrpTimer

Private Sub Timer1_Timer(ByVal Milliseconds As Long)
MsgBox ("Timer event")
End Sub

'*******************************************************
Private Sub Worksheet_Change(ByVal Target As Range)
'This function is called whenever a cell is changed on the
'current sheeet. The code detectes if it is in the range that
'is necessary to analyze (warning - hardcoded addresses), then
'proceeds to act upon trigger thresholds in the changed cells.
'
'The end resultant is a Msgbox showing the timer event trigger
'*******************************************************
Static TimerStarted As Boolean

If (TimerStarted = False) Then
TimerStarted = True
Set Timer1 = New ccrpTimer
Timer1.Enabled = True
Timer1.Interval = 300000 '5 minutes
End If

With Target
If .Address = "$A$2" Then
MsgBox ("Timer Cell detected in search range ... processing")
End If
End With
End Sub
'*******************************************************

a) This provides sheet change event triggers on a timed interval but
requires the user change an entry to get it running (instead of starting
automatically).

'The user entry needed, at $A$2, is probably solveable with:
Private Sub Workbook_Open()
...
End Sub
' Note: Sometimes something does trigger the timer startup anyway

'Sheet trigger
Private Sub Worksheet_Change(ByVal Target As Range)
'Go get stock quotes
...
End Sub


b) Requires a function to call to get the data, which led to "4" below:

(Other timer alternative exist like:
http://www.enhanceddatasystems.com/ED/Pages/ExcelTimer.htm, but still require
a function to go get the data)

4. Tried the Excel 2003/2002 MSN Money Stock Quotes Add-in:

http://www.microsoft.com/downloads/...D8-9305-4535-B939-3BF0A740A9B1&displaylang=en

a) No Auto-refresh, like the Refreshable Web Query function (this would
be a killer feature - 5 mins. would be great - project done)

b) The functions to call the individual Properties,
MSNStockQuote(Symbol,Property,CountryCode), cannot be auto-updated as a cell
will not recalculate unless a cell is updated. There is something else going
on here that I cannot quite pin down. If the "Update Quotes" button is
pushed, it greys out for the 5 minute interval, but new quotes can be added
anyway.

Obviously this is the preferred solution and seems like adding the automatic
timer from the refreshable web link is the answer. Any chance of getting this
implemented?

TIA

 

[Don Guillett]

try going here

http://finance.yahoo.com/q?s=ibm

right click in the body of the site>download to excel>then you can edit the
query to refresh as desired.
If you need custom work, let me know.

 

[Iker]

Hello Don,

Thank you kindly for the suggestion. This is the "Refreshable Web Link" I
have referred to. The table it imports is editable, but does not stay that
way after a refresh. It returns to the original table import, which would
create a mess if you were making a small compact table.

In my case, something else has happend in that the refresh table selected
has usually moved indicies by -2 (but not always); thus giving me no
confidence in what it will return (sometimes a mess), and will usually hiccup
in a set of several of these embedded into a sheet.

It is also "alot" of work to manage more than a few of these (due to table
size returned) as they are used in another worksheet for other calculations.

The "Excel 2003/2002 MSN Money Stock Quotes Add-in" is vastly superior and
just what is needed, with an automatic refresh feature.

Thanks again.

 

[Don Guillett]

Pls send me a copy of that addin so I can take a look. Address below.

 

[Iker]

Don,

The address is already listed in "4. Tried the Excel 2003/2002 MSN Money
Stock Quotes Add-in" below

 

[Don Guillett]

Actually, I found I already had it on my computer but hadn't used it due to
limitations. Prefer to design my own.