The Epistemological Question
Jul
7
Written by:
Charles Flock
7/7/2010 5:12 PM
How do you know if a function is accurate?
When we came up with the idea for XLeratorDB, among the many challenges we faced, the biggest one was probably testing. I contacted a former colleague of mine, Lorna, and asked for her help. Lorna had been in charge of system testing at an application software company that provided software to the financial services industry. After we demonstrated how everything worked, she thought about it for a couple of days and then she told me that she really had no idea how to do all the necessary testing.
Necessity being the mother of invention, we had to come up with another approach. Our first attempt was to come up with test cases to test all the conditions that we could think of. This proved to be an unbelievably laborious process (in addition to proving that Lorna knew what she was talking about).
Our solution was to test in volume. We decided to create test data randomly and to create lots of it, so that we could find the conditions where our functions were inaccurate. This turned out to be relatively easy. We would randomly populate each variable in the function, call the EXCEL function using the WorksheetFunction object, write the results to a file and upload the file to SQL Server. Then we would run T-SQL against the same data and compare the XLeratorDB function result to the EXCEL function result, and investigate the differences. We would also keep track of when we ran the test, how many records we processed, how long it took, what release of the software we were testing, what version of SQL Server we were running against, and other useful information.
For example, we have 1,000,000 test cases for the ACCRINT function. You could randomly create a ‘test harness’ by writing something like the following code in VBA:[1]
Public Sub create_ACCRINT_data()
On Error GoTo ErrorHandler
Dim issue As Date, settlement As Date, first_interest As Date, maturity As Date
Dim rate As Double, par As Double, basis As Double, frequency As Double
Dim recno As Double, xl_calc As Double
Open "ACCRINT.txt" For Output As #1
recno = 1
Do While recno < 1000001
maturity = WorksheetFunction.RandBetween(DateSerial(2011, 12, 31), DateSerial(2021, 12, 31))
basis = WorksheetFunction.RandBetween(0, 4)
frequency = 2 ^ WorksheetFunction.RandBetween(0, 2)
par = WorksheetFunction.RandBetween(50, 150)
settlement = WorksheetFunction.RandBetween(DateSerial(2007, 1, 1), DateSerial(2009, 12, 31))
issue = WorksheetFunction.CoupPcd(settlement, maturity, frequency, basis)
first_interest = WorksheetFunction.CoupNcd(settlement, maturity, frequency, basis)
rate = WorksheetFunction.RandBetween(1000000, 20000000) / 100000000
xl_calc = WorksheetFunction.AccrInt(issue, first_interest, settlement, rate, par, _
frequency, basis)
Write #1, recno, CStr(issue), CStr(first_interest), CStr(settlement), rate, par, _
frequency, basis, 1, xl_calc
recno = recno + 1
Loop
Close #1
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 1004
xl_calc = 0
Case Else
xl_calc = 0
End Select
Resume Next
End Sub
Take the file, ACCRINT.txt, and upload it into your database using the Import Data capability in SSMS. It then simply becomes a matter of writing a SELECT statement that returns all the rows where the XLeratorDB calculation does not agree with the EXCEL calculation to whatever number of significant digits is desired. Something like this:
SELECT *, ROUND(XLDB_ACCRINT/ XL_ACCRINT, 8)
FROM (
SELECT recno
,ROUND(wct.ACCRINT(issue
,first_interest
,settlement
,rate
,par
,frequency
,basis
,calc_method), 15) as XLDB_ACCRINT
,ROUND(xl_calc, 15) as XL_ACCRINT
FROM ACCRINT
) n
WHERE ROUND(XLDB_ACCRINT/ XL_ACCRINT, 8) - 1 > 1E-08
AND XL_ACCRINT <> 0
UNION
SELECT *, 0
FROM (
SELECT recno
,ROUND(wct.ACCRINT(issue
,first_interest
,settlement
,rate
,par
,frequency
,basis
,calc_method), 15) as XLDB_ACCRINT
,ROUND(xl_calc, 15) as XL_ACCRINT
FROM ACCRINT
) m
WHERE ROUND(XLDB_ACCRINT, 8) = 0 AND ROUND(XL_ACCRINT, 8) <> 0
Then, there is a short list of items to investigate. Why are there any items to investigate? It turns out that the ACCRINT function in EXCEL does not exactly work the way that you would expect it to work with bonds that have an odd period, especially if the basis code is Actual/Actual. How do we know this? Since we didn’t agree with the EXCEL calculation we then did further testing against industry bond calculators and Bloomberg, as well having in-house financial industry expertise. In fact, we ended up being so unhappy with the EXCEL ACCRINT function, that we wrote a distinct function, BONDINT, that better reflects industry practice.
It was surprising to us that our benchmark did not actually work the way we expected. This turned out to be an even bigger problem than the actual creation of the test harness (which was a pretty big undertaking on its own). Since our original mission was to replicate EXCEL functions in the database, we were now faced with a problem that we had not anticipated. Do we return the right answer (at least what we think the right answer is) or do we return the same answer as EXCEL?
And Microsoft has not made this issue any easier. With release of Excel 2010, Microsoft identified about 40 existing functions that have “improved accuracy.” For example, many of the statistical functions mentioned use the GAMMALN function (or at least something like the GAMMALN function) internally, and that function has increased accuracy, so naturally the functions that use that function will have increased accuracy—which I take to mean that they are accurate to more significant digits.
Similarly, there are six financial functions identified as improved by Microsoft: CUMIPMT; CUMPRINC; IPMT; IRR; PMT; and PPMT. With the exception of IRR, all these functions are loan-related. We have documented some problems with loan-related functions in other blogs and we are happy that most of the problems we identified are fixed, though there are still some problems in CUMIPMT.
Our testing has uncovered numerous other inaccuracies with EXCEL financial functions: XIRR, YIELD, ODDLPRICE, ACCRINT, and more, as well as more than a few things that don’t seem to make sense. For example, why can’t you enter negative rates in XNPV? Why does the settlement date of a bond need to be greater than the issue date? Why can’t you enter negative yields (or rates) in PRICE, ODDFPRICE, or ODDLPRICE?
Our users weighed in on both sides of the issue. Some felt that it was of the utmost importance that we match the EXCEL function, pointing out that some master agreements for derivatives contracts made reference to the results of an EXCEL function. Others were aware of the limitations in the EXCEL calculations and told us, of course, that we should conform to industry and scientific standards.
That’s how we ended up with a test harness with over 400 million rows of data in it. This test harness gives us a tremendous ability to verify the accuracy of the XLeratorDB calculations, with the additional benefit of being able to verify the accuracy of the EXCEL calculations. And, it has made us way more knowledgeable about the level of accuracy in the EXCEL functions. It also enabled us to enhance those functions where it made sense to do so. In some cases, we actually created an enhanced version of the function, since we felt that it was important to match the EXCEL function, but we also felt that the EXCEL function could be better. This is why we have an NPV function and an ENPV function.
As we add functions that are not in EXCEL, we continue with the same process. Find a benchmark, create a test harness against that benchmark, investigate the differences and make changes to our code, or document and publish the differences. In fact, we are adding 38 new functions to the statistics package, all of which can be found in the R programming language but not in EXCEL and we use exactly the same methodology. We created the test data in R, imported the data to the database, and compare the results to XLeratorDB. When we ran end-to-end testing of XLeratorDB/developer, with all the existing and new functions, it took over 36 hours.
Imagine if you had to do this yourself. This is one function. You could easily spend an entire day writing a function, unit testing, creating the test harness, and then testing against the harness, only to discover that there are dozens of discrepancies that require some serious brainpower and industry knowledge to research. Now, multiply that by 450.
How are your other software vendors testing their software? Come to think of it, how does Microsoft test EXCEL?
[1] Astute observers will quickly realize that the WorksheetFunction object ACCRINT and the actual EXCEL function do not have the same number of parameters. The difference is the
calc_method parameter. Kind of makes you wonder if that parameter actually does anything in EXCEL.