While we might never be able to have 100% of our code strict, we can at
the very least do it for all of our tests. This ensures that our tests
are using our API with the types as intended by the test author, and not
silently be cast to what our API requires.
* Additional Unit Tests for XIRR and XNPV
See discussion in issue #3297. Excel result disagreed with PhpSpreadsheet, but it seems apparent that the error is Excel's. Although we have other test cases where Excel and PhpSpreadsheet disagree, those have so far been cases where Excel cannot give an answer because its algorithm does not converge, which is possible for any algorithm. In the issue in question, Excel seems to converge to a different answer; this is different than the other exceptions, and so deserves documentation with a formal test case. The discussion also mentions some cases where XNPV seems to give the wrong result in Excel, and those cases are added for XNPV.
* Scrutinizer
Whatever.
Fix#689. XIRR is calculated by making guesses which are hopefully better with each iteration. It is not guaranteed to succeed for Excel, PhpSpreadsheet, or any other implementation. PhpSpreadsheet uses the Newton-Raphson method for its guesses. So does Python package xirr (https://github.com/tarioch/xirr/), but, if Newton-Raphson fails to converge, Python tries Brent's method as an alternative. Two sets of non-converging data are noted in 689. For both, a solution does converge in Excel. For the first of the problems, a solution converges in Python with Newton-Raphson; but, for the second, a solution converges which requires Brent. For the Java package https://github.com/RayDeCampo/java-xirr on which Python was based, and which uses only Newton-Raphson, a solution converges for the first, and does not converge for the second.
To try to match the good results of the others, I added an alternate algorithm if Newton-Raphson fails. Brent's algorithm seems difficult to implement to me. I might have gone there regardless, but I first tried a slightly simpler alternative, bisection. This solved the problem for both of the cases in 689. Perhaps someone will one day report a problem that doesn't converge for Newton-Raphson or bisection, but does for Brent. We can review this decision then.
The new code causes 3 changes in the unit test. In all 3 tests, Excel and PhpSpreadsheet had not converged, but Python and/or Java had. I now believe that Python/Java is correct in those cases, and Excel is not. The new code aligns PhpSpreadsheet with Python/Java for those tests. It is, of course, impossible to know when Excel's implementation doesn't converge, so we aren't guaranteed to match its results in those hopefully rare situations.
* Fix Unintential Deprecated Calls in Tests - FINANCIAL
I think it's best to install these before PR #3166. There are no changes to source code, only to doc-blocks and to test members which continue to inadvertently use calls to deprecated functions.
* Change Tests to Run in Spreadsheet Context
Found and fixed some problems with how MIRR handles errors.
* Refinement for XIRR
Fix#2469. The algorithm used for XIRR is known not to converge in some cases, some of which are because the value is legitimately unsolvable; for others, using a different guess might help.
The algorithm uses continual guesses at a rate to hopefully converge on the solution. The code in Python package xirr (https://github.com/tarioch/xirr/) suggests a refinement when this rate falls below -1. Adopting this refinement solves the problem for the data in issue 2469 without any adverse effect on the existing tests. My thanks to @tarioch for that refinement.
The data from 2469 is, of course, added to the test cases. The user also mentions that an initial guess equal to the actual result doesn't converge either. A test is also added to confirm that that case now works.
The test cases are changed to run in the context of a spreadsheet rather than by direct calls to XIRR calculation routine. This revealed some data validation errors which are also cleaned up with this PR. This suggests that other financial tests might benefit from the same change; I will look into that.
* More Unit Tests
From https://github.com/RayDeCampo/java-xirr/blob/master/src/test/java/org/decampo/xirr/XirrTest.javahttps://github.com/tarioch/xirr/blob/master/tests/test_math.py
Note that there are some cases where the PHP tests do not converge, but the non-PHP tests do. I have confirmed in each of those cases that Excel does not converge, so the PhpSpreadsheet results are good, at least for now. The discrepancies are noted in comments in the test member.
There were about 20 skipped tests for RATE and PRICE marked
"This test should be fixed". This change does that by fixing
the code for those functions, validating the existing tests,
and adding new ones. XIRR and XNPV are also substantially changed.
As part of this change, the following functions also have minor changes:
- isValidFrequency
- COUPDAYBS
- COUPNUM (additional tests)
- DB
- DDB
PhpUnit reports 100% coverage for all the changed functions.
Since I was dealing with skipped tests, I also fixed
tests/PhpSpreadsheetTests/Writer/Xlsx/LocaleFloatsTest,
which was being skipped in Windows. I also delete the temporary
file which it creates.
There is now only one remaining test which is skipped -
ODS Reader is not complete enough to run some tests against it.
Unfortunately, that test is too complicated for me to deal with now.
In researching this change, I found several places in the code where special code was added for Gnumeric claiming:
- Gnumeric does not handle free-format string dates
- Gnumeric adds extra options, not available in Excel,
for the frequency parameter for functions such as YIELD
- Gnumeric rounds the results for DB and DDB to 2 decimal places
None of these claims is true, at least not on a recent version
of Gnumeric, and the code which supports these differences is removed.
There did not appear to be any tests targeted for
these supposed properties of Gnumeric.
The PRICE function needed relatively minor changes - mostly
additional tests for invalid input. The main problem with the PRICE
tests is that Excel appears to have a bug. The algorithm is published:
https://support.office.com/en-us/article/price-function-3ea9deac-8dfa-436f-a7c8-17ea02c21b0a
The results that Excel returns for basis codes 2 and 3 appear to be
incorrect in many cases. I have segregated these tests into a
new test PRICE3. The results of these tests agree with the published
algorithm, and to the results for LibreOffice and Gnumeric.
The results returned by Excel do not agree with them.
The tests which remain in the test PRICE all use basis codes other
than 2 or 3, and all agree with Excel, LibreOffice, and Gnumeric.
For the RATE function, there appears to be a problem with how the
secant method was implemented. I studied the implementation of RATE
in Python numpy, and adapted its implementation of secant method.
The results now agree with numpy, and, more important, with Excel.
XIRR, which calls XNPV, permits its dates to be earlier than the
start date, whereas XNPV does not. I dealt with this by renaming
the existing XNPV function to xnpvOrdered, adding a parameter to
indicate whether start date has to be earliest. XNPV calls the new
function with that parameter set to TRUE, and XIRR calls it with
the parameter set to FALSE. Some additional error checking was
added to xnpvOrdered, and also to XIRR. XIRR tests benefited
from increasing the value of FINANCIAL_MAX_ITERATIONS.
Finally, since this change is very test-related:
samples/Basic/13_CalculationCyclicFormulae
PhpUnit started reporting an error like "too much regression".
The test deals with an infinite cyclic formula, and allowed
the calculation engine to run for 100 cycles. The actual number of cycles
seems irrelevant for the purpose of this test. I changed it to 15,
and PhpUnit no longer complains.
Fix: Return #NUM! if values and dates contain a different number of values
Fix: Return #NUM! if there is not at least one positive cash flow and one negative cash flow
Fix: Return #NUM! if any number in dates precedes the starting date
Fix: Return #NUM! if a result that works cannot be found after max iteration tries
Fix: Correct DocBlocks for XIRR & XNPV
Add: Validate XIRR with unit tests
Closes#1177
Adrien Crivelli
oleibman
oleibman
Paul Blacknell