Risk Measures

[Sartaj Hussain]

Kindly consider the below data example:

* Example generated by -dataex-. For more info, type help dataex
clear
input float stock_id str54 stock int date float(rt mkt)
1 "3M India Ltd."         15310    -.0887372  -.03955433
1 "3M India Ltd."         15341  -.026404494  .021463245
1 "3M India Ltd."         15372    .04250817   .07242663
1 "3M India Ltd."         15400      .149262  -.02732929
1 "3M India Ltd."         15431   .008990207  -.04528218
1 "3M India Ltd."         15461   -.10039777   -.0853004
1 "3M India Ltd."         15492   .008666431  .002052819
1 "3M India Ltd."         15522    .05207785    -.118447
1 "3M India Ltd."         15553    .05333333    .0436935
1 "3M India Ltd."         15584  -.003164557  -.04591998
1 "3M India Ltd."         15614   .003174603 -.012602217
1 "3M India Ltd."         15645  -.008227848   .04842421
1 "3M India Ltd."         15675  -.034620292   .04941667
1 "3M India Ltd."         15706  -.022806147 -.033169586
1 "3M India Ltd."         15737   -.06308135  .009157823
1 "3M India Ltd."         15765   -.13267148   -.0782898
1 "3M India Ltd."         15796     .1862643 -.010596635
1 "3M India Ltd."         15826    .10526316   .09825777
1 "3M India Ltd."         15857    .06095238   .06033916
1 "3M India Ltd."         15887  .0041891085   .04996381
1 "3M India Ltd."         15918    .10250298     .136254
1 "3M India Ltd."         15949    .11783784  .003297209
1 "3M India Ltd."         15979   .003384913   .06014735
1 "3M India Ltd."         16010     .2060241    .0712712
1 "3M India Ltd."         16040    .03886114   .18632157
1 "3M India Ltd."         16071    -.2037696  -.07444008
1 "3M India Ltd."         16102   -.02415459  .006037154
1 "3M India Ltd."         16131    .02722772  .007745117
1 "3M India Ltd."         16162     .1827711  .008075608
1 "3M India Ltd."         16192   -.17327085   -.1837298
1 "3M India Ltd."         16223    .06653524   .05848542
1 "3M India Ltd."         16253   .016751386   .05154314
1 "3M India Ltd."         16284    .03397341 -.002812333
1 "3M India Ltd."         16315     .1354945   .09279332
1 "3M India Ltd."         16345   -.04432401  .005587839
1 "3M India Ltd."         16376   .067341775   .07218837
1 "3M India Ltd."         16406     .0975332    .0309204
1 "3M India Ltd."         16437  -.006310512 -.030511327
2 "3P Land Holdings Ltd." 15310   -.04444445  -.03955433
2 "3P Land Holdings Ltd." 15341  -.011627907  .021463245
2 "3P Land Holdings Ltd." 15372     .2235294   .07242663
2 "3P Land Holdings Ltd." 15400   -.09615385  -.02732929
2 "3P Land Holdings Ltd." 15431   -.28723404  -.04528218
2 "3P Land Holdings Ltd." 15461     .9701493   -.0853004
2 "3P Land Holdings Ltd." 15492    .25757575  .002052819
2 "3P Land Holdings Ltd." 15522    -.1566265    -.118447
2 "3P Land Holdings Ltd." 15553    -.3214286    .0436935
2 "3P Land Holdings Ltd." 15584   -.15789473  -.04591998
2 "3P Land Holdings Ltd." 15614            0 -.012602217
2 "3P Land Holdings Ltd." 15645            0   .04842421
2 "3P Land Holdings Ltd." 15675         .125   .04941667
2 "3P Land Holdings Ltd." 15706   -.12777779 -.033169586
2 "3P Land Holdings Ltd." 15737     .2101911  .009157823
2 "3P Land Holdings Ltd." 15765    -.2105263   -.0782898
2 "3P Land Holdings Ltd." 15796    .13333334 -.010596635
2 "3P Land Holdings Ltd." 15826    -.1117647   .09825777
2 "3P Land Holdings Ltd." 15857     .3642384   .06033916
2 "3P Land Holdings Ltd." 15887   -.05339806   .04996381
2 "3P Land Holdings Ltd." 15918    .11794872     .136254
2 "3P Land Holdings Ltd." 15949    -.0825688  .003297209
2 "3P Land Holdings Ltd." 15979         -.19   .06014735
2 "3P Land Holdings Ltd." 16010     .4691358    .0712712
2 "3P Land Holdings Ltd." 16040    .25210086   .18632157
2 "3P Land Holdings Ltd." 16071    -.3389262  -.07444008
2 "3P Land Holdings Ltd." 16102    .11675127  .006037154
2 "3P Land Holdings Ltd." 16131     .0909091  .007745117
2 "3P Land Holdings Ltd." 16162   -.06666667  .008075608
2 "3P Land Holdings Ltd." 16192          .25   -.1837298
2 "3P Land Holdings Ltd." 16223   -.14285715   .05848542
2 "3P Land Holdings Ltd." 16253    .15833333   .05154314
2 "3P Land Holdings Ltd." 16284    .26258993 -.002812333
2 "3P Land Holdings Ltd." 16315    .36182335   .09279332
2 "3P Land Holdings Ltd." 16345    .10878661  .005587839
2 "3P Land Holdings Ltd." 16376    .10377359   .07218837
2 "3P Land Holdings Ltd." 16406    .14529915    .0309204
2 "3P Land Holdings Ltd." 16437   -.19701493 -.030511327
3 "A B B India Ltd."      15310  -.004149378  -.03955433
3 "A B B India Ltd."      15341    .09068628  .021463245
3 "A B B India Ltd."      15372      .154382   .07242663
3 "A B B India Ltd."      15400    .03387191  -.02732929
3 "A B B India Ltd."      15431   .002824327  -.04528218
3 "A B B India Ltd."      15461   -.07641757   -.0853004
3 "A B B India Ltd."      15492    .09270177  .002052819
3 "A B B India Ltd."      15522   .031069767    -.118447
3 "A B B India Ltd."      15553    .01930711    .0436935
3 "A B B India Ltd."      15584   -.00495663  -.04591998
3 "A B B India Ltd."      15614   -.14659314 -.012602217
3 "A B B India Ltd."      15645 -.0002084636   .04842421
3 "A B B India Ltd."      15675   .036905754   .04941667
3 "A B B India Ltd."      15706    .12306455 -.033169586
3 "A B B India Ltd."      15737    .14753805  .009157823
3 "A B B India Ltd."      15765    -.1015759   -.0782898
3 "A B B India Ltd."      15796    .07033692 -.010596635
3 "A B B India Ltd."      15826    .11487912   .09825777
3 "A B B India Ltd."      15857    .07335177   .06033916
3 "A B B India Ltd."      15887   .028338984   .04996381
3 "A B B India Ltd."      15918     .2542194     .136254
3 "A B B India Ltd."      15949   .003153911  .003297209
3 "A B B India Ltd."      15979    .09955984   .06014735
3 "A B B India Ltd."      16010   .010293557    .0712712
end
format %td date

