Equally weighted portfolio construction panel data

[Bram Staniksen]

Hi all,

I am trying to do something in Stata but I am not sure where to start. I have a set of panel data containing the following information:

- >firms based on companyID (gvkey),
--> financial year (fyear)
- stock return (stock_ret) for every firm for every year
- market values (mv) for every firm for every year
- revenues (rev)
- assets( ats)

I calculated returns for every firm for every year which is thus stock_ret

Now I am facing the following challengeS;

* I want to rank all firms on the variable ats each financial year and form ten equally sized portfolios for this variable. After forming the portfolios I want to tabulate a table in which shows the means of stock_ret, mv, rev and ats for each of the portfolios after portfolio formations based on ats.

Equal sized portfolio is a type of weighting that gives the same weight, or importance, to each stock in a portfolio. The smallest companies are given equal weight to the largest companies in an equal-weight portfolio. This allows all of the companies to be considered on an even playing field.


An example of my data;

* Example generated by -dataex-. To install: ssc install dataex
clear
input long gvkey double(fyear ats) float(stock_ret mv) double rev
1004 1972   24.068   -.5681813  17.88374   37.305
1004 1973   31.584  -.27193025  13.04136   57.312
1004 1974   43.353   -.3012047   8.98274   54.626
1004 1975   43.539    .3103446 11.627987   65.797
1004 1976    45.55   .14473729 13.408865   80.535
1004 1977   56.502 -.022988435 14.226865    92.82
1004 1978   64.194 -.023529205  17.47149  118.667
1004 1979   73.758 -.012048075  24.15924  130.617
1004 1980   83.075    .0609754 27.905235  132.482
1004 1981  113.653   -.3563219  27.59398  175.924
1004 1982  111.288   1.1071429  57.99697  155.006
1004 1983  137.228   .32203415 117.13646  177.762
1004 1984  155.405  -.03846157 113.17496  218.946
1004 1985  198.287   .26000026 214.96384  248.012
1004 1986  235.091   .28571343  320.0307  298.192
1004 1987  284.948  -.17283955  398.7334   347.64
1004 1988  356.391   .24378192  500.2811   406.36
1004 1989  388.521   -.3199999  341.7425  444.875
1004 1990  379.958   -.3352941 224.46036  466.542
1004 1991  395.351  -.08849565  204.6996  422.657
1004 1992  365.151   .04854373  214.6635   382.78
1004 1993  417.626   .06481478  228.6487  407.754
1004 1994  425.814   .06086964 243.40523  451.395
1004 1995  437.846    .4508197  353.9557   504.99
1004 1996  529.584    .4011293  564.3237  589.328
1004 1997  670.559    -.147177  732.4245  782.123
1004 1998   726.63  -.25295508  540.7748  918.036
1004 1999  740.998  -.29746836  372.7519 1024.333
1004 2000  701.854  .009009009   377.118  874.255
1004 2001  710.199  -.18285714  364.5928  638.721
1004 2002  686.621   -.6066434  143.3295  606.337
1004 2003  709.292   1.1288888  308.9071  651.958
1004 2004   732.23    .6743215 522.67944  747.848
1004 2005  978.819   .50124687  882.6283  897.284
1004 2006 1067.633    .3496678 1226.1925 1061.169
1004 2007  1362.01   -.4067692  747.5435 1384.919
1004 2008 1377.511  -.23755187  571.5948 1423.976
1004 2009 1501.042   .34013605  777.8348 1352.151
1004 2010 1703.727    .3395939 1049.8206 1775.782
1004 2011 2195.653  -.54338765  485.2896 2074.498
1004 2012   2136.9    .6647303  790.0029   2167.1
1004 2013   2199.5    .2113659   961.308     2035
1004 2014     1515   .21563786 1046.3954   1594.3
1045 1969 1490.798  -.12142857  623.6407  1032.96
1045 1970 1524.946   -.2682927  456.3225 1132.779
1045 1971 1662.614    .8111111 1153.6732 1245.331
1045 1972 1732.493   -.3834356  715.7108 1353.808
1045 1973 1687.264   -.6567164 245.69176 1481.987
1045 1974 1640.691   -.4057971 146.31363 1641.307
1045 1975 1604.936    .7317073 253.37238 1710.005
1045 1976 1715.229   .52112675   386.208 2007.882
1045 1977 2069.141  -.21296297 304.01312 2277.989
1045 1978 2767.717   .24705882  380.0233 2735.505
1045 1979 3182.494  -.23584905   290.547 3252.531
1050 1996    9.201    -.346832    14.402    9.848
1050 1997   13.961   .53099966 24.401073   14.531
1050 1998   15.475 -.020247987    24.753   26.382
1050 1999   51.073  -.16666667   20.6275   23.862
1050 2000   55.896        -.45  10.84325   89.817
1050 2001    53.03         1.4   31.7262   90.994
1050 2002   46.677   -.4393939   17.7415   78.877
1050 2003   41.154   -.1081081  16.47525   68.159
1050 2004   43.441   1.0848485  34.37592   69.366
1050 2005     42.9    .6685756  57.35882   81.521
1050 2006   63.188     .562745 103.02045  135.359
1050 2007   96.535   .22408026 162.38322  235.953
1050 2008  120.017   -.7795992  34.66166   217.89
1050 2009   77.515    .6322314  56.44155  138.985
1050 2010   74.791   .50886077  85.34124  140.602
1050 2011   79.345  -.06879195   80.5638  139.192
1050 2012   94.104    .7927928 168.74205  135.052
1050 2013  348.536    .6233166  413.2812  197.317
1050 2014  414.365  -.03789004  408.1892  263.217
1050 2015  598.819   -.5057915 260.49023  367.422
1072 1978   54.811   .28169012  41.29124    63.74
1072 1979   98.063   .20879108 101.28247   91.554
1072 1980  160.771   .10909094 142.52646  141.138
1072 1981  149.702   -.5450819  65.61487  122.667
1072 1982  150.174    .5495496   102.039  120.309
1072 1983  191.104   .26162803 241.00563  160.854
1072 1984   243.47   -.3548387    164.78  230.907
1072 1985  273.727  -.17142858  158.7025  178.684
1072 1986  251.433   -.2672414   138.125  195.359
1072 1987  420.046    .4235294  197.6535  271.888
1072 1988  424.171   .12396694   223.193  408.939
1072 1989  442.608    .8014706  406.6694   412.52
1072 1998  1058.04  -.20496894   1380.08 1245.473
1072 1999 1308.331    3.738281  6614.337 1630.273
1072 2000 1885.098   -.7723331 3015.3564 2608.113
1072 2001 1691.599   .21320973  3657.778  1249.98
1072 2002 1700.513  -.57020056  1563.849 1134.111
1072 2003 1667.877    .8322222 2863.4556 1136.577
1072 2004 1689.749  -.25712553 2118.6987 1283.202
1072 2005 1675.208   .44489795  3048.223 1333.208
1072 2006 1899.536  -.14124294  2609.445 1498.495
1072 2007 2109.078  -.15723684 2191.3555 1619.275
1072 2008 1872.529  -.29117876 1547.0867 1389.613
1072 2009 2051.492    .5638766 2415.0508 1304.966
1072 2010 2319.482         .05  2536.817 1653.176
1072 2011 2468.012  -.11066398 2248.9092 1545.254
end

