Exponential Moving Average

Exponential Moving Average EMA

Comes with formula, calcuation steps and VBA code

Introduction

The exponential moving average, EMA is calculated as follows.

EMA_t = α x Price + (1 – α) x EMA_t-1

,where α = 2 / (1 + n)

The subscript t is used to denote time e.g. t-1 refers to the period before t and n, to be specified by the user, refers to the "averging period" of the EMA. For example, the EMA equivalent of a 3 period simple moving average has n of 3. The larger the value of n, the smaller α becomes. This results in a larger (1-α) and the more of EMA_t-1 is retained in EMA_t.

The very first value of EMA in a time series may be assumed to be a simple moving average of n days’ of prices. Some users may also prefer to start the very first value of the EMA from the second period onwards where EMA on Period 2 = α x Period 2 Price + (1 – α ) x Period 1 Price.

Users should understand that the exponential moving average is actually an infinite series expansion where the earlier prices have an increasingly smaller weight on EMA_t. Consider the following:

EMA_t = α x Price_t + (1 – α) x EMA_t-1

EMA_t-1 = α x Price_t-1 + (1 – α) x EMA_t-2

Therefore,

EMA_t = α x Price_t + (1 – α) x (α x Price_t-1 + (1 – α) x EMA_t-2)

EMA_t = α x Price_t + α(1 – α)Price_t-1+(1-α)²EMA_t-2

EMA_t = α x Price_t + α(1 – α)Price_t-1+α(1-α)²Price_t-3+...

This results in the EMA being more responsive and less volatile than its simple moving average equivalent. A more detailed discussion of this can be found in my article about filters in finance and technical analysis.

VBA Code

Method A uses functions, while Method B uses sub procedures to calculate CMF. Method B is faster and more flexible.

Method A

'Paste this code into your ThisWorkBook code window in VBA. Right Click This WorkBook in Project Explorer and click View Code.

Private Sub Workbook_Open()

AddUDF

End Sub

'The rest belong to any module

Sub AddUDF()

'Tells Excel to includes these in list of functions, add descriptions to them and create a new category called Technical Indicators.

Application.MacroOptions macro:="EMA", _

Description:="Returns the Exponential Moving Average." & Chr(10) & Chr(10) & _

"Select last period's EMA or last period's price if current period is the first." & Chr(10) & Chr(10) & _

"Followed by current price and n." & Chr(10) & Chr(10) & Chr(10) & _

"The decay factor of the exponential moving average is calculated as alpha=2/(n+1)", _

Category:="Technical Indicators"

End Sub

Public Function EMA(EMAYesterday, price, n)

alpha = 2 / (n + 1)

EMA = alpha * price + (1 - alpha) * EMAYesterday

End Function

Once you are done with the above, you can compute exponential moving average by typing into any cell "=EMA([Last Period EMA],[Current Price],[n])". Enter last period price as last period EMA if you are computing the first EMA of your dataset.

Method B

To run Method B, you have to copy the Runthis sub from the page on Accumulation/Distribution Line into your module. You also must run EMA from the Runthis sub.

'Add the following line to the sub Runthis

'Place it right before End Sub

'And disable all other macros that Runthis will call

EMA close1, output, n

'Insert the following sub

'This sub will start calculating EMA from t=2 onwards

Sub EMA(close1 As Range, output As Range, n As Long)

output(0, 1).Value = "EMA"

close0 = close1(1, 1).Address(False, False)

close1a = close1(2, 1).Address(False, False)

output1 = output(1, 1).Address(False, False)

alpha = 2 / (n + 1)

output(2, 1).Value = "=2/(1+" & n & ")*" & close1a & "+(1-2/(1+" & n & "))*" & output1

output(2, 1).Copy output

output(2, 1).Value = "=2/(1+" & n & ")*" & close1a & "+(1-2/(1+" & n & "))*" & close0

End Sub

Other references

http://en.wikipedia.org/wiki/Moving_average

Like what you have just read? Digg it or Tip'd it.

The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)