Sort stocks into portfolios based on two different stock characteristics

Hello,

I try to sort stocks into portfolios and calculate their market cap weighted porrfolio returns based on two different stock characteristics. Suppose I have a Matrix A (180,400) with 180 (monthly) return timeseries of 400 different stocks. Matrices B and C (both 15x400) contain two different characteristics of each company, but at different dates (yearly, therefore 15 rows).

All three Matrices contain NaNs.

Firms shall be yearly sorted into portfolios based on the yearly 5 quantiles (0.2:0.2:0.8) of each of the two characteristics. Two different characteristics with 5 quantiles results in 5x5 = 25 portfolios each year.

I would be very delighted if anyone could give me some hints.

Please let me know if anyone of you had to solve the same problem in the past already.

Thank you very much in advance.

[Kirby Fears]

Hi Christoph,

Are B and C derived from A? If so, you can use operations that ignore NaN values so B and C do not have NaNs. E.g. nanmean() and nanstd() can be used on A to prevent NaNs in B and C.

[Christoph Kemmer]

Hi Kirby,

than you very much for your reply. B and C are not derived from A.

A contains returns of 400 stocks, B and C values (=characteristics) from the same 400 stocks, but independent from A. For the moment, only B and C are important.

I try to sort each stock each year to a portfolio based on the 0.2, 0.4, 0.6 and 0.8 quantiles of the Matrices B and C.

Example: If stock X's value in Matrix B at time t is in the 0.2 quantil of all values of Matrix B at time t, and at the same time its value in Matrix C at time t in the 0.8 quantil of all values of Matrix C at time t, then stock X shall at time t be assigned to the portfolio "Lowest quantile characteristic 1 / Highest quantile characteristic 2".

I have currently troubles in finding a smart way to solving this task. Until now I used only if/ifelse commands, but this looks very inconvenient to me.

Again, I appreciate your help. Thank you very much.

[John Mart]

If anyone want help in double sorting code or any other aassignment help then contact me.. [phone] Or whatsapp me [phone]

Answer 1

[Kirby Fears]

First, you should get rid of the NaN values in B and C by replacing them with something meaningful. If a NaN value in B is considered poor, replace it with -999 (or some other poor statistic) as a placeholder so it is nicely sorted into the worst (middling, or best) quantile based on your replacement value.

Once NaN's are removed from B and C, the code below creates 25 portfolios each year based on B and C quantiles.

% Making data
B=randn(15,400);
C=randn(15,400);

divB=5; % this must divide size(B,2)
divC=5; % this must divide size(C,2)

% These will be useful in loop later
stepB=size(B,2)/divB;
stepC=size(C,2)/divC;

% For each year (dim1), group into percentiles
[~,sortIdxB]=sort(B,2);
[~,sortIdxC]=sort(C,2);

% One collection of portfolios per year
portfolios=cell(size(B,1),1);
for yr=1:size(B,1),
    % Each year has divB*divC portfolios
    temp=cell(divB,divC);
    for prcB=1:divB,
        tempB=sortIdxB(yr,(1+(prcB-1)*stepB):prcB*stepB);
        for prcC=1:divC,
            tempC=sortIdxC(yr,(1+(prcC-1)*stepC):prcC*stepC);
            temp{prcB,prcC}=intersect(tempB,tempC);
        end,
    end,
    portfolios{yr}=temp;
end,

Hope this helps.

[Christoph Kemmer]

Kirby,

thank you very much for this great answer. The numbers in the portfolios{yr}-arrays represent the indices of the 400 stocks, right? I was exactly looking for an output like this.

The only problems are the NaNs. They unfortunately can't just be replaced by placeholders: If there is a NaN in any of the Matrices A, B or C, the stock shall not be included in any of the portfolios at this year. This means, that the approach of dividing all columns of both Matrices B and C into 5 groups of equal size doesn't work out. Please correct me if I am wrong.

[Kirby Fears]

That's right, the portfolios are indices of the stocks (1-400).

As for the NaN values...

If you want to divide 400 stocks into 5 equally sized bins but one of them is NaN (excluded), you now have 399 stocks which cannot be evenly divided into 5 quantiles.

1. If you want to leave one quantile with 79 stocks instead of 80, which quantile should come up short?

2. Similarly, if there are two NaN values, what quantiles should come up short?

If you can answer these questions, I think we can form a solution.

Aside: If a stock has a NaN value in matrix B, does it also have a NaN value in the same position in matrix C? If a stock has a NaN position in B or C but not both, it sounds like you'd want to exclude it. This will be important for the logic used in implementation.

[Christoph Kemmer]

To your last question: a NaN in B does not necessarily imply a NaN in C and vice versa. You are more than right, as soon as there is a NaN in either B or C or both, the stock shall be excluded in this year.

To 1. and 2.:

I got your point, with the NaNs there is no chance of forming 5 equally sized bins anymore, but they don't have to be equally sized.

