Sorting Data by Part of Column Name

Hi Mathworks

I have a big dataset collected from Datastream, which contains a price for each company and various firm related factors for each month, i.e. in total there is 8 columns pr. firm.

Each column starts with the ticker of the firm - otherwise the name is identical for the various measures. Please see the attached Excel sheet (a small sample with 3 firms or so) or the screenshot.

My question is:

Is it possible in any smart way to tell Matlab to collect all 3500 columns containing the word "MV#T" and find the max from it?

All the best from a desperate thesis student,

Christoffer

Answer 1

[dpb]

This is basically trivial exercise for Matlab...

>> t=readtable('DataDK.xlsx');  % read the file into table
Warning: Variable names were modified to make them valid MATLAB identifiers. The original names are saved in the VariableDescriptions property. 
>> whos t
  Name        Size             Bytes  Class    Attributes
  t         349x37            120624  table              
>> t(1:10,:)                    % see what looks like...
ans =
  10×37 table
       Date       DK_NON_UP_T__DK    DK_NON_RI_T__DK    DK_NON_MV_T__DK    DK_NON_WC05491__DK    DK_NON_NOSH_    DK_NON_MTBV_T_    DK_NON_WC03501__DK    DK_NON_WC02999__DK    DK_NON_WC18191__DK    DK_DEN_UP_T__DK    DK_DEN_RI_T__DK    DK_DEN_MV_T__DK    DK_DEN_WC05491__DK    DK_DEN_NOSH_    DK_DEN_MTBV_T_    DK_DEN_WC03501__DK    DK_DEN_WC02999__DK    DK_DEN_WC18191__DK    DK_DSV_UP_T__DK    DK_DSV_RI_T__DK    DK_DSV_MV_T__DK    DK_DSV_WC05491__DK    DK_DSV_NOSH_    DK_DSV_MTBV_T_    DK_DSV_WC03501__DK    DK_DSV_WC02999__DK    DK_DSV_WC18191__DK    DK_COL_UP_T__DK    DK_COL_RI_T__DK    DK_COL_MV_T__DK    DK_COL_WC05491__DK    DK_COL_NOSH_    DK_COL_MTBV_T_    DK_COL_WC03501__DK    DK_COL_WC02999__DK    DK_COL_WC18191__DK
    __________    _______________    _______________    _______________    __________________    ____________    ______________    __________________    __________________    __________________    _______________    _______________    _______________    __________________    ____________    ______________    __________________    __________________    __________________    _______________    _______________    _______________    __________________    ____________    ______________    __________________    __________________    __________________    _______________    _______________    _______________    __________________    ____________    ______________    __________________    __________________    __________________
    12/31/1989    331.9                3173             8967.9             1.909                 27020           1.69               6.208e+06            1.1609e+07            1.3141e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                      725             142.16              84.82             0.962                 117             NaN               1.4498e+05             3.744e+05            32945                 1736.6             262.86             963.81             2.198                 555             2.56              4.1545e+05            7.1203e+05            1.1315e+05        
    1/31/1990     323.2              3089.8             8732.9             2.146                 27020           1.46              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                    759.2             148.86              88.83             0.962                 117             NaN               1.4498e+05             3.744e+05            32945                   1450             292.64               1102             2.198                 760             2.93              4.1545e+05            7.1203e+05            1.1315e+05        
    2/28/1990     325.2              3108.9             8786.9             2.146                 27020           1.47              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                      825             161.76              96.52             0.962                 117             NaN               1.4498e+05             3.744e+05            32945                   1401             282.75             1064.8             2.198                 760             2.83              4.1545e+05            7.1203e+05            1.1315e+05        
    3/30/1990     313.6              2998.1             8473.5             2.146                 27020           1.42              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                   836.75             164.07               97.9             0.962                 117             NaN               1.4498e+05             3.744e+05            32945                 1457.5             294.16             1107.7             2.198                 760             2.95              4.1545e+05            7.1203e+05            1.1315e+05        
    4/30/1990     285.2              2764.5             7706.1             2.146                 27020           1.29              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                      840             164.71              98.28             0.962                 117             NaN               1.4498e+05             3.744e+05            32945                   1370             276.49             1041.2             2.198                 760             2.77              4.1545e+05            7.1203e+05            1.1315e+05        
    5/31/1990     303.8              2944.8             8208.7             2.146                 27020           1.38              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                   960.95             188.42             112.43             0.962                 117             NaN               1.4498e+05             3.744e+05            32945                 1464.5             295.55               1113             2.198                 760             2.96              4.1545e+05            7.1203e+05            1.1315e+05        
    6/29/1990     315.6              3059.2             8527.5             2.146                 27020           1.43              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                      860             193.82             187.48             0.962                 218             NaN               1.4498e+05             3.744e+05            32945                 1416.7             285.91             1076.7             2.198                 760             2.86              4.1545e+05            7.1203e+05            1.1315e+05        
    7/31/1990     333.5              3232.7             9011.2             2.146                 27020           1.51              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                      970             218.61             211.46             0.962                 218             NaN               1.4498e+05             3.744e+05            32945                   1430              288.6             1086.8             2.198                 760             2.89              4.1545e+05            7.1203e+05            1.1315e+05        
    8/31/1990     321.3              3114.5             8681.5             2.146                 27020           1.46              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                      850             191.57              185.3             0.801                 218             NaN               1.2233e+05            3.8782e+05            -1578                   1390             280.53             1056.4             2.198                 760             2.81              4.1545e+05            7.1203e+05            1.1315e+05        
    9/28/1990     307.9              2984.6             8319.5             2.146                 27020           1.39              6.9904e+06            1.2466e+07            1.4448e+06            NaN                NaN                NaN                NaN                   NaN             NaN               NaN                   NaN                   NaN                      800              180.3              174.4             0.801                 218             NaN               1.2233e+05            3.8782e+05            -1578                   1350             272.46               1026             2.198                 760             2.73              4.1545e+05            7.1203e+05            1.1315e+05        
