Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    2-Dimensional Sorts

    Recently we used this code to 5x5 monthly sorts(groupings). I need modification in this code so that sort variables [beta, idiovol] each month takes lag values for doing sorts. Further, mcap variable for value-weighting of portfolios returns should also use lag values for each month. I write the code and the data example for your attention.

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input float stock_id str52 stock float(mdate rt idiovol mcap beta)
1 "3M India Ltd."               737  -.04806162 .0323399  274291.5  .7719392
1 "3M India Ltd."               738  .010038767 .0478723 277842.25  .7580483
1 "3M India Ltd."               739  -.01487988 .0648479 274491.44  .7786915
1 "3M India Ltd."               740     .017993 .0667663 280279.44   .788112
1 "3M India Ltd."               741   .01566974 .0453918 285552.06  .7690934
1 "3M India Ltd."               742 -.015470287 .0542255  281996.8  .7138504
1 "3M India Ltd."               743   .00887538 .0702388 285379.13  .6812625
1 "3M India Ltd."               744  -.01658805 .0569543 281552.97  .6911102
1 "3M India Ltd."               745   -.1659978 .0645437 239224.45  .6915833
1 "3M India Ltd."               746    -.079899  .046399  221535.5   .674289
1 "3M India Ltd."               747   .01135885 .0625116  224757.3  .6059958
1 "3M India Ltd."               748   .00797105 .1198379 227254.77  .6482267
1 "3M India Ltd."               749   .02793397 .0411047 234413.16  .6151269
2 "A B B India Ltd."            737   .07670654 .0644571  382717.1  .6678931
2 "A B B India Ltd."            738  -.06371965 .0582048  360106.5  .6807057
2 "A B B India Ltd."            739    .1050913 .0832153  401110.8  .6953267
2 "A B B India Ltd."            740  -.01552735  .067464  396067.3  .6964302
2 "A B B India Ltd."            741   .11814798  .084044  447063.1 .52544713
2 "A B B India Ltd."            742 -.022736236 .0915238  438300.7 .45354235
2 "A B B India Ltd."            743   .07437541 .1100729  473583.4  .5153419
2 "A B B India Ltd."            744    .0265142 .0813817  487813.1  .5206743
2 "A B B India Ltd."            745  -.06714319 .0721692    457542  .5687587
2 "A B B India Ltd."            746  -.00532828 .0817813  456514.2  .5772421
2 "A B B India Ltd."            747  -.02856038 .0481881  445028.8 .56209445
2 "A B B India Ltd."            748    .1422505 .1214329  514640.7 .54890037
2 "A B B India Ltd."            749  -.05650755 .0596184  487866.1  .5626984
3 "A C C Ltd."                  737   .01217251 .0361403  378588.5 1.0423099
3 "A C C Ltd."                  738     .168647 .0591897  449403.1  .9831923
3 "A C C Ltd."                  739   .00553452 .0473815  453140.1  .9884856
3 "A C C Ltd."                  740  -.07083729 .0459216  423366.4  .9421372
3 "A C C Ltd."                  741   .03147357 .0619389  438201.6  .8724822
3 "A C C Ltd."                  742 -.022611026 .0428186  429666.7   .929243
3 "A C C Ltd."                  743  -.03441209  .032635  416399.5   .956625
3 "A C C Ltd."                  744   .02532012 .0639943  428399.1    .97095
3 "A C C Ltd."                  745  -.09133098 .0420379  392212.5  .9366999
3 "A C C Ltd."                  746   .02670798 .0707484 404071.25  .9499795
3 "A C C Ltd."                  747    .0779869  .088467  438192.2  .9934997
3 "A C C Ltd."                  748  -.06285676 .0505886  412765.8  .9128352
3 "A C C Ltd."                  749  -.03216004 .0255292  400935.2   .885094
4 "A I A Engineering Ltd."      737   .09931479 .0423663    201822 .48819375
4 "A I A Engineering Ltd."      738  -.06949053 .0609441  188805.8  .4890794
4 "A I A Engineering Ltd."      739   .00548727 .0634352  190366.8 .50125504
4 "A I A Engineering Ltd."      740  -.04084494 .0825511  183273.9  .4180706
4 "A I A Engineering Ltd."      741   -.0079203 .0591968 182368.44  .4177327
4 "A I A Engineering Ltd."      742 -.021472797 .0419732 179020.06  .3919841
4 "A I A Engineering Ltd."      743  -.01425336 .0546339  177025.2  .4376161
4 "A I A Engineering Ltd."      744 .0022769298 .0448919  177977.8  .3428403
4 "A I A Engineering Ltd."      745    -.150904 .0614575 153520.55  .4736804
4 "A I A Engineering Ltd."      746  -.00311022  .079035 153515.83  .4724625
4 "A I A Engineering Ltd."      747   .17224416  .063362 182934.36  .4127038
4 "A I A Engineering Ltd."      748    .1519105 .1306168 213602.63  .4483807
4 "A I A Engineering Ltd."      749  -.04459813 .0295284  204915.7  .4456185
5 "A P L Apollo Tubes Ltd."     737   .22085688 .0944705 203380.66 1.0019914
5 "A P L Apollo Tubes Ltd."     738   .06681455  .119728  218049.7  .9533787
5 "A P L Apollo Tubes Ltd."     739  -.05246616 .0883245    207471  .9535617
5 "A P L Apollo Tubes Ltd."     740  -.01374459  .098162  205229.1  .9802383
5 "A P L Apollo Tubes Ltd."     741  -.02892217 .0985184    199971 1.0365891
5 "A P L Apollo Tubes Ltd."     742   .13803338 .0505335 230534.73 1.0893663
5 "A P L Apollo Tubes Ltd."     743    .0781837 .1029456 250042.97  .9652008
5 "A P L Apollo Tubes Ltd."     744   -.1504703 .1204846  215778.5  .8929287
5 "A P L Apollo Tubes Ltd."     745  -.01655734 .0641626  212889.8 1.0983696
5 "A P L Apollo Tubes Ltd."     746   .06821336 .0951924  228781.4  .9912108
5 "A P L Apollo Tubes Ltd."     747   .11079407 .0818093 256374.83  .8997301
5 "A P L Apollo Tubes Ltd."     748   -.0905569 .1206685 234900.77  .9837191
5 "A P L Apollo Tubes Ltd."     749   .01356246 .0273971  238842.7  .9774483
6 "A U Small Finance Bank Ltd." 737    .7392504 .0846612  324437.3  1.740589
6 "A U Small Finance Bank Ltd." 738   .15985473 .1171609  381751.7 1.7164747
6 "A U Small Finance Bank Ltd." 739  -.07863426 .1548168    353912 1.6058732
6 "A U Small Finance Bank Ltd." 740   .02662255 .0729186  364648.4 1.5641183
6 "A U Small Finance Bank Ltd." 741  .035151202 .0621502  379356.5  1.528276
6 "A U Small Finance Bank Ltd." 742   -.1047406 .0662544  342758.1  1.530475
6 "A U Small Finance Bank Ltd." 743  -.05608828 .0929357 325159.25 1.5402966
6 "A U Small Finance Bank Ltd." 744    .2313568 .0840481  411117.4 1.5250118
6 "A U Small Finance Bank Ltd." 745  -.08724298  .063768  378007.6 1.5259495
6 "A U Small Finance Bank Ltd." 746   .03079853 .1236316  392318.2 1.3681957
6 "A U Small Finance Bank Ltd." 747   .10770035 .0642949  438295.5 1.1745833
6 "A U Small Finance Bank Ltd." 748  -.11025386 .0895615  393976.8 1.1438679
6 "A U Small Finance Bank Ltd." 749   .00369559 .0443322  396655.1 1.1259993
7 "Aarti Drugs Ltd."            737  -.03026349 .0438261  65519.13  .8370945
7 "Aarti Drugs Ltd."            738  -.06086754 .0626653  61824.39  .9349767
7 "Aarti Drugs Ltd."            739  -.08375075  .060229  57013.82  .8787534
7 "Aarti Drugs Ltd."            740  -.01619936 .0397843  56259.13  .7923868
7 "Aarti Drugs Ltd."            741  -.03976501 .0576542  54226.56  .6963603
7 "Aarti Drugs Ltd."            742  -.14864677   .07694  46874.12   .738667
7 "Aarti Drugs Ltd."            743   .06933748 .0830293  50392.92  .6845682
7 "Aarti Drugs Ltd."            744  -.08062452 .0379597  46633.36  .6391574
7 "Aarti Drugs Ltd."            745  -.10324657 .0571216  42188.56  .7986168
7 "Aarti Drugs Ltd."            746  -.06323781 .0616954   39725.4  .7479089
7 "Aarti Drugs Ltd."            747   .06907852 .0717106  42697.86  .7516047
7 "Aarti Drugs Ltd."            748  -.08213773 .0534167  39452.23  .7842628
7 "Aarti Drugs Ltd."            749   .01506264 .0502416  40174.51  .7792369
8 "Aarti Industries Ltd."       737   .03611046 .0671559  315940.4  .6314617
8 "Aarti Industries Ltd."       738   .06691506 .0657473    338760  .6677924
8 "Aarti Industries Ltd."       739   .00083189 .0700362  339974.4  .7041731
8 "Aarti Industries Ltd."       740  -.01170964 .0467174 336983.75  .6659802
8 "Aarti Industries Ltd."       741  .033680562 .0990134  349562.6  .8677816
8 "Aarti Industries Ltd."       742 -.031820938 .0718982  339611.9  .9145113
8 "Aarti Industries Ltd."       743   .06836545 .0643875  364751.6   .977203
8 "Aarti Industries Ltd."       744  -.02190028 .0728442  357954.6 1.0181726
8 "Aarti Industries Ltd."       745  -.06695323 .0670774  335805.6 1.2519336
end
format %tm mdate
    Code:
    capture program drop one_mdate
