Thread Subject:

What is the best way to import Excel files, with stock data, into Matlab? I’m using Matlab R2010b (64-bit)

What is the best way to import Excel files, with stock data, into Matlab? I’m using Matlab R2010b (64-bit)

I’m trying to import historical prices of stock data. Matlab keeps cutting off the header of each column; it gives me only values. I am thinking that this is a data-type-mixing-issue, or whatever it’s called.

I have ‘Dates’ in ColumnA, and ‘MSFT’, ‘PWER’, ‘KO’, and ‘SBUX’, in ColumnB-ColumnE.

Basically, if I put the Excel file in my Matlab folder and double-click the file, I see the values in a matrix (no dates and no headers), I hit ‘Next’. Then, I see only one option; one radio button is enabled – ‘Create variable matching preview’. The other two options, ‘Create vectors from each column using column names’ and ‘Create vectors from each row using row names’ – both are greyed out. I’ve seen videos, on YouTube and on the Matlab site, where people import excel data and these options are NOT greyed out. No matter what I do, there options are ALWAYS greyed out. The problem is, when I click ‘Finish’ in my ‘Workspace’ I have a variable named ‘data’ all values in a 575x4 matrix, but I have NO DATES and I have NO HEADERS on the columns. All of this seems to go into another variable, called ‘textdata’; all dates and headers ate in textdata. This is VERY inconvenient. I’m trying to
analyze some stock data and I have no headers, so I have no idea which stock is which (I can figure it out, but I think Matlab should do this for me). Also, I have no dates corresponding to the historical stock prices. This is VERY weird. How can I pull in test and values together?

I have a sample file here:
http://www.mediafire.com/view/?8k7ztoaaj82dqtj

On Friday, April 27, 2012 8:08:07 AM UTC+12, Ryan wrote:
> What is the best way to import Excel files, with stock data, into Matlab? I’m using Matlab R2010b (64-bit)
>
> I’m trying to import historical prices of stock data. Matlab keeps cutting off the header of each column; it gives me only values. I am thinking that this is a data-type-mixing-issue, or whatever it’s called.
>
> I have ‘Dates’ in ColumnA, and ‘MSFT’, ‘PWER’, ‘KO’, and ‘SBUX’, in ColumnB-ColumnE.
>
> Basically, if I put the Excel file in my Matlab folder and double-click the file, I see the values in a matrix (no dates and no headers), I hit ‘Next’. Then, I see only one option; one radio button is enabled – ‘Create variable matching preview’. The other two options, ‘Create vectors from each column using column names’ and ‘Create vectors from each row using row names’ – both are greyed out. I’ve seen videos, on YouTube and on the Matlab site, where people import excel data and these options are NOT greyed out. No matter what I do, there options are ALWAYS greyed out. The problem is, when I click ‘Finish’ in my ‘Workspace’ I have a variable named ‘data’ all values in a 575x4 matrix, but I have NO DATES and I have NO HEADERS on the columns. All of this seems to go into another variable, called ‘textdata’; all dates and headers ate in textdata. This is VERY inconvenient. I’m trying to
> analyze some stock data and I have no headers, so I have no idea which stock is which (I can figure it out, but I think Matlab should do this for me). Also, I have no dates corresponding to the historical stock prices. This is VERY weird. How can I pull in test and values together?
>
> I have a sample file here:
> http://www.mediafire.com/view/?8k7ztoaaj82dqtj

Why not revert to the old-fashioned way:
[a,b]=xlsread(xlsfile);
will give the numerical data in a matrix a and everything else as a cell array in b.

Do you have the Statistical Toolbox?

On 4/26/2012 4:08 PM, Ryan wrote:
> What is the best way to import Excel files, with stock data, into
> Matlab? I’m using Matlab R2010b (64-bit)
>
> I’m trying to import historical prices of stock data. Matlab keeps
> cutting off the header of each column; it gives me only values. I am
> thinking that this is a data-type-mixing-issue, or whatever it’s called.
>
> I have ‘Dates’ in ColumnA, and ‘MSFT’, ‘PWER’, ‘KO’, and ‘SBUX’, in
> ColumnB-ColumnE.

