How do I create ranking (descending) table in matlab based on inputs from two separate data tables?

Question

I have four data sets (please bear with me here):

• 1st Table: List of 10 tickers (stock symbols) in one column in txt format in matlab.
• 2nd table: dates in numerical format in one column (10 days in double format).
• 3rd table: I have 10*10 data set of random numbers (assume 0-1 for simplicity). (Earnings Per Share growth EPS for example)--so I want high EPS growth in my ranking for portfolio construction.
• 4th table: I have another 10*10 data set of random numbers (assume 0-1 for simplicity). (Price to earnings ratios for example daily).-so I want low P/E ratio in my ranking for portfolio construction.

NOW: I want to rank portfolio of stocks each day made up of 3 stocks (largest values) from table one for a particular day and bottom three stocks from table 2 (smallest values). The output must be list of tickers for each day (3 in this case) based on combined ranking of the two factors (table 3 & 4 as described).

Any ideas? In short I need to end up with a top bucket with three tickers...

Answer 1

It is not entirely clear from the post what you are trying to achieve. Here is a take based on guessing, with various options.

Your first two "tables" store symbols for stocks and days (irrelevant for ranking). Your third and fourth are scores arranged in a stock x day manner. Let's assume stocks vertical, days horizontal and stocks symbolized with a value in [1:10].

N = 10; % num of stocks
M = 10; % num of days   
T3 = rand(N,M); % table 3 stocks x days
T4 = rand(N,M); % table 4 stocks x days

Sort the score tables in ascending and descending order (to get upper and lower scores per day, i.e. per column):

[Sl,L] = sort(T3, 'descend'); 
[Ss,S] = sort(T4, 'ascend');

Keep three largest and smallest:

largest = L(1:3,:);  % bucket of 3 largest per day
smallest = S(1:3,:); % bucket of 3 smallest per day

IF you need the ones in both (0 is nan):

% Inter-section of both buckets
indexI = zeros(3,M);
for i=1:M
    z = largest(ismember(largest(:,i),smallest(:,i)));
    if ~isempty(z)
        indexI(1:length(z),i) = z;
    end
end

IF you need the ones in either one (0 is nan):

% Union of both buckets
indexU = zeros(6,M);
for i=1:M
    z = unique([largest(:,i),smallest(:,i)]);
    indexU(1:length(z),i) = z;
end

IF you need a ranking of scores/stocks from the set of largest_of_3 and smallest_of_4:

scoreAll = [Sl(1:3,:); Ss(1:3,:)];
indexAll = [largest;smallest];

[~,indexSort] = sort(scoreAll,'descend');
for i=1:M
    indexBest(:,i) = indexAll(indexSort(1:3,i),i);
end

UPDATE

To get a weighted ranking of the final scores, define the weight vector (1 x scores) and use one of the two options below, before sorting scoreAllW instead of scoreAll:

w = [0.3 ;0.3; 0.3; 0.7; 0.7; 0.7];
scoreAllW = scoreAll.*repmat(w,1,10);    % Option 1
scoreAllW = bsxfun(@times, scoreAll, w); % Option 2