# Moving average

1. ## 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

2. ## Re: Moving average

Hello!

Is there any chance you could upload an example of your report so I have a better idea of what we are looking at? I may be able to help if so! If not, I think others may be able to help as well if they can see an example.

Thanks!

4. ## Re: Moving average

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%

5. ## Re: Moving average

Although before I dive into learning more about HMA to apply it on my sheet (I have seen you have posted some content about it on other posts already) I think what you have posted is not right. For instance, if I calculate the value of E8 (AVERAGE(B2:B150)) it should be around 0.00216%.

6. ## Re: Moving average

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%

7. ## Re: Moving average

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

8. ## Re: Moving average

I see what you mean! I think I haven't made myself clear then:
I need the average of the daily observations for the last six months next to each monthly date. I could potentially somehow VLOOUK UP the values of the result of the first daily result of the average you have just posted to the monthly date. However, as I said in the note of my first post those will not always be the same :S

9. ## Re: Moving average

 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%

10. ## Re: Moving average

Hello thanks for your formula! However, why did you delete columns C & D? What I need is to match the average of DAILY observations for six months to the value of the corresponding following MONTHLY observation. For instance, if you take my original file, my first value should be placed in cell E8. That cell corresponds to the monthly observation of July 1929, hence the value that corresponds there is AVERAGE(B2:B150). For the next value, E9 (August) I would need the DAILY averages from February to July hence =AVERAGE(B27:B175) and so on...

I have tried to tweak your formula to see if I can fit it to what I am asking for but I had no luck so far!

I really would appreciate the help! I have been stuck at this for long already!

11. ## Re: Moving average

E8 should be the average return for what dates?

12. ## Re: Moving average

The daily average of the six preceding months. E8 corresponds to the monthly observation for July hence I need the average of the daily observations of January to June.
Therefore, for E9 (August) I would need the daily average from February to July. E10 (September) I would need the daily average from March to August and so on for the all the monthly observations.

13. ## Re: Moving average

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

14. ## Re: Moving average

I am replicating a study from Barroso & Santa Clara (2015). If you really want to know: what I called "returns" in column B are actually squared returns (which is a measure of volatility called realised variance). This serves as a measure to adjust the level of risk of a Momentum strategy. So I am not really measuring the return over the period, what I need is to calculate the average daily realised variance throughout the last 6 months and then I will multiply it x21 to make it monthly a monthly observation. Also I didn't forgot to mention that column C should therefore not be involved in any calculation for that matter, sorry in case it might have confused you!

15. ## Re: Moving average

 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.

16. ## Re: Moving average

Yes! this is exactly what I needed! Thank you very much for your help, I really appreciate it :D

17. ## Re: Moving average

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)