Thread Subject:

trying to read excel in, DATENUM failed

hello,
this was working fine on matlab R2009a , but now having problems when using same code in 7.5.0 (R2007b)

im trying to read in excel file to matlab and getting error below.

i cant figure out why, does anyone know what has changed between these 2 versions so as to make it not work when i use old version , but it works fine on 2009a?

here is the error:

??? DATENUM failed.
Error using ==> dtstr2dtnummx
Failed on converting date string to date number.

Error in ==> MAcodeUtil1 at 118
tday1=datestr(datenum(tday1, 'mm/dd/yyyy'), 'yyyymmdd');

please help me out, thanks

On 06/11/10 12:07 PM, Sean Douglas wrote:

> this was working fine on matlab R2009a , but now having problems when
> using same code in 7.5.0 (R2007b)

> i cant figure out why, does anyone know what has changed between these 2
> versions so as to make it not work when i use old version , but it works
> fine on 2009a?

I have a vague recollection of a bug in datenum a few releases ago, but
I am not certain.

> tday1=datestr(datenum(tday1, 'mm/dd/yyyy'), 'yyyymmdd');

If the format really is fixed to always have two digits for month and
day, then simply:

tday1 = tday1([7:10 1:2 4:5]);

Dear Sean,

> this was working fine on matlab R2009a , but now having problems when using same code in 7.5.0 (R2007b)
> tday1=datestr(datenum(tday1, 'mm/dd/yyyy'), 'yyyymmdd');

DATENUM of Matlab 7.5 has most likely no option to specify the format.
See "help datenum".
Anyhow, the already mentioned direct conversion is much fast at all:
  index = [7:10, 1:2, 4:5];
  tday1 = tday(index);

Kind regards, Jan

Walter Roberson <roberson@hushmail.com> wrote in message <o1gBo.5090$pp4.133@newsfe03.iad>...
> On 06/11/10 12:07 PM, Sean Douglas wrote:
>
> > this was working fine on matlab R2009a , but now having problems when
> > using same code in 7.5.0 (R2007b)
>
> > i cant figure out why, does anyone know what has changed between these 2
> > versions so as to make it not work when i use old version , but it works
> > fine on 2009a?
>
> I have a vague recollection of a bug in datenum a few releases ago, but
> I am not certain.
>
> > tday1=datestr(datenum(tday1, 'mm/dd/yyyy'), 'yyyymmdd');
>
> If the format really is fixed to always have two digits for month and
> day, then simply:
>
> tday1 = tday1([7:10 1:2 4:5]);



Hey guys, thanks for posts, I still cannot get this code to read the excel files into matlab.
I tried the previous suggestion, but i still could not get it to work (btw the format is not always fixed to 2 digit month and day, sometimes the date read in from excel will just have a single digit day or month)
But , the big update I have for you is that I now put MATLAB R2009a onto this computer and I am still getting the same problem. ( I also updated with a windows Netframework 3.5 that was not on this computer before.) However, matlab still cannot read the excel file into matlab, BUT, on my laptop this same exact code works fine, with the same exact version of matlab. So it seems that it might be some difference between my desk top and Laptop.
Does anyone have any suggestions? The error is once again below. I keep googleing the error, but I am not finding an answer to this problem.
Thank you.

??? Error using ==> datenum at 174
DATENUM failed.

Error in ==> MAcodeGearUtil1 at 112
tday1=datestr(datenum(tday1, 'mm/dd/yyyy'), 'yyyymmdd');

Caused by:
    Error using ==> dtstr2dtnummx
    Failed on converting date string to date number.

Sean Douglas:
Maybe we can verify it. Can you upload your workbook somewhere? Also
give your MATLAB code. Was it simply this:
[num, txt, raw] = xlsread(filename) ????
Or did you try to do it with a bunch of ActiveX calls?
I know that I'm reading in fine with R1010b. I don't have the three
year old R2007b version still hanging around so I can't personally
test it but maybe someone else hasn't upgraded yet either. The other
option is to upgrade to the newest version of MATLAB.

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <f9e3609b-8114-4a92-b614-accb12821459@fv1g2000vbb.googlegroups.com>...
> Sean Douglas:
> Maybe we can verify it. Can you upload your workbook somewhere? Also
> give your MATLAB code. Was it simply this:
> [num, txt, raw] = xlsread(filename) ????
> Or did you try to do it with a bunch of ActiveX calls?
> I know that I'm reading in fine with R1010b. I don't have the three
> year old R2007b version still hanging around so I can't personally
> test it but maybe someone else hasn't upgraded yet either. The other
> option is to upgrade to the newest version of MATLAB.

Image i wiill work on doing that. Thanks. But i am using 2009a now, and i was using 2009a on a laptop that i dont have anymore and this was all working then, so it seems that it is not the version, unless it is the version with this computer??? I also changed my excel format so the date would always be dd/mm/yyyy , but still not working.

I will work on your suggestion, thanks

