Time series regression of portfolios

[Dennis Uhrskov]

Hi Statalist,

I have the following data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(june_year portfolio27 return_27P size BE_ME ex_ret SMB HML MktRf_juneyear RF_juneyear Ri)
1968  1 -1.8528262  46.45491  .1871031 1.5746368  4.4502215 -.18595564     -5.71 5.59   2.421568
1968  4  -2.547138 251.19106 .21432753  .4401012  4.4502215 -.18595564     -5.71 5.59   2.421568
1968  6   .6621914  313.4844  .2781102 1.0470697  4.4502215 -.18595564     -5.71 5.59   2.421568
1968  7   1.537321  7275.767 .23540664  .5170542  4.4502215 -.18595564     -5.71 5.59   2.421568
1968  8  -5.010601  9117.553  .2335071  .8386894  4.4502215 -.18595564     -5.71 5.59   2.421568
1968  9   -2.05631  8135.452    .21004  .9150611  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 12 -2.0438352  48.08026  .7543209 1.4502113  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 13   -3.91688 257.77652 .51247776   .811896  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 14 -18.194893 261.58063  .6109893   .912639  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 15    7.45454  302.1777  .6917778 1.1835885  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 16   .9899478  4391.498 .53809595  .6806926  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 17  -.1312364 4638.0923  .5318634  .8871385  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 18 -2.4154315  5376.671  .5118892 1.0134673  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 22 -10.136987 190.33855 1.0471069  .9678418  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 24  -5.314354 236.21185 1.0772206 1.4393657  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 25   3.079225  3159.629 1.0939996  .8129234  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 26  .29291365  3547.268  1.004282  .9233693  4.4502215 -.18595564     -5.71 5.59   2.421568
1968 27  -1.276447  4640.856  .9542781 1.2754495  4.4502215 -.18595564     -5.71 5.59   2.421568
1969  1 -3.9950836  46.45491  .1871031 1.5746368  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969  2 -1.8826283  50.81953  .2147014   1.00526  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969  3  -9.609921  59.94537  .3157889 1.5480962  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969  4  -6.329717 251.19106 .21432753  .4401012  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969  5  -11.64971  311.6086  .2666884  .7875029  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969  6  -4.796626  313.4844  .2781102 1.0470697  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969  7  -6.094961  7275.767 .23540664  .5170542  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969  8  -3.250779  9117.553  .2335071  .8386894  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969  9 -1.6617693  8135.452    .21004  .9150611  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 10  -8.614918  40.39402   .511501 1.1060672  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 11  -3.953473  45.24791  .6864782 1.1754344  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 12  -7.507675  48.08026  .7543209 1.4502113  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 13 -4.2875295 257.77652 .51247776   .811896  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 14  -8.190659 261.58063  .6109893   .912639  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 15 -4.0016932  302.1777  .6917778 1.1835885  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 16    -2.6829  4391.498 .53809595  .6806926  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 17   -2.40084 4638.0923  .5318634  .8871385  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 18 -1.8902283  5376.671  .5118892 1.0134673  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 19  -3.924722  30.38341 1.1338295 2.5920134  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 20   -4.45997 34.586502 1.1837006 1.3762656  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 21  -3.505597 36.841274 1.3023628  2.400146  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 22  -6.620926 190.33855 1.0471069  .9678418  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 23  -5.472656  190.0638 1.0166473 1.0858502  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 24  -4.863846 236.21185 1.0772206 1.4393657  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 25  -5.596075  3159.629 1.0939996  .8129234  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 26 -2.4917464  3547.268  1.004282  .9233693  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1969 27  -.8543937  4640.856  .9542781 1.2754495  -3.045729  -.4228358     -34.4 6.81 -4.2821455
1970  1   .9647632  46.45491  .1871031 1.5746368  -.3249978  .56228745 33.079998 4.88   3.461765
1970  2  -5.884457  50.81953  .2147014   1.00526  -.3249978  .56228745 33.079998 4.88   3.461765
1970  3   3.420389  59.94537  .3157889 1.5480962  -.3249978  .56228745 33.079998 4.88   3.461765
1970  4   1.810736 251.19106 .21432753  .4401012  -.3249978  .56228745 33.079998 4.88   3.461765
1970  5   3.336357  311.6086  .2666884  .7875029  -.3249978  .56228745 33.079998 4.88   3.461765
1970  6  4.7002974  313.4844  .2781102 1.0470697  -.3249978  .56228745 33.079998 4.88   3.461765
1970  7  4.2953715  7275.767 .23540664  .5170542  -.3249978  .56228745 33.079998 4.88   3.461765
1970  8   4.043665  9117.553  .2335071  .8386894  -.3249978  .56228745 33.079998 4.88   3.461765
1970  9   3.012163  8135.452    .21004  .9150611  -.3249978  .56228745 33.079998 4.88   3.461765
1970 10   1.969447  40.39402   .511501 1.1060672  -.3249978  .56228745 33.079998 4.88   3.461765
1970 11   5.693785  45.24791  .6864782 1.1754344  -.3249978  .56228745 33.079998 4.88   3.461765
1970 12   3.821304  48.08026  .7543209 1.4502113  -.3249978  .56228745 33.079998 4.88   3.461765
1970 13   3.480667 257.77652 .51247776   .811896  -.3249978  .56228745 33.079998 4.88   3.461765
1970 14   2.866583 261.58063  .6109893   .912639  -.3249978  .56228745 33.079998 4.88   3.461765
1970 15   2.775916  302.1777  .6917778 1.1835885  -.3249978  .56228745 33.079998 4.88   3.461765
1970 16   3.238348  4391.498 .53809595  .6806926  -.3249978  .56228745 33.079998 4.88   3.461765
1970 17  2.5406885 4638.0923  .5318634  .8871385  -.3249978  .56228745 33.079998 4.88   3.461765
1970 18  2.2058363  5376.671  .5118892 1.0134673  -.3249978  .56228745 33.079998 4.88   3.461765
1970 19   3.181528  30.38341 1.1338295 2.5920134  -.3249978  .56228745 33.079998 4.88   3.461765
1970 20   2.457007 34.586502 1.1837006 1.3762656  -.3249978  .56228745 33.079998 4.88   3.461765
1970 21   2.581025 36.841274 1.3023628  2.400146  -.3249978  .56228745 33.079998 4.88   3.461765
1970 22   4.099085 190.33855 1.0471069  .9678418  -.3249978  .56228745 33.079998 4.88   3.461765
1970 23  2.8282115  190.0638 1.0166473 1.0858502  -.3249978  .56228745 33.079998 4.88   3.461765
1970 24   4.395001 236.21185 1.0772206 1.4393657  -.3249978  .56228745 33.079998 4.88   3.461765
1970 25    5.61247  3159.629 1.0939996  .8129234  -.3249978  .56228745 33.079998 4.88   3.461765
1970 26   2.620879  3547.268  1.004282  .9233693  -.3249978  .56228745 33.079998 4.88   3.461765
1970 27   4.284964  4640.856  .9542781 1.2754495  -.3249978  .56228745 33.079998 4.88   3.461765
1971  1  1.1173311  46.45491  .1871031 1.5746368  .10667497  -.2142508      7.37 4.04     1.1195
1971  2   1.331903  50.81953  .2147014   1.00526  .10667497  -.2142508      7.37 4.04     1.1195
1971  3  2.0281758  59.94537  .3157889 1.5480962  .10667497  -.2142508      7.37 4.04     1.1195
1971  4  .22670527 251.19106 .21432753  .4401012  .10667497  -.2142508      7.37 4.04     1.1195
1971  5  2.0210972  311.6086  .2666884  .7875029  .10667497  -.2142508      7.37 4.04     1.1195
1971  6  1.1571577  313.4844  .2781102 1.0470697  .10667497  -.2142508      7.37 4.04     1.1195
1971  7 -.10908414  7275.767 .23540664  .5170542  .10667497  -.2142508      7.37 4.04     1.1195
1971  8  2.0998197  9117.553  .2335071  .8386894  .10667497  -.2142508      7.37 4.04     1.1195
1971  9  1.8205957  8135.452    .21004  .9150611  .10667497  -.2142508      7.37 4.04     1.1195
1971 10   .9937426  40.39402   .511501 1.1060672  .10667497  -.2142508      7.37 4.04     1.1195
1971 11  1.6935568  45.24791  .6864782 1.1754344  .10667497  -.2142508      7.37 4.04     1.1195
1971 12  2.0720685  48.08026  .7543209 1.4502113  .10667497  -.2142508      7.37 4.04     1.1195
1971 13   1.251534 257.77652 .51247776   .811896  .10667497  -.2142508      7.37 4.04     1.1195
1971 14  1.0449735 261.58063  .6109893   .912639  .10667497  -.2142508      7.37 4.04     1.1195
1971 15  2.6218145  302.1777  .6917778 1.1835885  .10667497  -.2142508      7.37 4.04     1.1195
1971 16  -.3908081  4391.498 .53809595  .6806926  .10667497  -.2142508      7.37 4.04     1.1195
1971 17 -.14997593 4638.0923  .5318634  .8871385  .10667497  -.2142508      7.37 4.04     1.1195
1971 18  .27030286  5376.671  .5118892 1.0134673  .10667497  -.2142508      7.37 4.04     1.1195
1971 19  2.1744952  30.38341 1.1338295 2.5920134  .10667497  -.2142508      7.37 4.04     1.1195
1971 20   .7387667 34.586502 1.1837006 1.3762656  .10667497  -.2142508      7.37 4.04     1.1195
1971 21  2.1750655 36.841274 1.3023628  2.400146  .10667497  -.2142508      7.37 4.04     1.1195
1971 22   .9561493 190.33855 1.0471069  .9678418  .10667497  -.2142508      7.37 4.04     1.1195
1971 23   .9189107  190.0638 1.0166473 1.0858502  .10667497  -.2142508      7.37 4.04     1.1195
1971 24   .8568044 236.21185 1.0772206 1.4393657  .10667497  -.2142508      7.37 4.04     1.1195
1971 25  -1.543628  3159.629 1.0939996  .8129234  .10667497  -.2142508      7.37 4.04     1.1195
1971 26   1.114893  3547.268  1.004282  .9233693  .10667497  -.2142508      7.37 4.04     1.1195
1971 27  1.0763428  4640.856  .9542781 1.2754495  .10667497  -.2142508      7.37 4.04     1.1195
1972  1   -5.14349  46.45491  .1871031 1.5746368 -1.8268493   1.127177    -12.56 4.93  -.9649922
end

