[Clyde Schechter]

I need some clarification on the months here.

First, am I correct in understanding that the fiscal year t runs from July t-1 to June t? Above, you say June to June, but that would be a 13 month period.

Next, when you say

the mcap values for each stock will continue to be for June of year t, but bmr values will for December month of year t-1. What is the change to be introduced for so that sorts for mcap take latest june mcap values and sorts for bmr use preceding bmr values of December.

does this mean that bmr for calculations in fiscal year t will be taken from December of calendar year t, or from December of fiscal year t (which belongs to calendar year t-1)? It seems that no comparable question arises for mcap, because June of fiscal year t is the same ass June of calendar year t. Yet I would still like clarification because it seems that you will be analyzing data conditioning on the values of mcap that occur at the end of the fiscal year, which would stand any attempt at causal reasoning on its head.

[Sartaj Hussain]

1. Yes, you are correct. It should be from July t to June t+1. (i.e. in our case fiscal year runs from July t-1 to June t)

2. bmr for calculations in our fiscal year t should befrom December of fiscal year t (which belongs to calendar year t-1)

The point No: 2 here should be understood like this. If the fiscal year is from say July 2021 to June 2022, then bmr values are from December 2020 and so on for each fiscal year. The mcap will be of June 2021.

Hope this is amply clear.

[Clyde Schechter]

I think the following will do it.

bysort stock_id (date):gen rt =((pr[_n]-pr[_n-1])/pr[_n-1])
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 moy year mcap bmr if !missing(mcap) | !missing(bmr), into(mcap_bmr_work)
frame change mcap_bmr_work
assert (moy == 12) == !missing(bmr)
assert (moy == 6) == !missing(mcap)
replace fyear = year + 1 if !missing(mcap)
replace fyear = year + 2 if !missing(bmr)
collapse (firstnm) mcap bmr, by(stock_id fyear)

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 dec_bmr_group = bmr, cutpoints(cut)
    }
    else {
        gen dec_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 dec_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, dec_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, dec_bmr_group)
runby one_weighted_return, by(date june_mcap_group dec_bmr_group)

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

isid june_mcap_group dec_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 dec_bmr_group, sort: egen temp = mean(vw_mean_rt)
by date (dec_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 dec_bmr_group 1 "L" 2 "M" 3 "H"
label values june_mcap_group june_mcap_group
label values dec_bmr_group dec_bmr_group
decode june_mcap_group, gen (mcap_group)
decode dec_bmr_group, gen(bmr_group)
drop june_mcap_group dec_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

I have shown in bold face the changes to the previous code, down through the end of program one_three_year_groups. Thereafter, I have not shown the changes made: from that point on the change consists entirely of replacing every occurrence of march_bmr with dec_bmr, nothing else.

Note: As the available data example does not have the non-missing values of bmr in december, and it only covers data for less than one calendar year, it was not possible to test this code. Given the relative simplicity of the changes, I have moderate confidence in their correctness. However, should you find it does not work correctly, please include new example data that spans at least a period of two full calendar years (although one stock_id is sufficient) when posting back.

[Sartaj Hussain]

I tried running code #18 on daily data. But below error is encountered. The data example follows the error report.

gen fyear = cond(moy > 6, year, year-1)
. frame put stock_id fyear moy year mcap bmr if !missing(mcap) | !missing(bmr), into(mcap_bmr_work)
. frame change mcap_bmr_work
. assert (moy == 12) == !missing(bmr)
. assert (moy == 6) == !missing(mcap)
. replace fyear = year + 1 if !missing(mcap)
(1,498 real changes made)
. replace fyear = year + 2 if !missing(bmr)
(1,246 real changes made)
. collapse (firstnm) mcap bmr, by(stock_id fyear)
. frame change default
. rename (mcap bmr) orig=
. frlink m:1 stock_id fyear, frame(mcap_bmr_work)
(no observations in default matched!)
. frget mcap bmr, from(mcap_bmr_work)
(528,627 missing values generated)
(528,627 missing values generated)
(2 variables copied from linked frame)
. 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
1. xtile june_mcap_group = mcap, nq(2)
2. exit
3. 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)
no observations
r(2000);