You still haven't said what MATLAB code/function you were using to
read in the workbook. Was it xlsread()?

And are you sure that you're trying to read in an Excel 2007 or
earlier workbook, not an Excel 2010 workbook (in the event that
Microsoft changed something in the workbook formats when they went
from Office 2007 to Office 2010). If you're trying to read in an
Excel 2010 workbook with MATLAB R2009a, then that might possibly be a
problem, since Excel 2010 wasn't around when MATLAB R2009a was
released. What's the extension of the workbook? XL2007 and XL2010
use .xlsx but can write out in the earlier .xls format if requested.
XL2003 used .xls.

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <f9e3609b-8114-4a92-b614-accb12821459@fv1g2000vbb.googlegroups.com>...
> Sean Douglas:
> Maybe we can verify it. Can you upload your workbook somewhere? Also
> give your MATLAB code. Was it simply this:
> [num, txt, raw] = xlsread(filename) ????
> Or did you try to do it with a bunch of ActiveX calls?
> I know that I'm reading in fine with R1010b. I don't have the three
> year old R2007b version still hanging around so I can't personally
> test it but maybe someone else hasn't upgraded yet either. The other


Hey here is my code: the two data sets could be downloaded by going to Yahoo finance, and then: punch in the first stocks: ACI and CHK and then for each stock go to historical data and then download the excel file and make sure date is in the right format ( date seemed to always be in correct format , but now I am trying to check everything)

I will also look into finding a place where I can just post the data.

here is the code to be copy and pasted in mfile:

clear all
addpath('epchancode','cointcode')% ;'EnergyData'
dbstop if error
global ADFconfidence ADFtStat CritValues mode
%stock1='ACI';
%stock2='CHK';
%gear=0; %241:246
start=20080601; %RTN and NOC 10day MA 20070601
upto= 20090101; %20100705
TTthresh=2;
PPT=.04;
HalfLifeStart=20070101;
HalfLifeEnd=20090101;
ADFstart=20060101;
ADFend= 20090101;
HlfLfeThresh = 50;
ADFclThresh = .90;
SratioThresh = .7;
Sthresh=10;
mode=10; % standard mode = some double,set mode='slippage' for i+1 trading
             % 30 DAY GO BACK .5 MOS.
Hratio=0; % 50 DAY GO BACK 1.5 MOS.
MA=150; % FOR 150 DAYS GO BACK 6 MOS. yes
spread1=.15; % FOR 200 DAYS GO BACK 8.5 MOS yes
stoploss=1; rf=0;
spread2=.00; %.043830441781871
spread3=.05; % to re-enter after stop-loss
La=60;

spread1vec=[]; spread2vec=[]; tripsVEC=[]; daysInVEC=[]; totDaysVec=[];
hpReturnsVEC=[]; hprPerTripVEC=[]; annuHPRvec=[]; CumRetVEC=[];
stdPortSHRPvec=[]; sharpeRatioVEC=[]; mxDDvec=[]; mxDDDvec=[]; %ClEndVEC(end+1)=ClEnd;

%fprintf('\n%s\t%g\t\t%s\t%g\t\n','TotalTripThresh:',TTthresh,'Prof/tripThresh:',HPRperTripThresh)%need for macro
fprintf('%s\t%g\t\t%s\t%g\t\n','TotalTripThresh:',TTthresh,'Prof/tripThresh:',PPT)%need for macro


   
 
yyy={'ACI12'}; %atwN ACI
xxx={'CHK12'}; %sii CHK
 n3=length(xxx);
 for i3=1:n3
     stock1=yyy{i3}; %stock 1 = y for regression symetry
     stock2=xxx{i3} ;
 %fprintf('stock1 %s\t stock2 %s\t window: %.0f to %.0f\t \n',stock1,stock2,start,upto);
 fprintf('\n')
 fprintf('stock1\t %s\t stock2\t %s\t\t window:\t %.0f\t to\t %.0f\t \n',stock1,stock2,start,upto);
 fprintf('%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%5s\t%5s\t%s\t%s\t%s\t\n','MA','sprd1','sprd2','trips','profit','prof/trip','annuz prof','cumret','stdev','sharpe','mxDD','mxDDD','aveHP','tdays')
     
     vvv= [.1,.15,.15 ,.20,.20 ,.20 ,.25,.25,.25, .30,.30,.30 ,.30, .35, .35 ]; %
     www= [ 0, 0 ,.05 , 0 ,.05 ,.10 ,.05,.10,.15, .05,.10,.15 ,.20, .15, .20 ]; %
     %vvv= [.1];
     %www= [.05];
       n4=length(vvv) ;
       for i4=1:n4
        spread1=vvv(i4);
        spread2=www(i4);

[num, txt]=xlsread(stock1);
tday1=txt(2:end, 1);
tday1=datestr(datenum(tday1, 'mm/dd/yyyy'), 'yyyymmdd');
tday1=str2double(cellstr(tday1));
adjcls1=num(:, end);

