cumulative past returns

[Asad Rind]

Hi everyone,
I want to calculate cumulative returns for small (past 4 weeks) intermediate (past 5-52 weeks) and long horizon (past 53-156 weeks) and want to use all three as separate variables in my analysis for a set of huge panel data set. I know how to calculate simple cumulative returns but this thing is complicated for me as i have never done this before. I think this will be some code on rolling basis.

I use the data from CRSP. the id variable is "permno" time variable is "date2" and i have returns of each week titled "ret" and price defined by "prc". I have attached the image of sample file for ease.
This is how i calculated cumulative returns. xtset permno date2 by permno (date2), sort: gen cum_ret = sum(ret)
Thanks in advance.

[Clyde Schechter]

This is a job for -rangestat-, which was written by Robert Picard, Nick Cox and Roberto Ferrer. You can get it from SSC. The -help- file has numerous worked examples that show you how to do it.

In the future, please do not post screenshots of data. They are difficult to read, at best. Even when readable, have you ever thought how you would import the data from a screenshot into Stata if you wanted to test out some code before posting it to help someone? You'd have no option but to hand-type it in. So to be helpful to those who want to help you, follow the advice in the FAQ (#12, in particular) and use the -dataex- command (another of Robert Picard and Nick Cox's creations). -dataex- is also available at SSC, and the help file contains simple clear instructions for how to use it.

[William Lisowski]

Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create this post and your previous posts. Note especially sections 9-12 on how to best pose your question. The more you help others understand your problem, the more likely others are to be able to help you solve your problem. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using CODE delimiters, and to use the dataex command to provide sample data, as described in section 12 of the FAQ. Using CODE delimiters would have made the sample code you presented readable.

In particular, the easiest way to describe what needs to be done in your current situation is to provide sample code, which is most easily developed using sample data, with real variable names. A picture of data leaves it to the reader to not only answer your question, but create data with which to do so. 10 weeks of data for a few firms would allow one to demonstrate "past 4 weeks" and "5-10 weeks" which should be adequate to point the way to the code you need for the time ranges you are interested in.

[Asad Rind]

I applologize for any inconvenience.
this is the code i used for simple cumulative returns command

 xtset permno date2
by permno (date2), sort: gen cum_ret = sum(ret)

