Creating Rolling Averages over an exact year using daily data

[Kevin Ming]

Hi,

I have a time series of daily data and want to compute average values over an exact year. So for example, for the observation January 1st, 1999, I want the average from January 1st,1999 to January 1st, 2000. I can do this easily using monthly data with the rangestat command which can give me rolling observations and its straight forward because there's always 12 months in a year. However, I'm running into trouble for the same using daily data. I want the exact average over a year (February 27th, 2011 to February 27th, 2012). There aren't exact number of days in a year because this is stock returns data. Would appreciate any advice/insight on this. Thank you!

[Phil Bromiley]

Welcome to Stata list. You didn't get a quick answer. You will increase your chances of useful answer by following the FAQ on asking questions-provide Stata code in code delimiters, readable Stata output, and sample data using dataex.

While I am sure there is a much more efficient way to do this, you could loop over days and simply use year and day within year as conditions for calculating a mean. It might be possible to do it with statsby again conditioning it so that it calculates between the current date and the date one year further and the same day within year.

[Nick Cox]

rangestat is from SSC, as you are asked to explain (FAQ Advice #12).

I don't really understand the problem. Exact years might mean a maximum span or 365 or 366 days, but I've not ever heard that fussing about the difference is of any importance compared with just being clear and consistent. So, something like

rangestat whatever, int(ddate -365 -1)

is for example code to get an average over what is available within the previous 365 days. There are various small or large variations on that, especially if your data are really panel data. In practice I advise keeping track of the count of non-missing values too.

[Kevin Ming]

Thanks for the feedback. The reason it’s more complicated than rangestat (-365 -1) is because its daily stock data which have only trading days. Therefore it’s not obvious that 365 days from jan 1, 2002 will be jan 1, 2003. In fact it won’t be because weekends are not in the data set. If I had monthly data I could use your advice of rangestat (-12,-1) because there’s always 12 months in a year. Would appreciate any further guidance on this matter if available. Thank you very much!

[Kevin Ming]

Welcome to Stata list. You didn't get a quick answer. You will increase your chances of useful answer by following the FAQ on asking questions-provide Stata code in code delimiters, readable Stata output, and sample data using dataex.

While I am sure there is a much more efficient way to do this, you could loop over days and simply use year and day within year as conditions for calculating a mean. It might be possible to do it with statsby again conditioning it so that it calculates between the current date and the date one year further and the same day within year.

Thanks Phil. This is what I had in mind and I've been doing this on Excel using averageif. Are you aware of the syntax code if I have the date and the same date one year later how I can create a variable in which observations are the average conditional between those two days? I think that's most likely how to do it but I'm only familiar with the rangestat command. Thank you!

[Nick Cox]

You miss the point, I think.

rangestat does not count 365 observations or 365 non-missing values with that interval; it just uses all observations available within [ddate - 365, ddate - 1].