Moving average

Hello everyone!

I need to do a moving average in excel and I am struggling a little:

I have daily observations of a stock's return (column A has the date, with adate format, and B the underlying return). My columns C and D are respectively the date and returns for the same stock, in the same period, but monthly observations. I need to do a moving average in monthly observations, that calculates the average of the daily returns for the past 6 months.

For instance, in the monthly observation for July 2017 i want the average of daily returns from January to June.

note: since the stocks are not traded in weekends I normally do not have 30 or 31 observations per month but on average 21, it depends on the month itself.
Also for some stocks the first observation might not be on the first day of the month as it could coincide that that day is Saturday or Sunday!

I tried to explain my problem as clearly as possible, if however something is not clear enough let me know!
Thanks for the help in advance

I have a user-defined function for Hull's moving average if you think that's suitable. See https://www.fidelity.com/learning-ce...moving-average.

	C	D	E	F
1	Date Monthly	Returns Monthly
2	01 Jan 1927	0.44%
3	01 Feb 1927	-2.01%
4	01 Mar 1927	3.59%
5	01 Apr 1927	4.19%
6	01 May 1927	3.01%
7	01 Jun 1927	0.51%
8	01 Jul 1927	4.32%	2.56%	E8: =hma(D2:D8)
9	01 Aug 1927	1.12%	2.11%
10	01 Sep 1927	1.93%	1.85%
11	01 Oct 1927	-1.11%	0.23%
12	01 Nov 1927	-0.68%	-1.06%
13	01 Dec 1927	3.19%	0.40%
14	01 Jan 1928	-0.60%	0.92%
15	01 Feb 1928	-1.02%	-0.03%
16	01 Mar 1928	4.87%	1.61%
17	01 Apr 1928	-5.65%	-0.65%
18	01 May 1928	2.85%	-0.52%
19	01 Jun 1928	2.05%	0.99%
20	01 Jul 1928	2.75%	2.86%
21	01 Aug 1928	3.62%	3.85%
22	01 Sep 1928	3.62%	4.14%
23	01 Oct 1928	5.05%	4.71%
24	01 Nov 1928	1.93%	3.77%
25	01 Dec 1928	1.71%	2.25%

I was doing it on the monthly data as shown. For the daily data,

	A	B	C	D
140	Sat 18 Jun 1927	0.000081%
141	Mon 20 Jun 1927	0.000676%
142	Tue 21 Jun 1927	0.000484%
143	Wed 22 Jun 1927	0.000169%
144	Thu 23 Jun 1927	0.001936%
145	Fri 24 Jun 1927	0.001089%
146	Sat 25 Jun 1927	0.003136%
147	Mon 27 Jun 1927	0.000900%
148	Tue 28 Jun 1927	0.000064%
149	Wed 29 Jun 1927	0.000196%
150	Thu 30 Jun 1927	0.000225%	0.002878%	C150: =hma(B2:B150)
151	Fri 01 Jul 1927	0.002401%	0.002805%
152	Sat 02 Jul 1927	0.001156%	0.002735%
153	Tue 05 Jul 1927	0.001600%	0.002670%
154	Wed 06 Jul 1927	0.000256%	0.002602%
155	Thu 07 Jul 1927	0.000225%	0.002530%
156	Fri 08 Jul 1927	0.003721%	0.002477%

Your result is just the unweighted average. The HMA is a windowed average specifically designed to reduce lag.

	A	B	C	D
143	Wed 22 Jun 1927	0.000169%
144	Thu 23 Jun 1927	0.001936%
145	Fri 24 Jun 1927	0.001089%
146	Sat 25 Jun 1927	0.003136%
147	Mon 27 Jun 1927	0.000900%
148	Tue 28 Jun 1927	0.000064%
149	Wed 29 Jun 1927	0.000196%
150	Thu 30 Jun 1927	0.000225%	#N/A	C150: =AVERAGE(INDEX(B:B, MATCH(EDATE(A150, -6) + 1,A:A )):B150)
151	Fri 01 Jul 1927	0.002401%	#N/A
152	Sat 02 Jul 1927	0.001156%	0.002158%	2
153	Tue 05 Jul 1927	0.001600%	0.002179%
154	Wed 06 Jul 1927	0.000256%	0.002179%
155	Thu 07 Jul 1927	0.000225%	0.002153%
156	Fri 08 Jul 1927	0.003721%	0.002164%
157	Sat 09 Jul 1927	0.000256%	0.002148%
158	Mon 11 Jul 1927	0.002500%	0.002174%
159	Tue 12 Jul 1927	0.000225%	0.002168%

E8 should be the average return for what dates?

What does that result even mean? If you want to measure the return over the period, it would be PRODUCT(1+DailyReturns)-1

	A	B	C	D	E	F
1	Date	Daily Rtn	Month	Mothly Rtn
2	Mon 03 Jan 1927	0.000009%	01 Jan 1927	0.44%
3	Tue 04 Jan 1927	0.000361%	01 Feb 1927	-2.01%
4	Wed 05 Jan 1927	0.002304%	01 Mar 1927	3.59%
5	Thu 06 Jan 1927	0.000225%	01 Apr 1927	4.19%
6	Fri 07 Jan 1927	0.004096%	01 May 1927	3.01%
7	Sat 08 Jan 1927	0.002601%	01 Jun 1927	0.51%
8	Mon 10 Jan 1927	0.000324%	01 Jul 1927	4.32%	0.00216%	E8: {=AVERAGE(INDEX($B$2:$B$25000, MATCH(TRUE, $A$2:$A$25000 >= EDATE(C8, -6), 0)):INDEX($B$2:$B$25000, MATCH(C8 - 1, $A$2:$A$25000 )))}
9	Tue 11 Jan 1927	0.000484%	01 Aug 1927	1.12%	0.00208%
10	Wed 12 Jan 1927	0.001156%	01 Sep 1927	1.93%	0.00259%
11	Thu 13 Jan 1927	0.000576%	01 Oct 1927	-1.11%	0.00264%
12	Fri 14 Jan 1927	0.002704%	01 Nov 1927	-0.68%	0.00287%
13	Sat 15 Jan 1927	0.007569%	01 Dec 1927	3.19%	0.00301%

Note that column D is not used.

You're welcome.