program define one_mdate
    gen cutoffs = .
    _pctile beta, nq(5)
    forvalues i = 1/4 {
        replace cutoffs = r(r`i') in `i'
    }
    display `"`cutoffs'"'
    xtile beta_quintile = beta, cutpoints(cutoffs)
    drop cutoffs
    by beta_quintile, sort: egen idiovol_quintile = xtile(idiovol), nq(5)
    tab *_quintile
    exit
end


// DOUBLE SORT EACH FISCAL YEAR
runby one_mdate, by(mdate) status

capture program drop one_weighted_return
program define one_weighted_return
    if !missing(beta_quintile,idiovol_quintile){
        egen numerator = total(mcap*rt)
        egen denominator = total(mcap)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end

runby one_weighted_return, by(mdate beta_quintile idiovol_quintile)

gen int portfolio_num = (beta_quintile-1)*5 + idiovol_quintile

by mdate beta_quintile idiovol_quintile, sort: egen ew_mean_rt = mean(rt)
drop if missing(beta_quintile,idiovol_quintile, vw_mean_rt, ew_mean_rt)
keep mdate *_quintile *_mean_rt
by mdate *_quintile, sort: gen stock_count = _N
by mdate *_quintile, sort: keep if _n == 1

gen str32 group = "beta_q" + string(beta_quintile) +"_idiovol_q" +string(idiovol_q) + "_"
drop *_quintile
reshape wide @stock_count vw_mean_@rt ew_mean_@rt, i(mdate) j(group) string
    Tags: None
  • #2
    I'm interpreting this to mean that in each month, each stock is assigned to a pair of quintiles based on the previous (rather than the current) month's values of beta and idiovol. If that's right, then:
    Code:
    capture program drop one_mdate
program define one_mdate
    gen cutoffs = .
    _pctile beta_lag, nq(5)
    forvalues i = 1/4 {
        replace cutoffs = r(r`i') in `i'
    }
    display `"`cutoffs'"'
    xtile beta_quintile = beta_lag, cutpoints(cutoffs)
    drop cutoffs
    by beta_quintile, sort: egen idiovol_quintile = xtile(idiovol_lag), nq(5)
    tab *_quintile
    exit
end

xtset stock_id mdate
gen beta_lag = L1.beta
gen idiovol_lag = L1.idiovol

// DOUBLE SORT EACH FISCAL YEAR
runby one_mdate, by(mdate) status

capture program drop one_weighted_return
program define one_weighted_return
    if !missing(beta_quintile,idiovol_quintile){
        egen numerator = total(mcap*rt)
        egen denominator = total(mcap)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end

runby one_weighted_return, by(mdate beta_quintile idiovol_quintile)

gen int portfolio_num = (beta_quintile-1)*5 + idiovol_quintile

by mdate beta_quintile idiovol_quintile, sort: egen ew_mean_rt = mean(rt)
drop if missing(beta_quintile,idiovol_quintile, vw_mean_rt, ew_mean_rt)
keep mdate *_quintile *_mean_rt
by mdate *_quintile, sort: gen stock_count = _N
by mdate *_quintile, sort: keep if _n == 1

gen str32 group = "beta_q" + string(beta_quintile) +"_idiovol_q" +string(idiovol_q) + "_"
drop *_quintile
reshape wide @stock_count vw_mean_@rt ew_mean_@rt, i(mdate) j(group) string

    Comment

    • #3
      The code #2 runs fine except that the one_weighted_return part of code should also use lag mcap for weighting each month portfolio returns. This is not done in #2. Moreover, is the error which is given in attachment image worth ignoring or needs debugging. Further, if instead of 5*5, if i want to do 4*4 sorts, then what more changes are necessary.
      Attached Files
      Last edited by Sartaj Hussain; 22 Aug 2022, 13:49.

      Comment

      • #4
        To use lagged mcap, the code (changes in bold face) is:
        Code:
        capture program drop one_mdate
program define one_mdate
    gen cutoffs = .
    _pctile beta_lag, nq(5)
    forvalues i = 1/4 {
        replace cutoffs = r(r`i') in `i'
    }
    display `"`cutoffs'"'
    xtile beta_quintile = beta_lag, cutpoints(cutoffs)
    drop cutoffs
    by beta_quintile, sort: egen idiovol_quintile = xtile(idiovol_lag), nq(5)
    tab *_quintile
    exit
end

xtset stock_id mdate
gen beta_lag = L1.beta
gen idiovol_lag = L1.idiovol
gen mcap_lag = L1.mcap

// DOUBLE SORT EACH FISCAL YEAR
runby one_mdate, by(mdate) status

capture program drop one_weighted_return
program define one_weighted_return
    if !missing(beta_quintile,idiovol_quintile){
        egen numerator = total(mcap_lag*rt)
        egen denominator = total(mcap_lag)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end

runby one_weighted_return, by(mdate beta_quintile idiovol_quintile)

gen int portfolio_num = (beta_quintile-1)*5 + idiovol_quintile

by mdate beta_quintile idiovol_quintile, sort: egen ew_mean_rt = mean(rt)
drop if missing(beta_quintile,idiovol_quintile, vw_mean_rt, ew_mean_rt)
keep mdate *_quintile *_mean_rt
by mdate *_quintile, sort: gen stock_count = _N
by mdate *_quintile, sort: keep if _n == 1

gen str32 group = "beta_q" + string(beta_quintile) +"_idiovol_q" +string(idiovol_q) + "_"
drop *_quintile
reshape wide @stock_count vw_mean_@rt ew_mean_@rt, i(mdate) j(group) string
        In general it is unwise to ignore error messages. In this case, however, I have explored what is going on. The by-group with errors is arising when mdate is the very first month in the data. In this situation, there are no lagged values of beta or idiovol available, so it is not possible to identify the quintiles. So this can be ignored--it is expected.

        If you want to do quartiles instead of quintiles, the code would be as follows:

        Code:
        capture program drop one_mdate
program define one_mdate
    gen cutoffs = .
    _pctile beta_lag, nq(4)
    forvalues i = 1/3 {
        replace cutoffs = r(r`i') in `i'
    }
    display `"`cutoffs'"'
    xtile beta_quartile = beta_lag, cutpoints(cutoffs)
    drop cutoffs
    by beta_quartile, sort: egen idiovol_quartile = xtile(idiovol_lag), nq(4)
    tab *_quartile
    exit
end

xtset stock_id mdate
gen beta_lag = L1.beta
gen idiovol_lag = L1.idiovol
gen mcap_lag = L1.mcap

// DOUBLE SORT EACH FISCAL YEAR
runby one_mdate, by(mdate) status

capture program drop one_weighted_return
program define one_weighted_return
    if !missing(beta_quartile,idiovol_quartile){
        egen numerator = total(mcap_lag*rt)
        egen denominator = total(mcap_lag)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end

runby one_weighted_return, by(mdate beta_quartile idiovol_quartile)

gen int portfolio_num = (beta_quartile-1)*5 + idiovol_quartile

by mdate beta_quartile idiovol_quartile, sort: egen ew_mean_rt = mean(rt)
drop if missing(beta_quartile,idiovol_quartile, vw_mean_rt, ew_mean_rt)
keep mdate *_quartile *_mean_rt
by mdate *_quartile, sort: gen stock_count = _N
by mdate *_quartile, sort: keep if _n == 1

gen str32 group = "beta_q" + string(beta_quartile) +"_idiovol_q" +string(idiovol_q) + "_"
drop *_quartile
reshape wide @stock_count vw_mean_@rt ew_mean_@rt, i(mdate) j(group) string

        Comment

        • #5
          Code:
          frame create means_and_sds int (idiovol_group mcap_group) ///
    float(mean_vw sd_vw t_vw mean_ew sd_ew t_ew)
forvalues iv = 1/4  {
    forvalues mc = 1/4 {
        summ vw_mean_q`mc'_idiovol_q`iv'_rt
        local mean_vw = r(mean)
        local sd_vw = r(sd)
        ttest vw_mean_q`mc'_idiovol_q`iv'_rt = 0
        local t_vw = r(t)
        summ ew_mean_q`mc'_idiovol_q`iv'_rt
        local mean_ew = r(mean)
        local sd_ew = r(sd)
        ttest ew_mean_q`mc'_idiovol_q`iv'_rt = 0
        local t_ew = r(t)
        frame post means_and_sds (`iv') (`mc') (`mean_vw') (`sd_vw') (`t_vw') ///
            (`mean_ew') (`sd_ew') (`t_ew')
    }
}
frame change means_and_sds
rename (mean* sd* t* ) =_idiovol_
reshape wide *_idiovol_, i(mcap_group) j(idiovol_group)
          Changes in bold face.

          I did not remove the standard deviations. If you really don't want them, you can always drop them at the end.

          CORRECTION: Above posted here in error. Belongs at https://www.statalist.org/forums/for...enerate-t-stat.
          Last edited by Clyde Schechter; 25 Aug 2022, 14:59.

          Comment

          Previous Next
          Working...
          X