[num, txt]=xlsread(stock2);
tday2=txt(2:end, 1);
tday2=datestr(datenum(tday2, 'mm/dd/yyyy'), 'yyyymmdd');
tday2=str2double(cellstr(tday2));
adjcls2=num(:, end);
[tday, idx1, idx2]=intersect(tday1, tday2);

cl1=adjcls1(idx1);
cl2=adjcls2(idx2);
[tday, idx1, idx2];
wholevect=[tday,cl1,cl2]
a=[tday,cl1]
       end
 end




thanks

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <c57e86d2-47c1-473b-8b2d-0a631cbbef0c@g7g2000vbl.googlegroups.com>...
> You still haven't said what MATLAB code/function you were using to
> read in the workbook. Was it xlsread()?
>
> And are you sure that you're trying to read in an Excel 2007 or
> earlier workbook, not an Excel 2010 workbook (in the event that
> Microsoft changed something in the workbook formats when they went
> from Office 2007 to Office 2010). If you're trying to read in an
> Excel 2010 workbook with MATLAB R2009a, then that might possibly be a
> problem, since Excel 2010 wasn't around when MATLAB R2009a was
> released. What's the extension of the workbook? XL2007 and XL2010
> use .xlsx but can write out in the earlier .xls format if requested.
> XL2003 used .xls.


Ok I just sent you the code in my previous post ( we crossed posted) , you might be onto something. I just realized even though i have excel 2007 on here that these particular files are excel 2003 for some strange reason??? I dont know why, even before i transferred everthing to this computer is was all excel 2007. Anyways my excel data is excel 97 to 2003 worksheet. however seems from you post above you think this woud work . I will start playing around with this and see..

sorry for all the posting wanted to let you know you may be on to something

On Nov 14, 10:50 pm, "Sean Douglas" <seanjdoug...@hotmail.com> wrote:
> [num, txt]=xlsread(stock2);
> tday2=txt(2:end, 1);
> tday2=datestr(datenum(tday2, 'mm/dd/yyyy'), 'yyyymmdd');
> thanks
---------------------------
Take a look at tday2. Is it a string? Or a cell array? I think it
might be a cell array of end-1 cells when what datenum wants is a
string. If it wants a string you need to get the *contents* of the
cell, meaning you need to use braces instead of parentheses. But I
haven't really gone over all the input options of datenum() in detail.

ImageAnalyst <imageanalyst@mailinator.com> wrote in message <be8c5396-c7e1-4ed3-b74f-7c55d787400f@k5g2000vbn.googlegroups.com>...
> On Nov 14, 10:50 pm, "Sean Douglas" <seanjdoug...@hotmail.com> wrote:
> > [num, txt]=xlsread(stock2);
> > tday2=txt(2:end, 1);
> > tday2=datestr(datenum(tday2, 'mm/dd/yyyy'), 'yyyymmdd');
> > thanks
> ---------------------------
> Take a look at tday2. Is it a string? Or a cell array? I think it
> might be a cell array of end-1 cells when what datenum wants is a
> string. If it wants a string you need to get the *contents* of the
> cell, meaning you need to use braces instead of parentheses. But I
> haven't really gone over all the input options of datenum() in detail

Hey ImageAnalyst, thanks for your help.... tday2 I am pretty sure is a string (at date strings), in like the next line of the code it is converted itto cells .
however, you are right the problem is right there. But again this was all working before on a different computer, I am thinking it has something today with excel or something on this computer interfering with matlab as it reads excel?

i have to head in for the night,
thanks again imageAnalyst

"Sean Douglas" <seanjdouglas@hotmail.com> wrote in message
news:ibqfu4$g7p$1@fred.mathworks.com...
> ImageAnalyst <imageanalyst@mailinator.com> wrote in message
> <be8c5396-c7e1-4ed3-b74f-7c55d787400f@k5g2000vbn.googlegroups.com>...
>> On Nov 14, 10:50 pm, "Sean Douglas" <seanjdoug...@hotmail.com> wrote:
>> > [num, txt]=xlsread(stock2);
>> > tday2=txt(2:end, 1);
>> > tday2=datestr(datenum(tday2, 'mm/dd/yyyy'), 'yyyymmdd');
>> > thanks
>> ---------------------------
>> Take a look at tday2. Is it a string? Or a cell array? I think it
>> might be a cell array of end-1 cells when what datenum wants is a
>> string. If it wants a string you need to get the *contents* of the
>> cell, meaning you need to use braces instead of parentheses. But I
>> haven't really gone over all the input options of datenum() in detail
>
> Hey ImageAnalyst, thanks for your help.... tday2 I am pretty sure is a
> string (at date strings), in like the next line of the code it is
> converted itto cells .

Don't be "pretty sure", be _certain_. Set a breakpoint on that line of code
and determine that tday2 is what you think it is.

http://www.mathworks.com/help/techdoc/matlab_env/brqxeeu-175.html

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