MATLAB Answers

Compare average monthly stock return data

2 views (last 30 days)
Fabian Niederreiter
Fabian Niederreiter on 29 Mar 2021
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 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' 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.
  13 Comments
Fabian Niederreiter
Fabian Niederreiter on 6 Apr 2021
Nevermind I just managed to do it by myself :)
Attached my solution for the case that you are interested:
%% Mean Bottom Portfolio
meanDS_10B = NaN(1,numel(DS_10B));
for k =1:numel(DS_10B)
t = DS_10B{k,:};
% Extract stock data columns of every table
lastColumn = t{:, 4:end}; % Get all columns with stock return data of table as column vectors of doubles
%reshape into one single column in order to calculate mean
lastCo = reshape(lastColumn,[],1);
% mean the last column but use omitnan
mean_column = mean(lastCo,'omitnan');
meanDS_10B(k) = mean_column;
end
% Average all Means
avgRET_Bottom = mean(meanDS_10B,'omitnan');

Sign in to comment.

Answers (1)

dpb
dpb on 29 Mar 2021
See the optional 'nanflag' argument to mean--
M = mean(___,nanflag) specifies whether to include or omit NaN values from the calculation for any of the previous syntaxes. mean(A,'includenan') includes all NaN values in the calculation while mean(A,'omitnan') ignores them.
cellfun and an anonymous function should let you do this in one line, methinks. :)
I didn't explore the big table in depth, but I'd guess you could probably also do all the analyses directly with the table itself with the use of grouping variables and rowfun and perhaps varfun

Community Treasure Hunt

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

Start Hunting!