SPB Git

Repository Public

gen_securities.py 14.8 KB 208 lines python

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
Files main
Menu

Theme