Monthly Quintiles

[Sartaj Hussain]

Lastly we used this code to generates 5x5 = 25 size-idiovol independent portfolios using june values of mcap-idiovol for sorts and lagged june mcap value for weighting of portfolio.

Just require a slight change in it. That is now portfolios (groups) have to made using dependent sorts by mdate. From June month onwards, first to be cut into 5 groups based on previous month mcap and then within each mcap group again to be cut into 5 groups (quintiles) based on previous month idiovol. The value-weighted returns are to be updated each month with previous month mcap. The code with data example is appended.

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(mcap_idiovol_work)
frame change mcap_idiovol_work
collapse (count) n_mcap = mcap n_idiovol = idiovol (firstnm) mcap idiovol, by(stock_id fyear)
assert n_mcap <= 1 & n_idiovol <= 1 // VERIFY UNIQUE VALUE OF MCAP AND idiovol
replace fyear = fyear + 1 // CHANGE THE FYEAR TO WHICH THEY WILL APPLY
rename (mcap idiovol) prior_june_=
frame change default

frlink m:1 stock_id fyear, frame(mcap_idiovol_work)
frget prior_june_*, from(mcap_idiovol_work)
frame drop mcap_idiovol_work
drop mcap_idiovol_work
egen byte representative = tag(stock_id fyear)
tab representative

//  QUINTILES BASED ON PREVIOUS FY JUNE VALUE OF mcap AND idiovol
capture program drop double_quintiles
program define double_quintiles
    capture assert missing(prior_june_mcap)
    if `c(rc)' {
        xtile mcap_group = prior_june_mcap, nq(5)
    }
    capture assert missing(prior_june_idiovol)
    if `c(rc)' {
        xtile idiovol_group = prior_june_idiovol, nq(5)
    }
    exit
end

frame put stock_id fyear prior_june_mcap prior_june_idiovol if representative, into(representatives)
frame change representatives
runby double_quintiles, by(fyear) verbose
frame change default
frlink m:1 stock_id fyear, frame(representatives stock_id fyear) // ***
frget mcap_group idiovol_group, from(representatives)
frame drop representatives
drop representatives representative

capture program drop one_weighted_return
program define one_weighted_return
        egen numerator = total(lagged_mcap*rt)
        egen denominator = total(lagged_mcap)
        gen vw_mean_rt = numerator/denominator
        egen unwtd_mean_rt = mean(rt)
    exit
end

xtset stock_id mdate
gen lagged_mcap = L1.mcap
drop if missing(mcap_group, idiovol_group)
runby one_weighted_return, by(mdate mcap_group idiovol_group)

collapse (first) vw_mean_rt unwtd_mean_rt, by(mdate mcap_group idiovol_group)
drop if missing(vw_mean_rt)
keep mdate mcap_group idiovol_group *_mean_rt

gen str32 group = "q" + string(mcap_group) +"_idiovol_q" +string(idiovol_group) + "_"
drop *_group

reshape wide vw_mean_@rt unwtd_mean_@rt, i(mdate) j(group) string