here is the dataset example generated through dataex.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date2 double(prc vol ret)
10000  9504      -2.5 12800 -.024390242993831635
10000  9511    -2.875 22490   .04545454680919647
10000  9518        -3  2700                    0
10000  9525   -4.3125 24505 -.028169013559818268
10000  9532    -4.375   500                    0
10000  9539  -4.21875  2500 -.021739130839705467
10000  9546   -3.9375 14000             -.015625
10000  9553     -3.25  7200  -.08771929889917374
10000  9560  -3.46875 10800   .00909090880304575
10000  9567   -4.3125  1400                    0
10000  9574   -4.4375  1500                    0
10000  9581   -4.4375  1900                    0
10000  9588   -4.3125  1600                    0
10000  9595  -4.21875  4900  .022727273404598236
10000  9602  -4.09375  1800                    0
10000  9609   -4.0625  3100                    0
10000  9616        -4  1070 -.015384615398943424
10000  9623     -3.75  2300 -.016393441706895828
10000  9630  -4.15625  4900  .015267175622284412
10000  9637  -3.65625 18800  -.05645161122083664
10000  9644  -3.34375  1800 -.036036036908626556
10000  9651 -3.296875 13400   .03431372717022896
10000  9658   -2.9375  7350  -.02590673603117466
10000  9665   -2.9375   500                    0
10000  9672   -3.0625  1900                    0
10000  9679  -3.21875 11500  .019801979884505272
10000  9686    -2.875  3900  -.07070706784725189
10000  9693 -3.140625 11500   .11666666716337204
10000  9700  -2.90625  4550 -.010638297535479069
10000  9707  -2.59375  4100 -.017751479521393776
10000  9714    -2.375  4175                    0
10000  9721  -2.21875  3085   -.0533333346247673
10000  9728  -1.03125 40700                    0
10000  9735  -1.15625  1300                    0
10000  9742   -1.0625 26000   .03030303120613098
10000  9749  -1.21875 11050  -.04878048598766327
10000  9756  -1.03125  3000                    0
10000  9763   -.96875  3000                    0
10000  9770  -1.03125  6500                    0
10000  9777    -.9375  7450 -.032258063554763794
10000  9784   -.78125 34500    .0416666679084301
10000  9791   -.78125  1000                    0
10000  9798   -.78125     0                    0
10000  9805   -.71875 27000                    0
10000  9812   -.71875 22500                    0
10000  9819      -.75  6000  -.03999999910593033
10000  9826  -.828125  2221                    0
10000  9833    -.8125  2275  -.01886792480945587
10000  9840  -.671875  5750  -.06521739065647125
10000  9847    -.5625 15000  -.10000000149011612
10000  9854       -.5  1640                    0
10000  9863  -.515625     0                    0
10000  9869   -.46875  7900                    0
10000  9876   -.46875  3100                    0
10000  9883   -.46875  4850                    0
10000  9890   -.40625   650                    0
10000  9897   -.40625     0                    0
10000  9904   -.40625    60                    0
10000  9911    -.4375  1000   .07692307978868485
10000  9918    -.4375     0                    0
10000  9925  -.359375  4500   -.0416666679084301
10000  9932  -.390625     0                    0
10000  9939   -.34375 20730  -.11999999731779099
10000  9946   -.28125 14500   -.1818181872367859
10000  9953  -.234375     0                    0
10000  9960  -.234375    80                    0
10000  9967  -.234375     0                    0
10000  9974  -.203125     0                    0
10000  9981  -.234375     0    .1538461595773697
10000  9988   -.21875     0                    0
10000  9995   -.21875  1000                    0
10000 10002   -.21875 44171                    0
10000 10009   -.21875     0                    0
10000 10016   -.21875  1000                    0
10000 10023   -.21875   500                    0
10001  9506    -5.875     0  .021739130839705467
10001  9511   -6.0625  6000   .03191489353775978
10001  9518   -6.1875  5200                    0
10001  9525   -6.0625  4600 -.010204081423580647
10001  9532    -6.125  1000                    0
10001  9539     -6.25  2500 -.009900989942252636
10001  9546     -6.25  5200  .010101010091602802
10001  9553   -6.3125  3700                    0
10001  9560     -6.25  3100                    0
10001  9567     -6.25    20                    0
10001  9574     -6.25  1800                    0
10001  9581   -6.3125   300                    0
10001  9588   -6.3125  1500                    0
10001  9595   -6.3125  1520                    0
10001  9602   -6.3125  1000                    0
10001  9609    -6.375   600                    0
10001  9616    -6.375   800                    0
10001  9623    -6.375  1200                    0
10001  9630    -6.375  1850                    0
10001  9637    -6.375  1000                    0
10001  9644   -6.3125   600                    0
10001  9651   -6.1875  1300 -.019801979884505272
10001  9658   -6.1875  1630                    0
10001  9665   -6.1875     0                    0
10001  9672    -6.125    50                    0
end
format %d date2

[Asad Rind]

I used following code to solve the issue. the code worked well. However, the problem is that i got wrong values for first four observations in each "permno" ( obvious reason being the limitations of data). Is there a way that i can modify program to put "." in first four observations of each id "permno" so that i can drop these as they can bias my results.

 sort permno date2

by permno : gen weeks=_n

rangestat (sum) ret, interval(weeks -4 -1) by(permno)
rename ret_mean_sum cum_ret_4_weeks

