How to convert numerical dates to conventional dates

4 views (last 30 days)
I have imported a spreadsheet historic stock prices for a stock (ticker CHK) into Matlab using the importfile function. The goal is to plot the price movements, and volume movements and to show the conventional dates labeled on the x-axis.
When I imported the spreadsheet, the Date column converted to a number (for example August 17, 2011 reads 40772). I converted the numerical date to a conventional date and wrote it into Excel using the datestr function, and the xlswrite function. The conventional date can now be seen on the spreadsheet outside of Matlab; however, the variable cDate (conventional date) in the workspace shows this symbol { }cDate, instead of a grid like the other variables and I can't plot it.
Here are the functions I've used to get this far.
importfile('chk.xls');
cDate = Date;
cDate = x2mdate(cDate, 0);
str = datestr(cDate, 1);
cDt = cellstr( str );
I want the cDt cell array to convert into a double vector of the same size. I.e, in the workspace, all the variables are a double vector of the same size < 254x1 double >, except cDt, which reads < 254x1 cell >. I am not sure what I need to do to accomplish this. Essentially I want to graph a stock price movement from Aug 31, 2011 to Aug 31, 2012 and I want the x-label to read the day, month etc, versus the Matlab serial number.

Accepted Answer

per isakson
per isakson on 1 Sep 2012
Edited: per isakson on 1 Sep 2012
The Excel datetime-number is not the same as the serial date number of Matlab
>> datestr( 40772, 1 )
ans =
18-Aug-0111
>>
The pivot-year of Excel is 1900 and of Matlab is 0. You need to look it up.
The use of "ans" as temporary variable is not a good practice.
datestr(cDate, 1);
cellstr(ans);
cDate = ans;
Someone discovered that it saves a couple of bytes and now it has become common practice in Cody. However, outside Cody this is much better
str = datestr(cDate, 1);
cDate = cellstr( str );
It is easier to debug code if you do not reuse the temporary variables. Thus
str = datestr( cDate, 1 );
cDt = cellstr( str );
I might not have answered your question.
--- In response of comment 1 ---
Matlab documentation says:
Working with Serial Date Numbers
A serial date number represents a calendar date as the number
of days that has passed since a fixed base date.
In MATLAB, serial date number 1 is January 1,0000. MATLAB also
uses serial time to represent fractions of days beginning at
midnight; for example, 6 p.m. equals 0.75 serial days. So the
string '31-Oct-2003, 6:00 PM' in MATLAB is date number 731885.75.
Excel online help says:
Excel stores dates as sequential serial numbers so that they can be
used in calculations. By default, January 1, 1900, is serial number 1,
and January 1, 2008, is serial number 39448 because it is 39,448 days
after January 1, 1900.
.
A test in Matlab
>> datenum('2008-01-01','yyyy-mm-dd')-datenum('1900-01-01','yyyy-mm-dd')
ans =
39446
thus I have to add 2. Now you can calculate and write a number to Excel.
However, you need to set the "Number format" of the cell to "Short date". I don't know how to do that with Matlab code.
  6 Comments
harsh
harsh on 2 Sep 2012
Here is my issue. Your sdn is not a double vector of the same size. If I look in the workspace, your sdn, my cDt is a 254x1 cell, the max and min column is blank. The little picture next to cDt looks like this {cDt} versus 4 squares and cDt. Does this help you visualize it?
per isakson
per isakson on 2 Sep 2012
I would say that my sdn, the name I choose to illustrate the syntax, corresponds to your cDate.

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!