* Example generated by -dataex-. To install: ssc install dataex
clear
input int stock_id str52 stock float(date mdate rt mcap idiovol)
1 "3M India Ltd."                             22067 725           . 21170.67  .09459936
1 "3M India Ltd."                             22097 726   .12113695 23735.22  .05236398
1 "3M India Ltd."                             22128 727 -.037226338 22851.65   .0842787
1 "3M India Ltd."                             22159 728   -.0958004 20662.45  .04649255
1 "3M India Ltd."                             22189 729    .0222412 21122.01  .05208552
1 "3M India Ltd."                             22220 730   .16466135 24599.98  .07155111
1 "3M India Ltd."                             22250 731  -.04579532 23473.42 .037118677
1 "3M India Ltd."                             22281 732  -.08161782 21557.57  .05653918
1 "3M India Ltd."                             22312 733   .20678906 26015.44   .1024171
1 "3M India Ltd."                             22340 734    .3093739 34063.94   .0761481
1 "3M India Ltd."                             22371 735  -.15384005 28823.54  .11266434
1 "3M India Ltd."                             22401 736 -.004385145 28697.15   .0739418
1 "3M India Ltd."                             22432 737  -.04418543 27429.15  .03292042
2 "A B B India Ltd."                          22067 725           . 19995.67  .10871843
2 "A B B India Ltd."                          22097 726  -.05150485  18965.8 .067627124
2 "A B B India Ltd."                          22128 727   .06201117 20141.89  .06322751
2 "A B B India Ltd."                          22159 728  -.09579168 18212.47  .06247413
2 "A B B India Ltd."                          22189 729   .04403976 19014.54   .0660737
2 "A B B India Ltd."                          22220 730   .19658974  22752.6  .14531854
2 "A B B India Ltd."                          22250 731    .1289467 25686.47  .09560207
2 "A B B India Ltd."                          22281 732   .14499031 29410.76  .08255054
2 "A B B India Ltd."                          22312 733     .103934 32467.54  .11415926
2 "A B B India Ltd."                          22340 734  -.07489476 30035.89  .06083263
2 "A B B India Ltd."                          22371 735 -.024446186 29301.63  .05801463
2 "A B B India Ltd."                          22401 736    .2062195  35344.2  .07539472
2 "A B B India Ltd."                          22432 737   .08282872 38271.71  .06517829
3 "A B B Power Products & Systems India Ltd." 22067 725           .  3566.42 .016075514
3 "A B B Power Products & Systems India Ltd." 22097 726   .06625074   3802.7  .03139144
3 "A B B Power Products & Systems India Ltd." 22128 727  .022067415  3886.61    .124328
3 "A B B Power Products & Systems India Ltd." 22159 728  .008996238  3921.58  .05144717
3 "A B B Power Products & Systems India Ltd." 22189 729   .04031124  4079.66  .09119125
3 "A B B Power Products & Systems India Ltd." 22220 730   .19031797  4856.09  .11065537
3 "A B B Power Products & Systems India Ltd." 22250 731   .12842546  5479.74  .14846125
3 "A B B Power Products & Systems India Ltd." 22281 732  -.06635982   5116.1  .05424834
3 "A B B Power Products & Systems India Ltd." 22312 733   .19243667  6100.63   .1228909
3 "A B B Power Products & Systems India Ltd." 22340 734  -.05012324  5794.85  .08472132
3 "A B B Power Products & Systems India Ltd." 22371 735    .3193519  7645.44    .096315
3 "A B B Power Products & Systems India Ltd." 22401 736   .07594446  8226.07   .0819109
3 "A B B Power Products & Systems India Ltd." 22432 737   -.0265334  8007.81  .05989602
4 "A C C Ltd."                                22067 725           . 25013.26  .05451617
4 "A C C Ltd."                                22097 726  .069632135 26754.99  .06367326
4 "A C C Ltd."                                22128 727 -.072398685 24817.96  .04167877
4 "A C C Ltd."                                22159 728   .05466858 26174.73   .0552238
4 "A C C Ltd."                                22189 729    .1826237 30954.85  .06496176
4 "A C C Ltd."                                22220 730  .033274677 31984.87  .05231039
4 "A C C Ltd."                                22250 731  -.04981651 30391.49  .06558764
4 "A C C Ltd."                                22281 732 -.008403346  30136.1  .05752963
4 "A C C Ltd."                                22312 733   .07985412 32542.59  .05743942
4 "A C C Ltd."                                22340 734   .09786785 35727.47  .07591277
4 "A C C Ltd."                                22371 735  -.01064361  35347.2  .05981381
4 "A C C Ltd."                                22401 736   .05506556 37293.61  .03130351
4 "A C C Ltd."                                22432 737  .015156523 37858.85 .035984043
5 "A I A Engineering Ltd."                    22067 725           . 15157.76  .11163152
5 "A I A Engineering Ltd."                    22097 726  .027659345 15577.01  .07996228
5 "A I A Engineering Ltd."                    22128 727   .09109897 16996.06  .08275808
5 "A I A Engineering Ltd."                    22159 728   .02483421 17418.14  .11070403
5 "A I A Engineering Ltd."                    22189 729  -.07970973 16029.75  .08087932
5 "A I A Engineering Ltd."                    22220 730   .09799944 17600.65  .07386222
5 "A I A Engineering Ltd."                    22250 731   .05433933 18557.06  .13231656
5 "A I A Engineering Ltd."                    22281 732 -.006378778 18438.69   .0978856
5 "A I A Engineering Ltd."                    22312 733 -.069492035 17157.35  .06436446
5 "A I A Engineering Ltd."                    22340 734   .12583485 19316.34  .06380035
5 "A I A Engineering Ltd."                    22371 735  -.09013891 17575.19  .04568206
5 "A I A Engineering Ltd."                    22401 736  .036788605 18221.75 .071264036
5 "A I A Engineering Ltd."                    22432 737   .10758837  20182.2   .0423352
6 "A P L Apollo Tubes Ltd."                   22067 725           .  3877.83  .06370289
6 "A P L Apollo Tubes Ltd."                   22097 726   .17386007  4552.02   .1343903
6 "A P L Apollo Tubes Ltd."                   22128 727    .3133195  5991.94  .09383714
6 "A P L Apollo Tubes Ltd."                   22159 728   .21373597  7272.35  .08646914
6 "A P L Apollo Tubes Ltd."                   22189 729   .07821232  7844.95   .0876136
6 "A P L Apollo Tubes Ltd."                   22220 730   .06770723  8380.35 .066069156
6 "A P L Apollo Tubes Ltd."                   22250 731    .3168597 11039.03   .1009317
6 "A P L Apollo Tubes Ltd."                   22281 732  .024552327 11309.81  .08637045
6 "A P L Apollo Tubes Ltd."                   22312 733   .18087743 13355.64   .0683895
6 "A P L Apollo Tubes Ltd."                   22340 734     .308549 17492.31  .17121236
6 "A P L Apollo Tubes Ltd."                   22371 735  -.07057284 16257.71  .11828837
6 "A P L Apollo Tubes Ltd."                   22401 736  .000199746 16260.83   .1114527
6 "A P L Apollo Tubes Ltd."                   22432 737   .25072974 20338.06  .09736723
7 "A U Small Finance Bank Ltd."               22067 725           . 16631.81  .14167368
7 "A U Small Finance Bank Ltd."               22097 726    .3679993 22756.09  .14123882
7 "A U Small Finance Bank Ltd."               22128 727  -.10127266 20454.26  .09675895
7 "A U Small Finance Bank Ltd."               22159 728 -.019779654 20052.01  .08047066
7 "A U Small Finance Bank Ltd."               22189 729   .18596646 23781.35  .09965545
7 "A U Small Finance Bank Ltd."               22220 730   .07920858 25679.42  .09570134
7 "A U Small Finance Bank Ltd."               22250 731  .017856091 26141.96  .07963484
7 "A U Small Finance Bank Ltd."               22281 732   .02446606 26783.51  .05495229
7 "A U Small Finance Bank Ltd."               22312 733   .29087684 34609.04  .09901675
7 "A U Small Finance Bank Ltd."               22340 734   .08806566 38285.17  .13246192
7 "A U Small Finance Bank Ltd."               22371 735  -.18169214  31333.9   .1121216
7 "A U Small Finance Bank Ltd."               22401 736  -.01619413 30881.87  .10038955
7 "A U Small Finance Bank Ltd."               22432 737   .05019245 32443.73  .08761825
8 "Aarti Drugs Ltd."                          22067 725           .  2961.31  .13313535
8 "Aarti Drugs Ltd."                          22097 726    .3679424  4050.94  .14255284
8 "Aarti Drugs Ltd."                          22128 727    .5628667  6331.08    .335134
8 "Aarti Drugs Ltd."                          22159 728   .13580157  7190.85   .0926184
8 "Aarti Drugs Ltd."                          22189 729  -.11878681  6336.67  .20832872
8 "Aarti Drugs Ltd."                          22220 730   .05213995  6667.06  .10740176
8 "Aarti Drugs Ltd."                          22250 731  -.01383933  6574.79  .06569554
8 "Aarti Drugs Ltd."                          22281 732  -.04465235  6281.21  .08209758
8 "Aarti Drugs Ltd."                          22312 733  -.06402556  5879.06  .05586975
end
format %td date
format %tm mdate

