Extract monthly stock return data out of a table

3 views (last 30 days)
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 Comment
Fabian Niederreiter
Fabian Niederreiter on 22 Mar 2021
Just want to let everybody know, that the approach proposed by star Strider is working perfectly.

Sign in to comment.

Accepted Answer

Star Strider
Star Strider on 21 Mar 2021
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
_____ ____ _____ __________ __________
10001 2009 1 20091 0.043223
10001 2009 2 20092 0.064757
10001 2009 3 20093 -0.069798
10001 2009 4 20094 0.052383
10001 2009 5 20095 0.004916
10001 2009 6 20096 0.02546
10001 2009 7 20097 -0.043847
10001 2009 8 20098 0.057805
10001 2009 9 20099 0.015785
10001 2009 10 2.0091e+05 0.042746
10001 2009 11 2.0091e+05 0.007961
DS_02 =
11×5 table
ID year month date monthlyRET
_____ ____ _____ __________ __________
10001 2009 4 20094 0.052383
10001 2009 5 20095 0.004916
10001 2009 6 20096 0.02546
10001 2009 7 20097 -0.043847
10001 2009 8 20098 0.057805
10001 2009 9 20099 0.015785
10001 2009 10 2.0091e+05 0.042746
10001 2009 11 2.0091e+05 0.007961
10001 2009 12 2.0091e+05 0.16691
10001 2010 1 20101 -0.016774
10001 2010 2 20102 0.000271
DS_80 =
11×5 table
ID year month date monthlyRET
_____ ____ _____ __________ __________
10025 2015 12 2.0151e+05 -0.16054
10025 2016 1 20161 0.1172
10025 2016 2 20162 -0.063814
10025 2016 3 20163 -0.16176
10025 2016 4 20164 -0.059612
10025 2016 5 20165 -0.022708
10025 2016 6 20166 0.30606
10025 2016 7 20167 0.012423
10025 2016 8 20168 0.39732
10025 2016 9 20169 -0.010573
10025 2016 10 2.0161e+05 0.004453
DS_81 =
11×5 table
ID year month date monthlyRET
_____ ____ _____ __________ __________
10025 2016 3 20163 -0.16176
10025 2016 4 20164 -0.059612
10025 2016 5 20165 -0.022708
10025 2016 6 20166 0.30606
10025 2016 7 20167 0.012423
10025 2016 8 20168 0.39732
10025 2016 9 20169 -0.010573
10025 2016 10 2.0161e+05 0.004453
10025 2016 11 2.0161e+05 0.074598
10025 2016 12 2.0161e+05 -0.01457
10025 2017 1 20171 -0.050697
Those appear to me to be what you requested.
.
  21 Comments

Sign in to comment.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!