Extract monthly stock return data out of a table

Question

Fabian Niederreiter 2021-3-21

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/778942-extract-monthly-stock-return-data-out-of-a-table

评论： Star Strider 2021-4-6

210320_ShortData_C.mat

I have this 251x5 table with monthly stock return data over a 10 year time frame for a high number of stocks (ID). (see attachement)

My goal is to create 41 different "Reporting datasets" each depicting an 11 month time frame. I would expect to get 41 tables in the same format as my original table. For more detailed description, look at the notes below.

Note: The actual table of data contains a lot more lines than this short demo table.

%% Form 12-1 month Reporting datasets
% Out of the monthly returns I want to create 41 groups of 11-months-Reporting datasets 
% I want to create a new Reporting dataset every 3 months (quaterly)
% R1 09/01 to 09/11 [year / month]
% R2 09/04 to 10/02
% R3 09/07 to 10/05
% ...
% R41 19/1 to 19/11

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Fabian Niederreiter 2021-3-22

Just want to let everybody know, that the approach proposed by star Strider is working perfectly.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Star Strider 2021-3-21

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/778942-extract-monthly-stock-return-data-out-of-a-table#answer_653682

在 MATLAB Online 中打开

The data do not go all the way to 2019, instead stopping at 2017 1.

Also, if I understand corrctly what you want to do, there are 81, not 41, tables in the result.

Try this:

D = load('210320_ShortData_C.mat');
G = D.G;
First15Rows = G(1:15,:);
Last15Rows = G(end-14:end,:);
start = 1:3:size(G,1)-10;
finish = start+10;
for k = 1:numel(start)
    DS{k} = G(start(k):finish(k),:);
end

with:

DS_01 = DS{1}
DS_02 = DS{2}
DS_80 = DS{end-1}
DS_81 = DS{end}

producing:

DS_01 =
  11×5 table
     ID      year    month       date       monthlyRET
    _____    ____    _____    __________    __________
  2009      1           20091     0.043223 
  2009      2           20092     0.064757 
  2009      3           20093    -0.069798 
  2009      4           20094     0.052383 
  2009      5           20095     0.004916 
  2009      6           20096      0.02546 
  2009      7           20097    -0.043847 
  2009      8           20098     0.057805 
  2009      9           20099     0.015785 
  2009     10      2.0091e+05     0.042746 
  2009     11      2.0091e+05     0.007961 
DS_02 =
  11×5 table
     ID      year    month       date       monthlyRET
    _____    ____    _____    __________    __________
  2009      4           20094     0.052383 
  2009      5           20095     0.004916 
  2009      6           20096      0.02546 
  2009      7           20097    -0.043847 
  2009      8           20098     0.057805 
  2009      9           20099     0.015785 
  2009     10      2.0091e+05     0.042746 
  2009     11      2.0091e+05     0.007961 
  2009     12      2.0091e+05      0.16691 
  2010      1           20101    -0.016774 
  2010      2           20102     0.000271 
DS_80 =
  11×5 table
     ID      year    month       date       monthlyRET
    _____    ____    _____    __________    __________
  2015     12      2.0151e+05     -0.16054 
  2016      1           20161       0.1172 
  2016      2           20162    -0.063814 
  2016      3           20163     -0.16176 
  2016      4           20164    -0.059612 
  2016      5           20165    -0.022708 
  2016      6           20166      0.30606 
  2016      7           20167     0.012423 
  2016      8           20168      0.39732 
  2016      9           20169    -0.010573 
  2016     10      2.0161e+05     0.004453 
DS_81 =
  11×5 table
     ID      year    month       date       monthlyRET
    _____    ____    _____    __________    __________
  2016      3           20163     -0.16176 
  2016      4           20164    -0.059612 
  2016      5           20165    -0.022708 
  2016      6           20166      0.30606 
  2016      7           20167     0.012423 
  2016      8           20168      0.39732 
  2016      9           20169    -0.010573 
  2016     10      2.0161e+05     0.004453 
  2016     11      2.0161e+05     0.074598 
  2016     12      2.0161e+05     -0.01457 
  2017      1           20171    -0.050697 

Those appear to me to be what you requested.

.

21 个评论
显示 19更早的评论隐藏 19更早的评论

Star Strider 2021-3-21

编辑：Star Strider 2021-3-21

在 MATLAB Online 中打开

I did not completely understand what you wanted.

Try this:

