Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dipendent Double-Sorting 25 Portfolios

    Dear all,
    I am struggling to replicate the FF-25 portfolios with a variant, I should employ a dependent sort instead of a independent sort.
    My dataset is the following one:
    permno date primexch ret year month datem BM id MarketCap
    10001 30-May-86 Q -0.00980 1986 5 316 . 2 .
    10001 30-Jun-86 Q -0.01307 1986 6 317 . 2 1.797265
    10001 31-Jul-86 Q -0.01020 1986 7 318 . 2 1.797265
    10001 29-Aug-86 Q 0.07216 1986 8 319 . 2 1.797265
    10001 30-Sep-86 Q -0.00308 1986 9 320 . 2 1.797265
    10001 31-Oct-86 Q 0.03922 1986 10 321 . 2 1.797265
    10001 28-Nov-86 Q 0.05660 1986 11 322 . 2 1.797265
    10001 31-Dec-86 Q 0.01500 1986 12 323 . 2 1.797265
    10001 30-Jan-87 Q -0.03571 1987 1 324 . 2 1.797265
    10001 27-Feb-87 Q -0.07407 1987 2 325 . 2 1.797265
    10001 31-Mar-87 Q 0.03680 1987 3 326 . 2 1.797265
    10001 30-Apr-87 Q -0.03922 1987 4 327 . 2 1.797265
    10001 29-May-87 Q -0.07143 1987 5 328 . 2 1.797265
    10001 30-Jun-87 Q 0.05143 1987 6 329 1.0144155 2 1.761665
    10001 31-Jul-87 Q 0.02128 1987 7 330 1.0144155 2 1.761665
    10001 31-Aug-87 Q 0.08333 1987 8 331 1.0144155 2 1.761665
    10001 30-Sep-87 Q -0.02231 1987 9 332 1.0144155 2 1.761665
    10001 30-Oct-87 Q 0.02000 1987 10 333 1.0144155 2 1.761665
    10001 30-Nov-87 Q -0.02941 1987 11 334 1.0144155 2 1.761665
    10001 31-Dec-87 Q -0.03354 1987 12 335 1.0144155 2 1.761665
    10001 29-Jan-88 Q 0.06383 1988 1 336 1.0144155 2 1.761665
    10001 29-Feb-88 Q 0.08000 1988 2 337 1.0144155 2 1.761665
    10001 31-Mar-88 Q -0.07630 1988 3 338 1.0144155 2 1.761665
    10001 29-Apr-88 Q 0.03061 1988 4 339 1.0144155 2 1.761665
    10001 31-May-88 Q 0.01980 1988 5 340 1.0144155 2 1.761665
    10001 30-Jun-88 Q -0.01204 1988 6 341 1.2076184 2 1.824549
    10001 29-Jul-88 Q 0.03000 1988 7 342 1.2076184 2 1.824549
    10001 31-Aug-88 Q 0.02913 1988 8 343 1.2076184 2 1.824549
    10001 30-Sep-88 Q -0.021132076 1988 9 344 1.2076184 2 1.824549
    10001 31-Oct-88 Q 0.039215688 1988 10 345 1.2076184 2 1.824549
    10001 30-Nov-88 Q 0 1988 11 346 1.2076184 2 1.824549
    where permo identifies the company, primexch identifies the stock exhanges ( Q=Nasdaq, N=Nyse, A=Amex), ret indicates the returns, BM (book-to-market value) is calculated at the end of the year t and it results to be publicly available in June of the year t+1 until May of the year t+2, finally MarketCap indicates the size of each company and it is calculated in June of year t and remains constant until May of year t+1.
    I should sort stocks into 5 quintiles in accordance with their BM, and secondly I should double sort within each quintile according to companies’ Market Cap. The quintiles breakpoints should be calculated using only NYSE stocks (“N”).
    Therefore I should obtain 25 portfolios, which are firstly sorted on BM and secondly on MarketCap.
    Finally I should calculated the value-weighted monthly returns on these 25 portfolios from July of year t to June of year t+1.

    Ps: I tried to write a code for calculate the value-weighted monthly returns on 10 deciles sorted on MarketCap for another type of calculation I had to do..Maybe it could be helpful
    forvalues i = 1(1)10 {
    egen num_return_dec_`i' = total(MarketCap * ret * !missing(MarketCap, ret)) if deciles_MarketCap==`i', by (datem)
    egen den_return_dec_`i' = total(MarketCap * !missing(MarketCap, ret)) if deciles_MarketCap==`i', by (datem)
    gen vw_return_dec`i' = num_return_dec_`i'/den_return_dec_`i' if deciles_MarketCap==`i'
    }
    Any help would be really appreciated as it is one week that I have been trying to solve this problem.
    Best regards,
    Antonio

  • #2
    I thought I understood what you want until I got to
    [quote]The quintiles breakpoints should be calculated using only NYSE stocks (“N”)."
    What does that mean? And how will you implement it when, in any case, your example data seems to imply that there are no stocks with primexch == "N" anyway?

    Also please explain whether in the double sorting the same quintiles of MarketCap are to be used within each quintile of BM, or whether each quintile of BM should have its own quintiles of MarketCap.

    Please post back explaining what you mean by that. Also, in your return post, repost the example data with a new example that does include observations with primexch == "N", and be sure to use the -dataex- command to do that. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

    Comment


    • #3
      Dear Clyde,
      first of all I would like to thank you for having replied and secondly I will try to explain better my problem.
      By “The quintiles breakpoints should be calculated using only NYSE stocks (“N”).", I mean that instead of calculating the breakpoints using all CRSP stocks, I should calculate the breakpoints just using NYSE stocks in order to obtain at least some NYSE stock in each quintile. In fact it can happen that NYSE stocks have a higher BM value than the other stocks, therefore If I used all CRSP stocks to generate breakpoints I would have NYSE stocks only in the last quintile. Therefore, replying to your first question, yes in my dataset there are stocks which come from the NYSE stock exchange “N” and others from AMEX “A” and NASDAQ “Q” stock exchange.
      Regarding your second question, by conditional double sorting I mean that firstly the stocks are sorted in 5 quintiles in accordance with their BM value and secondly each quintile should be sorted in other 5 quintiles in accordance with the marketcap value, generating so 25 quintiles.
      I attach my dataset using the command dataex as suggested by you:

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input double permno long date str2 primexch double(ret year) float(month datem) double BM float(id marketcap)
      10000  9496 ""                      . 1985 12 311                  . 1         .
      10000  9527 "Q"                     . 1986  1 312                  . 1         .
      10000  9555 "Q"    -.2571428716182709 1986  2 313                  . 1         .
      10000  9586 "Q"    .36538460850715637 1986  3 314                  . 1         .
      10000  9616 "Q"   -.09859155118465424 1986  4 315                  . 1         .
      10000  9646 "Q"            -.22265625 1986  5 316                  . 1         .
      10000  9677 "Q"  -.005025125574320555 1986  6 317                  . 1  2.462541
      10000  9708 "Q"   -.08080808073282242 1986  7 318                  . 1  2.462541
      10000  9737 "Q"    -.6153846383094788 1986  8 319                  . 1  2.462541
      10000  9769 "Q"   -.05714285746216774 1986  9 320                  . 1  2.462541
      10000  9800 "Q"   -.24242424964904785 1986 10 321                  . 1  2.462541
      10000  9828 "Q"    .05999999865889549 1986 11 322                  . 1  2.462541
      10000  9861 "Q"   -.37735849618911743 1986 12 323                  . 1  2.462541
      10000  9891 "Q"   -.21212121844291687 1987  1 324                  . 1  2.462541
      10000  9919 "Q"                     0 1987  2 325                  . 1  2.462541
      10000  9951 "Q"   -.38461539149284363 1987  3 326                  . 1  2.462541
      10000  9981 "Q"                -.0625 1987  4 327                  . 1  2.462541
      10000 10010 "Q"   -.06666667014360428 1987  5 328                  . 1  2.462541
      10000 10042 "Q"                     . 1987  6 329 .21094632148742676 1 -.1606457
      10001  9496 ""                      . 1985 12 311                  . 2         .
      10001  9527 "Q"                     . 1986  1 312                  . 2         .
      10001  9555 "Q"   .020408162847161293 1986  2 313                  . 2         .
      10001  9586 "Q"   .025200003758072853 1986  3 314                  . 2         .
      10001  9616 "Q"   .009900989942252636 1986  4 315                  . 2         .
      10001  9646 "Q"  -.009803921915590763 1986  5 316                  . 2         .
      10001  9677 "Q"  -.013069307431578636 1986  6 317                  . 2  1.797265
      10001  9708 "Q"  -.010204081423580647 1986  7 318                  . 2  1.797265
      10001  9737 "Q"    .07216494530439377 1986  8 319                  . 2  1.797265
      10001  9769 "Q"  -.003076923545449972 1986  9 320                  . 2  1.797265
      10001  9800 "Q"    .03921568766236305 1986 10 321                  . 2  1.797265
      10001  9828 "Q"   .056603774428367615 1986 11 322                  . 2  1.797265
      10001  9861 "Q"   .014999999664723873 1986 12 323                  . 2  1.797265
      10001  9891 "Q"    -.0357142873108387 1987  1 324                  . 2  1.797265
      10001  9919 "Q"   -.07407407462596893 1987  2 325                  . 2  1.797265
      10001  9951 "Q"    .03680000081658363 1987  3 326                  . 2  1.797265
      10001  9981 "Q"   -.03921568766236305 1987  4 327                  . 2  1.797265
      10001 10010 "Q"    -.0714285746216774 1987  5 328                  . 2  1.797265
      10001 10042 "Q"   .051428571343421936 1987  6 329 1.0144155025482178 2 1.7616653
      10001 10073 "Q"   .021276595070958138 1987  7 330 1.0144155025482178 2 1.7616653
      10001 10104 "Q"     .0833333358168602 1987  8 331 1.0144155025482178 2 1.7616653
      10001 10134 "Q"   -.02230769209563732 1987  9 332 1.0144155025482178 2 1.7616653
      10001 10164 "Q"   .019999999552965164 1987 10 333 1.0144155025482178 2 1.7616653
      10001 10195 "Q"  -.029411764815449715 1987 11 334 1.0144155025482178 2 1.7616653
      10001 10226 "Q"   -.03353535383939743 1987 12 335 1.0144155025482178 2 1.7616653
      10001 10255 "Q"    .06382978707551956 1988  1 336 1.0144155025482178 2 1.7616653
      10001 10286 "Q"    .07999999821186066 1988  2 337 1.0144155025482178 2 1.7616653
      10001 10317 "Q"   -.07629629969596863 1988  3 338 1.0144155025482178 2 1.7616653
      10001 10346 "Q"   .030612245202064514 1988  4 339 1.0144155025482178 2 1.7616653
      10001 10378 "Q"   .019801979884505272 1988  5 340 1.0144155025482178 2 1.7616653
      10001 10408 "Q"   -.01203883532434702 1988  6 341 1.2076183557510376 2 1.8245493
      10001 10437 "Q"   .029999999329447746 1988  7 342 1.2076183557510376 2 1.8245493
      10001 10470 "Q"   .029126213863492012 1988  8 343 1.2076183557510376 2 1.8245493
      10001 10500 "Q"  -.021132076159119606 1988  9 344 1.2076183557510376 2 1.8245493
      10001 10531 "Q"    .03921568766236305 1988 10 345 1.2076183557510376 2 1.8245493
      10001 10561 "Q"                     0 1988 11 346 1.2076183557510376 2 1.8245493
      10001 10591 "Q"  -.021132076159119606 1988 12 347 1.2076183557510376 2 1.8245493
      10001 10623 "Q"   .019607843831181526 1989  1 348 1.2076183557510376 2 1.8245493
      10001 10651 "Q"    .03846153989434242 1989  2 349 1.2076183557510376 2 1.8245493
      10001 10682 "Q"   .017777778208255768 1989  3 350 1.2076183557510376 2 1.8245493
      10001 10710 "Q"    .07407407462596893 1989  4 351 1.2076183557510376 2 1.8245493
      10001 10743 "Q"   -.03448275849223137 1989  5 352 1.2076183557510376 2 1.8245493
      10001 10773 "Q"   .017142856493592262 1989  6 353 1.1451923847198486 2 1.9469097
      10001 10804 "Q"     .0357142873108387 1989  7 354 1.1451923847198486 2 1.9469097
      10001 10835 "Q"    .27586206793785095 1989  8 355 1.1451923847198486 2 1.9469097
      10001 10864 "Q"  -.027027027681469917 1989  9 356 1.1451923847198486 2 1.9469097
      10001 10896 "Q"    .07042253762483597 1989 10 357 1.1451923847198486 2 1.9469097
      10001 10926 "Q"    .03947368264198303 1989 11 358 1.1451923847198486 2 1.9469097
      10001 10955 "Q"   .037974681705236435 1989 12 359 1.1451923847198486 2 1.9469097
      10001 10988 "Q"  -.018518518656492233 1990  1 360 1.1451923847198486 2 1.9469097
      10001 11016 "Q"  -.006289307959377766 1990  2 361 1.1451923847198486 2 1.9469097
      10001 11046 "Q"   .012658228166401386 1990  3 362 1.1451923847198486 2 1.9469097
      10001 11077 "Q"                     0 1990  4 363 1.1451923847198486 2 1.9469097
      10001 11108 "Q"  -.012658228166401386 1990  5 364 1.1451923847198486 2 1.9469097
      10001 11137 "Q"   .014102564193308353 1990  6 365  .8181488513946533 2 2.3077965
      10001 11169 "Q"   .025641025975346565 1990  7 366  .8181488513946533 2 2.3077965
      10001 11200 "Q"   -.05000000074505806 1990  8 367  .8181488513946533 2 2.3077965
      10001 11228 "Q"    .04078947380185127 1990  9 368  .8181488513946533 2 2.3077965
      10001 11261 "Q"  -.012820512987673283 1990 10 369  .8181488513946533 2 2.3077965
      10001 11291 "Q"                     0 1990 11 370  .8181488513946533 2 2.3077965
      10001 11322 "Q"    .00129870162345469 1990 12 371  .8181488513946533 2 2.3077965
      10001 11353 "Q"    .01315789483487606 1991  1 372  .8181488513946533 2 2.3077965
      10001 11381 "Q"   .012987012974917889 1991  2 373  .8181488513946533 2 2.3077965
      10001 11409 "Q"  -.011538460850715637 1991  3 374  .8181488513946533 2 2.3077965
      10001 11442 "Q"    .03947368264198303 1991  4 375  .8181488513946533 2 2.3077965
      10001 11473 "Q"                     0 1991  5 376  .8181488513946533 2 2.3077965
      10001 11501 "Q"    .07848101109266281 1991  6 377   .942574679851532 2  2.421834
      10001 11534 "Q"    -.0357142873108387 1991  7 378   .942574679851532 2  2.421834
      10001 11564 "Q"     .1358024626970291 1991  8 379   .942574679851532 2  2.421834
      10001 11595 "Q"   .013043479062616825 1991  9 380   .942574679851532 2  2.421834
      10001 11626 "Q"     .1304347813129425 1991 10 381   .942574679851532 2  2.421834
      10001 11655 "Q"    .13461539149284363 1991 11 382   .942574679851532 2  2.421834
      10001 11687 "Q" -.0067796604707837105 1991 12 383   .942574679851532 2  2.421834
      10001 11718 "Q"    -.0517241396009922 1992  1 384   .942574679851532 2  2.421834
      10001 11746 "Q"   -.20000000298023224 1992  2 385   .942574679851532 2  2.421834
      10001 11778 "Q"    .08181818574666977 1992  3 386   .942574679851532 2  2.421834
      10001 11808 "Q"   .010638297535479069 1992  4 387   .942574679851532 2  2.421834
      10001 11837 "Q"   .010526316240429878 1992  5 388   .942574679851532 2  2.421834
      10001 11869 "Q"  -.007708333432674408 1992  6 389  .6679069995880127 2  2.536174
      10001 11900 "Q"    .06382978707551956 1992  7 390  .6679069995880127 2  2.536174
      10001 11931 "Q"    .03999999910593033 1992  8 391  .6679069995880127 2  2.536174
      end
      format %d date
      Ps: I would be able to perform the first part of my problem, namely generating five BM quintiles using the NYSE breakpoints, but I am not able to double sort and calculate the value-weighted monthly returns on these 25 portfolios from July of year t to June of year t+1.
      For the first part I would use the following code for instance:
      bys datem: astile quintiles_BM = BM, qc(primexch =="N") nq(5)
      Thank you for your availability

      Comment


      • #4
        Regarding your second question, by conditional double sorting I mean that firstly the stocks are sorted in 5 quintiles in accordance with their BM value and secondly each quintile should be sorted in other 5 quintiles in accordance with the marketcap value, generating so 25 quintiles.
        Unfortunately, that doesn't answer my question. Should the marketcap quintile cutoffs be the same in each of the 5 BM quintiles, or does each BM quintile get its own marketcap quintile cutoffs.

        Also #3 has raised another question in my mind: are the marketcap quintiles also calculated using only the primexch == "N" subset?

        Thank you for using -dataex-. But, also, in the end, to write and test this code, I will need an example that also contains observations with primexch == "N" (and enough of them so that I can calculate quintile cutoffs from them.)

        Comment


        • #5
          Dear Clyde,
          unfortunately my dataset contains 3.5 mln observations therefore I cannot post permno with different primexch otherwise I should post more than 1000 observations. I posted a new part of my dataset which contains for the same permno the different variables I explained above.
          Regarding your first question, I thing that there is a misunderstanding... At the beginning stocks should be sorted into 5 BM quintiles using NYSE breakpoints based on BM and not on MarketCap. Regarding your second question, the marketcap quintiles calculated whithin each BM quintile are calculated using the entire sample and not just the "N" subset.
          Anyway if the NYSE breakpoints creates too much confusion, for me the most important things to understand is how to perform a conditional double sorting and how to calculate the value-weighted monthly returns on these 25 portfolios from July of year t to June of year t+1.
          Ps: if the value weighted question creates confusion please do not take it into account.
          Best regards

          [CODE]
          * Example generated by -dataex-. To install: ssc install dataex
          clear
          input double permno long date str2 primexch double(ret shrout altprc year) float(month datem Bm id)
          10952 16009 "N" -.4845636487007141 14431 11.520000457763672 2003 10 525 .15610425 910
          10952 16037 "N" .0217013880610466 14431 11.770000457763672 2003 11 526 .15610425 910
          10952 16070 "N" .05522511526942253 14431 12.420000076293945 2003 12 527 .15610425 910
          10952 16100 "N" .06280191242694855 14431 13.199999809265137 2004 1 528 .15610425 910
          10952 16128 "N" .04166669398546219 14431 13.59000015258789 2004 2 529 .15610425 910
          10952 16161 "N" .04415006935596466 14431 14.1899995803833 2004 3 530 .15610425 910
          10952 16191 "N" -.17899930477142334 16456 11.649999618530273 2004 4 531 .15610425 910
          10952 16219 "N" -.020171677693724632 16456 11.1899995803833 2004 5 532 .15610425 910
          10952 16252 "N" .02680967003107071 16456 11.489999771118164 2004 6 533 -.1704273 910
          10952 16282 "N" .07658834755420685 16456 12.369999885559082 2004 7 534 -.1704273 910
          10952 16314 "N" .11358126997947693 16456 13.550000190734863 2004 8 535 -.1704273 910
          10952 16344 "N" .029520265758037567 16456 13.949999809265137 2004 9 536 -.1704273 910
          10952 16373 "N" -.030107533559203148 16456 13.529999732971191 2004 10 537 -.1704273 910
          10952 16405 "N" .021803447976708412 16456 13.600000381469727 2004 11 538 -.1704273 910
          10952 16436 "N" .051470573991537094 16456 14.300000190734863 2004 12 539 -.1704273 910
          10952 16467 "N" -.03426571935415268 16456 13.8100004196167 2005 1 540 -.1704273 910
          10952 16495 "N" .050325799733400345 16456 14.279999732971191 2005 2 541 -.1704273 910
          10952 16526 "N" -.0028011177200824022 19351 14.239999771118164 2005 3 542 -.1704273 910
          10952 16555 "N" -.030898846685886383 21975 13.800000190734863 2005 4 543 -.1704273 910
          10952 16587 "N" -.0032609030604362488 22341 13.529999732971191 2005 5 544 -.1704273 910
          10952 16617 "N" .0901700109243393 22341 14.75 2005 6 545 -.28477734 910
          10952 16646 "N" .045423734933137894 22341 15.420000076293945 2005 7 546 -.28477734 910
          10952 16679 "N" -.03339817002415657 22348 14.680000305175781 2005 8 547 -.28477734 910
          10952 16709 "N" -.014305179007351398 22348 14.470000267028809 2005 9 548 -.28477734 910
          10952 16740 "N" -.031098810955882072 22348 14.020000457763672 2005 10 549 -.28477734 910
          10952 16770 "N" -.0032097334042191505 28509 13.75 2005 11 550 -.28477734 910
          10952 16800 "N" .02327270433306694 28509 14.069999694824219 2005 12 551 -.28477734 910
          10952 16832 "N" .05117272958159447 28509 14.789999961853027 2006 1 552 -.28477734 910
          10952 16860 "N" .043610554188489914 28509 15.210000038146973 2006 2 553 -.28477734 910
          10952 16891 "N" .04142012447118759 29973 15.84000015258789 2006 3 554 -.28477734 910
          10952 16919 "N" -.06881313771009445 29940 14.75 2006 4 555 -.28477734 910
          10952 16952 "N" .001016946160234511 30492 14.539999961853027 2006 5 556 -.28477734 910
          10952 16982 "N" .012379663065075874 30492 14.720000267028809 2006 6 557 -.28256357 910
          10952 17013 "N" .029891274869441986 30492 15.15999984741211 2006 7 558 -.28256357 910
          10952 17044 "N" .029353579506278038 34423 15.380000114440918 2006 8 559 -.28256357 910
          10952 17073 "N" .051365405321121216 34423 16.170000076293945 2006 9 560 -.28256357 910
          10952 17105 "N" .03339511528611183 34423 16.709999084472656 2006 10 561 -.28256357 910
          10952 17135 "N" .105026975274086 35544 18.239999771118164 2006 11 562 -.28256357 910
          10952 17164 "N" -.1277412325143814 43044 15.90999984741211 2006 12 563 -.28256357 910
          10952 17197 "N" .052796993404626846 43044 16.75 2007 1 564 -.28256357 910
          10952 17225 "N" -.028955169022083282 43044 16.040000915527344 2007 2 565 -.28256357 910
          10952 17255 "N" .009975052438676357 44127 16.200000762939453 2007 3 566 -.28256357 910
          10952 17286 "N" -.014197560958564281 43600 15.970000267028809 2007 4 567 -.28256357 910
          10952 17317 "N" .012836539186537266 44188 15.949999809265137 2007 5 568 -.28256357 910
          10952 17346 "N" -.1003134474158287 44188 14.350000381469727 2007 6 569 -.3363993 910
          10952 17378 "N" -.1240418553352356 44231 12.569999694824219 2007 7 570 -.3363993 910
          10952 17409 "N" .07120127975940704 44231 13.239999771118164 2007 8 571 -.3363993 910
          10952 17437 "N" .028700916096568108 44231 13.619999885559082 2007 9 572 -.3363993 910
          10952 17470 "N" -.05653447285294533 44231 12.850000381469727 2007 10 573 -.3363993 910
          10952 17500 "N" -.07198447734117508 44231 11.699999809265137 2007 11 574 -.3363993 910
          10952 17531 "N" -.12564104795455933 44231 10.229999542236328 2007 12 575 -.3363993 910
          10952 17562 "N" .08602152019739151 44231 11.109999656677246 2008 1 576 -.3363993 910
          10952 17591 "N" .06615664064884186 44231 11.619999885559082 2008 2 577 -.3363993 910
          10952 17622 "N" .005163547117263079 44463 11.680000305175781 2008 3 578 -.3363993 910
          10952 17652 "N" .0702054500579834 44461 12.5 2008 4 579 -.3363993 910
          10952 17682 "N" -.006000015884637833 44461 12.199999809265137 2008 5 580 -.3363993 910
          10952 17713 "N" -.03934422507882118 44461 11.720000267028809 2008 6 581 .03671334 910
          10952 17744 "N" .08873719722032547 44488 12.760000228881836 2008 7 582 .03671334 910
          10952 17773 "N" .044279009103775024 44488 13.100000381469727 2008 8 583 .03671334 910
          10952 17805 "N" .009160296060144901 44488 13.220000267028809 2008 9 584 .03671334 910
          10952 17836 "N" -.2768532335758209 44489 9.5600004196167 2008 10 585 .03671334 910
          10952 17864 "N" -.48483267426490784 44489 4.699999809265137 2008 11 586 .03671334 910
          10952 17897 "N" .5063830018043518 44489 7.079999923706055 2008 12 587 .03671334 910
          10952 17927 "N" -.13418076932430267 44489 6.130000114440918 2009 1 588 .03671334 910
          10952 17955 "N" -.20840135216712952 44489 4.739999771118164 2009 2 589 .03671334 910
          10952 17987 "N" -.6329113841056824 44855 1.7400000095367432 2009 3 590 .03671334 910
          10952 18017 "N" 1.063218355178833 45062 3.5899999141693115 2009 4 591 .03671334 910
          10952 18046 "N" .4373258948326111 45062 5.159999847412109 2009 5 592 .03671334 910
          10952 18078 "N" -.12403098493814468 45062 4.519999980926514 2009 6 593 .32346675 910
          10952 18109 "N" .1747787594795227 45062 5.309999942779541 2009 7 594 .32346675 910
          10952 18140 "N" .21092276275157928 45064 6.429999828338623 2009 8 595 .32346675 910
          10952 18170 "N" .0031104169320315123 45064 6.449999809265137 2009 9 596 .32346675 910
          10952 18200 "N" -.0589146725833416 45084 6.070000171661377 2009 10 597 .32346675 910
          10952 18231 "N" -.004942373838275671 51903 6.039999961853027 2009 11 598 .32346675 910
          10952 18262 "N" .14072851836681366 51903 6.800000190734863 2009 12 599 .32346675 910
          10952 18291 "N" .02647056244313717 51903 6.980000019073486 2010 1 600 .32346675 910
          10952 18319 "N" -.05587390437722206 60711 6.590000152587891 2010 2 601 .32346675 910
          10952 18352 "N" .20030343532562256 62007 7.909999847412109 2010 3 602 .32346675 910
          10952 18382 "N" .006321136839687824 63068 7.960000038146973 2010 4 603 .32346675 910
          10952 18410 "N" -.12562815845012665 64755 6.869999885559082 2010 5 604 .32346675 910
          10952 18443 "N" -.12372633814811707 61223 6.019999980926514 2010 6 605 .24229483 910
          10952 18473 "N" .02990030311048031 65104 6.199999809265137 2010 7 606 .24229483 910
          10952 18505 "N" -.09516126662492752 65728 5.519999980926514 2010 8 607 .24229483 910
          10952 18535 "N" .10144926607608795 65728 6.079999923706055 2010 9 608 .24229483 910
          10952 18564 "N" .0378289520740509 66035 6.309999942779541 2010 10 609 .24229483 910
          10952 18596 "N" -.025356575846672058 66214 6.059999942779541 2010 11 610 .24229483 910
          10952 18627 "N" .03795379772782326 66214 6.289999961853027 2010 12 611 .24229483 910
          10952 18658 "N" -.03815576806664467 66214 6.050000190734863 2011 1 612 .24229483 910
          10952 18686 "N" .016528883948922157 66214 6.059999942779541 2011 2 613 .24229483 910
          10952 18717 "N" -.004950451198965311 66234 6.03000020980835 2011 3 614 .24229483 910
          10952 18746 "N" -.021558890119194984 66234 5.900000095367432 2011 4 615 .24229483 910
          10952 18778 "N" -.08644066005945206 67712 5.300000190734863 2011 5 616 .24229483 910
          10952 18808 "N" -.02830190397799015 67712 5.150000095367432 2011 6 617 .12102018 910
          10952 18837 "N" -.03689321503043175 67712 4.960000038146973 2011 7 618 .12102018 910
          10952 18870 "N" -.23991934955120087 68009 3.680000066757202 2011 8 619 .12102018 910
          10952 18900 "N" -.15489134192466736 68009 3.109999895095825 2011 9 620 .12102018 910
          10952 18931 "N" .1800643801689148 68009 3.6700000762939453 2011 10 621 .12102018 910
          10952 18961 "N" -.06539513915777206 68010 3.3399999141693115 2011 11 622 .12102018 910
          10952 18991 "N" .29041919112205505 68010 4.309999942779541 2011 12 623 .12102018 910
          10952 19023 "N" .15777258574962616 68010 4.989999771118164 2012 1 624 .12102018 910
          end
          format %d date

          Comment


          • #6
            Dear Clyde,
            So far I wrote this code but I am not completely sure if it does make sense..
            // Create Bm quintile portfolios using NYSE breakpoints
            bys datem: astile quintiles_Bm = Bm, qc(primexch =="N") nq(5)
            // Sort each Bm quintile into size quintiles
            egen quintiles_size = xtile(size), by(datem quintiles_Bm) nq(5)
            // Calculate equal weighted return portfolios
            forvalues i = 1(1)5 {
            forvalues j = 1(1)5 {
            egen ew_return_pt_`i'`j' = mean(ret) if quintiles_Bm ==`i' & quintiles_size==`j', by (datem)
            }
            }
            Any help would be really appreciated
            Kind regards

            Comment


            • #7
              I thing that there is a misunderstanding... At the beginning stocks should be sorted into 5 BM quintiles using NYSE breakpoints based on BM and not on MarketCap.
              Yes, and I think the misunderstanding persists. I understood all along that the first quintiling (to coin a word) is based on BM. My question is how, after that, to do the second quintiling on MarketCap. Do I calculate a single set of cutoffs on MarketCap and apply those same cutoffs to all five BM quintiles? Or do I calculate a separate set of cutoffs on MarketCap in each BM quintile?

              Thanks for the second data example. Between the two of them, I should be able to work this out once I have clarity on how the second quintiling is supposed to work.

              Comment


              • #8
                Dear Clyde,
                the second quintiling on MarketCap should be performed calculating a single set of cutoffs on MarketCap and apply these cutoffs to all five BM quintiles.
                Thanks again for your help

                Comment


                • #9
                  Dear Clyde,
                  I got confused... The breakpoints for the second sort variable should be formed within each group of the first sort variable.

                  Comment


                  • #10
                    OK. The code shown below does not work with your example data, because it is too scanty to support creating 25 subsets within each year. Also, you ask for value weighted averages for each year from July 1 to June 30: but the value changes within those time periods in your data. In your data it appears that the year over which those numbers hold constant is from June 1 to May 31--so the code below relies on that instead. I believe that in your full data, this will do the "double-sorting" you require.

                    To run this code you need to install both -egenmore- (by Nick Cox) and -runby- (by Robert Picard and me). Both of these are available from SSC.

                    Code:
                    //    PROGRAM TO DOUBLE-SORT THE DATA FOR A SINGLE FISCAL YEAR
                    capture program drop one_fyear
                    program define one_fyear
                        //    IDENTIFY QUINTILE CUTOFFS OF bm AMONG OBSERVATIONS WITH primexch == "N"
                        gen cutoffs = .
                        _pctile bm if primexch == "N", nq(5)
                        forvalues i = 1/4 {
                            replace cutoffs = r(r`i') in `i'
                        }
                        display `"`cutoffs'"'
                        //    APPLY THOSE TO MAKE "QUINTILES" OF BM FOR ALL OBSERVATIONS
                        xtile bm_quintile = bm, cutpoints(cutoffs)
                        drop cutoffs
                    
                        //    NOW, WOITHIN EACH BM QUINTILE, CRAETE QUINTILES OF MARKETCAP 
                        by bm_quintile, sort: egen mc_quintile = xtile(marketcap), nq(5)
                    
                        tab *_quintile
                        exit
                    end
                    
                    //    IDENTIFY FISCAL YEARS FROM JUNE 1 TO MAY 31
                    gen fyear = year(dofm(mofd(date) + 7))
                    
                    //    DOUBLE SORT EACH FISCAL YEAR
                    runby one_fyear, by(fyear) status
                    As this code does not produce usable results from the example data, I'm not going to proceed to the weighted average return at this point. I suggest you first verify that this works in your real data. Then, if you want help with that, you can post a new data example from the results of this code. The new data should consist of all observations from several different portfolios (where a portfolio means all the observations having a given combination of values of fyear, bm_quintile, and mc_quintile). From that, I should be able to give you code for the weighted average returns.


                    Comment


                    • #11
                      Dear Clyde,
                      when I run the first part of your code, namely:
                      \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\\\\\\
                      gen cutoffs = .
                      _pctile Bm if primexch == "N", nq(5)
                      forvalues i = 1/4 {
                      replace cutoffs = r(r`i') in `i'
                      }
                      xtile Bm_quintile = Bm, cutpoints(cutoffs)
                      \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ \\\\\\\\\\\\\\\\
                      only four values are generated. Moreover, when i run the code:
                      runby one_fyear, by(fyear) status
                      my data disappear and the output of the previous code is:
                      Number of by-groups = 47
                      by-groups with errors = 47
                      by-groups with no data = 0
                      Observations processed = 3,360,645
                      Observations saved = 0
                      ************************************************** ********
                      However I noticed that the results generated by your code are similar to the results generated by the code I posted above, namely:
                      bys datem: astile quintiles_Bm = Bm, qc(primexch =="N") nq(5)
                      egen quintiles_size = xtile(size), by(datem quintiles_Bm) nq(5)
                      Moreover I would like to know the difference between running the previous code and the following one:
                      bys datem: astile quintiles_Bm = Bm, qc(primexch =="N") nq(5)
                      egen quintiles_size = xtile(size), by(datem) nq(5)
                      ************************************************** ********
                      Anyway I poste the dataset I end up with your code
                      clear
                      input double permno long date str2 primexch double(ret year) float(month datem Bm id size) byte Bm_quintile float(mc_quintile fyear)
                      93436 19205 "Q" -.12368170917034149 2012 7 630 -2.590342 28586 8.10033 1 5 2013
                      93436 19173 "Q" .06067799776792526 2012 6 629 -2.590342 28586 8.10033 1 5 2013
                      93436 19082 "Q" .1146363914012909 2012 3 626 -2.5019956 28586 8.015977 1 5 2012
                      81689 14028 "N" -.25732898712158203 1998 5 460 -1.6358727 18857 6.162085 1 4 1998
                      93436 18991 "Q" -.12767262756824493 2011 12 623 -2.5019956 28586 8.015977 1 5 2012
                      77279 19082 "Q" .0967620387673378 2012 3 626 -1.276634 14949 6.429663 1 4 2012
                      12209 17773 "Q" -.15332835912704468 2008 8 583 -1.9785155 2110 5.293499 1 3 2009
                      76699 15917 "Q" 0 2003 7 522 -3.509296 14396 4.5539346 1 2 2004
                      11152 10500 "Q" .4117647111415863 1988 9 344 -1.1104232 1106 .57191485 1 1 1989
                      78881 14791 "Q" -.3239436745643616 2000 6 485 -2.1404061 16310 5.410378 1 3 2001
                      10180 13055 "Q" -.04878048598766327 1995 9 428 -1.3491285 174 3.566694 1 2 1996
                      11757 12508 "Q" -.2432432472705841 1994 3 410 -1.2238065 1681 3.614856 1 2 1994
                      40556 8308 "Q" .1875 1982 9 272 -1.2861203 7241 2.0710707 1 1 1983
                      93433 19113 "Q" -.05909096077084541 2012 4 627 -1.696732 28583 5.882883 1 4 2012
                      92874 18109 "Q" -.009645108133554459 2009 7 594 -1.0176274 28034 3.256295 1 1 2010
                      93433 18900 "Q" -.2746780514717102 2011 9 620 -1.696732 28583 5.882883 1 4 2012
                      67600 9070 "Q" -.1149425283074379 1984 10 297 -1.7741205 11838 6.143852 1 4 1985
                      75961 12053 "Q" -.0833333358168602 1992 12 395 -1.4093645 13690 3.6001165 1 2 1993
                      43078 3652 "A" .05000000074505806 1969 12 119 -1.0942332 7643 3.454383 1 2 1970
                      80674 13664 "Q" .3125 1997 5 448 -1.54013 18053 3.135494 1 1 1997
                      49197 11291 "A" .15000000596046448 1990 11 370 -.9601184 8677 4.2583323 1 2 1991
                      47837 4017 "N" .019761905074119568 1970 12 131 -3.016015 8450 4.922381 1 3 1971
                      29824 8490 "Q" .6666666865348816 1983 3 278 -3.0917795 5634 1.607442 1 1 1983
                      81740 19023 "Q" .08384817093610764 2012 1 624 -2.214674 18907 6.3473 1 4 2012
                      76901 12569 "Q" -.40625 1994 5 412 -2.981456 14589 4.2124276 1 2 1994
                      80001 13726 "Q" -.2916666567325592 1997 7 450 -1.382512 17395 3.0304234 1 1 1998
                      56427 10226 "Q" .20000000298023224 1987 12 335 -1.8399328 9928 1.137793 1 1 1988
                      93429 19358 "Q" .007671765983104706 2012 12 635 -2.1869037 28579 7.78974 1 5 2013
                      93429 19327 "Q" .0223956648260355 2012 11 634 -2.1869037 28579 7.78974 1 5 2013
                      93429 19264 "Q" .03375524282455444 2012 9 632 -2.1869037 28579 7.78974 1 5 2013
                      93429 19236 "Q" .0031579136848449707 2012 8 631 -2.1869037 28579 7.78974 1 5 2013
                      15560 9009 "N" .07943925261497498 1984 8 295 -1.3796308 3601 7.7899 1 5 1985
                      93429 19144 "Q" -.040469005703926086 2012 5 628 -1.7965212 28579 7.708328 1 5 2012
                      69084 12110 "Q" -.3030303120613098 1993 2 397 -2.928515 12086 2.79308 1 1 1993
                      89859 17073 "Q" .06489668786525726 2006 9 560 -1.764533 25150 4.593248 1 3 2007
                      93429 19023 "Q" -.010440852493047714 2012 1 624 -1.7965212 28579 7.708328 1 5 2012
                      93429 18991 "Q" -.0393759086728096 2011 12 623 -1.7965212 28579 7.708328 1 5 2012
                      93429 18808 "Q" -.06155012547969818 2011 6 617 -1.7965212 28579 7.708328 1 5 2012
                      60804 10226 "N" .10346154123544693 1987 12 335 -1.1601756 10667 6.114049 1 4 1988
                      58318 14301 "N" .04060913622379303 1999 2 469 -2.086643 10252 6.380038 1 4 1999
                      93428 19264 "Q" .1328362673521042 2012 9 632 -1.808187 28578 6.682734 1 4 2013
                      93428 19173 "Q" .06005131080746651 2012 6 629 -1.808187 28578 6.682734 1 4 2013
                      93428 19023 "Q" -.07781457901000977 2012 1 624 -2.448166 28578 6.932719 1 4 2012
                      93428 18991 "Q" -.13911062479019165 2011 12 623 -2.448166 28578 6.932719 1 4 2012
                      93428 18837 "Q" -.23393657803535461 2011 7 618 -2.448166 28578 6.932719 1 4 2012
                      23472 14640 "Q" -.03546099364757538 2000 1 480 -1.517959 4689 2.261138 1 1 2000
                      81639 14364 "Q" .18649046123027802 1999 4 471 -1.5217146 18808 6.510349 1 4 1999
                      79629 11077 "Q" 1.3478261232376099 1990 4 363 -1.3015076 17034 1.3890406 1 1 1990
                      89203 17955 "Q" -.14742857217788696 2009 2 589 -2.2469778 24560 6.525983 1 4 2009
                      93427 19144 "N" -.3067776560783386 2012 5 628 -1.140038 28577 6.721264 1 4 2012
                      77393 13755 "Q" -.0260869562625885 1997 8 451 -3.329383 15057 4.6605954 1 3 1998
                      76532 16891 "Q" .03803808242082596 2006 3 554 -1.2483436 14236 5.848224 1 4 2006
                      75758 11687 "Q" -.095238097012043 1991 12 383 -1.79942 13502 3.03116 1 1 1992
                      75228 14700 "A" .19780220091342926 2000 3 482 -1.161711 13009 7.337099 1 5 2000
                      93427 18870 "N" .0707792341709137 2011 8 619 -1.140038 28577 6.721264 1 4 2012
                      13901 11595 "N" -.01989882066845894 1991 9 380 -1.1848491 3405 10.98215 1 5 1992
                      83919 9555 "Q" -.1551724076271057 1986 2 313 -2.494105 20401 1.8113782 1 1 1986
                      77249 12814 "Q" -.2800000011920929 1995 1 420 -1.7773793 14919 3.6675186 1 2 1995
                      10545 10955 "A" -.029629629105329514 1989 12 359 -1.2643592 525 5.534162 1 3 1990
                      10860 10623 "Q" .22033898532390594 1989 1 348 -1.7738762 824 3.587891 1 2 1989
                      80482 14669 "Q" -.050847455859184265 2000 2 481 -1.9701067 17868 4.977998 1 3 2000
                      78881 16919 "Q" -.10011640936136246 2006 4 555 -1.8792397 16310 6.987423 1 4 2006
                      78481 11869 "Q" -.1428571492433548 1992 6 389 -1.0148158 15993 2.44454 1 1 1993
                      86731 15095 "Q" .0385965034365654 2001 4 495 -2.3609517 22645 5.261762 1 3 2001
                      77699 12873 "Q" .0117647061124444 1995 3 422 -1.299893 15344 3.714008 1 2 1995
                      37584 10804 "N" .16296295821666718 1989 7 354 -1.3393122 6798 6.781605 1 4 1990
                      77512 15644 "Q" -.053585704416036606 2002 10 513 -1.471688 15170 4.613451 1 3 2003
                      54164 12508 "N" .0043290043249726295 1994 3 410 -1.3814508 9536 6.522484 1 4 1994
                      36054 6299 "N" -.04054053872823715 1977 3 206 -.9597689 6566 4.894284 1 3 1977
                      92034 18078 "Q" .1641274243593216 2009 6 593 -1.2139492 27233 6.544627 1 4 2010
                      93420 19236 "N" .12032084167003632 2012 8 631 -1.3079185 28570 7.719254 1 5 2013
                      77338 17987 "N" .026602232828736305 2009 3 590 -1.0141762 15006 8.681023 1 5 2009
                      36054 5782 "N" -.1259259283542633 1975 10 189 -1.1486716 6566 5.428483 1 3 1976
                      84114 15825 "Q" .1805555373430252 2003 4 519 -1.4046717 20590 4.690278 1 3 2003
                      75101 12661 "N" .11326860636472702 1994 8 415 -1.0980935 12886 5.731028 1 3 1995
                      77242 12873 "N" -.2368421107530594 1995 3 422 -1.4521945 14914 5.457863 1 3 1995
                      44813 11869 "Q" -.13432836532592773 1992 6 389 -4.824164 7941 4.3201594 1 2 1993
                      79702 15187 "A" .20930223166942596 2001 7 498 -1.6726414 17105 3.8670006 1 2 2002
                      74916 9373 "Q" -.11363636702299118 1985 8 307 -1.6855196 12777 1.3394648 1 1 1986
                      76025 12782 "Q" -.1428571492433548 1994 12 419 -1.2276478 13749 3.471578 1 2 1995
                      75316 17652 "N" -.041853997856378555 2008 4 579 -1.87894 13095 8.020002 1 5 2008
                      93418 19264 "N" .09326842427253723 2012 9 632 -2.121739 28568 6.506363 1 4 2013
                      64215 11961 "A" .035087719559669495 1992 9 392 -1.6572554 11255 2.832434 1 1 1993
                      57155 9070 "Q" .005586592014878988 1984 10 297 -1.644126 10055 6.228445 1 4 1985
                      93418 19113 "N" .054849542677402496 2012 4 627 -2.5723646 28568 6.97338 1 4 2012
                      75700 12661 "Q" .4057970941066742 1994 8 415 -1.6103483 13447 5.45825 1 3 1995
                      47394 4074 "N" .08936170488595963 1971 2 133 -1.2928146 8374 3.3989365 1 2 1971
                      24459 15917 "N" -.008070850744843483 2003 7 522 -1.1742835 4827 9.104591 1 5 2004
                      87601 17713 "Q" -.19583338499069214 2008 6 581 -1.1958925 23458 4.816669 1 3 2009
                      92454 18658 "Q" .04709143564105034 2011 1 612 -1.1387447 27623 5.918148 1 4 2011
                      10104 14546 "Q" .04532967135310173 1999 10 477 -2.3798146 100 10.87096 1 5 2000
                      55212 14973 "N" .02244536392390728 2000 12 491 -1.0580596 9720 8.244435 1 5 2001
                      80237 13453 "Q" .042553190141916275 1996 10 441 -2.5317745 17625 4.447683 1 2 1997
                      90280 17437 "Q" .08646617084741592 2007 9 572 -1.233163 25539 5.803495 1 4 2008
                      59707 7486 "N" .002421307610347867 1980 6 245 -1.1938968 10483 6.059059 1 4 1981
                      43721 4228 "N" -.05489259958267212 1971 7 138 -1.925241 7754 6.959348 1 4 1972
                      73139 13179 "Q" .06428571790456772 1996 1 432 -1.5943383 12630 7.528248 1 5 1996
                      77421 13634 "Q" -.04848484694957733 1997 4 447 -1.1100986 15082 7.00187 1 4 1997
                      79850 16860 "Q" .05725696682929993 2006 2 553 -2.1632133 17247 5.884294 1 4 2006
                      10628 11808 "Q" -.1944444477558136 1992 4 387 -1.7247237 604 2.667072 1 1 1992
                      end
                      format %d date

                      I did not run the code: "runby one_fyear, by(fyear) status", otherwise as I already explained before, my data disappear.
                      Thanks a lot four your availability
                      Kind regards

                      Comment


                      • #12
                        So, I'm not sure what you're doing with the code I posted. What you referred to as the "first part" of the code is not stand-alone code that can be run by itself. It is part of a program, and the program itself only makes sense if used with -runby-.

                        That said, I think that probably there are complications in your data that the code is not set up to handle. But I don't know what those might be. Here is some code that begins by creating a toy data set with a lot of random values for bm, marketcap, and return. As you can see the code works correctly applied to this data set. (Try it.)
                        Code:
                        clear*
                        //    CREATE A TOY DATA SET TO TEST THE CODE
                        set obs 200 // PERMNOs
                        gen long permno = _n
                        set seed 1234
                        gen primexch = cond(runiform() < 0.25, "N", "OTHER")
                        
                        //    EXPAND FOR 10 YEARS
                        expand 120
                        by permno, sort: gen mdate = tm(1994m5) + _n
                        format mdate %tm 
                        gen date = dofm(mdate)
                        format date %td
                        gen long fyear = year(dofm(mdate + 7))
                        
                        by permno fyear (date), sort: gen bm = runiform() if _n == 1
                        by permno fyear (date): gen marketcap = rgamma(2, 5) if _n == 1
                        by permno fyear (date): replace bm = bm[1]
                        by permno fyear (date): replace marketcap = marketcap[1]
                        gen ret = runiform(-.5, 3.5)
                        drop fyear
                        
                        //    DATA SET NOW IN MEMORY
                        
                        //    NOW APPLY THE CODE
                        //    PROGRAM TO DOUBLE-SORT THE DATA FOR A SINGLE FISCAL YEAR
                        capture program drop one_fyear
                        program define one_fyear
                            //    IDENTIFY QUINTILE CUTOFFS OF bm AMONG OBSERVATIONS WITH primexch == "N"
                            gen cutoffs = .
                            _pctile bm if primexch == "N", nq(5)
                            forvalues i = 1/4 {
                                replace cutoffs = r(r`i') in `i'
                            }
                            display `"`cutoffs'"'
                            //    APPLY THOSE TO MAKE "QUINTILES" OF BM FOR ALL OBSERVATIONS
                            xtile bm_quintile = bm, cutpoints(cutoffs)
                            drop cutoffs
                        
                            //    NOW, WOITHIN EACH BM QUINTILE, CRAETE QUINTILES OF MARKETCAP 
                            by bm_quintile, sort: egen mc_quintile = xtile(marketcap), nq(5)
                        
                            tab *_quintile
                            exit
                        end
                        
                        //    IDENTIFY FISCAL YEARS FROM JUNE 1 TO MAY 31
                        gen fyear = year(dofm(mofd(date) + 7))
                        
                        //    DOUBLE SORT EACH FISCAL YEAR
                        runby one_fyear, by(fyear) status
                        If you look at the results you will see that all of the data is properly retained, and that the data is appropriately partitioned into quintiles within quintiles within years (defined as going from June to the following May) as requested. So the question is what is different about your data.

                        I suggest that you add the -verbose- option to the -runby- command, and run it on a relatively small subset of your data. Make sure that subset contains complete data on a moderate number of different permno's (at least 100) both from primexch == "N" and otherwise. (So at least 200 different permnos). When you run the code with the -verbose- option added to the -runby- command, you will get output from each run of program one_fyear, including error messages. That will give us some clue as to where the code is failing. It may be that in your real data there are numerous situations where there are not enough distinct values of bm and marketcap to actually create 5 quintiles.

                        Comment


                        • #13
                          Dear Clyde,
                          maybe I found a way to perform the conditional double sorting, namely firstly sorting on BM and then whithin each BM quintile we sort on size.
                          I think that this code should work:
                          by id: astile Portfolios_BM=BM,qc(primexch =="N") nq(5)
                          by id: astile Portfolios_Size1=size if Portfolios_BM==1, nq(5)
                          by id: astile Portfolios_Size2=size if Portfolios_BM==2, nq(5)
                          by id: astile Portfolios_Size3=size if Portfolios_BM==3, nq(5)
                          by id: astile Portfolios_Size4=size if Portfolios_BM==4, nq(5)
                          by id: astile Portfolios_Size5=size if Portfolios_BM==5, nq(5)
                          forvalues i = 1(1)5 {
                          forvalues j = 1(1)5 {
                          forvalues k = 1(1)5 {
                          forvalues l = 1(1)5 {
                          forvalues m = 1(1)5 {
                          egen ew_return_ds_`i'`j'`k'`l'`m' = mean(ret) if Portfolios_Size1==`i' & Portfolios_Size2==`j' & Portfolios_Size3==`k' & Portfolios_Size4==`l' & Portfolios_Size5==`m', by (datem)
                          }
                          }
                          }
                          }
                          }
                          Moreover the code I posted above would allow us to calculate the 25 equally weighted portfolios.
                          Do you think that it could work?
                          Best regards

                          Comment


                          • #14
                            I am not an -astile- user (it's a user written program I haven't installed). So I don't know exactly what it does in situations like this. On its face, the code you wrote looks like it will compute separate size quintiles within the BM quintiles as you wanted. But I question whether it will work any differently from my code which, also on its face, does that. I believe that the problem you had with my code arises from an incompatibility between your data and the quintiles-within-quintiles approach. That problem arose because some of the combinations of year and BM quintile simply did not contain enough distinct values of size to create 5 quintiles. I don't know how -astile- handles that situation. In my code, you end up with fewer than 5 groups. It may be that -astile- will partition the data into the 5 requested groups by applying some tie-breaking approach--which gives you the appearance of what you want, except that they aren't really quintiles. Maybe that's OK for your purposes: it's your project so you get to decide that.

                            Anyway, let's assume that your -astile- commands give you what you want. The rest of the code looks problematic to me. First of all, it does not calculate a weighted average return, just a simple average return. Again, it's your project, so you can decide whether that's acceptable or not. But the five nested loops are unnecessarily cumbersome and, I might add, will be extremely slow in a large data set. Also, I don't think it will even give you any output, because I don't think that the -if- condition is ever met. It seems to me based on -astile- commands that, for example Portfolios_Size1 is, in fact, always missing unless Portfolio_BM == 1. Similarly for 2 through 5. Consequently all but one of the Portfolio_Size variables is always missing, and the -if- condition is never satisfied. What you really need is to have a single Portfolio_Size variable for all the Portfolio_BM values. You can create that, and then you can get the mean return with a single command.
                            Code:
                            gen Portfolio_Size = .
                            forvalues i = 1/5 {
                                replace  Portfolio_Size = Portfolio_size`i' if Portfolios_BM == `i'
                            }
                            
                            by Portfolios_BM Portfolios_Size datem, sort: egen ew_return_ds = mean(ret)

                            Comment


                            • #15
                              Dear Clyde,
                              You are right, in fact when I tried to run the code to calculate the equallt weighted return portfolios I did not get the results even after 3 hours..
                              At the end I tried the following code and it seems to work:
                              bys datem: astile BMquintile = BM, qc(primexch =="N") nq(5)
                              forvalues i = 1(1)5{
                              bys datem: astile SizeBM`i' = size if BMquintile == `i', nq(5)
                              }
                              forvalues i = 1(1)5{
                              forvalues j = 1(1)5{
                              egen ew_return_bmsize`i'_`j' = mean(ret) if SizeBM`i'==`j' & BMquintile==`i', by (datem)
                              }
                              }
                              Regarding the choice to calculate equal and not weighted return portfolios, it is due to the fact that I am not able to calculate the weighted return portfolios.

                              Comment

                              Working...
                              X