Ryan, I don't know how you'll want to work with these data after you get
them into MATLAB, but if you have access to the Statistics Toolbox, this
kind of file is simple to read with the dataset constructor. Something like

 >> stocks = dataset('XLSFile','stock_data.xls');
 >> summary(stocks)
Dates: [575x1 cell string]
MSFT: [575x1 double]
     min 1st quartile median 3rd quartile max
     23.01 25.37 26.5 28.53 32.85
PWER: [575x1 double]
     min 1st quartile median 3rd quartile max
     3.1 4.625 7.43 8.9575 12.78
KO: [575x1 double]
     min 1st quartile median 3rd quartile max
     50.03 55.85 64.74 67.767 74.14
SBUX: [575x1 double]
     min 1st quartile median 3rd quartile max
     21.7 25.9 33.25 39.888 61.67
 >> stocks(stocks.MSFT>32.5,:)
ans =
     Dates MSFT PWER KO SBUX
     '3/27/2012' 32.52 4.86 71.87 56.26
     '3/26/2012' 32.59 4.74 71.9 55.91
     '3/16/2012' 32.6 4.69 70.16 53.21
     '3/15/2012' 32.85 4.38 70.33 53.07
     '3/14/2012' 32.77 4.18 70.22 52.68
     '3/13/2012' 32.67 4.39 70.25 52.34
 >> plot(datenum(stocks.Dates),stocks.MSFT,'o')
 >> [y,m] = datevec(stocks.Dates);
 >> stocks.Year = y; stocks.Month = m;
 >> stocks2012 = stocks(stocks.Year==2012,:);
 >> grpstats(stocks2012,{'Year' 'Month'},{'min'
'max'},'DataVars',{'MSFT' 'PWER'})
ans =
          Year Month GroupCount min_MSFT max_MSFT min_PWER max_PWER
  2012_1 2012 1 20 26.77 29.73 3.98 4.95
  2012_2 2012 2 20 29.89 31.87 4.34 5.51
  2012_3 2012 3 22 31.56 32.85 4.1 4.86
  2012_4 2012 4 9 30.35 32.29 4.06 4.48

And so on. Hope this helps.

Thanks so much! I tried this:
stocks = dataset('XLSFile','stock_data.xls');

That let me import the date; array, or variable name (not exactly sure what to call it) is 'stocks'. It definitely looks good. however, I can't plot the data. I selected this item, and tried to plot it, but got a message that reads, 'No plot available for current selection'.

Basically, I think my question boils down to this. I can import the data, with no indication of which stock is which, and no dates, but the plots work perfect. I can import the data with dates and headers and values, but I can't PLOT anything.

There must be a simple thing that I'm missing. There must be a simple work-around, right.

I'd really like see the prices on the Y-Axis and see the dates on the X-Axis. How can I do that? it must be simple, right.

Thanks, everyone!!


"Ryan" wrote in message <jnc9v7$1ot$1@newscl01ah.mathworks.com>...
> What is the best way to import Excel files, with stock data, into Matlab? I’m using Matlab R2010b (64-bit)
>
> I’m trying to import historical prices of stock data. Matlab keeps cutting off the header of each column; it gives me only values. I am thinking that this is a data-type-mixing-issue, or whatever it’s called.
>
> I have ‘Dates’ in ColumnA, and ‘MSFT’, ‘PWER’, ‘KO’, and ‘SBUX’, in ColumnB-ColumnE.
>
> Basically, if I put the Excel file in my Matlab folder and double-click the file, I see the values in a matrix (no dates and no headers), I hit ‘Next’. Then, I see only one option; one radio button is enabled – ‘Create variable matching preview’. The other two options, ‘Create vectors from each column using column names’ and ‘Create vectors from each row using row names’ – both are greyed out. I’ve seen videos, on YouTube and on the Matlab site, where people import excel data and these options are NOT greyed out. No matter what I do, there options are ALWAYS greyed out. The problem is, when I click ‘Finish’ in my ‘Workspace’ I have a variable named ‘data’ all values in a 575x4 matrix, but I have NO DATES and I have NO HEADERS on the columns. All of this seems to go into another variable, called ‘textdata’; all dates and headers ate in textdata. This is VERY inconvenient. I’m trying to
> analyze some stock data and I have no headers, so I have no idea which stock is which (I can figure it out, but I think Matlab should do this for me). Also, I have no dates corresponding to the historical stock prices. This is VERY weird. How can I pull in test and values together?
>
> I have a sample file here:
> http://www.mediafire.com/view/?8k7ztoaaj82dqtj

