Finance
Calculate a Complete Four-Period Hull Moving Average and Efficiency Ratio
Calculate two consecutive raw Hull values and their final weighted smoothing, then compare net change with total absolute movement using Kaufman’s efficiency ratio.
The raw Hull step is not the finished HMA
A Hull moving average combines a short and a long weighted average, then smooths the resulting series. It reduces lag; it does not promise zero lag or predict the next observation. Here the period is fixed at four, so both the half-period and square-root smoothing length are exactly two.
Enter five chronological observations in B2:B6, oldest first. This supplies two overlapping four-observation windows. Weights increase toward the newest value. Keep intermediate results unrounded; round only the displayed final value.
Calculate the first raw value in D5
The first window uses B2:B5. Twice the latest two-point WMA minus the four-point WMA gives approximately 109.2667 for 100,104,102,110.
=IF(COUNT(B2:B5)<>4,"Enter numeric observations",2*(B4+2*B5)/3-(B2+2*B3+3*B4+4*B5)/10)Calculate the next raw value in D6
The next overlapping window uses B3:B6. With a fifth observation of 112, its raw value is approximately 114.0667.
=IF(COUNT(B3:B6)<>4,"Enter numeric observations",2*(B5+2*B6)/3-(B3+2*B4+3*B5+4*B6)/10)Complete the HMA in E6
Apply a two-point WMA to D5 and D6, giving twice the weight to the newer result. The completed HMA is approximately 112.4667; the raw intermediate value is not interchangeable with it. These formulas assume D5 and D6 retain the preceding formulas.
=IF(COUNT(B2:B6)<>5,"Enter numeric observations",(D5+2*D6)/3)Measure efficiency over three changes in F5
Four observations in B2:B5 provide three consecutive changes. Divide the absolute endpoint change by the sum of absolute individual changes. This is a three-change efficiency window, not a four-change window.
For the first four sample values the ratio is 10/14, approximately 0.7143. A monotonic rise or fall gives 1; a non-flat round trip gives 0. This example also returns 0 for a completely flat series by explicit convention, since its mathematical ratio is 0/0.
The ratio discards direction and does not provide a trading recommendation. It is only one ingredient of KAMA; this example does not calculate the complete adaptive moving average.
=IF(COUNT(B2:B5)<>4,"Enter numeric observations",IF(ABS(B3-B2)+ABS(B4-B3)+ABS(B5-B4)=0,0,ABS(B5-B2)/(ABS(B3-B2)+ABS(B4-B3)+ABS(B5-B4))))Try the example
Use the original values or change them to test your understanding.