I appreciate any type of help. Thank you in advance.

[Nick Cox]

Often asked and answered answered here. See http://www.statalist.org/forums/foru...dummy-variable e.g.

or search the forum for references to fastxtile or xtile.

[Bram Staniksen]

Often asked and answered answered here. See http://www.statalist.org/forums/foru...dummy-variable e.g.

or search the forum for references to fastxtile or xtile.

Hello Nick,

Thank you for your response. I have taken a look at it and edited some to get this, which could help me with constructing the portfolio's, I guess;

  
 bysort gvkey fyear: egen portfolio = xtile(ats), nq(10)

But still I am not sure about the following things:

1. if fyear needs to be with or without parentheses () and if I need to put gvkey before fyear in the bysort code (?).

2. This still does not put equal weight to each stock (firmID) in each yearly portfolio..., right?

3. In addition to that, I still have not figured out how to let STATA summarize all ten portfolios in a table which shows the mean values of the variables stock_ret, mv, rev and ats for each of the portfolios (?)

[Nick Cox]

On your questions:

1. If you leave out the parentheses you are splitting on gvkey fyear groups. If you put them in you are lumping years together. See the help for by:

2. Wrong. (Or why do you doubt this? What in the syntax or what results make you wonder?)

3. help tabstat etc. But unless the portfolios are based on lumping years together (#1 seems to imply otherwise) that's a three-way table.

See also http://www.statalist.org/forums/help#spelling

[Bram Staniksen]

On your questions:

1. If you leave out the parentheses you are splitting on gvkey fyear groups. If you put them in you are lumping years together. See the help for by:

2. Wrong. (Or why do you doubt this? What in the syntax or what results make you wonder?)

3. help tabstat etc. But unless the portfolios are based on lumping years together (#1 seems to imply otherwise) that's a three-way table.

See also http://www.statalist.org/forums/help#spelling

Regarding 2; I thought that I, maybe, need to add an extra restriction so STATA knows that each stock needs to be equally weighted for each particular year portfolio.

Regarding 3; I will take a look at it, thank you!

By the way, is 'my' code sufficiënt or do I need to add or change something?

[Bram Staniksen]

On your questions:

1. If you leave out the parentheses you are splitting on gvkey fyear groups. If you put them in you are lumping years together. See the help for by:

2. Wrong. (Or why do you doubt this? What in the syntax or what results make you wonder?)

3. help tabstat etc. But unless the portfolios are based on lumping years together (#1 seems to imply otherwise) that's a three-way table.

See also http://www.statalist.org/forums/help#spelling

Regarding 1.

I need to rank all firms on the variable ats each financial year and form ten equally sized portfolio's for this variable ats. After that I want to summarize the means of stock_ret, mv, rev and ats for the 1 year period after portfolio formations based on ats.