I have 27 portfolios across 50 years, and i want to run the following regression:

Where Ri is the return of the portfolio and are the variable to be explained.
(Rm - Rf) = MktRf_juneyear
Rf = Rf_juneyear

portfolio27 = the 27 portfolios
return_27P = the return of each of the 27 portfolios each year

furthermore i would like to end up with a table looking like this:

the t()'s are the t-statestics of the constants, R2 is the adjusted/robust R2.
The first column is the 27 portfolios (the picture is just part of the table).

For the regressions i tried something like:

regress return_27P MktRf_juneyear SMB HML, robust
predict Ri
tablist portfolio27 Ri, sort(v)

But this code ends up returning 50*27 values (one for each year and for each portfolio).

For the layout i have had a look at -estout- which seems to be something i can use.


Let me know if anything is unclear or if i should provide additional intuition.

Best regards,
Dennis

[Dennis Uhrskov]

So far i have come a bit of the way:

regress return_27P MktRf_juneyear SMB HML, robust 
statsby, by(portfolio27) clear: regress return_27P MktRf_juneyear SMB HML, robust
list

Here i find the constants of the regressions.

I tried something similar for the t-statistics, but it doesn't quite work as hoped: (Just t-stats for one of the variables so far, but you get the idea)

statsby se1=_se[MktRf_juneyear] slope1=_b[MktRf_juneyear] _b_cons, by(portfolio27) clear : regress return_27P MktRf_juneyear SMB HML, robust
gen t_b = slope1/se1

