measuring portfolio daily returns, after sorting funds into groups based on year t-1 volatility

[Yanyu Li]

I have the daily returns for 278 funds. I calculated the standard deviation over the year for each fund. I also sorted the funds into 10 groups based on yearly standard deviation.

Then, what I want to do is: calculating the portfolio's daily returns for the next year. So, the problem is the portfolios re-sorted yearly, and the portfolio's returns that I want to calculate is daily in the next year. So, I don't know how to figure it out.

I had one idea is:
First, egen a 1-year lagged time variable

gen sort_time=time-1format; sort_time %ty

Second, generate a new variable (new_vol_unique_yearly) by matching the vol_unique_yearly with the sort_time (I have no idea how to write the code), so the year 1989 will be empty, the new variable will be start from 1990

Third, I can sort funds into groups based on new variable

ssc install egenmore; egen vol_group=xtile(new_vol_unique_yearly), nq(10) by(sort_time)

Forth, I will calculate the portfolio daily return by the following code:

egen group1=group(vol_group time)
sort group1
egen group2=group(group1 Name)
sort group2
by group2, sort: egen vol_rp=mean(UT)

Is it feasible? If it is, how to write the code for the second stage? If it is not, I am really grateful for any suggestions. MANY THX!

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Name double(rm rf rmrf) int id double UT float(add_ut_daily time group vol_unique_yearly sort_time)
10959 .0051641 .0005273 .00463677   1 -.0073132214800006754  -.012683742 1990    1 .008186089 1989
10959 .0051641 .0005273 .00463677   2                     0   -.00537052 1990   14 .006704837 1989
10959 .0051641 .0005273 .00463677   4   .007864509080544818  .0024939885 1990   42  .00642566 1989
10959 .0051641 .0005273 .00463677   5   .011066155864239683   .005695635 1990   65 .008037343 1989
10959 .0051641 .0005273 .00463677   6   .010015028490713111   .004644508 1990   80 .007166115 1989
10959 .0051641 .0005273 .00463677   7   .011321639219625687   .005951119 1990   95 .008561103 1989
10959 .0051641 .0005273 .00463677   8   .011451424620719861   .006080904 1990  110 .007710387 1989
10959 .0051641 .0005273 .00463677  11   .006951422461694154   .001580902 1990  153 .007646048 1989
10959 .0051641 .0005273 .00463677  12   .008491754820561987   .003121234 1990  167 .006521586 1989
10959 .0051641 .0005273 .00463677  14  .0074879628306570994  .0021174422 1990  185  .00568583 1989
10959 .0051641 .0005273 .00463677  15  -.013116571107528141  -.018487092 1990  203 .008288375 1989
10959 .0051641 .0005273 .00463677  16 -.0017048617385306097  -.007075382 1990  216 .009480007 1989
10959 .0051641 .0005273 .00463677  17   .012287108160070346   .006916588 1990  237  .00729491 1989
10959 .0051641 .0005273 .00463677  22                     0   -.00537052 1990  297 .009458628 1989
10959 .0051641 .0005273 .00463677  23  .0055789854150412755  .0002084649 1990  315 .004633269 1989
10959 .0051641 .0005273 .00463677  24   .011596665905581853   .006226145 1990  336 .014137934 1989
10959 .0051641 .0005273 .00463677  25                     0   -.00537052 1990  359  .01520184 1989
10959 .0051641 .0005273 .00463677  26   .015543328468069639   .010172808 1990  372 .006860244 1989
10959 .0051641 .0005273 .00463677  28   .008259559581204423   .002889039 1990  392 .013096617 1989
10959 .0051641 .0005273 .00463677  31   .005967426475864591   .000596906 1990  432 .005238086 1989
10959 .0051641 .0005273 .00463677  35   .008751933598375212   .003381413 1990  478 .009571055 1989
10959 .0051641 .0005273 .00463677  36   .008787280198761198    .00341676 1990  494 .006753792 1989
10959 .0051641 .0005273 .00463677  37   .016533396209218548   .011162876 1990  511 .011159747 1989
10959 .0051641 .0005273 .00463677 131   .012182962478310584   .006812442 1990 1478 .013734082 1989
10959 .0051641 .0005273 .00463677 132                     0   -.00537052 1990 1504 .006001617 1989
10959 .0051641 .0005273 .00463677 133   .008042047624611096   .002671527 1990 1530 .007046652 1989
10959 .0051641 .0005273 .00463677 134   .009363417701815955   .003992897 1990 1556  .00850876 1989
10959 .0051641 .0005273 .00463677 135   .003743132985763919 -.0016273875 1990 1582 .006764278 1989
10959 .0051641 .0005273 .00463677 136   .007720670255422476    .00235015 1990 1607 .008676307 1989
10959 .0051641 .0005273 .00463677 137                     0   -.00537052 1990 1633 .010870368 1989
10959 .0051641 .0005273 .00463677 138                     0   -.00537052 1990 1659  .01269936 1989
10959 .0051641 .0005273 .00463677 139   .006371847667490941  .0010013272 1990 1685 .006219334 1989
10959 .0051641 .0005273 .00463677 140   .011193109739723432   .005822589 1990 1711 .007393776 1989
10959 .0051641 .0005273 .00463677 141                     0   -.00537052 1990 1737 .011777747 1989
10959 .0051641 .0005273 .00463677 142                     0   -.00537052 1990 1763 .006813985 1989
10959 .0051641 .0005273 .00463677 143  .0025411335256378268  -.002829387 1990 1789  .00802959 1989
10959 .0051641 .0005273 .00463677 144    .01125504283973175   .005884522 1990 1815 .008027705 1989
10959 .0051641 .0005273 .00463677 145                     0   -.00537052 1990 1841  .01157636 1989
10959 .0051641 .0005273 .00463677 146  .0070717106021397445    .00170119 1990 1867 .007883353 1989
10959 .0051641 .0005273 .00463677 147                     0   -.00537052 1990 1893 .006693038 1989
10959 .0051641 .0005273 .00463677 148                     0   -.00537052 1990 1919 .005939841 1989
10959 .0051641 .0005273 .00463677 149                     0   -.00537052 1990 1945 .006670801 1989
10959 .0051641 .0005273 .00463677 150   .001971528113896478 -.0033989924 1990 1971  .00696747 1989
10959 .0051641 .0005273 .00463677 151                     0   -.00537052 1990 1997 .006081656 1989
10959 .0051641 .0005273 .00463677 152                     0   -.00537052 1990 2023 .010023894 1989
10959 .0051641 .0005273 .00463677 153                     0   -.00537052 1990 2049 .010932214 1989
10959 .0051641 .0005273 .00463677 154   .023381876814584607   .018011356 1990 2075  .00712135 1989
10959 .0051641 .0005273 .00463677 155  -.005036434784881888  -.010406955 1990 2101 .009449773 1989
10959 .0051641 .0005273 .00463677 156   .023822898612798578   .018452378 1990 2127  .01164404 1989
10959 .0051641 .0005273 .00463677 157   .007033457993759473  .0016629375 1990 2153  .01127639 1989
10959 .0051641 .0005273 .00463677 158                     0   -.00537052 1990 2179 .006601872 1989
10959 .0051641 .0005273 .00463677 159   .008096347624895924   .002725827 1990 2205 .006661708 1989
10959 .0051641 .0005273 .00463677 160                     0   -.00537052 1990 2231 .006225983 1989
10959 .0051641 .0005273 .00463677 161   .007240162639145381  .0018696422 1990 2257 .009017049 1989
10959 .0051641 .0005273 .00463677 162   .012253570138626208    .00688305 1990 2283 .006991299 1989
10959 .0051641 .0005273 .00463677 163   .003154434592597267  -.002216086 1990 2309 .006045517 1989
10959 .0051641 .0005273 .00463677 164    .00815093129904151   .002780411 1990 2334 .011524676 1989
10959 .0051641 .0005273 .00463677 165   .010740068650561608   .005369548 1990 2360 .007793026 1989
10960 .0131379 .0005276 .01261032   1   .010169472770298055  -.002219254 1990    1 .008186089 1989
10960 .0131379 .0005276 .01261032   2    .01945556929411236   .007066843 1990   14 .006704837 1989
10960 .0131379 .0005276 .01261032   4   .005861373408863951  -.006527353 1990   42  .00642566 1989
10960 .0131379 .0005276 .01261032   5   .008153769116468165 -.0042349575 1990   65 .008037343 1989
10960 .0131379 .0005276 .01261032   6   .007934679449636196 -.0044540474 1990   80 .007166115 1989
10960 .0131379 .0005276 .01261032   7  .0076460148005410315  -.004742712 1990   95 .008561103 1989
10960 .0131379 .0005276 .01261032   8   .007942526322210352 -.0044462006 1990  110 .007710387 1989
10960 .0131379 .0005276 .01261032  11   .008940858558079561  -.003447868 1990  153 .007646048 1989
10960 .0131379 .0005276 .01261032  12   .010853369334912835 -.0015353573 1990  167 .006521586 1989
10960 .0131379 .0005276 .01261032  14   .009365773947960277  -.003022953 1990  185  .00568583 1989
10960 .0131379 .0005276 .01261032  15    .02193759222194742   .009548865 1990  203 .008288375 1989
10960 .0131379 .0005276 .01261032  16   .004515237318675541 -.0078734895 1990  216 .009480007 1989
10960 .0131379 .0005276 .01261032  17   .007313172293843255  -.005075554 1990  237  .00729491 1989

[Phil Bromiley]

You didn't get a quick answer. It may be that your question is relatively long and complicated.

One way to do this would be by calculating the return on each stock while the data is still sorted by stock-year, Do your portfolio indicators. Then use collapse by portfolio year to generate the means and standard deviations by portfolio-year. I assume at the end you'll be working on yearly data? If not, you can merge the collapsed data with the uncollapsed data.