Calculating the cumulative return over a time window

[Jeffrey Choi]

Hi Statalist,

I am trying to calculate the cumulative return over some time window, suppose we assume t=0 at the event date, then I want to sum the return over t=-504 to t=-1 and t=0 to t=503

* Example generated by -dataex-. To install: ssc install dataex
clear
input str11 tre_ric double tre_ret float(an_t tregroup_id t dif)
"0336.HK"           . 20431 1 19701 -522
"0336.HK"  .003033501 20431 1 19702 -521
"0336.HK"           0 20431 1 19703 -520
"0336.HK"  -.01122798 20431 1 19704 -519
"0336.HK"  .012234456 20431 1 19705 -518
"0336.HK" -.006074148 20431 1 19708 -517
"0336.HK"  .014043077 20431 1 19709 -516
"0336.HK" -.008975406 20431 1 19710 -515
"0336.HK" -.003033501 20431 1 19711 -514
"0336.HK"  .019834245 20431 1 19712 -513
"0336.HK" -.002918003 20431 1 19715 -512
"0336.HK"  .004852503 20431 1 19716 -511
"0336.HK"           0 20431 1 19717 -510
"0336.HK"           0 20431 1 19718 -509
"0336.HK"  .002885688 20431 1 19719 -508
"0336.HK" -.022629746 20431 1 19722 -507
"0336.HK" -.001011163 20431 1 19723 -506
"0336.HK"           0 20431 1 19724 -505
"0336.HK" -.005091436 20431 1 19725 -504
"0336.HK" -.002053406 20431 1 19726 -503
"0336.HK"  .001027916 20431 1 19729 -502
"0336.HK" -.002058272 20431 1 19730 -501
"0336.HK" -.022222105 20431 1 19731 -500
"0336.HK" -.005462896 20431 1 19732 -499
"0336.HK" -.001100874 20431 1 19733 -498
"0336.HK" -.001103671 20431 1 19736 -497
"0336.HK" -.004442949 20431 1 19737 -496
"0336.HK"  .009933471 20431 1 19738 -495
"0336.HK" -.001092565 20431 1 19739 -494
"0336.HK"  -.00550444 20431 1 19740 -493
"0336.HK"  .007686829 20431 1 19743 -492
"0336.HK" -.009908289 20431 1 19744 -491
"0336.HK"  .005532489 20431 1 19745 -490
"0336.HK" -.005532489 20431 1 19746 -489
"0336.HK"  .005532489 20431 1 19747 -488
"0336.HK" -.005532489 20431 1 19750 -487
"0336.HK"  .004431615 20431 1 19751 -486
"0336.HK"  .016227086 20431 1 19752 -485
"0336.HK" -.016227086 20431 1 19753 -484
"0336.HK"           0 20431 1 19754 -483
"0336.HK"           0 20431 1 19757 -482
"0336.HK"           0 20431 1 19758 -481
"0336.HK" -.008908917 20431 1 19759 -480
"0336.HK"  .004477302 20431 1 19760 -479
"0336.HK"  .014240439 20431 1 19761 -478
"0336.HK" -.002160674 20431 1 19764 -477
"0336.HK" -.016557068 20431 1 19765 -476
"0336.HK" -.001126575 20431 1 19766 -475
"0336.HK"  .011136366 20431 1 19767 -474
"0336.HK"    .0043758 20431 1 19768 -473
"0336.HK"           0 20431 1 19771 -472
"0336.HK" -.001089824 20431 1 19772 -471
"0336.HK" -.003285976 20431 1 19773 -470
"0336.HK"           0 20431 1 19774 -469
"0336.HK" -.005532489 20431 1 19775 -468
"0336.HK" -.005603878 20431 1 19778 -467
"0336.HK"  -.01608682 20431 1 19779 -466
"0336.HK"  .003497693 20431 1 19780 -465
"0336.HK" -.011770219 20431 1 19781 -464
"0336.HK"  .009441556 20431 1 19782 -463
"0336.HK"   .00809627 20431 1 19785 -462
"0336.HK"           0 20431 1 19786 -461
"0336.HK"  .022333686 20431 1 19787 -460
"0336.HK" -.023481096 20431 1 19788 -459
"0336.HK" -.010465434 20431 1 19789 -458
"0336.HK" -.022958322 20431 1 19792 -457
"0336.HK" -.013868346 20431 1 19793 -456
"0336.HK" -.003860421 20431 1 19794 -455
"0336.HK"  .012736821 20431 1 19795 -454
"0336.HK"  -.00632172 20431 1 19796 -453
"0336.HK"  .023548122 20431 1 19799 -452
"0336.HK"  .009545318 20431 1 19800 -451
"0336.HK" -.004746435 20431 1 19801 -450
"0336.HK" -.013326678 20431 1 19802 -449
"0336.HK"  .006108321 20431 1 19803 -448
"0336.HK" -.002433029 20431 1 19806 -447
"0336.HK"           0 20431 1 19807 -446
"0336.HK"  .007258573 20431 1 19808 -445
"0336.HK" -.010933865 20431 1 19809 -444
"0336.HK"  .010933865 20431 1 19810 -443
"0336.HK" -.007258573 20431 1 19813 -442
"0336.HK"  .027189212 20431 1 19814 -441
"0336.HK"  .003424153 20431 1 19815 -440
"0336.HK" -.002279766 20431 1 19816 -439
"0336.HK" -.003442246 20431 1 19817 -438
"0336.HK"  .004583625 20431 1 19820 -437
"0336.HK" -.018632111 20431 1 19821 -436
"0336.HK"  .003554954 20431 1 19822 -435
"0336.HK"  .012791391 20431 1 19823 -434
"0336.HK"   .00682152 20431 1 19824 -433
"0336.HK" -.011429462 20431 1 19827 -432
"0336.HK"  .005752329 20431 1 19828 -431
"0336.HK"  .001141379 20431 1 19829 -430
"0336.HK"  .009024626 20431 1 19830 -429
"0336.HK"           0 20431 1 19831 -428
"0336.HK"           0 20431 1 19834 -427
"0336.HK"  .001115006 20431 1 19835 -426
"0336.HK" -.004477302 20431 1 19836 -425
"0336.HK"  .007805246 20431 1 19837 -424
"0336.HK" -.007805246 20431 1 19838 -423
end
format %td an_t
format %td t

