How to calculate 3-day window buy-and-hold return

[AzamonTuscomer]

Good evening everyone,

I am trying to calculate 3-day-window (Day -1,Day 0, Day +1) market-adjusted buy-and-hold return with CRSP data.

I checked several posts which had a similar topic to mine but still can't figure out how to do it. 

My data are as below:

TICKER	DATE	RET	VWRETX
AAAP	2007/05/01	0.033	-0.0173
AAAP	2007/05/02	0.021	0.00147
AAAP	2007/05/03	-0.01	0.00165
AAAP	2007/05/04	0.016	0.03
AAAP	2007/05/05	0.023	0.045
AAAP	2007/05/06	-0.005	0.004
AAPL	2007/05/01	-0.056	0.0014
AAPL	2007/05/02	0.029	-0.007
AAPL	2007/05/03	0.1	-0.00016
AAPL	2007/05/04	0.001	0.045
AAPL	2007/05/05	-0.0821	0.0018
AAPL	2007/05/06	0.005	0.065

RET  = stock return, and VWRETX  = market return.

And the formula is: { [(1+RET) of day -1]*[(1+RET) of day 0]*[(1+RET) of day +1]  

                   -  { [1+VWRETX) of day -1]*[1+VWRETX) of day 0]*[1+VWRETX) of day +1] }

Also, I want to set each day as day 0 so that I can get buy-and-hold return for each day (somewhat similar to moving average). 

How can I do that? Any advice or comment will be highly appreciated.  

Thank you! 

[PGStats · Accepted solution]

One of many ways to do this. Merging data views:

data have;
input TICKER $	DATE:yymmdd.	RET	VWRETX;
format date yymmdd10.;
datalines;
AAAP	2007/05/01	0.033	-0.0173
AAAP	2007/05/02	0.021	0.00147
AAAP	2007/05/03	-0.01	0.00165
AAAP	2007/05/04	0.016	0.03
AAAP	2007/05/05	0.023	0.045
AAAP	2007/05/06	-0.005	0.004
AAPL	2007/05/01	-0.056	0.0014
AAPL	2007/05/02	0.029	-0.007
AAPL	2007/05/03	0.1	-0.00016
AAPL	2007/05/04	0.001	0.045
AAPL	2007/05/05	-0.0821	0.0018
AAPL	2007/05/06	0.005	0.065
;

data t_1 / view=t_1;
set have;
date = intnx("day", date, +1);
rename ret=ret_1 vwretx=vwretx_1;
run;


data t1 / view=t1;
set have;
date = intnx("day", date, -1);
rename ret=ret1 vwretx=vwretx1;
run;

data want;
merge t_1 have(in=ok) t1;
by ticker date;
if ok;
if n(of ret:) = 3 then
    ma_ret = (ret_1+1)*(ret+1)*(ret1+1) -
        (vwretx_1+1)*(vwretx+1)*(vwretx1+1);
keep ticker date ret vwretx ma_ret;
run;

proc print data=want noobs; run;

              TICKER          DATE      RET       VWRETX      ma_ret

               AAAP     2007-05-01     0.0330    -0.01730      .
               AAAP     2007-05-02     0.0210     0.00147     0.05838
               AAAP     2007-05-03    -0.0100     0.00165    -0.00625
               AAAP     2007-05-04     0.0160     0.03000    -0.04915
               AAAP     2007-05-05     0.0230     0.04500    -0.04648
               AAAP     2007-05-06    -0.0050     0.00400      .
               AAPL     2007-05-01    -0.0560     0.00140      .
               AAPL     2007-05-02     0.0290    -0.00700     0.07428
               AAPL     2007-05-03     0.1000    -0.00016     0.09551
               AAPL     2007-05-04     0.0010     0.04500    -0.03601
               AAPL     2007-05-05    -0.0821     0.00180    -0.19152
               AAPL     2007-05-06     0.0050     0.06500      .

[AzamonTuscomer]

Thank you so much for your help! I had a hard time with this problem. I really appreciate it.

[PeterClemmensen]

data have;
input TICKER $ DATE :yymmdd. RET VWRETX;
format date yymmdd10.;
datalines;
AAAP 2007/05/01 0.033 -0.0173
AAAP 2007/05/02 0.021 0.00147
AAAP 2007/05/03 -0.01 0.00165
AAAP 2007/05/04 0.016 0.03
AAAP 2007/05/05 0.023 0.045
AAAP 2007/05/06 -0.005 0.004
AAPL 2007/05/01 -0.056 0.0014
AAPL 2007/05/02 0.029 -0.007
AAPL 2007/05/03 0.1 -0.00016
AAPL 2007/05/04 0.001 0.045
AAPL 2007/05/05 -0.0821 0.0018
AAPL 2007/05/06 0.005 0.065
;

data want;
    if _N_=1 then do;
        declare hash h(dataset:'have');
        h.definekey('ticker', 'date');
        h.definedata('ret', 'vwretx');
        h.definedone();
    end;
    
    set have;
    ret0=ret; vwretx0=vwretx;

    rc=h.find(key:ticker, key:date-1);
    ret_1=ifn(rc=0, ret, .);
    vwretx_1=ifn(rc=0, vwretx, .);

    rc=h.find(key:ticker, key:date+1);
    ret1=ifn(rc=0, ret, .);
    vwretx1=ifn(rc=0, vwretx, .);

    ma_ret = (ret_1+1)*(ret0+1)*(ret1+1) -
        (vwretx_1+1)*(vwretx0+1)*(vwretx1+1);

    keep ticker date ret vwretx ma_ret;
    format ma_ret 8.5;
run;

Result:

ticker  date        ret      vwretx   ma_ret 
                                             
AAAP    2007-05-01  0.021    0.00147  .      
AAAP    2007-05-02  -0.01    0.00165  0.05838
AAAP    2007-05-03  0.016    0.03    -0.00625
AAAP    2007-05-04  0.023    0.045   -0.04915
AAAP    2007-05-05  -0.005   0.004   -0.04648
AAAP    2007-05-06  0.023    0.045    .      
AAPL    2007-05-01  0.029   -0.007    .      
AAPL    2007-05-02  0.1     -0.00016  0.07428
AAPL    2007-05-03  0.001    0.045    0.09551
AAPL    2007-05-04  -0.0821  0.0018  -0.03601
AAPL    2007-05-05  0.005    0.065   -0.19152
AAPL    2007-05-06  -0.0821  0.0018   .       

[AzamonTuscomer]

Thank you very much for your help! I really appreciate it!!