How to convert numerical dates to conventional dates

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.

Answer 1

[per isakson]

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.

[harsh]

Thanks for your response.

This definitely helps me recognize best practices with Matlab, but the issue still remains. I have no idea how to convert the cellstr function back into the excel column to chart the actual dates.

In terms of the datestr, what do I need to look up and where do I go to look it up?

[harsh]

Ok so I converted it correctly so it equals 8/31/2012. The function I used was x2mDate, and then datestr to convert the x2mdate into a conventional date.

Now I have a cell array and I can't figure out how to plot it against my data which is in "double" format.

[harsh]

To clarify x2mdate basically converts the Excel 1900 base date to Matlab 0000 base date, then you can convert that to a conventional string using datestr

[per isakson]

I not sure I understand. You don't need a cell array. Read the documentation on the two functions, plot and datetick. Note the syntax of plot

    plot( sdn, data )

where sdn and data are double vectors of the same size. sdn is a serial date number and represents date and time. sdn is the output of x2mdate. (I don't have the finacial toolbox.)

[harsh]

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]

I would say that my sdn, the name I choose to illustrate the syntax, corresponds to your cDate.