Quintile Portfolios - xtile

[Dustin Miller]

Hi Statalisters

I have data which looks as follows

QUARTER STOCK VAR1 MARKETCAP
2010Q1 APL2 0.5 100
2010Q1 AI1G 0.2 50
2010Q1 MS3T 0.1 20
2010Q1 ABCD 0.05 10
... ...... ..... ...



I would like to create quintile portfolios per quarter based on VAR1.
This is easily done with the -xtile- command (user written by UIi Kohler )

Now, it would like to create quintile portfolios per quarter based on VAR1, however, every quintile portfolio based on VAR1 should have an equal amount of market capitalization.
E.g. Portfolio 1 has stocks with the lowest VAR1 values and a fifth of the market capitlization of all stocks in quarter 2010Q1. Portfolio 5 has stocks with the highest values of VAR1 and a fifth of the market capitlization of all stocks in quarter 2010Q1.

much appreciated

[Clyde Schechter]

Your requirements seem inconsistent: the lowest 20% of the VAR1 values do not necessarily (and generally will not) add up to 1/5th of the market capitalization of all stocks. I don't see how you can satisfy both constraints at once unless there is something very unusual about your data. More likely, I just don't understand what you want to do. Can you show a hand-worked example?

[Dustin Miller]

HI Clyde,

thanks for your quick answer and many apologies for the misunderstanding. Each portfolio should have the same market capitalization. Therefore the market capitalization of each portfolio is = total market cap per quarter / 5. Additionally, portfolio 1 should have the lowest values of VAR1 and a market cap of one fifth, whereas portfolio 5 should have the highest values of VAR1 and also one fifth of the market cap. Accordingly, it is highly likely that portoflios will have varying number of stocks, but by definiton the same market cap

[Nick Cox]

Just to correct a minor confusion: xtile as a command is official. xtile() as an extra egen function is indeed written by Ulrich Kohler and included in egenmore (SSC).

If I understand this correctly, it falls easily to an assault from first principles:

 
. sysuse auto, clear
(1978 Automobile Data)

. bysort foreign (weight) : gen double quintile = sum(weight)

. bysort foreign :  replace quintile = ceil(5 * quintile/quintile[_N])
(74 real changes made)

. l foreign weight quintile , sepby(foreign)

     +------------------------------+
     |  foreign   weight   quintile |
     |------------------------------|
  1. | Domestic    1,800          1 |
  2. | Domestic    1,800          1 |
  3. | Domestic    2,110          1 |
  4. | Domestic    2,120          1 |
  5. | Domestic    2,200          1 |
  6. | Domestic    2,230          1 |
  7. | Domestic    2,520          1 |
  8. | Domestic    2,580          1 |
  9. | Domestic    2,640          1 |
 10. | Domestic    2,650          1 |
 11. | Domestic    2,690          1 |
 12. | Domestic    2,730          1 |
 13. | Domestic    2,750          1 |
 14. | Domestic    2,830          1 |
 15. | Domestic    2,930          2 |
 16. | Domestic    3,180          2 |
 17. | Domestic    3,200          2 |
 18. | Domestic    3,210          2 |
 19. | Domestic    3,220          2 |
 20. | Domestic    3,250          2 |
 21. | Domestic    3,260          2 |
 22. | Domestic    3,280          2 |
 23. | Domestic    3,300          2 |
 24. | Domestic    3,310          2 |
 25. | Domestic    3,330          3 |
 26. | Domestic    3,350          3 |
 27. | Domestic    3,370          3 |
 28. | Domestic    3,370          3 |
 29. | Domestic    3,400          3 |
 30. | Domestic    3,420          3 |
 31. | Domestic    3,430          3 |
 32. | Domestic    3,470          3 |
 33. | Domestic    3,600          3 |
 34. | Domestic    3,600          3 |
 35. | Domestic    3,670          4 |
 36. | Domestic    3,690          4 |
 37. | Domestic    3,690          4 |
 38. | Domestic    3,700          4 |
 39. | Domestic    3,720          4 |
 40. | Domestic    3,740          4 |
 41. | Domestic    3,830          4 |
 42. | Domestic    3,880          4 |
 43. | Domestic    3,900          4 |
 44. | Domestic    4,030          4 |
 45. | Domestic    4,060          5 |
 46. | Domestic    4,060          5 |
 47. | Domestic    4,080          5 |
 48. | Domestic    4,130          5 |
 49. | Domestic    4,290          5 |
 50. | Domestic    4,330          5 |
 51. | Domestic    4,720          5 |
 52. | Domestic    4,840          5 |
     |------------------------------|
 53. |  Foreign    1,760          1 |
 54. |  Foreign    1,830          1 |
 55. |  Foreign    1,930          1 |
 56. |  Foreign    1,980          1 |
 57. |  Foreign    1,990          1 |
 58. |  Foreign    2,020          2 |
 59. |  Foreign    2,040          2 |
 60. |  Foreign    2,050          2 |
 61. |  Foreign    2,070          2 |
 62. |  Foreign    2,130          2 |
 63. |  Foreign    2,160          3 |
 64. |  Foreign    2,200          3 |
 65. |  Foreign    2,240          3 |
 66. |  Foreign    2,280          3 |
 67. |  Foreign    2,370          4 |
 68. |  Foreign    2,410          4 |
 69. |  Foreign    2,650          4 |
 70. |  Foreign    2,670          4 |
 71. |  Foreign    2,750          5 |
 72. |  Foreign    2,830          5 |
 73. |  Foreign    3,170          5 |
 74. |  Foreign    3,420          5 |
     +------------------------------+

