Adjusting shares in holdings data

A constant problem in using holdings data is adjusting the shares held for stock splits between the reported date and any date in the future.

For instance, if you are calculating the change in shares of Adobe held by a mutual fund from January 1, 2005 to December 31, 2005, you need to adjust the shares held on January 1 for splits over the year before you can subtract them from the shares held in December.

The way to do this is to use CRSP's CFACSHR variable, available in the MSF file. To adjust shares reported on reported date to another date, desired date, I say

shares_reported_adjusted_to_desired_date=shares_reported*cfacshr_reported_date/cfacshr_desired_date

Suppose the fund held 2000 shares of Adobe(PERMNO 75510) on January 1, 2005 and 4500 shares on December 31, 2005.

If I say

proc print data=crsp.msf;
   where permno=75510 and date>='01Dec2004'd and date<='31Dec2005'd;
   var permno date ret prc cfacshr;
run;

SAS produces:

PERMNO        DATE            RET             PRC    CFACSHR	
													
 75510    20041231       0.036204        62.74000       2   	
 75510    20050131      -0.093083        56.90000       2   	
 75510    20050228       0.085237        61.75000       2   	
 75510    20050331       0.087976        67.17000       2   	
 75510    20050429      -0.114634        59.47000       2   	
 75510    20050531       0.113839        33.12000       1   	
 75510    20050630      -0.136171        28.61000       1   	
 75510    20050729       0.036001        29.64000       1   	
 75510    20050831      -0.087719        27.04000       1   	
 75510    20050930       0.103920        29.85000       1   	
 75510    20051031       0.080402        32.25000       1   	
 75510    20051130       0.011163        32.61000       1   	
 75510    20051230       0.133395        36.96000       1

Apparently, there was a 2-for-1 stock split between the end of April and the end of May. The 2000 shares in January would have become 4000 shares in December. The formula above yields:

shares in January adjusted to December=shares in january*January cfacshr/December cfacshr

Or

shares in January adjusted to December=shares in january*2/1

Or

shares in January adjusted to December=2000*2/1=4000

Therefore the increase in shares is 500 ``December shares''. The value of this trade, assuming it happened in December, is 500*36.96, which is the December price.