help on excel capturing Hi & lo

Hello all,
I need some help on excel as I am able to DDE future prices to excel.

So my intention is to allow me to capture the the Hi & Lo for each 30 mins.

I am having problems on circular reference in Excel.

what i want the excel is to check for me is the lastest future prices against the last recorded hi or lo within the 30 mins time frame.

attached is the simple excel.

thanks in advance for any help.

Ben

 

VBA.

 

Does the last hi always equals hi? If so, I might have a solution, otherwise it is not possible due to circular reference.

Not sure even if VBA will be able to help.

 

what I need is for the excel to compare the market price against last hi or lo and store in any cells.

as for input of high & low, right now I am sitting in front of the screen to manually input the last high or low for the 30min time frame.

my problems is when I need to go away for about half an hour or so, I couldn't capture the hi or lo (for the last 30mins) to do my market profile.

thanks.

 

It can definitely be done with a bit of VBA. Unfortunately I am too busy at the moment to write it for you but this is what you need to do:

You will need to keep the last checked time in a cell somewhere.

In the Worksheet_Change event for Sheet1, put some code that does the following:

Test the cell reference that has changed. If it is not the cell receiving the realtime prices, exit.

Now check the current time with the last checked time. If we have gone over the half hour point, copy the current high low values to the previous values. Then set the current values to the current price.

If we have not gone over the half hour, check if the current price is higher than the current high - if it is, replace the current high with the current price. Do similar test for the low.

Finally, set the last checked time to now.

I appreciate this may be of no use to you if you do not know VBA, but at least you have the design done, which is a step in the right direction!

 

Might be doable without vba

In TOOLS OPTIONS CALCULATIONS try setting it to "Iterations" and setting it to 1.

Then jsut set up two cells. One cell is price and the second cell set.

=if('price cell'>'own cell', 'price cell', 'own cell')

Might work

By setting the max iterations to 1 it eliminates the problem with circular reference.

 

Sorry didn't see your example, try changing that seeting and it should work.

 

Hi Minmike,
It works! & it's what I am looking for.
simple & sweet.

thanks to all the guys who responded.