From this data, following is needed on rolling basis:

1. Monthly standard deviation, skewness and kurtosis of rt. For each month, previous 36 months of rt data should be used.

2. Beta (slope coefficient), idiovol (standard deviation of residuals) and idskew (skewness of residuals) obtained from least square regressions of rt (dependent)on mkt (independent) data using previous 36 months.

3. Third, from a separate regression of rt on mkt and mkt squared using previous 36 months data, generate coskew (as beta coefficient of mkt squared term).

As a whole these are seven monthly variables to be generated.

[Carlo Lazzaro]

Sartaj:
please note https://www.statalist.org/forums/help#adviceextras #4. Thanks.

[Clyde Schechter]

I'm pretty sure O.P. is no longer a student and this is not homework.

I assume here that 36 previous months means from 36 months before through 1 month before the current month and does not include the current month.

gen mdate = mofd(date)
format mdate %tm

rangestat (sd) rt (skewness) rt (kurtosis) rt, by(stock_id) interval(mdate -36 -1)

rangestat (reg) rt mkt, by(stock_id) interval(mdate -36 -1)
gen residual_linear = rt - b_mkt*mkt - b_cons
rename b_mkt beta
drop reg_nobs reg_r2 reg_adj_r2 *_cons se_* b_cons

rangestat (sd) idiovol = residual (skewness) idskew = residual, ///
    by(stock_id) interval(mdate -36 -1)
