6-month Cumulative Return

[Sakvarelidze Elene]

Dear Statalists,
I know there are couple of posts about cumulative returns but unfortunately none of them fit my question therefore please assist me. I am replicating study where at the end of each month, all stocks in each country are ranked in ascending order based on the past 6-month cumulative return. I tried to use the ways that were posted here but they do not work for me. Please suggest something. Below you will find the portion of the data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 DSCD double Price str14 Country float(month year return)
"10000" 4.375 " United States" 1 1986 .
"10000" 3.25 " United States" 2 1986 -29.725153
"10000" 4.4375 " United States" 3 1986 31.143616
"10000" 4 " United States" 4 1986 -10.37968
"10000" 3.10938 " United States" 5 1986 -25.1871
"10000" 3.09375 " United States" 6 1986 -.5039402
"10000" 2.84375 " United States" 7 1986 -8.426034
"10000" 1.09375 " United States" 8 1986 -95.55115
"10000" 1.03125 " United States" 9 1986 -5.88405
"10000" .78125 " United States" 10 1986 -27.763174
"10000" .82813 " United States" 11 1986 5.827495
"10000" .51563 " United States" 12 1986 -47.37807
"10000" .40625 " United States" 1 1987 -23.84207
"10000" .40625 " United States" 2 1987 0
"10000" .25 " United States" 3 1987 -48.55078
"10000" .23438 " United States" 4 1987 -6.451719
"10000" .21875 " United States" 5 1987 -6.901421
"10001" 6.125 " United States" 1 1986 .
"10001" 6.25 " United States" 2 1986 2.0202708
"10001" 6.3125 " United States" 3 1986 .9950331
"10001" 6.375 " United States" 4 1986 .9852297
"10001" 6.3125 " United States" 5 1986 -.9852297
"10001" 6.125 " United States" 6 1986 -3.015304
"10001" 6.0625 " United States" 7 1986 -1.02565
"10001" 6.5 " United States" 8 1986 6.967992
"10001" 6.375 " United States" 9 1986 -1.9418086
"10001" 6.625 " United States" 10 1986 3.846628
"10001" 7 " United States" 11 1986 5.505978
"10001" 7 " United States" 12 1986 0
"10001" 6.75 " United States" 1 1987 -3.6367645
"10001" 6.25 " United States" 2 1987 -7.696104
"10001" 6.375 " United States" 3 1987 1.9802628
"10001" 6.125 " United States" 4 1987 -4.0005336
"10001" 5.6875 " United States" 5 1987 -7.410797
"10001" 5.875 " United States" 6 1987 3.243528
"10001" 6 " United States" 7 1987 2.105341
"10001" 6.5 " United States" 8 1987 8.004271
"10001" 6.25 " United States" 9 1987 -3.922071
"10001" 6.375 " United States" 10 1987 1.9802628
"10001" 6.1875 " United States" 11 1987 -2.985296
"10001" 5.875 " United States" 12 1987 -5.182507
"10001" 6.25 " United States" 1 1988 6.187541
"10001" 6.75 " United States" 2 1988 7.696104
"10001" 6.125 " United States" 3 1988 -9.716374
"10001" 6.3125 " United States" 4 1988 3.015304
"10001" 6.4375 " United States" 5 1988 1.960847
"10001" 6.25 " United States" 6 1988 -2.95588
"10001" 6.4375 " United States" 7 1988 2.95588
"10001" 6.625 " United States" 8 1988 2.8710105
"10001" 6.375 " United States" 9 1988 -3.846628
"10001" 6.625 " United States" 10 1988 3.846628
"10001" 6.625 " United States" 11 1988 0
"10001" 6.375 " United States" 12 1988 -3.846628
"10001" 6.5 " United States" 1 1989 1.9418086
"10001" 6.75 " United States" 2 1989 3.774033
"10001" 6.75 " United States" 3 1989 0
"10001" 7.25 " United States" 4 1989 7.145896
"10001" 7 " United States" 5 1989 -3.509132
"10001" 7 " United States" 6 1989 0
"10001" 7.25 " United States" 7 1989 3.509132
"10001" 9.25 " United States" 8 1989 24.36221
"10001" 8.875 " United States" 9 1989 -4.1385217
"10001" 9.5 " United States" 10 1989 6.805346
"10001" 9.875 " United States" 11 1989 3.871451
"10001" 10.125 " United States" 12 1989 2.50013
"10001" 9.9375 " United States" 1 1990 -1.8692133
"10001" 9.875 " United States" 2 1990 -.6309169
"10001" 9.875 " United States" 3 1990 0
"10001" 9.875 " United States" 4 1990 0
"10001" 9.75 " United States" 5 1990 -1.2739025
"10001" 9.75 " United States" 6 1990 0
"10001" 10 " United States" 7 1990 2.531781
"10001" 9.5 " United States" 8 1990 -5.129329
"10001" 9.75 " United States" 9 1990 2.597549
"10001" 9.625 " United States" 10 1990 -1.2903405
"10001" 9.625 " United States" 11 1990 0
"10001" 9.5 " United States" 12 1990 -1.3072082
"10001" 9.625 " United States" 1 1991 1.3072082
"10001" 9.75 " United States" 2 1991 1.2903405
"10001" 9.5 " United States" 3 1991 -2.597549
"10001" 9.875 " United States" 4 1991 3.871451
"10001" 9.875 " United States" 5 1991 0
"10001" 10.5 " United States" 6 1991 6.136895
"10001" 10.125 " United States" 7 1991 -3.6367645
"10001" 11.5 " United States" 8 1991 12.733942
"10001" 11.5 " United States" 9 1991 0
"10001" 13 " United States" 10 1991 12.260232
"10001" 14.75 " United States" 11 1991 12.629373
"10001" 14.5 " United States" 12 1991 -1.7094433

[Attaullah Shah]

When you say that a given method did not work, it creates two confusions for us. (1) which method (2) how it failed against what you wanted. Upon my guess, here is one solution using asrol (from SSC)

ssc install asrol

. gen mofd = mofd(mdy(month, 1, year))

. format mofd %tm

// Assumsing that all firm IDs are unique and they do not have similar names in different countries.
. bys DSCD : asrol return , stat(sum) window(mofd 6) min(5)


. list in 1/10

     +------------------------------------------------------------------------------------+
     |  DSCD     Price          Country   month   year      return      mofd   sum6_ret~n |
     |------------------------------------------------------------------------------------|
  1. | 10000     4.375    United States       1   1986           .    1986m1            . |
  2. | 10000      3.25    United States       2   1986   -29.72515    1986m2            . |
  3. | 10000    4.4375    United States       3   1986    31.14362    1986m3            . |
  4. | 10000         4    United States       4   1986   -10.37968    1986m4            . |
  5. | 10000   3.10938    United States       5   1986    -25.1871    1986m5            . |
     |------------------------------------------------------------------------------------|
  6. | 10000   3.09375    United States       6   1986   -.5039402    1986m6   -34.652257 |
  7. | 10000   2.84375    United States       7   1986   -8.426034    1986m7   -43.078291 |
  8. | 10000   1.09375    United States       8   1986   -95.55115    1986m8   -108.90429 |
  9. | 10000   1.03125    United States       9   1986    -5.88405    1986m9   -145.93195 |
 10. | 10000    .78125    United States      10   1986   -27.76317   1986m10   -163.31545 |
     +------------------------------------------------------------------------------------+

.