Quantitative finance collector
C++ Matlab VBA/Excel Java Mathematica R/Splus Net Code Site Other
May 5

Dealing with Excel Date in Matlab

Posted by abiao at 10:21 | Code » Matlab | Comments(6) | Reads(17735)
I was drived crazy yesterday by dealing with the date in Matlab. I have to download data from different sources, some from datastream, and the others from Wharton Research Data Services, unfortunately, the date is in different format, either in a double format as 20081125, or in a cell format as '25/11/2008' (or something similar) after I import the data with xlsread() command in Matlab.
Open in new window

those different formats really bring a problem, as I need to match the dates and compare the prices, it is easy to convert a cell format date to what matlab recognizes using cell2mat() and then datenum(), for instance, matlab returns a number 733737 for '25/11/2008', however, how can I compare that with 20081125 then?

what I am thinking is:
first, convert the cell format to string using cell2mat(), it becomes a string 25/11/2008;
second, reformat the string using datestr(,26), it becomes a string 2008/11/25;
third, remove '/' by using find(b~='/'), it becomes a string 20081125;
finally, convert the string to number using str2num(), it is eventually a double number I need 20081125, then I am able to use and match the date from different sources.

It sounds lengthy, do you have a better idea? please share with us by leaving a comment, cheers.

Stanley suggests to use the following code: datenum(datevec(num2str(dates),'yyyymmdd')), here dates is double number, perfect, it is much better than my method as it allows us not only for date comparison but also for calculation, what's more, it is more efficient. many thanks, stanley.

Tags: ,
thk you for your QF blog, it really bring me a lot
about your problem, can't excel format the date into yyyymmdd??
I have some suggestions for one of your recent post, which is Dealing with Excel Date in Matlab. I found the online comment system failed to work, so I just send u an email. The suggestion is the following:

After doing all these conversion, u can only make comparisons for the data, but not calculation and etc.

What u can do is the other way around. Change the 20081125 back to the matlab time format, which is the number 733737.

The command is surprisingly simple:

This format not only allows you do calculation among dates, but also serves as a standard input to matlab functions, for example, impvbybls.
wenchao, yes, but even after that, it is still a cell or string after xlsread() in format 'dd/mm/yyyy'. thanks.
stanley, sorry, I didn't realize the comment was down, now I fix it, sorry for the trouble.
yeah, you are probably right, I will check again tomorrow and go back to you, thanks.
Stanley, since 20081125 is recognized as a double number by matlab, I can't convert it to Matlab date format as you suggested. datenum() only convert string.
You are my saving saint! that line of code saved me so much time!
Pages: 1/1 First page 1 Final page
Add a comment
Enable HTML
Enable UBB
Enable Emots
Nickname   Password   Optional
Site URI   Email   [Register]