drop residual
    
gen mkt_sq = mkt*mkt
rangestat (reg) rt mkt mkt_sq, by(stock_id) interval(mdate -36 -1)
rename b_mkt_sq coskew
drop b_mkt b_cons se_* reg_* mkt_sq

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

[Sartaj Hussain]

I assume here that 36 previous months means from 36 months before through 1 month before the current month and does not include the current month.

Yes it doesn't include the current month, for current month, previous 36 observations are the calculation or estimation window.

But generation of variables should begin from year/month (2004,12) based on previous 36 months of data from year/month (2001, 12 to 2004,11) which apparently is not happening using code #3. Had it been so then each stock_id should generate only two variables, i.e. from year/month (2004,12) onwards.

[Clyde Schechter]

I take it you mean that you only want to keep results that are based on a complete 36 months of data: observations where the data does not extend far back enough to produce 36 observations should be excluded. If so:

gen mdate = mofd(date)
format mdate %tm


rangestat (count) rt (sd) rt (skewness) rt (kurtosis) rt, by(stock_id) interval(mdate -36 -1)
foreach v of varlist rt_sd rt_skewness rt_kurtosis {
    replace `v' = . if rt_count < 36
}
drop rt_count

rangestat (reg) rt mkt, by(stock_id) interval(mdate -36 -1)
gen residual = rt - b_mkt*mkt - b_cons
rename b_mkt beta
drop reg_r2 reg_adj_r2 *_cons se_* b_cons
rangestat (sd) idiovol = residual (skewness) idskew = residual, ///
    by(stock_id) interval(mdate -36 -1)
foreach v of varlist beta idiovol idskew {
    replace `v' = . if reg_nobs < 36
}
drop residual reg_nobs
    
gen mkt_sq = mkt*mkt
rangestat (reg) rt mkt mkt_sq, by(stock_id) interval(mdate -36 -1)
rename b_mkt_sq coskew
replace coskew = . if reg_nobs < 36
drop b_mkt b_cons se_* reg_* mkt_sq

In the future, it will be clearer if you state explicitly when you want to retain results only for observations having a complete window. In my line of work, when we speak of, say, a 36 month window, we normally mean "however much data you can find going back up to 36 months." (And in my line of work, actually finding someone with monthly data for 36 months would be a very rare event.) So, unless prompted in that way, my response will normally be to leave behind results for anything up to 36 months.

[Sartaj Hussain]

Sure i will ensure what you suggested. Now there is some code that we developed in past which is reproduced below. I tried running it on the below dataset but encountered some errors. I would like you to examine it. The actual data set is from December 2001 to December 2022.

