Carry trade portfolio with 20 countries

I have twenty countries monthly data from 1970 to 2020, and each country has two variables: Exchange rate change and Interest rate.

I would like to

1. sort 20 countries into 5 groups of 4 countries for every month byInterest rate from the lowest to the highest. (for example [1 2 3 4], [5 6 7 8]... [17 18 19 20] )
2. Group 1 always has the highest interest rate and follow by group 2, 3 ,4 ,5 for every period so the member of each group in every period will change if the rank of interest rate change.
3. Calculate the average interest rate and exchange rate change of each group for every period.

This is how my data look like CAEX means Canada exchage rate change. There is another array name CAIR meaning Canada Interest rate.

I tried putting each country into a matrix with 2 arrays of data and sort country every period base on interest rate and then do the simple averaging for each period but I am new to using Matlab so I am not sure how to put my thoughts into operation.

I did

CA = zeros(nrow,2);
CA = [Matlab1(1:nrow,'CAEX'), Matlab1(1:nrow,'CAIR')];
UK = zeros(nrow,2);
UK = [Matlab1(1:nrow,'UKEX'), Matlab1(1:nrow,'UKIR')];
IR = zeros(nrow,2);

Then I dont know how to sort matrixs into groups base on elements in it and not to mention doing calculation by extracting selected elements.

I think there should be other ways that is more clean and efficient than what I am doing here. Any helps would be greatly appreciated!

[dpb]

In general, creating named variables is the wrong approach -- makes it very difficult to write generic code.

It would help others to experiment if you were to attach a sample dataset -- wouldn't need to be nearly so big; 3 or 4 countries and a few months worth of data would suffice. Use the paperclip icon and attach a .mat file with the sample data.

Answer 1

[dpb]

That was kinda' fun!!! :)

% create some sample data...use a table for convenience
tPort=[      table(datetime(1997,0:20,1,'Format','yyyy-MM-dd').','VariableNames',{'Date'})  ...
       array2table(10*rand(21,6),'VariableNames',{'CAIR','UKIR','AUIR','MEXIR','IREIR','NZIR'})];
countries=categorical(cellfun(@(s) s(1:end-2),tPort.Properties.VariableNames(2:end),'UniformOutput',false));

% sample first raw data looks like--
>> head(tPort)
ans =
  8×7 table
       Date        CAIR      UKIR      AUIR      MEXIR      IREIR      NZIR 
    __________    ______    ______    _______    ______    _______    ______
    1996-12-01    2.3337    8.5625    0.95377    8.8964     8.7635    3.8014
    1997-01-01    6.0084     5.842     3.5256    1.0963     9.9979    8.1283
    1997-02-01    1.1246    3.7358     5.9342    4.3777     8.6425     2.441
    1997-03-01    5.1577    2.2169     5.8518    2.8023    0.36878    8.8442
    1997-04-01    8.3784    2.1899     6.6768    9.8525     5.4468    7.1265
    1997-05-01    9.2079    5.2223     6.4803    6.0876     9.9762    3.7815
    1997-06-01    4.9823    4.3342     4.3337    2.5375     5.1101    2.4892
    1997-07-01    2.7761     7.413     1.3976    1.3261     8.7351    2.5285
>> 

% the engine...
% generate cell array of the sorted interest rates and associated indices
PortSort=cell2mat(rowfun(@(x)sort(x,2,'descend'),tPort,'InputVariables',tPort.Properties.VariableNames(2:end), ...
                                               'SeparateInputs',0,'numoutputs',2,'OutputFormat','cell'));
N=size(PortSort,2);         % number of columns in the sorted array--first half rates, second sort indices
% Build output table from the sorted rates and arrange country IDs by sort order to match
tPortSort=[tPort(:,1) array2table(PortSort(:,1:6),'VariableNames',compose("IR_%02d",1:6)) ...
           array2table(countries(PortSort(:,7:end)),'VariableNames',compose("ID_%02d",1:6))];

% yields
>> head(tPortSort)
ans =
  8×13 table
       Date       IR_01     IR_02     IR_03     IR_04     IR_05      IR_06     ID_01    ID_02    ID_03    ID_04    ID_05    ID_06
    __________    ______    ______    ______    ______    ______    _______    _____    _____    _____    _____    _____    _____
    1996-12-01    8.8964    8.7635    8.5625    3.8014    2.3337    0.95377     MEX      IRE      UK       NZ       CA       AU  
    1997-01-01    9.9979    8.1283    6.0084     5.842    3.5256     1.0963     IRE      NZ       CA       UK       AU       MEX 
    1997-02-01    8.6425    5.9342    4.3777    3.7358     2.441     1.1246     IRE      AU       MEX      UK       NZ       CA  
    1997-03-01    8.8442    5.8518    5.1577    2.8023    2.2169    0.36878     NZ       AU       CA       MEX      UK       IRE 
    1997-04-01    9.8525    8.3784    7.1265    6.6768    5.4468     2.1899     MEX      CA       NZ       AU       IRE      UK  
    1997-05-01    9.9762    9.2079    6.4803    6.0876    5.2223     3.7815     IRE      CA       AU       MEX      UK       NZ  
    1997-06-01    5.1101    4.9823    4.3342    4.3337    2.5375     2.4892     IRE      CA       UK       AU       MEX      NZ  
    1997-07-01    8.7351     7.413    2.7761    2.5285    1.3976     1.3261     IRE      UK       CA       NZ       AU       MEX 
>> 

Oh. The averages by group will be simply to use mean over the sequential columns in the number of columns per group. One could either build a new summary table or augment the existing one.

For simplicity, I left the rates in sequence grouped with the associated names also grouped; one could rearrangethe table to have them alternate by either countryID/IR or vice versa to have the visual connection. For a large number, that probably would be the thing to do.

[dpb]

OBTW: I hardcoded in the number of columns "1:6" and "7:end" for quick 'n dirty -- real code would query the data table for number of columns and use that value as "1:nVars" and "nVars+1:end"