Function dependent on prior cell value

[Post Tenebras Lux]

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.

 

[NickHK]

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]

Thanks. Solved the problem and point taken.

Is it possible to create a recursive function? In this case, I would
replace the argPreviousPrice, argPreviousEMA and argCurrentPrice with just
the whole Price range up to the current row (putting aside the offset issue).
The recursive function would call the Ubound(Pricerange) - 1 element (or
something like that).

I tried to implement it, and received #Value error. I don't know if that
would be faster, slower, or not possible. Thanks.

 

[NickHK]

You can certainly make the function recursive, but you need some
limit/parameter/iteration to enforce some halt in calculation.
I don't know these financial function, so I not 100% on what you need, but
recursion may not be needed. This is not your correct, but it performs a
calculation on a progressively wider range of prices from 1 to All:

Public Function EMA(argPriceRange As Variant) As Single
Dim TempAverage As Single
Dim i As Long

For i = 1 To argPriceRange.Rows.Count
'Whatever calculation you need on the range of prices
TempAverage = TempAverage +
Application.WorksheetFunction.Average(argPriceRange.Resize(i, 1))
Next
EMA = TempAverage
End Function

NickHK

 

[Post Tenebras Lux]

Really appreciated. It may be that VBA doesn't do recursion well. I'll play
around with it, but I suspect that doing the calcs in VBA arrays, and then
outputing values may be the way to go. I've been avoiding this, as I'm
building an optimization / sensitivity model, and "live", rather than running
a sub every time a factor changes might have been preferable.

However, for every problem, there are at least 10 solutions...

 

[NickHK]

Recursion is useful when you have no idea how times you need to call a
routine, as in a directory/file search.
However in your case I suspect you are calculating some kind of running
average of a range of cells. If so, you know how many times you need to run
the loop (the number of rows in the range) and you can .Resize your range
for the calculation each time.
As I said, these financial functions are new to me, so the actual
implantation will be up to you.

NickHK