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

[robert_weir@us.ibm.com]

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.

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.

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.  

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