.

[Clyde Schechter]

The only part of the code that needs changing is program double_quintiles:

//  DEPENDENT QUINTILES BASED ON PREVIOUS FY JUNE VALUE OF mcap AND idiovol
capture program drop double_quintiles
program define double_quintiles
    capture assert missing(prior_june_mcap)
    if `c(rc)' {
        xtile mcap_group = prior_june_mcap, nq(5)
    }
    capture assert missing(prior_june_idiovol)
    if `c(rc)' {
        levelsof mcap_group, local(groups)
        foreach g of local groups {
            xtile idiovol_group = prior_june_idiovol if mcap_group == `g', nq(5)
        }
    }
    exit
end

Now, this will not run with the example data because there simply isn't enough data to calculate the idiovol_groups. In particular, when we come to trying to create the idiovol quintiles within the first mcap quintile, there are only 2 different values of prior_june_idiovol, so -xtile- cannot create 5 groups. Presumably your real data set is large enough to overcome this problem.

[Sartaj Hussain]

What does prior stand for. I want sorts and groupings every month on lagged mcap and idiovol. Does the amendment in #2 take care of that.

[Clyde Schechter]

Prior refers to the June of the preceding fiscal year. So, for October 2002, prior_june means June 2001. If you meant you wanted an actual one-month lag, that is September 2002 for October 2002, well, that is different. Let me know.

[Sartaj Hussain]

Yes exactly. I want an actual one-month lag. i.e., June 2002 for July 2002, July 2002 for August 2002, August 2002 for September 2002 and so on till last month in data.

[Clyde Schechter]

OK. That's more of a change, and, actually, a big simplification of the code:

//  QUINTILES BASED ON PREVIOUS FY JUNE VALUE OF mcap AND idiovol
capture program drop double_quintiles
program define double_quintiles
    capture assert missing(lagged_mcap)
    if `c(rc)' {
        xtile mcap_group = lagged_mcap, nq(5)
    }
    capture assert missing(lagged_idiovol)
    if `c(rc)' {
        gen idiovol_group = .
        levelsof mcap_group, local(groups)
        foreach g of local groups {
            xtile temp = lagged_idiovol if mcap_group == `g', nq(5)
            replace idiovol_group = temp if mcap_group == `g'
            drop temp
        }
    }
    exit
end

xtset stock_id mdate
foreach v of varlist mcap idiovol {
    gen lagged_`v' = L1.`v'
}
runby double_quintiles, by(mdate) verbose

