## Friday, February 13, 2009

### Solving the EMA Backtest Dilemma

Warning: This post is directed towards newbie system developers, backtesters and data snoopers only!

Over the years, I have found that many of my mechanical systems are improved by applying exponential moving averages (EMAs) to either the raw time series, or, in other cases, actual signal lines. This has the effect of smoothing the data, but in a time-responsive manner. Here is the simple math over at Decision Point. Wonderful -- you say -- so what's the dilemma!

The problem with EMAs and many similar smoothing mechanisms, is that they contain an eternal memory going back to the start of your data series. If you are a system developer, this has the disadvantage of occasionally altering your signals depending on the date you start your program as earlier periods are dropped out of the analysis window. Unless you run your model against an ever growing database, this will eventually have the effect of generating "oops" moments when historical "on the margin" signals become unstable as the EMAs themselves subtly shift on each new run. This is ruinous for the careful backtester and will make even well conceived systems appear arbitrary.

One simple solution? Calculate the EMA against a set rolling window with the first point always calculated against a similarly running simple moving average (i.e. not just the first period in your full data series). As long as you have a fixed "starter" series of data (say 20 or 40 periods), your calculations and signals will remain stable while retaining the benefits espoused above. But wait -- the astute programmer says -- that will require a special function or array! Hey, I didn't say it would be that easy!

If you want something simpler still, you could always try something like this: (Price[0] + Price[1]*2 + Price[2]*2 + Price[4])/6. But... since I've brought it up and gone through the trouble of writing this post, I'll go the extra mile and post an Excel VBA Function into the comments section below later this long weekend for those who are interested.

* * VBA Function Code Posted to Comments * *

Jeff Pietsch CFA Esq said...

Hopefully this clarifys what I wrote above.

Function EMA(Cell As Range, Length As Double, Optional Window As Double, Optional Direction As Integer)

'Window EMA by marketrewind.blogspot.com
'Length = EMA Lookback Period
'Window = Rolling Window
'Direction: 0 = Bottom to Top; 1 = Top to Bottom

If Direction <> 1 Then Direction = 0
If Window = 0 Then Window = Length
y = Cell.Row
x = Cell.Column
Alpha = 2 / (Length + 1)

If Direction = 0 Then

yy = y + Window - 1

EMA = WorksheetFunction.Average(Range(Cells(y + Window, x), Cells(y + Window + Length - 1, x)))

For i = yy To y Step -1
EMA = (Cells(i, x).Value * Alpha) + (EMA * (1 - Alpha))
Next i

Else:

yy = y - Window + 1

EMA = WorksheetFunction.Average(Range(Cells(y - Window, x), Cells(y - Window - Length + 1, x)))

For i = yy To y
EMA = (Cells(i, x).Value * Alpha) + (EMA * (1 - Alpha))
Next i

End If

End Function

Michael S said...

Wow...I didn't know you were such a geek Jeff. I likey. Good stuff sir. michael