Creating Calendar-Time Portfolios Following Events

[Robert Prins]

Dear all,

I am currently working on a study on the effect of televised stock recommendations in the Netherlands. As part of my analysis, I need to create calendar time portfolios based on these recommendations in order to examine the possible long term excess returns caused by the recommendations. Here is where I hope anyone is able to help:

On an eventdate (i.e date of broadcast) a certain recommendation is given about stock X. The recommendation is represented by the variable ADVICE in my dataset (see the DataEx below) where an ADVICE of 1 represents a “strong buy” advice, 2 a “buy” advice, 3 a “sell” advice and 4 a “strong sell” advice. I wish to create separate equally weighted portfolios for each type of recommendation that looks as follows: on the date of the recommendation, companies with the same value for ADVICE will be put in the same portfolio. The stocks will be bought using the opening price (PO in my dataset) of the same date and will be held within the portfolio for several durations (60, 120, 180, 240 and 300 trading days) after which the stock is sold. Stocks with ADVICE values 3 and 4 (sell recommendations imply that short positions need to be taken, hence the portfolios of advices 3 and 4 only contain short positions.
I wish to create portfolio returns of the separate portfolios (this means for each type of recommendation, five portfolios which represent the different holding durations) which I can use in CAPM and four-factor regressions in order to calculate excess returns of the portfolios. My dataset looks as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ADVICE long companyid float(eventdate date) double(P PO) float(ret mrktret) double(MOM SMB HML rf) float Mkt_rf
1 3 16320 16320  36.6 35.93   .018753996    .005853557 -.43  -.16 -.13 .01  -.0041464427
1 3 16320 16321  36.7 36.65  .0027285146    .003200389 -.23   .07  .16 .01   -.006799611
1 3 16320 16322 35.86 36.48  -.023154287  -.0013875903 -.01   .14 -.11 .01    -.01138759
1 3 16320 16323  35.5 35.65  -.010089772    -.01034675  .33   .31  .23 .01    -.02034675
1 3 16320 16324  35.2 35.25  -.008486614    .005626621 -.22     0  .04 .01  -.0043733786
1 3 16320 16327 34.78  35.3  -.012003574    .013465884  -.4  -.42 -.07 .01    .003465884
1 3 16320 16328 35.22  34.4   .012571594  -.0031168545  .08   .37 -.21 .01   -.013116854
1 3 16320 16329 35.28 35.22   .001702128   -.003247049  .13    .2  .16 .01   -.013247048
1 3 16320 16330 35.25 35.02 -.0008507019  .00012044927  .04  -.05  .05 .01    -.00987955
1 3 16320 16331 34.95 35.08   -.00854706     .00836574  .08  -.25 -.14 .01    -.00163426
1 3 16320 16334  35.2 35.06   .007127614   -.008938003  .23   .28  .14 .01   -.018938003
1 3 16320 16335  35.3 35.52  .0028368814    .006456576   .3  -.42  .11 .01   -.003543424
1 3 16320 16336 18.78  18.9    -.6310905   -.013925955  .48   .57 -.06 .01   -.023925954
1 3 16320 16337 35.15 35.38     .6268321    -.00939289  .52   .38    0 .01    -.01939289
1 3 16320 16338 35.57 35.15   .011877968  -.0014104375  .12  -.12 -.01 .01   -.011410438
1 3 16320 16341 35.87  35.7   .008398705   -.007514896  .49   .26  .04 .01   -.017514896
1 3 16320 16342  35.5 35.61  -.010368595    .002439289  .37  -.28    0 .01   -.007560711
1 3 16320 16343  35.8 35.44   .008415197   .0032021704 -.07   -.3   .3 .01    -.00679783
1 3 16320 16344  36.1 35.82   .008344972    -.00418952  .29   .69  .21 .01    -.01418952
1 3 16320 16345 35.81 36.25  -.008065681    .021167235 -.38 -1.33  .08 .01    .011167236
1 3 16320 16348 35.59 35.53  -.006162485    .012971986 -.44  -.24 -.11 .01    .002971986
1 3 16320 16349 35.63 35.82  .0011232801   .0027111995  .13  -.09   .4 .01     -.0072888
1 3 16320 16350 35.91  35.8   .007827829    .002525817  .09   .25 -.11 .01   -.007474183
1 3 16320 16351 35.98 35.79  .0019474203   .0021641501  .06    .4  .33 .01    -.00783585
1 3 16320 16352 35.79 35.75  -.005294704   -.008236863  .21   .16  .03 .01   -.018236862
1 3 16320 16355 35.41 35.71   -.01067426   -.001434249   .2    .2  .22 .01    -.01143425
1 3 16320 16356 35.35 35.53 -.0016958738   -.013425848   .2   .28 -.14 .01    -.02342585
1 3 16320 16357 35.23 35.25    -.0034004    .004716562 -.27  -.15 -.03 .01   -.005283438
1 3 16320 16358  35.2  35.4 -.0008519097   -.008481558  .16   .27  .03 .01   -.018481558
1 3 16320 16359  35.1 35.12  -.002844952    -.00240608  .06   .05  .01 .01    -.01240608
1 3 16320 16362 35.35  35.2   .007097262  -.0039414037  .38   .06  .07 .01   -.013941403
1 3 16320 16363 35.68  35.6   .009291915    .010657836 -.46  -.39  -.3 .01   .0006578357
1 3 16320 16364 35.91 35.66     .0064255   -.014153914  .25   .47 -.22 .01   -.024153914
1 3 16320 16365 35.73 35.75  -.005025136    .003496078 -.19   .05 -.01 .01   -.006503922
1 3 16320 16366 35.65 35.65  -.002241525   .0011626486  .25    .2  .17 .01   -.008837352
1 3 16320 16369 35.37 35.67  -.007885144     -.0154072  .36   .79  .11 .01     -.0254072
1 3 16320 16370 35.15 35.23  -.006239385    .003379062  -.3  -.12  .04 .01   -.006620937
1 3 16320 16371    35 35.24  -.004276557    .014135814  -.2   -.8  -.2 .01   .0041358145
1 3 16320 16372 34.45  34.7  -.015839064    .008810095 -.44  -.48  .06 .01  -.0011899055
1 3 16320 16373 18.93 18.78     -.598761 -.00042353655 -.14    .4  .23 .01   -.010423536
1 3 16320 16376 34.41 33.86     .5975993   .0020554992  .02  -.41 -.15 .01   -.007944501
1 3 16320 16377 34.05 34.43  -.010517187    .009377323 -.19  -.32   .1 .01   -.000622677
1 3 16320 16378  34.2  34.2  .0043956116    .003851613 -.15   .03 -.25 .01   -.006148387
1 3 16320 16379 34.05 34.16 -.0043956116    -.00600782  .12   .12  .45 .01    -.01600782
1 3 16320 16380 34.04 33.94 -.0002937289    .008359256 -.47   .06 -.25 .01  -.0016407438
1 3 16320 16383 34.05 34.19  .0002937289   .0019009154    0    .3  .04 .01   -.008099085
1 3 16320 16384 34.65 34.05   .017467692   -.001247068 -.07   .13  .19 .01   -.011247068
1 3 16320 16385 34.55 34.75 -.0028901754    .005481414 -.33   .02 -.02 .01  -.0045185857
1 3 16320 16386 34.73  34.5   .005196317    .011574747 -.38   -.4  .24 .01    .001574747
1 3 16320 16387 34.65 34.85 -.0023061412     .00361527 -.06   .03 -.12 .01    -.00638473
1 3 16320 16390 34.46 34.61  -.005498494  -.0032940314  .07   .24  .05 .01    -.01329403
1 3 16320 16391 34.61  34.3  .0043434263   -.008621502  .23   .41 -.09 .01   -.018621502
1 3 16320 16392 35.33 34.62   .020589804   .0078620445 -.24  -.42  .07 .01  -.0021379555
1 3 16320 16393 35.85 35.31   .014611105  -.0015791786 -.02   .12  .25 .01   -.011579178
1 3 16320 16394 37.15    37   .035620205   -.006577426  .56   .73  .33 .01   -.016577426
1 3 16320 16397    37  37.2 -.0040458585    -.00587985  .52   .08  .33 .01   -.015879849
1 3 16320 16398 36.89 36.83  -.002977401   -.000889416  .36   .29  .12 .01   -.010889416
1 3 16320 16399 37.03 37.06   .003787883   .0003558613  .15   .16  .11 .01    -.00964414
1 3 16320 16400 36.63 36.98  -.010860818    .008679277 -.11  -.35 -.01 .01  -.0013207227
1 3 16320 16401  36.9 36.95   .007343974 -.00002939404  .11   .31  .02 .01   -.010029394
1 3 16320 16404 36.78 36.97  -.003257332  -.0017652255  .07    .2 -.03 .01   -.011765226
1 3 16320 16405 36.27 36.72  -.013963266    -.00724052  .45   .58  .25 .01   -.017240519
1 3 16320 16406 35.26 36.34  -.028241776    .016473383 -.11  -.58 -.05 .01    .006473383
1 3 16320 16407  34.9  35.2  -.010262348    .004947624 -.36  -.22  .05 .01   -.005052376
1 3 16320 16408    35 35.01   .002861232   -.002820056  .02   .49    0 .01   -.012820056
1 3 16320 16411 35.39 34.79   .011081233    -.00364586  .16   .25   .2 .01    -.01364586
1 3 16320 16412 19.05  18.9    -.6193622    .003325557 -.04  -.05 -.03 .01   -.006674442
1 3 16320 16413  34.6 34.69     .5967866  -.0001747539 -.29   .01  .12 .01   -.010174754
1 3 16320 16414 35.24 34.81    .01832812   -.009305932  .34   .25 -.03 .01    -.01930593
1 3 16320 16415 35.05 35.16  -.005406187    .004897873  .14  -.06 -.05 .01   -.005102126
1 3 16320 16418 35.29 34.99   .006824024    .010216794 -.21  -.57  .12 .01    .000216794
1 3 16320 16419 35.69 35.63     .0112709   .0024874196  .27   .03 -.18 .01    -.00751258
1 3 16320 16420    36 35.68   .008648401   -.004806866  .17   .38  .17 .01   -.014806867
1 3 16320 16421 35.07 35.86  -.026172874     .00555766   .3  -.06  .14 .01  -.0044423393
1 3 16320 16422 33.85 34.28  -.035407066   -.008522779  .47   .74  .27 .01   -.018522779
1 3 16320 16425 34.48 34.38   .018440446 -.00020381127  .27  -.29 -.11 .01    -.01020381
1 3 16320 16426 34.42 34.63  -.001741655   .0016874695  .06  -.11   .2 .01   -.008312531
1 3 16320 16427 33.87 33.95  -.016108124    .008135422 -.23  -.42  .04 .01   -.001864578
1 3 16320 16428 31.85  33.1   -.06149229    .004373227 -.07   .03  .04 .01   -.005626773
1 3 16320 16429 33.57  31.9    .05259543  -.0007466974 -.16   .04  .07 .01   -.010746697
1 3 16320 16432 34.05 33.86   .014197222  -.0007472553  .15   .17 -.12 .01   -.010747255
1 3 16320 16433 34.02    34 -.0008814457   .0017235936 -.03  -.05 -.09 .01   -.008276407
1 3 16320 16434 34.69 34.26   .019502874   -.003133402  .13     0  .01 .01   -.013133402
1 3 16320 16435  34.6 34.97  -.002597779 -.00014396982  .06   .25 -.09 .01    -.01014397
1 3 16320 16436 34.23 34.44   -.01075123    .002329763 -.04   .19 -.07 .01   -.007670237
1 3 16320 16439 34.38  33.9  .0043725474    .010943123 -.24  -.15  .18 .01   .0009431231
1 3 16320 16440 33.64 34.18  -.021759165    .002185669 -.31   .01  .13 .01   -.007814331
1 3 16320 16441 33.91 33.15   .007994121   -.007170937   .1   .35  .11 .01   -.017170938
1 3 16320 16442  34.3 34.25   .011435398    .009776138 -.28  -.16   .2 .01 -.00022386223
1 3 16320 16443 34.13  34.5  -.004968591    .002429174 -.17   .26  .16 .01   -.007570826
1 3 16320 16446 34.63 34.13   .014543596    .001888415 -.05   .23 -.01 .01   -.008111585
1 3 16320 16447 34.84  34.5   .006045794   -.007773781  .43   .63    0 .01   -.017773781
1 3 16320 16448  34.1  34.7   -.02146877   -.006491311  .12   .71  .13 .01    -.01649131
1 3 16320 16449 18.94 19.12    -.5880213    .002823773  .14     0 -.05 .01   -.007176227
1 3 16320 16450 34.46 34.33     .5985231     .00466029  .22  -.14  .22 .01   -.005339709
1 3 16320 16453 33.83 34.36   -.01845125    .004271758  .09   .04  .05 .01   -.005728242
1 3 16320 16454 33.09 33.61   -.02211686 -.00005646049  .35   .33  .12 .01    -.01005646
1 3 16320 16455 34.08 33.42    .02947958  -.0007907818   .2   .46 -.04 .01   -.010790782
1 3 16320 16456 34.25 34.12   .004975863  -.0033678166  .18   .32  .15 .01   -.013367817
1 3 16320 16457 35.19 34.25    .02707539   .0022653274  .25    .2  .06 .01   -.007734673
end
format %td eventdate
format %td date
label values companyid companyid
label def companyid 3 "ABN AMRO HOLDING", modify

In my dataset, I have 949 recommendations (an advice for a specific company on a specific eventdate) meaning I have have 949 panels: for each recommendation I have a year of pricing data of the respective stock following the recommendation of said stock.

Variable "ADVICE" represents the type of recommendation, "companyid" the recommended stock, "eventdate" is the date of the recommendation, "date" is the date for which the pricing and factor data is shown, "ret" is the daily log return of the stock, "marketret" is the daily log market return, rf is the riskfree rate and MOM, SMB, HML and Mkt_rf the factor data which will be used in the regressions.

Any help or ideas on how to form the portfolios and to compute its returns would be much appreciated, I thank you all in advance!

Kind regards,

Robert

[Maria Boutchkova]

Hi Robert,
thank you for using dataex.
Here is what I would do:

* create groups of the 4 recs and each eventdate
egen port = group(ADVICE eventdate)
* gen a counter of trading days
bys eventdate (date): gen day_count = _n
* save the buying price in a separate var
bys eventdate (date): gen P0_initial = PO[1]
* compute returns
* note that if you only have 1 calendar year of daily prices, you cannot do the 300-day holding period - one calendar year has approx 250 trading days
gen hold_ret = P / P0_initial - 1 if day_count == 61
foreach l in 120 180 240 {
replace hold_ret = P / P0_initial - 1 if day_count == `l' + 1
}
* now form the portfolios
egen port_ret = mean(hold_ret), by(port)
* if you are shorting the sell recs, you could go
replace port_ret = - port_ret if ADVICE > 2

[Robert Prins]

Thank you Maria, good call regarding the year of pricing data! I will collect sufficient data and give it a shot! Will let you know how it went, again thank you so much!

[Robert Prins]

Hello,
The steps provided above seem to properly generate the portfolio holding returns, thank you very much Maria!
This leads to another question: I wish to apply CAPM and a four factor model to estimate the portfolios alphas, similar to the regression table found below:

Does anyone know how I should tackle this in an efficient manner using the variables (Mkt_rf for CAPM and Mkt_rf, SMB, HML and MOM for the four-factor model) I explained in my initial post.
Thank you in advance!

Kind regards,

Robert

[Maria Boutchkova]

Hi Robert,
what you asked for before is not going to work for the factor model regressions. We need daily returns of the portfolios not just the buy-and-hold return at the end of each holding period. This raises another qn, that you should investigate in the original paper carefully - the number of observations are the same for all columns in the tables you have screenshot - this doesn't make sense if indeed daily returns are used.
I note in the data you provided initially that the factor daily returns appear too high to me - verify whether you have them in decimals or in % points. Accordingly transform the daily portfolio returns (and the factors if required) to % points as is in the paper you are replicating.
This is how I would do it for the market model, you should adapt and extend the code for the additional factors. Also note that I use Ben Jann's esttab for producing the regression results, there are native methods now in Stata for this.

egen daily_port_ret = mean(ret), by(port)
replace daily_port_ret = - daily_port_ret if ADVICE > 2
gen ex_daily_port_ret = daily_port_ret - rf
* now we need to either collapse the data to port-date level so we dont get repeated obs or simply keep the first obs per portfolio-date
egen tag_port_date = tag(port date)
keep if tag_port_date == 1
* now you do your regs
* mkt model
eststo clear
forv a = 1/4 {
foreach d in 61 121 181 241 {
eststo mkt_a`a'_d`d': reg ex_daily_port_ret Mkt_rf if ADVICE == `a' & day_count <= `d'
}
}
esttab mkt_a1_d61 mkt_a1_d121 mkt_a1_d181 mkt_a1_d241 using reg_res_mkt.rtf, replace star(* 0.10 ** 0.05 *** 0.01) mtitles("Hold 60 days" "Hold 120 days" "Hold 180 days" "Hold 240 days") s(N r2_a, label("Obs" "Adj R-sq" ) fmt(%9.0fc 3)) varlabels(_cons "Alpha" Mkt_rf "MKT - RF") order(_cons Mkt_rf)