Excel: Column Headers & Dates

A short time ago, I was fiddling around with some methods of importing data from an Excel file, specifically, stock prices. I found some useful information here: http://www.mathworks.com/help/matlab/ref/xlsread.html

Of all the techniques listed, I didn't find any that would import column headers; you just have all the values in lots of rows and columns, but no way to identify which stock is which. Also, dates seemed to become Text, and when I tried to plot the nameless stocks (column headers just fell off), there was no dates on the X-axis. The version that I was using before; R2010b. I just saw a video demo online; it was definitely a newer version of Matlab. In the new version, it seems like all data is imported automatically; no issues at all. Dates come in fine; column headers show up fine. Whenever I tried to import any stock data, the 'create vectors from each column using column names' was ALWAYS greyed out. It can't be so difficult. Any idea what was going wrong???

Answer 1

[per isakson]

The documentation (R2012a) says:

    [num,txt,raw] = xlsread(filename)
    ...
    [num,txt,raw] = xlsread(filename,sheet,range)
    ...

I'm sure this has not changed during the last few years. The text, e.g. column headers, of the excel-file appears in both the second, i.e. txt, and third, i.e. raw, output argument.

Beware: Headers (of type General in Excel) with numeric values, e.g. 1234, confuses xlsread (and sometimes me). Row headers, i.e. text or datetime in the first column, cause the column indices of num and txt not to line up. In this case it might be more robust to use the third output argument

    [~,~,raw] = xlsread(filename)

Make some experiments with a small xls-file.

--- EDIT in response to Answer 6 ---

I have downloaded stock_data.xls and run the following six lines of code line by line.

    file_spec           = 'h:\m\cssm\stock_data.xls';
    [ num, txt, raw ]   = xlsread( file_spec );
    whos
    num( 1:3, : )
    txt{1,:}
    raw{1,:}

The results are:

    >> whos
      Name             Size             Bytes  Class     Attributes
      file_spec        1x24                48  char                
      num            575x4              18400  double              
      raw            576x5             352498  cell                
      txt            576x5             334098  cell

The size of the numerical array, num, is smaller than that of the two cell arrays.

    >> num( 1:3, : )
    ans =
       30.8100    4.4500   71.9400   61.6700
       30.9800    4.2900   72.2200   60.6300
       30.3500    4.1900   72.1200   59.2900

The variable, num, only holds numeric data and the column and row indices differs from those in the Excel-sheet.

    >> txt{1,:}
    ans =
    Dates
    ans =
    MSFT
    ans =
    PWER
    ans =
    KO
    ans =
    SBUX

The first row of txt contains the headers! The first row of raw is identical to that of txt.

    >> txt{2:3,1}
    ans =
    2012-04-13
    ans =
    2012-04-12

The first column of txt contains the dates. The specific date format depends on the "localization" of Windows.

I run this in with R2012a on Windows7 with Excel installed. The result will be the same with R2010b and previous versions of Windows and Excel.

Answer 2

[owr]

The latest version of MATLAB (2012A) seems to have some better support. I havent personally played with this yet though.

If you are dealing with nicely formatted Excel files where each column has a header and you have heterogeneous columns (mixed type - like text for dates and numeric for price data), check out the "dataset arrays" in the Statistics Toolbox (if you have access to it). The dataset array class has a constructor that can read directly from Excel (or any delimited text file) that works great for this sort of thing.

Answer 3

[Ryan]

Ah! Progress has been made! I just tried this: data = dataset('xlsfile','stock_data.xls');

Nevertheless, I still can't seem to plot this data!!

I tried this before posting my question: [num,txt,raw] = xlsread('stock_data.xls','stocks','A1:E576')

Just tried it again; it still doesn't give me headers, but I can add them in, when you edit the chart properties. It's a bit of work to do that though, and I wouldn't like to do it for dozens and dozens of stocks. Also, the dates come in as text and these do not corresponding to the stock price data. It's in a totally separate data set in my 'Workspace'. Is this how it's supposed to be done? I can't join the dates to the stock prices.

Thanks for the help so far.

Answer 4

[Image Analyst]

I have no idea what your data looks like. Can you give the header row (with the column headers in it) and a couple of rows of actual data, or else (even better) post your workbook somewhere online that we can download. Then we'd be able to try it and fix it for you.

Answer 5

[Ryan]

Yes! Of course! I should have done that originally.

http://www.mediafire.com/view/?8k7ztoaaj82dqtj

[Image Analyst]

When you have a response to an answer, please add it on as a "Comment on this Answer" so we know whom you're talking to.

Answer 6

[Ryan]

There must be some kind of setting that isn't quite right. I just watched the video tutorial about Excel and Matlab: MATLAB for Excel Users: http://www.mathworks.com/videos/matlab-for-excel-users-81634.html

I created some sample data with dates in ColumnA and Hours as the field headers: ColumnB-ColumnY. Now, as I try to import the data into Matlab, from Excel, one of the radio buttons is still greyed out: ‘Create vectors from each column using column names’. I click OK, to finish the import, and the dates and headers are in ‘textdata’ and the values are in ‘data’. When I try to plot any of this, I find that I can’t plot anything with a date, and the columns with values have no headers. I don’t get it. I’ve been doing VBA programming for Excel and Access for almost 10 years, and SQL Server and C# for three years; none of those tools cut off dates and headers. It seems like once the data gets into the Matlab workspace, it is basically useless.

If I use this: data = dataset('xlsfile','Energy_Reading.xls'); I can’t plot anything.

If I use this: [num,txt,raw] = xlsread('Energy_Reading.xls','Energy','A1:Y365') I can’t plot anything.

Here's a small file with random numbers; just trying to replicate the example in the video tutorial, to some extent: http://www.mediafire.com/view/?a90pp0w1633yslc

Maybe it is supposed to be like this; I don’t know. It doesn’t seem intuitive, at all. Also, in the few video tutorials that I’ve seen, the data imports perfectly fine, and ‘Create vectors from each column using column names’ is NEVER greyed out. Mine is ALWAYS greyed out.

[per isakson]

I have added an example based on stock_data.xls to my answere above

[Eshu Jain]

Hi

I am also facing a similar issue. I have data of 50 stocks closing price for entire year 2015. Now using [num, txt, raw] i could get the complete data with headers in raw. However I want to know how to store this data in the a matrix of different name. Or is the by default matrix for the entire data with header names is 'raw' ?

[Walter Roberson]

Eshu, if you are using R2014b or later, I recommend that you use readtable() to read the XLS file into a MATLAB table() data structure. (Table() data structures exist from R2013b but readtable was not created at the same time.)