DATA Step, Macro, Functions and more

Rolling 36 month cumulative return calculation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Rolling 36 month cumulative return calculation

Hi guys. 


I am having a problem with calculating rolling window cumulative returns. 

I have a panel data set with daily returns of approximately 40 different companies and 8 years of data. None of the companies have the same inception date. 

 

I would like to create a variable that shows the moving cumulative returns for the past 36 month for each company. If the company has not been in the sample for 36 months it should just be the cumulative return for total period the company has been the data set. Once it reaches 36 month of data, it should start calculating a rolling 36 month (750 business day) cumulative return.

 

I have no problem calulating a variable with a return series since inception. But I have trouble, when the base date has to move one day forward in time. 

 

My data looks like this: 

 

Data=Rolling: 

date                     firm                retun (daily)

1/1/2010                a                      4%

1/2/2010                a                      3%

.

.

1/1/2010                b                       1%

1/2/2010                b                      -2%  

.

.

04/24/2015            c                       3% 

04/25/2015            c                      -5%

 

 

Do you guys have any suggestions how to creating a variable with a rolling compounded cumulative return series with a window size of 36 month (approximately 750 week days of observations)? I would have to do a graph, when the variable has been created, but that shold not be a problem. 

 

I really hope you can help, 


best, Joe. 


Accepted Solutions
Solution
‎07-05-2017 05:03 AM
Trusted Advisor
Posts: 1,630

Re: Rolling 36 month cumulative return calculation

[ Edited ]

PROC EXPAND let's you calculate a rolling N month cumulative product. That sounds exactly like what you want.

View solution in original post


All Replies
PROC Star
Posts: 553

Re: Rolling 36 month cumulative return calculation

Occasional Contributor
Posts: 10

Re: Rolling 36 month cumulative return calculation

I would have loved that to work, but it is not a moving average, but a moving compoundig of the return that I am looking for :/... 

Solution
‎07-05-2017 05:03 AM
Trusted Advisor
Posts: 1,630

Re: Rolling 36 month cumulative return calculation

[ Edited ]

PROC EXPAND let's you calculate a rolling N month cumulative product. That sounds exactly like what you want.

Occasional Contributor
Posts: 10

Re: Rolling 36 month cumulative return calculation

Proc expand using 'movprod [Window]' did the job! Thanks :-) 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 125 views
  • 2 likes
  • 3 in conversation