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.

Answer

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