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