Repository Public
1"""Builds the per-function validation table of COMPATIBILITY.md.23 swift test --filter dumpRegistry # writes /tmp/functions_numeric_registry.tsv4 /opt/homebrew/bin/python3 coverage.py # prints the Markdown table56For every registered function it counts the reference cases whose formula calls it, by source7(LibreOffice fixtures, independent numerical references, hand-written Swift tests).8"""9import collections10import glob11import json12import os13import re14import sys1516HERE = os.path.dirname(os.path.abspath(__file__))17NAME = re.compile(r"(?<![A-Za-z0-9_.])([A-Z][A-Z0-9]*(?:\.[A-Z0-9]+)*)\(")18LABEL = {"scipy": "scipy/mpmath", "numpy": "numpy/scipy", "statsmodels": "statsmodels/numpy", "decimal": "Python decimal", "cmath": "Python cmath"}1920# Short per-function remarks (details in the "Known deviations" section of COMPATIBILITY.md).21NOTES = {22 "ROUND": "15-digit decimal rule", "ROUNDUP": "15-digit decimal rule", "ROUNDDOWN": "15-digit decimal rule", "TRUNC": "15-digit decimal rule",23 "MROUND": "LO differs on mixed signs (Excel: #NUM!)", "POWER": "LO differs for 0^0 and negative^fraction",24 "COMBINA": "LO differs for n < k and (0,0)", "MOD": "no #NUM! for huge quotients", "ROMAN": "LO differs for logical form",25 "ARABIC": "LO rejects '-X' and spaces", "ATAN2": "LO returns 0 for (0,0)", "LARGE": "non-integer k rounded up (unverified)",26 "SMALL": "non-integer k rounded up (unverified; LO truncates)", "MODE": "first mode encountered (LO: smallest)", "MODE.SNGL": "first mode encountered",27 "GEOMEAN": "LO returns 0 when a value is 0", "PERCENTRANK": "truncates decimals (LO rounds significant digits)",28 "PERCENTRANK.INC": "truncates decimals (LO rounds significant digits)", "PERCENTRANK.EXC": "truncates decimals (LO rounds significant digits)",29 "COUNTIF": "numeric criterion matches numeric text; wildcards match text only", "SUMIF": "same criteria rules as COUNTIF",30 "CONVERT": "LO only for exact-definition units; u, eV, parsec, mmHg unverified", "BESSELJ": "exact (Excel itself ~7 digits)", "BESSELY": "exact (Excel itself ~7 digits)",31 "BESSELI": "exact (Excel itself ~7 digits)", "BESSELK": "exact (Excel itself ~7 digits)", "BITLSHIFT": "result >= 2^48 is #NUM! (unverified)",32 "BITAND": "LO truncates non-integers (Excel: #NUM!)", "BITOR": "LO truncates non-integers", "BITXOR": "LO truncates non-integers",33 "DEC2HEX": "LO floors negative non-integers", "IMSUB": "LO allows mixing i and j", "IMDIV": "LO allows mixing i and j", "IMSUM": "LO allows mixing i and j",34 "DGET": "LO ignores empty fields when counting matches", "DSTDEV": "LO returns 0 without matches", "DVAR": "LO returns 0 without matches",35 "LINEST": "collinear regressors: minimum-norm solution", "LOGEST": "collinear regressors: minimum-norm solution",36 "SUBTOTAL": "not volatile: see integration note", "AGGREGATE": "not volatile: see integration note", "CELL": "5 info types", "INFO": "5 info types",37 "RANDBETWEEN": "random: range and integrality", "RANDARRAY": "random: shape, range, integrality", "ISOMITTED": "unknown to LibreOffice",38 "BINOM.DIST.RANGE": "unknown to LibreOffice's xlsx import", "FACTDOUBLE": "negative is #NUM! (documentation)", "ISNUMBER": "LO has no logical type",39 "TYPE": "LO has no logical type", "LOGNORM.DIST": "x <= 0 is #NUM! (LO: 0)", "EXPON.DIST": "x < 0 is #NUM! (LO: 0)", "FORMULATEXT": "en-US syntax",40 "ERROR.TYPE": "ORIX extensions: #SPILL! 9, #CALC! 14, #CIRC! 15",41}424344def names_in(formula):45 out = set()46 for i, part in enumerate(formula.split('"')):47 if i % 2 == 0:48 out.update(NAME.findall(part))49 return out505152def main(registry="/tmp/functions_numeric_registry.tsv"):53 rows = [line.rstrip("\n").split("\t") for line in open(registry) if line.strip()]54 counts = collections.defaultdict(collections.Counter)55 for path in sorted(glob.glob(os.path.join(HERE, "..", "Fixtures", "*.json"))):56 fx = json.load(open(path))57 for case in fx["cases"]:58 for n in names_in(case["f"]):59 counts[n][fx["source"]] += 160 hand = collections.Counter()61 for path in glob.glob(os.path.join(HERE, "..", "*.swift")):62 text = open(path).read()63 for lit in re.findall(r'"(=(?:[^"\\]|\\.)*)"', text):64 for n in names_in(lit.replace('\\"', '"')):65 hand[n] += 166 print("| Function | Category | LibreOffice | Independent reference | Hand-written | Remark |")67 print("|---|---|---:|---|---:|---|")68 missing = []69 for name, category, _signature, _summary in rows:70 c = counts.get(name, {})71 indep = ", ".join(f"{LABEL.get(k, k)} {v}" for k, v in sorted(c.items()) if k != "libreoffice") or "–"72 if sum(c.values()) + hand[name] == 0:73 missing.append(name)74 print(f"| `{name}` | {category} | {c.get('libreoffice', 0) or '–'} | {indep} | {hand[name] or '–'} | {NOTES.get(name, '')} |")75 print(f"functions: {len(rows)}; without any reference: {missing}", file=sys.stderr)767778if __name__ == "__main__":79 main(*sys.argv[1:])80