Excel: Column Headers & Dates

146 views (last 30 days)
Ryan
Ryan on 19 Apr 2012
Commented: Walter Roberson on 17 Jan 2016
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???

Answers (6)

per isakson
per isakson on 19 Apr 2012
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.

owr
owr on 19 Apr 2012
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.

Ryan
Ryan on 20 Apr 2012
Edited: John Kelly on 2 Mar 2015
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.

Image Analyst
Image Analyst on 20 Apr 2012
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.

Ryan
Ryan on 20 Apr 2012
Yes! Of course! I should have done that originally.
  1 Comment
Image Analyst
Image Analyst on 20 Apr 2012
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.

Sign in to comment.


Ryan
Ryan on 20 Apr 2012
Edited: John Kelly on 2 Mar 2015
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.
  3 Comments
Walter Roberson
Walter Roberson on 17 Jan 2016
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.)

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by