topic Re: Calculate stock buy and hold with both firm return and market benchmark in SAS Programming

Calculate stock buy and hold with both firm return and market benchmark

LucyDang — Sat, 29 Dec 2018 19:19:33 GMT

Dear all,

I have data like demonstrated in the code. "lret" is the stock return of a particular firm. "lbenchret" is the return of the market accordingly. This data is just the short version for demonstration. In the full sample, I have 36 months for more than 100 firms with all proper "lret" and "lbenchret".

data bhar;
input ticker $ Counthmonth EvntMonth AccMonth :yymmn6. lret lbenchret ;
datalines;
AA 0 201208 201208 0.0140682449 0.0195706004
AA 1 201208 201209 0.0338819793 0.0239470567
AA 2 201208 201210 -0.029220052 -0.019987832
AA 3 201208 201211 -0.018846242 0.0028426588
AA 4 201208 201212 0.031600109 0.0070434466
AA 5 201208 201301 0.0182653297 0.0491977573
AA 6 201208 201302 -0.033355557 0.0109998789
AA 7 201208 201303 0 0.0353553677
AA 8 201208 201304 -0.002350231 0.0179241582
AA 9 201208 201305 0.003523198 0.0205501713
AA 10 201208 201306 -0.083381591 -0.015112958
AA 11 201208 201307 0.0164873283 0.0482777567
AA 12 201208 201308 -0.028063068 -0.031798258
;
run;

I want to calculate the buy-and-hold return (BHAR) for 12 months/24 months/36 months of all firms base on lret and lbenchret. The formula is as follow:

BHAR(i) = { [(1+lret) of month 0] * [(1+lret) of month 1] * [(1+lret) of month 2] * [(1+lret) of month 3] * ... * [(1+lret) of month 12] } - { [(1+lbenchret) of month 0] * [(1+lbenchret) of month 1] * [(1+lbenchret) of month 2] * [(1+lbenchret) of month 3] * ... * [(1+lbenchret) of month 12] }

where: i = firm i, lret = return of firm i in particular month, lbenchret = return of market for firm i in particular month.

How can I create a SAS code to do so?

Thank you so much!

Re: Calculate stock buy and hold with both firm return and market benchmark

s_lassen — Sat, 29 Dec 2018 15:56:52 GMT

I do not have SAS on this computer, so I cannot test, but I think something like this should work:

data want;
  set bhar;
  by ticker;
  array loglret (0:12) 8 _temporary_;
  array logbench(0:12) 8 _temporary_;
  if first.ticker then
    call missing(of loglret(*),of logbench(*));
  _N_=mod(_N_,13);
  loglret(_N_)=log(1+lret);
  logbench(_N_)=log(1+lbenchret);
  n_months=n(of loglret(*)); /* number of months used in calculation */
  bhar=exp(sum(of loglret(*))-sum(of logbench(*));
run;

The idea is that the arrays are overwritten if there are more than 13 months for a given ticker.

Re: Calculate stock buy and hold with both firm return and market benchmark

Ksharp — Sun, 30 Dec 2018 11:18:12 GMT

data bhar;
input ticker $ Counthmonth EvntMonth AccMonth :yymmn6. lret lbenchret ;
datalines;
AA 0 201208 201208 0.0140682449 0.0195706004
AA 1 201208 201209 0.0338819793 0.0239470567
AA 2 201208 201210 -0.029220052 -0.019987832
AA 3 201208 201211 -0.018846242 0.0028426588
AA 4 201208 201212 0.031600109 0.0070434466
AA 5 201208 201301 0.0182653297 0.0491977573
AA 6 201208 201302 -0.033355557 0.0109998789
AA 7 201208 201303 0 0.0353553677
AA 8 201208 201304 -0.002350231 0.0179241582
AA 9 201208 201305 0.003523198 0.0205501713
AA 10 201208 201306 -0.083381591 -0.015112958
AA 11 201208 201307 0.0164873283 0.0482777567
AA 12 201208 201308 -0.028063068 -0.031798258
;
run;
data want;
 set bhar;
 by ticker;
 retain cum_lret cum_lben 1;
 if first.ticker then do;cum_lret=1;cum_lben=1;end;;
 lret=sum(lret,1);
 lbenchret=sum(lbenchret,1);
 cum_lret=cum_lret*lret;
 cum_lben=cum_lben*lbenchret;
 if last.ticker then BHAR=cum_lret-cum_lben;
 run;

Re: Calculate stock buy and hold with both firm return and market benchmark

mkeintz — Sun, 30 Dec 2018 18:00:20 GMT

You're using the term 12-month BHAR, but your formula is for 13-month BHAR. Which is the one you want?
If you're using the same benchmark for all firms (and for the same time range), you could create the benchmark 12, 24, and 36 month returns once, store them in an array and just lookup their values as needed. Then you only need to calculate the individual firms rolling window returns. Here's code that develops the complete benchmark cumulative "returns" (actually cumulative 1+returns) during the first ticker, and uses those values for all other tickers:

data bhar;
input ticker $ Counthmonth EvntMonth AccMonth :yymmn6. lret lbenchret ;
datalines;
AA 0 201208 201208 0.0140682449 0.0195706004
AA 1 201208 201209 0.0338819793 0.0239470567
AA 2 201208 201210 -0.029220052 -0.019987832
AA 3 201208 201211 -0.018846242 0.0028426588
AA 4 201208 201212 0.031600109 0.0070434466
AA 5 201208 201301 0.0182653297 0.0491977573
AA 6 201208 201302 -0.033355557 0.0109998789
AA 7 201208 201303 0 0.0353553677
AA 8 201208 201304 -0.002350231 0.0179241582
AA 9 201208 201305 0.003523198 0.0205501713
AA 10 201208 201306 -0.083381591 -0.015112958
AA 11 201208 201307 0.0164873283 0.0482777567
AA 12 201208 201308 -0.028063068 -0.031798258
;
run;


data want (drop=_:);
  array cumbnchmrk {-36:36} _temporary_ (73*1);
  array cumfirm    {-36:36} _temporary_ (73*1);
  set bhar;
  by ticker;
  retain _bnchmrkdone 0;
  if _bnchmrkdone=0 then cumbnchmrk{counthmonth}=  cumbnchmrk{counthmonth-1}*(1+lbenchret);
  if last.ticker then _bnchmrkdone=1;

  cumfirm{counthmonth}=cumfirm{counthmonth-1}*(1+lret);
  if counthmonth>=12 then bhar12 =  cumfirm{counthmonth}/cumfirm{counthmonth-12}
                                  - cumbnchmrk{counthmonth}/cumbnchmrk{counthmonth-12};

  if counthmonth>=24 then bhar24 =  cumfirm{counthmonth}/cumfirm{counthmonth-24}
                                  - cumbnchmrk{counthmonth}/cumbnchmrk{counthmonth-24};

  if counthmonth>=36 then bhar36 =  cumfirm{counthmonth}/cumfirm{counthmonth-36}
                                  - cumbnchmrk{counthmonth}/cumbnchmrk{counthmonth-36};
run;

This develops "true" 12 (not 13), 24, and 36-month rolling returns, but only for complete windows (i.e. no "short" windows).

Re: Calculate stock buy and hold with both firm return and market benchmark

LucyDang — Mon, 31 Dec 2018 08:03:52 GMT

Dear mkeintz, I want to calculate 12 months. Month "0" is the event month. That's why it's 13 months there in the data. Since your code has "countmonth-1", I think that I do not need to delete the event month before applying your code, right?

Btw, what does "73*1" mean?

How about in case that there are 2 events on 2 different days for the same month for the same company with the same ticker?

I applied your code to my whole data, but it did not work 😞

Re: Calculate stock buy and hold with both firm return and market benchmark

LucyDang — Mon, 31 Dec 2018 07:43:21 GMT

Dear Ksharp, what if I have two different event days but for the same 1 firm with the same ticker? How should I revise your code?