IVOL Factor

[Sartaj Hussain]

Some time back we used below code to construct SMB and HML factors based on sorts using mcap of June month and bmratio of March month each year t. Now, instead of bmr, i want second sort on idiovol observed in June month of each year to construct new SMB and LMH factors. Also, cut offs or breakpoints for idiovol sort should be 33.33 and 66.67 instead of previous 30 and 70. The break point for mcap sort should be median. The new factor LMH is equal to difference between simple average of (S/L, B/L) and (S/H, B/H). I append the code and compatible data file and look forward for necessary modifications.

Data File

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str52 stock float(date mdate rt mcap idiovol)
1 "3M India Ltd."               20971 689           . 14880.26 .030854195
1 "3M India Ltd."               21001 690  .012188475 15061.62  .04349442
1 "3M India Ltd."               21032 691   .10125874 16586.75   .0621037
1 "3M India Ltd."               21063 692  -.01966171 16260.62  .02093438
1 "3M India Ltd."               21093 693 -.017932642 15969.03  .02951201
1 "3M India Ltd."               21124 694    .0912477 17426.16  .10230613
1 "3M India Ltd."               21154 695    .2389296 21589.79  .12055484
1 "3M India Ltd."               21185 696   -.0229947 21093.34  .06959994
1 "3M India Ltd."               21216 697    .1045953  23299.6   .1569885
1 "3M India Ltd."               21244 698 -.062826864 21835.76    .032576
1 "3M India Ltd."               21275 699   .05668452 23073.51  .04730011
1 "3M India Ltd."               21305 700  -.05294316 21851.93  .05523421
1 "3M India Ltd."               21336 701   .01194714 22112.99  .05845971
2 "A B B India Ltd."            20971 689           . 30750.02  .06163194
2 "A B B India Ltd."            21001 690 -.016194612 30252.04 .036971405
2 "A B B India Ltd."            21032 691  -.05873492 28475.19 .033790894
2 "A B B India Ltd."            21063 692   .03892097 29583.47  .05326683
2 "A B B India Ltd."            21093 693   -.0094553 29303.75  .04159006
2 "A B B India Ltd."            21124 694   .00846084 29551.68   .0470377
2 "A B B India Ltd."            21154 695  .003549497 29656.58  .04584638
2 "A B B India Ltd."            21185 696   .18420868 35119.57  .07194038
2 "A B B India Ltd."            21216 697  -.07406625  32518.4  .06020544
2 "A B B India Ltd."            21244 698  -.16157836 27264.13 .027741956
2 "A B B India Ltd."            21275 699    .0531634 28713.58  .05918191
2 "A B B India Ltd."            21305 700  -.09398522 26014.93  .05317919
2 "A B B India Ltd."            21336 701   -.0383253  25017.9  .07222698
3 "A C C Ltd."                  20971 689           .  29444.1 .034043655
3 "A C C Ltd."                  21001 690   .10536055 32546.35  .05223475
3 "A C C Ltd."                  21032 691   .03903299 33816.73  .04923428
3 "A C C Ltd."                  21063 692  -.08143604 31062.83  .04735231
3 "A C C Ltd."                  21093 693   .09155757 33906.87  .04126554
3 "A C C Ltd."                  21124 694   -.0770381 31294.75  .03942691
3 "A C C Ltd."                  21154 695    .0548455 33011.12 .036063313
3 "A C C Ltd."                  21185 696 -.023607714 32231.81  .05039952
3 "A C C Ltd."                  21216 697  -.05278488 30530.45   .0664309
3 "A C C Ltd."                  21244 698  -.07233368 28322.08     .04139
3 "A C C Ltd."                  21275 699    .0499271 29736.11  .03316721
3 "A C C Ltd."                  21305 700  -.14354281 25467.71  .05211456
3 "A C C Ltd."                  21336 701  -.01319857 25131.57  .06173234
4 "A I A Engineering Ltd."      20971 689           . 13153.45  .07784388
4 "A I A Engineering Ltd."      21001 690   .03524427 13617.03   .0565655
4 "A I A Engineering Ltd."      21032 691  -.10698203 12160.25 .069798686
4 "A I A Engineering Ltd."      21063 692   .02644947 12481.89  .05084259
4 "A I A Engineering Ltd."      21093 693   .02886619 12842.19  .06197286
4 "A I A Engineering Ltd."      21124 694   .08163483 13890.56  .06435061
4 "A I A Engineering Ltd."      21154 695   .04987443 14583.34  .05660894
4 "A I A Engineering Ltd."      21185 696  .002199026 14615.41 .067634605
4 "A I A Engineering Ltd."      21216 697   -.0751509 13517.05  .05549525
4 "A I A Engineering Ltd."      21244 698 -.004326252 13458.57  .06685477
4 "A I A Engineering Ltd."      21275 699  .012439554 13625.99   .0454492
4 "A I A Engineering Ltd."      21305 700   .07313192 14622.49  .05373463
4 "A I A Engineering Ltd."      21336 701  -.03705739 14080.62  .04916281
5 "A P L Apollo Tubes Ltd."     20971 689           .  3654.91  .10767166
5 "A P L Apollo Tubes Ltd."     21001 690   .02362207  3741.37 .025067857
5 "A P L Apollo Tubes Ltd."     21032 691 -.007692315  3712.47  .07237318
5 "A P L Apollo Tubes Ltd."     21063 692   .07580374  3994.02  .08805783
5 "A P L Apollo Tubes Ltd."     21093 693   .09704098  4407.46  .08370487
5 "A P L Apollo Tubes Ltd."     21124 694    .0535156  4643.33  .07357185
5 "A P L Apollo Tubes Ltd."     21154 695  .013031496  4703.84  .06042716
5 "A P L Apollo Tubes Ltd."     21185 696   .09483934  5149.96  .14989756
5 "A P L Apollo Tubes Ltd."     21216 697  -.05100674  4887.27   .1172995
5 "A P L Apollo Tubes Ltd."     21244 698 -.031559527  4733.03 .065598994
5 "A P L Apollo Tubes Ltd."     21275 699    .0959089  5186.98  .05031106
5 "A P L Apollo Tubes Ltd."     21305 700   -.1393476  4464.41  .05615015
5 "A P L Apollo Tubes Ltd."     21336 701  -.11598366   3946.5  .06485675
6 "A U Small Finance Bank Ltd." 20971 689           .        .          .
6 "A U Small Finance Bank Ltd." 21001 690           . 16753.75          .
6 "A U Small Finance Bank Ltd." 21032 691  -.06905329 15596.85  .05831755
6 "A U Small Finance Bank Ltd." 21063 692  .034809504 16139.77  .05591066
6 "A U Small Finance Bank Ltd." 21093 693    .0240402 16527.77   .0982524
6 "A U Small Finance Bank Ltd." 21124 694   .19700746 19870.32  .07655682
6 "A U Small Finance Bank Ltd." 21154 695  -.04094828 19065.33  .06842521
6 "A U Small Finance Bank Ltd." 21185 696 -.015880113 18762.57  .04706053
6 "A U Small Finance Bank Ltd." 21216 697   -.1007764 16875.05 .036987774
6 "A U Small Finance Bank Ltd." 21244 698   .04613171 17655.06  .05148622
6 "A U Small Finance Bank Ltd." 21275 699    .1661137  20587.8  .09375516
6 "A U Small Finance Bank Ltd." 21305 700 -.032958645 19909.26  .07907445
6 "A U Small Finance Bank Ltd." 21336 701  -.05689885 19061.04  .06512215
7 "Aarti Industries Ltd."       20971 689           .  7547.68  .09839864
7 "Aarti Industries Ltd."       21001 690   .05518325  7964.45  .05101867
7 "Aarti Industries Ltd."       21032 691  -.10154256  7155.56  .07032389
7 "Aarti Industries Ltd."       21063 692  .021850945     7312  .05502936
7 "Aarti Industries Ltd."       21093 693   .06855343  7813.34  .05571729
7 "Aarti Industries Ltd."       21124 694  -.04313459  7476.24  .03745327
7 "Aarti Industries Ltd."       21154 695    .2472759  9324.77  .10898668
7 "Aarti Industries Ltd."       21185 696 -.069008015  8681.36  .06980622
7 "Aarti Industries Ltd."       21216 697      .11249  9658.18 .063616745
7 "Aarti Industries Ltd."       21244 698 -.027107004  9302.35  .06916821
7 "Aarti Industries Ltd."       21275 699   .17514424  10931.6  .05467262
7 "Aarti Industries Ltd."       21305 700  -.07535323 10108.03  .06475983
7 "Aarti Industries Ltd."       21336 701 -.007689385 10029.98  .06876207
8 "Aban Offshore Ltd."          20971 689           .   1092.6  .13090679
8 "Aban Offshore Ltd."          21001 690  -.01415595  1077.13  .03033517
8 "Aban Offshore Ltd."          21032 691  -.04009758  1033.94   .1492878
8 "Aban Offshore Ltd."          21063 692    .0270957  1061.96  .06336385
8 "Aban Offshore Ltd."          21093 693   .10662275  1175.19  .05887188
8 "Aban Offshore Ltd."          21124 694  -.02085926  1150.67  .11674014
8 "Aban Offshore Ltd."          21154 695   .03068731  1185.98  .09699592
8 "Aban Offshore Ltd."          21185 696   .03789369  1230.92   .1754007
8 "Aban Offshore Ltd."          21216 697  -.18895207   998.34  .06364476
end
format %td date
format %tm mdate

