Making date regular in timetable

Why are the date sequences on a quarterly frequency irregular? Is there a way to make them regular?

% creating sequence of dates with quarterly frequency
enDt = datetime('31/12/2018');
stDt = enDt - calquarters(24);
Time = stDt:calquarters(1):enDt;

%%
% creating random timetable
Var1 = rand(length(Time),1);
Var2 = rand(length(Time),1);
Var3 = rand(length(Time),1);
Var4 = rand(length(Time),1);
TT = timetable(Time,Var1,Var2,Var3,Var4);

%%
t = TT.Time;
missIDX = ismissing( TT ); % nothing missing
all(sum(missIDX))

% Question 1: Why is TT not regular??? 
isReg = isregular( TT, 'quarters' );

% Date vector is all showing consistent 1 quarter duration
dt = caldiff( t, 'quarters' ); 

% Question 2: Why do the dates all shift when i synchronize??? I would like
% the dates to remain as the date sequence given above.
TT2 = synchronize( TT, 'quarterly' );

Answer 1

[dpb]

Q1. Because you created a timetable with the time vector that is last day of each calendar quarter month over a period of years. A timetable uses actual time and there aren't the same number of days in every calendar quarter--there can't be because neither 365 nor 366 is exactly divisible by 4.

For your TT

>> diff(TT.Time)
ans = 
  24×1 duration array
   2160:00:00
   2184:00:00
   2208:00:00
   2208:00:00
   2160:00:00
   ...

showing the differing hours/days in each quarter. They simply are NOT the same actual time spans; a "quarter" is a division of an equal number of months, but all months are not created equal.

caldiff has the logic built into it to return equal intervals of the type requested accounting for the actual differences in absolute time. Consequently, it returns the expected result that matches the result of calquarters (as it should). But neither are consistent in terms of absolute time.

synchronize uses the beginning of the time period and you're defined your starting point as the last day of the quarter, not the first. I'm suspecting that's at least part of the issue there.

The first two parts are clear and expected; I'm not sure how to resolve the last Q? because I don't fully understand the objective from the example. If you showed the real application, probably somebody here could help resolve your difficulties.

[Jeff Slee]

Thanks for the comment. I agree that the "regular" issue is related with day having to be the first of the month. Once i adjust my quarterly series to the first day of each quarter then the object is returned as regular. This is a bit of an inconvenience that the timetable object can't recogonize the end of each quarter as also being regular -- noting that the difference between each period is different regardless taking the first day or last day.

My main goal is to understand how to use the timetable object with financial timeseries (typically with quarterly/monthly/daily frequencies). I am currently interested in simply taking the difference between periods but this does not appear to be as straightforward as a user would hope (recalling a simple function "diff" available for the fints object). I do believe now that the object is "regular" i will then be able to create a function that will do the period difference for me (staying within the timetable object).

Q: Would you happen to know how to take the difference between periods with the timetable object (i.e. for any series S and time period t, then St - S(t-1) )

Below is the mock up example for quarterly to show that switching to the first day of the quarter makes the object "regular".

% creating sequence of dates with quarterly frequency
Time = (datetime('01/12/2000'):calquarters(1):datetime('01/12/2018'))';

% creating random timetable
Var1 = rand(length(Time),1);
Var2 = rand(length(Time),1);
Var3 = rand(length(Time),1);
Var4 = rand(length(Time),1);
TT = timetable(Time,Var1,Var2,Var3,Var4);

% Note: Difference between periods are still different 
diff( TT.Time(1:10) )

% Flipping dates to first day of each quarter then makes timeseries regular 
% (seems like a limitation with the object)
isReg = isregular( TT, 'quarters' );

% Date vector still shows consistent 1q duration
dt = caldiff( TT.Time, 'quarters' ); 

[dpb]

>> Time = (datetime('01/12/2000'):calquarters(1):datetime('01/12/2018'))';
Warning: Successfully read the date/time text using the format 'MM/dd/uuuu', but their format is ambiguous and could also be 'dd/MM/uuuu'. Specify a format character vector to avoid
ambiguity. 
> In guessFormat (line 66)
  In datetime (line 612) 
Warning: Successfully read the date/time text using the format 'MM/dd/uuuu', but their format is ambiguous and could also be 'dd/MM/uuuu'. Specify a format character vector to avoid
ambiguity. 
> In guessFormat (line 66)
  In datetime (line 612) 
>> TT
TT =
  73×4 timetable
        Time         Var1        Var2        Var3         Var4  
    ___________    ________    ________    _________    ________
    12-Jan-2000     0.85181     0.62545      0.61368     0.37435
    12-Apr-2000     0.80991     0.34665      0.78297      0.9015
   ....

Don't think you have what you think you have here...

[Jeff Slee]

Phewwww, still recogonized as "regular" thanks should have inserted the format.

% creating sequence of dates with quarterly frequency
Time = (datetime('01/12/2000','InputFormat','dd/MM/uuuu'):calquarters(1):datetime('01/12/2018','InputFormat','dd/MM/uuuu'))';