end of do-file

r(2000);

.

* Example generated by -dataex-. For more info, type help dataex
clear
input float stock_id str52 stock int date float(mdate rt) double mcap float(bmr moy year)
1 "20 Microns Ltd." 20789 683            .      . .8064516 12 2016
1 "20 Microns Ltd." 20968 688            .      .        .  5 2017
1 "20 Microns Ltd." 20969 688  -.022788204      .        .  5 2017
1 "20 Microns Ltd." 20970 688   .021947874      .        .  5 2017
1 "20 Microns Ltd." 20971 689   .024161074 133.56        .  6 2017
1 "20 Microns Ltd." 20972 689    .00655308      .        .  6 2017
1 "20 Microns Ltd." 20975 689  -.005208333      .        .  6 2017
1 "20 Microns Ltd." 20976 689   .005235602      .        .  6 2017
1 "20 Microns Ltd." 20977 689 -.0013020834      .        .  6 2017
1 "20 Microns Ltd." 20978 689   .010430248      .        .  6 2017
1 "20 Microns Ltd." 20979 689   .003870968      .        .  6 2017
1 "20 Microns Ltd." 20982 689    .06041131      .        .  6 2017
1 "20 Microns Ltd." 20983 689  -.024242423      .        .  6 2017
1 "20 Microns Ltd." 20984 689   -.01242236      .        .  6 2017
1 "20 Microns Ltd." 20985 689   .006289308      .        .  6 2017
1 "20 Microns Ltd." 20986 689      -.02125      .        .  6 2017
1 "20 Microns Ltd." 20989 689 -.0012771392      .        .  6 2017
1 "20 Microns Ltd." 20990 689  -.003836317      .        .  6 2017
1 "20 Microns Ltd." 20991 689   .007702183      .        .  6 2017
1 "20 Microns Ltd." 20992 689  -.006369427      .        .  6 2017
1 "20 Microns Ltd." 20993 689  -.016666668      .        .  6 2017
1 "20 Microns Ltd." 20997 689  -.033898305      .        .  6 2017
1 "20 Microns Ltd." 20998 689   -.00539811      .        .  6 2017
1 "20 Microns Ltd." 20999 689    .03256445      .        .  6 2017
1 "20 Microns Ltd." 21000 689  -.005256242      .        .  6 2017
1 "20 Microns Ltd." 21003 690   .003963012      .        .  7 2017
1 "20 Microns Ltd." 21004 690   .013157895      .        .  7 2017
1 "20 Microns Ltd." 21005 690   .012987013      .        .  7 2017
1 "20 Microns Ltd." 21006 690            0      .        .  7 2017
1 "20 Microns Ltd." 21007 690  .0012820513      .        .  7 2017
1 "20 Microns Ltd." 21010 690    .04353393      .        .  7 2017
1 "20 Microns Ltd." 21011 690     .0392638      .        .  7 2017
1 "20 Microns Ltd." 21012 690  -.012987013      .        .  7 2017
1 "20 Microns Ltd." 21013 690  -.016746411      .        .  7 2017
1 "20 Microns Ltd." 21014 690  -.032846715      .        .  7 2017
1 "20 Microns Ltd." 21017 690 -.0012578616      .        .  7 2017
1 "20 Microns Ltd." 21018 690  .0037783375      .        .  7 2017
1 "20 Microns Ltd." 21019 690  .0037641155      .        .  7 2017
1 "20 Microns Ltd." 21020 690        -.005      .        .  7 2017
1 "20 Microns Ltd." 21021 690  -.002512563      .        .  7 2017
1 "20 Microns Ltd." 21024 690   -.01511335      .        .  7 2017
1 "20 Microns Ltd." 21025 690  -.002557545      .        .  7 2017
1 "20 Microns Ltd." 21026 690    .02179487      .        .  7 2017
1 "20 Microns Ltd." 21027 690  -.030112924      .        .  7 2017
1 "20 Microns Ltd." 21028 690   .006468305      .        .  7 2017
1 "20 Microns Ltd." 21031 690  -.008997429      .        .  7 2017
1 "20 Microns Ltd." 21032 691  -.001297017      .        .  8 2017
1 "20 Microns Ltd." 21033 691   -.02207792      .        .  8 2017
1 "20 Microns Ltd." 21034 691  -.014608234      .        .  8 2017
1 "20 Microns Ltd." 21035 691   -.01078167      .        .  8 2017
1 "20 Microns Ltd." 21038 691  .0013623978      .        .  8 2017
1 "20 Microns Ltd." 21039 691   -.02585034      .        .  8 2017
1 "20 Microns Ltd." 21040 691   .005586592      .        .  8 2017
1 "20 Microns Ltd." 21041 691  -.029166667      .        .  8 2017
1 "20 Microns Ltd." 21042 691  -.031473532      .        .  8 2017
1 "20 Microns Ltd." 21045 691   .022156574      .        .  8 2017
1 "20 Microns Ltd." 21047 691  -.002890173      .        .  8 2017
1 "20 Microns Ltd." 21048 691  .0014492754      .        .  8 2017
1 "20 Microns Ltd." 21049 691   -.00723589      .        .  8 2017
1 "20 Microns Ltd." 21052 691  -.011661808      .        .  8 2017
1 "20 Microns Ltd." 21053 691 -.0044247787      .        .  8 2017
1 "20 Microns Ltd." 21054 691 -.0014814815      .        .  8 2017
1 "20 Microns Ltd." 21055 691   .008902078      .        .  8 2017
1 "20 Microns Ltd." 21059 691   .007352941      .        .  8 2017
1 "20 Microns Ltd." 21060 691    .04087591      .        .  8 2017
1 "20 Microns Ltd." 21061 691   .032258064      .        .  8 2017
1 "20 Microns Ltd." 21062 691   .017663043      .        .  8 2017
1 "20 Microns Ltd." 21063 692    .04539386      .        .  9 2017
1 "20 Microns Ltd." 21066 692    .01532567      .        .  9 2017
1 "20 Microns Ltd." 21067 692   -.02515723      .        .  9 2017
1 "20 Microns Ltd." 21068 692  -.006451613      .        .  9 2017
1 "20 Microns Ltd." 21069 692  -.006493506      .        .  9 2017
1 "20 Microns Ltd." 21070 692  -.013071896      .        .  9 2017
1 "20 Microns Ltd." 21073 692    .00794702      .        .  9 2017
1 "20 Microns Ltd." 21074 692   .011826544      .        .  9 2017
1 "20 Microns Ltd." 21075 692  -.024675325      .        .  9 2017
1 "20 Microns Ltd." 21076 692   -.00665779      .        .  9 2017
1 "20 Microns Ltd." 21077 692   .036193028      .        .  9 2017
1 "20 Microns Ltd." 21080 692   .032341525      .        .  9 2017
1 "20 Microns Ltd." 21081 692    .04010025      .        .  9 2017
1 "20 Microns Ltd." 21082 692   .007228916      .        .  9 2017
1 "20 Microns Ltd." 21083 692   .023923445      .        .  9 2017
1 "20 Microns Ltd." 21084 692   -.06308411      .        .  9 2017
1 "20 Microns Ltd." 21087 692  -.033665836      .        .  9 2017
1 "20 Microns Ltd." 21088 692            0      .        .  9 2017
1 "20 Microns Ltd." 21089 692   -.03870968      .        .  9 2017
1 "20 Microns Ltd." 21090 692   .005369128      .        .  9 2017
1 "20 Microns Ltd." 21091 692    .11348464      .        .  9 2017
1 "20 Microns Ltd." 21095 693    .05515588      .        . 10 2017
1 "20 Microns Ltd." 21096 693   -.04204546      .        . 10 2017
1 "20 Microns Ltd." 21097 693    .02728351      .        . 10 2017
1 "20 Microns Ltd." 21098 693   .011547344      .        . 10 2017
1 "20 Microns Ltd." 21101 693   -.02625571      .        . 10 2017
1 "20 Microns Ltd." 21102 693   .007033998      .        . 10 2017
1 "20 Microns Ltd." 21103 693   -.04889406      .        . 10 2017
1 "20 Microns Ltd." 21104 693   .009791922      .        . 10 2017
1 "20 Microns Ltd." 21105 693   -.01090909      .        . 10 2017
1 "20 Microns Ltd." 21108 693  -.007352941      .        . 10 2017
1 "20 Microns Ltd." 21109 693   -.01728395      .        . 10 2017
1 "20 Microns Ltd." 21110 693   .030150754      .        . 10 2017
end
format %td date
format %tm mdate

