Re: Refreshable Stock Watcher

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Don Guillett (donaldb_at_281.com)
Date: 12/24/04 

Date: Fri, 24 Dec 2004 08:49:18 -0600

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. 

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Iker" <Iker@newsgroup.nospam> wrote in message
news:FB4DE3F5-60D0-4310-BFF5-750936ADD659@microsoft.com...
> Environment: Excel 2003:
>
> I am attempting to make an automatically updating (timed) stock tracking
> work***, 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 *** 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 *** 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
>
>       '*** 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/details.aspx?FamilyID=485FCCD8-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
>
Quantcast