. tab foreign quintile 

           |                        quintile
  Car type |         1          2          3          4          5 |     Total
-----------+-------------------------------------------------------+----------
  Domestic |        14         10         10         10          8 |        52 
   Foreign |         5          5          4          4          4 |        22 
-----------+-------------------------------------------------------+----------
     Total |        19         15         14         14         12 |        74

[Clyde Schechter]

OK. So I think this will get you started in the right direction. (I'm assuming you want to do this within each quarter)

// GENERATE SOME RANDOM NUMBERS TO BREAK TIES IF ANY OCCUR
set seed 1234
gen double shuffle1 = runiform()
gen double shuffle2 = runiform()

//  ORDER THE OBSERVATIONS IN INCREASING VALUES OF VAR1
//  AND TALLY THE RUNNING TOTAL OF MARKET CAPITALIZATION
by quarter (VAR1 shuffle1 shuffle2), sort: gen running_mkt_cap = sum(market_capitalization)
by quarter (VAR1 shuffle1 shuffle2): replace running_mkt_cap  = running_mkt_cap/running_mkt_cap[_N]

//  NOW SPLIT INTO PORTFOLIOS AT 0.2, 0.4, 0.6, AND 0.8 OF TOTAL MARKET CAP, OR AS CLOSE
//  TO THAT AS WE CAN COME
gen portfolio = 1 if running_mkt_cap <= 0.2
replace portfolio = 2 if running_mkt_cap > 0.2 & running_mkt_cap <= 0.4
replace portfolio = 3 if running_mkt_cap > 0.4 & running_mkt_cap <= 0.6
replace portfolio = 4 if running_mkt_cap > 0.6 & running_mkt_cap <= 0.8
replace portfolio = 5 if running_mkt_cap > 0.8
drop shuffle1 shuffle2

Note: I don't know how "lumpy" the market capitalization variable is. It may be that there are some companies with huge amounts of market capitalizations that cause running_mkt_cap to jump abruptly from say 0.15 to 0.30. If that sort of thing happens, I don't see a way to split into groups with essentially equal amounts of market capitalization. But if there are no terribly abrupt jumps like that, the above should be reasonably close to your need. You might want to tinker with the choice of > and <= to even things out better, depending on how the market capitalization variables falls out.

[Nick Cox]

Readers will note that Clyde's proposal and mine are identical in essence.

Note further that the direction of cumulation is a matter of convention. So you can reverse it and so if you get better results according to some other criterion.

 
. gsort foreign -weight 

. by foreign : gen double quintile2 = sum(weight) 

. by foreign : replace quintile2 = 6 - ceil(5 * quintile2/quintile2[_N])
(74 real changes made)

. 
. by foreign : tab quintile* 

-----------------------------------------------------------------------------------------
-> foreign = Domestic

           |                       quintile2
  quintile |         1          2          3          4          5 |     Total
-----------+-------------------------------------------------------+----------
         1 |        14          0          0          0          0 |        14 
         2 |         1          9          0          0          0 |        10 
         3 |         0          1          9          0          0 |        10 
         4 |         0          0          1          9          0 |        10 
         5 |         0          0          0          1          7 |         8 
-----------+-------------------------------------------------------+----------
     Total |        15         10         10         10          7 |        52 


-----------------------------------------------------------------------------------------
-> foreign = Foreign

           |                       quintile2
  quintile |         1          2          3          4          5 |     Total
-----------+-------------------------------------------------------+----------
         1 |         5          0          0          0          0 |         5 
         2 |         1          4          0          0          0 |         5 
         3 |         0          1          3          0          0 |         4 
         4 |         0          0          1          3          0 |         4 
         5 |         0          0          0          1          3 |         4 
-----------+-------------------------------------------------------+----------
     Total |         6          5          4          4          3 |        22