here is the output that i got with this code

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date2 double(prc vol ret cum_ret_4_weeks)
10000  9504      -2.5 12800 -.024390242993831635                     .
10000  9511    -2.875 22490   .04545454680919647   .014645760878920555
10000  9518        -3  2700                    0   .032432323321700096
10000  9525   -4.3125 24505 -.028169013559818268    .11770595423877239
10000  9532    -4.375   500                    0     .1150523517280817
10000  9539  -4.21875  2500 -.021739130839705467    .09793324302881956
10000  9546   -3.9375 14000             -.015625   .061992243863642216
10000  9553     -3.25  7200  -.08771929889917374  -.045942907221615314
10000  9560  -3.46875 10800   .00909090880304575   -.04929470270872116
10000  9567   -4.3125  1400                    0   .002155051566660404
10000  9574   -4.4375  1500                    0   .026106589939445257
10000  9581   -4.4375  1900                    0    .04876811010763049
10000  9588   -4.3125  1600                    0    .04774374095723033
10000  9595  -4.21875  4900  .022727273404598236  -.010013569612056017
10000  9602  -4.09375  1800                    0  -.017247090465389192
10000  9609   -4.0625  3100                    0  -.018773808027617633
10000  9616        -4  1070 -.015384615398943424  -.011732296901755035
10000  9623     -3.75  2300 -.016393441706895828  -.015543888206593692
10000  9630  -4.15625  4900  .015267175622284412  .0014950796030461788
10000  9637  -3.65625 18800  -.05645161122083664  -.007764990441501141
10000  9644  -3.34375  1800 -.036036036908626556    -.0345741743221879
10000  9651 -3.296875 13400   .03431372717022896   -.03792496584355831
10000  9658   -2.9375  7350  -.02590673603117466   -.06416827999055386
10000  9665   -2.9375   500                    0    -.0584946870803833
10000  9672   -3.0625  1900                    0  -.023186609148979187
10000  9679  -3.21875 11500  .019801979884505272 -.0011621019802987576
10000  9686    -2.875  3900  -.07070706784725189   .005884690675884485
10000  9693 -3.140625 11500   .11666666716337204  -.007421227637678385
10000  9700  -2.90625  4550 -.010638297535479069 -.0057798889465630054
10000  9707  -2.59375  4100 -.017751479521393776   -.03270327765494585
10000  9714    -2.375  4175                    0   -.04781472869217396
10000  9721  -2.21875  3085   -.0533333346247673  -.031958841951563954
10000  9728  -1.03125 40700                    0    -.1854247481096536
10000  9735  -1.15625  1300                    0   -.13847535219974816
10000  9742   -1.0625 26000   .03030303120613098    -.1475691564846784
10000  9749  -1.21875 11050  -.04878048598766327   -.10023104399442673
10000  9756  -1.03125  3000                    0  .0013853944838047028
10000  9763   -.96875  3000                    0   -.03703243937343359
10000  9770  -1.03125  6500                    0   .004759609699249268
10000  9777    -.9375  7450 -.032258063554763794   -.05213648919016123
10000  9784   -.78125 34500    .0416666679084301   -.05822394508868456
10000  9791   -.78125  1000                    0  -.036679720506072044
10000  9798   -.78125     0                    0   -.05067254137247801
10000  9805   -.71875 27000                    0   -.05455132760107517
10000  9812   -.71875 22500                    0  -.006766380742192268
10000  9819      -.75  6000  -.03999999910593033  .0010289866477251053
10000  9826  -.828125  2221                    0   .013932201080024242
10000  9833    -.8125  2275  -.01886792480945587   .029932199977338314
10000  9840  -.671875  5750  -.06521739065647125  .0024406658485531807
10000  9847    -.5625 15000  -.10000000149011612  -.040419154800474644
10000  9854       -.5  1640                    0   -.09619818814098835
10000  9863  -.515625     0                    0   -.08869818784296513
10000  9869   -.46875  7900                    0   -.08393392711877823
10000  9876   -.46875  3100                    0   -.05810309201478958
10000  9883   -.46875  4850                    0  -.015227273106575012
10000  9890   -.40625   650                    0   -.05013468116521835
10000  9897   -.40625     0                    0  -.027407407760620117
10000  9904   -.40625    60                    0  -.027407407760620117
10000  9911    -.4375  1000   .07692307978868485  -.027407407760620117
10000  9918    -.4375     0                    0   .015384616330265999
10000  9925  -.359375  4500   -.0416666679084301   -.01428571529686451
10000  9932  -.390625     0                    0 -.0052277445793151855
10000  9939   -.34375 20730  -.11999999731779099 -.0049200527428183705
10000  9946   -.28125 14500   -.1818181872367859   -.04387176819727756
10000  9953  -.234375     0                    0   -.08528729938552715
10000  9960  -.234375    80                    0   -.09434527010307647
10000  9967  -.234375     0                    0   -.09465296193957329
10000  9974  -.203125     0                    0   -.10560967400670052
10000  9981  -.234375     0    .1538461595773697   -.03452381119132042
10000  9988   -.21875     0                    0  -.017087914049625397
10000  9995   -.21875  1000                    0  -.017087914049625397
10000 10002   -.21875 44171                    0   .017435897141695023
10000 10009   -.21875     0                    0   .017435897141695023
10000 10016   -.21875  1000                    0                     0
10000 10023   -.21875   500                    0                     0
10001  9506    -5.875     0  .021739130839705467                     .
10001  9511   -6.0625  6000   .03191489353775978   .007246376946568489
10001  9518   -6.1875  5200                    0    .01785452663898468
10001  9525   -6.0625  4600 -.010204081423580647   .015834324527531862
10001  9532    -6.125  1000                    0    .01585536385027808
10001  9539     -6.25  2500 -.009900989942252636   .014690207131934585
10001  9546     -6.25  5200  .010101010091602802 -.0008931897573347669
10001  9553   -6.3125  3700                    0   .005147214327735128
10001  9560     -6.25  3100                    0  .0031848042272031307
10001  9567     -6.25    20                    0 .00016338704153895378
10001  9574     -6.25  1800                    0   .005138634238392115
10001  9581   -6.3125   300                    0   .003157647792249918
10001  9588   -6.3125  1500                    0   .005099018570035696
10001  9595   -6.3125  1520                    0  .0020392155274748802
10001  9602   -6.3125  1000                    0  .0020392155274748802
10001  9609    -6.375   600                    0  .0019801980815827847
10001  9616    -6.375   800                    0  .0019801980815827847
10001  9623    -6.375  1200                    0  .0019801980815827847
10001  9630    -6.375  1850                    0  .0019801980815827847
10001  9637    -6.375  1000                    0                     0
10001  9644   -6.3125   600                    0 -.0024509804788976908
10001  9651   -6.1875  1300 -.019801979884505272 -.0024509804788976908
10001  9658   -6.1875  1630                    0 -.0030174372950568795
10001  9665   -6.1875     0                    0 -.0030174372950568795
10001  9672    -6.125    50                    0 -.0025866589276120067
end
format %d date2

