## Question

Anomaly Hunters has asked: How Can I find cumulative returns from month T-1 to T-12, i.e. not including the current month in the calculation. I want to do it on a rolling window for each firm.

Let us use a small dataset. We shall create this dataset in Stata with the following code


clear
input byte firm_id str6 date float ret
1 "200310" .
1 "200311" -.049505
1 "200312" .0121528
1 "200401" .0360206
1 "200402" -.0480132
1 "200403" .0434783
1 "200404" .0933333
1 "200405" .0121951
1 "200406" .0060241
1 "200407" .0883234
1 "200408" -.0261348
1 "200409" -.0819209
1 "200410" -.02
1 "200411" -.0062794
1 "200412" .0458136
1 "200501" .0891239
1 "200502" .0443828
1 "200503" .
end
⠀
* Convert the date to Stata format
tostring date, replace
gen month = mofd(date(date, "YM"))
format month %tm
⠀
* Find cumulative returns in a 12-month rolling window
bys firm_id : asrol ret, stat(product) add(1) window(month 12)
⠀
* Lag the returns by one month to skip the current month returns from the calculation
tsset firm_id month
gen ret_skip1 = L.ret_product12
⠀
* List month, ret, product12_ret, and ret_skip1
⠀
list month ret ret_product12 ret_skip1

And appreciate the results:

Table: Results
firm_id date ret month ret_product12 ret_skip1
1 200310 . 2003m10 . .
1 200311 -0.050 2003m11 -0.050 .
1 200312 0.012 2003m12 -0.038 -0.050
1 200401 0.036 2004m1 -0.003 -0.038
1 200402 -0.048 2004m2 -0.051 -0.003
1 200403 0.043 2004m3 -0.010 -0.051
1 200404 0.093 2004m4 0.083 -0.010
1 200405 0.012 2004m5 0.096 0.083
1 200406 0.006 2004m6 0.102 0.096
1 200407 0.088 2004m7 0.200 0.102
1 200408 -0.026 2004m8 0.168 0.200
1 200409 -0.082 2004m9 0.073 0.168
1 200410 -0.020 2004m10 0.051 0.073
1 200411 -0.006 2004m11 0.099 0.051
1 200412 0.046 2004m12 0.136 0.099
1 200501 0.089 2005m1 0.194 0.136
1 200502 0.044 2005m2 0.310 0.194
1 200503 . 2005m3 0.255 0.310
Notes: Table created by asdocx, see asdocx.com