Hull Moving Average HMA
Comes with formula, calculation step and VBA code
The Hull moving average for n periods is calculated as such
HMA = WMA of (2 x WMA for last n/2 periods – WMA for last n periods) for last k periods
, where
- k =square root of n rounded to a whole number
- n/2 is also rounded to a whole number.
- and the WMA refers to a weighted moving average.
Both n is specified by the user. A single WMA will often lag behind current prices if n is large. On the other hand, too small a value of n will lead to an unsmooth moving average which might give false signals. On closer observation, the HMA consists of 3 steps to reduce lags and smooth the kinks in a typical moving average.
- Reduce the period by half using WMA for the last n/2 periods to reduce the lag effect.
- Make the HMA even more responsive by adding (WMA for last n/2 periods – WMA for last n periods) to the WMA for the last n/2 periods in step 1.
- Smooth the output of 2 by applying a WMA of square root n periods.
VBA Code
Method A
The Hull moving average is a series of nested weighted moving averages. Using the WMA custom function for calculating weighted moving averages, the Hull moving average can be calculated following the steps below without a custom function of its own.
- Calculate the n periodweighted moving average of a series "=WMA(price for n periods)"
- Calculate the n/2 period weighted moving average of a series"=WMA(price for n/2 periods)". Round n/2 to the nearest whole number
- Create a time series with 2*WMA from Step 2 - WMA from Step 1
- The HMA is the WMA of the series in Step 3. "=WMA(Step 3 outputs fo k period)"
Method B
Method B automates the entire process in Method A using sub routines. It produces in 4 columns the respective outputs from Step 1 to Step 4 of Method A.
Sub Runthis()
Dim close1 As Range, output As Range, n As Long
Dim k 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
HMA_1 close1, output, n, k
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
Sub HMA_1(close1 As Range, output As Range, n As Long, k As Long)
WMA_1 close1, output, n
Dim j As Long
j = WorksheetFunction.Round(n / 2, 0)
WMA_1 close1, output.Offset(0, 2), j
WMAn = output(n, 2).Address(False, False)
WMAj = output(n, 4).Address(False, False)
output(n, 5).Value = "=" & WMAn & "-" & WMAj
output(n, 5).Copy output.Offset(0, 4)
Range(output(1, 5), output(n - 1, 5)).Clear
WMA_1 output.Offset(0, 4), output.Offset(0, 5), k
End Sub
It looks like you have left out the '2*' from the vba statement above 'output(n, 5).Value = "=" & WMAn & "-" & WMAj'.
ReplyDeleteIt should read 'output(n, 5).Value = "=2*" & WMAn & "-" & WMAj'.
Your code has been of great assistance, thanks.
Good morning,
ReplyDeleteI have to say that your website is very useful. Congratulations!
I was looking for information about Hull Moving Average formulas to write a code in VBA (Excel) for
entry signals. After doing some googling I have found your code.
Apart for this I've found two more websites with EXcel formulas that builds the HMA formula.
From here: (I think they are reliable as yours)
1) http://www.financialwisdomforum.org/gummy-stuff/MA-stuff.htm (must download)
2) http://traders.com/Documentation/FEEDbk_docs/2010/12/TradingIndexesWithHullMA.xls
My purpose was to contrast the outputs of 3 diferent authors and then look for the same result.
I have to say that I've spent 3 full days learnig/fixing/interpreting and finally I've gave up today
because 3 of you get differents results. I'm quite lost.
I'm encourage to write you fist because I do prefer the VBA code.
I'm trying to buid a strategy that HMA (5) together with Heikin Ashi in a 120 min time frame. In your
code if n=5, Which should be k in your code? May be 2... (because the sqrt(5) is 2.33) or may be 3
because it´s rounded up to 3?
Please I will be happy and grateful If you could use for me your precious time to find me error.
I am looking forward to your answer.
Thank you,
Josu Izaguirre
josugst@gmail.com
N.B: I am not English, I'm from Basque Country and this may not be Perfect, but i hope it serves you.
This comment has been removed by the author.
ReplyDeleteThanks, i fixed some things and calculation is forking:
ReplyDeleteSub Runthis()
Dim close1 As Range, output As Range, n As Long
Dim k As Long
Set close1 = Range("E2:E55")
Set output = Range("H2:H55")
Range("H2:N99999").ClearContents
n = 21 'number of historical periods to look at
k = Int(Math.Sqr(n))
HMA_1 close1, output, n, k
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
Sub HMA_1(close1 As Range, output As Range, n As Long, k As Long)
WMA_1 close1, output, n
Dim j As Long
j = WorksheetFunction.Round(Int(n / 2), 0)
WMA_1 close1, output.Offset(0, 2), j
WMAn = output(n, 2).Address(False, False)
WMAj = output(n, 4).Address(False, False)
output(n, 5).Value = "=2*" & WMAj & "-" & WMAn
output(n, 5).Copy output.Offset(0, 4)
Range(output(1, 5), output(n - 1, 5)).Clear
WMA_1 output.Offset(0, 4), output.Offset(0, 5), k
Range("N2:N" & n + k - 1).Clear
End Sub