Kamburov:
yeah you can do that. I do exactly what you are describing in keeping track of VWAP in excel. This means I have cells (Price and size) that constantly update in real-time through a dde link and keep a cumulative total. As an example, let's say you want to keep a running total of volume.
First, you need to be able to recognize when the field has been updated. For this I use the trade time as the trigger - if you used the volume field itself, 2 consecutive trades of 100 shares would not register as a difference.
To do this, assume the trade time is updating in cell A1.
In cell A2: If(Reset=0,0,if(A1<>B2,2,0)
The reset referred to is a named cell that is set to either 0 or 1 manually. If it's set to zero, this stops excel looping.
The cell B1 is where the previous value of A1 is held.
So, if the value in A1 has changed, A2 will equal 2, otherwise 0.
In cell A3: If(reset=0,0,min(A2,A3+1)
This is a circular reference so excel will give you dire warnings about this. Ignore them. Ensure that iteration is enabled under Tools, Options, Calculation otherwise it won't work. If you are doing this real time, set iterations to a low number (I use 5) instead of 100 to cut down on possible loops.
This will set the value of A3 to the lower of A2 (which will be either 0 or 2) and thecurrent value in A3 + 1. If the current value is 0, it will change to 1; if 1 to 2. It will change back to 0 when the value in a2 reverts to 0.
This basically has set up a loop based on a change in the value of cell A1.
Next, we use the changing values of A3 to copy the new value in A1.
So, in B1: if(reset=0,0,if(A3=1,A1,B2))
When the value in A1 has changed, the loop will trigger and A3 will be set to 1. When this is so, copy the value in A1 to B1, otherwise leave it as it is. Again, this is a circulkar reference.
In B2, we will terminate the loop:
If(A3=2,B1,B2)
This copies the value from B1 into B2 when the loop in A3 increments from 1 to 2. By copying the new value of A1 into B1 and then into B2, now the loop is terminated (see the comparison in A2 between A1 and B2).
Now, using this looping structure, you can track changes in a real-time dde link and can do whatever you like. Like I say, I track VWAP for example. There is a caveat though: when you enter stuff in excel manually, it stops updating the dde link while you are entering data, so don't do it or you will miss ticks. Use another pc with excel to actually enter stuff while the dde link is active.
BTW, I am not the genius who thought this method up. I got it from a MrEXcel.com link to a guy called Stephen Bullen in Uk who had some example spreadsheets which i can't find anymore on my pc.
