Calculating Average ...of a dynamic data.

**Voltron** · 07-29-2009, 03:59 AM

Please help !

I'm interested in calculating the moving average of a stock price intraday using the "last price" data .

Scenario : I have a cell in excel that displays the "last trade price" as it occurs throughout the trading day ie its dynamic and linked to a data source.

Solution : I need two cells to collect the data required for the moving average . Cell 1 : I need it to calculate the running total or sum of the last prices traded as it occurs. Cell 2 : I need this to keep a running count everytime the last price changes.

Question : Need assistance with the necessary forumla for both cells 1 & 2

Many thanks ....

**DonkeyOte** · 07-29-2009, 04:29 AM

Realistically you would want to use VBA for this ... formula wise you would need to enable Iteration (to permit circular references) and these are Volatile, ie will recalculate whenever excel recalculates be it the result of the value updating or something else so this method is open to error.

In VBA terms, if we assume the cell being altered is A1, the running total to be stored in B1, the count of alterations in C1 then the below VBA might work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("B1").Value = Range("B1").Value + Val(Target)
Range("C1").Value = Range("C1").Value + 1
End Sub

To insert the above, right click on the Tab against which the code is to be applied - select View Code and paste above into resulting window, thereafter ensure Macros are Enabled.

Your average calc is thus B1/C1

**Voltron** · 07-30-2009, 05:52 AM

Hi again ....

Thank you very much , i've finally managed to copy and paste the code correctly however , it only works manually ie i have to manually change the data in A1 using my keyboard.

I tried making cell A1 display the "last traded price" automatically by linking it to another worksheet however B1 and C1 stopped summing up and counting respectively. Is there anyway round this ?

Once again thank you for your assistance so far

**DonkeyOte** · 07-30-2009, 06:59 AM

How is A1 updated and how quickly is it altering ... you may have issues keeping track if the changes are too frequent etc... most likely you will need to revert to a Worksheet_Calculate event (rather than Change event)

I am unlikely to be online over the next few days but I will endeavour to resolve...

**Voltron** · 07-30-2009, 05:27 PM

Hi again ...

A1 is updated from reuters ... there is a DDE (dynamic data exchange) connected to my excel spreadsheet . It updates many times per minute . Basically its prices of stocks being traded live on the stock exchange.

Thanks of your assistance ...

**DonkeyOte** · 07-30-2009, 05:36 PM

Hmm... I confess I have zero experience using DDE's but you would need to adopt a different event as already outlined given DDE udpates do not invoke a Change event, rather you need to use the Calculate event, however, the problem here is that technically you're open to error if the sheet recalculates on the basis of some action other than DDE update having taken place... if this is a reality we should discuss further, if not, that is to say only DDE's will be recalculating then perhaps try:

Private Sub Worksheet_Calculate()
Range("B1").Value = Range("B1").Value + Val(Target)
Range("C1").Value = Range("C1").Value + 1
End Sub

That said, given the above to all intents & purposes becomes a Volatile Event you could just as easily dispense with VBA, enable Iteration with Max Iteration of 1 (to permit circular references) and enter formulae instead:

B1: =B1+A1
C1: =C1+1