Hello,
My goal is to form portfolios in each year on January based on the one past year return. As you can see Ive already done that, but now I have to calculate the excess return (mf_u_mnret) on each decile over the years.
I am trying to replicate a data with following information:
funds are sorted on January 1 each year from 1990 to 2018 into decile portfolios based on their previous calendar year's return. The portfolios are equally weighted monthly so the weights are readjusted whenever a fund disappears. Funds with the highest past one-year return comprise decile 1 and funds with the lowest comprise decile 10. Deciles 1 and 10 are further subdivided into thirds on the same measure.
The result should look like this:
1A
1B
1C
1
2
3
4
5
6
7
8
9
10
10A
10B
10C
Next to these deciles there should be the monthly excess return.
Ive tried using 'tabstat mf_u_mnret , by(decile) s(n sum mean sd)' which results in the means of the monthly excess returns over the deciles.
Summary for variables: mf_u_mnret
Group variable: decile
decile | N Sum Mean SD
---------+----------------------------------------
1 | 51928 28923.04 .5569835 4.925831
2 | 52797 30872.3 .5847358 4.646036
3 | 53078 30509 .5747956 4.561271
4 | 53001 30332.06 .5722921 4.526425
5 | 52902 29703.88 .5614889 4.532059
6 | 53088 31531.74 .5939523 4.554174
7 | 52945 31397.86 .5930279 4.633395
8 | 52904 33611.45 .6353291 4.718085
9 | 52849 33557.83 .6349757 4.905058
10 | 51686 32406.69 .6269916 5.204433
---------+----------------------------------------
Total | 527178 312845.8 .5934349 4.723781
--------------------------------------------------
Is there perhaps an error with my code, resulting in the means of the deciles being so close to one another?
I also have problems in identifying 1A-C and 10A-C.
Code:
* Example generated by -dataex-. For more info, type help dataex clear input float(fundnr month year mktrf smb hml mom mf_u_mnret) double cum_excess_return_prior_year float decile 1 7 2011 -2.36 -1.31 -1.23 .09 -6 . . 1 8 2011 -5.99 -3.06 -2.44 -.33 -5.96745 . . 1 9 2011 -7.59 -3.48 -1.46 -2.57 -11.31222 . . 1 10 2011 11.35 3.41 -.17 -1.43 11.840167 . . 1 11 2011 -.28 -.17 -.35 3.99 -1.3491888 . . 1 12 2011 .74 -.71 1.74 1.93 .4632918 . . 1 1 2012 5.05 2.15 -1.09 -7.92 2.5078635 -2.0542334417502084 1 1 2 2012 4.42 -1.75 .09 -.31 2.922143 -2.0542334417502084 1 1 3 2012 3.11 -.61 .87 1.51 3.088548 -2.0542334417502084 1 1 4 2012 -.85 -.52 -.47 3.83 1.48305 -2.0542334417502084 1 1 5 2012 -6.19 .02 -.62 6.62 -4.1853704 -2.0542334417502084 1 1 6 2012 3.89 .77 .44 -1.13 3.48584 -2.0542334417502084 1 1 7 2012 .79 -2.58 -.25 3.13 -.21053 -2.0542334417502084 1 1 8 2012 2.55 .41 1.28 -2.48 .7284 -2.0542334417502084 1 1 9 2012 2.73 .5 1.52 -1.02 2.2660294 -2.0542334417502084 1 1 10 2012 -1.76 -1.14 3.79 .09 -.931438 -2.0542334417502084 1 1 11 2012 .78 .59 -.96 .38 1.436699 -2.0542334417502084 1 1 12 2012 1.18 1.47 3.55 -2.94 .5022256 -2.0542334417502084 1 1 1 2013 5.57 .39 .92 -1.86 5.196007 1.0911216922104359 4 1 2 2013 1.29 -.45 0 1.3 .8044029 1.0911216922104359 4 1 3 2013 4.03 .79 -.26 1.95 4.1279926 1.0911216922104359 4 1 4 2013 1.55 -2.44 .59 .24 2.4800675 1.0911216922104359 4 1 5 2013 2.8 1.67 2.55 -1.81 1.9914284 1.0911216922104359 4 1 6 2013 -1.2 1.22 -.19 .6 -2.662564 1.0911216922104359 4 1 7 2013 5.65 1.86 .55 1.72 5.379548 1.0911216922104359 4 1 8 2013 -2.71 .3 -2.78 .03 -3.460949 1.0911216922104359 4 1 9 2013 3.77 2.94 -1.18 3.01 4.4599648 1.0911216922104359 4 1 10 2013 4.18 -1.49 1.15 .16 4.118799 1.0911216922104359 4 1 11 2013 3.12 1.24 .24 .41 1.3186208 1.0911216922104359 4 1 12 2013 2.81 -.5 -.3 .07 2.9396176 1.0911216922104359 4 1 1 2014 -3.32 .87 -2.08 1.7 -3.32604 2.2244112888971963 2 1 2 2014 4.65 .33 -.39 2.07 4.2795906 2.2244112888971963 2 1 3 2014 .43 -1.89 5.09 -3.31 -2.73598 2.2244112888971963 2 1 4 2014 -.19 -4.25 1.14 -3.87 -1.5979946 2.2244112888971963 2 1 5 2014 2.06 -1.83 -.26 1.14 2.438273 2.2244112888971963 2 1 6 2014 2.61 3.06 -.74 .7 2.0519252 2.2244112888971963 2 1 7 2014 -2.04 -4.23 .01 -.24 -.4278609 2.2244112888971963 2 1 8 2014 4.24 .37 -.57 .81 5.492631 2.2244112888971963 2 1 9 2014 -1.97 -3.82 -1.22 .51 -2.679913 2.2244112888971963 2 1 10 2014 2.52 4.23 -1.69 .02 2.88534 2.2244112888971963 2 1 11 2014 2.55 -2.06 -2.99 1.03 2.1411743 2.2244112888971963 2 1 12 2014 -.06 2.54 2.07 1.08 -.7996011 2.2244112888971963 2 1 1 2015 -3.11 -.57 -3.47 3.86 -2.498645 .6434620469808578 4 1 2 2015 6.13 .53 -1.77 -2.86 5.210761 .6434620469808578 4 1 3 2015 -1.12 3.05 -.45 2.97 -1.489463 .6434620469808578 4 1 4 2015 .59 -2.97 1.85 -7.37 .02187492 .6434620469808578 4 2 8 2011 -5.99 -3.06 -2.44 -.33 -9.45162 . . 2 9 2011 -7.59 -3.48 -1.46 -2.57 -8.07175 . . 2 10 2011 11.35 3.41 -.17 -1.43 12.21446 . . 2 11 2011 -.28 -.17 -.35 3.99 -3.03359 . . 2 12 2011 .74 -.71 1.74 1.93 -.38230005 . . 2 1 2012 5.05 2.15 -1.09 -7.92 6.62177 -1.7449600994586945 1 2 2 2012 4.42 -1.75 .09 -.31 4.84211 -1.7449600994586945 1 2 3 2012 3.11 -.61 .87 1.51 3.41365 -1.7449600994586945 1 2 4 2012 -.85 -.52 -.47 3.83 -.87379 -1.7449600994586945 1 2 5 2012 -6.19 .02 -.62 6.62 -7.649571 -1.7449600994586945 1 2 6 2012 3.89 .77 .44 -1.13 1.59067 -1.7449600994586945 1 2 7 2012 .79 -2.58 -.25 3.13 .93946 -1.7449600994586945 1 2 8 2012 2.55 .41 1.28 -2.48 4.5401597 -1.7449600994586945 1 2 9 2012 2.73 .5 1.52 -1.02 3.3529575 -1.7449600994586945 1 2 10 2012 -1.76 -1.14 3.79 .09 -3.45493 -1.7449600994586945 1 2 11 2012 .78 .59 -.96 .38 1.8729877 -1.7449600994586945 1 2 12 2012 1.18 1.47 3.55 -2.94 1.3395475 -1.7449600994586945 1 2 1 2013 5.57 .39 .92 -1.86 6.116453 1.3779184420903523 8 2 2 2013 1.29 -.45 0 1.3 .5488696 1.3779184420903523 8 2 3 2013 4.03 .79 -.26 1.95 2.638819 1.3779184420903523 8 2 4 2013 1.55 -2.44 .59 .24 .6204902 1.3779184420903523 8 2 5 2013 2.8 1.67 2.55 -1.81 3.436172 1.3779184420903523 8 2 6 2013 -1.2 1.22 -.19 .6 -1.9591722 1.3779184420903523 8 2 7 2013 5.65 1.86 .55 1.72 5.99483 1.3779184420903523 8 2 8 2013 -2.71 .3 -2.78 .03 -.9836019 1.3779184420903523 8 2 9 2013 3.77 2.94 -1.18 3.01 4.884068 1.3779184420903523 8 2 10 2013 4.18 -1.49 1.15 .16 4.1830773 1.3779184420903523 8 2 11 2013 3.12 1.24 .24 .41 3.030184 1.3779184420903523 8 2 12 2013 2.81 -.5 -.3 .07 3.427718 1.3779184420903523 8 2 1 2014 -3.32 .87 -2.08 1.7 -2.562135 2.6614922682444253 7 2 2 2014 4.65 .33 -.39 2.07 5.49103 2.6614922682444253 7 2 3 2014 .43 -1.89 5.09 -3.31 -1.9795718 2.6614922682444253 7 2 4 2014 -.19 -4.25 1.14 -3.87 -.7477948 2.6614922682444253 7 2 5 2014 2.06 -1.83 -.26 1.14 3.089635 2.6614922682444253 7 2 6 2014 2.61 3.06 -.74 .7 2.1929486 2.6614922682444253 7 2 7 2014 -2.04 -4.23 .01 -.24 -1.4307023 2.6614922682444253 7 2 8 2014 4.24 .37 -.57 .81 3.7009826 2.6614922682444253 7 2 9 2014 -1.97 -3.82 -1.22 .51 -1.3996656 2.6614922682444253 7 2 10 2014 2.52 4.23 -1.69 .02 2.8388646 2.6614922682444253 7 2 11 2014 2.55 -2.06 -2.99 1.03 3.1056645 2.6614922682444253 7 2 12 2014 -.06 2.54 2.07 1.08 -.9094238 2.6614922682444253 7 2 1 2015 -3.11 -.57 -3.47 3.86 -2.934771 .9491526633501053 8 2 2 2015 6.13 .53 -1.77 -2.86 7.105071 .9491526633501053 8 2 3 2015 -1.12 3.05 -.45 2.97 -1.199782 .9491526633501053 8 2 4 2015 .59 -2.97 1.85 -7.37 -.428566 .9491526633501053 8 2 5 2015 1.36 .94 -1.33 5.97 1.362967 .9491526633501053 8 2 6 2015 -1.53 2.81 -.81 3.1 -1.203177 .9491526633501053 8 2 7 2015 1.54 -4.15 -4.14 10.29 3.58163 .9491526633501053 8 2 8 2015 -6.04 .49 2.69 -2.08 -6.224092 .9491526633501053 8 2 9 2015 -3.08 -2.64 .53 5.31 -5.088461 .9491526633501053 8 2 10 2015 7.75 -1.98 -.09 -3.94 8.546943 .9491526633501053 8 2 11 2015 .56 3.64 -.51 2.27 1.36005 .9491526633501053 8 2 12 2015 -2.17 -2.81 -2.57 3.42 -1.208936 .9491526633501053 8 2 1 2016 -5.77 -3.39 2.1 1.43 -6.865881 .3057396163543065 9 end
I am trying to replicate a data with following information:
funds are sorted on January 1 each year from 1990 to 2018 into decile portfolios based on their previous calendar year's return. The portfolios are equally weighted monthly so the weights are readjusted whenever a fund disappears. Funds with the highest past one-year return comprise decile 1 and funds with the lowest comprise decile 10. Deciles 1 and 10 are further subdivided into thirds on the same measure.
The result should look like this:
1A
1B
1C
1
2
3
4
5
6
7
8
9
10
10A
10B
10C
Next to these deciles there should be the monthly excess return.
Ive tried using 'tabstat mf_u_mnret , by(decile) s(n sum mean sd)' which results in the means of the monthly excess returns over the deciles.
Summary for variables: mf_u_mnret
Group variable: decile
decile | N Sum Mean SD
---------+----------------------------------------
1 | 51928 28923.04 .5569835 4.925831
2 | 52797 30872.3 .5847358 4.646036
3 | 53078 30509 .5747956 4.561271
4 | 53001 30332.06 .5722921 4.526425
5 | 52902 29703.88 .5614889 4.532059
6 | 53088 31531.74 .5939523 4.554174
7 | 52945 31397.86 .5930279 4.633395
8 | 52904 33611.45 .6353291 4.718085
9 | 52849 33557.83 .6349757 4.905058
10 | 51686 32406.69 .6269916 5.204433
---------+----------------------------------------
Total | 527178 312845.8 .5934349 4.723781
--------------------------------------------------
Is there perhaps an error with my code, resulting in the means of the deciles being so close to one another?
I also have problems in identifying 1A-C and 10A-C.
Comment