The grouping only is to be done once in a year. While as the calculation of vw_mean_rt is to be done by month and not by fyear. So, there won't be any repeated values in that case.
Code:
* Example generated by -dataex-. For more info, type help dataex clear input float stock_id str51 stock float(mdate rt mcap mom) long obs_no 1 "3I Infotech Ltd." 582 .0838774 1300.66 -.12633932 1 1 "3I Infotech Ltd." 583 .0435588 1410.03 -.2648224 2 1 "3I Infotech Ltd." 584 -.3898757 1471.64 -.17417905 3 1 "3I Infotech Ltd." 585 -.389374 898.22 -.1374322 4 1 "3I Infotech Ltd." 586 -.2276519 548.5 -.4467936 5 1 "3I Infotech Ltd." 587 .1929012 423.63 -.8273377 6 1 "3I Infotech Ltd." 588 -.0776197 505.35 -1.1370238 7 1 "3I Infotech Ltd." 589 -.1879383 466.13 -.7739682 8 1 "3I Infotech Ltd." 590 .1226252 378.52 -.8983014 9 1 "3I Infotech Ltd." 591 .4138462 424.94 -.8540121 10 1 "3I Infotech Ltd." 592 .5984765 600.8 -1.0945559 11 1 "3I Infotech Ltd." 593 -.0251872 960.37 -.58259636 12 1 "3I Infotech Ltd." 594 .0838774 1300.66 -.12633932 1 1 "3I Infotech Ltd." 595 .0435588 1410.03 -.2648224 2 1 "3I Infotech Ltd." 596 -.3898757 1471.64 -.17417905 3 1 "3I Infotech Ltd." 597 -.389374 898.22 -.1374322 4 1 "3I Infotech Ltd." 598 -.2276519 548.5 -.4467936 5 1 "3I Infotech Ltd." 599 .1929012 423.63 -.8273377 6 1 "3I Infotech Ltd." 600 -.0776197 505.35 -1.1370238 7 1 "3I Infotech Ltd." 601 -.1879383 466.13 -.7739682 8 1 "3I Infotech Ltd." 602 .1226252 378.52 -.8983014 9 1 "3I Infotech Ltd." 603 .4138462 424.94 -.8540121 10 1 "3I Infotech Ltd." 604 .5984765 600.8 -1.0945559 11 1 "3I Infotech Ltd." 605 -.0251872 960.37 -.58259636 12 2 "3M India Ltd." 582 -.0403587 1884.08 -.04965548 13 2 "3M India Ltd." 583 .0123053 1808.04 -.00929656 14 2 "3M India Ltd." 584 -.1137713 1830.29 -.0061362 15 2 "3M India Ltd." 585 -.3538788 1622.06 -.03710564 16 2 "3M India Ltd." 586 -.0153705 1048.05 -.3864366 17 2 "3M India Ltd." 587 .0108619 1031.94 -.6253247 18 2 "3M India Ltd." 588 .0210583 1043.15 -.8057244 19 2 "3M India Ltd." 589 -.0546272 1065.11 -.6232147 20 2 "3M India Ltd." 590 -.0166135 1006.93 -.6206292 21 2 "3M India Ltd." 591 .1656428 990.2 -.6131397 22 2 "3M India Ltd." 592 .2114972 1154.22 -.6534325 23 2 "3M India Ltd." 593 .035205 1398.33 -.48089325 24 2 "3M India Ltd." 594 -.0403587 1884.08 -.04965548 13 2 "3M India Ltd." 595 .0123053 1808.04 -.00929656 14 2 "3M India Ltd." 596 -.1137713 1830.29 -.0061362 15 2 "3M India Ltd." 597 -.3538788 1622.06 -.03710564 16 2 "3M India Ltd." 598 -.0153705 1048.05 -.3864366 17 2 "3M India Ltd." 599 .0108619 1031.94 -.6253247 18 2 "3M India Ltd." 600 .0210583 1043.15 -.8057244 19 2 "3M India Ltd." 601 -.0546272 1065.11 -.6232147 20 2 "3M India Ltd." 602 -.0166135 1006.93 -.6206292 21 2 "3M India Ltd." 603 .1656428 990.2 -.6131397 22 2 "3M India Ltd." 604 .2114972 1154.22 -.6534325 23 2 "3M India Ltd." 605 .035205 1398.33 -.48089325 24 3 "63 Moons Technologies Ltd." 582 -.142147 7708.22 -.4747278 25 3 "63 Moons Technologies Ltd." 583 -.0119002 6612.52 -.34828845 26 3 "63 Moons Technologies Ltd." 584 -.2733497 6533.83 -.4611148 27 3 "63 Moons Technologies Ltd." 585 -.5062092 4747.81 -.5940032 28 3 "63 Moons Technologies Ltd." 586 .1500146 2344.42 -.8046051 29 3 "63 Moons Technologies Ltd." 587 -.1507828 2696.12 -1.2564824 30 3 "63 Moons Technologies Ltd." 588 -.0406813 2289.59 -1.1768951 31 3 "63 Moons Technologies Ltd." 589 -.1151034 2196.45 -1.1431617 32 3 "63 Moons Technologies Ltd." 590 .4634089 1943.63 -1.1361648 33 3 "63 Moons Technologies Ltd." 591 .1415551 2844.33 -1.0393578 34 3 "63 Moons Technologies Ltd." 592 1.04409 3246.96 -.6623359 35 3 "63 Moons Technologies Ltd." 593 -.0962323 6637.07 -.51756567 36 3 "63 Moons Technologies Ltd." 594 -.142147 7708.22 -.4747278 25 3 "63 Moons Technologies Ltd." 595 -.0119002 6612.52 -.34828845 26 3 "63 Moons Technologies Ltd." 596 -.2733497 6533.83 -.4611148 27 3 "63 Moons Technologies Ltd." 597 -.5062092 4747.81 -.5940032 28 3 "63 Moons Technologies Ltd." 598 .1500146 2344.42 -.8046051 29 3 "63 Moons Technologies Ltd." 599 -.1507828 2696.12 -1.2564824 30 3 "63 Moons Technologies Ltd." 600 -.0406813 2289.59 -1.1768951 31 3 "63 Moons Technologies Ltd." 601 -.1151034 2196.45 -1.1431617 32 3 "63 Moons Technologies Ltd." 602 .4634089 1943.63 -1.1361648 33 3 "63 Moons Technologies Ltd." 603 .1415551 2844.33 -1.0393578 34 3 "63 Moons Technologies Ltd." 604 1.04409 3246.96 -.6623359 35 3 "63 Moons Technologies Ltd." 605 -.0962323 6637.07 -.51756567 36 4 "A B B India Ltd." 582 -.0510167 17196.36 .01307075 37 4 "A B B India Ltd." 583 .140761 16319.06 -.24201564 38 4 "A B B India Ltd." 584 -.1065452 18616.15 -.2728126 39 4 "A B B India Ltd." 585 -.3175564 16632.69 -.28264767 40 4 "A B B India Ltd." 586 -.1824886 11350.87 -.6069741 41 4 "A B B India Ltd." 587 .0355104 9279.47 -.9133573 42 4 "A B B India Ltd." 588 .05811 9608.99 -1.0667948 43 4 "A B B India Ltd." 589 -.2337432 10167.36 -.7721182 44 4 "A B B India Ltd." 590 .1625187 7790.81 -.745961 45 4 "A B B India Ltd." 591 .1402668 9056.96 -.9950995 46 4 "A B B India Ltd." 592 .3331282 10327.35 -.8049828 47 4 "A B B India Ltd." 593 .1960904 13767.69 -.5617028 48 4 "A B B India Ltd." 594 -.0510167 17196.36 .01307075 37 4 "A B B India Ltd." 595 .140761 16319.06 -.24201564 38 4 "A B B India Ltd." 596 -.1065452 18616.15 -.2728126 39 4 "A B B India Ltd." 597 -.3175564 16632.69 -.28264767 40 4 "A B B India Ltd." 598 -.1824886 11350.87 -.6069741 41 4 "A B B India Ltd." 599 .0355104 9279.47 -.9133573 42 4 "A B B India Ltd." 600 .05811 9608.99 -1.0667948 43 4 "A B B India Ltd." 601 -.2337432 10167.36 -.7721182 44 4 "A B B India Ltd." 602 .1625187 7790.81 -.745961 45 4 "A B B India Ltd." 603 .1402668 9056.96 -.9950995 46 4 "A B B India Ltd." 604 .3331282 10327.35 -.8049828 47 4 "A B B India Ltd." 605 .1960904 13767.69 -.5617028 48 5 "A B G Shipyard Ltd." 582 .0023809 1710.97 .4182359 49 5 "A B G Shipyard Ltd." 583 .076158 1715.05 -.3066918 50 5 "A B G Shipyard Ltd." 584 -.1761622 1845.66 -.2124559 51 5 "A B G Shipyard Ltd." 585 -.6138647 1520.52 -.3513617 52 5 "A B G Shipyard Ltd." 586 -.1548136 587.13 -.7107053 53 5 "A B G Shipyard Ltd." 587 .3689071 496.23 -1.6249357 54 5 "A B G Shipyard Ltd." 588 -.4745127 679.3 -1.7767352 55 5 "A B G Shipyard Ltd." 589 .1875892 356.96 -1.1264615 56 5 "A B G Shipyard Ltd." 590 -.0054054 423.92 -1.5662667 57 5 "A B G Shipyard Ltd." 591 .3864734 421.63 -1.424076 58 5 "A B G Shipyard Ltd." 592 .9551393 584.58 -1.2780082 59 5 "A B G Shipyard Ltd." 593 -.113611 1142.94 -.7924519 60 5 "A B G Shipyard Ltd." 594 .0023809 1710.97 .4182359 49 5 "A B G Shipyard Ltd." 595 .076158 1715.05 -.3066918 50 5 "A B G Shipyard Ltd." 596 -.1761622 1845.66 -.2124559 51 5 "A B G Shipyard Ltd." 597 -.6138647 1520.52 -.3513617 52 5 "A B G Shipyard Ltd." 598 -.1548136 587.13 -.7107053 53 5 "A B G Shipyard Ltd." 599 .3689071 496.23 -1.6249357 54 5 "A B G Shipyard Ltd." 600 -.4745127 679.3 -1.7767352 55 5 "A B G Shipyard Ltd." 601 .1875892 356.96 -1.1264615 56 5 "A B G Shipyard Ltd." 602 -.0054054 423.92 -1.5662667 57 5 "A B G Shipyard Ltd." 603 .3864734 421.63 -1.424076 58 5 "A B G Shipyard Ltd." 604 .9551393 584.58 -1.2780082 59 5 "A B G Shipyard Ltd." 605 -.113611 1142.94 -.7924519 60 6 "A C C Ltd." 582 .1182775 9804.72 -.27696294 61 6 "A C C Ltd." 583 -.0387643 10964.48 -.6211684 62 6 "A C C Ltd." 584 .0890234 10539.54 -.5085044 63 6 "A C C Ltd." 585 -.1933296 11477.8 -.6682932 64 6 "A C C Ltd." 586 -.1766315 9258.83 -.4810188 65 6 "A C C Ltd." 587 .1763692 7623.52 -.6854388 66 6 "A C C Ltd." 588 .0563926 8969.31 -.8024532 67 6 "A C C Ltd." 589 .0692284 9475.17 -.3900176 68 6 "A C C Ltd." 590 .0682661 10131.12 -.346466 69 6 "A C C Ltd." 591 .1324026 10822.84 -.319372 70 6 "A C C Ltd." 592 .1990812 12255.82 -.1694572 71 6 "A C C Ltd." 593 -.0180076 14696.14 .09212224 72 6 "A C C Ltd." 594 .1182775 9804.72 -.27696294 61 6 "A C C Ltd." 595 -.0387643 10964.48 -.6211684 62 6 "A C C Ltd." 596 .0890234 10539.54 -.5085044 63 6 "A C C Ltd." 597 -.1933296 11477.8 -.6682932 64 6 "A C C Ltd." 598 -.1766315 9258.83 -.4810188 65 6 "A C C Ltd." 599 .1763692 7623.52 -.6854388 66 6 "A C C Ltd." 600 .0563926 8969.31 -.8024532 67 6 "A C C Ltd." 601 .0692284 9475.17 -.3900176 68 6 "A C C Ltd." 602 .0682661 10131.12 -.346466 69 6 "A C C Ltd." 603 .1324026 10822.84 -.319372 70 6 "A C C Ltd." 604 .1990812 12255.82 -.1694572 71 6 "A C C Ltd." 605 -.0180076 14696.14 .09212224 72 7 "A I A Engineering Ltd." 582 .0162214 2363.88 -.06549466 73 7 "A I A Engineering Ltd." 583 .2260563 2402.23 -.13198939 74 7 "A I A Engineering Ltd." 584 -.1385219 2945.27 -.04077616 75 7 "A I A Engineering Ltd." 585 -.4228988 2537.28 .24033707 76 7 "A I A Engineering Ltd." 586 -.1980103 1464.27 -.00271862 77 7 "A I A Engineering Ltd." 587 .0892358 1174.33 -.4690253 78 7 "A I A Engineering Ltd." 588 -.1620132 1279.12 -.7592906 79 7 "A I A Engineering Ltd." 589 .018413 1071.89 -.5515879 80 7 "A I A Engineering Ltd." 590 .0714593 1091.62 -.8733688 81 7 "A I A Engineering Ltd." 591 .359984 1169.63 -.7269815 82 7 "A I A Engineering Ltd." 592 .4254062 1590.68 -.7309081 83 7 "A I A Engineering Ltd." 593 -.0580311 2267.36 -.33348125 84 7 "A I A Engineering Ltd." 594 .0162214 2363.88 -.06549466 73 7 "A I A Engineering Ltd." 595 .2260563 2402.23 -.13198939 74 7 "A I A Engineering Ltd." 596 -.1385219 2945.27 -.04077616 75 7 "A I A Engineering Ltd." 597 -.4228988 2537.28 .24033707 76 7 "A I A Engineering Ltd." 598 -.1980103 1464.27 -.00271862 77 7 "A I A Engineering Ltd." 599 .0892358 1174.33 -.4690253 78 7 "A I A Engineering Ltd." 600 -.1620132 1279.12 -.7592906 79 7 "A I A Engineering Ltd." 601 .018413 1071.89 -.5515879 80 7 "A I A Engineering Ltd." 602 .0714593 1091.62 -.8733688 81 7 "A I A Engineering Ltd." 603 .359984 1169.63 -.7269815 82 7 "A I A Engineering Ltd." 604 .4254062 1590.68 -.7309081 83 7 "A I A Engineering Ltd." 605 -.0580311 2267.36 -.33348125 84 8 "Aban Offshore Ltd." 582 -.1301212 10872.86 .46470425 85 8 "Aban Offshore Ltd." 583 -.1686682 9458.07 .18721692 86 8 "Aban Offshore Ltd." 584 -.0145899 7862.79 .08449736 87 8 "Aban Offshore Ltd." 585 -.611874 7748.9 -.2840065 88 8 "Aban Offshore Ltd." 586 -.1288336 3007.55 -.6351557 89 8 "Aban Offshore Ltd." 587 -.0308757 2620.08 -1.2225515 90 8 "Aban Offshore Ltd." 588 -.2791425 2539.18 -1.4337457 91 8 "Aban Offshore Ltd." 589 -.3478934 1830.39 -1.20281 92 8 "Aban Offshore Ltd." 590 .2657165 1193.61 -1.608876 93 8 "Aban Offshore Ltd." 591 .0192669 1510.77 -1.692288 94 8 "Aban Offshore Ltd." 592 1.222536 1539.88 -1.5530345 95 8 "Aban Offshore Ltd." 593 -.0119843 3422.43 -1.7080214 96 8 "Aban Offshore Ltd." 594 -.1301212 10872.86 .46470425 85 8 "Aban Offshore Ltd." 595 -.1686682 9458.07 .18721692 86 8 "Aban Offshore Ltd." 596 -.0145899 7862.79 .08449736 87 8 "Aban Offshore Ltd." 597 -.611874 7748.9 -.2840065 88 8 "Aban Offshore Ltd." 598 -.1288336 3007.55 -.6351557 89 8 "Aban Offshore Ltd." 599 -.0308757 2620.08 -1.2225515 90 8 "Aban Offshore Ltd." 600 -.2791425 2539.18 -1.4337457 91 8 "Aban Offshore Ltd." 601 -.3478934 1830.39 -1.20281 92 8 "Aban Offshore Ltd." 602 .2657165 1193.61 -1.608876 93 8 "Aban Offshore Ltd." 603 .0192669 1510.77 -1.692288 94 8 "Aban Offshore Ltd." 604 1.222536 1539.88 -1.5530345 95 8 "Aban Offshore Ltd." 605 -.0119843 3422.43 -1.7080214 96 9 "Abbott India Ltd." 582 .0186034 774.07 .03810834 97 9 "Abbott India Ltd." 583 .0295522 745.03 .040532 98 9 "Abbott India Ltd." 584 -.1340703 767.04 -.03217566 99 9 "Abbott India Ltd." 585 -.0898703 664.21 -.00843637 100 9 "Abbott India Ltd." 594 .0186034 774.07 .03810834 97 9 "Abbott India Ltd." 595 .0295522 745.03 .040532 98 9 "Abbott India Ltd." 596 -.1340703 767.04 -.03217566 99 9 "Abbott India Ltd." 597 -.0898703 664.21 -.00843637 100 end format %tm mdate gen moy = month(dofm(mdate)) gen year = year(dofm(mdate)) // CREATE A "FISCAL YEAR" RUNNING FROM JULY THROUGH SUBSEQUENT JUNE gen fyear = cond(moy > 6, year, year-1) // MEDIAN SPLIT BASED ON JUNE VALUE OF mcap capture program drop one_year_median_split program define one_year_median_split xtile june_mcap_group = mcap, nq(2) exit end frame put stock_id year mcap if moy == 6, into(median_split) frame change median_split runby one_year_median_split, by(year) frame change default frlink m:1 stock_id fyear, frame(median_split stock_id year) frget june_mcap_group, from(median_split) frame drop median_split drop median_split // NOW SPLIT AT 30TH AND 70TH PERCENTILES OF MOM capture program drop one_year_three_groups program define one_year_three_groups _pctile mom, percentiles(30 70) gen cut = `r(r1)' in 1 replace cut = `r(r2)' in 2 xtile march_mom_group = mom, cutpoints(cut) exit end frame put stock_id year mom if moy == 3, into(three_groups) frame change three_groups runby one_year_three_groups, by(year) verbose frame change default frlink m:1 stock_id fyear, frame(three_groups stock_id year) frget march_mom_group, from(three_groups) frame drop three_groups drop three_groups capture program drop one_weighted_return program define one_weighted_return if !missing(june_mcap_group, march_mom_group) { egen numerator = total(mcap*rt) egen denominator = total(mcap) gen vw_mean_rt = numerator/denominator } exit end runby one_weighted_return, by(mdate june_mcap_group march_mom_group) collapse (first) vw_mean_rt, by(mdate june_mcap_group march_mom_group) drop if missing(vw_mean_rt) keep mdate june_mcap_group march_mom_group vw_mean_rt isid june_mcap_group march_mom_group mdate, sort by mdate june_mcap_group, sort: egen temp = mean(vw_mean_rt) by mdate (june_mcap_group), sort: gen SMB = temp[1] - temp[_N] drop temp by mdate march_mom_group, sort: egen temp = mean(vw_mean_rt) by mdate (march_mom_group): gen HML = temp[1] - temp[_N] drop temp // AND IF YOU WANT TO REDUCE TO ONE OBSERVATION PER MONTH label define june_mcap_group 1 "S" 2 "B" label define march_mom_group 1 "L" 2 "M" 3 "H" label values june_mcap_group june_mcap_group label values march_mom_group march_mom_group decode june_mcap_group, gen (mcap_group) decode march_mom_group, gen(mom_group) drop june_mcap_group march_mom_group egen groups = concat(mcap_group mom_group) keep mdate groups SMB HML vw_mean_rt rename vw_mean_rt =_ reshape wide vw_mean_rt_, i(mdate) j(groups) string
Also, in order to have some results data to work with, I took your original data example and just duplicated every observation, changing the dates so that there would now be two July-June years worth of data. You don't need to do anything like that with your real data set. The -dataex- part is just to provide something to demo the code.
Comment