* Example generated by -dataex-. For more info, type help dataex
clear
input float stock_id str54 stock int date float(mdate rt) double mcap float bmr
1 "3M India Ltd."         15310 503    -.0887372  300.78 .23148148
1 "3M India Ltd."         15341 504  -.026404494  292.84  .2375297
1 "3M India Ltd."         15372 505    .04250817  305.28 .22779043
1 "3M India Ltd."         15400 506      .149262  350.85  .1984127
1 "3M India Ltd."         15431 507   .008990207     354 .20876826
1 "3M India Ltd."         15461 508   -.10039777  318.46 .23201856
1 "3M India Ltd."         15492 509   .008666431  321.22 .22988506
1 "3M India Ltd."         15522 510    .05207785  337.95 .23474178
1 "3M India Ltd."         15553 511    .05333333  355.98 .22271715
1 "3M India Ltd."         15584 512  -.003164557  354.85 .23809524
1 "3M India Ltd."         15614 513   .003174603  355.98  .2369668
1 "3M India Ltd."         15645 514  -.008227848  353.05 .23923445
1 "3M India Ltd."         15675 515  -.034620292  340.82 .24813896
1 "3M India Ltd."         15706 516  -.022806147  333.05  .2538071
1 "3M India Ltd."         15737 517   -.06308135  312.04  .2710027
1 "3M India Ltd."         15765 518   -.13267148  270.64  .3355705
1 "3M India Ltd."         15796 519     .1862643  321.05 .28248587
1 "3M India Ltd."         15826 520    .10526316  354.85 .25575447
1 "3M India Ltd."         15857 521    .06095238  376.48  .2544529
1 "3M India Ltd."         15887 522  .0041891085  378.06 .25316456
1 "3M India Ltd."         15918 523    .10250298  416.81 .22988506
1 "3M India Ltd."         15949 524    .11783784  465.92  .2202643
1 "3M India Ltd."         15979 525   .003384913   467.5  .2197802
1 "3M India Ltd."         16010 526     .2060241  563.82 .18214937
1 "3M India Ltd."         16040 527    .03886114  585.73  .1869159
2 "3P Land Holdings Ltd." 15310 503   -.04444445     3.1  11.11111
2 "3P Land Holdings Ltd." 15341 504  -.011627907    3.06  11.11111
2 "3P Land Holdings Ltd." 15372 505     .2235294    3.74  9.090909
2 "3P Land Holdings Ltd." 15400 506   -.09615385    3.38        10
2 "3P Land Holdings Ltd." 15431 507   -.28723404    2.41 14.285714
2 "3P Land Holdings Ltd." 15461 508     .9701493    4.75  7.142857
2 "3P Land Holdings Ltd." 15492 509    .25757575    5.98  5.555555
2 "3P Land Holdings Ltd." 15522 510    -.1566265    5.04  6.666667
2 "3P Land Holdings Ltd." 15553 511    -.3214286    3.42        10
2 "3P Land Holdings Ltd." 15584 512   -.15789473    2.88      12.5
2 "3P Land Holdings Ltd." 15614 513            0    2.88      12.5
2 "3P Land Holdings Ltd." 15645 514            0    2.88      12.5
2 "3P Land Holdings Ltd." 15675 515         .125    3.24        10
2 "3P Land Holdings Ltd." 15706 516   -.12777779    2.83      12.5
2 "3P Land Holdings Ltd." 15737 517     .2101911    3.42        10
2 "3P Land Holdings Ltd." 15765 518    -.2105263     2.7      12.5
2 "3P Land Holdings Ltd." 15796 519    .13333334    3.06  11.11111
2 "3P Land Holdings Ltd." 15826 520    -.1117647    2.72      12.5
2 "3P Land Holdings Ltd." 15857 521     .3642384    3.71  9.090909
2 "3P Land Holdings Ltd." 15887 522   -.05339806    3.51        10
2 "3P Land Holdings Ltd." 15918 523    .11794872    3.92  8.333333
2 "3P Land Holdings Ltd." 15949 524    -.0825688     3.6  9.090909
2 "3P Land Holdings Ltd." 15979 525         -.19    2.92  11.11111
2 "3P Land Holdings Ltd." 16010 526     .4691358    4.28  7.692307
2 "3P Land Holdings Ltd." 16040 527    .25210086    5.36      6.25
3 "A B B India Ltd."      15310 503  -.004149378  864.59  .4901961
3 "A B B India Ltd."      15341 504    .09068628  942.99  .4484305
3 "A B B India Ltd."      15372 505      .154382 1088.57 .38910505
3 "A B B India Ltd."      15400 506    .03387191 1125.45 .37593985
3 "A B B India Ltd."      15431 507   .002824327 1128.62  .3802281
3 "A B B India Ltd."      15461 508   -.07641757 1042.38  .4115226
3 "A B B India Ltd."      15492 509    .09270177 1139.01  .3773585
3 "A B B India Ltd."      15522 510   .031069767  1174.4  .3937008
3 "A B B India Ltd."      15553 511    .01930711 1197.07  .3861004
3 "A B B India Ltd."      15584 512   -.00495663 1191.14  .4016064
3 "A B B India Ltd."      15614 513   -.14659314 1016.52  .4716981
3 "A B B India Ltd."      15645 514 -.0002084636 1016.31  .4716981
3 "A B B India Ltd."      15675 515   .036905754 1053.82  .4716981
3 "A B B India Ltd."      15706 516    .12306455 1183.51  .4201681
3 "A B B India Ltd."      15737 517    .14753805 1358.12  .3649635
3 "A B B India Ltd."      15765 518    -.1015759 1220.17 .41322315
3 "A B B India Ltd."      15796 519    .07033692 1305.99  .3861004
3 "A B B India Ltd."      15826 520    .11487912 1456.02  .3472222
3 "A B B India Ltd."      15857 521    .07335177 1562.82  .3521127
3 "A B B India Ltd."      15887 522   .028338984 1607.11  .3424658
3 "A B B India Ltd."      15918 523     .2542194 2015.67 .27322406
3 "A B B India Ltd."      15949 524   .003153911 2022.03 .28248587
3 "A B B India Ltd."      15979 525    .09955984 2223.34  .2570694
3 "A B B India Ltd."      16010 526   .010293557 2246.23  .2544529
3 "A B B India Ltd."      16040 527     .2712264 2855.47 .20618556
4 "A B C India Ltd."      15310 503   -.15254237     2.5      12.5
4 "A B C India Ltd."      15341 504          -.2       2 16.666666
4 "A B C India Ltd."      15372 505       -.2375    1.53        20
4 "A B C India Ltd."      15400 506     .8032787    2.75  11.11111
4 "A B C India Ltd."      15431 507   -.28181818    1.98      12.5
4 "A B C India Ltd."      15461 508     .0886076    2.15      12.5
4 "A B C India Ltd."      15492 509    .25581396     2.7        10
4 "A B C India Ltd."      15522 510  -.074074075     2.5  11.11111
4 "A B C India Ltd."      15553 511          .02    2.55        10
4 "A B C India Ltd."      15584 512   -.11764706    2.25      12.5
4 "A B C India Ltd."      15614 513           .2     2.7        10
4 "A B C India Ltd."      15645 514   -.24074075    2.05      12.5
4 "A B C India Ltd."      15675 515    .09756097    2.25      12.5
4 "A B C India Ltd."      15706 516    -.1777778    1.85 14.285714
4 "A B C India Ltd."      15737 517     .1081081    2.05      12.5
4 "A B C India Ltd."      15765 518    -.1707317     1.7 16.666666
4 "A B C India Ltd."      15796 519    .05882353     1.8 14.285714
4 "A B C India Ltd."      15826 520    .31944445    2.38  11.11111
4 "A B C India Ltd."      15857 521    .24210526    2.95  9.090909
4 "A B C India Ltd."      15887 522   -.18305084    2.41  11.11111
4 "A B C India Ltd."      15918 523     .3298755    3.21  8.333333
4 "A B C India Ltd."      15949 524     -.074883    2.97  9.090909
4 "A B C India Ltd."      15979 525   -.08094435    2.73        10
4 "A B C India Ltd."      16010 526    .06422018     2.9        10
4 "A B C India Ltd."      16040 527     1.336207    6.78 4.1666665
end
format %td date
format %tm mdate

