[Sartaj Hussain]

Dear Clyde,

Require your intervention again. Here is the code that we used to create Fama and French Monthly size(mcap) and value(bmr) factors. Now I want to run this code on daily stock return data. As is defined in this code, groups (portfolios) are to be constructed by making different mcap and bmr sorts only once using, i.e. in june month using june month mcap and march month bmr values. But, instead of monthly weighting the groups to create value-weighted portfolio returns , weighting of portfolios need to be done for each day using begin year june month mcap values. Here is the code and example data set for your perusal.

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 bmr
capture program drop one_year_three_groups
program define one_year_three_groups
    _pctile bmr, percentiles(30 70)
    gen cut = `r(r1)' in 1
    replace cut = `r(r2)' in 2
    xtile march_bmr_group = bmr, cutpoints(cut)
    exit
end
frame put stock_id year bmr 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_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

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str52 stock float(date mdate rt mcap bmr)
1 "3M India Ltd." 15430 506            .      . .1984127
1 "3M India Ltd." 15494 509  -.026353067      .        .
1 "3M India Ltd." 15495 509    .04813806      .        .
1 "3M India Ltd." 15496 509  -.020450585      .        .
1 "3M India Ltd." 15497 509   .008138669      .        .
1 "3M India Ltd." 15498 509  -.031063553      .        .
1 "3M India Ltd." 15501 509            .      .        .
1 "3M India Ltd." 15502 509            .      .        .
1 "3M India Ltd." 15503 509  -.010072921      .        .
1 "3M India Ltd." 15504 509   -.02210522      .        .
1 "3M India Ltd." 15505 509   .015428732      .        .
1 "3M India Ltd." 15508 509   .017314466      .        .
1 "3M India Ltd." 15509 509   .024487734      .        .
1 "3M India Ltd." 15510 509 -.0015257236      .        .
1 "3M India Ltd." 15511 509  -.001697793      .        .
1 "3M India Ltd." 15512 509  -.034013607      .        .
1 "3M India Ltd." 15515 509   .021302775      .        .
1 "3M India Ltd." 15516 509 -.0032752345      .        .
1 "3M India Ltd." 15517 509   -.02282949      .        .
1 "3M India Ltd." 15518 509    .02654867      .        .
1 "3M India Ltd." 15519 509  -.016724158 321.22        .
1 "3M India Ltd." 15522 510   .007189263      .        .
1 "3M India Ltd." 15523 510  -.007486157      .        .
1 "3M India Ltd." 15524 510     .0349062      .        .
1 "3M India Ltd." 15525 510           .1      .        .
1 "3M India Ltd." 15526 510  -.019106355      .        .
1 "3M India Ltd." 15529 510 -.0009424688      .        .
1 "3M India Ltd." 15530 510   .017610082      .        .
1 "3M India Ltd." 15531 510  -.031520434      .        .
1 "3M India Ltd." 15532 510   -.03637522      .        .
1 "3M India Ltd." 15533 510  -.000993337      .        .
1 "3M India Ltd." 15536 510   -.02220753      .        .
1 "3M India Ltd." 15537 510   .016949153      .        .
1 "3M India Ltd." 15538 510   -.02666667      .        .
1 "3M India Ltd." 15539 510   .013184953      .        .
1 "3M India Ltd." 15540 510    .02585768      .        .
1 "3M India Ltd." 15543 510   -.04118616      .        .
1 "3M India Ltd." 15544 510    .04037801      .        .
1 "3M India Ltd." 15545 510  -.010900042      .        .
1 "3M India Ltd." 15546 510   .005176115      .        .
1 "3M India Ltd." 15547 510  -.003322259      .        .
1 "3M India Ltd." 15550 510   .005666707      .        .
1 "3M India Ltd." 15551 510  -.005634777      .        .
1 "3M India Ltd." 15552 510            0      .        .
1 "3M India Ltd." 15553 511            0      .        .
1 "3M India Ltd." 15554 511   .009666646      .        .
1 "3M India Ltd." 15557 511   .002806227      .        .
1 "3M India Ltd." 15558 511  .0008230453      .        .
1 "3M India Ltd." 15559 511   .009210486      .        .
1 "3M India Ltd." 15560 511  -.022164237      .        .
1 "3M India Ltd." 15561 511        .0025      .        .
1 "3M India Ltd." 15564 511 -.0011637775      .        .
1 "3M India Ltd." 15565 511 -.0013315376      .        .
1 "3M India Ltd." 15566 511            0      .        .
1 "3M India Ltd." 15568 511   .013666687      .        .
1 "3M India Ltd." 15571 511  -.013482427      .        .
1 "3M India Ltd." 15572 511          .01      .        .
1 "3M India Ltd." 15573 511  -.003465306      .        .
1 "3M India Ltd." 15574 511 .00049675076      .        .
1 "3M India Ltd." 15575 511  .0043031704      .        .
1 "3M India Ltd." 15578 511            .      .        .
1 "3M India Ltd." 15579 511            .      .        .
1 "3M India Ltd." 15580 511  .0008196721      .        .
1 "3M India Ltd." 15581 511 -.0008190008      .        .
1 "3M India Ltd." 15582 511   .036065575      .        .
1 "3M India Ltd." 15585 512  -.003164557      .        .
1 "3M India Ltd." 15586 512  -.004761905      .        .
1 "3M India Ltd." 15587 512   .004784689      .        .
1 "3M India Ltd." 15588 512            0      .        .
1 "3M India Ltd." 15589 512  -.014603194      .        .
1 "3M India Ltd." 15592 512  -.012564413      .        .
1 "3M India Ltd." 15594 512    .01141925      .        .
1 "3M India Ltd." 15595 512            .      .        .
1 "3M India Ltd." 15596 512            .      .        .
1 "3M India Ltd." 15599 512            0      .        .
1 "3M India Ltd." 15600 512    .05919357      .        .
1 "3M India Ltd." 15601 512  -.010202547      .        .
1 "3M India Ltd." 15602 512  -.015230807      .        .
1 "3M India Ltd." 15603 512  -.009842194      .        .
1 "3M India Ltd." 15606 512  -.004891094      .        .
1 "3M India Ltd." 15607 512  -.008720469      .        .
1 "3M India Ltd." 15608 512   .005758118      .        .
1 "3M India Ltd." 15609 512   .007474574      .        .
1 "3M India Ltd." 15610 512  -.005209136      .        .
1 "3M India Ltd." 15613 512 -.0003173789      .        .
1 "3M India Ltd." 15614 513  -.012698413      .        .
1 "3M India Ltd." 15616 513   .012861736      .        .
1 "3M India Ltd." 15617 513   -.00952381      .        .
1 "3M India Ltd." 15620 513            .      .        .
1 "3M India Ltd." 15621 513            .      .        .
1 "3M India Ltd." 15622 513   .010583767      .        .
1 "3M India Ltd." 15623 513 -.0003173789      .        .
1 "3M India Ltd." 15624 513            0      .        .
1 "3M India Ltd." 15627 513            0      .        .
1 "3M India Ltd." 15629 513            0      .        .
1 "3M India Ltd." 15630 513            0      .        .
1 "3M India Ltd." 15631 513            0      .        .
1 "3M India Ltd." 15634 513  -.003174603      .        .
1 "3M India Ltd." 15635 513 -.0031847134      .        .
1 "3M India Ltd." 15636 513  .0006390166      .        .
end
format %td date
format %tm mdate

[Clyde Schechter]

The code you show in #76 seems to accomplish most of what you want. You have created the portfolios--though the code is not quite correct. The place it seems to fall short is in creating the weighted returns because it is weighting by the value of mcap in the current month whereas you want something else ("weighting of portfolios need to be done for each day using begin year june month mcap values," which I do not understand.) In addition, the data seem very problematic in that the portfolio-defining and weighting variables mcap and bmr are mostly missing values.

So, the first thing you need to do is populated the values of mcap and bmr to cover the entire fiscal year to which you want them to apply. Based on your example data, it seems as if you have only one non-missing value for mcap and bmr in each firm in each fiscal year, located in the final observation of June for mcap and March for bmr. If that is correct, the following code will verify my assumption and then accomplish the task:

//  SPREAD VALUES OF mcap AND bmr OVER ENTIRE FISCAL YEAR
by stock_id fyear (mcap), sort: assert !missing(mcap) == (_n == 1)
by stock_id fyear (mcap): replace mcap = mcap[1]
by stock_id fyear (bmr), sort: assert !missing(bmr) == (_n == 1)
by stock_id fyear (bmr): replace bmr = bmr[1]

Evidently, this block of code needs to appear after the command that creates the fyear variable, and it must precede the code that defines the portfolios. Note that this code will not run on your example data because the example data spans 2 fiscal years, but the values of mcap and bmr are unavailable for FY 2002. Presumably in your real data, that problem won't arise.

Concerning the creation of the portfolios, your -runby- commands specify -by(year)-, but if I understand what you want to do correctly they should specify -by(fyear)- as it is over the span of the fiscal year, not the calendar year, that mcap and bmr are defined in this data.

Concerning the weighted return, I do not understand what you want to do, so I can't advise you how to fix that part of the code.

[Sartaj Hussain]

The method is basically: Each year double (bi-variate independent: first on mcap and then followed by bmr) sorts of stocks (rt) are to be done beginning from July month of year t till June month of year t +1 using June month mcap value of year t and March month bmr value of year t. So sorts (or grouping) in that sense happen only once. Then is the issue of creating value-weighted portfolio returns for each days starting from July month of year t till June month of year t+1. Earlier this was done each month t using month t-1 mcap values. Now this process will happen daily and weights for each day remain constant as June month mcap values of year t.

This is the reason missing value are in case of mcap and bmr. The data is not relevant as per above for rest of months except june for mcap and march for bmr.

Rest there is no change. Hope it makes sense now.

[Clyde Schechter]

OK, I misunderstood the intended use of mcap and bmr, so the code in #77 is incorrect. Here is a corrected version:

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

This will save the original values of mcap and bmr in new variables orig_mcap and orig_bmr in case you need them. And it will re-populate the values of mcap and bmr with the value from the preceding June and the preceding March, respectively, in fiscal years that begin in July.

My earlier observation that the -runby- commands that create the portfolios need to be run -by(fyear)-, not -by(year)- still stands.

And with the values of mcap populated by the code above, the commands you have for calculated weighted return are mostly correct. The only change that appears necessary is, again, in the -by()- option of the -runby- command, where mdate shold be replaced by date to do the weighted return daily.

[Sartaj Hussain]

Thanks you got it finally. That is fine. However, i modified the code as per suggestions, it has still some bug. I reproduce new code here again for your examination please:

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

//  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(fyear)
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 bmr
capture program drop one_year_three_groups
program define one_year_three_groups
    _pctile bmr, percentiles(30 70)
    gen cut = `r(r1)' in 1
    replace cut = `r(r2)' in 2
    xtile march_bmr_group = bmr, cutpoints(cut)
    exit
end
frame put stock_id year bmr if moy == 3, 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 year)
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(date june_mcap_group march_bmr_group)

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

isid june_mcap_group march_bmr_group date, 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 date march_bmr_group, sort: egen temp = mean(vw_mean_rt)
by date (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(date) j(groups) string

[Sartaj Hussain]

This is the first bug related to #80. If you please run the code on appended data, it might be easy to figure out everything.

runby one_year_median_split, by(fyear)
variable fyear not found
(error in option by())
r(111);

[Clyde Schechter]

Yes, I see. So there are a few places where the code uses the variable year when it needs to use the variable fyear instead. I have made those changes below, and the affected commands are followed by // *** so you can find them easily.

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

//  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 moy == 6, 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
    _pctile bmr, percentiles(30 70)
    gen cut = `r(r1)' in 1
    replace cut = `r(r2)' in 2
    xtile march_bmr_group = bmr, cutpoints(cut)
    exit
end
frame put stock_id fyear bmr if moy == 3, 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(date june_mcap_group march_bmr_group)

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

isid june_mcap_group march_bmr_group date, 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 date march_bmr_group, sort: egen temp = mean(vw_mean_rt)
by date (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(date) j(groups) string

Basically, from the point where the variable fyear is generated on, the variable year should no longer be used in any command because the current problem requires everything to be calculated in terms of fiscal years ending in June rather than calendar years.

[Sartaj Hussain]

While running the code, the following two errors encountered in case of following lines:

1.

runby one_year_median_split, by(fyear)

--------------------------------------
Number of by-groups = 2
by-groups with errors = 1
by-groups with no data = 0
Observations processed = 24,723
Observations saved = 12,663
--------------------------------------

2.

frlink m:1 stock_id fyear, frame(median_split stock_id fyear)

Error:

invalid match variables for 1:1 or m:1 match
The variables you specified for matching do not uniquely identify the observations in frame median_split. Each
observation in the current frame default must link to one observation in median_split.
r(459);

[Clyde Schechter]

I won't be able to troubleshoot this without example data that reproduces these errors. Well, more correctly, the example data shown in #76 exhibits this problem--but it also contains no data that has the potential to produce results. The example data contains only data from year 2002, some being fyear 2001, and some fyear 2002. There are no data from fyear 2000, so no value of mcap exists for fy2001, and now values from June of fyear 2002 are available either, so frame median-split has no data on mcap at all, so the -xtile- command fails. In order to make this work, more ample data is needed, involving more years and more firms. In order to keep it small enough to post here, I suggest you pick out several firms and several years, but include only two observations for each firm-year combination--specifically those in which the value of mcap and bmr are non-missing.

[Sartaj Hussain]

Here is the data set:

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str51 stock float(date mdate rt mcap bmr)
1 "3M India Ltd." 15795 518            .      . .33557045
1 "3M India Ltd." 15858 521   .015873017      .         .
1 "3M India Ltd." 15859 521      -.03125      .         .
1 "3M India Ltd." 15860 521  -.005967762      .         .
1 "3M India Ltd." 15861 521   .005030077      .         .
1 "3M India Ltd." 15862 521  -.017113393      .         .
1 "3M India Ltd." 15865 521    .09986857      .         .
1 "3M India Ltd." 15866 521   -.02150532      .         .
1 "3M India Ltd." 15867 521    .01923073      .         .
1 "3M India Ltd." 15868 521  -.007187763      .         .
1 "3M India Ltd." 15869 521  -.014328809      .         .
1 "3M India Ltd." 15872 521  -.004131618      .         .
1 "3M India Ltd." 15873 521  -.007068187      .         .
1 "3M India Ltd." 15874 521   .032033443      .         .
1 "3M India Ltd." 15875 521  .0013494727      .         .
1 "3M India Ltd." 15876 521   .004791872      .         .
1 "3M India Ltd." 15879 521   .004321944      .         .
1 "3M India Ltd." 15880 521  -.002967799      .         .
1 "3M India Ltd." 15881 521  -.013841418      .         .
1 "3M India Ltd." 15882 521   .011168163      .         .
1 "3M India Ltd." 15883 521  -.011940299      .         .
1 "3M India Ltd." 15886 521    .00966771 376.48         .
1 "3M India Ltd." 15887 522   -.01256736      .         .
1 "3M India Ltd." 15888 522    .08484849      .         .
1 "3M India Ltd." 15889 522  -.031983275      .         .
1 "3M India Ltd." 15890 522    .01298514      .         .
1 "3M India Ltd." 15893 522   .022361506      .         .
1 "3M India Ltd." 15894 522  -.034410715      .         .
1 "3M India Ltd." 15895 522   .009811065      .         .
1 "3M India Ltd." 15896 522  -.025432274      .         .
1 "3M India Ltd." 15897 522    -.0177393      .         .
1 "3M India Ltd." 15900 522 -.0089552235      .         .
1 "3M India Ltd." 15901 522  -.003012048      .         .
1 "3M India Ltd." 15902 522    .02703931      .         .
1 "3M India Ltd." 15903 522    -.0476541      .         .
1 "3M India Ltd." 15904 522  -.004015406      .         .
1 "3M India Ltd." 15907 522   .017211933      .         .
1 "3M India Ltd." 15908 522   .005487768      .         .
1 "3M India Ltd." 15909 522  .0003032326      .         .
1 "3M India Ltd." 15910 522  -.002728081      .         .
1 "3M India Ltd." 15911 522    .02340429      .         .
1 "3M India Ltd." 15914 522 -.0020790382      .         .
1 "3M India Ltd." 15915 522  -.016815457      .         .
1 "3M India Ltd." 15916 522    .04147111      .         .
1 "3M India Ltd." 15917 522   -.02456033      .         .
1 "3M India Ltd." 15918 523  -.014600697      .         .
1 "3M India Ltd." 15921 523      .027215      .         .
1 "3M India Ltd." 15922 523   -.01957617      .         .
1 "3M India Ltd." 15923 523   -.02041732      .         .
1 "3M India Ltd." 15924 523    .02375479      .         .
1 "3M India Ltd." 15925 523    .01946108      .         .
1 "3M India Ltd." 15928 523  -.009691594      .         .
1 "3M India Ltd." 15929 523    .05397385      .         .
1 "3M India Ltd." 15930 523    .09988745      .         .
1 "3M India Ltd." 15931 523 -.0034535844      .         .
1 "3M India Ltd." 15935 523    .00628934      .         .
1 "3M India Ltd." 15936 523  -.007653061      .         .
1 "3M India Ltd." 15937 523   .001028262      .         .
1 "3M India Ltd." 15938 523   -.03685158      .         .
1 "3M India Ltd." 15939 523   .008265581      .         .
1 "3M India Ltd." 15942 523  -.016792296      .         .
1 "3M India Ltd." 15943 523    .01600326      .         .
1 "3M India Ltd." 15944 523  -.015221708      .         .
1 "3M India Ltd." 15945 523   .021505376      .         .
1 "3M India Ltd." 15946 523   -.02631579      .         .
1 "3M India Ltd." 15949 524   .010135135      .         .
1 "3M India Ltd." 15950 524  -.014448144      .         .
1 "3M India Ltd." 15951 524   .007194228      .         .
1 "3M India Ltd." 15952 524  .0036388305      .         .
1 "3M India Ltd." 15953 524   .007116943      .         .
1 "3M India Ltd." 15956 524   .018666666      .         .
1 "3M India Ltd." 15957 524      .021466      .         .
1 "3M India Ltd." 15958 524   .022680614      .         .
1 "3M India Ltd." 15959 524   -.00513717      .         .
1 "3M India Ltd." 15960 524   -.01209065      .         .
1 "3M India Ltd." 15963 524 -.0030596945      .         .
1 "3M India Ltd." 15964 524   .007928404      .         .
1 "3M India Ltd." 15965 524    .00989595      .         .
1 "3M India Ltd." 15966 524  -.005401995      .         .
1 "3M India Ltd." 15967 524   .007325992      .         .
1 "3M India Ltd." 15970 524  -.010532946      .         .
1 "3M India Ltd." 15971 524   .011278703      .         .
1 "3M India Ltd." 15972 524    .00989978      .         .
1 "3M India Ltd." 15973 524    .01501424      .         .
1 "3M India Ltd." 15974 524   .003178454      .         .
1 "3M India Ltd." 15977 524    .01072392      .         .
1 "3M India Ltd." 15978 524 -.0026525345      .         .
1 "3M India Ltd." 15979 525 -.0087040765      .         .
1 "3M India Ltd." 15981 525   .006097561      .         .
1 "3M India Ltd." 15984 525  -.006060606      .         .
1 "3M India Ltd." 15985 525            0      .         .
1 "3M India Ltd." 15986 525  .0024390244      .         .
1 "3M India Ltd." 15987 525   -.00243309      .         .
1 "3M India Ltd." 15988 525 .00048783465      .         .
1 "3M India Ltd." 15991 525   .028035104      .         .
1 "3M India Ltd." 15992 525  .0018970543      .         .
1 "3M India Ltd." 15993 525    .00591716      .         .
1 "3M India Ltd." 15994 525  -.002352941      .         .
1 "3M India Ltd." 15995 525   .008018853      .         .
1 "3M India Ltd." 15998 525  -.005849321      .         .
end
format %td date
format %tm mdate

[Clyde Schechter]

I think your response crossed with my more extensive edit. The data shown in #85 exhibits the same problem, and will not enable me to fix the code. Please read my edited request in #84 and create an example that fits that description.

[Sartaj Hussain]

I won't be able to troubleshoot this without example data that reproduces these errors. Well, more correctly, the example data shown in #76 exhibits this problem--but it also contains no data that has the potential to produce results. The example data contains only data from year 2002, some being fyear 2001, and some fyear 2002. There are no data from fyear 2000, so no value of mcap exists for fy2001, and now values from June of fyear 2002 are available either, so frame median-split has no data on mcap at all, so the -xtile- command fails. In order to make this work, more ample data is needed, involving more years and more firms. In order to keep it small enough to post here, I suggest you pick out several firms and several years, but include only two observations for each firm-year combination--specifically those in which the value of mcap and bmr are non-missing.

Reference to data example #85. Well, the process of xtile commands and value weighted returns have to work from July 2003 onwards, and for that mcap and bmr values for june 2003 and march 2003 are given for all the cases. The data for stock returns is provided for all the months from June 2002 till June 2003. This much data is sufficient to ensure process to work from July 2003 through June 2004 in the given case.

[Clyde Schechter]

That is not sufficient. Even when I combine the example data in #85 with that in #76, I can get through the point where the error messages you showed crop up, but then there is a new problem at calculating the 30th and 70th percentiles of bmr because there is only one firm represented in the data. I can patch the code to pass through that error by simply setting the bmr groups to missing value. But then it is not possible to calculate the weighted returns because dropping observations with a missing value of the march_bmr_group eliminates all of the data. (Moreover, also because there is only one firm in the data, the "median split" on mcap leads to only a single grouping there--which will cause further complications later in the code.)

So the following works as far as it goes with the combined example data, but I can't do anything with the rest of the code because at this point the data are unsuitable for the analyses you want to do and so I can neither test the code nor fix any problems that arise beyond this point.

* Example generated by -dataex-. For more info, type help dataex
clear*
input int stock_id str52 stock float(date mdate rt mcap bmr)
1 "3M India Ltd." 15430 506            .      . .1984127
1 "3M India Ltd." 15494 509  -.026353067      .        .
1 "3M India Ltd." 15495 509    .04813806      .        .
1 "3M India Ltd." 15496 509  -.020450585      .        .
1 "3M India Ltd." 15497 509   .008138669      .        .
1 "3M India Ltd." 15498 509  -.031063553      .        .
1 "3M India Ltd." 15501 509            .      .        .
1 "3M India Ltd." 15502 509            .      .        .
1 "3M India Ltd." 15503 509  -.010072921      .        .
1 "3M India Ltd." 15504 509   -.02210522      .        .
1 "3M India Ltd." 15505 509   .015428732      .        .
1 "3M India Ltd." 15508 509   .017314466      .        .
1 "3M India Ltd." 15509 509   .024487734      .        .
1 "3M India Ltd." 15510 509 -.0015257236      .        .
1 "3M India Ltd." 15511 509  -.001697793      .        .
1 "3M India Ltd." 15512 509  -.034013607      .        .
1 "3M India Ltd." 15515 509   .021302775      .        .
1 "3M India Ltd." 15516 509 -.0032752345      .        .
1 "3M India Ltd." 15517 509   -.02282949      .        .
1 "3M India Ltd." 15518 509    .02654867      .        .
1 "3M India Ltd." 15519 509  -.016724158 321.22        .
1 "3M India Ltd." 15522 510   .007189263      .        .
1 "3M India Ltd." 15523 510  -.007486157      .        .
1 "3M India Ltd." 15524 510     .0349062      .        .
1 "3M India Ltd." 15525 510           .1      .        .
1 "3M India Ltd." 15526 510  -.019106355      .        .
1 "3M India Ltd." 15529 510 -.0009424688      .        .
1 "3M India Ltd." 15530 510   .017610082      .        .
1 "3M India Ltd." 15531 510  -.031520434      .        .
1 "3M India Ltd." 15532 510   -.03637522      .        .
1 "3M India Ltd." 15533 510  -.000993337      .        .
1 "3M India Ltd." 15536 510   -.02220753      .        .
1 "3M India Ltd." 15537 510   .016949153      .        .
1 "3M India Ltd." 15538 510   -.02666667      .        .
1 "3M India Ltd." 15539 510   .013184953      .        .
1 "3M India Ltd." 15540 510    .02585768      .        .
1 "3M India Ltd." 15543 510   -.04118616      .        .
1 "3M India Ltd." 15544 510    .04037801      .        .
1 "3M India Ltd." 15545 510  -.010900042      .        .
1 "3M India Ltd." 15546 510   .005176115      .        .
1 "3M India Ltd." 15547 510  -.003322259      .        .
1 "3M India Ltd." 15550 510   .005666707      .        .
1 "3M India Ltd." 15551 510  -.005634777      .        .
1 "3M India Ltd." 15552 510            0      .        .
1 "3M India Ltd." 15553 511            0      .        .
1 "3M India Ltd." 15554 511   .009666646      .        .
1 "3M India Ltd." 15557 511   .002806227      .        .
1 "3M India Ltd." 15558 511  .0008230453      .        .
1 "3M India Ltd." 15559 511   .009210486      .        .
1 "3M India Ltd." 15560 511  -.022164237      .        .
1 "3M India Ltd." 15561 511        .0025      .        .
1 "3M India Ltd." 15564 511 -.0011637775      .        .
1 "3M India Ltd." 15565 511 -.0013315376      .        .
1 "3M India Ltd." 15566 511            0      .        .
1 "3M India Ltd." 15568 511   .013666687      .        .
1 "3M India Ltd." 15571 511  -.013482427      .        .
1 "3M India Ltd." 15572 511          .01      .        .
1 "3M India Ltd." 15573 511  -.003465306      .        .
1 "3M India Ltd." 15574 511 .00049675076      .        .
1 "3M India Ltd." 15575 511  .0043031704      .        .
1 "3M India Ltd." 15578 511            .      .        .
1 "3M India Ltd." 15579 511            .      .        .
1 "3M India Ltd." 15580 511  .0008196721      .        .
1 "3M India Ltd." 15581 511 -.0008190008      .        .
1 "3M India Ltd." 15582 511   .036065575      .        .
1 "3M India Ltd." 15585 512  -.003164557      .        .
1 "3M India Ltd." 15586 512  -.004761905      .        .
1 "3M India Ltd." 15587 512   .004784689      .        .
1 "3M India Ltd." 15588 512            0      .        .
1 "3M India Ltd." 15589 512  -.014603194      .        .
1 "3M India Ltd." 15592 512  -.012564413      .        .
1 "3M India Ltd." 15594 512    .01141925      .        .
1 "3M India Ltd." 15595 512            .      .        .
1 "3M India Ltd." 15596 512            .      .        .
1 "3M India Ltd." 15599 512            0      .        .
1 "3M India Ltd." 15600 512    .05919357      .        .
1 "3M India Ltd." 15601 512  -.010202547      .        .
1 "3M India Ltd." 15602 512  -.015230807      .        .
1 "3M India Ltd." 15603 512  -.009842194      .        .
1 "3M India Ltd." 15606 512  -.004891094      .        .
1 "3M India Ltd." 15607 512  -.008720469      .        .
1 "3M India Ltd." 15608 512   .005758118      .        .
1 "3M India Ltd." 15609 512   .007474574      .        .
1 "3M India Ltd." 15610 512  -.005209136      .        .
1 "3M India Ltd." 15613 512 -.0003173789      .        .
1 "3M India Ltd." 15614 513  -.012698413      .        .
1 "3M India Ltd." 15616 513   .012861736      .        .
1 "3M India Ltd." 15617 513   -.00952381      .        .
1 "3M India Ltd." 15620 513            .      .        .
1 "3M India Ltd." 15621 513            .      .        .
1 "3M India Ltd." 15622 513   .010583767      .        .
1 "3M India Ltd." 15623 513 -.0003173789      .        .
1 "3M India Ltd." 15624 513            0      .        .
1 "3M India Ltd." 15627 513            0      .        .
1 "3M India Ltd." 15629 513            0      .        .
1 "3M India Ltd." 15630 513            0      .        .
1 "3M India Ltd." 15631 513            0      .        .
1 "3M India Ltd." 15634 513  -.003174603      .        .
1 "3M India Ltd." 15635 513 -.0031847134      .        .
1 "3M India Ltd." 15636 513  .0006390166      .        .
1 "3M India Ltd." 15795 518            .      . .33557045
1 "3M India Ltd." 15858 521   .015873017      .         .
1 "3M India Ltd." 15859 521      -.03125      .         .
1 "3M India Ltd." 15860 521  -.005967762      .         .
1 "3M India Ltd." 15861 521   .005030077      .         .
1 "3M India Ltd." 15862 521  -.017113393      .         .
1 "3M India Ltd." 15865 521    .09986857      .         .
1 "3M India Ltd." 15866 521   -.02150532      .         .
1 "3M India Ltd." 15867 521    .01923073      .         .
1 "3M India Ltd." 15868 521  -.007187763      .         .
1 "3M India Ltd." 15869 521  -.014328809      .         .
1 "3M India Ltd." 15872 521  -.004131618      .         .
1 "3M India Ltd." 15873 521  -.007068187      .         .
1 "3M India Ltd." 15874 521   .032033443      .         .
1 "3M India Ltd." 15875 521  .0013494727      .         .
1 "3M India Ltd." 15876 521   .004791872      .         .
1 "3M India Ltd." 15879 521   .004321944      .         .
1 "3M India Ltd." 15880 521  -.002967799      .         .
1 "3M India Ltd." 15881 521  -.013841418      .         .
1 "3M India Ltd." 15882 521   .011168163      .         .
1 "3M India Ltd." 15883 521  -.011940299      .         .
1 "3M India Ltd." 15886 521    .00966771 376.48         .
1 "3M India Ltd." 15887 522   -.01256736      .         .
1 "3M India Ltd." 15888 522    .08484849      .         .
1 "3M India Ltd." 15889 522  -.031983275      .         .
1 "3M India Ltd." 15890 522    .01298514      .         .
1 "3M India Ltd." 15893 522   .022361506      .         .
1 "3M India Ltd." 15894 522  -.034410715      .         .
1 "3M India Ltd." 15895 522   .009811065      .         .
1 "3M India Ltd." 15896 522  -.025432274      .         .
1 "3M India Ltd." 15897 522    -.0177393      .         .
1 "3M India Ltd." 15900 522 -.0089552235      .         .
1 "3M India Ltd." 15901 522  -.003012048      .         .
1 "3M India Ltd." 15902 522    .02703931      .         .
1 "3M India Ltd." 15903 522    -.0476541      .         .
1 "3M India Ltd." 15904 522  -.004015406      .         .
1 "3M India Ltd." 15907 522   .017211933      .         .
1 "3M India Ltd." 15908 522   .005487768      .         .
1 "3M India Ltd." 15909 522  .0003032326      .         .
1 "3M India Ltd." 15910 522  -.002728081      .         .
1 "3M India Ltd." 15911 522    .02340429      .         .
1 "3M India Ltd." 15914 522 -.0020790382      .         .
1 "3M India Ltd." 15915 522  -.016815457      .         .
1 "3M India Ltd." 15916 522    .04147111      .         .
1 "3M India Ltd." 15917 522   -.02456033      .         .
1 "3M India Ltd." 15918 523  -.014600697      .         .
1 "3M India Ltd." 15921 523      .027215      .         .
1 "3M India Ltd." 15922 523   -.01957617      .         .
1 "3M India Ltd." 15923 523   -.02041732      .         .
1 "3M India Ltd." 15924 523    .02375479      .         .
1 "3M India Ltd." 15925 523    .01946108      .         .
1 "3M India Ltd." 15928 523  -.009691594      .         .
1 "3M India Ltd." 15929 523    .05397385      .         .
1 "3M India Ltd." 15930 523    .09988745      .         .
1 "3M India Ltd." 15931 523 -.0034535844      .         .
1 "3M India Ltd." 15935 523    .00628934      .         .
1 "3M India Ltd." 15936 523  -.007653061      .         .
1 "3M India Ltd." 15937 523   .001028262      .         .
1 "3M India Ltd." 15938 523   -.03685158      .         .
1 "3M India Ltd." 15939 523   .008265581      .         .
1 "3M India Ltd." 15942 523  -.016792296      .         .
1 "3M India Ltd." 15943 523    .01600326      .         .
1 "3M India Ltd." 15944 523  -.015221708      .         .
1 "3M India Ltd." 15945 523   .021505376      .         .
1 "3M India Ltd." 15946 523   -.02631579      .         .
1 "3M India Ltd." 15949 524   .010135135      .         .
1 "3M India Ltd." 15950 524  -.014448144      .         .
1 "3M India Ltd." 15951 524   .007194228      .         .
1 "3M India Ltd." 15952 524  .0036388305      .         .
1 "3M India Ltd." 15953 524   .007116943      .         .
1 "3M India Ltd." 15956 524   .018666666      .         .
1 "3M India Ltd." 15957 524      .021466      .         .
1 "3M India Ltd." 15958 524   .022680614      .         .
1 "3M India Ltd." 15959 524   -.00513717      .         .
1 "3M India Ltd." 15960 524   -.01209065      .         .
1 "3M India Ltd." 15963 524 -.0030596945      .         .
1 "3M India Ltd." 15964 524   .007928404      .         .
1 "3M India Ltd." 15965 524    .00989595      .         .
1 "3M India Ltd." 15966 524  -.005401995      .         .
1 "3M India Ltd." 15967 524   .007325992      .         .
1 "3M India Ltd." 15970 524  -.010532946      .         .
1 "3M India Ltd." 15971 524   .011278703      .         .
1 "3M India Ltd." 15972 524    .00989978      .         .
1 "3M India Ltd." 15973 524    .01501424      .         .
1 "3M India Ltd." 15974 524   .003178454      .         .
1 "3M India Ltd." 15977 524    .01072392      .         .
1 "3M India Ltd." 15978 524 -.0026525345      .         .
1 "3M India Ltd." 15979 525 -.0087040765      .         .
1 "3M India Ltd." 15981 525   .006097561      .         .
1 "3M India Ltd." 15984 525  -.006060606      .         .
1 "3M India Ltd." 15985 525            0      .         .
1 "3M India Ltd." 15986 525  .0024390244      .         .
1 "3M India Ltd." 15987 525   -.00243309      .         .
1 "3M India Ltd." 15988 525 .00048783465      .         .
1 "3M India Ltd." 15991 525   .028035104      .         .
1 "3M India Ltd." 15992 525  .0018970543      .         .
1 "3M India Ltd." 15993 525    .00591716      .         .
1 "3M India Ltd." 15994 525  -.002352941      .         .
1 "3M India Ltd." 15995 525   .008018853      .         .
1 "3M India Ltd." 15998 525  -.005849321      .         .
end
format %td date
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)
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)
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

Note: Changes/Additions in italics.

[Sartaj Hussain]

Meanwhile i ran code #88 along with parts from #82. It ran without errors on a large file. But i need confirmation from your side. So i am appending a new example data file below for your testing the code. It has more firms but fewer observations:

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str52 stock float(date mdate rt mcap bmr)
 1 "3M India Ltd."                                  15430 506            .       .  .1984127
 1 "3M India Ltd."                                  15519 509  -.016724158  321.22         .
 1 "3M India Ltd."                                  15522 510   .007189263       .         .
 1 "3M India Ltd."                                  15523 510  -.007486157       .         .
 1 "3M India Ltd."                                  15524 510     .0349062       .         .
 1 "3M India Ltd."                                  15525 510           .1       .         .
 1 "3M India Ltd."                                  15526 510  -.019106355       .         .
 2 "A B B India Ltd."                               15430 506            .       . .37593985
 2 "A B B India Ltd."                               15519 509    .06520018 1139.01         .
 2 "A B B India Ltd."                               15522 510   .011906966       .         .
 2 "A B B India Ltd."                               15523 510  -.007905871       .         .
 2 "A B B India Ltd."                               15524 510    .02891033       .         .
 2 "A B B India Ltd."                               15525 510   .006123922       .         .
 2 "A B B India Ltd."                               15526 510    .00680274       .         .
 3 "A C C Ltd."                                     15430 506            .       .  .4672897
 3 "A C C Ltd."                                     15519 509    .02914508 2714.39         .
 3 "A C C Ltd."                                     15522 510    .01825053       .         .
 3 "A C C Ltd."                                     15523 510  -.008343671       .         .
 3 "A C C Ltd."                                     15524 510  -.005297543       .         .
 3 "A C C Ltd."                                     15525 510    -.0100251       .         .
 3 "A C C Ltd."                                     15526 510  -.002848082       .         .
 4 "A D C India Communications Ltd."                15430 506            .       .   .877193
 4 "A D C India Communications Ltd."                15519 509   -.00877193   38.98         .
 4 "A D C India Communications Ltd."                15522 510   .029498525       .         .
 4 "A D C India Communications Ltd."                15523 510 -.0017192152       .         .
 4 "A D C India Communications Ltd."                15524 510   .006888616       .         .
 4 "A D C India Communications Ltd."                15525 510   .021664785       .         .
 4 "A D C India Communications Ltd."                15526 510    .02957591       .         .
 5 "A G C Networks Ltd."                            15430 506            .       . .26595744
 5 "A G C Networks Ltd."                            15519 509    .02118376  233.32         .
 5 "A G C Networks Ltd."                            15522 510    -.0234289       .         .
 5 "A G C Networks Ltd."                            15523 510   .013744826       .         .
 5 "A G C Networks Ltd."                            15524 510 -.0003697619       .         .
 5 "A G C Networks Ltd."                            15525 510 -.0027127154       .         .
 5 "A G C Networks Ltd."                            15526 510   .031404562       .         .
 6 "Aarti Industries Ltd."                          15430 506            .       . 2.2727273
 6 "Aarti Industries Ltd."                          15519 509  .0036496315   80.09         .
 6 "Aarti Industries Ltd."                          15522 510    .03272724       .         .
 6 "Aarti Industries Ltd."                          15523 510   .007042247       .         .
 6 "Aarti Industries Ltd."                          15524 510            0       .         .
 6 "Aarti Industries Ltd."                          15525 510     -.013986       .         .
 6 "Aarti Industries Ltd."                          15526 510   .014184384       .         .
 7 "Aban Offshore Ltd."                             15430 506            .       .  2.564103
 7 "Aban Offshore Ltd."                             15519 509    .07301102   78.61         .
 7 "Aban Offshore Ltd."                             15522 510    .09995313       .         .
 7 "Aban Offshore Ltd."                             15523 510   -.04095567       .         .
 7 "Aban Offshore Ltd."                             15524 510    .05026695       .         .
 7 "Aban Offshore Ltd."                             15525 510    .04786104       .         .
 7 "Aban Offshore Ltd."                             15526 510     .0998383       .         .
 8 "Abbott India Ltd."                              15430 506            .       . .28818443
 8 "Abbott India Ltd."                              15519 509    .01531915  483.16         .
 8 "Abbott India Ltd."                              15522 510   -.01894382       .         .
 8 "Abbott India Ltd."                              15523 510  -.002563226       .         .
 8 "Abbott India Ltd."                              15524 510   .007366777       .         .
 8 "Abbott India Ltd."                              15525 510  -.003061204       .         .
 8 "Abbott India Ltd."                              15526 510  .0030706034       .         .
 9 "Accelya Solutions India Ltd."                   15430 506            .       .  .6578947
 9 "Accelya Solutions India Ltd."                   15519 509    .01359519   77.16         .
 9 "Accelya Solutions India Ltd."                   15522 510  -.012667637       .         .
 9 "Accelya Solutions India Ltd."                   15523 510  -.016603751       .         .
 9 "Accelya Solutions India Ltd."                   15524 510    .03376818       .         .
 9 "Accelya Solutions India Ltd."                   15525 510   .012620616       .         .
 9 "Accelya Solutions India Ltd."                   15526 510  -.006598196       .         .
10 "Adani Enterprises Ltd."                         15430 506            .       . 1.5151515
10 "Adani Enterprises Ltd."                         15519 509   -.01300107  401.15         .
10 "Adani Enterprises Ltd."                         15522 510  -.021953877       .         .
10 "Adani Enterprises Ltd."                         15523 510   .004489334       .         .
10 "Adani Enterprises Ltd."                         15524 510   .001117344       .         .
10 "Adani Enterprises Ltd."                         15525 510  .0044642813       .         .
10 "Adani Enterprises Ltd."                         15526 510 -.0033333036       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15430 506            .       .  .2777778
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15519 509 -.0011016813  102.68         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15522 510  .0022059048       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15523 510  -.013206185       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15524 510   .035315987       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15525 510  -.005385996       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15526 510   -.00252712       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15430 506            .       .  2.702703
12 "Aditya Birla Nuvo Ltd. [Merged]"                15519 509   .023121985  594.88         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15522 510  .0035409704       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15523 510  -.024595296       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15524 510   .009256333       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15525 510   -.01265019       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15526 510    .07997008       .         .
13 "Ador Welding Ltd."                              15430 506            .       .  2.564103
13 "Ador Welding Ltd."                              15519 509    .04238915   42.82         .
13 "Ador Welding Ltd."                              15522 510   .007393744       .         .
13 "Ador Welding Ltd."                              15523 510     .0568807       .         .
13 "Ador Welding Ltd."                              15524 510    .14062497       .         .
13 "Ador Welding Ltd."                              15525 510   -.05175035       .         .
13 "Ador Welding Ltd."                              15526 510    .16372393       .         .
14 "Aftek Ltd."                                     15430 506            .       .  .4132231
14 "Aftek Ltd."                                     15519 509    .03029734  247.92         .
14 "Aftek Ltd."                                     15522 510  -.007623924       .         .
14 "Aftek Ltd."                                     15523 510  -.015913645       .         .
14 "Aftek Ltd."                                     15524 510     .0401487       .         .
14 "Aftek Ltd."                                     15525 510  -.025196567       .         .
14 "Aftek Ltd."                                     15526 510   .016681941       .         .
15 "Agro Dutch Inds. Ltd."                          15430 506            .       . 2.4390244
15 "Agro Dutch Inds. Ltd."                          15519 509  -.004261864   54.54         .
end
format %td date
format %tm mdate

[Clyde Schechter]

Thank you. This is very helpful.

The following code seems to work sensibly. It contains the code from #88, with no further modifications, and extends it through calculation of daily weighted mean returns within each portfolio (defined by combination of mcap and bmr, and weighted by mcap), and then calculating various group differences. Assuming that's where you're going with this, I think it's good now.

* Example generated by -dataex-. For more info, type help dataex
clear*
input int stock_id str52 stock float(date mdate rt mcap bmr)
 1 "3M India Ltd."                                  15430 506            .       .  .1984127
 1 "3M India Ltd."                                  15519 509  -.016724158  321.22         .
 1 "3M India Ltd."                                  15522 510   .007189263       .         .
 1 "3M India Ltd."                                  15523 510  -.007486157       .         .
 1 "3M India Ltd."                                  15524 510     .0349062       .         .
 1 "3M India Ltd."                                  15525 510           .1       .         .
 1 "3M India Ltd."                                  15526 510  -.019106355       .         .
 2 "A B B India Ltd."                               15430 506            .       . .37593985
 2 "A B B India Ltd."                               15519 509    .06520018 1139.01         .
 2 "A B B India Ltd."                               15522 510   .011906966       .         .
 2 "A B B India Ltd."                               15523 510  -.007905871       .         .
 2 "A B B India Ltd."                               15524 510    .02891033       .         .
 2 "A B B India Ltd."                               15525 510   .006123922       .         .
 2 "A B B India Ltd."                               15526 510    .00680274       .         .
 3 "A C C Ltd."                                     15430 506            .       .  .4672897
 3 "A C C Ltd."                                     15519 509    .02914508 2714.39         .
 3 "A C C Ltd."                                     15522 510    .01825053       .         .
 3 "A C C Ltd."                                     15523 510  -.008343671       .         .
 3 "A C C Ltd."                                     15524 510  -.005297543       .         .
 3 "A C C Ltd."                                     15525 510    -.0100251       .         .
 3 "A C C Ltd."                                     15526 510  -.002848082       .         .
 4 "A D C India Communications Ltd."                15430 506            .       .   .877193
 4 "A D C India Communications Ltd."                15519 509   -.00877193   38.98         .
 4 "A D C India Communications Ltd."                15522 510   .029498525       .         .
 4 "A D C India Communications Ltd."                15523 510 -.0017192152       .         .
 4 "A D C India Communications Ltd."                15524 510   .006888616       .         .
 4 "A D C India Communications Ltd."                15525 510   .021664785       .         .
 4 "A D C India Communications Ltd."                15526 510    .02957591       .         .
 5 "A G C Networks Ltd."                            15430 506            .       . .26595744
 5 "A G C Networks Ltd."                            15519 509    .02118376  233.32         .
 5 "A G C Networks Ltd."                            15522 510    -.0234289       .         .
 5 "A G C Networks Ltd."                            15523 510   .013744826       .         .
 5 "A G C Networks Ltd."                            15524 510 -.0003697619       .         .
 5 "A G C Networks Ltd."                            15525 510 -.0027127154       .         .
 5 "A G C Networks Ltd."                            15526 510   .031404562       .         .
 6 "Aarti Industries Ltd."                          15430 506            .       . 2.2727273
 6 "Aarti Industries Ltd."                          15519 509  .0036496315   80.09         .
 6 "Aarti Industries Ltd."                          15522 510    .03272724       .         .
 6 "Aarti Industries Ltd."                          15523 510   .007042247       .         .
 6 "Aarti Industries Ltd."                          15524 510            0       .         .
 6 "Aarti Industries Ltd."                          15525 510     -.013986       .         .
 6 "Aarti Industries Ltd."                          15526 510   .014184384       .         .
 7 "Aban Offshore Ltd."                             15430 506            .       .  2.564103
 7 "Aban Offshore Ltd."                             15519 509    .07301102   78.61         .
 7 "Aban Offshore Ltd."                             15522 510    .09995313       .         .
 7 "Aban Offshore Ltd."                             15523 510   -.04095567       .         .
 7 "Aban Offshore Ltd."                             15524 510    .05026695       .         .
 7 "Aban Offshore Ltd."                             15525 510    .04786104       .         .
 7 "Aban Offshore Ltd."                             15526 510     .0998383       .         .
 8 "Abbott India Ltd."                              15430 506            .       . .28818443
 8 "Abbott India Ltd."                              15519 509    .01531915  483.16         .
 8 "Abbott India Ltd."                              15522 510   -.01894382       .         .
 8 "Abbott India Ltd."                              15523 510  -.002563226       .         .
 8 "Abbott India Ltd."                              15524 510   .007366777       .         .
 8 "Abbott India Ltd."                              15525 510  -.003061204       .         .
 8 "Abbott India Ltd."                              15526 510  .0030706034       .         .
 9 "Accelya Solutions India Ltd."                   15430 506            .       .  .6578947
 9 "Accelya Solutions India Ltd."                   15519 509    .01359519   77.16         .
 9 "Accelya Solutions India Ltd."                   15522 510  -.012667637       .         .
 9 "Accelya Solutions India Ltd."                   15523 510  -.016603751       .         .
 9 "Accelya Solutions India Ltd."                   15524 510    .03376818       .         .
 9 "Accelya Solutions India Ltd."                   15525 510   .012620616       .         .
 9 "Accelya Solutions India Ltd."                   15526 510  -.006598196       .         .
10 "Adani Enterprises Ltd."                         15430 506            .       . 1.5151515
10 "Adani Enterprises Ltd."                         15519 509   -.01300107  401.15         .
10 "Adani Enterprises Ltd."                         15522 510  -.021953877       .         .
10 "Adani Enterprises Ltd."                         15523 510   .004489334       .         .
10 "Adani Enterprises Ltd."                         15524 510   .001117344       .         .
10 "Adani Enterprises Ltd."                         15525 510  .0044642813       .         .
10 "Adani Enterprises Ltd."                         15526 510 -.0033333036       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15430 506            .       .  .2777778
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15519 509 -.0011016813  102.68         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15522 510  .0022059048       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15523 510  -.013206185       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15524 510   .035315987       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15525 510  -.005385996       .         .
11 "Aditya Birla Minacs I T Services Ltd. [Merged]" 15526 510   -.00252712       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15430 506            .       .  2.702703
12 "Aditya Birla Nuvo Ltd. [Merged]"                15519 509   .023121985  594.88         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15522 510  .0035409704       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15523 510  -.024595296       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15524 510   .009256333       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15525 510   -.01265019       .         .
12 "Aditya Birla Nuvo Ltd. [Merged]"                15526 510    .07997008       .         .
13 "Ador Welding Ltd."                              15430 506            .       .  2.564103
13 "Ador Welding Ltd."                              15519 509    .04238915   42.82         .
13 "Ador Welding Ltd."                              15522 510   .007393744       .         .
13 "Ador Welding Ltd."                              15523 510     .0568807       .         .
13 "Ador Welding Ltd."                              15524 510    .14062497       .         .
13 "Ador Welding Ltd."                              15525 510   -.05175035       .         .
13 "Ador Welding Ltd."                              15526 510    .16372393       .         .
14 "Aftek Ltd."                                     15430 506            .       .  .4132231
14 "Aftek Ltd."                                     15519 509    .03029734  247.92         .
14 "Aftek Ltd."                                     15522 510  -.007623924       .         .
14 "Aftek Ltd."                                     15523 510  -.015913645       .         .
14 "Aftek Ltd."                                     15524 510     .0401487       .         .
14 "Aftek Ltd."                                     15525 510  -.025196567       .         .
14 "Aftek Ltd."                                     15526 510   .016681941       .         .
15 "Agro Dutch Inds. Ltd."                          15430 506            .       . 2.4390244
15 "Agro Dutch Inds. Ltd."                          15519 509  -.004261864   54.54         .
end
format %td date
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)
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(date june_mcap_group march_bmr_group)

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

isid june_mcap_group march_bmr_group date, sort
by date june_mcap_group, sort: egen temp = mean(vw_mean_rt)
by date (june_mcap_group), sort: gen SMB = temp[1] - temp[_N]
drop temp

by date march_bmr_group, sort: egen temp = mean(vw_mean_rt)
by date (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 date groups SMB HML vw_mean_rt
rename vw_mean_rt =_
reshape wide vw_mean_rt_, i(date) j(groups) string