Interpolate time series data for the same time period on each day

Hi,

I'm new to Matlab, so please forgive me if I'm asking an easy question.

I have created a timetable TT1 for minutely interpolation,

TT1 = timetable(DateTime,Close);

for example:

'02-Jan-1990 09:31:00'  353.40
'02-Jan-1990 09:32:00'  353.25
'02-Jan-1990 10:33:00'  353.02
'02-Jan-1990 12:34:00'  352.97
'02-Jan-1990 15:59:00'  352.81

Then I used retime to interpolate data for the first period

DateTime1=[datetime('02-Jan-1990 09:31:00'):minutes(1):datetime('02-Jan-1990 15:59:00')];
TT2 = retime(TT1,DateTime1,'linear');

However, I got more than 6000 trading days to do this 'minutely' interpolation.

The results should be a timetable with a column 'Close' for 6000 days (which is not linear) from 09:31 to 15:59

Is there any better ways than repeatedly typing in the same codes with 6000 different Dates?

Thanks for your help!

[jonas]

One way would be to interpolate over the entire data set and then remove certain values based on time of day. You would have to temporarily store a timetable 4x the final size though.

[dpb]

Are there any base data before interpolation outside the start/stop times?

I've not tried before with timetable but I'm thinking could use grouping variables on the day as one method.

Alternatively, I think one could use the above idea and simply build the times for datetime programmatically for all days found in the table.

The former would be a little more automatic I think...let me go piddle for a few minutes and see.

Answer 1

[jonas]

Here's one way to do it.

%%Original timetable, 5 minute intervals
t=[datetime('1990-1-1 00:00'):minutes(5):datetime('1990-1-8 00:00')]';
t(day(t)==3)=[];
t(day(t)==6)=[];
TT=timetable(t,[1:length(t)]');

%%List of unique days
uDays=unique(datetime(year(t),month(t),day(t)));

%%New time-series 1 min interval over 2 days
t_new=[datetime('1990-1-1 00:00'):minutes(1):datetime('1990-1-8 00:00')]';
Days=datetime(year(t_new),month(t_new),day(t_new));

%%Remove irrelevant times 
t_new(~ismember(Days,uDays))=[];  
t_new(hour(t_new)<9)=[];
t_new(hour(t_new)>16)=[];
t_new(hour(t_new)==9 & minute(t_new)<30)=[];

%%Interp
retime(TT,t_new,'linear')

I think this should give you what you want. Only problem is you need to store a datetime-array with 8 million rows temporarily (~ 60 MB)

[Peiying LI]

Thanks Jonas.

I thought to do this way too, but another problem is that except for 'hours' I also need to filter the 'dates', which have no patterns.

Actually, I have an array with unique dates, and I wonder whether they are useful for delete unwanted data.

[dpb]

Well, I got it able to group by day and select within the identified open/close times but then retime has so far failed to interpolate the associated data with the subselection. It succeeds on interpolating the full series; I've not yet been able to figure out what causes it to break; no errors, just returns NaN for all interpolated values. I was pretty sure I had bounded the range by selecting values before and after the new time vector and it's supposed to extrapolate anyway, but it seems to be acting like it's got data from outside the first location that returns NaN for the first and then that gets propagated throughout (that's conjecture, haven't yet been able to figure out just what is actually happening).

I've not worked on the alternate of building the datetime vector to simulate your one-at-a-time case, that should be straightforward but I've got other things must do at the moment...

Would still like to know answer to the question posed earlier; what are the time ranges for the days in your raw dataset; do they include times outside the start/close times? I presume not??

[Peiying LI]

Thank you so much for your time and work, dpb. Sorry that I didn't know how to reply you on comments.

There aren't any data outside, so it's just starts from 9:31 to 15:59 on each date by nature.

[dpb]

OK, I'm not positive it matters but if one doesn't have to worry about trashing data outside the times-of-day of interest that's one less detail.

It'll be later on tonight before have chance to actually do anything else but how about taking the first three/four days of your timetable and saving in .mat file and attaching it so we've got a piece of your actual dataset? That would make simpler than trying to mimic the problem.

You could do that something like

 idx=find(TT1.Time<=('05-Jan-1990'),1,'last');
 save TT1Section TT1(1:idx,:)

which will save as many days as are in the data from the (I assume) beginning of Jan-02 until the 5th. I didn't check for which day of week would have been weekend days that year; if the second happened to be Friday move the cutoff up a couple days. You can obviously look a the value returned for idx to see how many records are going to be saved.

[jonas]

Oh, I didnt realize you had unwanted dates although its clearly stated.

+1 on uploading a sample of your data

EDIT: I've also updated the code. The interpolated time-series now excludes days not also included in the original time-series.

[Peiying LI]

Thanks dpb and jonas.

Attached is part of TT1 with DateTime and Close, which contains entire raw data for the first three days (02-Jan-1990, 03-Jan-1990, and 04-Jan-1990).

[jonas]

Did you try my edited code? I think it should work. Just change it to:

TT=load('TT1Section.mat')
TT=TT.TT1Section;
t=TT.DateTime;
%%List of unique days
uDays=unique(datetime(year(t),month(t),day(t)));
%%New time-series 1 min interval over 2 days
t_new=[t(1):minutes(1):t(end)]';
Days=datetime(year(t_new),month(t_new),day(t_new));
%%Remove irrelevant times 
t_new(~ismember(Days,uDays))=[];  
t_new(hour(t_new)<9)=[];
t_new(hour(t_new)>16)=[];
t_new(hour(t_new)==9 & minute(t_new)<30)=[];
%%Interp
retime(TT,t_new,'linear')

There are no missing days in your data set. However, I think that part of the code also works.

[Peiying LI]

Jonas, I tried the code and it works fine! except that it still includes times after 16:00 to 17:00.

I changed 16 to 15 like follows:

   t_new(hour(t_new)>15)=[];

so it works perfect on 6000 days.

Thank you so much and I would accept your answer!

[jonas]

Happy to help!