How to record the amount a cell consecutively rises or falls.

**holmwood** · 11-20-2009, 07:34 AM

I cannot work this out!

I have live Dynamic Data of share prices feeding into Excel.

Imagine cell A1 contains one share price. The cell is constantly changing; rising and falling throughout the day.

What I can't work out (and what I would greatly appreciate you help with) is how to determine the maximum size of a consecutive (uninterrupted) fall.

So for example say the price changed as follows: 100, 101, 102, 103, 102, 101, 100, 99, 98, 97, 96, 97, 98, 99, 100.

I would like to detect the uninterrupted drop between 103-96 and show in another cell, the size of the continuous fall which in this case is: 6.8% (103-96)/103).

Then to keep that 6.8% figure displayed until it is replaced by a larger figure.

Any ideas how this could be done??

Many thanks.

**Cheeky Charlie** · 11-20-2009, 07:48 AM

The only practical way is to append new information, rather than overwrite - the numbers have to be stored somewhere for the maths to be done on them

How is the update achieved?

**DonkeyOte** · 11-20-2009, 08:06 AM

Holmwood, I would suggest revisiting your earlier thread re: capturing the DDE update and tracking changes ... specifically the use of Static variables in the Calculate event.

**WHER** · 11-20-2009, 09:35 AM

Would this be a possibility? It's based on a macro by J. Beaucaire, maybe from the earlier thread Don refers to, i'm not sure.
Change the (price) value in A1 to simulate a live feed.

**holmwood** · 11-20-2009, 11:27 AM

Don Hi:

I have never used VB basic, so when you said "..the use of Static variables in the Calculate event." means not a lot to me. The limit of my abilities was to copy your code and change the cell references. I am happy to learn if you point me in the direction of what I need to learn. The last time I programmed was basic Basic on my Commodore Pet in the early eighties!

I was hoping for a formula, as I have 369 different DDE price feeds to monitor. You used 'prior' in your earlier code, (which incidentally is working well. Thank you.) this infers the previous value, I would need to capture many prior values, until the price stopped falling and then do the same for all 369 variables.

I happily learn what I need to know, if you think the right code will solve the problem.

Charlie:

You said .."the numbers have to be stored somewhere for the maths to be done on them"

This is the problem. With my knowledge, I can't figure out how to capture maybe up to 50+ prior events for each of 369 DDE price variables. The update is achieved via the data stream provider's desktop authentication program. Once started the prices start changing using cell formulas like =ADVFN|LSE_BARC!CHANGE_PC which would stream the %age price change on the day for Barclay's Bank.

Wher:

"Could I use the earlier code?"

Don infers Yes, but with 369 varibles and mabe 50+ prior events to capture, I don't know where to begin to modify Don's previous code.

**Cheeky Charlie** · 11-20-2009, 02:04 PM

Hmmm... the previous thread was an interesting read and I've been pondering; I have an idea, but I'm concerned about speed, you're saying you need 369 variables*50 previous values - this can be done (anything's possible) but depending on the update frequency, you may struggle to capture all the changes, and apply the appropriate calculations, in time... so, what's the update frequency?

**holmwood** · 11-20-2009, 09:42 PM

Charlie, you say: ...what's the update frequency?

It depends on the volume of shares traded per stock, per day. With some heavily traded stocks, their price may change several times a minute. Most would see a price change every few minutes, and some (with very low daily volumes traded) may see their price change only a few times a day.

Does this answer your question?