**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-α)^{2}EMA_{t-2}**EMA**

_{t}= α x Price_{t}+ α(1 – α)Price_{t-1}+α(1-α)^{2}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**

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)*
## 0 comments:

## Post a Comment