Thanks, TideMan. I tried this:
[a,b] = xlsread('XLSFile','stock_data.xls');

I got this error message:
??? Error using ==> xlsread at 219
XLSREAD unable to open file XLSFile.
File C:\Program Files\MATLAB\XLSFile.xls not found.

I need to look into this a bit more.

Basically, I think my question boils down to this. I can import the data, with no indication of which stock is which, and no dates, but the plots work perfect. I can import the data with dates and headers and values, but I can't PLOT anything.

There must be a simple thing that I'm missing. There must be a simple work-around, right.

I'd really like see the prices on the Y-Axis and see the dates on the X-Axis. How can I do that? it must be simple, right.

Thanks, everyone!!

"per isakson" wrote in message <jncmr3$o02$1@newscl01ah.mathworks.com>...
> Do you have the Statistical Toolbox?

On Friday, April 27, 2012 3:19:08 PM UTC+12, Ryan wrote:
> Thanks, TideMan. I tried this:
> [a,b] = xlsread('XLSFile','stock_data.xls');
>
> I got this error message:
> ??? Error using ==> xlsread at 219
> XLSREAD unable to open file XLSFile.
> File C:\Program Files\MATLAB\XLSFile.xls not found.
>
> I need to look into this a bit more.
>
> Basically, I think my question boils down to this. I can import the data, with no indication of which stock is which, and no dates, but the plots work perfect. I can import the data with dates and headers and values, but I can't PLOT anything.
>
> There must be a simple thing that I'm missing. There must be a simple work-around, right.
>
> I'd really like see the prices on the Y-Axis and see the dates on the X-Axis. How can I do that? it must be simple, right.
>
> Thanks, everyone!!
>
> "per isakson" wrote in message <jncmr3$o02$1@newscl01ah.mathworks.com>...
> > Do you have the Statistical Toolbox?

Try this:
[a,b] = xlsread('stock_data.xls');

TideMan <mulgor@gmail.com> wrote in message <26075409.1291.1335501386128.JavaMail.geo-discussion-forums@pbsw19>...
> On Friday, April 27, 2012 3:19:08 PM UTC+12, Ryan wrote:
> > Thanks, TideMan. I tried this:
> > [a,b] = xlsread('XLSFile','stock_data.xls');
> >
> > I got this error message:
> > ??? Error using ==> xlsread at 219
> > XLSREAD unable to open file XLSFile.
> > File C:\Program Files\MATLAB\XLSFile.xls not found.
> >
> > I need to look into this a bit more.
> >
> > Basically, I think my question boils down to this. I can import the data, with no indication of which stock is which, and no dates, but the plots work perfect. I can import the data with dates and headers and values, but I can't PLOT anything.
> >
> > There must be a simple thing that I'm missing. There must be a simple work-around, right.
> >
> > I'd really like see the prices on the Y-Axis and see the dates on the X-Axis. How can I do that? it must be simple, right.
> >
> > Thanks, everyone!!
> >
> > "per isakson" wrote in message <jncmr3$o02$1@newscl01ah.mathworks.com>...
> > > Do you have the Statistical Toolbox?
>
> Try this:
> [a,b] = xlsread('stock_data.xls');


Thanks again, TideMan. It lets me import the data. Again, the text and values are in separate arrays; 'a' contains values and 'b' contains dates and text (my 5 headers). Maybe that is the way it is supposed to be. I'm really new to this. I'm still unable to get dates on the X-axis. I tried to modify the 'X Limits'. I can't add this 1/1/2011 (Matlab tries to divide the numbers, I think). I can't add this '1/1/2011' (Matlab says I need to enter a numeric value). Also, manually adding dates to define a range of stock prices seems kind of weird. Why would a user want to do that? Shouldn't Matlab handle this automatically? I can imagine there must be a lot of input error, and a great waste of time if you need to do this repeatedly.

On 4/26/2012 11:14 PM, Ryan wrote:
> plot the data. I selected this item, and tried to plot it, but got a
> message that reads, 'No plot available for current selection'.