Any help is much appreciated

Dennis

[Phil Bromiley]

You didn't get a quick answer. You'll increase your chances of a useful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output, and sample data using dataex. You also have provided a very long complex posting. And, it is usually better to provide material not in pictures or files.

If se1=_se[MktRf_juneyear] works, then why can't you just add more for the other variables?

[Robert Picard]

The following follows closely the example here on how to perform commands on by-groups using runby (from SSC). Because the data example in #1 is too thin, I create a demonstration dataset that has the same structure.

* create a demonstration dataset
clear all
set seed 321
set obs 27
gen portfolio27 = _n
expand 50
bysort portfolio27: gen june_year = 1967 + _n
gen return_27P = runiform()
gen MktRf_juneyear = runiform()
gen SMB = runiform()
gen HML = runiform()

* do a single case that we'll spot check later on
regress return_27P MktRf_juneyear SMB HML if portfolio27 == 5, robust

program myreg
    cap regress return_27P MktRf_juneyear SMB HML, robust
    keep in 1
    keep portfolio27
    
    gen nobs = e(N)
    
    gen b_MktRf_juneyear = _b[MktRf_juneyear]
    gen t_MktRf_juneyear = _b[MktRf_juneyear] / _se[MktRf_juneyear]
    gen b_SMB = _b[SMB]
    gen t_SMB = _b[SMB] / _se[SMB]
    gen b_HML = _b[HML]
    gen t_HML = _b[HML] / _se[HML]
    gen b_cons   = _b[_cons]
    gen t_cons = _b[_cons] / _se[_cons]
    
    gen r2   = e(r2)
    gen r2a  = e(r2_a)
