Re: [office] Excel 2007 != Ecma spec YEARFRAC. Not even slightly. What should we do?

[Patrick Durusau <patrick@durusau.net>]

Rob,

robert_weir@us.ibm.com wrote:
>
> For some of these bases, the behavior was intended to follow some 
> external authority, right?  One basis was using an NASD definition, 
> for example.  Another was for the European bourses, etc.  
>
> So I see three choices:
>
> 1) One would be to use the external authorities, and consult their 
> definitions and define our functions to match theirs.  So OpenFormula 
> would be correct and current to those authorities.  This approach is 
> logical and defensible.  If I were running a publicly traded company 
> subject to Sarbannes-Oxley, I'd certainly want my financial functions 
> to give the correct answer.
>
Well, except that the definitions by the "authorities" are at least as 
vague and/or ambiguous as those of #2. If such "authorities" did have 
complete definitions it would be the "logical and defensible" approach. 
Unfortunately, I don't think those have ever been defined.
> 2) Another approach would be to match what OOXML does, which may be 
> ambiguous, may not match the relevant external authority's 
> definitions, and may not even be what Excel does.  I think we've found 
> that this approach not to be workable.
>
True. OOXML needs to be corrected.
> 3) Another approach would be to figure out what Excel actual does.  My 
> assumption is that Excel started from an external authority, circa 
> 1985, and what we have today is that, with possibly some 
> implementation bugs layered on top.  The external authorities may have 
> also changed their definitions since 1985 as well.  
>
I think this is important, even if we choose another option (see next).

I would mention a fourth option:

4) Work with MS to reach a public agreement on (A) What Excel 2007 does 
now. (Important for compatibility reasons.) and (B) What the future 
definitions should be in both ODF and OOXML.

I don't think there is any dispute that we don't have sufficient 
definitions now and that such definitions would be a benefit to 
everyone. Granted as both you and Dave point out,  there may be other 
definitions that can be included at some later point but the critical 
thing now is to get the most commonly used ones defined.

Hope you are having a great day!

Patrick
> However we do this, I'd recommend an exhaustive, brute force test.   
> Assuming we are willing to ignore the 1900 leap year weirdness in 
> Excel, we should be able to take any range of 4 years + 1 day and 
> calculate all possible date intervals.  So (365+365+365+366+1)^2 = 
> 2,137,444 test cases by brute force.  If you do this in a grid, you 
> get a 23 MB XLSX file (sorry, Excel 2007 format needed to handle the 
> 1400+ columns.)
>
> The basis value (currently 0) is set in cell A1.  If you can make your 
> Python script output a similar grid, in CSV format, you could load it 
> into a second sheet in the workbook and then have a third sheet 
> calculate the delta.
>
> I think this is similar to what you did before, but there may be some 
> advantage to doing an exhaustive enumeration of test cases.  Let me 
> know if you want that sheet.  It is too big to post to the list, but I 
> could ftp it to by web site for download.
>
> And for those reading this who have no idea why we have this mess, I'd 
> recommend the Wikipedia page on Date Count Conventions as a start: 
>  http://en.wikipedia.org/wiki/Day_count_convention
>
> You'll see that there are many more conventions and variations on 
> conventions than the 4 that Excel defines.  What is not certain is 
> whether Excel's calculations map 100% into any recognized definition.
>
> -Rob
>
>
>
>
>
>
> *"David A. Wheeler" <dwheeler@dwheeler.com>*
>
> 04/16/2008 06:34 PM
> Please respond to
> dwheeler@dwheeler.com
>
>
> 	
> To
> 	office@lists.oasis-open.org
> cc
> 	
> Subject
> 	[office] Excel 2007 != Ecma spec YEARFRAC.  Not even slightly.  What 
> should we do?
>
>
>
> 	
>
>
>
>
>
> In the process of re-creating the Excel 2007 algorithms for YEARFRAC,
> and comparing them to the draft ISO spec for Microsoft XML,
> I've found that they are simply nothing like each other.
> I can already confirm this for basis==0 and basis==1.
>
> Three possibilities:
> 1. The Microsoft XML spec is drastically wrong
> 2. Excel 2007 is drastically wrong
> 3. Both are wrong.
>
> I intend to work with Patrick to document the ACTUAL algorithms
> used by Excel 2007's YEARFRAC, and along with examples of how those 
> algorithms
> produce DIFFERENT results from the algorithms defined in the Microsoft 
> XML spec.
> That will at least give us an understanding of what the differences are.
>
> We will then need to make a strategic decision regarding spreadsheet 
> functions.
>
> We may need to consider dropping the basis-using functions as part of 
> the spreadsheet spec, and later release an update to add them once we 
> can have firm definitions of them, unless other agreements/decisions 
> can be made soon.  We've already waited for a very long time.  ISO 
> doesn't appear to mind ratifying specifications that are loaded with 
> known errors.  But I don't think anyone here wants to release an 
> OpenDocument spec that is known-wrong.
>
> --- David A. Wheeler
>
> ========================
>
> P.S., here's a basis==0 example.
> Ecma says, for basis=0, "If the second date has a day value of 31, it is
> changed to 30 days as long as the first date was not 28 or 29 February, in
> which case it does not change."  But in Excel 2007,
> given 2000-01-01 and 2000-01-31 with  basis 0, it returns "0.083333333",
> which is 30/360.  In other words, Jan 31 does NOT change to Jan 30
> (which is really weird for a "30-day month").
> Details are at:
> http://www.dwheeler.com/yearfrac
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this mail list, you must leave the OASIS TC that
> generates this mail.  You may a link to this group and all your TCs in 
> OASIS
> at:
> https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php
>
>

-- 
Patrick Durusau
patrick@durusau.net
Chair, V1 - US TAG to JTC 1/SC 34
Convener, JTC 1/SC 34/WG 3 (Topic Maps)
Editor, OpenDocument Format TC (OASIS), Project Editor ISO/IEC 26300
Co-Editor, ISO/IEC 13250-1, 13250-5 (Topic Maps)