Moving 12 - 1 cumulative return

[Jasper de Fluiter Balledux]

Hey all,

I am trying to replica a momentum trading strategy for which I'm building portfolios based on the 12 - 1 cumulative returns. This means that for each observation I calculate the cumulative return of the 12 months before, but without the 1 month before. My dataset is ordered like this:

firm_id date company stockprice market_value ret
1 200310 77 BANK (#T) - 77 BANK (#T) 3030 232266.8
1 200311 77 BANK (#T) - 77 BANK (#T) 2880 220768.4 -.049505
1 200312 77 BANK (#T) - 77 BANK (#T) 2915 223451.4 .0121528
1 200401 77 BANK (#T) - 77 BANK (#T) 3020 231500.2 .0360206
1 200402 77 BANK (#T) - 77 BANK (#T) 2875 220385.1 -.0480132
1 200403 77 BANK (#T) - 77 BANK (#T) 3000 229967.1 .0434783
1 200404 77 BANK (#T) - 77 BANK (#T) 3280 251430.7 .0933333
1 200405 77 BANK (#T) - 77 BANK (#T) 3320 254496.9 .0121951
1 200406 77 BANK (#T) - 77 BANK (#T) 3340 256030.1 .0060241
1 200407 77 BANK (#T) - 77 BANK (#T) 3635 278643.5 .0883234
1 200408 77 BANK (#T) - 77 BANK (#T) 3540 271361.2 -.0261348
1 200409 77 BANK (#T) - 77 BANK (#T) 3250 249131 -.0819209
1 200410 77 BANK (#T) - 77 BANK (#T) 3185 244148.4 -.02
1 200411 77 BANK (#T) - 77 BANK (#T) 3165 242615.3 -.0062794
1 200412 77 BANK (#T) - 77 BANK (#T) 3310 253730.4 .0458136
1 200501 77 BANK (#T) - 77 BANK (#T) 3605 276343.8 .0891239
1 200502 77 BANK (#T) - 77 BANK (#T) 3765 288608.7 .0443828
1 200503 77 BANK (#T) - 77 BANK (#T) 3745 287075.6 -.0053121

What would be the best way going forward to calculate the 12-1 cumulative returns for each observation (starting at 200411) ? I know it has something to do with the rangestat command but I can't seem to make it work the way I want it to.

Thanks in advance.

[Attaullah Shah]

See this post, and my reply at #3 https://www.statalist.org/forums/for...ily-price-data
Also, this blog post might be of interest to you https://fintechprofessor.com/2017/10...ct-of-returns/

[Jasper de Fluiter Balledux]

Thanks for your reply. Using your blogpost its clear to me how to get the 12 month cumulative return, but its still unclear how to ensure that the previous month is not part of the calculation. Could you clarify this?

[Attaullah Shah]

See this example

* First generate a dummy data

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.product12_ret
list month ret product12_ret ret_skip1

*And appreciate the results

     +---------------------------------------------+
     |   month        ret   product1~t   ret_skip1 |
     |---------------------------------------------|
  1. | 2003m10          .            .           . |
  2. | 2003m11   -.049505     -.049505           . |
  3. | 2003m12    .012153   -.03795382    -.049505 |
  4. |  2004m1    .036021   -.00330034   -.0379538 |
  5. |  2004m2   -.048013   -.05115508   -.0033003 |
     |---------------------------------------------|
  6. |  2004m3    .043478   -.00990092   -.0511551 |
  7. |  2004m4    .093333    .08250829   -.0099009 |
  8. |  2004m5    .012195    .09570959    .0825083 |
  9. |  2004m6    .006024    .10231025    .0957096 |
 10. |  2004m7    .088323    .19967004    .1023103 |
     |---------------------------------------------|
 11. |  2004m8   -.026135     .1683169      .19967 |
 12. |  2004m9   -.081921    .07260733    .1683169 |
 13. | 2004m10       -.02    .05115519    .0726073 |
 14. | 2004m11   -.006279     .0989585    .0511552 |
 15. | 2004m12    .045814    .13550617    .0989585 |
     |---------------------------------------------|
 16. |  2005m1    .089124     .1937088    .1355062 |
 17. |  2005m2    .044383    .30956536    .1937088 |
 18. |  2005m3   -.005312     .2483334    .3095654 |
     +---------------------------------------------+

[Leonardo Guizzetti]

Anomaly Hunters , we ask that you do use your real name for the forum. This is clearly outlined in the FAQ. You may use the FAQ to request a name change.

[Jasper de Fluiter Balledux]

Dear Attaulah,

I think we are almost there. The 12 - 1 means that the previous month is not part of the calculation, it's just the 11 months before. So for our observation regarding november of 2004 we get the data from November 2003 until september of 2004, Oktober of 2004 is not a part of the calculation. Is this achieved by the codes you just sent?

Update: We have found an update, thanks for helping out.

[Ayub UOM]

Attaullah Shah
AOA sir,
sir i want to calculate stocks return9commulation,logrithmic,adjusted) for that very purppose i tried to use your https://fintechprofessor.com/2017/10...ct-of-returns/ commands,but i am facing one problem, when i am calculating daily simple return then in some cases i get missing value because of some previous days in the middle are missing,so how can i deal with this situation sir, so that previous day should be considered as previous day, irrespective of the date,

* Example generated by -dataex-. To install: ssc install dataex
clear
input int stkcd str10 trddt float(opnprc clsprc ymd simple_ri)
2 "1/4/2006"   4.4 4.66 16805            .
2 "1/5/2006"  4.65 4.72 16806   .012875524
2 "1/6/2006"  4.73 4.69 16807  -.006355876
2 "1/9/2006"  4.68 4.63 16810            .
2 "1/10/2006" 4.63 4.75 16811     .0259179
2 "1/11/2006" 4.75 4.61 16812  -.029473657
2 "1/12/2006"  4.6 4.63 16813  .0043383907
2 "1/13/2006" 4.62 4.45 16814   -.03887695
2 "1/16/2006" 4.45 4.32 16817            .
2 "1/17/2006" 4.32 4.44 16818    .02777775
2 "1/18/2006" 4.44 4.54 16819     .0225225
2 "1/19/2006" 4.54  4.6 16820   .013215846
2 "1/20/2006" 4.63 4.55 16821  -.010869503
2 "1/23/2006" 4.55 4.54 16824            .
2 "1/24/2006" 4.55 4.79 16825    .05506608
2 "1/25/2006" 4.79 4.93 16826    .02922753
2 "2/6/2006"  4.96 5.37 16838            .
2 "2/7/2006"  5.38 5.25 16839   -.02234635
2 "2/8/2006"  5.24 5.26 16840  .0019048055
2 "2/9/2006"  5.26 5.06 16841   -.03802286
2 "2/10/2006" 5.05 5.16 16842   .019762827
2 "2/13/2006" 5.16  5.1 16845            .
2 "2/14/2006"  5.1 5.35 16846    .04901961
2 "2/15/2006" 5.35 5.51 16847     .0299066
2 "2/16/2006"  5.5 5.37 16848   -.02540841
2 "2/17/2006" 5.36  5.6 16849    .04283055
2 "2/20/2006" 5.61 5.62 16852            .
2 "2/21/2006" 5.59 5.69 16853   .012455547
2 "2/22/2006" 5.68 5.62 16854  -.012302315
2 "2/23/2006"  5.6 5.55 16855  -.012455462
2 "2/24/2006" 5.55 5.61 16856     .0108108
2 "2/27/2006" 5.61 5.42 16859            .
2 "2/28/2006" 5.39 5.46 16860   .007380066
2 "3/1/2006"  5.46 5.49 16861   .005494457
2 "3/2/2006"  5.49 5.36 16862   -.02367935
2 "3/3/2006"  5.35 5.45 16863   .016790984
2 "3/6/2006"  5.43 5.38 16866            .
2 "3/7/2006"  5.38 5.19 16867  -.035315994
2 "3/8/2006"  5.18 5.05 16868  -.026974926
2 "3/9/2006"  5.05 5.04 16869 -.0019802433
2 "3/10/2006" 5.04 5.04 16870            0
2 "3/13/2006" 5.07 5.39 16873            .
2 "3/14/2006" 5.39 5.35 16874  -.007421143
2 "3/15/2006" 5.38 5.47 16875   .022429885
2 "3/16/2006" 5.48 5.52 16876  .0091408035
2 "3/17/2006" 5.53 5.46 16877  -.010869555
2 "3/20/2006" 5.46 5.71 16880            .
2 "3/21/2006" 5.72 5.77 16881    .01050787
2 "3/22/2006" 5.77 5.98 16882   .036395155
2 "3/23/2006" 5.98 5.99 16883  .0016721993
2 "3/24/2006"    6 5.93 16884  -.010016685
2 "3/27/2006" 5.93 6.24 16887            .
2 "3/28/2006" 6.24 6.51 16888    .04326931
2 "3/29/2006" 6.55 6.62 16889   .016897028
2 "3/30/2006"  6.6 6.52 16890  -.015105726
2 "3/31/2006"  6.5 6.55 16891  .0046012593
2 "4/3/2006"  6.55 6.89 16894            .
2 "4/4/2006"   6.9  6.8 16895  -.013062363
2 "4/5/2006"   6.8 6.98 16896    .02647056
2 "4/6/2006"  6.99 6.97 16897 -.0014326975
2 "4/7/2006"  6.96 6.83 16898  -.020086065
2 "4/10/2006" 6.83 6.71 16901            .
2 "4/11/2006"  6.7 6.88 16902    .02533533
2 "4/12/2006"  6.9 6.67 16903   -.03052326
2 "4/13/2006" 6.65 6.45 16904   -.03298355
2 "4/14/2006"  6.4  6.6 16905    .02325583
2 "4/17/2006" 6.58 6.38 16908            .
2 "4/18/2006" 6.38 6.48 16909   .015673965
2 "4/19/2006" 6.45 6.81 16910    .05092591
2 "4/20/2006"  6.8 6.65 16911   -.02349484
2 "4/21/2006" 6.65 6.67 16912   .003007516
2 "4/24/2006" 6.66 6.36 16915            .
2 "4/25/2006" 6.36 6.57 16916   .033018872
2 "4/26/2006" 6.55 6.46 16917   -.01674279
2 "4/27/2006" 6.45 6.43 16918 -.0046439953
2 "4/28/2006"  6.1 6.23 16919   -.03110417
2 "5/8/2006"  6.28 6.85 16929            .
2 "5/9/2006"   6.9 6.93 16930   .011678821
2 "5/10/2006" 6.93 6.74 16931  -.027417036
2 "5/11/2006"  6.7 6.26 16932   -.07121655
2 "5/12/2006" 6.25 6.18 16933  -.012779616
2 "5/15/2006" 6.18  6.4 16936            .
2 "5/16/2006"  6.3 6.05 16937   -.05468749
2 "5/17/2006" 6.02 6.08 16938   .004958633
2 "5/18/2006" 5.98 6.18 16939   .016447352
2 "5/19/2006" 6.17 6.07 16940  -.017799297
2 "5/22/2006" 6.02 6.09 16943            .
2 "5/23/2006" 6.05 5.85 16944   -.03940891
2 "5/24/2006" 5.84 5.96 16945    .01880344
2 "5/25/2006"  5.9 5.97 16946  .0016778107
2 "5/26/2006" 5.94 5.94 16947  -.005025081
2 "5/29/2006" 5.98 5.94 16950            .
2 "5/31/2006" 6.09 5.97 16952            .
2 "6/1/2006"  5.97 5.95 16953  -.003350081
2 "6/2/2006"  5.95 5.64 16954   -.05210083
2 "6/5/2006"   5.6  5.8 16957            .
2 "6/6/2006"  5.76 5.59 16958    -.0362069
2 "6/7/2006"  5.55 5.55 16959  -.007155628
2 "6/8/2006"  5.53 5.45 16960  -.018018086
2 "6/9/2006"  5.43 5.29 16961   -.02935777
end
format %td ymd

Sir different authors used different measures, maybe the meaning is the same, but i am little bit confused,
(2) Logarithmic stock return of firm i at year t measured three months after fiscal year-end;
(3) Annual returns are obtained by cumulating monthly returns starting from the fourth month after the firm’s fiscal year end

(4) Rit represents the adjusted stock return of firm i over a year ended April 30.
sir i have a daily simple return as well as monthly return,
----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input long code str7 tradingmonth float(monthlyopeningprice monthlyclosingprice Month_ret ym)
2 "2003-01"  9.48 10.07  .043523 516
2 "2003-02"  10.1 10.33  .025819 517
2 "2003-03" 10.35 11.81  .143272 518
2 "2003-04" 11.88 12.84  .087214 519
2 "2003-05" 12.92  7.13  .126947 520
2 "2003-06"   7.1  5.82 -.183731 521
2 "2003-07"  5.77  6.37  .094502 522
2 "2003-08"  6.38  6.46  .014129 523
2 "2003-09"  6.45  5.86 -.092879 524
2 "2003-10"  5.88  5.94  .013652 525
2 "2003-11"  5.89  5.91 -.005051 526
2 "2003-12"  5.91  6.54  .106599 527
2 "2004-01"   6.5  7.85  .200306 528
2 "2004-02"  8.13  8.16   .03949 529
2 "2004-03"  8.18  8.83  .082108 530
2 "2004-04"  8.99  7.69 -.129105 531
2 "2004-05"  7.67  5.05 -.008308 532
2 "2004-06"  5.06  4.86 -.037624 533
2 "2004-07"  4.85  5.35  .100823 534
2 "2004-08"  5.33  5.02 -.061682 535
2 "2004-09"  5.03  5.42  .079681 536
2 "2004-10"  5.42     5 -.077491 537
2 "2004-11"     5  5.12     .024 538
2 "2004-12"  5.12  5.26  .027344 539
2 "2005-01"  5.22  5.68  .079848 540
2 "2005-02"   5.7   5.7  .003521 541
2 "2005-03"   5.7  5.52 -.031579 542
2 "2005-04"  5.53   5.9  .068841 543
2 "2005-05"   5.9  4.65 -.211864 544


end
format %tm ym

sir could you please guide me regarding me these questions, please.
Best regards.

[Ayub UOM]

Attaullah Shah hello sir,
sir i am so sorry to re-ask my questions #7 , i tried to calculate my annual stock return from my monthly stock return, but there are two issues identified by the Clyde Schechter , on this post https://www.statalist.org/forums/for...annual-returns,
sir could you please guide me how can i get the annual return, adjusted annual return from the monthly data, normally the previous papers used from April to next march as a period, therefore i am facing problem in proper calculation.
Sir looking forward for your kind reply in this regard.
thank you in advance