>> 

>> imvt=find(contains(t.Properties.VariableDescriptions,'MV#T'));   % locate wanted columns
>> t(1:10,imvt)                                                     % show is what wanted...
ans =
  10×4 table
    DK_NON_MV_T__DK    DK_DEN_MV_T__DK    DK_DSV_MV_T__DK    DK_COL_MV_T__DK
    _______________    _______________    _______________    _______________
    8967.9             NaN                 84.82             963.81         
    8732.9             NaN                 88.83               1102         
    8786.9             NaN                 96.52             1064.8         
    8473.5             NaN                  97.9             1107.7         
    7706.1             NaN                 98.28             1041.2         
    8208.7             NaN                112.43               1113         
    8527.5             NaN                187.48             1076.7         
    9011.2             NaN                211.46             1086.8         
    8681.5             NaN                 185.3             1056.4         
    8319.5             NaN                 174.4               1026         
>> 
>> format bank            % to print as decimal at command line only for convenience
>> [min(t{:,imvt});max(t{:,imvt});mean(t{:,imvt})]   % display some statistics
ans =
       7706.10     100681.20         79.57        963.81
     824819.00     183412.20     113138.40     137609.90
     201838.45           NaN      17838.16      30106.59

[Christoffer Benneballe]

@dbp - thank you very much. It runs perfect - I really appreciate it!

Do you know if Matlab can perform a logic argument as an "if((and))" as in Excel dragging on the names as well?

Having found the maximum MV I'm trying to collect returns as the price in column UP#T calculated as (price at time t / price at time t-1)-1. However, the return should be put in 1 of 5 portfolios sorted as 0-20% of max MV, 20-40% of max MV (...) 80-100% of max MV.

I'm planning to do this for all firms, one at a time, but if it can be done a smarter way that you know of I'd really value it.

Otherwise it will be done one firm at a time and, then I will take an average in each decile to form a portfolio.

All the best,

Christoffer

[dpb]

I "know nuthink!" to quote Sgt. Schultz of Excel so have no idea what that means, sorry.

You can do anything you would want to do in Matlab I'm sure altho if you're talking about a GUI user interface, you possibly might have to implement something yourself...TMW has built a bunch of new GUI tools into their interface but I've never looked at any of it...I code what I want, don't mess with that kind of stuff in the way.