D = load('210320_ShortData_C.mat')
G = D.G;
Gu = unstack(G,'monthlyRET','ID');                                              % Unstack Using ‘ID’
Gu.Properties.VariableNames = strrep(Gu.Properties.VariableNames, 'x','ID_');   % Change ‘ID’ Names
Gu = Gu(Gu.year <= 2019,:);                                                     % Limit Rows
start = 1:3:size(Gu,1)-10;
finish = start+10;
for k = 1:numel(start)
    DS{k} = Gu(start(k):finish(k),:);
end

with:

DS_01 = DS{1}
DS_02 = DS{2}
DS_40 = DS{end-1}
DS_41 = DS{end}

producing:

DS_01 =
  11×9 table
    year    month       date       ID_10001     ID_10002     ID_10025     ID_10026     ID_10028     ID_10032 
    ____    _____    __________    _________    _________    _________    _________    _________    _________
    1           20091     0.043223     -0.43835     -0.18457    -0.022692     -0.18829     -0.13439
    2           20092     0.064757     -0.33206    -0.082562    -0.096532     -0.39222     -0.10564
    3           20093    -0.069798      0.21945      0.22589      0.10504     0.023135      0.10171
    4           20094     0.052383      0.12377      0.30476      0.12002      0.19034      0.51421
    5           20095     0.004916     -0.40998      0.21662    -0.030642    -0.001542     -0.18203
    6           20096      0.02546     -0.29761     0.076464    -0.039256      0.14498      0.12626
    7           20097    -0.043847    -0.012998      0.19584      0.19526     -0.13307      0.23759
    8           20098     0.057805      0.32204       0.1964     0.010337      0.38647    -0.016389
    9           20099     0.015785    -0.024919     0.044851    -0.008996      0.14316     0.045934
   10      2.0091e+05     0.042746    -0.072414     -0.12666    -0.095975     0.068962    -0.032813
   11      2.0091e+05     0.007961     -0.12658     0.085056    -0.078458    -0.051351     0.075131
DS_02 =
  11×9 table
    year    month       date       ID_10001     ID_10002     ID_10025    ID_10026     ID_10028     ID_10032 
    ____    _____    __________    _________    _________    ________    _________    _________    _________
    4           20094     0.052383      0.12377     0.30476      0.12002      0.19034      0.51421
    5           20095     0.004916     -0.40998     0.21662    -0.030642    -0.001542     -0.18203
    6           20096      0.02546     -0.29761    0.076464    -0.039256      0.14498      0.12626
    7           20097    -0.043847    -0.012998     0.19584      0.19526     -0.13307      0.23759
    8           20098     0.057805      0.32204      0.1964     0.010337      0.38647    -0.016389
    9           20099     0.015785    -0.024919    0.044851    -0.008996      0.14316     0.045934
   10      2.0091e+05     0.042746    -0.072414    -0.12666    -0.095975     0.068962    -0.032813
   11      2.0091e+05     0.007961     -0.12658    0.085056    -0.078458    -0.051351     0.075131
   12      2.0091e+05      0.16691      0.03871    0.018124      0.10658     0.052497     0.050052
    1           20101    -0.016774       0.3426    -0.08201     0.050076    -0.020832      0.18898
    2           20102     0.000271      0.15655    0.004063     0.022007      0.17511     0.018585
DS_40 =
  11×9 table
    year    month       date       ID_10001    ID_10002    ID_10025    ID_10026     ID_10028     ID_10032 
    ____    _____    __________    ________    ________    ________    _________    _________    _________
   10      2.0181e+05      NaN         NaN         NaN        0.035841     -0.18726     0.005142
   11      2.0181e+05      NaN         NaN         NaN        0.005752    -0.023593     0.046395
   12      2.0181e+05      NaN         NaN         NaN       -0.074561      -0.0235     -0.17362
    1           20191      NaN         NaN         NaN        0.075106    -0.060102     0.098057
    2           20192      NaN         NaN         NaN        0.006812     0.084715     0.096575
    3           20193      NaN         NaN         NaN        0.026897     0.016577    -0.010477
    4           20194      NaN         NaN         NaN       -0.009117     0.045077    -0.008486
    5           20195      NaN         NaN         NaN        0.024136      0.53771     -0.19107
    6           20196      NaN         NaN         NaN        0.004705      0.63712      0.16685
    7           20197      NaN         NaN         NaN         0.15242     -0.43362     0.026426
    8           20198      NaN         NaN         NaN        0.040125      0.50335    -0.038035
