Sorting Data by Part of Column Name
Show older comments
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
Accepted Answer
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
2 Comments
Christoffer Benneballe
on 14 Dec 2019
@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
on 14 Dec 2019
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.
More Answers (0)
Categories
Find more on Resizing and Reshaping Matrices in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
