Quintile Sorting

[Patricia Benker]

Hello I have a dataset of investment funds and want to divide the variable Turnover into 5 quintiles (the values range from 0 to 2.85) and should represent high or low activity, then I want to calculate the average Turnover of each fund (about 2 million in total) with a certain Portfolioid (e.g. 4)
and then divide the funds into the quintiles (numbers and percentages)
Since this is the first time in my life that I am working with Stata, I would be very grateful for any help.


Greetings

Patricia

[Nick Cox]

This is a common kind of problem here (hundreds of posts on it) and there are many solutions. But I am guessing that your data have more structure than you are telling us. People who want to do this usually have a time series for each fund.

As you're new here, and welcome, I guess you read through the FAQ Advice as requested. Please look again at #12 in https://www.statalist.org/forums/help and give us a data example using dataex or (what may be simpler) phrase your question in terms of a standard dataset such as

webuse grunfeld, clear

where all you need to do is imagine that invest is your Turnover and tell us how binning should be done with such data.

[Ali Atia]

To separate your Turnover variable into quintiles, you can use:

xtile quintile=Turnover, n(5)

To calculate averages by Portfolioid you can use:

sum Turnover if Portfolioid==4

[Patricia Benker]

I try to explain it in more detail with the grunfeld dataset.

I need a command that divides the values of invest into 5 quintiles, taking all companies into account. So in this case 5 quintiles from the values 0.93 to 1486.7.

This would be the first important step.

If I use the command
xtile quintile=Turnover, n(5) and then tabstat, it says too many values. The other code sum Turnover if Portfolioid==4 I did alreay use it but I need a kind of loop where i can do this with all funds that have the same portfolioid.

In grunfeld dataset, I need a loop that calculates the average of invest for all companies with the number 1 and then with the number 2 and so on and then divides this into the previously defined invest quintiles.

[Nick Cox]

No loop is needed. This works for me.

. webuse grunfeld, clear

. xtile quintile=invest, nq(5)

. tabstat invest, by(quintile) s(mean)

Summary for variables: invest
     by categories of: quintile (5 quantiles of invest)

quintile |      mean
---------+----------
       1 |    13.813
       2 |  39.73725
       3 |  58.69475
       4 |  108.2988
       5 |  509.2475
---------+----------
   Total |  145.9583
--------------------

You don't give the details of your tabstat command that didn't work, so I am reduced to a wild guess that you got your variables the wrong way round.

[Patricia Benker]

Thank you. It worked out!!