DS_41 =
  11×9 table
    year    month       date       ID_10001    ID_10002    ID_10025    ID_10026     ID_10028     ID_10032 
    ____    _____    __________    ________    ________    ________    _________    _________    _________
    1           20191      NaN         NaN         NaN        0.075106    -0.060102     0.098057
    2           20192      NaN         NaN         NaN        0.006812     0.084715     0.096575
    3           20193      NaN         NaN         NaN        0.026897     0.016577    -0.010477
    4           20194      NaN         NaN         NaN       -0.009117     0.045077    -0.008486
    5           20195      NaN         NaN         NaN        0.024136      0.53771     -0.19107
    6           20196      NaN         NaN         NaN        0.004705      0.63712      0.16685
    7           20197      NaN         NaN         NaN         0.15242     -0.43362     0.026426
    8           20198      NaN         NaN         NaN        0.040125      0.50335    -0.038035
    9           20199      NaN         NaN         NaN       -0.001763    -0.026812     0.092446
   10      2.0191e+05      NaN         NaN         NaN       -0.005651     0.078102      0.17332
   11      2.0191e+05      NaN         NaN         NaN       -0.028654      0.10819     0.027209

MATLAB does not allow strictlly numeric variable names, so it is necessary to replace the ‘ID’ labels with compatible variable names.

EDIT — (21 Mar 2021 at 15:02)

Corrected typographical errors.

Fabian Niederreiter 2021-3-21

210321_ShortData_C.mat

This is excactly what I was looking for! :)

After seeing your quick and fitting answers, I would love to post a follow up question:

I don't know if this is appropriate or if I should rather open a new Thread? If you want me to open a new thread just tell me in the next comment and I will do that. Otherwise I just formulate my follow up problem here:

I once again attached the output 1x41 cell I got on my demo dataset with the help of your code.

What I am looking for right now is two more 1x41 cells. The first one should include all stocks (in the same structure as before) out of the top 10% decile based on their summed up return for each 11 month reporting period. The second one should inlcude all stocks (in the same structure as before) out of the bottom 10% decile based on their summed up returns for each 11month reporting period.

Example: DS_01 right now includes 10 columns of different stocks.

As an output I would need DS_01_top with the top 10% decile stocks in this reporting period based on stock performance as well as a DS_01_bottom with the bottom 10% decile stocks in this reporting period based on stock performance.

Star Strider 2021-3-21

在 MATLAB Online 中打开

Now I understand!

This took a few minutes of experimenting:

D = load('210320_ShortData_C.mat')%, 'VariableNamingRule','preserve');
G = D.G;
Gu = unstack(G,'monthlyRET','ID');                                              % Unstack Using ‘ID’
Gu.Properties.VariableNames = strrep(Gu.Properties.VariableNames, 'x','ID_');   % Change ‘ID’ Names
Gu = Gu(Gu.year <= 2019,:);                                                     % Limit Rows
start = 1:3:size(Gu,1)-10;
finish = start+10;
for k = 1:numel(start)
    T11 = Gu(start(k):finish(k),:);                                             % Same Construction As Previously
    VarSum = sum(T11{:,4:end});                                                 % Sum Stock Columns
    thrshld = prctile(VarSum,[10 90]);                                          % Calculate [10 90] Percentiles
    colsl = find(VarSum<=thrshld(1))+3;                                         % Column With Lowest
    colsh = find(VarSum>=thrshld(2))+3;                                         % Column With Highest
    DS_10H{k,:} = T11(:,[1:3 colsh]);                                           % Create Table For Highest
    DS_10L{k,:} = T11(:,[1:3 colsl]);                                           % Create Table For Lowest
end

with:

DS_10_H01 = DS_10H{1}
DS_10_L01 = DS_10L{1}
DS_10_H41 = DS_10H{end}
DS_10_L41 = DS_10L{end}

producing:

DS_10_H01 =
  11×4 table
    year    month       date       ID_10025 
    ____    _____    __________    _________
    1           20091     -0.18457
    2           20092    -0.082562
    3           20093      0.22589
    4           20094      0.30476
    5           20095      0.21662
    6           20096     0.076464
    7           20097      0.19584
    8           20098       0.1964
    9           20099     0.044851
   10      2.0091e+05     -0.12666
   11      2.0091e+05     0.085056