capture program drop one_weighted_return
program define one_weighted_return
        egen numerator = total(lagged_mcap*rt)
        egen denominator = total(lagged_mcap)
        gen vw_mean_rt = numerator/denominator
        egen unwtd_mean_rt = mean(rt)
    exit
end

drop if missing(mcap_group, idiovol_group)
runby one_weighted_return, by(mdate mcap_group idiovol_group)

collapse (first) vw_mean_rt unwtd_mean_rt, by(mdate mcap_group idiovol_group)
drop if missing(vw_mean_rt)
keep mdate mcap_group idiovol_group *_mean_rt

gen str32 group = "q" + string(mcap_group) +"_idiovol_q" +string(idiovol_group) + "_"
drop *_group

reshape wide vw_mean_@rt unwtd_mean_@rt, i(mdate) j(group) string

Again, this won't run with the example data because of insufficient data. But I tested it substituting median splits, and then it runs fine. So I think it should be good for quintiles in the full data set.

[Sartaj Hussain]

This code works very well. But i try to run the following subsequent code and there is following error.

frame create newey_results int (idiovol_quintile mcap_quintile) float(intercept tstat)
forvalues iv = 1/5 {
    forvalues mc = 1/5 {
        local v
        newey unwtd_mean_q`mc'_idiovol_q`iv'_rt, lag(5)
        matrix M = r(table)
        frame post newey_results (`iv') (`mc') (M["b", "_cons"]) (M["t", "_cons"])
    }
}
frame change newey_results
rename (intercept tstat) =_mcap_
reshape wide *_mcap_, i(idiovol_quintile) j(mcap_quintile)

ERROR:

frame create newey_results int (idiovol_quintile mcap_quintile) float(intercept tstat)

. forvalues iv = 1/5 {
2. forvalues mc = 1/5 {
3. local v
4. newey unwtd_mean_q`mc'_idiovol_q`iv'_rt, lag(5)
5. matrix M = r(table)
6. frame post newey_results (`iv') (`mc') (M["b", "_cons"]) (M["t", "_cons"])
7. }
8. }
variable stock_id not found
r(111);

end of do-file

r(111);

[Clyde Schechter]

Well, I suspect you are running this code in the wrong place. To do this analysis, you have to do it before the -collapse- command in #6. Once the -collapse- command runs, there is no longer any individual stock_id data, nor a stock_id variable: everything has been aggregated to months and quintiles. So I think if you run this before the -collapse- command and after -runby one_weighted_return...-, you will get the results you want.