Cumulative returns per month excluding last observation

[Luis Saias]

Hello, I want to calculate the cumulative returns of each company of the last twelve months (excluding the month itself).
So basically given the example in attach for company 1000 it should not return nothing until 31/mar/1987 (month 12). In mar it should return -0.90714 given the calculation [(1+(-0,25714))*(1+0,365385)*(1+(-0,09859))*...*(1+(-0,37736))*(1+(-0,21212))]-1. In April it should return 0,092308 given the calculation [(1+0,365285)*(1+(-0,09859))*...*(1+(-0,38462))]-1. And so on.
It should do this per company.

I used the following code but it misses 1 observation per company:

sort permno date
gen id=_n
sort permno date
. by permno: gen newid = 1 if _n==1
. replace newid = sum(newid)
. replace newid = . if missing(permno)
sort permno date
. by permno: generate sumlogr = sum(ln(1 + ret))
. by permno: gen sumlogr1=sumlogr[_n-1]
xtset newid id
generate r12 = exp(s12.sumlogr1) - 1

I also have the problem that sometimes there are month that do not exist.. If possible in other code could you do the exact something but returning the cumulative returns for the last year instead of last 12 months? So in the example in march it should return -0,365385 given the calculation [(1+0,365385)*(1+(-0,09859))*...*(1+(0,0,21212))]-1. This means for each month it starts in that month in the previous year and ends the closest possible (in this case January 1987).

Thanks and I hope you can help me!

[Nick Cox]

Unreadable screenshot! Please read http://www.statalist.org/forums/help#stata to understand why this does not help (nearly as much as you think) and what to do instead.

[Luis Saias]

Okok thanks for the feedback. Here is the data as asked in: http://www.statalist.org/forums/help#stata:


input double permno long date double ret
10000 9555 -.2571428716182709
10000 9586 .36538460850715637
10000 9616 -.09859155118465424
10000 9646 -.22265625
10000 9677 -.005025125574320555
10000 9708 -.08080808073282242
10000 9737 -.6153846383094788
10000 9769 -.05714285746216774
10000 9800 -.24242424964904785
10000 9828 .05999999865889549
10000 9861 -.37735849618911743
10000 9891 -.21212121844291687
10000 9951 -.38461539149284363
10000 9981 -.0625
10000 10010 -.06666667014360428
10001 9555 .020408162847161293
10001 9586 .025200003758072853
10001 9616 .009900989942252636
10001 9646 -.009803921915590763
10001 9677 -.013069307431578636
10001 9708 -.010204081423580647
10001 9737 .07216494530439377
10001 9769 -.003076923545449972


Hello, I want to calculate the cumulative returns of each company of the last twelve months (excluding the month itself).
So basically given the example for company 1000 it should not return nothing until 31/mar/1987 (month 12). In mar it should return -0.90714 given the calculation [(1+(-0,25714))*(1+0,365385)*(1+(-0,09859))*...*(1+(-0,37736))*(1+(-0,21212))]-1. In April it should return 0,092308 given the calculation [(1+0,365285)*(1+(-0,09859))*...*(1+(-0,38462))]-1. And so on.
It should do this per company.

I used the following code but it misses 1 observation per company:

sort permno date
gen id=_n
sort permno date
. by permno: gen newid = 1 if _n==1
. replace newid = sum(newid)
. replace newid = . if missing(permno)
sort permno date
. by permno: generate sumlogr = sum(ln(1 + ret))
. by permno: gen sumlogr1=sumlogr[_n-1]
xtset newid id
generate r12 = exp(s12.sumlogr1) - 1

I also have the problem that sometimes there are month that do not exist.. If possible in other code could you do the exact something but returning the cumulative returns for the last year instead of last 12 months? So in the example in march it should return -0,365385 given the calculation [(1+0,365385)*(1+(-0,09859))*...*(1+(0,0,21212))]-1. This means for each month it starts in that month in the previous year and ends the closest possible (in this case January 1987).

Thanks and I hope you can help me!

[Nick Cox]

Thanks for the more readable data example.

I got lost part way through this as I don't deal with financial data in general or returns in particular. What seems clear is that you have in essence monthly data. I'd therefore xtset in terms of permno and monthly date. There is no need to do it in terms of home-brewed variables which as you create them will (as you sense) not be smart about gaps.


Here is your example pushed a little bit forward. Note the use of CODE delimiters.