.

[Clyde Schechter]

Well, with the example data given, the code is not runnable. The problem is the absence of lagged mcap values. In the example data, you need an mcap from May 2016 to work with the 2017 data. But there is none: the only observation from 2016 is in December, and even if it were May, the value of mcap is missing. So when frame median split is created, there are no observations that meet the -if- condition to go into it. So frame median split is empty, and when -runby- attempts to use the data, it finds nothing there, complains, and quits.

[Sartaj Hussain]

It needs some explanation. Well, for each stock id, there is only one mcap and bmr data of previous year Dec (2016) and current year March (2017), hence, it remains same for all the dates. So, lagged value here for each day means the value of mcap in Dec and value of bmr in march. So, we will have repeated values. This is the reason i think it cannot generate lagged values in the first case. I think the code needs some change to accommodate that.

Pertinent to mention, the earlier code #5 did work fine, the only change that was to be done to it was that instead of March end bmr values we now wanted to use previous year Dec end bmr values. Just that.

[Clyde Schechter]

the only change that was to be done to it was that instead of March end bmr values we now wanted to use previous year Dec end bmr values.

Well, the code was not changed in a way that would support that. The code you show in #19 still tries to find the lagged mcap in May of the preceding fiscal year. But even if it did try to find it in December, in the data you show there is no December mcap value either.