[Clyde Schechter]

by permno (weeks), sort: replace cum_ret_4_weeks = . if _n < = 4

Thanks for the good examples with -dataex-.

[William Lisowski]

I think Clyde meant

by permno (date2), sort: replace cum_ret_4_weeks = . if _n < = 4

[Clyde Schechter]

Yes, William is right. That's what I meant. Sorry!

[Robert Picard]

Since the variable weeks is simply the number of the current observation within permno group after having been sorted by permno date2, both #6 and #7 will generate the same results (replace the first 4 observations with missing values).

To backtrack a bit, the proposed solution relies on the assumption that there are no gaps in the data. This is hard to confirm since date2 is a daily date and what's wanted is a window based on weeks. So the first thing to do is to reflect on what is a week. In the posted data example, the difference in days between the current observation and the previous one can be observed using:

. sort permno date2

. by permno: gen span = date2 - date2[_n-1]
(2 missing values generated)

. tab span

       span |      Freq.     Percent        Cum.
------------+-----------------------------------
          5 |          1        1.02        1.02
          6 |          1        1.02        2.04
          7 |         95       96.94       98.98
          9 |          1        1.02      100.00
------------+-----------------------------------
      Total |         98      100.00

A more direct solution would be to specify the 4 weeks prior window in days. If 4 weeks is 28 days, you can make the argument that a return date that is up to and including 31 days prior to the current return would fall within the 4 weeks window, in which case, you can get the desired results using:

rangestat (sum) cr31=ret (count) n31=ret, interval(date2 -31 -1) by(permno)

If you insist on working in weeks, you can use the span of days between observations to calculate the number of weeks. Since the span is not always 7, one approach is to round to the nearest week using:

by permno: gen span_in_weeks = round(span/7)
by permno: gen weeks = sum(span_in_weeks)
rangestat (sum) cum_ret_4_weeks=ret (count) nw=ret, interval(weeks -4 -1) by(permno)