% creating random timetable
Var1 = rand(length(Time),1);
Var2 = rand(length(Time),1);
Var3 = rand(length(Time),1);
Var4 = rand(length(Time),1);
TT = timetable(Time,Var1,Var2,Var3,Var4);

% Note: Difference between periods are still different 
diff( TT.Time(1:10) )

% Flipping dates to first day of each quarter then makes timeseries regular 
% (seems like a limitation with the object)
isReg = isregular( TT, 'quarters' );

% Date vector still shows consistent 1q duration
dt = caldiff( TT.Time, 'quarters' ); 

[dpb]

Hmmm...I guess on further reflection I'd agree that isregular behavior is at least rude if not in error for the other anniversary dates. It doesn't seem unreasonable to have quarterly or other calendar periods considered "regular" besides the first day of the month.

I'd suggest submit an enhancement request to <[e-mail]> requesting such or asking for a workaround.

I've not messed with the time series object that much; it seemed for the most part to be more in the way than of help for anything I've tried to do with it...but I think perhaps the answer to your Q? above is

>> caldiff(TT.Time,'quarters')
ans = 
  4×1 calendarDuration array
   1q
   1q
   1q
   1q
>> 

Before, I wasn't sure I understood what the question was but I'm thinking mayhaps that's it?

I don't know just what you can do with the resulting calendarDuration data type as far as what kind of calculations or other manipulations you're after...

[Jeff Slee]

Thanks for your reply, i greatly appreciate it.

I just sent an email out to the support as you suggested.

For the difference between periods, yes the calldiff returns the duration but I would like to get the difference between my series for example:

I also am not sure if the calendarDuration can be used for further calculations but my guess is that it is not the correct function that I am looking for. For the financial time seris object the function is simply "diff"

[dpb]

You can diff a time series; just that it will return the absolute difference, not in quarters...if you're working with financial time series, I think there are methods specific for the purpose but I don't have the Financial TB so not really sure.

[dpb]

Again the timetable isn't well suited for the task at hand it appears to me...you likely would do better to just use a regular table or separate arrays.

BUT you can produce your second table from a timetable; I had just a tiny little one here with just two columns--

>> TT
TT =
  5×2 timetable
        Time        Var1       Var2  
    ___________    _______    _______
    31-Dec-2000    0.92048    0.50455
    31-Mar-2001    0.75084    0.61069
    30-Jun-2001    0.28559     0.7038
    30-Sep-2001    0.79682    0.38334
    31-Dec-2001    0.14277    0.72868
>> dTT=timetable(TT.Time(2:end),diff(TT.Var1),diff(TT.Var2))
dTT =
  4×2 timetable
        Time         Var1        Var2  
    ___________    ________    ________
    31-Mar-2001    -0.16965     0.10614
    30-Jun-2001    -0.46525    0.093109
    30-Sep-2001     0.51122    -0.32046
    31-Dec-2001    -0.65405     0.34534
>> 

But, you are correct that you can't diff(TT), you have to dereference the content. Stuff like this is why I said earlier it seemed like the class sorta' gets in the way as much or more than it helps.

IF you still think other reasons make it worthwhile to keep the timetable, what you might find more advantageous would be to keep the data in a single array instead of by variables so you don't have to dereference each by name: the same example as above recast in that form--

>> TT=timetable(Time,rand(length(Time),2))
TT =
  5×1 timetable
        Time               Var1        
    ___________    ____________________
    31-Dec-2000     0.88728     0.41131
    31-Mar-2001    0.055848     0.95914
    30-Jun-2001     0.13822     0.75025
    30-Sep-2001     0.86306       0.981
    31-Dec-2001     0.42175     0.23352
>> dTT=timetable(TT.Time(2:end),diff(TT.Var1))
dTT =
  4×1 timetable
        Time               Var1        
    ___________    ____________________
    31-Mar-2001    -0.83144     0.54783
    30-Jun-2001    0.082368     -0.2089
    30-Sep-2001     0.72485     0.23075
    31-Dec-2001    -0.44132    -0.74748
>> 

Now you've got the differences but will have to use array indexing to get a given column. Not sure which would end up being more useful, if either.

[Peter Perkins]

Jeff, I hear what you are saying, and this is something we're actively looking at, but let me ask some questions. We can agree that this works as you expect (the 1st day in each quarter):

>> d = dateshift(datetime,'start','quarter') + calquarters(0:5);
>> x = rand(6,1);
>> tt = timetable(x,'RowTimes',d)
tt =
  6×1 timetable
            Time               x   
    ____________________    _______
    01-Jan-2019 00:00:00    0.54722
    01-Apr-2019 00:00:00    0.13862
    01-Jul-2019 00:00:00    0.14929
    01-Oct-2019 00:00:00    0.25751
    01-Jan-2020 00:00:00    0.84072
    01-Apr-2020 00:00:00    0.25428
>> isregular(tt,'quarters')
ans =
  logical
   1

This also works (the 20th day of each quarter):