DS_10_L01 =
  11×4 table
    year    month       date       ID_10002 
    ____    _____    __________    _________
    1           20091     -0.43835
    2           20092     -0.33206
    3           20093      0.21945
    4           20094      0.12377
    5           20095     -0.40998
    6           20096     -0.29761
    7           20097    -0.012998
    8           20098      0.32204
    9           20099    -0.024919
   10      2.0091e+05    -0.072414
   11      2.0091e+05     -0.12658
DS_10_H41 =
  11×4 table
    year    month       date       ID_10028 
    ____    _____    __________    _________
    1           20191    -0.060102
    2           20192     0.084715
    3           20193     0.016577
    4           20194     0.045077
    5           20195      0.53771
    6           20196      0.63712
    7           20197     -0.43362
    8           20198      0.50335
    9           20199    -0.026812
   10      2.0191e+05     0.078102
   11      2.0191e+05      0.10819
DS_10_L41 =
  11×4 table
    year    month       date       ID_10026 
    ____    _____    __________    _________
    1           20191     0.075106
    2           20192     0.006812
    3           20193     0.026897
    4           20194    -0.009117
    5           20195     0.024136
    6           20196     0.004705
    7           20197      0.15242
    8           20198     0.040125
    9           20199    -0.001763
   10      2.0191e+05    -0.005651
   11      2.0191e+05    -0.028654

Some documentation —

It first calculates the column sums, then uses the Statistics and Machine Learning Toolbox prctile function to calculate the percentiles, finds the columns with the highest and lowest percentiles, and creates new tables for each. The code is essentially the same as the previous code, in that it creates the same initial tables, however then edits them to include only the desired column (variable) in each new table.

I used the earlier data for this, noting that if more than one column meets the criteria for being greater than or less than than the 90th or 10th percentiles, the resulting tables could have more than one variable (column). That is not the situation here, although it could be with a sufficient number of variables (columns) in each table.

I checked a few of the returned tables in detail, and it appears to return the requested information correctly.

.

Fabian Niederreiter 2021-3-22

在 MATLAB Online 中打开

Thanks Star Strider!

Clean work as usual.

In order to finish my analysis I would need input on two more tasks. I hope you don't bother if I asked you for help on these two as well.

Step 1:

%% Form 12 month Analysing Portfolios
% These Analysing Portfolios should now be consisting out of the stocks from 
% the assiociated TOP and BOTTOM Reporting Portfolios (DS_10H and DS_10L).
% To be more precise A1Top should include the monthly returns for the 12 month 
% analysing period 10/1 to 10/12 (see below) from the stocks of DS_10H{1,1}, whereas 
% A1Bottom should include the monthly returns for the 12 month analysing 
% period 10/1 to 10/12 (see below) from the stocks of DS_10L{1,1}.
% This should once agian result in 41 TOP Analysing Portfolios and 41 
% BOTTOM Analysing portfolios for the following 12-month time frames
% A1Top 10/1 to 10/12 [year/month]
% A1Bottom 10/1 to 10/12
% A2Top 10/4 to 11/03 
% A2Bottom 10/4 to 11/03 
% A3Top 10/7 to 11/06
% A3Bottom 10/7 to 11/06
% ...
% A41Top 20/1 to 20/12
% A41Bottom 20/1 to 20/12

Step 2:

%% Compare avg Return of Top- to Bottom Analysing Portfolios
% Here I'd like to compare the avg monthly returns of all stocks in the Top
% Analysing portfolios over all 41 time periods with the avg monthly returns of all stocks in the
% Bottom Analysing portfolios. 
% Resulting in just two simple avg monthly return numbers: The avg Top
% monthly return and the avg Bottom monthly return.

The output should contain these two numbers as well as the difference between the two numbers similar to the attached photo (Sell = Bottom; Buy = Top; Buy - Sell = Difference):

The numbers in parentheses are not needed as of right now.

PS: Please let me know if there is a way to express my gratitute for your effort! Like a place where I can somehow write a positive evalutation or something. :)

Fabian Niederreiter 2021-3-22

编辑：Fabian Niederreiter 2021-3-22

210320_ShortData_C.mat

Thanks for your answer.

I try to rephrase my problem from Step 1 again by giving you a little more backround on it:

In the previous step we created a Top 10% and a Bottom 10% portfolio for each timeframe (R1Top 09/1 to 09/11, R1Bottom 09/1 to 09/11;...;R41Top 19/1 to 19/11, R41Bottom 19/1 to 19/11). These are our "Reporting portfolios".