.
So as you can see, I have the code/name of the company as "tre_ric"; "tre_ret" is the log return; "tregroup_id" is the unique identifier for each company; "an_t" is the event date; "t" is the date which the return is generated and "dif" is the time different between event date and the time, such that if an_t == t, dif==0.

Now my problem is: How can I compute the cumulative return from t=-504 to t=-1 for each company (i.e. for each "tregroup_id")?
Currently I have tried:

by tregroup_id: egen cum_ret=sum(tre_ret) if dif<=-504 & dif>=-1

but this code add up all the return I have for each company and assign the value for observation where dif is between (-504,-1), this is clearly not what I want.

What sort of code should I use if I want my condition

if dif<=-504 & dif>=-1

to be embedded into the accumulation of return? Thanks in advance.

Regards,
Jeffrey Choi

[Clyde Schechter]

I think what you want is:

by tregroup_id, sort: egen total_pre_return = total(cond(inrange(dif, -504, -1), tre_ret, .))

This will place a result in every observation associated with tregroup_id, but the total it calculates will include only those observations where -504 <= dif <= -1.

It isn't entirely clear to me that this is what you want, because you also refer to "accumulation of return" and you used the (now renamed) -egen, sum()- function. Both of those suggest to me that you what you actually want is a running sum starting at dif = -504 and running to dif = -1. So it's a bit unclear.

Do note that there is no longer an -egen, sum()- function. Some years back it was renamed -total()- to avoid confusion with the -gen, sum()- function which calculates a running sum, instead of a grand total. While -egen, sum()- still runs, it is discouraged because it is so easy to confuse with -gen, sum()-.

[Jeffrey Choi]

Sorry for not being clear enough. My objective is to sum all return for observation where -504<=dif<=-1, I think your code have done the work. Thank you for your help!!