Rangestat cumulative returns

Joelle van Esch

Join Date: Jun 2023

Posts: 7
#1

Rangestat cumulative returns

11 Jun 2023, 04:09

Hello everyone,

I am having trouble using rangestat for calculating cumulative returns.
My idea is to get cumulative returns from the AnnualReportDate until 7 days after but also the cumulative returns from the AnnualReportDate until 30 days after etcetera.
Previously I used rangejoin to combine my returns data to the AnnualReportDate and therefore used the interval -10 365 to expand the range and combine with returns of 10 observations before the annualreportdate until 365 observations after.

Now I want to use rangestat to recognise that the interval is for instance from day 0 (which is the annualreportdate) until 7 days after and then cumulate the returns from those observations.
This is the code I used up to now: rangestat (sum) sevend_a_cum_2 = return, by(ISIN) interval(AnnualReportDate 0 7)
However in my opinion it does not recognise the fact that they should cumulate the returns which are linked to the variable date_U from the annualreportdate until 7 days after.
I hope I explained my problem clearly and that you can help me how to solve this.

Thank you so much in advance,

Joëlle
Attached Files

statalist2.csv (552.1 KB, 1 view)
Tags: None

Nick Cox

Join Date: Mar 2014
Posts: 35468

11 Jun 2023, 04:32

Please show a data example using dataex -- not an attachment. Indeed I suspect you would be better off showing a very simplified example here with one ISIN, some simple values of return and dates and what you want or expect to get.

rangestat and rangejoin are from SSC.

For more on how to show data best and how to refer to community-contributed commands you use, please see https://www.statalist.org/forums/help#stata

I guess you might need to be using the Stata function sum() for what you want. rangestat shows sums, over the stated interval, which are not necessarily cumulative sums unless you specify say int(whatever . 0). For example if a variable is always 1, then its sum over rolling intervals of 8 observations with spacing 1 is always going to be 8, setting aside boundary problems. rangestat isn't going to show you 1 2 3 4 5 6 7 8 as a cumulative sum with the interval() specification you gave.

Code:

.   clear

. set obs 20
Number of observations (_N) was 0, now 20.

. gen foo = 1

. gen time = _n

. rangestat (sum) cumul=foo, int(time . 0)



. rangestat (sum) notcumul=foo, int(time 0 7)

. l

     +-------------------------------+
     | foo   time   cumul   notcumul |
     |-------------------------------|
  1. |   1      1       1          8 |
  2. |   1      2       2          8 |
  3. |   1      3       3          8 |
  4. |   1      4       4          8 |
  5. |   1      5       5          8 |
     |-------------------------------|
  6. |   1      6       6          8 |
  7. |   1      7       7          8 |
  8. |   1      8       8          8 |
  9. |   1      9       9          8 |
 10. |   1     10      10          8 |
     |-------------------------------|
 11. |   1     11      11          8 |
 12. |   1     12      12          8 |
 13. |   1     13      13          8 |
 14. |   1     14      14          7 |
 15. |   1     15      15          6 |
     |-------------------------------|
 16. |   1     16      16          5 |
 17. |   1     17      17          4 |
 18. |   1     18      18          3 |
 19. |   1     19      19          2 |
 20. |   1     20      20          1 |
     +-------------------------------+

.

Last edited by Nick Cox; 11 Jun 2023, 04:48.

Comment

Joelle van Esch

Join Date: Jun 2023

Posts: 7
#3

11 Jun 2023, 05:39

Excuse me.
as shown here, the annualreportdate is 14579 which I now define as day 0, I then want to create a variable called Returns_bseven containing the cumulative returns from the interval day -7 to 0 so that would be date_U number 14570 until date_U number 14579 (which is equal to the annualreportdate)
I then also want a variable Returns_aseven containing the cumulative returns from the interval day 0 to day 7 so from date_U 14579 until 14588

AnnualReportDate ISIN date_U return
14579 "AU000000RIO1" 14570 .
14579 "AU000000RIO1" 14571 -.2073158439087841
14579 "AU000000RIO1" 14572 -.24647780198753244
14579 "AU000000RIO1" 14573 1.6590162888112436
14579 "AU000000RIO1" 14574 -2.777775202268362
14579 "AU000000RIO1" 14577 -1.446431023733974
14579 "AU000000RIO1" 14578 -.16307330100987505
14579 "AU000000RIO1" 14579 2.504539212942758
14579 "AU000000RIO1" 14580 2.524785232474658
14579 "AU000000RIO1" 14581 1.7165808832975138
14579 "AU000000RIO1" 14584 -2.455013451179189
14579 "AU000000RIO1" 14585 -1.117412725037381
14579 "AU000000RIO1" 14586 -1.5771314231421862
14579 "AU000000RIO1" 14587 3.369337682435658
14579 "AU000000RIO1" 14588 .3460220857775198

