I have written a function to calculate an exponential moving average (EMA) on
some stock prices. The prices are arranged with the increasing down the
spreadsheet (so that the prior day's price is in the previous row).

I have compared the results to cells that contain the actual formula. The
problem is that it will calculate "incorrectly" unless I sequentially edit
each cell holding the function and hit enter, and then go to the cell below
(i.e. the more recent date EMA). I guess this is because a simple copy -
paste of the function downwards doesn't allow the sequential calculation of
the EMA.



The formula for the EMA(0) is:

(2 / ( 1 + nPeriods) * ( (Price(0) - EMA(-1) ) ) + EMA(-1)

Where Price(0) is today's closing price (in the same row as the function),
and EMA(-1) is yesterday's EMA (in the cell directly above the current
function cell). If this is the first EMA value, then the current day's price
is used.

My actual code is:

Function EMA(DRange As Range, nPeriods As Integer)

'DRange is the cell holding the current day's price

'Make sure that this is not the first data point

If DRange.Row Range("DtHead").Row + 1 Then

EMA = (2 / (1 + nPeriods) * (DRange.Offset(0, 0) -
ActiveCell.Offset(-1, 0))) + ActiveCell.Offset(-1, 0)

Else
EMA = DRange.Offset(0, 0)
End If

Application.Calculate

End Function

I have tried inserting
Application.Volatile
but then I get a wrong and constantly changing EMA.

I'm sure that this sort of slightly iterative function - which depends on
the results of the preceding cell's function results - is not uncommon.

Is there a way to have this sort of function work so that I can simply copy
and paste. If not (sigh!), I guess I can just sequentially calculate the EMA
in a sub and then insert the value into each cell.

Any thoughts would be greatly appreciated.

A second question:

This function is part of a backtesting program that I am building. As it's
getting quite large, it's no longer as quick as it once was. To improve
performance, I'm trying to code repetitive calculations - but I'm guessing
which types of calculations will give you the most bang for the buck.

For example, I think (but could be wrong) that IF functions on the
spreadsheet consume more CPU / memory than straight mathematical operators
(such as Max or Min). As my spreadsheet has nearly 200 columns and 2500
rows, I figure that coding away the IF functions - either through VBA or
arithmetic operators - will improve the speed significantly. Though it's
nowhere near as straight forward (or as easy to find errors), I've found you
can do alot with max, min and sign functions.

Does anyone have any suggestions about any of these issues?

Once again, hats off for the advice.