How to create an 'Equally Weighted Portfolio' and cross sectional absolute deviations in an unbalanced panel dataset

[Robin Wagenbrenner]

Hi all,

ich have a panel dataset in long format of all companies listed on the Amsterdam and Frankfurt stock exchange. It consists of daily observations of +200 firms from 01.01.2006 until 31.12.2016.
I also have the daily closing prices and trading volume of the firms. The panel dataset is unbalanced, as not all firms are listed at the same time (eg: some firms only in the dataset from 2008-2011.) I have already calculated returns for each firm using:

bysort FirmID : gen return = 100*ln(ClosingPrice[_n]/ClosingPrice[_n-1])

I now have 3 issues that I am confronted with:

1) I would like to generate a cross-sectional return for all firms at each daily observation, ie. I would like to construct an equally weighted portfolio for each daily observation that consists of the firms listed at time t.

2) I need to calculate the sum of the cross-sectional absolute deviation specified by the following equation CSADt = 1/N (∑(i=1)^N |R(i,t)-R(m,t) | )
where i are returns of the individual stocks at time t and m is the return of the equally weighted market portfolio at time t
How would I go about coding this?

3) I would also like to create a dummy variable that indicates whether the return/trading volume of a stock at time t is in the top/bottom 5% for that day.

See the excerpt for a snapshot of the dataset below.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey long Date double(ClosingPrice TradingVolume) float return
"001166" 16803 14.13  222111          .
"001166" 16804 14.36  999136  1.6146367
"001166" 16805 14.54 1443669   1.245691
"001166" 16806 14.47  695923 -.48259315
"001166" 16807  14.7  665417  1.5769954
"001166" 16810 14.74  579313   .2717393
"001166" 16811  14.3  709197  -3.030535
"001166" 16812  14.4  914303   .6968669
"001166" 16813 14.44  380370   .2773927
"001166" 16814  14.3  446776  -.9742596
"001166" 16817 14.14  307870 -1.1251876
"001166" 16818    14  370900  -.9950331
"001166" 16819 13.64 1468501  -2.605068
"001166" 16820 13.95  786986  2.2472856
"001166" 16821  13.7  395433 -1.8083675
"001166" 16824 13.63  191331  -.5122587
"001166" 16825 13.46  278558  -1.255092
"001166" 16826 13.85  417824   2.856291
"001166" 16827 14.16  319604  2.2135856
"001166" 16828 14.95 1436506   5.429021
"001166" 16831 15.02  625966  .46713465
"001166" 16832  14.9  491726  -.8021433
"001166" 16833 14.95  281859   .3350087
"001166" 16834    15  366021   .3338901
"001166" 16835 14.91  264618  -.6018072
"001166" 16838 14.93  207666  .13404828
"001166" 16839 14.94  387451  .06695682
"001166" 16840 15.08  393793   .9327183
"001166" 16841 15.37  257606  1.9048195
"001166" 16842 15.44  492961   .4543987
"001166" 16845 15.61  690090   1.095019
"001166" 16846 15.57  391741 -.25657487
"001166" 16847  15.6  151881  .19249284
"001166" 16848 15.83  412456   1.463596
"001166" 16849 15.78  340065 -.31635585
"001166" 16852 16.02  433337  1.5094626
"001166" 16853 15.85  916755 -1.0668441
"001166" 16854 15.37  894890 -3.0751944
"001166" 16855 15.71  414751  2.1879895
"001166" 16856 15.79  253450   .5079376
"001166" 16859 15.91  397230   .7571014
"001166" 16860  15.6  381260  -1.967693
"001166" 16861 15.97  243600  2.3441048
"001166" 16862 15.94  220558  -.1880289
"001166" 16863 15.49  521605  -2.863702
"001166" 16866  15.7  317474  1.3466058
"001166" 16867  15.4  471386 -1.9293203
"001166" 16868 15.36  244755 -.26007816
"001166" 16869  15.5  308346   .9073296
"001166" 16870 15.54  181971   .2577321
"001166" 16873 15.47  162489   -.451468
"001166" 16874 15.58  371882   .7085376
"001166" 16875 15.88  351104  1.9072416
"001166" 16876 15.56  420219 -2.0356936
"001166" 16877 15.45  568250  -.7094516
"001166" 16880 15.63  458227  1.1583141
"001166" 16881 15.69  327780   .3831422
"001166" 16882 15.64  208945  -.3191832
"001166" 16883 15.83  851103   1.207514
"001166" 16884 16.13  339053   1.877402
"001166" 16887 16.15  229405  .12391575
"001166" 16888    16  365097  -.9331328
"001166" 16889 16.21  300030  1.3039614
"001166" 16890  16.8 1850033   3.575055
"001166" 16891 16.69  675499  -.6569149
"001166" 16894 16.97  392584  1.6637342
"001166" 16895  16.8  478650 -1.0068192
"001166" 16896 16.88  393469   .4750603
"001166" 16897 16.88  257868          0
"001166" 16898  16.8  236392  -.4750603
"001166" 16901 16.87  234242    .415801
"001166" 16902 16.38  291754  -2.947582
"001166" 16903 16.42  216077  .24390256
"001166" 16904 16.52  164734   .6071664
"001166" 16909 16.41  144963  -.6680863
"001166" 16910 16.62  210633  1.2715884
"001166" 16911 16.51  236658  -.6640531
"001166" 16912 16.45  153746  -.3640781
"001166" 16915  16.3  147508   -.916037
"001166" 16916 16.34  174165  .24509816
"001166" 16917  16.4  316092  .36652455
"001166" 16918 15.82  483508  -3.600637
"001166" 16919  15.6  690403 -1.4004048
"001166" 16923 15.19  431185   -2.66336
"001166" 16924  15.1  320092  -.5942573
"001166" 16925 15.73  466781   4.087497
"001166" 16926    16  609119  1.7019005
"001166" 16929 16.15  349470   .9331328
"001166" 16930 16.09  367535  -.3722089
"001166" 16931    16  461086  -.5609239
"001166" 16932 15.81  295871  -1.194607
"001166" 16933 15.44  278126 -2.3681107
"001166" 16936 15.34  711868  -.6497748
"001166" 16937 15.18  423393 -1.0485024
"001166" 16938 14.72  420104  -3.077166
"001166" 16939 13.93  924073  -5.516232
"001166" 16940 14.08  780572  1.0710562
"001166" 16943 13.87  426038 -1.5027117
"001166" 16944  13.8  780494  -.5059642
"001166" 16945 13.45  497481 -2.5689485
end
format %d Date

Any help on the matter is greatly appreciated
Best,
Robin

[Robin Wagenbrenner]

I have managed to calculate the cross sectional average returns of all stock using the following code:

bysort Date: egen MarketReturn= mean(return)

However this gives me the duplicate average value for all firms at observation t. Is there a way to collapse this?

[Phil Bromiley]

You can use collapse. Or, you can do something by date and keep if _n==1.