Re: [office] YEARFRAC: Why both Microsoft XML definitions are wrong,and next steps

[Patrick Durusau <patrick@durusau.net>]

David,

This is precisely the sort of information that will make a more 
effective report of a "bug" in the OpenXML definitions.

I am willing to help do test cases to report the results back to you. I 
assume it requires Excel 2007?

I am assuming that if it is broken up that the large number of test 
cases will go more quickly, although that will still leave you or 
someone like you the task of deciding what rule was in operation.

Hope you are having a great day!

Patrick

PS: I guess the other cases you uncovered should go in to ODF 1.3. ;-)

David A. Wheeler wrote:
> Andreas J Guelzow:
>   
>> This exactly is the crux of the matter. Anybody who wants to implement
>> YEARFRAC has to know "what the rule really is". That's all I am really
>> asking for!
>>     
>
> And we should try to determine them.  We are going to have to reverse-engineer them, because Microsoft failed to define them properly in the Microsoft XML specification.
>
> If anyone could help in doing so, please let me know ASAP.  It's easily parallelizable.  I have started to do Yearfrac's basis=1, using a very large set of test cases. Help would be VERY APPRECIATED.
>
> Aside: This is, of course, ridiculous.  ISO's failure to require Microsoft to fully and correctly document these rules, and a long list of other errors and omissions, is (in my viewpoint) a shameful failure by ISO.  I don't care WHO the submitter is, a specification that is as full of errors as Microsoft XML should NEVER be fast-tracked; it needs to go through the normal standards review process to get the review it clearly needs.  The "last minute repairs" were, unsurprisingly, a complete failure at getting the necessary quality.   It appears that we are holding ourselves to a MUCH higher level-of-quality requirement than ISO actually requires.
>
> Rob Weir says that Microsoft claims this is their rule for basis=1 (actual/actual):
>   
>> The fractional number of years represented by the number of whole days 
>> between two dates, start-date and end-date., according to basis. If the 
>> Actual/actual basis is used, the year length used is the average length 
>> of the years that the range crosses, regardless of where start-date and 
>> end-date fall in their respective years.   However, if the value of 
>> basis is out of range, #NUM! is returned.
>>     
>
> This statement is not true (all dates below are in U.S. MM/DD/YYYY format):
> * Given dates 01/01/2000 and 01/01/2001, YEARFRAC returns
> 1.0000000000, which is 366/366 - and NOT 366/((366+365)/2).  Note that only the FIRST year counts.
> * It is NOT true that the Jan 1. as an end-date means that that year is ignored.  Given dates 01/01/2000 and 01/01/2002, it returns 2.0009124090, which is =(731)/((366+365+365)/3).  If Jan 1. were ignored as an end-date, it would be (731)/((366+365)/2) = 2 exactly.
>
>
> Their older posted rules for basis=1, Actual/actual, are:
>   
>> The actual number of days between the two dates are counted. If the date
>> range includes the date 29 February, the year is 366 days; otherwise it
>> is 365 days.
>>     
>
> This statement is also untrue.
> * Given dates 01/01/2000 and 01/02/2000, the Excel result is 0.0027322400, which is 1/366, and NOT 1/365.
> * Given dates 01/01/2000 and 01/31/2004, Excel produces 4.080459770, which is =(diff)/((366+365+365+365+366)/5)... Note that there are TWO, not ONE, 366's in there.  Replacing a 366 with 365 (as that rule would suggest) gives the wrong answer.
>
> So different rules are needed.  Starting with the large set of test cases, I am working backward to determine how each test case is calculated, and from that, to determine the REAL ruleset.  Then I write code to implement the ruleset, and test it against an extremely large set of test cases; once I have accounted for all the test data, I have the correct ruleset.  Here's an extract of what I have so far for basis=1, so you can see what I'm doing:
>
> Date1	Date2	Official Answer	Calculated Answer	OK?	Formula	Comments
> 2000-01-01	2001-01-01	1.0000000000	1.0000000000	OK	=366/366	Doesn’t average
> 2000-01-01	2001-01-02	1.0041039670	1.0041039672	OK	=(B13-A13)/((366+365)/2)	Averages here
> 2000-01-01	2001-01-30	1.0807113540	1.0807113543	OK	=(B14-A14)/((366+365)/2)	
> 2000-01-01	2002-01-01	2.0009124090	2.0009124088	OK	=(B15-A15)/((366+365+365)/3)	DOES average
> 2000-01-01	2000-01-02	0.0027322400	0.0027322404	OK	=1/366	Crossing Feb 29 NOT required
> 2000-01-01	2001-02-02	1.0889192890	1.0889192886	OK	=(B17-A17)/((366+365)/2)	
> 2000-01-01	2001-03-01	1.1627906980	1.1627906977	OK	=(B18-A18)/((366+365)/2)	
> 2003-01-01	2004-02-01	1.0834473320	1.0834473324	OK	=(B19-A19)/((365+366)/2)	
> 2003-01-01	2004-02-28	1.1573187410	1.1573187415	OK	=(B20-A20)/((365+366)/2)	
> 2003-01-01	2004-03-01	1.1627906980	1.1627906977	OK	=(B21-A21)/((365+366)/2)	
> 2000-01-01	2004-01-31	4.0804597700	4.0804597701	OK	=(B22-A22)/((366+365+365+365+366)/5)	Crossing Feb 29 NOT required
> 2000-01-01	2004-02-28	4.1570881230	4.1570881226	OK	=(B23-A23)/((366+365+365+365+366)/5)	
> 2000-12-31	2001-01-01	0.0027397260	0.0027397260	OK	=(B24-A24)/365	WHOA. Day in leapyear, yet 365.
> 2000-12-31	2001-01-02	0.0054794520	0.0054794521	OK	=(B25-A25)/365	WHOA. Day in leapyear, yet 365.
>
>
> Currently, I suspect that it computes the "daydifference" or some related "size" value, and then uses DIFFERENT rules depending on that value.  (Something like "if daydiff <=366, ruleset1, else ruleset2").  I will post more once I have a better handle on what I believe the ACTUAL ruleset to be.
>
> What's even MORE remarkable is that according to ISMA there are 3 different actual/actual computations required by different financial institutions: ISMA actual/actual (also US treasury convention), AFB, and the ISDA method.  NONE of them appear to be supported by Excel!! My suspicion is that financial organizations specify "actual/actual", and that all of them THINK that they are referring to some official standard.  But instead, they are depending on undocumented functionality of Excel.
>
> It would be great to define basis values for the other actual/actual conventions, and agree on them.  But that's for another day; let's start by determining what we need today, to share what we already have.
>
>
> --- David A. Wheeler
>
> ---------------------------------------------------------------------
> 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)