The Code

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)
frame put stock_id fyear mcap bmr, into(mcap_bmr_work)
frame change mcap_bmr_work
collapse (count) n_mcap = mcap n_bmr = bmr (firstnm) mcap bmr, by(stock_id fyear)
assert n_mcap <= 1 & n_bmr <= 1 // VERIFY UNIQUE VALUE OF MCAP AND BMR
replace fyear = fyear + 1 // CHANGE THE FYEAR TO WHICH THEY WILL APPLY
frame change default
rename (mcap bmr) orig=
frlink m:1 stock_id fyear, frame(mcap_bmr_work)
frget mcap bmr, from(mcap_bmr_work)
frame drop mcap_bmr_work
drop mcap_bmr_work
egen byte representative = tag(stock_id fyear)

//  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 fyear mcap if representative & !missing(mcap), into(median_split) // ***
frame change median_split
runby one_year_median_split, by(fyear)
frame change default
frlink m:1 stock_id fyear, frame(median_split stock_id fyear) // ***
frget june_mcap_group, from(median_split)
frame drop median_split
drop median_split

//  NOW SPLIT AT 30TH AND 70TH PERCENTILES OF bmr
capture program drop one_year_three_groups
program define one_year_three_groups
    if _N > = 3 {
        _pctile bmr, percentiles(30 70)
        gen cut = `r(r1)' in 1
        replace cut = `r(r2)' in 2
        xtile march_bmr_group = bmr, cutpoints(cut)
    }
    else {
        gen march_bmr_group = .
    }
    exit
