Info

此问题已关闭。 请重新打开它进行编辑或回答。

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

1 次查看(过去 30 天)
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.

回答(1 个)

Paul Shoemaker
Paul Shoemaker 2018-3-1
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

此问题已关闭。

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by