SPB Git

Repository Public

COMPATIBILITY.md 12.3 KB

# FunctionsLookup — compatibility and validation

81 functions: 10 lookup & reference, 22 dynamic-array, 49 financial. Every registered function passes all of its reference cases (swift test, 86 965 formula cases + 1 397 YEARFRAC rows + hand-written tests).

# Reference sources

tag source cases
XL Values computed by Microsoft Excel: test data of ExcelFinancialFunctions (fsprojects, Copyright 2010-2013, Apache License 2.0), converted by Generators/gen_excel_reference.py into Fixtures/excel_reference.json (dates → serials, enumerations → Excel codes; nothing else changed, nothing thinned) 68 075 + 1 397 YEARFRAC
LO LibreOffice Calc 26.8.2 headless recalculation of generated workbooks (Generators/lo_harness.py) 14 223
PY Independent Python computations (Generators/pyref.py, gen_arrays.py): closed forms, period-by-period simulations, explicit cash-flow discounting, scipy.optimize.brentq 17 874 (13 207 of them also confirmed by LO)
DOC Worked examples of Microsoft's function reference, typed by hand in FinancialTests.swift 90
HW Hand-written Excel-semantics tests (LookupTests, DynamicArrayTests, FinancialTests, PublicAPITests) ≈ 500 assertions

The generators fail when LibreOffice and Python disagree without a recorded reason. Where LibreOffice is known not to match Excel, the case keeps the Python value only and the fixture records the LibreOffice value and the reason (4 472 cases, listed per function below). Each such reason was confirmed against the Excel data. LibreOffice's xlsx export reports most errors as #VALUE! and logical values as 1/0, so fixtures check "is an error" (exact kind only for #N/A and #DIV/0!); exact Excel error kinds are asserted in the hand-written tests.

Comparison tolerance: relative 1e-9 (1e-6…1e-8 for iterative results against Excel/LibreOffice, whose solvers stop earlier than ours).

# Lookup & reference

