Repository Public
1"""Reference cases for the day-count based security functions, all five bases.23LibreOffice 26.8 recalculates every formula; pyref.py supplies an independent Python value4(explicit cash-flow discounting, scipy brentq). Classes of inputs on which LibreOffice is known to5deviate from Excel are routed to the Python reference only — each is listed with its reason in6KNOWN_LO_DEVIATIONS and in COMPATIBILITY.md. The script fails on any unexplained disagreement.7"""8import itertools9import sys10from datetime import date1112import pyref as R13from lo_harness import Fixture, close, is_error1415fx = Fixture("securities", "COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, PRICE, YIELD, DURATION, MDURATION, ACCRINT, ACCRINTM, DISC, "16 "INTRATE, RECEIVED, PRICEDISC, PRICEMAT, YIELDDISC, YIELDMAT, TBILLEQ, TBILLPRICE, TBILLYIELD — LibreOffice 26.8 + independent Python", scalar=True)1718D = date19PAIRS = [20 (D(2011, 1, 25), D(2011, 11, 15)), (D(2007, 1, 25), D(2008, 11, 15)), (D(2008, 2, 15), D(2017, 11, 15)), (D(2018, 7, 1), D(2048, 1, 1)),21 (D(2008, 1, 1), D(2016, 1, 1)),22 # end-of-month maturities (coupon dates stick to month ends) and leap days23 (D(2019, 3, 15), D(2021, 2, 28)), (D(2020, 1, 31), D(2024, 2, 29)), (D(2023, 8, 31), D(2025, 8, 31)), (D(2021, 12, 31), D(2026, 6, 30)),24 (D(2022, 2, 28), D(2027, 11, 30)), (D(2024, 2, 29), D(2030, 8, 31)), (D(2023, 2, 28), D(2025, 8, 31)), (D(2024, 2, 28), D(2028, 2, 29)),25 (D(2023, 3, 1), D(2024, 2, 29)), (D(2019, 8, 30), D(2022, 2, 28)),26 # settlement on / next to a coupon date27 (D(2020, 5, 15), D(2025, 11, 15)), (D(2020, 5, 14), D(2025, 11, 15)), (D(2020, 5, 16), D(2025, 11, 15)),28 # settlement on the 30th / 31st29 (D(2021, 3, 31), D(2030, 9, 15)), (D(2021, 7, 31), D(2029, 1, 30)), (D(2021, 10, 30), D(2031, 4, 30)), (D(2022, 5, 31), D(2026, 12, 31)),30 # last coupon period31 (D(2024, 9, 20), D(2024, 12, 15)), (D(2024, 1, 10), D(2024, 3, 31)), (D(2023, 12, 29), D(2024, 2, 29)), (D(2025, 6, 2), D(2025, 6, 30)),32 # long and odd33 (D(1995, 6, 6), D(2045, 3, 1)), (D(2000, 2, 29), D(2010, 1, 10)), (D(2016, 12, 25), D(2019, 7, 7)), (D(2030, 11, 5), D(2031, 5, 5)),34]3536s = R.to_serial37disagreements = []383940# Reasons why LibreOffice is not an Excel-compatible reference for a class of inputs. A case tagged41# with one of them keeps the LibreOffice value when it agrees and the Python value alone otherwise.42# Each reason was established against Excel-generated values (see gen_excel_reference.py).43LAST_PERIOD = "last coupon period: LibreOffice compounds; Excel uses simple interest (T1/T2 − T3)"44E_MINUS_A = "Excel measures the time to the next coupon as (E − A)/E; LibreOffice uses COUPDAYSNC/E (PRICE, YIELD) or YEARFRAC × frequency (DURATION)"45NOT_YEARFRAC = "LibreOffice does not apply its own YEARFRAC day count in this function (off by a day in leap years, 365-day actual/actual year)"46MAT_TERMS = "Excel takes B from issue → settlement and DSM = DIM − A; LibreOffice uses three separate YEARFRAC values"47QUASI = "Excel sums Aᵢ/NLᵢ over quasi-coupon periods; LibreOffice returns par × rate × YEARFRAC and ignores first_interest and calc_method"48BILL_DAYS = "LibreOffice counts 30/360 days plus one; Excel counts actual days (and switches formula beyond 182 days)"49NC_30360 = "COUPDAYSNC, 30/360 bases: Excel's count differs from E − A around the end of February"505152def add(name, args, py, tol=1e-9, lotol=None, lo=True, note=None, dev=None):53 f = "=%s(%s)" % (name, ",".join(str(a) if not isinstance(a, float) else repr(a) for a in args))54 meta = dict(py=py, tol=tol, lo=lo)55 if dev and not isinstance(py, str): meta["lo_dev"] = dev56 if lotol: meta["lotol"] = lotol57 if note: meta["note"] = note58 fx.case(f, **meta)596061# ---- COUP* ----------------------------------------------------------------------------------62for (st, mt), freq, basis in itertools.product(PAIRS, (1, 2, 4), range(5)):63 c = R.coup(st, mt, freq, basis)64 a = [s(st), s(mt), freq, basis]65 add("COUPPCD", a, s(c["pcd"])); add("COUPNCD", a, s(c["ncd"])); add("COUPNUM", a, c["n"])66 add("COUPDAYBS", a, c["a"]); add("COUPDAYS", a, c["e"]); add("COUPDAYSNC", a, c["dsc"], dev=NC_30360 if basis in (0, 4) else None)67for (st, mt) in PAIRS[:6]:68 for freq in (1, 2, 4): # basis omitted = 069 c = R.coup(st, mt, freq, 0)70 add("COUPDAYBS", [s(st), s(mt), freq], c["a"]); add("COUPNUM", [s(st), s(mt), freq], c["n"]); add("COUPNCD", [s(st), s(mt), freq], s(c["ncd"]))71ERRV = "#VALUE!"72for name in ("COUPDAYBS", "COUPDAYS", "COUPDAYSNC", "COUPNCD", "COUPNUM", "COUPPCD"):73 add(name, [40000, 40000, 2, 0], ERRV); add(name, [40100, 40000, 2, 0], ERRV); add(name, [40000, 41000, 3, 0], ERRV)74 add(name, [40000, 41000, 2, 5], ERRV); add(name, [40000, 41000, 2, -1], ERRV); add(name, [40000, 41000, 12, 1], ERRV)7576# ---- PRICE / YIELD / DURATION / MDURATION -----------------------------------------------------77BOND_TERMS = [(0.0575, 0.065, 100), (0.03, 0.0125, 100), (0.0, 0.05, 100), (0.08, 0.08, 105.5), (0.045, 0.0, 100)]78for (st, mt), freq, basis in itertools.product(PAIRS, (1, 2, 4), range(5)):79 for rate, yld, red in BOND_TERMS:80 p = R.price(st, mt, rate, yld, red, freq, basis)81 last = LAST_PERIOD if R.coupons(st, mt, freq)[2] == 1 else (E_MINUS_A if basis in (2, 3) else None)82 add("PRICE", [s(st), s(mt), rate, yld, red, freq, basis], p, tol=1e-9, dev=last)83 pr = round(p, 3)84 if pr > 0:85 y = R.bond_yield(st, mt, rate, pr, red, freq, basis)86 if y >= -1e-12:87 add("YIELD", [s(st), s(mt), rate, pr, red, freq, basis], y, tol=1e-8, lotol=1e-5, dev=last)88 for cpn, yld in [(0.08, 0.09), (0.03, 0.05), (0.0, 0.04), (0.05, 0.0)]:89 d = R.duration(st, mt, cpn, yld, freq, basis)90 add("DURATION", [s(st), s(mt), cpn, yld, freq, basis], d, tol=1e-9, dev=E_MINUS_A)91 add("MDURATION", [s(st), s(mt), cpn, yld, freq, basis], d / (1 + yld / freq), tol=1e-9, dev=E_MINUS_A)92add("PRICE", [s(D(2008, 2, 15)), s(D(2017, 11, 15)), 0.0575, 0.065, 100, 2], R.price(D(2008, 2, 15), D(2017, 11, 15), 0.0575, 0.065, 100, 2, 0))93add("YIELD", [s(D(2008, 2, 15)), s(D(2016, 11, 15)), 0.0575, 95.04287, 100, 2], R.bond_yield(D(2008, 2, 15), D(2016, 11, 15), 0.0575, 95.04287, 100, 2, 0), tol=1e-8, lotol=1e-5)94for bad in ([40000, 39000, 0.05, 0.05, 100, 2, 0], [40000, 41000, -0.01, 0.05, 100, 2, 0], [40000, 41000, 0.05, -0.01, 100, 2, 0],95 [40000, 41000, 0.05, 0.05, 0, 2, 0], [40000, 41000, 0.05, 0.05, 100, 3, 0], [40000, 41000, 0.05, 0.05, 100, 2, 7]):96 add("PRICE", bad, ERRV)97for bad in ([40000, 39000, 0.05, 95, 100, 2, 0], [40000, 41000, -0.01, 95, 100, 2, 0], [40000, 41000, 0.05, 0, 100, 2, 0], [40000, 41000, 0.05, 95, -1, 2, 0],98 [40000, 41000, 0.05, 95, 100, 5, 0]):99 add("YIELD", bad, ERRV)100for bad in ([40000, 39000, 0.05, 0.05, 2, 0], [40000, 41000, -0.05, 0.05, 2, 0], [40000, 41000, 0.05, -0.05, 2, 0], [40000, 41000, 0.05, 0.05, 3, 0], [40000, 41000, 0.05, 0.05, 2, 9]):101 add("DURATION", bad, ERRV); add("MDURATION", bad, ERRV)102103# ---- discount / at-maturity securities ------------------------------------------------------104SHORT = [105 (D(2007, 1, 25), D(2007, 6, 15)), (D(2008, 2, 15), D(2008, 5, 15)), (D(2008, 2, 16), D(2008, 3, 1)), (D(2019, 11, 30), D(2020, 2, 29)),106 (D(2020, 2, 29), D(2021, 2, 28)), (D(2023, 12, 31), D(2024, 12, 31)), (D(2024, 1, 31), D(2024, 3, 31)), (D(2021, 8, 31), D(2021, 9, 30)),107 (D(2022, 3, 1), D(2023, 3, 1)), (D(2018, 7, 1), D(2021, 1, 1)), (D(2016, 2, 28), D(2020, 2, 29)), (D(2023, 5, 31), D(2023, 12, 31)),108 (D(2015, 10, 10), D(2016, 4, 9)), (D(2019, 1, 30), D(2019, 2, 28)),109]110for (st, mt), basis in itertools.product(SHORT, range(5)):111 yf = R.yearfrac(st, mt, basis)112 a = [s(st), s(mt)]113 for pr, red in [(97.975, 100), (99.2, 101.5), (88.0, 100)]:114 add("DISC", a + [pr, red, basis], (red - pr) / red / yf); add("YIELDDISC", a + [pr, red, basis], (red / pr - 1) / yf)115 for inv, red in [(1000000, 1014420), (500, 512.25)]:116 add("INTRATE", a + [inv, red, basis], (red - inv) / inv / yf, dev=NOT_YEARFRAC)117 for inv, disc in [(1000000, 0.0575), (2500, 0.021)]:118 add("RECEIVED", a + [inv, disc, basis], inv / (1 - disc * yf), dev=NOT_YEARFRAC)119 for disc, red in [(0.0525, 100), (0.019, 102)]:120 add("PRICEDISC", a + [disc, red, basis], red * (1 - disc * yf), dev=NOT_YEARFRAC)121 for back in (30, 200, 400):122 issue = R.to_date(s(st) - back)123 for rate, yld in [(0.061, 0.061), (0.04, 0.0725)]:124 pm = R.pricemat(st, mt, issue, rate, yld, basis)125 add("PRICEMAT", a + [s(issue), rate, yld, basis], pm, dev=MAT_TERMS)126 prc = round(pm, 4)127 add("YIELDMAT", a + [s(issue), rate, prc, basis], R.yieldmat(st, mt, issue, rate, prc, basis), tol=1e-8, dev=MAT_TERMS)128 add("ACCRINTM", [s(st), s(mt), 0.1, 1000, basis], 1000 * 0.1 * yf, dev=NOT_YEARFRAC); add("ACCRINTM", [s(st), s(mt), 0.0375, 250000, basis], 250000 * 0.0375 * yf, dev=NOT_YEARFRAC)129add("ACCRINTM", [39539, 39614, 0.1, 1000, 3], 1000 * 0.1 * 75 / 365); add("ACCRINTM", [39539, 39614, 0.1], 1000 * 0.1 * R.yearfrac(R.to_date(39539), R.to_date(39614), 0))130for name, bad in [("DISC", [40000, 39000, 95, 100, 0]), ("DISC", [40000, 40100, 0, 100, 0]), ("DISC", [40000, 40100, 95, 100, 5]), ("INTRATE", [40000, 40100, 0, 100, 0]),131 ("RECEIVED", [40000, 40100, 100, 0, 0]), ("PRICEDISC", [40000, 40100, 0, 100, 0]), ("YIELDDISC", [40000, 40100, 0, 100, 0]),132 ("PRICEMAT", [40000, 40100, 39900, -0.1, 0.05, 0]), ("YIELDMAT", [40000, 40100, 39900, 0.05, 0, 0]), ("ACCRINTM", [40100, 40000, 0.1, 1000, 0]),133 ("ACCRINTM", [40000, 40100, 0, 1000, 0]), ("ACCRINTM", [40000, 40100, 0.1, 0, 0])]:134 add(name, bad, ERRV)135136# ---- ACCRINT ----------------------------------------------------------------------------------137ACC = [138 (D(2008, 3, 1), D(2008, 8, 31), D(2008, 5, 1)), (D(2008, 3, 5), D(2008, 8, 31), D(2008, 5, 1)), (D(2008, 4, 5), D(2008, 8, 31), D(2008, 5, 1)),139 (D(2020, 1, 15), D(2020, 7, 15), D(2020, 4, 1)), (D(2019, 11, 30), D(2020, 5, 31), D(2020, 2, 29)), (D(2023, 2, 28), D(2023, 8, 31), D(2023, 6, 30)),140 (D(2021, 6, 1), D(2021, 12, 1), D(2021, 11, 30)), (D(2022, 9, 10), D(2023, 3, 10), D(2022, 9, 11)), (D(2024, 2, 29), D(2024, 8, 29), D(2024, 3, 31)),141]142for (issue, first, st), freq, basis in itertools.product(ACC, (1, 2, 4), range(5)):143 for rate, par in [(0.1, 1000), (0.0425, 100)]:144 add("ACCRINT", [s(issue), s(first), s(st), rate, par, freq, basis], R.accrint(issue, first, st, rate, par, freq, basis), dev=QUASI)145# settlement after several coupon dates146LONG = [(D(2012, 2, 2), D(2012, 3, 30), D(2013, 12, 4)), (D(2018, 1, 15), D(2018, 7, 15), D(2021, 3, 20)), (D(2019, 8, 31), D(2020, 2, 29), D(2022, 5, 17))]147for (issue, first, st), freq, basis in itertools.product(LONG, (1, 2, 4), range(5)):148 add("ACCRINT", [s(issue), s(first), s(st), 0.1, 1000, freq, basis], R.accrint(issue, first, st, 0.1, 1000, freq, basis), dev=QUASI)149 add("ACCRINT", [s(issue), s(first), s(st), 0.1, 1000, freq, basis, "FALSE"], R.accrint(issue, first, st, 0.1, 1000, freq, basis, False), lo=False,150 note="calc_method FALSE: accrual from first_interest (Excel documentation); LibreOffice ignores the argument")151add("ACCRINT", [39508, 39691, 39569, 0.1, 1000, 2, 0], 1000 * 0.1 * 60 / 360.0); add("ACCRINT", [39508, 39691, 39569, 0.1, 1000, 2], 1000 * 0.1 * 60 / 360.0)152for bad in ([39569, 39691, 39508, 0.1, 1000, 2, 0], [39508, 39691, 39569, 0, 1000, 2, 0], [39508, 39691, 39569, 0.1, 0, 2, 0], [39508, 39691, 39569, 0.1, 1000, 3, 0],153 [39508, 39691, 39569, 0.1, 1000, 2, 6]):154 add("ACCRINT", bad, ERRV)155156# ---- Treasury bills -------------------------------------------------------------------------157BILLS = [(D(2008, 3, 31), D(2008, 6, 1)), (D(2024, 1, 2), D(2024, 4, 2)), (D(2023, 11, 15), D(2024, 5, 15)), (D(2020, 2, 28), D(2020, 3, 1)),158 (D(2022, 7, 1), D(2022, 12, 30)), (D(2021, 1, 4), D(2021, 7, 5)), (D(2019, 6, 3), D(2019, 6, 4)), (D(2024, 1, 2), D(2024, 7, 2))]159for (st, mt), disc in itertools.product(BILLS, (0.0914, 0.09, 0.0025, 0.0535)):160 dsm = (mt - st).days161 a = [s(st), s(mt)]162 add("TBILLPRICE", a + [disc], 100 * (1 - disc * dsm / 360.0), dev=BILL_DAYS)163 pr = round(100 * (1 - disc * dsm / 360.0), 4)164 add("TBILLYIELD", a + [pr], (100 - pr) / pr * 360.0 / dsm, dev=BILL_DAYS)165 add("TBILLEQ", a + [disc], R.tbilleq(st, mt, disc), tol=1e-9, dev=BILL_DAYS)166# bills longer than 182 days: Excel switches to the semi-annual bond-equivalent formula, LibreOffice does not167for (st, mt), disc in itertools.product([(D(2023, 1, 5), D(2023, 12, 28)), (D(2022, 3, 1), D(2023, 3, 1)), (D(2024, 2, 1), D(2024, 8, 2)), (D(2021, 6, 30), D(2022, 6, 29))], (0.05, 0.0914, 0.012)):168 dsm = (mt - st).days169 add("TBILLPRICE", [s(st), s(mt), disc], 100 * (1 - disc * dsm / 360.0), dev=BILL_DAYS)170 add("TBILLYIELD", [s(st), s(mt), round(100 * (1 - disc * dsm / 360.0), 4)], (100 - round(100 * (1 - disc * dsm / 360.0), 4)) / round(100 * (1 - disc * dsm / 360.0), 4) * 360.0 / dsm, dev=BILL_DAYS)171 add("TBILLEQ", [s(st), s(mt), disc], R.tbilleq(st, mt, disc), lo=False, note="more than 182 days: textbook bond-equivalent yield; LibreOffice keeps the short formula")172for name in ("TBILLEQ", "TBILLPRICE", "TBILLYIELD"):173 add(name, [40000, 39990, 0.05 if name != "TBILLYIELD" else 98], ERRV); add(name, [40000, 40100, 0], ERRV)174 # Excel: #NUM! when maturity is not after settlement or more than one year later; LibreOffice computes a value175 add(name, [40000, 40000, 0.05 if name != "TBILLYIELD" else 98], ERRV, lo=False); add(name, [40000, 40400, 0.05 if name != "TBILLYIELD" else 98], ERRV, lo=False)176177# ---- write, then compare the two references ---------------------------------------------------178doc = fx.write()179by_fn = {}180for c in doc["cases"]:181 if c["source"] != "libreoffice+python":182 continue183 e, p = c["expect"], c["py"]184 if not close(e, p, c.get("lotol", c.get("tol", 1e-9))):185 name = c["f"][1:].split("(")[0]186 by_fn.setdefault(name, []).append(c)187total = sum(len(v) for v in by_fn.values())188for name, cs in sorted(by_fn.items()):189 print("%s: %d disagreements" % (name, len(cs)))190 for c in cs[:6]:191 print(" %s LO=%r PY=%r" % (c["f"], c["expect"], c["py"]))192counts, deviating = {}, {}193for c in doc["cases"]:194 src = c["source"]195 if src.startswith("python (LibreOffice deviates"):196 name = c["f"][1:].split("(")[0]197 deviating[name] = deviating.get(name, 0) + 1198 src = "python (documented LibreOffice deviation)"199 counts[src] = counts.get(src, 0) + 1200per_fn = {}201for c in doc["cases"]:202 name = c["f"][1:].split("(")[0]203 k = "lo" if c["source"].startswith("libreoffice") else "py"204 per_fn.setdefault(name, {"lo": 0, "py": 0})[k] += 1205print("cases per function (LibreOffice+Python agree / Python only):", {k: "%d/%d" % (v["lo"], v["py"]) for k, v in sorted(per_fn.items())})206print(counts, "unexplained disagreements:", total)207sys.exit(1 if total else 0)208