Now I want to analyise how the stocks of our Reporting portfolios are doing in the following 12 months after skipping one month (A1Top 10/1 to 10/12, A1Bottom 10/1 to 10/12; ... ; A41Top 20/1 to 20/12, A41Bottom 20/1 to 20/12). Meaning R1 09/1 to 09/11 corresponding with A1Top 10/1 to 10/12, A1Bottom 10/1 to 10/12.

Example: "We take the stocks of our R1 09/1-09/11 Top 10% portfolio and now analyse their returns in the corresponding timeframe A1Top 10/1 to 10/12.

Likewise we take the stocks of our R1 09/1-09/11 Bottom 10% portfolio and now analyse their returns in the corresponding timeframe A1Bottom 10/1 to 10/12."

Then we repeat these two steps for all 41 timeframes and we are done.

Note: As we need the stocks data until 20/12 you'd need to take my initial table G with the complete data for the 10 sample stocks. I reattached it to this post again.

Fabian Niederreiter 2021-3-22

Sorry for that. I might have not expressed this clearly enough.

DS_10_H01 = DS_10H{1} this is one table within the 41x1cell we created with your previous code. The table contains the Top 10% of stocks (in our case only one stock per portfolio) for the first timeframe (09/01-09/11). In my description above this equals R1Top 09/1 to 09/11.

DS_10_L01 = DS_10L{1} is also one table within the 41x1cell we created with your previous code. The table contains the Bottom 10% of stocks (in our case only one stock per portfolio) for the first timeframe (09/01-09/11). In my description above this equals R1Bottom 09/1 to 09/11.

Now we want to analyse how these exact portfolios (in our case only one stock per portfolio) is doing in the corresponding analysing timeframe (10/1 to 10/12). In my description above this equals A1Top 10/1 to 10/12 and A1Bottom 10/1 to 10/12.

As a result for every Reporting timeframe R1Top/R1Bottom to R41Top/R41Bottom we should be having a corresponding analysing timeframe A1Top/A1Bottom to A41Top/A41Bottom.

Star Strider 2021-3-22

在 MATLAB Online 中打开

I still have absolutely no clue as to what you want with this.

Try this:

filename = '210320_ShortData_C.mat';
D = load(filename);%, 'VariableNamingRule','preserve');
G = D.G;
Gu = unstack(G,'monthlyRET','ID');                                              % Unstack Using ‘ID’
Gu.Properties.VariableNames = strrep(Gu.Properties.VariableNames, 'x','ID_');   % Change ‘ID’ Names
Gu = Gu(Gu.year <= 2019,:);                                                     % Limit Rows
start = 1:3:size(Gu,1)-10;
finish = start+10;
for k = 1:numel(start)
    T11 = Gu(start(k):finish(k),:);                                             % Same Construction As Previously
    VarSum = sum(T11{:,4:end});                                                 % Sum Stock Columns
    thrshld = prctile(VarSum,[10 90]);                                          % Calculate [10 90] Percentiles
    colsl = find(VarSum<=thrshld(1))+3;                                         % Column With Lowest
    colsh = find(VarSum>=thrshld(2))+3;                                         % Column With Highest
    DS_10H{k,:} = T11(:,[1:3 colsh]);                                           % Create Table For Highest
    DS_10L{k,:} = T11(:,[1:3 colsl]);                                           % Create Table For Lowest
    DS_10H_Perf{k,:} = DS_10H{k,:}([1 end],:);                                  % Create Performance Table For Highest
    LL_H = varfun(@(x)diff(x,1),DS_10H_Perf{k});                                % Calculate Performance Table For Highest
    DS_10H_Perf{k,:}(end+1,4) = LL_H(:,4);                                      % Add Row
    DS_10L_Perf{k,:} = DS_10L{k,:}([1 end],:);                                  % Create Performance Table For Lowest
    LL_L = varfun(@(x)diff(x,1),DS_10L_Perf{k});                                % Calculate Performance Table For Lowest
    DS_10L_Perf{k,:}(end+1,4) = LL_L(:,4);                                      % Add Row
end
DS_10_H01 = DS_10H{1}
DS_10_H01P = DS_10H_Perf{1}
DS_10_L01 = DS_10L{1};
DS_10_L01P = DS_10L_Perf{1}

producing:

DS_10_H01P =
  3×4 table
    year    month       date       ID_10078
    ____    _____    __________    ________
    2009      1           20091     0.14615
    2009     11      2.0091e+05     0.04109
       0      0               0    -0.10506
DS_10_L01P =
  3×4 table
    year    month       date       ID_10002
    ____    _____    __________    ________
    2009      1           20091    -0.43835
    2009     11      2.0091e+05    -0.12658
       0      0               0     0.31177

