You need to pass in the values required to calculate your EMA. It is not a
good idea to use .Offset within your function because then you cannot change
the structure of the WS.
So the signature for the function should be something like:
Public Function EMA(argPeriods As Integer, _
argPreviousPrice As Single, _
Optional argPreviousEMA As Variant, _
Optional argCurrentPrice As Variant) _
As Variant
Dim CalVal As Single

'Must have either argPreviousEMA or argCurrentPrice
If IsMissing(argPreviousEMA) And IsMissing(argCurrentPrice) Then
'Not possible to calculate, so error out
EMA = CVErr(xlErrNA)
Exit Function
End If

If IsMissing(argPreviousEMA) Then
'Use Current Price
CalVal = argCurrentPrice
Else
'Use Previous EMA
CalVal = argPreviousEMA
End If

EMA = 2 / (1 + argPeriods) * (argPreviousPrice - CalVal) + CalVal

End Function

NickHK

"Post Tenebras Lux" wrote in
message ...
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.