reading dates and time

Hi Struggling with this. Got a clean financial time series csv, reads like this:

03/01/2006,08:00,40.10,40.42

etc. in one minute intervals, with some minutes missing

How do I import this properly so I can plot prices on a simple line graph with dates and times. Tried all the importfunctions and textscan to no avail. Thanks for any help in advance

Al

[reddy]

hi everyone,

I am also having similar problem but my problem is I am having data every two minutes time interval with the format

9/10/2013 4:00 721.19141 4.999999523 0 35.29541 -300 -0.011333227

9/10/2013 4:02 721.67969 4.999999523 0 35.43457 -300 0.561000109 ....

Now i would like to average the data to each hour and save.

Now the problem is there are some data missing. how to overcome this problem??

Answer 1

[Walter Roberson]

thecells = textscan(fid, '%f/%f/%f,%f:%f,%f,%f', 'CollectOutput', 1);
thecells = thecells{1};
times = datenum([thecells(:,3), thecells(:,2), thecells(:,1), thecells(:,4), thecells(:,5), zeros(size(thecells,1),1)]);
price1 = thecells(:,6);
price2 = thecells(:,7);

The times that you would get out would be in serial date number, which is the format most suitable for date plots.

The zeros(size(thecells,1),1) part of the datenum call is to insert 0 as the second for each line, as it happens that the conversion from datevec format (that I am constructing) to datenum needs the seconds fields.

I have guessed in the above that the order is Day Month Year; if it is Month Day Year then exchange the 2 and the 3 in the times calculation.

Answer 2

[alex]

Thx

the first line seems to work, but on the second I get

??? Index exceeds matrix dimensions.

any ideas?

thx Al

[Matt Tearle]

Add thecells = thecells{1}; between the first and second lines

[Walter Roberson]

Matt is correct. I edited that in to my answer.

[Matt Tearle]

That drives me nuts -- seems to defeat the purpose of 'CollectOutput' (IMNSHO).

Answer 3

[Dr. Seis]

Also a neat trick with reading date/time info:

date_str = '03/01/2006,08:00:00,40.10,40.42';
date_serial = datenum(date_str,'mm/dd/yyyy,HH:MM:SS');

See "doc datenum" for more examples!

Answer 4

[alex]

thanks for the numerous replies. still cant get any of these to work. the 'textscan' simply creates an empty matrix and I don't understand what Elige meant??

Sorry beginner question, but this is so simple in excel??? thx in advance if anyone can still walk me through

[bym]

what version of matlab are you using?

[Dr. Seis]

I was just pointing out another way to read date/time information from a string... if you know the ordering of the month,day,year,hour,minute,second and what sort of separator you have between them (i.e., "/" or ":" or "," or etc.) then "datenum" can read that string information without having to pull information out using "textscan". However, you also need other information from your string (the price info) so you would still need to use "textscan" to pull that info out. It was just a helpful hint to figure out the serial date/time with the fewest amount of keystrokes.

Just in case that wasn't what you were asking... typing "doc datenum" (without the quotes) at the command line will bring up the help page for "datenum".

Answer 5

[alex]

7.10.0.499 (R2010a) 64bits !!??!

Answer 6

[Thanh Cao]

I have a text file contain the date and time like this

20180405125901 (2018-April-05 12:59:01)

can matlab load the file and convert it to datetime like this 05-Apr-2018 12:59:01

[Walter Roberson]

I am not completely clear as to whether the '(2018-April-05 12:59:01)' is part of the original file, or if only the 20180405125901 is ?

To convert t = 20180405125901 you can just do arithmetic:

rest = t;
t_sec = mod(rest, 100);
rest = (rest-t_sec)/100;
t_min = mod(rest, 100);
rest = (rest-t_min)/100;
t_hour = mod( rest, 100 );
rest = (rest - t_hour)/100;
t_day = mod(rest, 100);
rest = (rest - t_day)/100;
t_mon = mod(rest, 100);
rest = (rest - t_mon)/100;
t_year = rest;

after which you can use the components to format strings using datestr() or datetime()

Alternately, you can

datetime(sprintfc('%lu', t), 'InputFormat', 'yyyyMMddHHmmss', 'Format', 'dd-MMM-yyyy HH:mm:ss')