Weighted Moving Average

Weighted Moving Average WMA

Comes with formula, calculation steps and VBA code

The weighted moving average is calculated by giving each lagging price a smaller weight. The weight decreases at a constant rate from n to 0.

WMA = [n x Price_t + (n-1) x Price_t-1 + … + 2 x Price_t-n+2 + Price_t-n+1] / (n + (n-1) + …+ 2 + 1)

The divisor (n + (n-1) + …+ 2 + 1) can be calculated using the formula [n x (n+1)] / 2.

VBA Code

Two seperate methods are presented below.

Method A

To compute the weighted moving average of a series, enter into any cell "=WMA([n periods of your series])" after you have pasted the code below. The AddUDF sub routine adds your functions to a custom category called "Technical Indicators".

Sub AddUDF()

Application.MacroOptions macro:="WMA", _

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

"Select n periods of prices", _

Category:="Technical Indicators"

End Sub

Public Function WMA(close_)

total1 = 0

n = WorksheetFunction.Count(close_)

divisor = (n * (n + 1)) / 2

For a = 1 To n

total1 = total1 + close_(a, 1) * a

Next a

WMA = total1 / divisor

End Function

Method B

In general, Method B is preferred over Method Afor large datasets. It is much more faster.

Sub Runthis()

Dim close1 As Range, output as Range, n 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

WMA_1 close1, output, n

End Sub

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

For a = 1 To n

output(a, 1).Value = a

Next a

numrange = Range(output(1, 1), output(n, 1)).Address

pricerange = Range(close1(1, 1), close1(n, 1)).Address(False, False)

output(n, 2).Value = "=sumproduct(" & numrange & "," & pricerange & ")/(" & n & "*(" & n & "+1)/2)"

output(n, 2).Copy output.Offset(0, 1)

Range(output(1, 2), output(n - 1, 2)).Clear

End Sub