Portfolio Formation and Cumulative Ret (1,3,6,12,24mths) with Analysts’ Forecast Revision
Posted: 06 August 2015 04:23 AM   [ Ignore ]
Newbie
Rank
Total Posts:  4
Joined  2015-07-29

Hi all,

I am using IBES analysts forecast revision and CRSP for stock prices.

I am quite new to SAS and I have been trying to figure out how to form portfolio of analysts forecast revision to examine stock price drift for 1month, 3months, 6months, 12months and 24months. What I am trying to achieve is to hold stocks at time t, and buy or sell stocks according to the degree of analysts’ forecast revision (1 = lowest (SELL) and 5 = highest (BUY)) at time t, and find the cumulative returns for 1,3,6,12 and 24 months of the stocks bought or sold at time t. (Without changing the portfolio). In other words, stocks will stay in the portfolio at time t and I will buy or sell according to the ranking of analysts’ forecast revision.

HOWEVER, I only can manage to find stock returns at time t to analysts’ forecast revision portfolio at time t, and I cant get the codes for the cumulative returns.

Below are my codes:

/* Step 1. Specifying Options */
%let J=6; /* Formation Period Length: J can be between 3 to 12 months - can be adjusted */
%let K=6; /* Holding   Period Length: K can be between 3 to 12 months - can be adjusted*/
%let begdate=01JAN1994;
%let enddate=31DEC2014;
run;


/* Step 2. Assign Ranks to the Next 6 (K) Months After Portfolio Formation */
/* Forecast_revision_portfolio is the portfolio rank variable taking values between 1 and 5: */
/*      1 - the lowest momentum group: Losers   */
/*      5 - the highest momentum group: Winners */
data getr_2 ;
set getr_2;
HDATE1 = intnx(“MONTH”,date, 0,“B”)-1;
HDATE2 = intnx(“MONTH”,date,&k-1,“E”);
format HDATE1 HDATE2 monyy.;
label HDATE1= “First Holding Date”;
label HDATE2= “Last Holding Date”;
run;

/* Portfolio returns are average monthly returns rebalanced monthly */
proc sql;
  create table getr_3
  as select distinct*
  from getr_2 as a, recency as b
  where a.cusip=b.cusip
  and a.HDATE1<=b.date<=a.HDATE2
order by cusip, date;
quit;

/* Step 4. Calculate Equally-Weighted Average Monthly Returns */
proc sort data=getr_3 nodupkey; by cusip date analys; run;
proc sort data=getr_3; by date forecast_revision_rank HDATE1;run;

/* Calculate Equally-Weighted returns across portfolio stocks */
/* Every date, each MOM group has J portfolios identified by formation date */
proc means data = getr_3 noprint;
  by date forecast_revision_rank HDATE1;
  var mean_returns;
  output out = umd3 mean=mean_returns;
run;

/* Portfolio average monthly returns */
proc sort data=umd3; by date Forecast_revision_rank;
  where year(date) >= year(”&begdate;“d);
run;

/* Create one return series per MOM group every month */
proc means data = umd3 noprint;
  by date forecast_revision_rank;
  var mean_returns;
  output out = ewretdat mean= ewret std = ewretstd;
run;

proc sort data=ewretdat; by forecast_revision_rank ; run;


Title “Table 1: Returns of Analysts’ Forecast Revision Portfolios”;
Title2 “Portfolios based on 6 months lagged return and held for 6 months”;

proc means data=ewretdat n mean t probt;
  class Forecast_revision_rank;
  var ewret;
run;

/* Step 5. Calculate Long-Short Portfolio Returns */
proc sort data=ewretdat; by date Forecast_revision_rank; run;
proc transpose data=ewretdat out=ewretdat2
    (rename = (_1=SELL _2=PORT2 _3=PORT3 _4=PORT4 _5=BUY)
    drop=_NAME_ _LABEL_);
  by date;
  id Forecast_revision_rank;
  var ewret;
run;

