YEARFRAC/Basis in more detail

["David A. Wheeler" <dwheeler@dwheeler.com>]

Can anyone help me with a minor coding-and-analysis job involving YEARFRAC, so that YEARFRAC (and thus the "Basis" used in MANY functions) will be fully defined?

One of the three main comments we got back on the formula spec was that "Basis"/YEARFRAC wasn't defined in enough detail.  When the test cases get removed, it'll be much worse.  In this case, I believe we should take the Excel values as "canonical" unless shown it should NOT be otherwise... but I've found NO place that actually defines it.

Here's needs to be done:
* Create a test spreadsheet programmatically, with enough date combos to cover all possible cases (e.g., all combinations of dates, where the dates are day 1, 2, 28, 29, 30, and 31 for all months where legal, for all 12 months, in both a leap-year and an adjoining non-leap year (so 24 months covered)... plus a few dates in other years to catch other problems).  I'd expect that to have 50,000 to 200,000 cases.
* Send that Excel, and save the numerical calculated results for each of the 5 Basis values (0..4).  You could write an ODF document, and then use an application's ability to "Save As..." to do a lot of this.
* Write a program that implements what we believe the various basis definitions are, and ensure that the results match.

The goal is to have a rock-solid definition.  All the functions that have a "Basis" parameter essentially use YEARFRAC as their underpinning, so once we have YEARFRAC defined, we end up completing the definition for a lot of financial functions.

--- David A. Wheeler