This is the reason i think it cannot generate lagged values in the first case. I think the code needs some change to accommodate that.

The error message you got suggests that in the entire data set you attempted to run the code with there were no fiscal years with lagged mcaps available. I don't know what kind of accommodation can be made to this situation. The entire computation at that point is to split the daily data into halves based on the lagged mcap. There is no way to do this if there is no mcap. When only some years are missing the mcap, well, the computation proceeds for those years that have it and skips over those that don't. But when there are no mcaps at all, what would an accommodation look like? There is simply nothing that can be done with the data in that condition.

[Sartaj Hussain]

Well, the code was not changed in a way that would support that. The code you show in #19 still tries to find the lagged mcap in May of the preceding fiscal year. But even if it did try to find it in December, in the data you show there is no December mcap value either.

I want make it clear. The mcap value is of current calendar year (in this case June 2017) and the bmr values is of immediate preceding calendar year (i.e., Dec 2016). So, the first split (into two) is to be done on the basis of current calendar year June month mcap and the second split (into three) on previous calendar year December end bmr. I hope the data supports that and I hope this makes it clear to introduce change in the code.

[Sartaj Hussain]

P.S.
Earlier we defined fiscal year as: July of year t till June of year t+1, In that case, the value of mcap is of June fiscal year t and value of bmr is of December fiscal year t-1 to make groups from July fiscal year t till June year t+1. Just thought of adding this, @Clyde

[Clyde Schechter]

The mcap value is of current calendar year (in this case June 2017) and the bmr values is of immediate preceding calendar year (i.e., Dec 2016).

Ah, that is the key. Yes, with that, the code can be modified to work with this data:

* Example generated by -dataex-. For more info, type help dataex
clear*
input float stock_id str52 stock int date float(mdate rt) double mcap float(bmr moy year)
1 "20 Microns Ltd." 20789 683            .      . .8064516 12 2016
1 "20 Microns Ltd." 20968 688            .      .        .  5 2017
1 "20 Microns Ltd." 20969 688  -.022788204      .        .  5 2017
1 "20 Microns Ltd." 20970 688   .021947874      .        .  5 2017
1 "20 Microns Ltd." 20971 689   .024161074 133.56        .  6 2017
1 "20 Microns Ltd." 20972 689    .00655308      .        .  6 2017
1 "20 Microns Ltd." 20975 689  -.005208333      .        .  6 2017
1 "20 Microns Ltd." 20976 689   .005235602      .        .  6 2017
1 "20 Microns Ltd." 20977 689 -.0013020834      .        .  6 2017
1 "20 Microns Ltd." 20978 689   .010430248      .        .  6 2017
1 "20 Microns Ltd." 20979 689   .003870968      .        .  6 2017
1 "20 Microns Ltd." 20982 689    .06041131      .        .  6 2017
1 "20 Microns Ltd." 20983 689  -.024242423      .        .  6 2017
1 "20 Microns Ltd." 20984 689   -.01242236      .        .  6 2017
1 "20 Microns Ltd." 20985 689   .006289308      .        .  6 2017
1 "20 Microns Ltd." 20986 689      -.02125      .        .  6 2017
1 "20 Microns Ltd." 20989 689 -.0012771392      .        .  6 2017
1 "20 Microns Ltd." 20990 689  -.003836317      .        .  6 2017
1 "20 Microns Ltd." 20991 689   .007702183      .        .  6 2017
1 "20 Microns Ltd." 20992 689  -.006369427      .        .  6 2017
1 "20 Microns Ltd." 20993 689  -.016666668      .        .  6 2017
1 "20 Microns Ltd." 20997 689  -.033898305      .        .  6 2017
1 "20 Microns Ltd." 20998 689   -.00539811      .        .  6 2017
1 "20 Microns Ltd." 20999 689    .03256445      .        .  6 2017
1 "20 Microns Ltd." 21000 689  -.005256242      .        .  6 2017
1 "20 Microns Ltd." 21003 690   .003963012      .        .  7 2017
1 "20 Microns Ltd." 21004 690   .013157895      .        .  7 2017
1 "20 Microns Ltd." 21005 690   .012987013      .        .  7 2017
1 "20 Microns Ltd." 21006 690            0      .        .  7 2017
1 "20 Microns Ltd." 21007 690  .0012820513      .        .  7 2017
1 "20 Microns Ltd." 21010 690    .04353393      .        .  7 2017
1 "20 Microns Ltd." 21011 690     .0392638      .        .  7 2017
1 "20 Microns Ltd." 21012 690  -.012987013      .        .  7 2017
1 "20 Microns Ltd." 21013 690  -.016746411      .        .  7 2017
1 "20 Microns Ltd." 21014 690  -.032846715      .        .  7 2017
1 "20 Microns Ltd." 21017 690 -.0012578616      .        .  7 2017
1 "20 Microns Ltd." 21018 690  .0037783375      .        .  7 2017
1 "20 Microns Ltd." 21019 690  .0037641155      .        .  7 2017
1 "20 Microns Ltd." 21020 690        -.005      .        .  7 2017
1 "20 Microns Ltd." 21021 690  -.002512563      .        .  7 2017
1 "20 Microns Ltd." 21024 690   -.01511335      .        .  7 2017
1 "20 Microns Ltd." 21025 690  -.002557545      .        .  7 2017
1 "20 Microns Ltd." 21026 690    .02179487      .        .  7 2017
1 "20 Microns Ltd." 21027 690  -.030112924      .        .  7 2017
1 "20 Microns Ltd." 21028 690   .006468305      .        .  7 2017
1 "20 Microns Ltd." 21031 690  -.008997429      .        .  7 2017
1 "20 Microns Ltd." 21032 691  -.001297017      .        .  8 2017
1 "20 Microns Ltd." 21033 691   -.02207792      .        .  8 2017
1 "20 Microns Ltd." 21034 691  -.014608234      .        .  8 2017
1 "20 Microns Ltd." 21035 691   -.01078167      .        .  8 2017
1 "20 Microns Ltd." 21038 691  .0013623978      .        .  8 2017
1 "20 Microns Ltd." 21039 691   -.02585034      .        .  8 2017
1 "20 Microns Ltd." 21040 691   .005586592      .        .  8 2017
1 "20 Microns Ltd." 21041 691  -.029166667      .        .  8 2017
1 "20 Microns Ltd." 21042 691  -.031473532      .        .  8 2017
1 "20 Microns Ltd." 21045 691   .022156574      .        .  8 2017
1 "20 Microns Ltd." 21047 691  -.002890173      .        .  8 2017
1 "20 Microns Ltd." 21048 691  .0014492754      .        .  8 2017
1 "20 Microns Ltd." 21049 691   -.00723589      .        .  8 2017
1 "20 Microns Ltd." 21052 691  -.011661808      .        .  8 2017
1 "20 Microns Ltd." 21053 691 -.0044247787      .        .  8 2017
1 "20 Microns Ltd." 21054 691 -.0014814815      .        .  8 2017
1 "20 Microns Ltd." 21055 691   .008902078      .        .  8 2017
1 "20 Microns Ltd." 21059 691   .007352941      .        .  8 2017
1 "20 Microns Ltd." 21060 691    .04087591      .        .  8 2017
1 "20 Microns Ltd." 21061 691   .032258064      .        .  8 2017
1 "20 Microns Ltd." 21062 691   .017663043      .        .  8 2017
1 "20 Microns Ltd." 21063 692    .04539386      .        .  9 2017
1 "20 Microns Ltd." 21066 692    .01532567      .        .  9 2017
1 "20 Microns Ltd." 21067 692   -.02515723      .        .  9 2017
1 "20 Microns Ltd." 21068 692  -.006451613      .        .  9 2017
1 "20 Microns Ltd." 21069 692  -.006493506      .        .  9 2017
1 "20 Microns Ltd." 21070 692  -.013071896      .        .  9 2017
1 "20 Microns Ltd." 21073 692    .00794702      .        .  9 2017
1 "20 Microns Ltd." 21074 692   .011826544      .        .  9 2017
1 "20 Microns Ltd." 21075 692  -.024675325      .        .  9 2017
1 "20 Microns Ltd." 21076 692   -.00665779      .        .  9 2017
1 "20 Microns Ltd." 21077 692   .036193028      .        .  9 2017
1 "20 Microns Ltd." 21080 692   .032341525      .        .  9 2017
1 "20 Microns Ltd." 21081 692    .04010025      .        .  9 2017
1 "20 Microns Ltd." 21082 692   .007228916      .        .  9 2017
1 "20 Microns Ltd." 21083 692   .023923445      .        .  9 2017
1 "20 Microns Ltd." 21084 692   -.06308411      .        .  9 2017
1 "20 Microns Ltd." 21087 692  -.033665836      .        .  9 2017
1 "20 Microns Ltd." 21088 692            0      .        .  9 2017
1 "20 Microns Ltd." 21089 692   -.03870968      .        .  9 2017
1 "20 Microns Ltd." 21090 692   .005369128      .        .  9 2017
1 "20 Microns Ltd." 21091 692    .11348464      .        .  9 2017
1 "20 Microns Ltd." 21095 693    .05515588      .        . 10 2017
1 "20 Microns Ltd." 21096 693   -.04204546      .        . 10 2017
1 "20 Microns Ltd." 21097 693    .02728351      .        . 10 2017
1 "20 Microns Ltd." 21098 693   .011547344      .        . 10 2017
1 "20 Microns Ltd." 21101 693   -.02625571      .        . 10 2017
1 "20 Microns Ltd." 21102 693   .007033998      .        . 10 2017
1 "20 Microns Ltd." 21103 693   -.04889406      .        . 10 2017
1 "20 Microns Ltd." 21104 693   .009791922      .        . 10 2017
1 "20 Microns Ltd." 21105 693   -.01090909      .        . 10 2017
1 "20 Microns Ltd." 21108 693  -.007352941      .        . 10 2017
1 "20 Microns Ltd." 21109 693   -.01728395      .        . 10 2017
1 "20 Microns Ltd." 21110 693   .030150754      .        . 10 2017
end
format %td date
format %tm mdate

//  CREATE A "FISCAL YEAR" RUNNING FROM JULY THROUGH SUBSEQUENT JUNE
gen fyear = cond(moy > 6, year, year-1)
frame put stock_id fyear moy year mcap bmr if !missing(mcap) | !missing(bmr), into(mcap_bmr_work)
frame change mcap_bmr_work
assert (moy == 12) == !missing(bmr)
assert (moy == 6) == !missing(mcap)
replace fyear = year  if !missing(mcap)
replace fyear = year + 1 if !missing(bmr)
collapse (firstnm) mcap bmr, by(stock_id fyear)

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 dec_bmr_group = bmr, cutpoints(cut)
    }
    else {
        gen dec_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 dec_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, dec_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, dec_bmr_group)
runby one_weighted_return, by(date june_mcap_group dec_bmr_group)

The above is just the code in #19 with the removal of the first three lines (because the variables year, moy, and rt already exist in this data example) and the changes shown in the bold faced lines.

Now, remember that with the example data, you may encounter additional difficulties further along because, for example, you only have one stock_id, so division into three groups on bmr is not possible. But with an ample data set, there should be no problems.

[Sartaj Hussain]

Clyde Schechter, Thanks indeed. I ran this code on actual data and it worked fine. I must say that it is being brilliant and the code is awesome.

