Weighted & Hull Moving Average

[rush1884]

I am desperately looking for some help in calculating 2000, 500, and 100 period hull moving average on a big dataset. I understand to do this I would need to first calculate the weighted moving average of each of these periods also (but I am unsure how to do this without having to type in an extremely long formula (especially for the 2000 period weighted average as every data point would have a different moving average).

The dataset is the Dow Jones Industrial Average Daily Price from 1900 to present. Therefore a 2000 weighted and hull moving average would start on the 2000th trading day in the data and any formula would easily be able to be dragged down to the rest of the data set.

Any help would be great, I was looking to attach the dataset and columns I am trying to work out to the post but it appears that is not allowed.

Thank you.

FYI I have no experience of using code but if this is the most feasible option please let me know.

 

[michaelsmith559]

Here is one way to do a moving average. Look at how I setup sheet1. You can change the value in b2 to whatever you need.

Excel 2012
	A	B	C
1	Value	Period	MovAvg
2	0.81	2	#N/A
3	0.74		0.775
4	0.76		0.75
5	0.87		0.815
6	0.8		0.835
7	0.66		0.73
8	0.52		0.59
9	0.39		0.455
10	0.63		0.51
11	0.36		0.495
12	0.46		0.41
13	0.49		0.475
14	0.53		0.51
15	0.45		0.49
16	0.43		0.44
17	0.54		0.485
18	0.29		0.415
19	0.35		0.32
20	0.4		0.375
21	0.4		0.4
22	0.32		0.36
23	0.36		0.34
24	0.22		0.29
25	0.31		0.265
26	0.27		0.29
Sheet1

Cell Formulas
Range	Formula
C2	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A2, -$B$2+1,0,$B$2)),NA())
C3	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A3, -$B$2+1,0,$B$2)),NA())
C4	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A4, -$B$2+1,0,$B$2)),NA())
C5	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A5, -$B$2+1,0,$B$2)),NA())
C6	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A6, -$B$2+1,0,$B$2)),NA())
C7	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A7, -$B$2+1,0,$B$2)),NA())
C8	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A8, -$B$2+1,0,$B$2)),NA())
C9	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A9, -$B$2+1,0,$B$2)),NA())
C10	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A10, -$B$2+1,0,$B$2)),NA())
C11	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A11, -$B$2+1,0,$B$2)),NA())
C12	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A12, -$B$2+1,0,$B$2)),NA())
C13	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A13, -$B$2+1,0,$B$2)),NA())
C14	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A14, -$B$2+1,0,$B$2)),NA())
C15	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A15, -$B$2+1,0,$B$2)),NA())
C16	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A16, -$B$2+1,0,$B$2)),NA())
C17	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A17, -$B$2+1,0,$B$2)),NA())
C18	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A18, -$B$2+1,0,$B$2)),NA())
C19	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A19, -$B$2+1,0,$B$2)),NA())
C20	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A20, -$B$2+1,0,$B$2)),NA())
C21	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A21, -$B$2+1,0,$B$2)),NA())
C22	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A22, -$B$2+1,0,$B$2)),NA())
C23	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A23, -$B$2+1,0,$B$2)),NA())
C24	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A24, -$B$2+1,0,$B$2)),NA())
C25	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A25, -$B$2+1,0,$B$2)),NA())
C26	=IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A26, -$B$2+1,0,$B$2)),NA())

 

[StephenCrump]

Here is one way to do a moving average.

I agree, but we have been asked for the Hull moving average (which is new to me!)

If I'm interpreting correctly, the Hull MA for period 100, say, will be:

MA( 2*MA(OriginalData,50) - MA(OriginalData,100), INT(SQRT(100)))

rush1884,

Will we always be averaging the same number of data points, e.g. for period 100, we will always pick up 100 data points = 100 trading days, i.e. we don't need to allow for variations in weekends/holidays/other non-trading days.

You mentioned "weighted", but I assume we're not actually weighting the index values with any other values?

 

[shg]

There's a workbook with four user-defined functions and several examples for calculating weighted moving averages and Hull's moving average (which is also just another WMA) at https://app.box.com/s/dfsv8o8omn6x35j31nwtqvgw331adg3u.

Are you really sure you want a 2000-day average? That's 10 years of data. There's a constant in the code that would need to change.

 

[Cindy Ellis]

I only know what I've found online (so no expertise ), but it seems that the Hull moving average (HMA) depends on the weighted moving average (WMA), which is weighted based on how close (in series) the data is relative to the WMA being calculated. For a relatively simple 5-day WMA, the formula would be Day5value(5/15) + Day4value(4/15) + Day3value(3/15) + Day2value(2/15) + Day2value(1/15). It seems that the most reasonable way to approach it would be VBA (code). I'll see what I can come up with.

Edit...I didn't see shg's post. Do that

 

[StephenCrump]

I am desperately looking for some help in calculating 2000, 500, and 100 period hull moving average on a big dataset.

I am also wondering whether this is homework/assignment?

In which case we're still happy to help with Excel questions, but not do all the work.

 

[michaelsmith559]

I'm with Cindy Ellis. Looking online I found this vba solution. It may help. Also use shg's post:
Hull Moving Average-Excel VBA, Technical Indicators- Finance4Traders

 

[joe miller]

the excel spreadsheet i created checks with your example. however, when i use the formula =IF(ROW()>=($B$2+1),AVERAGE(OFFSET(A2, -$B$2+1,0,$B$2)),NA()) against data other than than shown in sheet 1 column A results are not correct.

those incorrect results indicate that the formula is incorrect. i am excel formula challenged so i am trying to understand the ''offset'' function,
etc.

in the meantime, any comments, suggestions, etc. will be much appreciated.

 

[on_an_island]

I know this is an excel forum but if you're constantly working with very large datasets I recommend looking into R programming. You can calculate the WMA and HMA (and many other technical trading indicators) with two lines of code and fewer than 5 inputs for each line of code. You can then easily export to an excel file and manipulate / work with the dataset however you want. Look up R's TTR library if you're interested in going down that route.

 

[joe miller]

thanks everyone, you have been super helpful. i have stumbled into resolving my problem
so don't pay any attention to any of my posts from now on. i did not create this thread, i jumped
in in the middle of it.