How to sort multiple rows in quintiles and calculate portfolio returns?

I have two excel files of stock market data. First includes daily price multiples and the second daily stock returns. Companies are in the same order in the both files. I have 3073 days and 684 companies, thus excel files are 3074x685 (the first row is company name and the fist column is date).

For every day/row, I need to sort companies in 5 equal size portfolios based on their price multiples and calculate those next day average returns.

Results should be documented something as following. Q1 is the portfolio with the lowest multiples and so on:

           Q1     Q2     Q3      Q4     Q5
1.1.2017  1.0%   2.0%   1.1%    0.4%   0.8%
2.1.2017  3.0%   1.1%   1.25%   1.5%   0.27%
...

Is there a code to do that? Would take ages to sort one by one manually.

Answer 1

[Paul Shoemaker]

Check out the prctile function (Statistics toolbox). You can do something like this:

    returnQuintiles = prctile(returns,[20 40 60 80]);

Then you could get all the values in each quintile:

    quintile1 = returns(returns<returnedQuintiles(1));
    quintile2 = returns(returns>=returnedQuintiles(1) & returns<returnedQuintiles(2));
    quintile3 = returns(returns>=returnedQuintiles(2) & returns<returnedQuintiles(3));
    quintile4 = returns(returns>=returnedQuintiles(3) & returns<returnedQuintiles(4));
    quintile5 = returns(returns>=returnedQuintiles(4));

If you need the indexes of each quintile so that you can grab other information, such as date and ticker, from the corresponding row, just save the index values first with a minor variation on the above, perhaps something like this:

quintile1idx = returns<returnedQuintiles(1);
quintile1 = returns(quintile1Idx);
tickersq1 = tickers(quintile1Idx);
dateq1 = tickers(quintile1Idx);
....

Note that it's possible to not have exactly an even number of cases in each quintile since you could have values that repeat right around the quintile boundaries.

Paul Shoemaker

http://www.matlabinvesting.com