[Sartaj Hussain]

Clyde Schechter I just want a little change to the code #25. If for the second split, we use March end bmr values of fiscal year t instead of bmr values of December end of fiscal year t-1 as used earlier, what changes are to be incorporated in the code #25. Please read #23 and #24 while you suggest that.

[Clyde Schechter]

Change the paragraph that begins -// CREATE A "FISCAL YEAR" RUNNING FROM JULY THROUGH SUBSEQUENT JUNE- to read

//  CREATE A "FISCAL YEAR" RUNNING FROM JULY THROUGH SUBSEQUENT JUNE
gen fyear = cond(moy > 6, year, year-1)
frame put stock_id fyear moy year mcap bmr if !missing(mcap) | !missing(bmr), into(mcap_bmr_work)
frame change mcap_bmr_work
assert (moy == 3) == !missing(bmr)
assert (moy == 6) == !missing(mcap)
replace fyear = year  if !missing(mcap)
replace fyear = year if !missing(bmr)
collapse (firstnm) mcap bmr, by(stock_id fyear)

[Sartaj Hussain]

This is fine. Kindly check calculation of HML in #18. HML is basically simple average of two high bmr minus the simple average of low bmr groups given by: HML = ( vw_mean_rt_BH+ vw_mean_rt_SH)/2-( vw_mean_rt_BL+ vw_mean_rt_SL)/2. I code is not doing it correctly.

 
 by date dec_bmr_group, sort: egen temp = mean(vw_mean_rt) by date (dec_bmr_group): gen HML = temp[1] - temp[_N] drop temp

[Clyde Schechter]

Kindly check calculation of HML in #18. HML is basically simple average of two high bmr minus the simple average of low bmr groups given by: HML = ( vw_mean_rt_BH+ vw_mean_rt_SH)/2-( vw_mean_rt_BL+ vw_mean_rt_SL)/2. I code is not doing it correctly.

That's because that isn't what was originally asked. The original request was for the highest one minus lowest one, and that is what it does. Before I change the code to reflect the changed purpose, let me ask if you also want to change the definition of SMB to the simple average of the highest two minus the simple average of the lowest two, or do you wish to leave that as highest one minus lowest one?