end
runby myreg, by(portfolio27)

list if portfolio27 == 5

and here are the results

. * do a single case that we'll spot check later on
. regress return_27P MktRf_juneyear SMB HML if portfolio27 == 5, robust

Linear regression                               Number of obs     =         50
                                                F(3, 46)          =       2.62
                                                Prob > F          =     0.0617
                                                R-squared         =     0.1385
                                                Root MSE          =     .29046

-------------------------------------------------------------------------------
              |               Robust
   return_27P |      Coef.   Std. Err.      t    P>|t|     [95% Conf. Interval]
--------------+----------------------------------------------------------------
MktRf_juney~r |   .1635632   .1466464     1.12   0.270    -.1316208    .4587471
          SMB |   .2971545   .1361167     2.18   0.034     .0231658    .5711431
          HML |  -.2016717   .1458858    -1.38   0.174    -.4953245    .0919812
        _cons |   .3503611   .1288368     2.72   0.009     .0910261    .6096961
-------------------------------------------------------------------------------

. 
. program myreg
  1.         cap regress return_27P MktRf_juneyear SMB HML, robust
  2.         keep in 1
  3.         keep portfolio27
  4.         
.     gen nobs = e(N)
  5.     
.     gen b_MktRf_juneyear = _b[MktRf_juneyear]
  6.     gen t_MktRf_juneyear = _b[MktRf_juneyear] / _se[MktRf_juneyear]
  7.     gen b_SMB = _b[SMB]
  8.     gen t_SMB = _b[SMB] / _se[SMB]
  9.     gen b_HML = _b[HML]
 10.     gen t_HML = _b[HML] / _se[HML]
 11.         gen b_cons   = _b[_cons]
 12.     gen t_cons = _b[_cons] / _se[_cons]
 13.     
.     gen r2   = e(r2)
 14.     gen r2a  = e(r2_a)
 15. end

. runby myreg, by(portfolio27)

--------------------------------------
Number of by-groups    =            27
by-groups with errors  =             0
by-groups with no data =             0
Observations processed =         1,350
Observations saved     =            27
--------------------------------------

. 
. list if portfolio27 == 5

     +-------------------------------------------------------------------------+
  5. | portf~27 | nobs | b_MktR~r | t_MktR~r |    b_SMB |    t_SMB |     b_HML |
     |        5 |   50 | .1635631 | 1.115357 | .2971545 | 2.183086 | -.2016717 |
     |----------------------------+--------------------------------------------|
     |      t_HML   |    b_cons   |     t_cons   |         r2   |        r2a   |
     |  -1.382394   |  .3503611   |   2.719418   |   .1384783   |   .0822921   |
     +-------------------------------------------------------------------------+

.

[Dennis Uhrskov]

Hi Robert,

Thank you very much for your answer, it works as intended

Regards
Dennis