function validation notes / deviations from Excel
VLOOKUP, HLOOKUP LO 198, HW Approximate match = binary search on ascending data (last of equal items); exact match linear with *, ?, ~. An empty range_lookup placeholder (VLOOKUP(a,b,2,)) means FALSE. Text never matches numbers ("5" ≠ 5) and logical values only match logical values — LibreOffice differs on both.
LOOKUP LO 77, HW Vector and array forms. A result vector shorter than the match position gives #N/A (Excel reads past the end of a range).
MATCH LO 130, HW Types 1, 0, −1. Type −1 is a binary search on descending data. On unsorted data approximate modes return a position that may differ from Excel's (undefined behaviour in both). Blank cells and errors inside the range are skipped by the binary search.
XMATCH, XLOOKUP LO 365, HW Match modes 0, −1, 1, 2; search modes 1, −1, 2, −2. Binary modes return the first of several equal items on ascending data and the last on descending data (LibreOffice's behaviour; Excel documents none). Modes −1/1 with a linear search compare only items of the lookup value's type. An empty lookup value matches empty cells and "". XLOOKUP returns a reference when return_array is a reference and there is one lookup value. With several lookup values each result is the first cell of the matching row/column.
ADDRESS LO 66, HW abs_num 5–8 accepted as 1–4. Sheet names are quoted when they are not plain identifiers.
AREAS LO 3, HW Always 1: SheetCore has no multi-area (union) references.
HYPERLINK HW Returns the friendly name (or the location); the link itself is not stored — cell hyperlinks are a model feature. LibreOffice returns a two-cell array and is not used as reference.
TRANSPOSE LO 7, HW —

Text comparison is case-insensitive and uses a fixed collation shared with the core (TextOrder): accented letters sort next to their base letter but stay distinct ("é" ≠ "e", punctuation is significant); Excel uses the system locale's collation. Performance: whole-column references are clamped to the used range and only the lookup column and the hit row are read. A hash index is built when one call carries at least 4 lookup values against at least 16 cells (XLOOKUP(E2:E5000, A:A, B:B) is one index build + 5 000 probes; measured: 2 000 lifted lookups over 200 000 rows < 1.5 s in a debug build, a single whole-column lookup < 0.4 s). A cache shared between cells is not implemented: it cannot be invalidated safely without a recalculation generation in SheetCore (see the report).

# Dynamic arrays

function validation notes / deviations from Excel
FILTER LO+PY 22, HW include must be a column of rows items or a row of columns items; numbers ≠ 0 count as TRUE; text is #VALUE!. Empty result: if_empty or #CALC!.
SORT, SORTBY LO+PY 35, PY 6, HW Stable. Order: numbers < text < FALSE < TRUE < errors, empty cells last in both directions (the rule of Excel's Sort command; the position of errors and empty cells was not verified against the SORT function). Mixed-type cases are Python-only (LibreOffice sorts logical values as numbers).
UNIQUE LO+PY 13, PY 2, HW Case-insensitive; 1 ≠ "1"; an empty cell, "" and 0 are three distinct values (Excel's handling of empty cells versus "" was not verified).
SEQUENCE LO+PY 11, PY 2, HW SEQUENCE(0) → #CALC!, negative size → #VALUE!.
TAKE, DROP LO+PY 68, HW Zero count / nothing left → #CALC!.
CHOOSEROWS, CHOOSECOLS LO+PY 23, HW Index 0 or out of range → #VALUE!.
EXPAND LO+PY 9, PY 1, HW Smaller target → #VALUE!; default padding #N/A.
HSTACK, VSTACK LO+PY 13, HW Padding #N/A.
TOCOL, TOROW LO+PY 21, PY 1, HW Everything ignored → #CALC!.
WRAPROWS, WRAPCOLS LO+PY 27, HW Non-vector → #VALUE!; wrap_count < 1 → #NUM!.
MAP, REDUCE, SCAN, BYROW, BYCOL, MAKEARRAY PY 40, HW Not in LibreOffice: Python model only. A LAMBDA returning an array inside MAP/SCAN/BYROW/BYCOL/MAKEARRAY gives #CALC! for that element. The LAMBDA must declare exactly the expected number of parameters (#VALUE!). With initial_value omitted REDUCE/SCAN start from the first element. BYROW/BYCOL pass references when the source is a range.

Scalars are accepted wherever an array is (SORT(5)), unlike LibreOffice. Empty cells inside results display 0 once spilled (SheetCore rule, same as Excel).

# Financial

"XL" = number of Excel-computed cases passing; "LO+PY" = cases on which LibreOffice and the Python reference agree and we match both; "PY (LO≠)" = Python-only cases where LibreOffice is known to differ from Excel.

function XL LO+PY PY (LO≠) other notes / deviations from Excel
PMT / PV / FV 1 752 / 1 842 / 2 026 483 / 450 / 451 DOC —
NPER 855 99 DOC No solution → #NUM!.
RATE 8 449 PY 73, DOC, HW Root always found when one exists (Excel gives #NUM! after 20 Newton steps). 73 cases where LibreOffice's solver fails or stops on a non-root are Python-only.
IPMT / PPMT 4 552 / 4 552 363 / 362 DOC —
CUMIPMT / CUMPRINC 210 / 210 103 / 103 DOC Fractional bounds: ⌈start⌉…⌊end⌋ (Excel).
ISPMT 626 48 DOC —
EFFECT / NOMINAL 14 / 14 38 / 38 DOC Rate ≤ 0 → #NUM! (LibreOffice returns 0).
PDURATION / RRI 23 / 31 40 / 50 DOC RRI(n, 0, 0) = 0.
DOLLARDE / DOLLARFR 14 / 14 91 / 91 DOC —
NPV 23 96 DOC, HW —
IRR 11 41 DOC, HW See "iterative functions" in FINANCIAL-CONVENTIONS.md. With multiple roots the result may differ from Excel's when the guess is far from every root.
MIRR 149 49 DOC —
XNPV — 61 DOC, HW No Excel data set: LibreOffice + Python + documentation example. Rates > −1 accepted.
XIRR 26 21 DOC, HW —
FVSCHEDULE 14 7 DOC —
SLN / SYD 26 / 134 8 / 53 DOC SYD accepts 0 < per ≤ life (fractions too).
DB 398 236 DOC Period life + 1 allowed when month < 12.
DDB 458 215 DOC Fractional periods follow Excel (LibreOffice differs).
VDB 2 546 297 LO 168, DOC —
COUPDAYBS / COUPDAYS / COUPDAYSNC 917 / 17 / 917 474 / 456 / 433 0 / 0 / 23 DOC COUPDAYSNC in 30/360 around the end of February: Excel's own count (LibreOffice differs).
COUPNCD / COUPPCD / COUPNUM 917 / 917 / 917 474 / 456 / 474 DOC Coupon dates are derived from maturity directly (see below).
PRICE 10 982 1 409 848 DOC Time to next coupon (E − A)/E; simple interest in the last period. yld < 0 → #NUM!.
YIELD 441 1 400 751 DOC The Excel set covers negative yields; positive yields rest on PRICE (10 982 Excel cases) being inverted, LO+PY and the documentation example.
DURATION / MDURATION 5 492 / 5 492 585 / 585 1 220 / 1 220 DOC LibreOffice measures time with YEARFRAC × frequency and misses Excel's documented 5.993775.
ACCRINT 1 923 200 122 PY 45, DOC Excel data covers settlement ≤ first_interest with calc_method TRUE. Not covered by Excel data: settlement after first_interest (TRUE: same per-period rule continued; FALSE: accrual from first_interest, as documented) — Python model only.
ACCRINTM 362 125 20 DOC —
DISC / YIELDDISC 2 747 / 2 747 213 / 211 DOC —
INTRATE / RECEIVED / PRICEDISC 2 749 / 1 290 / 3 662 121 / 121 / 121 20 / 20 / 20 DOC RECEIVED with discount × years ≥ 1 → #NUM!.
PRICEMAT / YIELDMAT 1 942 / 2 912 359 / 359 62 / 62 DOC DSM = DIM − A, B from issue → settlement (Excel; LibreOffice uses three YEARFRACs).
TBILLEQ / TBILLPRICE / TBILLYIELD 50 / 83 / 71 14 / 14 / 14 20 / 32 / 32 PY 18, DOC Actual days; TBILLEQ switches formula beyond 182 days (Excel data includes such bills). Maturity equal to settlement → #NUM!.

# Known limits and deviations (financial)

  1. Dates given as text ("2024-01-31") are #VALUE!; Excel parses them. Use DATE() or a cell.
  2. Coupon dates when maturity falls on the 29th or 30th and is not a month end (e.g. 30 Aug): we keep the maturity day wherever the month allows (28 Feb, 30 Aug, 28 Feb, 30 Aug …), as LibreOffice does. Neither the Excel data set nor the documentation contains such a maturity; an Excel-compatible library derives each date from the previous one (28 Feb, 28 Aug, …). Unverified against Excel.
  3. Iterative results are more precise than Excel's (Excel stops at 1e-7) and are found in cases where Excel reports #NUM!.
  4. Error kinds for invalid arguments follow Excel's documentation (#NUM!); the Excel data set contains valid inputs only (plus PDURATION/RRI failures), so error cases are validated by documentation and LibreOffice ("is an error").
  5. PRICE rejects negative yields (documented Excel behaviour); recent Excel builds accept them.
  6. ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, AMORDEGRC, AMORLINC and GETPIVOTDATA are out of scope and not registered. ISOMITTED is not part of this package either, but it exists in the application: FunctionsNumeric registers it (Information.swift).

# Attribution

Tests/FunctionsLookupTests/Fixtures/excel_reference.json is derived from the test data of ExcelFinancialFunctions (https://github.com/fsprojects/ExcelFinancialFunctions), licensed under the Apache License, Version 2.0 (http://www.apache.org/licenses/LICENSE-2.0). The file states its origin and the changes made. The day-count rules that Excel applies inside its bond functions (US 30/360 variants, (E − A)/E, ACCRINT per quasi-coupon period, the T-bill formula beyond 182 days) were learned from that project's documentation of Excel's behaviour and re-implemented here in Swift.

# Regenerating the fixtures

sh
cd Tests/FunctionsLookupTests/Generators
/opt/homebrew/bin/python3 gen_lookup.py        # LibreOffice
/opt/homebrew/bin/python3 gen_arrays.py        # Python model + LibreOffice
/opt/homebrew/bin/python3 gen_financial.py     # LibreOffice + Python
/opt/homebrew/bin/python3 gen_securities.py    # LibreOffice + Python
/opt/homebrew/bin/python3 -I gen_excel_reference.py <dir with the downloaded Excel data>   # see the script header
Files main
Menu

Theme