Extracting data in specific time intervals

Hi! I'm working with financial market HF data that looks like this:

            Time             Midpoint
    _____________________    ________

    20130102 01:00:00:558     30.33  
    20130102 01:00:01:272    30.325  
    20130102 01:00:02:228    30.325  
    20130102 01:00:02:308     30.33  
    20130102 01:00:02:892    30.337  
    20130102 01:00:03:517    30.338  
    20130102 01:00:05:298    30.331  
    20130102 01:00:13:857    30.337  
    20130102 01:00:14:296    30.337  
    20130102 01:00:14:417     30.33

What I want to do is, extract the price in column 2 at specific intervals, say 15 seconds. The problem is that the data is not equally spaced, so simply taking any xth value like this y:x:z doesn't work. Does anyone have an idea how to approach this?

Answer 1

[Star Strider]

One approach:

C = {'20130102 01:00:00:558'     30.33  
    '20130102 01:00:01:272'    30.325  
    '20130102 01:00:02:228'    30.325  
    '20130102 01:00:02:308'     30.33  
    '20130102 01:00:02:892'    30.337  
    '20130102 01:00:03:517'    30.338  
    '20130102 01:00:05:298'    30.331  
    '20130102 01:00:13:857'    30.337  
    '20130102 01:00:14:296'    30.337  
    '20130102 01:00:14:417'     30.33};

mktHF = cell2table(C, 'VariableNames',{'Time','Midpoint'});                         % Convert
mktHF.Time = datetime(mktHF.Time, 'InputFormat','yyyyMMdd HH:mm:ss:SSS');           % Create ‘datetime’ Object From ‘Time’ Variable
mktHF = table2timetable(mktHF);                                                     % Convert To ‘timetable’
mktHFr = retime(mktHF, 'secondly', 'linear');                                       % Interpolate To Seconds
sec15 = mktHFr(1:15:end,:)                                                          % Every 15 Seconds

producing:

sec15 =

    2×1 timetable

              Time            Midpoint
      ____________________    ________

      02-Jan-2013 01:00:00     30.334 
      02-Jan-2013 01:00:15     30.296

You will likely not need the first conversion step, since it appears that your data are already in a table object. The others will be necessary.

You may have to experiment to get the result you want. This should get you started.

[Ben]

Thank you. That's exactly what I wanted. Guess it was really time to upgrade to a newer version.

[Star Strider]

As always, my pleasure.

There may not be version differences. It depends on how you imported or created your table. I copied and formatted mine from the data you posted, so I had to go through the conversions.

I am not certain if readtable automatically creates datetime objects, and it did not in R2018b when I used it for my own code recently. I had to do the datetime conversion first, in order to use retime with it.

Note that Guillaume’s retime call would eliminate the need for the separate ‘sec15’ assignment in my code, so the last two lines could be replaced by:

sec15 = retime(mktHF, 'regular', 'nearest', 'TimeStep', seconds(15))

Answer 2

[Guillaume]

Since it appears your data is stored in a timetable, the simplest way would be to call retime with whichever aggregation method is most suitable ( 'nearest maybe?):

retime(yourtimetable, yourtimetable.Time(1): seconds(15): yourtimetable.Time(end), 'method', 'nearest')

Note that in R2018a or later, the above would be a lot simpler:

retime(yourtimetable, 'regular', 'nearest', 'TimeStep', seconds(15))

If your data is in a table convert it to a timetable with table2timetable.

[Ben]

Thank you! The timetable command is what I needed. Just had to upgrade Matlab to get it