The ’performance’ metric is simply the difference between the first and alst prices, and is the last row of each of these table.

This is not my area of expertise.

Fabian Niederreiter 2021-3-28

Hello again :)

Sorry for the late reply, I once again appreciate your effort a lot. Unfortunatelly these are still not the outputs I was looking for.

As I think that I wasn't clear enough in describing the targeted solution again, I will give it one more try before stopping to bother you with my questions in here. :)

I will attach the current status of my code as well as the data in order for us to be on the same page again.

Let us also please ignore what I wrote as Step 2 before and solely focus on what I describe right now:

The .mat file I attached contains the 3 cell arrays we created in previous steps plus the table 'G' with the monthly return data (2009-2020) of our now 20 sample stocks.

DS_10T and DS_10B now both contain 41 tables with the top/bottom 10% percentile of stocks based on their performance in the respective time frame.

I decided to give you a step by step instruction on what I'd like to see the code doing:

Look into table DS_10T{1, 1} (top 10% of stocks in reporting time frame R1 09/01 - 09/11)
Take the Stock_IDs out of DS_10T{1, 1} and search for their return data in table 'G'
Extract the return data of these stocks out of 'G' for the corresponding analysing time frame A1Top 10/1 - 10/12 (12 months, skipping one month after reporting time frame)
Save the extracted return data as the first table of a new 41x1 cell DS_10T_An
Look into table DS_10T{2, 1} (top 10% of stocks in reporting time frame R1 09/04 - 10/02)
Take the Stock_IDs out of DS_10T{2, 1} and search for their return data in table 'G'
Extract the return data of these stocks out of 'G' for the corresponding analysing time frame A1Top 10/4 - 11/03 (12 months, skipping one month after reporting time frame)
Save the extracted return data as the second table of the 41x1 cell DS_10T_An

... continue this until the we created A41Top as the last Analysing portfolio of our Top performing stocks.

Then perform excactly the same task for the 41 Bottom Portfolios:

Look into table DS_10B{1, 1} (bottom 10% of stocks in reporting time frame R1 09/01 - 09/11)
Take the Stock_IDs out of DS_10B{1, 1} and search for their return data in table 'G'
Extract the return data of these stocks out of 'G' for the corresponding analysing time frame A1Bottom 10/1 - 10/12 (12 months, skipping one month after reporting time frame)
Save the extracted return data as the first table of a new 41x1 cell DS_10B_An
Look into table DS_10B{2, 1} (bottom 10% of stocks in reporting time frame R1 09/04 - 10/02)
Take the Stock_IDs out of DS_10B{2, 1} and search for their return data in table 'G'
Extract the return data of these stocks out of 'G' for the corresponding analysing time frame A1Bottom 10/4 - 11/03 (12 months, skipping one month after reporting time frame)
Save the extracted return data as the second table of the 41x1 cell DS_10B_An

... continue this until the we created B41Top as the last Analysing portfolio of our Bottom performing stocks.

I assume we put all of this into a loop as we did before and therefore create our desired 41x1 cell 'DS_10T_An' (with the top 10% stocks data as input) as well as the other 41x1 cell 'DS_10B_An' (with the bottom 10% stocks data as input).

I hope this time I was precise enough to make you want to tackle it one last time for me.

Thanks a lot in advance!

Fabian Niederreiter 2021-4-1

210329_ShortData_C.mat

Hello again,

Now that I managed to get my analysing portfolios sorted, I asked the community for some advice on the tiny last step of my project... unfortunatelly we did not find a fitting solution until now. That's why I am coming back to you once again and will ask you for one more advise. :)

I would like to compare the average of the monthly stock return data listed in the 41x1 cell array 'DS_10T' with the data listed in the cell array 'DS_10B'.

As there are some NaNs within my data I hope that one can somehow not factor them into the averages.

I once again attached a short version of the relevant data as my original dataset would need significantly more memory, because it includes a lot more stocks.

Even tough every cell array contains 41 tables with data from different timeframes I would only need a comparison of the overall average monthly return out of all tables in 'DS_10T' versus the overall average monthly return of all tables of 'DS_10B' (so literally just 1 number for each of the two cell arrays) in the following format:

Where "Sell" depicts the average monthly returns of the 'DS_10B' array, "Buy" the average monthly returns of the 'DS_10T' array and Buy-Sell the difference between the two values. The numbers in parentheses can be ignored for now.