financial time series time with milliseconds

I'm trying to create and merge two financial time series, but I'm getting an issue where it seems to truncate the overall number of data and round to the nearest minute. Is there a way to display the full time to the millisecond?

val =

    desc:  (none)
    freq:  Unknown (0)

    'dates:  (230)'    'times:  (230)'    'bid:  (230)'
    '05-Jan-0116'      '07:31'            [          0]
    '     "     '      '08:59'            [          0]
    '     "     '      '09:00'            [   34220.00]
    '     "     '      '09:01'            [   36030.00]
    '     "     '      '09:02'            [   36200.00]
    '     "     '      '09:03'            [   36150.00]
    '     "     '      '09:04'            [   36130.00]
    '     "     '      '09:05'            [   36150.00]
    '     "     '      '09:06'            [   36150.00]
    '     "     '      '09:07'            [   36160.00]
    '     "     '      '09:08'            [   36150.00]
    '     "     '      '09:09'            [   36060.00]
    '     "     '      '09:10'            [   35970.00]
    '     "     '      '09:11'            [   35930.00]
    '     "     '      '09:12'            [   35930.00]
    '     "     '      '09:13'            [   35970.00]
    '     "     '      '09:14'            [   35890.00]
    '     "     '      '09:15'            [   35890.00]
    '     "     '      '09:16'            [   35770.00]
    '     "     '      '09:17'            [   35730.00]
    '     "     '      '09:18'            [   35790.00]
    '     "     '      '09:19'            [   35810.00]
    '     "     '      '09:20'            [   35820.00]

Millisecond

'05 Jan 0116 07:31:41:500'
    '05 Jan 0116 08:59:00:500'
    '05 Jan 0116 09:00:00:500'
    '05 Jan 0116 09:00:01:000'
    '05 Jan 0116 09:00:01:500'
    '05 Jan 0116 09:00:02:000'
    '05 Jan 0116 09:00:02:500'
    '05 Jan 0116 09:00:03:000'
    '05 Jan 0116 09:00:03:500'
    '05 Jan 0116 09:00:04:000'
    '05 Jan 0116 09:00:04:500'
    '05 Jan 0116 09:00:05:000'
    '05 Jan 0116 09:00:05:500'
    '05 Jan 0116 09:00:06:000'
    '05 Jan 0116 09:00:06:500'
    '05 Jan 0116 09:00:07:000'

Further, another issue I notice that it converts 2016 to 0116 while in excel it reads as 2016. Is there a reason for this?

Answer 1

[Peter Perkins]

Unless you're using a pretty old version of MATLAB, you should consider using readtable. For example, using R2017a,

>> t = readtable('rev2.xls','ReadVariableNames',false)
t =
  4601×6 table
              Var1              Var2     Var3               Var4              Var5     Var6 
    ________________________    _____    _____    ________________________    _____    _____
    04-Jan-2016 07:31:41.500        0        0    04-Jan-2016 07:31:41.500        0        0
    04-Jan-2016 08:59:00.500        0    36950    04-Jan-2016 08:59:00.500    35370    36930
    04-Jan-2016 09:00:00.500    34220    36950    04-Jan-2016 09:00:00.500    35370    36930
    04-Jan-2016 09:00:01.000    35860    36410    04-Jan-2016 09:00:01.000    35670    36920
    04-Jan-2016 09:00:01.500    35870    36400    04-Jan-2016 09:00:01.500    35680    36680
    04-Jan-2016 09:00:02.000    35870    36400    04-Jan-2016 09:00:02.000    35760    36530
    04-Jan-2016 09:00:02.500    36000    36330    04-Jan-2016 09:00:02.500    35890    36520
[snip]

And then you could convert that to a timetable.

Depending on what version of MATLAB you have, you may need to tell readtable to read the datetimes, or you may have to read them as strings and convert to dattimes after importing.

[liu James]

Hey Peter, thanks for responding. I am using 2017a. I just tried it and yes it seems that readtable works; however, I'm converting it to a timetable and I'm getting this error.

Error using timetable (line 202)
Provide datetime or duration vector for row times to create timetable.

Error in FTSO (line 9)
u=timetable(Time,t(:,2),t(:,3),'VariableNames',{'Ask','Bid'})

I found that using table2timetable would fix this, but then as I try to combine the two different timetable sets from above (combine var1 and var4 while keeping var 2 and var3 and var5 and var 6 separate) using merge doesn't work nor using setdiff to find the data in A that is not in B, with no repetitions. C is in sorted order.

Error using tabular/setdiff (line 42)
A and B must contain the same variables.

Which this error I don't get since they are tabular and that A and B have time in it.

Is there a way to use merge as I know this function in Matlab while using financial time series would combine the two times and leave the other variables the way it is.

[Peter Perkins]

You've used readtable to create a table, so table2timetable is the right way to get a timetable. In

timetable(Time,t(:,2),t(:,3),'VariableNames',{'Ask','Bid'})

I don't know what Time is, but it looks like that's the problem. In any case, you don't want to use t(:,2), that is a table with one column. You'd want something like t.Ask. I'm guessing that Time is t(:,1), which again is a table, not a datetime. In MATLAB, parenthesis subscripting (usually) preserves the type, and in this case that means t(...) returns a table, whereas you want the data in the table.

But table2timetable is what you really want.

I can't tell what you are asking about merging two timetables. Perhaps that should be a different thread.

Answer 2

[Andrei Bobrov]

a = readtable('rev2.xlsx','ReadVariableNames',false);
T = cell(2,1);
T{1} = a(:,1:3);
T{2} = a(~isnat(a{:,4}),4:end);
T{1}.Properties.VariableNames = {'Time','Ask','Bid'};
T{2}.Properties.VariableNames = {'Time','Ask','Bid'};
TT = cellfun(@(x)table2timetable(x,'RowTimes',1),T,'un',0);
TTout = synchronize(TT{:});