>> tt.Time = tt.Time + caldays(20)
tt =
  6×1 timetable
            Time               x   
    ____________________    _______
    21-Jan-2019 00:00:00    0.54722
    21-Apr-2019 00:00:00    0.13862
    21-Jul-2019 00:00:00    0.14929
    21-Oct-2019 00:00:00    0.25751
    21-Jan-2020 00:00:00    0.84072
    21-Apr-2020 00:00:00    0.25428
>> isregular(tt,'quarters')
ans =
  logical
   1

The problem, as you've discovered, is that this does not work as you expect (the last day in each quarter):

>> tt.Time = dateshift(tt.Time,'end','quarter')
tt =
  6×1 timetable
            Time               x   
    ____________________    _______
    31-Mar-2019 00:00:00    0.54722
    30-Jun-2019 00:00:00    0.13862
    30-Sep-2019 00:00:00    0.14929
    31-Dec-2019 00:00:00    0.25751
    31-Mar-2020 00:00:00    0.84072
    30-Jun-2020 00:00:00    0.25428
>> isregular(tt,'quarters')
ans =
  logical
   0   

So here's my two questions:

1) I know that end-of-quarter series are common in finance, but in a sense, isn't the specific day within a quarter just a label? The differencing you are doing is all in terms of quarters, not days, is that correct? So as a work-around, would it suffice to just use the beginning days?

Perhaps at some point you need to interpolate or something in order to synchronize to daily or whatever series. Using the beginning of each quarter would not work for that. But if none of the arithmetic you are doing involves converting to days or months, it seems like it should make no computational difference.

2) Does having isregular return false block your work in some way? It may be that you are using isregular as a way to determine if you have missing data. Perhaps you could test if quarters(calldiff(t)) > 1 as a work-around for that?

[Jeff Slee]

Hi Peter, thank you for your further question. Please see my replies below:

1) We have various frequencies we work with. The most common being monthly and quarterly. Absolutely, I do not see this being a huge impact in my current workflow. This would be something that would require adjustments if running data through models or graphing. This was more a self-discovery issue as I wasn't sure why it wasn't working with end of month.

2.) Without doing the workaround then some functions that are available for the timestable object might not work (e.g. lag). I would suggest to look at other functions that also require the timetable object to be regular.

This issue came up when I was trying to create my own user function (after realising one wasn't already available) that returns the timetable object back and gives the difference of all series in the object by a specified period of duration -- which I was using the lag function. My workaround is that i first convert the dates to the beginning of the period, take the difference and adjust the dates back to the end of period.

[Peter Perkins]

Thanks, Jeff. As I said, this is something we're looking at, including the lag thing. Arithmetic with calendars is surprisingly ill-posed, but "end of month/quarter" is important enough in finance to support as a special case.

[dpb]

"Arithmetic with calendars is surprisingly ill-posed,..."

Indeed, I can just imagine some of the gyrations, Peter. Always great to get your feedback on the time-related stuff -- you've got the "inside scoop" on how to use some of the newer features such as the durations that have some interesting issues as we've discussed before...

It would seem like "just" the isregular part wouldn't be too bad, however, unless there's some outside definition that it isn't simply that the day() difference between successive elements is same for a uniform difference of year and month (ignoring time for the moment)?

I'm no econometrics type so what special stuff they've developed in timeseries models based on calendar dates as opposed to "real time" I don't have a clue--I can imagine some of that is tricky at best, though.

[michal.markun]

Hi, the discussion is long ago, but it's still funny, sort of, that I 'self-discovered', just like Jeff did, all the above issues again on my own.

I use Matlab R2019b and maybe the issue has already been solved, but at this point my conclusion is that I don't see the point in keeping the precise dates in timetables if the format is specified as mytimetable.Time.Format='yyyyQQQ'.

I expected that in this case Matlab abstract from a precise day of a quarter, so that I can synchronize two timetables read from Excel files where dates formats were different, eg. 30.06.1992 and 1992Q2. Instead I first had to dateshift the fist timetable to the start of quarter. No big deal, but it took me some time to figure it out, and still seems a bit illogical.

[Steven Lord]

@michal.markun, changing the Format property of a datetime array does not actually change the contents of the array. All it does is change how the array is displayed. If we did as you suggested and actually changed the data, x1 and x2 in the following example would not be the same and I think a lot of people would consider that a bug.

x1 = datetime('now')

x1 = datetime
   05-Jan-2023 16:10:22

F = x1.Format % Store the original format

F = 'dd-MMM-uuuu HH:mm:ss'

x1.Format = 'yyyyQQQ'

x1 = datetime
   2023Q1

x2 = x1

x2 = datetime
   2023Q1

x1.Format = F

x1 = datetime
   05-Jan-2023 16:10:22

x2 % Keeps its Format even though x1's Format changed

x2 = datetime
   2023Q1

isequal(x1, x2)

ans = logical
   1

x3 = datetime('2023Q1', 'InputFormat', 'yyyyQQQ')

x3 = datetime
   01-Jan-2023

isequal(x1, x3) % false

ans = logical
   0

x3.Format = 'yyyyQQQ' % But its display matches x2

x3 = datetime
   2023Q1

isequal(x2, x3) % also false

ans = logical
   0