You're right, in R2010b, this isn't available (it is available from
within the Variable Editor in R2012a). But still, from the command
line, you can make any plot you want. This is the code I suggested
earlier, with the addition of dateticks to pretty up the horizontal axis.

    plot(datenum(stocks.Dates),stocks.MSFT,'o');
    datetick('x',1,'keepticks');

Hope this helps.

Peter Perkins <Peter.Remove.Perkins.This@mathworks.com> wrote in message <jne71p$sfl$1@newscl01ah.mathworks.com>...
> On 4/26/2012 11:14 PM, Ryan wrote:
> > plot the data. I selected this item, and tried to plot it, but got a
> > message that reads, 'No plot available for current selection'.
>
>
> You're right, in R2010b, this isn't available (it is available from
> within the Variable Editor in R2012a). But still, from the command
> line, you can make any plot you want. This is the code I suggested
> earlier, with the addition of dateticks to pretty up the horizontal axis.
>
> plot(datenum(stocks.Dates),stocks.MSFT,'o');
> datetick('x',1,'keepticks');
>
> Hope this helps.


Thanks for the follow up, Peter. I saw a video tutorial of importing excel data using R2012a; it seems so easy to do it now. Since I'm working with a slightly older version, I suppose I have to do a little more work to get the results I want. That's fine. I have something to look forward to whenever I can afford to do an upgrade!!

I just tried this:
[a,b] = xlsread('stock_data.xls');
plot(datenum(stocks.Dates),stocks.MSFT,'o');
datetick('x',1,'keepticks');

The code pretty much makes sense, but generates this error:
??? Undefined function or variable 'stocks'.

I tried this:
[a,b] = xlsread('stock_data.xls');
plot(datenum(b.Dates),b.MSFT,'o');
datetick('x',1,'keepticks');
??? Attempt to reference field of non-structure array.

'b' is the text and 'a' is the value. I've gotta get some better naming conventions as soon as I get this working!

Also, how would I do it for all 4 stocks???

Would it be something like this?
[a,b] = xlsread('stock_data.xls');
plot(datenum(stocks.Dates),stocks.MSFT,'o');
plot(datenum(stocks.Dates),stocks.PWER,'o');
plot(datenum(stocks.Dates),stocks.KO,'o');
plot(datenum(stocks.Dates),stocks.SBUX,'o');
datetick('x',1,'keepticks');

On 4/27/2012 10:12 AM, Ryan wrote:
> I just tried this:
> [a,b] = xlsread('stock_data.xls');
> plot(datenum(stocks.Dates),stocks.MSFT,'o');
> datetick('x',1,'keepticks');
>
> The code pretty much makes sense, but generates this error:
> ??? Undefined function or variable 'stocks'.

Right, you haven't created a variable called stocks. The code I
suggested presumed you had executed the code from my earlier post, i.e.,

stocks = dataset('XLSFile','stock_data.xls');

That line creates what's called a dataset array. The rest of the code
demonstrated a few things: selecting a subset from a dataset array,
creating a new dataset variable based on existing ones, plotting two
dataset variables, computing statistics on groups in the data. The
documentation for the Statistics Toolbox has all of this in the User Guide.

Peter Perkins <Peter.Remove.Perkins.This@mathworks.com> wrote in message <jnejfk$pba$1@newscl01ah.mathworks.com>...
> On 4/27/2012 10:12 AM, Ryan wrote:
> > I just tried this:
> > [a,b] = xlsread('stock_data.xls');
> > plot(datenum(stocks.Dates),stocks.MSFT,'o');
> > datetick('x',1,'keepticks');
> >
> > The code pretty much makes sense, but generates this error:
> > ??? Undefined function or variable 'stocks'.
>
> Right, you haven't created a variable called stocks. The code I
> suggested presumed you had executed the code from my earlier post, i.e.,
>
> stocks = dataset('XLSFile','stock_data.xls');
>
> That line creates what's called a dataset array. The rest of the code
> demonstrated a few things: selecting a subset from a dataset array,
> creating a new dataset variable based on existing ones, plotting two
> dataset variables, computing statistics on groups in the data. The
> documentation for the Statistics Toolbox has all of this in the User Guide.

That works!! Thanks so much, Peter! I'm going to have to look at the documentation for the Statistics Toolbox. That's the only way I'm going to learn this stuff.

Regards.