Note that regardless of whether the interval is specified in days or weeks, the results are correct and based on the number of observations that fall within the interval. If you want the cumulative returns to be missing if there are fewer than 4 weeks, you can then use:

replace cum_ret_4_weeks = . if nw < 4

Of course if there are gaps in the data, all results based on fewer than 4 weeks of returns will be set to missing. We frequently see on this list similar examples with incomplete data within the windows where the poster specifies a minimum number of periods but I note that nobody has made a cogent argument as to why the initial observations within groups are to be treated on a different standard.

Putting all of the above together, starting from the data example from #4 but with gaps:

set seed 31435
drop if runiform() < .1

sort permno date2
by permno: gen span = date2 - date2[_n-1]
tab span
by permno: gen span_in_weeks = round(span/7)
by permno: gen weeks = sum(span_in_weeks)

rangestat (sum) cr31=ret (count) n31=ret, interval(date2 -31 -1) by(permno)
rangestat (sum) cum_ret_4_weeks=ret (count) nw=ret, interval(weeks -4 -1) by(permno)

replace cum_ret_4_weeks = . if nw < 4

and the results for the first 20 observations:

. list permno date2 ret span weeks-nw in 1/20

     +-------------------------------------------------------------------------------------+
     | permno       date2          ret   span   weeks         cr31   n31   cum_ret_~s   nw |
     |-------------------------------------------------------------------------------------|
  1. |  10000   08jan1986   -.02439024      .       0            .     .            .    . |
  2. |  10000   15jan1986    .04545455      7       1   -.02439024     1            .    1 |
  3. |  10000   22jan1986            0      7       2     .0210643     2            .    2 |
  4. |  10000   29jan1986   -.02816901      7       3     .0210643     3            .    3 |
  5. |  10000   05feb1986            0      7       4   -.00710471     4   -.00710471    4 |
     |-------------------------------------------------------------------------------------|
  6. |  10000   12feb1986   -.02173913      7       5    .01728553     4    .01728553    4 |
  7. |  10000   19feb1986     -.015625      7       6   -.04990814     4   -.04990814    4 |
  8. |  10000   26feb1986    -.0877193      7       7   -.06553314     4   -.06553314    4 |
  9. |  10000   05mar1986    .00909091      7       8   -.12508343     4   -.12508343    4 |
 10. |  10000   12mar1986            0      7       9   -.11599252     4   -.11599252    4 |
     |-------------------------------------------------------------------------------------|
 11. |  10000   19mar1986            0      7      10   -.09425339     4   -.09425339    4 |
 12. |  10000   26mar1986            0      7      11   -.07862839     4   -.07862839    4 |
 13. |  10000   09apr1986    .02272727     14      13            0     3            .    3 |
 14. |  10000   16apr1986            0      7      14    .02272727     3            .    3 |
 15. |  10000   23apr1986            0      7      15    .02272727     3            .    3 |
     |-------------------------------------------------------------------------------------|
 16. |  10000   30apr1986   -.01538462      7      16    .02272727     3            .    3 |
 17. |  10000   07may1986   -.01639344      7      17    .00734266     4    .00734266    4 |
 18. |  10000   14may1986    .01526718      7      18   -.03177806     4   -.03177806    4 |
 19. |  10000   21may1986   -.05645161      7      19   -.01651088     4   -.01651088    4 |
 20. |  10000   28may1986   -.03603604      7      20   -.07296249     4   -.07296249    4 |
     +-------------------------------------------------------------------------------------+

.

[Asad Rind]

Thanks for this explanation Robert Picard. I had the daily data i converted it to weekly by taking weekly averages. Although your code is way to much for a beginner like me to understand it. But i really appreciate your efforts. I am thankful to you for this code. I will definitely utilize this code in future.

[Nick Cox]

You should be able to understand most what is done by just going through line by line and looking at help files.

runiform() sum() and round() are functions, so make sure to look at help functions.

[Asad Rind]

Thanks Nick Cox I read the code line by line. It is very clear and understandable in this way. Robert Picard has described each and every step very clearly. I appreciate his all efforts. I was able to understand it after looking closely to your advise on functions. Once again thanks a lot to all of you for making this possible for me to get the solution just in 2 days.