I think, this problem might arise because of the "dividing into 5 groups" logic (as you said it, 399 cannot be divided by 5). Would it maybe be better to use the quantile function, in order to (row by row) calculate and work with the quantile breakpoints?

If a stock is < than the smallest quantile breakpoint in year 1, it shall come into the smallest quantile portfolio, if a stock is >= the smallest quantile breakpoint and < than the second-smallest quantile breakpoint, it shall come into the second smallest quantile portfolio and so on (applied on both characteristics of course).

Thanks again Kirby, I am impressed that you understood my task here so quickly!

[Kirby Fears]

Hi Christoph,

As long as you have the statistics toolbox, using the quantile function is a good idea. Quantile() ignores NaNs already, so you can directly plug in B and C to get your quantiles. Then use less than / greater than to subset B and C. Note that you will always get "false" for NaN values when using less than or greater than.

Code is reworked below. Use ctrl+i in Matlab to correct the indention.

% Making data
B=randn(15,400);
C=randn(15,400);
B(2)=NaN; % for testing NaNs
C(16)=NaN;

% Set (upper) quantile boundaries for portfolio subsets
bQuants=[0.2,0.4,0.6,0.8,1];
cQuants=bQuants; % These don't need to be equal

% Store quantile upper boundary values
bBounds=quantile(B,bQuants,2); % quantiles per row
cBounds=quantile(C,cQuants,2);

% One collection of portfolios per year
portfolios=cell(size(B,1),1);
for yr=1:size(B,1),
    % Pre-allocate quantile matrix
    temp=cell(numel(bQuants),numel(cQuants));
    yrB=B(yr,:);
    yrC=C(yr,:);
    for prcB=1:numel(bQuants),
          if prcB==1,
        tempB=find(yrB<=bBounds(yr,prcB));
    else
        tempB=find((yrB>bBounds(yr,prcB-1))&(yrB<=bBounds(yr,prcB)));
    end,
          for prcC=1:numel(cQuants),
            if prcC==1,
          tempC=find(yrC<=cBounds(yr,prcC));
      else
          tempC=find((yrC>cBounds(yr,prcC-1))&(yrC<=cBounds(yr,prcC)));
      end,
                temp{prcB,prcC}=intersect(tempB,tempC);
          end,
    end,
    portfolios{yr}=temp;
end,

Hope this helps.

[Christoph Kemmer]

Amazing Kirby, works perfectly. Thank you very much.

A very last question on this I have. In case there are NaNs in Matrix A (monthly returns): A stock shall as well be excluded from the portfolios in the year, in which it has a NaN in any of the respective 12 months (rows) of Matrix A. Is there a way to include this into the above code? I am trying hard to implement it by a third intersect criteria, without success.

Nevertheless, again a huge thanks already until here. Great job.

[Kirby Fears]

Sure, a sneaky way to ignore these values would be to make those stocks have a NaN B or C value (temporarily). For example, in the lines below, yrB and yrC are temporary variables that reset every year, so you can set them to NaN using your extra criteria.

for yr=1:size(B,1),
    ...
    yrB=B(yr,:);
    yrC=C(yr,:);

Adding in some NaN overrides below.

for yr=1:size(B,1),
    ...
    aNanIdx=any(isnan(A(1+(yr-1)*12:yr*12,:)),1);
    yrB=B(yr,:);
    yrB(aNaNIdx)=NaN; % set these to NaN for exclusion
    yrC=C(yr,:);

There are many other ways to solve this problem, but this solution is a 2-liner. Annotate these lines well since they only work as long as your data (row factor of 12) and implementation of the portfolio logic don't change.

Hope this helps.

[Christoph Kemmer]

Amazing! Works perfectly.

From here I calculated certain values (let's say D) for every single stock in each of the 25 portfolios, for each year. I am now trying to subdivide the 25 portfolios yearly into three more bins (1x3), according to the tertiles of the formerly calculated values D.

For example:

portfolios{1,1}{1,1}{1,3} would contain the stocks of {1,1}{1,1}, whose D values are in the highest tertile of all D values of the stocks within {1,1}{1,1}.

Right now I am trying to do it with a third "criteria matrix" D (again 15x415, containing all stocks), and to include it into the above code (making a third intersect criteria). I am stuck at the problem, that the quantile function shall now be applied on only the subset stocks instead of all stocks.

But maybe there is a smarter way, without the need of a third criteria matrix.

Thank you Kirby!

[Walter Roberson]

Richard Mawulawoe Ahadzie comments,

How come Matrix A is not in the code. Was to double sort A based on B and C.

[John Mart]

 anyone want help in double sorting code or any other aassignment help then contact me.. [phone] Or whatsapp me [phone]

Answer 2

[Sara Elsayed]

Hi Thanks for very useful discussion I want to ask why when I integrate this command aNanIdx=any(isnan(A(1+(yr-1)*12:yr*12,:)),1); it does not work, I have tried different representation, however, the common message is Index exceeds matrix dimensions. Can you help me with that? Second, how can I get the corresponding monthly returns to each portfolio?