Reading Excel xlsread cell with formula in them

[Dez]

Hi,

Slightly strange problem i need to read a value off of an Excel spread sheet which has a formula in it ( A bloomberg one to be precise )

I can import spread sheet data no problem into matlab using the function xlsread but if there is any formula in the cell it returns NaN.

My question is there anyway to import the actual value of the cell formula into matlab so it just reads it as a number.

I.e =C1+C2 in the cell and if c1 and c2 are both 5 it would import 10.

I do realise i would be better actually importing pure data and recreating the function in matlab but this isn't possible in this particular case.

Thanks for any help.

[TideMan]

Well, that's odd.
Using my version of Matlab (2006a) and Excel 2002, this works fine. The cell with the formula in it returns that numerical value.

[John Smith]

"Dez" wrote in message <jl1uc2$de9$1...@newscl01ah.mathworks.com>...

What version of matlab and excel are you using? Because my matlab (2007b) can import formulas fine from excel file formats 97-2003, 2007 etc.
Have you tried saving your Excel file as a CSV file and importing it like that?

[YUC hu]

I have similar problem lately. Where the cell formula is Bloomberg's BDP function
Simply copy value of the sheet containing BDP function to another worksheet and read with XLSREAD, everything works fine.

"Dez" wrote in message <jl1uc2$de9$1...@newscl01ah.mathworks.com>...

[Dez]

"YUC hu" <k9...@56.com> wrote in message <jmg3m4$d9p$1...@newscl01ah.mathworks.com>...

Thanks for the response! I am no longer getting an error kick back when i try to read the cell it just reads as empty.

subsetA =

[]

Like that. I was thinking is it because i am calculating a live figure on excel? It is constantly updating so possibly it can't read it into matlab.

I basically want matlab to read the figure from the spread sheet and then e-mail it to an external server so it can keep me updated while i am travelling.

[tolst...@gmail.com]

Whenever I read excel data info Matlab, and a cell contains a formula that yields a text value, I get a NaN in Matlab. This happens even if I change the format of the excel sheet cell to text. This way I can never read dynamic texts from Excel, which largely reduces the value of xlsread.

For instance, a cell in Excel contains a formula yielding a text value that I want to read into Matlab to use a header of a figure. The text in Excel adapts automatically when I change the data to reflect some alternative situation.

It happens when using xlsread on Mac OS X. Mac OS X is handicapped enormously form the fact that xlsread on the Mac can only read old 95/5.0 Excel file versions. As long as the Mathworks refuses to update this for its Mac users, we have to live with it. But it is mighty irritating. I hope someone has solved this issue.

Theo Olsthoorn, the Netherlands

[Steven_Lord]

<tolst...@gmail.com> wrote in message
news:20a162b8-1c1a-4d40...@googlegroups.com...

> Whenever I read excel data info Matlab, and a cell contains a formula that
> yields a text value, I get a NaN in Matlab. This happens even if I change
> the format of the excel sheet cell to text. This way I can never read
> dynamic texts from Excel, which largely reduces the value of xlsread.

In order to evaluate that formula, you need to be running Microsoft Excel.
Correct?

As an analogy, I can write the formula:

sin(0)

inside a MATLAB program file. If I tried to read that program file in C++,
all I'll get is the text string "sin(0)". In order to obtain the _value_ of
that expression, I need to evaluate it in MATLAB.

> For instance, a cell in Excel contains a formula yielding a text value
> that I want to read into Matlab to use a header of a figure. The text in
> Excel adapts automatically when I change the data to reflect some
> alternative situation.

The text may change, but only when the file is being processed by Excel and
the formula is evaluated.

> It happens when using xlsread on Mac OS X. Mac OS X is handicapped
> enormously form the fact that xlsread on the Mac can only read old 95/5.0
> Excel file versions. As long as the Mathworks refuses to update this for
> its Mac users, we have to live with it. But it is mighty irritating. I
> hope someone has solved this issue.

As of release R2012b, according to the Release Notes for MATLAB:

"The xlsread function now reads data from XLSM, XLTX, and XLTM files on all
platforms. Previously, this functionality was available only on Microsoft
Windows systems with Excel software."

http://www.mathworks.com/help/matlab/release-notes.html

I don't think that will resolve the problem you're experiencing, though,
which is that you want MATLAB to be able to evaluate Excel "code" as part of
the reading process. On Windows, if you have Microsoft Excel installed,
MATLAB uses COM to interact with Excel and assuming that the file is set to
automatically reevaluate that formula when the file is opened, you would be
able to do what you want. But I'm not certain that approach is possible on
Macs.

--
Steve Lord
sl...@mathworks.com
To contact Technical Support use the Contact Us link on
http://www.mathworks.com

[Marc]

"Steven_Lord" <sl...@mathworks.com> wrote in message <kpn707$e2u$1...@newscl01ah.mathworks.com>...

I apologize if my comment is treating you like a newbie but are you using the following format with xlsread()....

[YourData, YourText] = xlsread();

Any cell with text will give an NaN in YourData and will be shown in YourText.

This works fine for me with Mac OS 10.8.3, Matlab 2012b and Office 2011 for Mac with .xlsx and .xlsm files. By default, I am saving everything as .xlsm, which seems to give me the most consistent results on my Macs. Here column B has the values and column C simply is "=B2...."

tic
[YourData, YourText] = xlsread('TextVsNum.xlsm','Sheet1', 'B2:C7');
toc

Elapsed time is 0.042532 seconds.
>> YourText

YourText =

'Your data' 'Your data'
'My data' 'My data'
'' ''
'' ''
'five' 'five'

>> YourData

YourData =

2 2
3 3
NaN NaN
5 5

[Marc]

"Marc" wrote in message <kpofv5$qoj$1...@newscl01ah.mathworks.com>...

I should have been clear w.r.t saving and consistency with .xlsm.... A lot of my excel files from work contain application based macros to get stuff from databases, historians and analytical equipment, so saving everything as .xlsm has worked best for me across both platforms.

[Marc]

"Marc" wrote in message <kpogk0$s1j$1...@newscl01ah.mathworks.com>...

Another minor point, brought up in several other threads, with 2012b and on, xlsread started shaving off leading and lagging data and text. In the above example, rows 2 through 7 should give "vectors" with 6 rows, but as you can see I ended up with 5 and 4 rows.

This has been a real pain and I am trying to find a link to the "answers" post were you can comment a line out of xlsread to make this fungible with previous matlab versions.

NOT THAT I SUPPORT THAT.... but everyone here are adults and can make there own choices.

[John Wood]

This problem has had me tearing my hair out too!

Using Matlab 2015 adn Excel 2010 for Windows, if I use
values = xlsread(fileXLS,'Sheet2','B456:IX456');
I get an empty vector.

If I use the 'basic' reader, then it gives me the numbers I see on screen when the spreadsheet is open.
[num,txt,raw]= xlsread(fileXLS,'Sheet2','B456:IX456','basic');
numeric & text values are separated into the different output variables.

It seems that xlsread can't deal well with using recent Excel installations directly...

John

"Dez" wrote in message <jl1uc2$de9$1...@newscl01ah.mathworks.com>...