clear 
input double permno long date double ret
10000 9555 -.2571428716182709
10000 9586 .36538460850715637
10000 9616 -.09859155118465424
10000 9646 -.22265625
10000 9677 -.005025125574320555
10000 9708 -.08080808073282242
10000 9737 -.6153846383094788
10000 9769 -.05714285746216774
10000 9800 -.24242424964904785
10000 9828 .05999999865889549
10000 9861 -.37735849618911743
10000 9891 -.21212121844291687
10000 9951 -.38461539149284363
10000 9981 -.0625
10000 10010 -.06666667014360428
10001 9555 .020408162847161293
10001 9586 .025200003758072853
10001 9616 .009900989942252636
10001 9646 -.009803921915590763
10001 9677 -.013069307431578636
10001 9708 -.010204081423580647
10001 9737 .07216494530439377
10001 9769 -.003076923545449972
end 
format %td date 
gen mdate = mofd(date) 
format %tm mdate 
xtset permno mdate 

list, sepby(permno)
 
     +-------------------------------------------+
     | permno        date          ret     mdate |
     |-------------------------------------------|
  1. |  10000   28feb1986   -.25714287    1986m2 |
  2. |  10000   31mar1986    .36538461    1986m3 |
  3. |  10000   30apr1986   -.09859155    1986m4 |
  4. |  10000   30may1986   -.22265625    1986m5 |
  5. |  10000   30jun1986   -.00502513    1986m6 |
  6. |  10000   31jul1986   -.08080808    1986m7 |
  7. |  10000   29aug1986   -.61538464    1986m8 |
  8. |  10000   30sep1986   -.05714286    1986m9 |
  9. |  10000   31oct1986   -.24242425   1986m10 |
 10. |  10000   28nov1986          .06   1986m11 |
 11. |  10000   31dec1986    -.3773585   1986m12 |
 12. |  10000   30jan1987   -.21212122    1987m1 |
 13. |  10000   31mar1987   -.38461539    1987m3 |
 14. |  10000   30apr1987       -.0625    1987m4 |
 15. |  10000   29may1987   -.06666667    1987m5 |
     |-------------------------------------------|
 16. |  10001   28feb1986    .02040816    1986m2 |
 17. |  10001   31mar1986        .0252    1986m3 |
 18. |  10001   30apr1986    .00990099    1986m4 |
 19. |  10001   30may1986   -.00980392    1986m5 |
 20. |  10001   30jun1986   -.01306931    1986m6 |
 21. |  10001   31jul1986   -.01020408    1986m7 |
 22. |  10001   29aug1986    .07216495    1986m8 |
 23. |  10001   30sep1986   -.00307692    1986m9 |
     +-------------------------------------------+

[Robert Picard]

Here's how to do this using tsegen and rangestat, both available from SSC. Note that 1987m2 is missing for permno 10000. So with the example data, there are no results computed on a complete set of 12 monthly observations. It's up to you to determine how many observations are needed for the computation to be considered valid.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno str9 date_str double ret
10000 "28feb1986"   -.2571428716182709
10000 "31mar1986"   .36538460850715637
10000 "30apr1986"  -.09859155118465424
10000 "30may1986"           -.22265625
10000 "30jun1986" -.005025125574320555
10000 "31jul1986"  -.08080808073282242
10000 "29aug1986"   -.6153846383094788
10000 "30sep1986"  -.05714285746216774
10000 "31oct1986"  -.24242424964904785
10000 "28nov1986"   .05999999865889549
10000 "31dec1986"  -.37735849618911743
10000 "30jan1987"  -.21212121844291687
10000 "31mar1987"  -.38461539149284363
10000 "30apr1987"               -.0625
10000 "29may1987"  -.06666667014360428
10001 "28feb1986"  .020408162847161293
10001 "31mar1986"  .025200003758072853
10001 "30apr1986"  .009900989942252636
10001 "30may1986" -.009803921915590763
10001 "30jun1986" -.013069307431578636
10001 "31jul1986" -.010204081423580647
10001 "29aug1986"   .07216494530439377
10001 "30sep1986" -.003076923545449972
end

* generate a monthly date and declare data to be panel
gen mdate = mofd(date(date_str,"DMY")) 
format %tm mdate 
xtset permno mdate 

* cumulative returns over past 12 months using tsegen (from SSC)
gen double lret = log(1+ret)
tsegen double prod = rowtotal(L(1/12).lret)
tsegen nobs = rownonmiss(L(1/12).lret)
replace prod = exp(prod) - 1

* cumulative returns over past 12 months using rangestat (from SSC)
rangestat (sum) prod2=lret (count) nobs2=lret, interval(mdate -12 -1) by(permno)
replace prod2 = exp(prod2) - 1

* spot check 1987m3 for permno 1000
sum lret if inrange(mdate, ym(1986,3) , ym(1987,2) ) & permno == 10000
dis exp(r(sum)) - 1
list if mdate == ym(1987,3) & permno == 10000