Extract the last day of each month in daily data set

69 views (last 30 days)
I have a matrix of daily data from 1990 to 2015. The data is daily, but I need the all column of last day for each month to make it monthly data.Please note, that the end date of each month does not correspond to the calender end date of each month. The dates are in the first column and the date format is like 01/02/1990 .I need to extract the data of last day for each month and put them in the new matrix. I need the whole column for all last day of months.
for example for the first month in data I need this variables:
28/02/1990 21.99 21.99 21.99 21.99 21.99 0
for the second month :
30/03/1990 19.73 19.73 19.73 19.73 19.73 0
for the third month:
30/04/1990 19.52 19.52 19.52 19.52 19.52 0
and so on and then put them in a new matrix. How would I do this? Data is attached as VIX.CSV.

Accepted Answer

Star Strider
Star Strider on 8 Mar 2022
One approach —
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/918889/VIX.csv', 'VariableNamingRule','preserve')
T1 = 6530×7 table
Var1 Var2 Var3 Var4 Var5 Var6 Var7 __________ _____ _____ _____ _____ _____ ____ 1990-02-01 24.87 24.87 24.87 24.87 24.87 0 1990-02-02 24.32 24.32 24.32 24.32 24.32 0 1990-02-05 24.54 24.54 24.54 24.54 24.54 0 1990-02-06 24.69 24.69 24.69 24.69 24.69 0 1990-02-07 24.29 24.29 24.29 24.29 24.29 0 1990-02-08 23.77 23.77 23.77 23.77 23.77 0 1990-02-09 23.69 23.69 23.69 23.69 23.69 0 1990-02-12 24.38 24.38 24.38 24.38 24.38 0 1990-02-13 23.76 23.76 23.76 23.76 23.76 0 1990-02-14 22.05 22.05 22.05 22.05 22.05 0 1990-02-15 19.71 19.71 19.71 19.71 19.71 0 1990-02-16 20.78 20.78 20.78 20.78 20.78 0 1990-02-20 22.78 22.78 22.78 22.78 22.78 0 1990-02-21 23.89 23.89 23.89 23.89 23.89 0 1990-02-22 22.54 22.54 22.54 22.54 22.54 0 1990-02-23 23.69 23.69 23.69 23.69 23.69 0
[G,Y,M,D] = findgroups(year(T1.Var1),month(T1.Var1), day(T1.Var1));
Yu = unique(Y);
for k1 = 1:numel(Yu)
for k2 = 1:12
d = max(D((M==k2) & (Y==Yu(k1))));
if ~isempty(d)
EOM(k1,k2) = d;
end
end
end
% EOM
End_of_Month_Day = array2table(EOM, 'RowNames',compose('%d',Yu), 'VariableNames',compose('%2d',1:12))
End_of_Month_Day = 26×12 table
1 2 3 4 5 6 7 8 9 10 11 12 __ __ __ __ __ __ __ __ __ __ __ __ 1990 0 28 30 30 31 29 31 31 28 31 30 31 1991 31 28 28 30 31 28 31 30 30 31 29 31 1992 31 28 31 30 29 30 31 31 30 30 30 31 1993 29 26 31 30 28 30 30 31 30 29 30 31 1994 31 28 31 29 31 30 29 31 30 31 30 30 1995 31 28 31 28 31 30 31 31 29 31 30 29 1996 31 29 29 30 31 28 31 30 30 31 29 31 1997 31 28 31 30 30 30 31 29 30 31 28 31 1998 30 27 31 30 29 30 31 31 30 30 30 31 1999 29 26 31 30 28 30 30 31 30 29 30 31 2000 31 29 31 28 31 30 31 31 29 31 30 29 2001 31 28 30 30 31 29 31 31 28 31 30 31 2002 31 28 28 30 31 28 31 30 30 31 29 31 2003 31 28 31 30 30 30 31 29 30 31 28 31 2004 30 27 31 30 28 30 30 31 30 29 30 31 2005 31 28 31 29 31 30 29 31 30 31 30 30
.
  4 Comments

Sign in to comment.

More Answers (2)

Arif Hoq
Arif Hoq on 7 Mar 2022
if your data is arranged properly it would be 10227 rows from 1990 to 2017. then try this
N=1990:2017;
for i=1:length(N)
A(i,:)=eomday(i, 1:12); % returns the last day of month from 1990 to 2017
end
A1=A'; % transpose
A2=A1(:); % convert to column vector
A3=cumsum(A2); % last day number of a month from 1990 to 2017
last_row=A3(end,:)
Output= T(A3,:) % if T is the Table name
  6 Comments

Sign in to comment.


Steven Lord
Steven Lord on 8 Mar 2022
Let's look at a three month data set.
dates = (datetime(2022, 01, 01):datetime(2022, 03, 31)).';
data = randperm(numel(dates)).';
t = timetable(dates, data);
Let's make this monthly data by taking the mean of the data for each month.
t2 = retime(t, 'monthly', 'mean')
t2 = 3×1 timetable
dates data ___________ ______ 01-Jan-2022 42.355 01-Feb-2022 44.75 01-Mar-2022 49.323
The element of data in the first row of t2 should be the mean of the first 31 elements of data. Similarly the second row of t2 should be the mean of the next 28 days, and the third row the final 31.
mean(data(1:31)) - t2{1, 'data'}
ans = 0
mean(data(31 + (1:28))) - t2{2, 'data'}
ans = 0
mean(data(31+28 + (1:31))) - t2{3, 'data'}
ans = 0
But if you need to manually determine the end of the month for each of the dates in t, you can dateshift them.
datesEOM = dateshift(dates, 'end', 'month');
% Show a small sample of the dates to check
sample = randperm(numel(dates), 8);
[dates(sample), datesEOM(sample)]
ans = 8×2 datetime array
14-Jan-2022 31-Jan-2022 05-Mar-2022 31-Mar-2022 15-Jan-2022 31-Jan-2022 26-Mar-2022 31-Mar-2022 20-Mar-2022 31-Mar-2022 25-Feb-2022 28-Feb-2022 30-Mar-2022 31-Mar-2022 14-Mar-2022 31-Mar-2022

Categories

Find more on Dates and Time in Help Center and File Exchange

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by