/* Compute Long-Short Portfolio Cumulative Returns */
data ewretdat3;
set ewretdat2;
by date;
LONG_SHORT=BUY-SELL;
retain CUMRET_BUY CUMRET_SELL CUMRET_LONG_SHORT 0;
CUMRET_BUY   = (CUMRET_BUY+1)*(BUY+1)-1;
CUMRET_LOSERS     = (CUMRET_SELL +1)*(SELL +1)-1;
CUMRET_LONG_SHORT = (CUMRET_LONG_SHORT+1)*(LONG_SHORT+1)-1;
format BUY SELL LONG_SHORT PORT: CUMRET_: percentn12.1;
run;

proc means data=ewretdat3 n mean t probt;
var BUY SELL LONG_SHORT;
run;

Below is a screen shot of the table:
DATE CUSIP COMNAM Adjusted_Price high days_since_52WH Returns mean_returns RR obsno analys fpedats Forecast_Revision forecast_revision_Rank HDATE1 HDATE2
Jun-06 00036020 AAON INC 5.068641945 5.635555691 37 0.009441376 0.004199479 4 121980 112011 30-Jun-06 0.33408324 5 May-06 Nov-06
Jun-06 00036020 AAON INC 5.068641945 5.635555691 37 0.009441376 0.004199479 4 121980 79788 30-Jun-06 0.466816648 5 May-06 Nov-06
Nov-06 00036020 AAON INC 5.515061743 5.730370416 11 0.034074077 0.00757694 4 Oct-06 Apr-07


Jan-07 00036020 AAON INC 5.428148058 5.730370416 51 -0.006148285 0.002447311 3 Dec-06 Jun-07

 


Feb-07 00036020 AAON INC 5.485432189 5.827160494 7 0.000720737 0.000650488 4 Jan-07 Jul-07

 


May-07 00036020 AAON INC 5.868641794 5.929876634 13 -0.003020139 0.005742444 4 Apr-07 Oct-07

 


Jul-07 00036020 AAON INC 5.908148118 6.806913399 9 -0.009602679 -0.002715823 5 Jun-07 Dec-07

 


Aug-07 00036020 AAON INC 6.204444603 6.816789792 19 0.030004949 0.002654603 5 Jul-07 Jan-08

 


Oct-07 00036020 AAON INC 5.410370438 6.816789792 61 0.033389935 -0.003414025 3 Sep-07 Mar-08

 


Nov-07 00036020 AAON INC 5.638518722 6.816789792 82 0.004221632 0.002566941 3 Oct-07 Apr-08

 


Dec-07 00036020 AAON INC 5.872592502 6.816789792 102 -0.025086091 0.00248648 2 121980 107780 31-Dec-07 -0.320916905 1 Nov-07 May-08

 


Please help my guardian angels. Thank You.

Profile
 
 
Posted: 08 August 2015 08:55 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi nochtan,

It is difficult for me to replicate the problem. Can you narrow down the problem, and make small test (fake) datasets using code like this (obviously needs different variables/values with analyst data)?

data returns;
  
input @01 id       
   
@03 date  MMDDYY10.
        @
14 return;
format date date9.;
datalines;
1 10/31/2013 0.01
1 11
/30/2013 0.02
1 12
/31/2013 0.03
1 01
/31/2014 -0.01
1 02
/28/2014 0.01
2 10
/31/2013 -0.01
2 11
/30/2013 0.02
2 12
/31/2013 0.01
2 01
/31/2014 -0.02
2 02
/28/2014 -0.03
2 03
/31/2014 0.02 
run
;

data yearly;
  
input @01 id        
   
@03 date  MMDDYY10.
        @
14 equity;
format date date9.;
datalines;
1 12/31/2011 8
1 12
/31/2012 10
1 12
/31/2013 11
2 12
/31/2012 30
2 12
/31/2013 28
run

Me (and others) would then be able to run the code on the test-data, and switching to ‘real’ IBES data should be easy as well.

Thanks,

Joost

 

 Signature 

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

Profile