Effacer les filtres
Effacer les filtres

Get max and min of each row in a table and create a new table with those values

5 vues (au cours des 30 derniers jours)
Dennis Heizler
Dennis Heizler le 9 Avr 2020
Modifié(e) : Andrei Bobrov le 10 Avr 2020
i have a table with returns of 10 different portfolios for each month. Columns are the names of the portfolios and each row corresponds to one month. I want to rebuild a momentum portfolio, so in the first row(first month) I look at the 3 best performers and 3 worst performers. After determining which Portfolio performed best/worst, I will invest in it for 1 month. This means that I need the return of month 2 (row 2) for the best/worst performers of row 1. In row 2 (month 2) I look again at the best/worst performers and I need their return from row 3 (month 3) and so on...Those values should be in a new table so I can plot it later.
Screenshot of first 5 rows attached. the first column is the date, I want to ignore that one.
I'm very new to Matlab and tried to do it on my own but did not succeed at all.
My initial thought was to go with
[maxVals,maxLocs] = maxk(A{:,vartype('numeric')},2,2)
[minVals,minLocs] = mink(A{:,vartype('numeric')},2,2)
But I don't know how to get the corresponding return of the next month associated with the best/worst performers of the previous month.
Thank you all!

Réponses (1)

Andrei Bobrov
Andrei Bobrov le 9 Avr 2020
Modifié(e) : Andrei Bobrov le 9 Avr 2020
Let A - your table.
[~,i] = sort(A{1,2:end});
out = A(:,[1,1+i([end:-1:end-2,3:-1:1])]);
  2 commentaires
Dennis Heizler
Dennis Heizler le 9 Avr 2020
This only gives me the best performers of row 1 (month 1) and doesn't change if afterwards. I'd need the code to pull the best/worst performers each month (each month there are different best/worst performers)...
Andrei Bobrov
Andrei Bobrov le 10 Avr 2020
Modifié(e) : Andrei Bobrov le 10 Avr 2020
Maybe it's
T = readtable('Path\your\file\Screenshot 2020-04-09 at 11.34.40.xlsx',...
'ReadVariableNames',1);
T.Date = datetime(T.Date,'InputFormat','yyddMM');
N = string(T.Properties.VariableNames(2:end));
[~,i] = sort(T{:,2:end},2,'descend');
out = [T(:,1),array2table(N(i(:,[1:3,end-2:end])))];

Connectez-vous pour commenter.

Catégories

En savoir plus sur Matrices and Arrays dans Help Center et File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!

Translated by