gen mdate = mofd(date)
format mdate %tm
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 == 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)

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 mar_bmr_group = bmr, cutpoints(cut)
    }
    else {
        gen mar_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 mar_bmr_group, from(three_groups)
frame drop three_groups
drop three_groups

gen lagged_mcap = L1.mcap
capture program drop one_weighted_return
program define one_weighted_return
    if !missing(june_mcap_group, mar_bmr_group) {
        egen numerator = total(lagged_mcap*rt)
        egen denominator = total(lagged_mcap)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end
drop if missing(june_mcap_group, mar_bmr_group)
runby one_weighted_return, by(mdate june_mcap_group mar_bmr_group)

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

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

Moreover, where would the below code be placed in the overall code.

by mdate: egen S = mean(cond(june_mcap_group == 1, vw_mean_rt, .))
by mdate: egen B = mean(cond(june_mcap_group == 2, vw_mean_rt, .))
gen SMB = S - B

by mdate: egen H = mean(cond(mar_bmr_group == 3, vw_mean_rt, .))
by mdate: egen L = mean(cond(mar_bmr_group == 1, vw_mean_rt, .))
gen HML = H - L

[Carlo Lazzaro]

Clyde Schechter is correct:
My mistake that calls for apologizing: Sartaj Hussain is no longer a student.
I was misled by the way the questions were posed.

[Clyde Schechter]

Re #6: This code is not usable with this data; it solves a problem which is not defined in this data set. The code was written to define the preceding fiscal year's mcap and bmr from values provided in March (bmr) and June (mcap) only. But this data set contains mcap and bmr in all months, so which month to use as the reference value for the preceding fiscal year is unspecified.

Moreover, with only 12 months of data, depending on how you define the fiscal year, your results might actually be based on only 6 months (or possibly even fewer) of data.

If you can clarify the time points to use, I think we can modify the code to suit the current situation.

[Sartaj Hussain]

Re #6: This code is not usable with this data; it solves a problem which is not defined in this data set. The code was written to define the preceding fiscal year's mcap and bmr from values provided in March (bmr) and June (mcap) only. But this data set contains mcap and bmr in all months, so which month to use as the reference value for the preceding fiscal year is unspecified.

If preceding fiscal year is the previous year running through July to June then it is to be kept intact in code, i,e, mcap and bmr values of preceding fiscal year June and March months should be taken for doing sorts and forming groups. All other values of mcap and bmr are irrelevant and to be ignored. But we need mcap values for generating weighted average returns.

Moreover, with only 12 months of data, depending on how you define the fiscal year, your results might actually be based on only 6 months (or possibly even fewer) of data.

The example data in #6 has about 24 months of data for each stock_id. The actual data file contains data from December 2001 to December 2022.

[Clyde Schechter]

Thank you. Additional question. In calculating the weighted returns, what is the correct mcap to use for weighting. Is it the mcap from the June preceding the start of the current fiscal year, or is it the mcap from the immediately preceding month? Or the current month's mcap? Or something else? (In the data for which the code in #1 was written, only the first of these was available. But, now we have mcap in every month, and your saying "we need mcap values for generating weighted average returns" in #6 leads me to think perhaps you mean the immediately preceding month's mcap.)

[Sartaj Hussain]

Sorry. it is mcap from the June preceding the start of the current fiscal year and not the immediately preceding month's mcap. The mcap values other than June preceding for each year are irrelevant. Thanks for pointing out these issues. I hope the clarification will be helpful in forming a right code.

[Clyde Schechter]

No problem; thanks for the clarification. I think this does it:

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)) & inlist(moy, 3, 6), into(mcap_bmr_work)
frame change mcap_bmr_work
isid stock_id fyear moy, sort
replace fyear = year
collapse (first) bmr (last) mcap, by(stock_id fyear)
// assert (moy == 3) == !missing(bmr)
// assert (moy == 6) == !missing(mcap)
// replace fyear = year  if !missing(mcap)
// replace fyear = year if !missing(bmr)