Thank you!
Comment

Nick Cox

Join Date: Mar 2014
Posts: 35468

11 Jun 2023, 07:55

#3 isn't quite what I asked for, a simplified example using dataex with working shown, but it sheds some light on likely problems.

You have multiple observations with the same AnnualReportDate and ISIN, so the sum you ask for with the syntax in #1 will be based on all of those values, and is certainly no kind of cumulative sum taking on one value from each distinct srock and date.

To see this, experiment with

Code:

rangestat (sum) unwanted = return, by(ISIN) interval(AnnualReportDate 0 0)

which returns over a sum over duplicate observations, and is the same calculation as

Code:

egen double UNWANTED = total(return), by(ISIN AnnualReportDate)

If your AnnualReportDates are about a year apart, this will show the same result as

Code:

rangestat (sum) unwanted = return, by(ISIN) interval(AnnualReportDate 0 7)

Otherwise put, you seem to be thinking that your calculation will pay attention to date_U -- but that is nowhere mentioned in the syntax.

Perhaps this will help, or help you explain what different result you want. I have not tried to adjust for the fact that each 7 days includes at least two weekend days.

Code:

clear 
input AnnualReportDate str42 ISIN date_U return
14579 "AU000000RIO1" 14570 .
14579 "AU000000RIO1" 14571 -.2073158439087841
14579 "AU000000RIO1" 14572 -.24647780198753244
14579 "AU000000RIO1" 14573 1.6590162888112436
14579 "AU000000RIO1" 14574 -2.777775202268362
14579 "AU000000RIO1" 14577 -1.446431023733974
14579 "AU000000RIO1" 14578 -.16307330100987505
14579 "AU000000RIO1" 14579 2.504539212942758
14579 "AU000000RIO1" 14580 2.524785232474658
14579 "AU000000RIO1" 14581 1.7165808832975138
14579 "AU000000RIO1" 14584 -2.455013451179189
14579 "AU000000RIO1" 14585 -1.117412725037381
14579 "AU000000RIO1" 14586 -1.5771314231421862
14579 "AU000000RIO1" 14587 3.369337682435658
14579 "AU000000RIO1" 14588 .3460220857775198
end 

bysort ISIN (date_U): gen double wanted = return if date_U == AnnualReportDate 
by ISIN : replace wanted = wanted[_n-1] + return if inrange(date_U - AnnualReportDate, 1, 7)

list 


     +----------------------------------------------------------+
     | Annual~e           ISIN   date_U      return      wanted |
     |----------------------------------------------------------|
  1. |    14579   AU000000RIO1    14570           .           . |
  2. |    14579   AU000000RIO1    14571   -.2073158           . |
  3. |    14579   AU000000RIO1    14572   -.2464778           . |
  4. |    14579   AU000000RIO1    14573    1.659016           . |
  5. |    14579   AU000000RIO1    14574   -2.777775           . |
     |----------------------------------------------------------|
  6. |    14579   AU000000RIO1    14577   -1.446431           . |
  7. |    14579   AU000000RIO1    14578   -.1630733           . |
  8. |    14579   AU000000RIO1    14579    2.504539   2.5045393 |
  9. |    14579   AU000000RIO1    14580    2.524785   5.0293245 |
 10. |    14579   AU000000RIO1    14581    1.716581   6.7459054 |
     |----------------------------------------------------------|
 11. |    14579   AU000000RIO1    14584   -2.455014   4.2908919 |
 12. |    14579   AU000000RIO1    14585   -1.117413   3.1734792 |
 13. |    14579   AU000000RIO1    14586   -1.577131   1.5963478 |
 14. |    14579   AU000000RIO1    14587    3.369338           . |
 15. |    14579   AU000000RIO1    14588    .3460221           . |
     +----------------------------------------------------------+

Comment

Joelle van Esch

Join Date: Jun 2023

Posts: 7
#5

12 Jun 2023, 07:57

Thank you Nick, this command works! Indeed when AnnualReportDate is never equal to date_U because AnnualReportDate is on a holiday or a weekend the command does not work. So ideally would be that if this is the case that the cumulation starts from the closest day after the AnnualReportDate. Is there any way to fix that? I tried adjusting for weekends and a bcal but the bcal does not work

Kind regards,

Joëlle
Comment
Nick Cox

Join Date: Mar 2014

Posts: 35468
#6

12 Jun 2023, 11:51

Data example please!
Comment
Joelle van Esch

Join Date: Jun 2023

Posts: 7
#7

26 Jun 2023, 10:02

Hello Nick, sorry for the late response. In the mean time the issue has been solved using: egen double cumretMinus7to0 = total(cond(inrange(date_U - AnnualReportDate, -10, 0), logret, 0)), by (ISIN AnnualReportDate). Thank you for your responses!
Comment

Announcement