end
frame put stock_id fyear bmr if representative & !missing(bmr), into(three_groups) // ***
frame change three_groups
runby one_year_three_groups, by(fyear) verbose
frame change default
frlink m:1 stock_id fyear, frame(three_groups stock_id fyear) // ***
frget march_bmr_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_bmr_group) {
        egen numerator = total(mcap*rt)
        egen denominator = total(mcap)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end
drop if missing(june_mcap_group, march_bmr_group)
runby one_weighted_return, by(mdate june_mcap_group march_bmr_group)

collapse (first) vw_mean_rt, by(mdate june_mcap_group march_bmr_group)
drop if missing(vw_mean_rt)
keep mdate june_mcap_group march_bmr_group vw_mean_rt

isid june_mcap_group march_bmr_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_bmr_group, sort: egen temp = mean(vw_mean_rt)
by mdate (march_bmr_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_bmr_group 1 "L" 2 "M" 3 "H"
label values june_mcap_group june_mcap_group
label values march_bmr_group march_bmr_group
decode june_mcap_group, gen (mcap_group)
decode march_bmr_group, gen(bmr_group)
drop june_mcap_group march_bmr_group
egen groups = concat(mcap_group bmr_group)
keep mdate groups SMB HML vw_mean_rt
rename vw_mean_rt =_
reshape wide vw_mean_rt_, i(mdate) j(groups) string

[Clyde Schechter]

There are a few things left unclear here.

In the previous calculations, we matched each month to the mcap and bmr values from the june and march of the preceding fiscal year. You make no mention of that here. I assume you still want that.

In the previous calculations, the variable HML was actually calculated as the average in the L bmr group minus the average in the H bmr group. (The SMB was similarly counted as the average in the S minus the average in the B). This time you want to call the idio_vol based index LMH. I don't know if the change in the name means you also want to calculate the index in the reverse way, as the average in H minus the average in L, or the other way around. In the code below, I have avoided the issue altogether: I call the variable HML and it is the average value weighted mean return in the L idiovol group minus the average value weighted mean return in the H idiovol group. It will be simple enough for you to change the name from HML to LMH, or replace its calculated value by its negative, or both should you desire to.

* Example generated by -dataex-. For more info, type help dataex
clear*
input int stock_id str52 stock float(date mdate rt mcap idiovol)
1 "3M India Ltd."               20971 689           . 14880.26 .030854195
1 "3M India Ltd."               21001 690  .012188475 15061.62  .04349442
1 "3M India Ltd."               21032 691   .10125874 16586.75   .0621037
1 "3M India Ltd."               21063 692  -.01966171 16260.62  .02093438
1 "3M India Ltd."               21093 693 -.017932642 15969.03  .02951201
1 "3M India Ltd."               21124 694    .0912477 17426.16  .10230613
1 "3M India Ltd."               21154 695    .2389296 21589.79  .12055484
1 "3M India Ltd."               21185 696   -.0229947 21093.34  .06959994
1 "3M India Ltd."               21216 697    .1045953  23299.6   .1569885
1 "3M India Ltd."               21244 698 -.062826864 21835.76    .032576
1 "3M India Ltd."               21275 699   .05668452 23073.51  .04730011
1 "3M India Ltd."               21305 700  -.05294316 21851.93  .05523421
1 "3M India Ltd."               21336 701   .01194714 22112.99  .05845971
2 "A B B India Ltd."            20971 689           . 30750.02  .06163194
2 "A B B India Ltd."            21001 690 -.016194612 30252.04 .036971405
2 "A B B India Ltd."            21032 691  -.05873492 28475.19 .033790894
2 "A B B India Ltd."            21063 692   .03892097 29583.47  .05326683
2 "A B B India Ltd."            21093 693   -.0094553 29303.75  .04159006
2 "A B B India Ltd."            21124 694   .00846084 29551.68   .0470377
2 "A B B India Ltd."            21154 695  .003549497 29656.58  .04584638
2 "A B B India Ltd."            21185 696   .18420868 35119.57  .07194038
2 "A B B India Ltd."            21216 697  -.07406625  32518.4  .06020544
2 "A B B India Ltd."            21244 698  -.16157836 27264.13 .027741956
2 "A B B India Ltd."            21275 699    .0531634 28713.58  .05918191
2 "A B B India Ltd."            21305 700  -.09398522 26014.93  .05317919
2 "A B B India Ltd."            21336 701   -.0383253  25017.9  .07222698
3 "A C C Ltd."                  20971 689           .  29444.1 .034043655
3 "A C C Ltd."                  21001 690   .10536055 32546.35  .05223475
3 "A C C Ltd."                  21032 691   .03903299 33816.73  .04923428
3 "A C C Ltd."                  21063 692  -.08143604 31062.83  .04735231
3 "A C C Ltd."                  21093 693   .09155757 33906.87  .04126554
3 "A C C Ltd."                  21124 694   -.0770381 31294.75  .03942691
3 "A C C Ltd."                  21154 695    .0548455 33011.12 .036063313
3 "A C C Ltd."                  21185 696 -.023607714 32231.81  .05039952
3 "A C C Ltd."                  21216 697  -.05278488 30530.45   .0664309
3 "A C C Ltd."                  21244 698  -.07233368 28322.08     .04139
3 "A C C Ltd."                  21275 699    .0499271 29736.11  .03316721
3 "A C C Ltd."                  21305 700  -.14354281 25467.71  .05211456
3 "A C C Ltd."                  21336 701  -.01319857 25131.57  .06173234
4 "A I A Engineering Ltd."      20971 689           . 13153.45  .07784388
4 "A I A Engineering Ltd."      21001 690   .03524427 13617.03   .0565655
4 "A I A Engineering Ltd."      21032 691  -.10698203 12160.25 .069798686
4 "A I A Engineering Ltd."      21063 692   .02644947 12481.89  .05084259
4 "A I A Engineering Ltd."      21093 693   .02886619 12842.19  .06197286
4 "A I A Engineering Ltd."      21124 694   .08163483 13890.56  .06435061
4 "A I A Engineering Ltd."      21154 695   .04987443 14583.34  .05660894
4 "A I A Engineering Ltd."      21185 696  .002199026 14615.41 .067634605
4 "A I A Engineering Ltd."      21216 697   -.0751509 13517.05  .05549525
4 "A I A Engineering Ltd."      21244 698 -.004326252 13458.57  .06685477
4 "A I A Engineering Ltd."      21275 699  .012439554 13625.99   .0454492
4 "A I A Engineering Ltd."      21305 700   .07313192 14622.49  .05373463
4 "A I A Engineering Ltd."      21336 701  -.03705739 14080.62  .04916281
5 "A P L Apollo Tubes Ltd."     20971 689           .  3654.91  .10767166
5 "A P L Apollo Tubes Ltd."     21001 690   .02362207  3741.37 .025067857
5 "A P L Apollo Tubes Ltd."     21032 691 -.007692315  3712.47  .07237318
5 "A P L Apollo Tubes Ltd."     21063 692   .07580374  3994.02  .08805783
5 "A P L Apollo Tubes Ltd."     21093 693   .09704098  4407.46  .08370487
5 "A P L Apollo Tubes Ltd."     21124 694    .0535156  4643.33  .07357185
5 "A P L Apollo Tubes Ltd."     21154 695  .013031496  4703.84  .06042716
5 "A P L Apollo Tubes Ltd."     21185 696   .09483934  5149.96  .14989756
5 "A P L Apollo Tubes Ltd."     21216 697  -.05100674  4887.27   .1172995
5 "A P L Apollo Tubes Ltd."     21244 698 -.031559527  4733.03 .065598994
5 "A P L Apollo Tubes Ltd."     21275 699    .0959089  5186.98  .05031106
5 "A P L Apollo Tubes Ltd."     21305 700   -.1393476  4464.41  .05615015
5 "A P L Apollo Tubes Ltd."     21336 701  -.11598366   3946.5  .06485675
6 "A U Small Finance Bank Ltd." 20971 689           .        .          .
6 "A U Small Finance Bank Ltd." 21001 690           . 16753.75          .
6 "A U Small Finance Bank Ltd." 21032 691  -.06905329 15596.85  .05831755
6 "A U Small Finance Bank Ltd." 21063 692  .034809504 16139.77  .05591066
6 "A U Small Finance Bank Ltd." 21093 693    .0240402 16527.77   .0982524
6 "A U Small Finance Bank Ltd." 21124 694   .19700746 19870.32  .07655682
6 "A U Small Finance Bank Ltd." 21154 695  -.04094828 19065.33  .06842521
6 "A U Small Finance Bank Ltd." 21185 696 -.015880113 18762.57  .04706053
6 "A U Small Finance Bank Ltd." 21216 697   -.1007764 16875.05 .036987774
6 "A U Small Finance Bank Ltd." 21244 698   .04613171 17655.06  .05148622
6 "A U Small Finance Bank Ltd." 21275 699    .1661137  20587.8  .09375516
6 "A U Small Finance Bank Ltd." 21305 700 -.032958645 19909.26  .07907445
6 "A U Small Finance Bank Ltd." 21336 701  -.05689885 19061.04  .06512215
7 "Aarti Industries Ltd."       20971 689           .  7547.68  .09839864
7 "Aarti Industries Ltd."       21001 690   .05518325  7964.45  .05101867
7 "Aarti Industries Ltd."       21032 691  -.10154256  7155.56  .07032389
7 "Aarti Industries Ltd."       21063 692  .021850945     7312  .05502936
7 "Aarti Industries Ltd."       21093 693   .06855343  7813.34  .05571729
7 "Aarti Industries Ltd."       21124 694  -.04313459  7476.24  .03745327
7 "Aarti Industries Ltd."       21154 695    .2472759  9324.77  .10898668
7 "Aarti Industries Ltd."       21185 696 -.069008015  8681.36  .06980622
7 "Aarti Industries Ltd."       21216 697      .11249  9658.18 .063616745
7 "Aarti Industries Ltd."       21244 698 -.027107004  9302.35  .06916821
7 "Aarti Industries Ltd."       21275 699   .17514424  10931.6  .05467262
7 "Aarti Industries Ltd."       21305 700  -.07535323 10108.03  .06475983
7 "Aarti Industries Ltd."       21336 701 -.007689385 10029.98  .06876207
8 "Aban Offshore Ltd."          20971 689           .   1092.6  .13090679
8 "Aban Offshore Ltd."          21001 690  -.01415595  1077.13  .03033517
8 "Aban Offshore Ltd."          21032 691  -.04009758  1033.94   .1492878
8 "Aban Offshore Ltd."          21063 692    .0270957  1061.96  .06336385
8 "Aban Offshore Ltd."          21093 693   .10662275  1175.19  .05887188
8 "Aban Offshore Ltd."          21124 694  -.02085926  1150.67  .11674014
8 "Aban Offshore Ltd."          21154 695   .03068731  1185.98  .09699592
8 "Aban Offshore Ltd."          21185 696   .03789369  1230.92   .1754007
8 "Aban Offshore Ltd."          21216 697  -.18895207   998.34  .06364476
end
format %td date
format %tm mdate

isid stock_id mdate, sort
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)

frame put stock_id fyear mcap idiovol if moy == 6, into(june_values)
frame june_values {
    replace fyear = fyear + 1 // TO SUBSEQUENT FISCAL YEAR'S VALUES
    rename (mcap idiovol) june_=
}

frlink m:1 stock_id fyear, frame(june_values)
frget june_*, from(june_values)
egen byte representative = tag(stock_id fyear)

//  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 fyear mcap if representative & !missing(mcap), into(median_split) // ***
frame change median_split
runby one_year_median_split, by(fyear)
frame change default
frlink m:1 stock_id fyear, frame(median_split stock_id fyear) // ***
frget june_mcap_group, from(median_split)
frame drop median_split
drop median_split

//  NOW SPLIT AT 33.3 TH AND 66.7 TH PERCENTILES OF idiovol
capture program drop one_year_three_groups
program define one_year_three_groups
    if _N > = 3 {
        _pctile idiovol, percentiles(33.3 66.7)
        gen cut = `r(r1)' in 1
        replace cut = `r(r2)' in 2
        xtile june_idiovol_group = idiovol, cutpoints(cut)
    }
    else {
        gen june_idiovol_group = .
    }
    exit
end
frame put stock_id fyear idiovol if representative & !missing(idiovol), into(three_groups) // ***
frame change three_groups
runby one_year_three_groups, by(fyear) verbose
frame change default
frlink m:1 stock_id fyear, frame(three_groups stock_id fyear) // ***
frget june_idiovol_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, june_idiovol_group) {
        egen numerator = total(mcap*rt) if !missing(mcap, rt)
        egen denominator = total(mcap) if !missing(mcap)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end
drop if missing(june_mcap_group, june_idiovol_group)
runby one_weighted_return, by(mdate june_mcap_group june_idiovol_group)

collapse (first) vw_mean_rt, by(mdate june_mcap_group june_idiovol_group)
drop if missing(vw_mean_rt)
keep mdate june_mcap_group june_idiovol_group vw_mean_rt

isid june_mcap_group june_idiovol_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 june_idiovol_group, sort: egen temp = mean(vw_mean_rt)
by mdate (june_idiovol_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 june_idiovol_group 1 "L" 2 "M" 3 "H"
label values june_mcap_group june_mcap_group
label values june_idiovol_group june_idiovol_group
decode june_mcap_group, gen (mcap_group)
decode june_idiovol_group, gen(idiovol_group)
drop june_mcap_group june_idiovol_group
egen groups = concat(mcap_group idiovol_group)
keep mdate groups SMB HML vw_mean_rt
rename vw_mean_rt =_
reshape wide vw_mean_rt_, i(mdate) j(groups) string

[Sartaj Hussain]

Thank you indeed. Will try it.

[Sartaj Hussain]

In the previous calculations, we matched each month to the mcap and bmr values from the june and march of the preceding fiscal year. You make no mention of that here. I assume you still want that.

Now, instead of bmr of march month, idiovol of starting or begin year june month value is to be used. Does the code in #2 take care of that?

[Clyde Schechter]

I'm not sure what you mean by "starting or begin year june month." Here's what it does. If we have a fiscal year running from, say, July 2017 through June 2018, the code uses the idiovol from June 2017.

[Sartaj Hussain]

If we have a fiscal year running from, say, July 2017 through June 2018, the code uses the idiovol from June 2017.

That is what i mean and want the code should do. Thanks again!

[Sartaj Hussain]

I tried running code given in #2 on the appended example data. It doesn't run successfully. The following error is encountered after the line which precedes the error.

by mdate june_mcap_group, sort: egen temp = mean(vw_mean_rt)
variable __000001 not found
r(111);

clear
input int stock_id str52 stock float(date mdate rt mcap idiovol)
1 "3M India Ltd."                   15492 509           .  321.22   .0916427
1 "3M India Ltd."                   15522 510   .05207787       .          .
1 "3M India Ltd."                   15553 511   .05333333       .          .
1 "3M India Ltd."                   15584 512 -.003164557       .          .
1 "3M India Ltd."                   15614 513  .003174603       .          .
1 "3M India Ltd."                   15645 514 -.008227867       .          .
1 "3M India Ltd."                   15675 515 -.034620315       .          .
1 "3M India Ltd."                   15706 516  -.02280613       .          .
1 "3M India Ltd."                   15737 517 -.063081324       .          .
1 "3M India Ltd."                   15765 518  -.13267148       .          .
1 "3M India Ltd."                   15796 519    .1862643       .          .
1 "3M India Ltd."                   15826 520   .10526316       .          .
1 "3M India Ltd."                   15857 521   .06095242       .          .
2 "A B B India Ltd."                15492 509           . 1139.01  .09048145
2 "A B B India Ltd."                15522 510  .031069733       .          .
2 "A B B India Ltd."                15553 511  .019307174       .          .
2 "A B B India Ltd."                15584 512 -.004956675       .          .
2 "A B B India Ltd."                15614 513   -.1465931       .          .
2 "A B B India Ltd."                15645 514 -.000208508       .          .
2 "A B B India Ltd."                15675 515  .036905766       .          .
2 "A B B India Ltd."                15706 516   .12306453       .          .
2 "A B B India Ltd."                15737 517   .14753802       .          .
2 "A B B India Ltd."                15765 518  -.10157583       .          .
2 "A B B India Ltd."                15796 519   .07033691       .          .
2 "A B B India Ltd."                15826 520   .11487909       .          .
2 "A B B India Ltd."                15857 521  .073351786       .          .
3 "A C C Ltd."                      15492 509           . 2714.39   .0494189
3 "A C C Ltd."                      15522 510  -.16299556       .          .
3 "A C C Ltd."                      15553 511   .03759398       .          .
3 "A C C Ltd."                      15584 512 -.001811594       .          .
3 "A C C Ltd."                      15614 513 -.036660638       .          .
3 "A C C Ltd."                      15645 514   .23247933       .          .
3 "A C C Ltd."                      15675 515  .009477243       .          .
3 "A C C Ltd."                      15706 516  -.13416116       .          .
3 "A C C Ltd."                      15737 517   .07764957       .          .
3 "A C C Ltd."                      15765 518  -.10094127       .          .
3 "A C C Ltd."                      15796 519  -.05487369       .          .
3 "A C C Ltd."                      15826 520   .11000772       .          .
3 "A C C Ltd."                      15857 521   .14934616       .          .
4 "A D C India Communications Ltd." 15492 509           .   38.98   .0899244
4 "A D C India Communications Ltd." 15522 510   -.1504425       .          .
4 "A D C India Communications Ltd." 15553 511   .02291669       .          .
4 "A D C India Communications Ltd." 15584 512  -.09029194       .          .
4 "A D C India Communications Ltd." 15614 513  -.08955224       .          .
4 "A D C India Communications Ltd." 15645 514   .09180325       .          .
4 "A D C India Communications Ltd." 15675 515  .013513537       .          .
4 "A D C India Communications Ltd." 15706 516   -.1525926       .          .
4 "A D C India Communications Ltd." 15737 517  -.08216784       .          .
4 "A D C India Communications Ltd." 15765 518  -.08952382       .          .
4 "A D C India Communications Ltd." 15796 519   .17573225       .          .
4 "A D C India Communications Ltd." 15826 520   .18683267       .          .
4 "A D C India Communications Ltd." 15857 521   .11244378       .          .
5 "A G C Networks Ltd."             15492 509           .  233.32 .072601065
5 "A G C Networks Ltd."             15522 510   -.2367297       .          .
5 "A G C Networks Ltd."             15553 511   .11510792       .          .
5 "A G C Networks Ltd."             15584 512  -.06050181       .          .
5 "A G C Networks Ltd."             15614 513   -.1571799       .          .
5 "A G C Networks Ltd."             15645 514    .1819663       .          .
5 "A G C Networks Ltd."             15675 515   .13311891       .          .
5 "A G C Networks Ltd."             15706 516    -.153035       .          .
5 "A G C Networks Ltd."             15737 517 -.072146855       .          .
5 "A G C Networks Ltd."             15765 518  -.17684504       .          .
5 "A G C Networks Ltd."             15796 519  .065412775       .          .
5 "A G C Networks Ltd."             15826 520    .2004708       .          .
5 "A G C Networks Ltd."             15857 521 -.001634024       .          .
6 "Aarti Industries Ltd."           15492 509           .   80.09  .07460695
6 "Aarti Industries Ltd."           15522 510  -.11272725       .          .
6 "Aarti Industries Ltd."           15553 511   .06967206       .          .
6 "Aarti Industries Ltd."           15584 512  -.05747121       .          .
6 "Aarti Industries Ltd."           15614 513   .06910572       .          .
6 "Aarti Industries Ltd."           15645 514 -.034220587       .          .
6 "Aarti Industries Ltd."           15675 515  .023622025       .          .
6 "Aarti Industries Ltd."           15706 516  .023076994       .          .
6 "Aarti Industries Ltd."           15737 517  -.07518799       .          .
6 "Aarti Industries Ltd."           15765 518 -.032520294       .          .
6 "Aarti Industries Ltd."           15796 519   .06722682       .          .
6 "Aarti Industries Ltd."           15826 520   .07480317       .          .
6 "Aarti Industries Ltd."           15857 521   .27838826       .          .
7 "Aban Offshore Ltd."              15492 509           .   78.61  .16154057
7 "Aban Offshore Ltd."              15522 510 -.014547137       .          .
7 "Aban Offshore Ltd."              15553 511   .11904762       .          .
7 "Aban Offshore Ltd."              15584 512  -.07191492       .          .
7 "Aban Offshore Ltd."              15614 513  .072902344       .          .
7 "Aban Offshore Ltd."              15645 514   .14358976       .          .
7 "Aban Offshore Ltd."              15675 515     .107997       .          .
7 "Aban Offshore Ltd."              15706 516   .11298484       .          .
7 "Aban Offshore Ltd."              15737 517  -.08424245       .          .
7 "Aban Offshore Ltd."              15765 518  -.15287885       .          .
7 "Aban Offshore Ltd."              15796 519   .06093748       .          .
7 "Aban Offshore Ltd."              15826 520    .4554492       .          .
7 "Aban Offshore Ltd."              15857 521    .4950671       .          .
8 "Abbott India Ltd."               15492 509           .  483.16  .03758101
8 "Abbott India Ltd."               15522 510   -.0880134       .          .
8 "Abbott India Ltd."               15553 511  .013786765       .          .
8 "Abbott India Ltd."               15584 512 -.004533092       .          .
8 "Abbott India Ltd."               15614 513    .0564663       .          .
8 "Abbott India Ltd."               15645 514 -.010344828       .          .
8 "Abbott India Ltd."               15675 515   -.0254355       .          .
8 "Abbott India Ltd."               15706 516   .09617445       .          .
8 "Abbott India Ltd."               15737 517   .02609263       .          .
end
format %td date
format %tm mdate

[Clyde Schechter]

Sorry about that. There were several places where the code referenced the variable mcap when it should have referenced the variable june_mcap. Similarly for idiovol. The following is fixed:

clear*
input int stock_id str52 stock float(date mdate rt mcap idiovol)
1 "3M India Ltd."                   15492 509           .  321.22   .0916427
1 "3M India Ltd."                   15522 510   .05207787       .          .
1 "3M India Ltd."                   15553 511   .05333333       .          .
1 "3M India Ltd."                   15584 512 -.003164557       .          .
1 "3M India Ltd."                   15614 513  .003174603       .          .
1 "3M India Ltd."                   15645 514 -.008227867       .          .
1 "3M India Ltd."                   15675 515 -.034620315       .          .
1 "3M India Ltd."                   15706 516  -.02280613       .          .
1 "3M India Ltd."                   15737 517 -.063081324       .          .
1 "3M India Ltd."                   15765 518  -.13267148       .          .
1 "3M India Ltd."                   15796 519    .1862643       .          .
1 "3M India Ltd."                   15826 520   .10526316       .          .
1 "3M India Ltd."                   15857 521   .06095242       .          .
2 "A B B India Ltd."                15492 509           . 1139.01  .09048145
2 "A B B India Ltd."                15522 510  .031069733       .          .
2 "A B B India Ltd."                15553 511  .019307174       .          .
2 "A B B India Ltd."                15584 512 -.004956675       .          .
2 "A B B India Ltd."                15614 513   -.1465931       .          .
2 "A B B India Ltd."                15645 514 -.000208508       .          .
2 "A B B India Ltd."                15675 515  .036905766       .          .
2 "A B B India Ltd."                15706 516   .12306453       .          .
2 "A B B India Ltd."                15737 517   .14753802       .          .
2 "A B B India Ltd."                15765 518  -.10157583       .          .
2 "A B B India Ltd."                15796 519   .07033691       .          .
2 "A B B India Ltd."                15826 520   .11487909       .          .
2 "A B B India Ltd."                15857 521  .073351786       .          .
3 "A C C Ltd."                      15492 509           . 2714.39   .0494189
3 "A C C Ltd."                      15522 510  -.16299556       .          .
3 "A C C Ltd."                      15553 511   .03759398       .          .
3 "A C C Ltd."                      15584 512 -.001811594       .          .
3 "A C C Ltd."                      15614 513 -.036660638       .          .
3 "A C C Ltd."                      15645 514   .23247933       .          .
3 "A C C Ltd."                      15675 515  .009477243       .          .
3 "A C C Ltd."                      15706 516  -.13416116       .          .
3 "A C C Ltd."                      15737 517   .07764957       .          .
3 "A C C Ltd."                      15765 518  -.10094127       .          .
3 "A C C Ltd."                      15796 519  -.05487369       .          .
3 "A C C Ltd."                      15826 520   .11000772       .          .
3 "A C C Ltd."                      15857 521   .14934616       .          .
4 "A D C India Communications Ltd." 15492 509           .   38.98   .0899244
4 "A D C India Communications Ltd." 15522 510   -.1504425       .          .
4 "A D C India Communications Ltd." 15553 511   .02291669       .          .
4 "A D C India Communications Ltd." 15584 512  -.09029194       .          .
4 "A D C India Communications Ltd." 15614 513  -.08955224       .          .
4 "A D C India Communications Ltd." 15645 514   .09180325       .          .
4 "A D C India Communications Ltd." 15675 515  .013513537       .          .
4 "A D C India Communications Ltd." 15706 516   -.1525926       .          .
4 "A D C India Communications Ltd." 15737 517  -.08216784       .          .
4 "A D C India Communications Ltd." 15765 518  -.08952382       .          .
4 "A D C India Communications Ltd." 15796 519   .17573225       .          .
4 "A D C India Communications Ltd." 15826 520   .18683267       .          .
4 "A D C India Communications Ltd." 15857 521   .11244378       .          .
5 "A G C Networks Ltd."             15492 509           .  233.32 .072601065
5 "A G C Networks Ltd."             15522 510   -.2367297       .          .
5 "A G C Networks Ltd."             15553 511   .11510792       .          .
5 "A G C Networks Ltd."             15584 512  -.06050181       .          .
5 "A G C Networks Ltd."             15614 513   -.1571799       .          .
5 "A G C Networks Ltd."             15645 514    .1819663       .          .
5 "A G C Networks Ltd."             15675 515   .13311891       .          .
5 "A G C Networks Ltd."             15706 516    -.153035       .          .
5 "A G C Networks Ltd."             15737 517 -.072146855       .          .
5 "A G C Networks Ltd."             15765 518  -.17684504       .          .
5 "A G C Networks Ltd."             15796 519  .065412775       .          .
5 "A G C Networks Ltd."             15826 520    .2004708       .          .
5 "A G C Networks Ltd."             15857 521 -.001634024       .          .
6 "Aarti Industries Ltd."           15492 509           .   80.09  .07460695
6 "Aarti Industries Ltd."           15522 510  -.11272725       .          .
6 "Aarti Industries Ltd."           15553 511   .06967206       .          .
6 "Aarti Industries Ltd."           15584 512  -.05747121       .          .
6 "Aarti Industries Ltd."           15614 513   .06910572       .          .
6 "Aarti Industries Ltd."           15645 514 -.034220587       .          .
6 "Aarti Industries Ltd."           15675 515  .023622025       .          .
6 "Aarti Industries Ltd."           15706 516  .023076994       .          .
6 "Aarti Industries Ltd."           15737 517  -.07518799       .          .
6 "Aarti Industries Ltd."           15765 518 -.032520294       .          .
6 "Aarti Industries Ltd."           15796 519   .06722682       .          .
6 "Aarti Industries Ltd."           15826 520   .07480317       .          .
6 "Aarti Industries Ltd."           15857 521   .27838826       .          .
7 "Aban Offshore Ltd."              15492 509           .   78.61  .16154057
7 "Aban Offshore Ltd."              15522 510 -.014547137       .          .
7 "Aban Offshore Ltd."              15553 511   .11904762       .          .
7 "Aban Offshore Ltd."              15584 512  -.07191492       .          .
7 "Aban Offshore Ltd."              15614 513  .072902344       .          .
7 "Aban Offshore Ltd."              15645 514   .14358976       .          .
7 "Aban Offshore Ltd."              15675 515     .107997       .          .
7 "Aban Offshore Ltd."              15706 516   .11298484       .          .
7 "Aban Offshore Ltd."              15737 517  -.08424245       .          .
7 "Aban Offshore Ltd."              15765 518  -.15287885       .          .
7 "Aban Offshore Ltd."              15796 519   .06093748       .          .
7 "Aban Offshore Ltd."              15826 520    .4554492       .          .
7 "Aban Offshore Ltd."              15857 521    .4950671       .          .
8 "Abbott India Ltd."               15492 509           .  483.16  .03758101
8 "Abbott India Ltd."               15522 510   -.0880134       .          .
8 "Abbott India Ltd."               15553 511  .013786765       .          .
8 "Abbott India Ltd."               15584 512 -.004533092       .          .
8 "Abbott India Ltd."               15614 513    .0564663       .          .
8 "Abbott India Ltd."               15645 514 -.010344828       .          .
8 "Abbott India Ltd."               15675 515   -.0254355       .          .
8 "Abbott India Ltd."               15706 516   .09617445       .          .
8 "Abbott India Ltd."               15737 517   .02609263       .          .
end
format %td date
format %tm mdate

isid stock_id mdate, sort
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)

frame put stock_id fyear mcap idiovol if moy == 6, into(june_values)
frame june_values {
    replace fyear = fyear + 1 // TO SUBSEQUENT FISCAL YEAR'S VALUES
    rename (mcap idiovol) june_=
}

frlink m:1 stock_id fyear, frame(june_values)
frget june_*, from(june_values)
egen byte representative = tag(stock_id fyear)

//  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 = june_mcap, nq(2)
    exit
end
frame put stock_id fyear june_mcap if representative & !missing(june_mcap), into(median_split) // ***
frame change median_split
runby one_year_median_split, by(fyear)
frame change default
frlink m:1 stock_id fyear, frame(median_split stock_id fyear) // ***
frget june_mcap_group, from(median_split)
frame drop median_split
drop median_split

//  NOW SPLIT AT 33.3 TH AND 66.7 TH PERCENTILES OF idiovol
capture program drop one_year_three_groups
program define one_year_three_groups
    if _N > = 3 {
        _pctile june_idiovol, percentiles(33.3 66.7)
        gen cut = `r(r1)' in 1
        replace cut = `r(r2)' in 2
        xtile june_idiovol_group = june_idiovol, cutpoints(cut)
    }
    else {
        gen june_idiovol_group = .
    }
    exit
end
frame put stock_id fyear june_idiovol if representative & !missing(june_idiovol), into(three_groups) // ***
frame change three_groups
runby one_year_three_groups, by(fyear) verbose
frame change default
frlink m:1 stock_id fyear, frame(three_groups stock_id fyear) // ***
frget june_idiovol_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, june_idiovol_group) {
        egen numerator = total(june_mcap*rt) if !missing(june_mcap, rt)
        egen denominator = total(june_mcap) if !missing(june_mcap)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end
drop if missing(june_mcap_group, june_idiovol_group)
runby one_weighted_return, by(mdate june_mcap_group june_idiovol_group)

collapse (first) vw_mean_rt, by(mdate june_mcap_group june_idiovol_group)
drop if missing(vw_mean_rt)
keep mdate june_mcap_group june_idiovol_group vw_mean_rt

isid june_mcap_group june_idiovol_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 june_idiovol_group, sort: egen temp = mean(vw_mean_rt)
by mdate (june_idiovol_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 june_idiovol_group 1 "L" 2 "M" 3 "H"
label values june_mcap_group june_mcap_group
label values june_idiovol_group june_idiovol_group
decode june_mcap_group, gen (mcap_group)
decode june_idiovol_group, gen(idiovol_group)
drop june_mcap_group june_idiovol_group
egen groups = concat(mcap_group idiovol_group)
keep mdate groups SMB HML vw_mean_rt
rename vw_mean_rt =_
reshape wide vw_mean_rt_, i(mdate) j(groups) string