frame change default
rename (mcap bmr) orig=
frlink m:1 stock_id fyear, frame(mcap_bmr_work)
frget mcap bmr, from(mcap_bmr_work)
label var mcap "mcap June Preceding FY"
label var bmr "bmr March Preceding FY"
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

//  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 mar_bmr_group = bmr, cutpoints(cut)
    }
    else {
        gen mar_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 mar_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, mar_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, mar_bmr_group)
runby one_weighted_return, by(mdate june_mcap_group mar_bmr_group)

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

by mdate: egen S = mean(cond(june_mcap_group == 1, vw_mean_rt, .))
by mdate: egen B = mean(cond(june_mcap_group == 2, vw_mean_rt, .))
gen SMB = S - B

by mdate: egen H = mean(cond(mar_bmr_group == 3, vw_mean_rt, .))
by mdate: egen L = mean(cond(mar_bmr_group == 1, vw_mean_rt, .))
gen HML = H - L

//  AND IF YOU WANT TO REDUCE TO ONE OBSERVATION PER MONTH
label define june_mcap_group 1 "S" 2 "B"
label define mar_bmr_group 1 "L" 2 "M" 3 "H"
label values june_mcap_group june_mcap_group
label values mar_bmr_group mar_bmr_group
decode june_mcap_group, gen (mcap_group)
decode mar_bmr_group, gen(bmr_group)
drop june_mcap_group mar_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

