Calculating return for multiple firms
Posted: 14 October 2014 12:03 PM   [ Ignore ]
Newbie
Rank
Total Posts:  7
Joined  2014-10-14

Hi,

I am trying to calculate the monthly returns based on a data sample that contains: CUSIP, datadate, monthly closing price. There are multiple CUSIPs (i.e. multiple firms) and of course multiple rows of datadate and price per CUSIP. The output should be two new columns that contain the return and the cumulative return so far, for example:

CUSIP           DATADATE           PRCCM           Return                                 Cumulative return
00077R         30SEP1997         27.6250         0             0
00077R         31OCT1997         23.5000         (23.500/27.6250)-1           (23.500/27.6250)-1
00077R         30NOV1997         24.0000         (24.000/23.5000)-1           [(23.500/27.6250)-1]+[(24.000/23.5000)-1]
* same for next CUSIP / firm etc.

My - extremely simple - example in code looks like this:

data have;
do 
until (targetCUSIP last.targetCUSIP);
  
cumulative_return 0;
  do 
until (datadate last.datadate);
  return = (
datadate/lag(datadate))-1;
  
cumulative_return cumulative_return + return;
  
end;
 
end;
run

I think I need something like “DISTINCT” in the first “do until”, however am not sure how to do this.

Any help is much appreciated!

Profile
 
 
Posted: 14 October 2014 01:05 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Art,

How about this (assuming the dataset with cusip datadate and prccm is called art1):

data art2;
set art1;
retain prev_prccm;/* remembers this across the rows */
by cusip/* repeat for each firm */
if first.cusip then prev_prccm = .;
return = 
prccm prev_prccm/* will be missing for first month */
prev_prccm prccm/* remember for next month */
run

The ‘by cusip’ is a key ingredient here: it will repeat the code-block for each cusip, so that you don’t need ‘bookkeeping’ to check if cusip changes. Obviously, the dataset needs sorting by cusip and datadate, and adjusting for stock splits.

An alternative way that I quite like is proc sql:

proc sql;
  
create table art2 as select a.*, a.prccm b.prccm as return 
  
from art1 aart1 b
  where a
.datadate -35 <= b.datadate <= a.datadate 28 and a.cusip b.cusip;
quit

This matches the input dataset against itself (both ‘a’ and ‘b’ are the same), and will take all records in a (a.*), and will compute the return based on b.prccm and a.prccm. The datadate range filter is to make sure ‘b’ is the prior month. (please doublecheck because it is easy to make mistakes, for example by including b.datadate as datadate_prev and inspect if it is indeed the prior month.)

best regards,

Joost

 

 

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 14 October 2014 01:59 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  7
Joined  2014-10-14

Thank you Joost, very helpful! I have got the return calculation working in both codes, however I am now stuck with a little addition I made to calculate the cumulative return. The cumReturn column just remains empty and I have no idea why. If I set

cumReturn = return + prev_cumReturn
to
cumReturn = return; 

, I do get a column identical to return, however the below code leaves a blank column without any error messages for cumReturn.

It must be very simple, what am I missing?

data WIP.Returns (drop prev_prccm prev_cumReturn);
set WIP.SDC_CS_CRSP_WV;
retain prev_cumReturn prev_prccm;
by targetCUSIP
if 
first.targetCUSIP then 
 
do;
 
prev_prccm = .;
    
prev_cumReturn = .;
    
end;
return = (
prccm prev_prccm)-1
cumReturn = return + prev_cumReturn;
prev_prccm prccm
prev_cumReturn cumReturn;
run
Profile
 
 
Posted: 14 October 2014 03:26 PM   [ Ignore ]   [ # 3 ]
Newbie
Rank
Total Posts:  7
Joined  2014-10-14

Fixed the above by using sum() instead of +. I believe it had to do with the fact that SAS propagates missing values in arithmetic expression, which sum() does not.

Profile
 
 
Posted: 14 October 2014 04:10 PM   [ Ignore ]   [ # 4 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Art,

Curious indeed…

I would set cum_ret to 0 instead of missing. Like this:

data art2;
set art1;
retain prev_prccm cum_return;/* remembers this across the rows */
by cusip/* repeat for each firm */
if first.cusip then prev_prccm = .;
if 
first.cusip then cum_return 0;
return = 
prccm prev_prccm/* will be missing for first month */
cum_return cum_return + return;
prev_prccm prccm/* remember for next month */
run

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 14 October 2014 04:43 PM   [ Ignore ]   [ # 5 ]
Newbie
Rank
Total Posts:  7
Joined  2014-10-14

Hi Joost,

It becomes more curious: I tried that as well when problem solving, yet it didn’t fix it…

All sorted now though, thanks again for the quick responses!

Profile
 
 
Posted: 14 October 2014 04:49 PM   [ Ignore ]   [ # 6 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Ok, glad it worked out! smile

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile