Going back in time 1 week or a year

Dear all,

I attach a data set of weekly observations on an index.

Suppose that I am in week 2023/6/16 (yellow cell). For this cell I want to obtain the corresponding index value

Then I want to go back one week (7 days) ; that is to 2023/6/9 (yellow cell) and calculate its corresponding index value and then calculate the percentage change between the two values.

I used something like

t = datetime(2023,6,16);

t2 = dateshift(t,'dayofweek', 7,'previous')

but it does not work. I am sure that there is a mistake in my code. Is there a way to do that.

Also, assuming that we are in week 2023/6/16, I want to calculate the mean, min, max of the index values for the last 12 months. Is there a way to do that?

I would very much appreciate some guidance.

Thank you in advance!

Answer 1

[Voss]

T = readtable('data.xlsx')

T = 1390×2 table
       Date        Index
    ___________    _____

    02-Jan-2018    2281 
    03-Jan-2018    2426 
    04-Jan-2018    2715 
    05-Jan-2018    2791 
    08-Jan-2018    2826 
    09-Jan-2018    2820 
    10-Jan-2018    2677 
    11-Jan-2018    2384 
    12-Jan-2018    2296 
    15-Jan-2018    2231 
    16-Jan-2018    2010 
    17-Jan-2018    1743 
    18-Jan-2018    1611 
    19-Jan-2018    1493 
    22-Jan-2018    1445 
    23-Jan-2018    1525 

"Suppose that I am in week 2023/6/16 (yellow cell). For this cell I want to obtain the corresponding index value

Then I want to go back one week (7 days) ; that is to 2023/6/9 (yellow cell) and calculate its corresponding index value and then calculate the percentage change between the two values."

dt = datetime(2023,6,9); % June 9th, 2023

row_dt = find(T.Date == dt);
row_a_week_before_dt = find(T.Date == dt - caldays(7));

Index_dt = T{row_dt,'Index'}

Index_dt = 1514

Index_a_week_before_dt = T{row_a_week_before_dt,'Index'}

Index_a_week_before_dt = 1116

percent_change = 100*(Index_dt-Index_a_week_before_dt)/Index_a_week_before_dt

percent_change = 35.6631

"Also, assuming that we are in week 2023/6/16, I want to calculate the mean, min, max of the index values for the last 12 months. Is there a way to do that?"

dt = datetime(2023,6,16); % June 16th, 2023

row_dt = find(T.Date == dt);
row_a_year_before_dt = find(T.Date > dt - calyears(1), 1);

Index_last_year = T{row_a_year_before_dt:row_dt,'Index'};

mean_last_year = mean(Index_last_year)

mean_last_year = 1.6455e+03

min_last_year = min(Index_last_year)

min_last_year = 271

max_last_year = max(Index_last_year)

max_last_year = 3031

[ektor]

Hi Voss,

Thank you a lot.

[Peter Perkins]

This answer is incorrect. Do NOT subtract days or years for this purpose.

As the doumentation says, these are "exact-length time" units equal to exactly 24hrs and 365.2425*24hrs. You may be using unzoned datetimes today, but eventually, you will use time zones, in which case days will give you the wrong answer.

years will already give you the wrong answer.

Use caldays and calyears, as Steve Lord says.

I also recommend that you use timetables, and look into things like groupsummary or retime, to compute data summaries over each week, or year or whatever. Your life will be much easier.

Answer 2

[Steven Lord]

Subtract the appropriate calendar duration.

t = datetime('today')

t = datetime
   15-Jul-2023

oneWeekAgo = t - calweeks(1)

oneWeekAgo = datetime
   08-Jul-2023

oneYearAgo = t - calyears(1)

oneYearAgo = datetime
   15-Jul-2022

Don't try to subtract 1 year as a duration. That's 365.24 days, not exactly 1 year.

oneYearAgoAlmost = t - years(1)

oneYearAgoAlmost = datetime
   14-Jul-2022 18:10:48