Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Forming portfolio's by sorting on variable and mean of variables per portfolio

    Dear Statalist,


    So I have got annual panel data of many firms over a period of many years;

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input long firmid double year float(Re MrktValue) double cogs
    1004 1972   -.5681813  17.88374   27.568
    1004 1973  -.27193025  13.04136   44.136
    1004 1974   -.3012047   8.98274   40.686
    1004 1975    .3103446 11.627987   51.331
    1004 1976   .14473729 13.408865   64.328
    1004 1977 -.022988435 14.226865   73.371
    1004 1978 -.023529205  17.47149   91.646
    1004 1979 -.012048075  24.15924  101.391
    1004 1980    .0609754 27.905235  100.354
    1004 1981   -.3563219  27.59398  137.658
    1004 1982   1.1071429  57.99697  117.927
    1004 1983   .32203415 117.13646  134.726
    1004 1984  -.03846157 113.17496  165.259
    1004 1985   .26000026 214.96384   185.74
    1004 1986   .28571343  320.0307  224.534
    1004 1987  -.17283955  398.7334  262.047
    1004 1988   .24378192  500.2811  303.056
    1004 1989   -.3199999  341.7425  336.931
    1004 1990   -.3352941 224.46036   366.04
    1004 1991  -.08849565  204.6996  331.056
    1004 1992   .04854373  214.6635  307.436
    1004 1993   .06481478  228.6487  328.562
    1004 1994   .06086964 243.40523  363.196
    1004 1995    .4508197  353.9557   404.11
    1004 1996    .4011293  564.3237    468.5
    1004 1997    -.147177  732.4245  619.434
    1004 1998  -.25295508  540.7748  727.714
    1004 1999  -.29746836  372.7519  833.107
    1004 2000  .009009009   377.118  713.811
    1004 2001  -.18285714  364.5928  526.477
    1004 2002   -.6066434  143.3295  496.747
    1004 2003   1.1288888  308.9071  523.302
    1004 2004    .6743215 522.67944  598.172
    1004 2005   .50124687  882.6283  704.081
    1004 2006    .3496678 1226.1925  837.171
    1004 2007   -.4067692  747.5435 1080.895
    1004 2008  -.23755187  571.5948 1110.677
    1004 2009   .34013605  777.8348 1065.902
    1004 2010    .3395939 1049.8206 1408.071
    1004 2011  -.54338765  485.2896 1662.408
    1004 2012    .6647303  790.0029   1714.5
    1004 2013    .2113659   961.308   1581.4
    1004 2014   .21563786 1046.3954   1342.7
    1045 1969  -.12142857  623.6407  720.378
    1045 1970   -.2682927  456.3225   856.08
    1045 1971    .8111111 1153.6732  916.627
    1045 1972   -.3834356  715.7108  993.626
    1045 1973   -.6567164 245.69176 1153.814
    1045 1974   -.4057971 146.31363 1230.784
    1045 1975    .7317073 253.37238 1355.248
    1045 1976   .52112675   386.208 1505.318
    1045 1977  -.21296297 304.01312 1719.437
    1045 1978   .24705882  380.0233 2016.362
    1045 1979  -.23584905   290.547 2540.672
    1050 1996    -.346832    14.402    5.188
    1050 1997   .53099966 24.401073    8.116
    1050 1998 -.020247987    24.753   17.952
    1050 1999  -.16666667   20.6275   15.475
    1050 2000        -.45  10.84325   71.924
    1050 2001         1.4   31.7262   71.406
    1050 2002   -.4393939   17.7415    63.52
    1050 2003   -.1081081  16.47525   55.201
    1050 2004   1.0848485  34.37592   56.372
    1050 2005    .6685756  57.35882   64.509
    1050 2006     .562745 103.02045  111.261
    1050 2007   .22408026 162.38322  195.591
    1050 2008   -.7795992  34.66166  172.874
    1050 2009    .6322314  56.44155  106.283
    1050 2010   .50886077  85.34124  106.692
    1050 2011  -.06879195   80.5638  100.066
    1050 2012    .7927928 168.74205    91.69
    1050 2013    .6233166  413.2812  134.576
    1050 2014  -.03789004  408.1892  176.177
    1050 2015   -.5057915 260.49023  254.922
    1072 1978   .28169012  41.29124   43.889
    1072 1979   .20879108 101.28247   60.496
    1072 1980   .10909094 142.52646   99.817
    1072 1981   -.5450819  65.61487  101.254
    1072 1982    .5495496   102.039   85.182
    1072 1983   .26162803 241.00563  110.651
    1072 1984   -.3548387    164.78  155.576
    1072 1985  -.17142858  158.7025  148.073
    1072 1986   -.2672414   138.125  141.283
    1072 1987    .4235294  197.6535  180.126
    1072 1988   .12396694   223.193  264.989
    1072 1989    .8014706  406.6694  270.363
    1072 1998  -.20496894   1380.08  987.206
    1072 1999    3.738281  6614.337 1194.771
    1072 2000   -.7723331 3015.3564 1504.415
    1072 2001   .21320973  3657.778  981.254
    1072 2002  -.57020056  1563.849  945.018
    1072 2003    .8322222 2863.4556  985.094
    1072 2004  -.25712553 2118.6987  1024.43
    1072 2005   .44489795  3048.223 1060.951
    1072 2006  -.14124294  2609.445 1146.441
    1072 2007  -.15723684 2191.3555 1272.945
    1072 2008  -.29117876 1547.0867 1091.998
    1072 2009    .5638766 2415.0508  969.195
    1072 2010         .05  2536.817 1148.171
    1072 2011  -.11066398 2248.9092 1106.405
    end



    I need to construct 10 portfolio's which are sorted on the stock returns (Re) and where the output gives the mean value for the variables stock returns (Re) and cost of goods sold (cogs) for each portfolio.

    The first portfolio would need to be the portfolio with the lowest stock returns and the tenth portfolio the portfolio with the highest stock returns

    Since I am not very experience with stata I was wondering whether someone could help me with the commands.

    I tried the following:


    Code:
     
     bysort firmid & year: egen portfolio= mean(Re) // generating portfolio's
    
     portfolio = mean(Re) & mean(cogs), by (Re) // for mean portfolio returns and cogs
    
    display portfolio





    But it did not work?! Can somebody help me?






    Last edited by Jan Filips; 30 Apr 2017, 08:53.

  • #2
    Hi,
    bysort is not correct
    Code:
     bysort firmid year: egen portfolio= mean(Re) // generating portfolio's
    the second relation is not clear

    Comment


    • #3
      Originally posted by Enrico Zorzi View Post
      Hi,
      bysort is not correct
      Code:
      bysort firmid year: egen portfolio= mean(Re) // generating portfolio's
      the second relation is not clear

      Hi Enrico,

      Thanks for your response. I appreciate it.

      I want to rank e.g. the variable cogs (cost of goods sold) each financial year for each firmid and then form ten value weighted (based on market value) portfolio's for these variables and then create an output in a table which could show the mean of the stock return (Re) and cogs.

      So basically I need a code which I can use multiple times, because i have a couple of variables which I want to rank and on which I want to form ten equally sized portfolio's for the particular variables. After forming portfolio's I want to tabulate a table in which the portfolio's are shown from 1 to 10 (1 is lowest en 10 is highest value for a certain variable) where the means are reported of certain variables which corresponds to each portfolio.

      As in the dataex, for 1 portfolio I want to do the following:

      - sorting based on cogs and construct 10 value weighted portfolio's (forming deciles based on market value) and then create an output in a table which could show the mean of the variables Re and cogs for each portfolio. As stated earlier, my data is a panel data (it includes a time period and different firms based on firmid)
      Last edited by Jan Filips; 30 Apr 2017, 15:47.

      Comment


      • #4
        Jan Filips hello and morning,
        how you construct portfolios can you share code?

        Thanks

        Comment

        Working...
        X