Moving Average Convergence/Divergence

Moving Average Convergence/Divergence MACD

Comes with formula, calculation steps and VBA Code

The MACD is based on the difference of two exponential moving averages. Convergence signifies the end of a trend, while divergence means that a trend is forming. To compute the MACD, users need to specify

a) Fast, the number of periods the slow moving average is based on,

b) Slow, the number of periods the slow moving average is based on and

c) n, the number of periods MACD is smoothed against.

Step 1: Compute the fast and slow exponential moving averages, EMAs.

Fast EMA_t = [(2 / (1+Fast)] x Price + [1 – (2/(1+Slow))] x Yesterday’s Fast EMA_t-1

Slow EMA_t = [(2 / (1+Fast)] x Price + [1 – (2/(1+Slow))] x Slow EMA_t-1

Step 2: Calculate MACD, Average MACD, Difference

MACD = Fast EMA – Slow EMA

Average MACD = (2 / (1 + n)) x MACD + [1 – (2 / (1+n))] x Yesterday’s MACD

Difference = MACD – Average MACD

The picture below shows you how the MACD, Average MACD and Difference are plotted on a price chart.

VBA Code

The MACD can be calculated in Excel using user defined functions as in Method A or via sub routines in Method B.

Method A

The MACD custom function provided below is an array function that returns 3 values - the MACD, average MACD and the difference between the two outputs. The inputs are the current period's fast and slow exponential moving averages, the prior period MACD and n the smoothing period of the average MACD. To calculate MACD for the current period, input in any cell, "=MACD([fast EMA], [slow EMA], [previous MACD], [n])". Select the cell and the 2 cells next to it and press F2 followed by Crtl+Shift+Enter.

'This sub adds the MACD function to a list of functions called Technical Indicators

Sub AddUDF()

Application.MacroOptions macro:="MACD", _

Description:="Returns Moving Average Convergence/Divergence, Average MACD and Difference" & Chr(10) & Chr(10) & _

"Select current fast EMA, slow EMA, previous MACD and n, the smoothing period of the average MACD", _

Category:="Technical Indicators"

End Sub

Public Function MACD(fastEMA, slowEMA, MACDYesterday, n)

Dim result(0, 1 To 3)

result(0, 1) = fastEMA - slowEMA

alpha = 2 / (1 + n)

result(0, 2) = alpha * result(0, 1) + (1 - alpha) * MACDYesterday

result(0, 3) = result(0, 1) - result(0, 2)

MACD = result

End Function

Method B

Method B does not require you to compute the exponential moving average in advance. You need only input historical prices, where you want the output to be located, n the smoothing period of the fast EMA, k the smoothing period of the slow EMA and j the smoothing period of the MACD. I typically write a sub called MACD_1 and call this sub from a major sub with all the variables.

Sub Runthis()

Dim close1 As Range, output As Range, n As Long

Dim k As Long, j As Long

Set close1 = Range("E2:E11955")

Set output = Range("H2:H11955")

n = 5 'number of historical periods to look at

'needed for certain indicators

k = 10 'number of historical periods to look at

'needed for certain indicators as slow average

j = 5 'number of historical periods to look at

'to smooth the MACD

MACD_1 close1, output, n, k, j

End Sub

Sub MACD_1(close1 As Range, output As Range, n As Long, k As Long, j As Long)

EMA_1 close1, output, n

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

EMA_1 close1, output.Offset(0, 1), k

output(0, 2).Value = "SlowEMA"

output(0, 3).Value = "MACD"

EMAF = output(2, 1).Address(False, False)

EMAS = output(2, 2).Address(False, False)

output(2, 3).Value = "=" & EMAF & "-" & EMAS

output(2, 3).Copy output.Offset(0, 2)

output(0, 4).Value = "Avg MACD"

DIFFL = output(2, 3).Address(False, False)

DIFFN = output(3, 3).Address(False, False)

DIFFC = output(4, 3).Address(False, False)

MACDL = output(3, 4).Address(False, False)

output(4, 4).Value = "=(2/(1+" & j & "))*" & DIFFC & "+(1-(2/(1+" & j & ")))*" & MACDL

output(4, 4).Copy output.Offset(0, 3)

output(3, 4).Value = "=(2/(1+" & j & "))*" & DIFFN & "+(1-(2/(1+" & j & ")))*" & DIFFL

output(0, 5).Value = "Difference"

MACDA = output(3, 4).Address(False, False)

MACDN = output(3, 3).Address(False, False)

output(3, 5).Value = "=" & MACDN & "-" & MACDA

output(3, 5).Copy output.Offset(0, 4)

Range(output(1, 3), output(1, 5)).Clear

Range(output(2, 4), output(2, 5)).Clear

End Sub

The code above is not optimal. There are repetitive sections that can be removed. Nonetheless, it serves as an excellent point for further refinement.

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)