DDE function

[Thomas]

I get a live feed for the rates of different currencies.
Column 1: USD/CAD, column 2: 1.3310, column 3: exact
time/date. It updates every few seconds.

I need to record the close price of the day (as a static
number) at 17:00 hours in a fresh column. The market is
open 24 hours and feeds continuously, and 17:00 is just
an entry point of time.

Then after that, based on the static close price, I need
two more columns; one that show the continuously updated
highest price over the next 24 hours, and the other
column showing the lowest price. The calculation would
be live price minus static close price (17:00 hours.) If
a positive number results it would update the high price
column if the existing number is lower. If a negative
number results it would update the low price column if a
higher number is present. At 17:00 the high low columns
would naturally go to zero, when the static close price
is updated.

Can I get some help with this? Much appreciated!

Thomas

 

[Tom Ogilvy]

You can use intentional circular references.
You could go into Tools=>Options=>Calculation tab

check iterations to turn off circular reference checking and set max
iterations to 1

Then you could build formulas such as

column 4 (d1 in the example)
=If(timevalue(c1)=TimeValue("17:00"),B1,d1)

You could build similar self-referencing formulas for the min and max.