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

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)