[Sartaj Hussain]

#12 works perfectly fine. Now, for each stock_id in #1, I require EWMA volatility measures. The model of EWMA volatility is:

The below table shows the step-wise method for EWMA. The procedure in this table is fairly simple. But instead of rt^2 used in it, we use demeaned rt^2.

The decay factor or lamda in our case is 0.97. The recent observations in this model get a higher lamda than the older observations. The process follows an exponential decaying process. The EWMA volatility of a stock_id in a month should be calculated on previous 36 months of squared demeaned rt. The demeaned rt is rt on a given month minus the mean value. Let's name this measure as ewma_rt. The second measure should use squared demeaned residuals from regression of rt on mkt instead of rt as in #1 above. Let's call this measure as ewma_idio. Hope that i stated the problem clearly and a code could be possible for it.

[Clyde Schechter]

I am unclear on just one thing. When you say

The demeaned rt is rt on a given month minus the mean value.

I imagine that "the mean value" is the mean value of rt for the particular stock_id during the 36 month window? Is that correct? Or is it the mean value of rt for the particular stock_id over all available observations? Or perhaps the mean value of rt for all stocks during the window? Or perhaps the mean value of rt for all stocks over all available observations? Similar question regarding ewma_idio: which mean value is to be use for demeaning?

Added: One more question. Like the other measures from earlier in the thread, shall I assume you want this calculated only for those windows where there is data for the all 36 months in the window?

[Sartaj Hussain]

I imagine that "the mean value" is the mean value of rt for the particular stock_id during the 36 month window? Is that correct? Or is it the mean value of rt for the particular stock_id over all available observations? Or perhaps the mean value of rt for all stocks during the window? Or perhaps the mean value of rt for all stocks over all available observations? Similar question regarding ewma_idio: which mean value is to be use for demeaning?

The answer is the mean value" is the mean value of rt for the particular stock_id during the 36 month window.

One more question. Like the other measures from earlier in the thread, shall I assume you want this calculated only for those windows where there is data for the all 36 months in the window?

Yes.

I am keeping the window 36 months as dataex does not allow more data observation. In my actual data file, this window is 5 years, i.e. 60 months and there are no missing data observation. I hope that change ca be easily done.