Updating High/Low Values from a set of streaming data
 

Hi,

I have a worksheet in which column A contains a list of stock /
(share) codes.
Column B contains formulae which refer to an external feed of
streaming share prices. In case it's of importance a typical formula
in column B looks like this:

=TRADER|LSE\EQUITY!ALLD\TRADE

This changes in line with the underlying streaming data.

I'd like to include two extra columns, C&D, which record the Maximum
and Minimum Values from the constantly changing data in column B.

I've tried using a couple of working columns (E & F) which contain
formulae like

E1: =IF($B1C1,$B1,C1)
F1: =IF($B1<D1,$B1,D1)

and a Workbook Sheet Change event (with the target as EXCEL.Range),
which copies the values of columns E & D into columns B&C. So that
this event is triggered every time a price changes.

However when testing this with the streaming data turned off, and
manually changing the values in column B, the Sheet Change event seems
to go off into some sort of loop and spends a few seconds calculating
away before finishing with the correct data.

However, with the streaming data turned on, it seems to have the right
values in the working (formulae) columns, but cols C&D seem to display
the same value.

This is the first time I've tried to get a workbook to re-act in this
way, and I suspect I am missing some trick or another.

Can anyone offer any advice / solutions please.

For instance:

1. When manually testing this, why does it appear to loop around for a
few seconds before finishing. (I say loop although there is of course
no explicit formal loops built into the sheet change event)?

2. Any ideas why with the streaming data switched on I'm not getting
the right answer?

Usual TIA.

Rgds

Richard Buttrey