Repository Public
1"""Converts Excel-generated reference data into fixtures.23Source: the test data of the ExcelFinancialFunctions project (https://github.com/fsprojects/ExcelFinancialFunctions,4Apache License 2.0, tests/ExcelFinancialFunctions.Tests/testdata). Those files hold results computed5by Microsoft Excel itself for large matrices of arguments; they are the only reference in this test6suite that comes from Excel rather than from an independent re-implementation.78Usage:9 mkdir /tmp/eff && cd /tmp/eff10 base=https://raw.githubusercontent.com/fsprojects/ExcelFinancialFunctions/master/tests/ExcelFinancialFunctions.Tests/testdata11 for f in accrint accrintm coupdays coupdaysbs coupdaysnc coupncd coupnum couppcd cumipmt cumprinc db ddb disc dollarde dollarfr \12 duration effect fv fvschedule intrate ipmt irr ispmt mduration mirr nominal nper npv pmt ppmt price pricedisc pricemat pv \13 rate received sln syd tbilleq tbillprice tbillyield vdb xirr yearfrac yielddisc yieldmat; do curl -sSO $base/$f.test; done14 for f in pduration rri yieldnegative; do curl -sSO $base/$f.csv; done15 python3 -I gen_excel_reference.py /tmp/eff [max cases per function]1617The files are parsed as plain text only (numbers, dates and a closed list of enumeration names).18By default every case is kept; a limit thins large files with a fixed stride.19"""20import json21import os22import sys23from datetime import date, datetime, timedelta2425SRC = sys.argv[1]26LIMIT = int(sys.argv[2]) if len(sys.argv) > 2 else 10 ** 927OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), "..", "Fixtures")28EPOCH = date(1899, 12, 30)2930FREQ = {"Annual": 1, "SemiAnnual": 2, "Quarterly": 4}31BASIS = {"UsPsa30_360": 0, "ActualActual": 1, "Actual360": 2, "Actual365": 3, "Europ30_360": 4}32DUE = {"EndOfPeriod": 0, "BeginningOfPeriod": 1}33SWITCH = {"SwitchToStraightLine": "FALSE", "DontSwitchToStraightLine": "TRUE"}343536def serial(text):37 d = datetime.strptime(text.split(" ")[0], "%m/%d/%Y").date()38 return str((d - EPOCH).days)394041def ticks_to_serial(text):42 days = round(float(text) / 864e9) # .NET ticks (100 ns) since 0001-01-0143 return (date(1, 1, 1) + timedelta(days=days) - EPOCH).days444546def num(text):47 float(text) # validates: only numbers may enter a formula48 return text495051def arr(text, conv=num):52 return "{" + ",".join(conv(x) for x in text.split(";")) + "}"535455def enum(table):56 return lambda text: str(table[text])575859D, N, F, B, T = serial, num, enum(FREQ), enum(BASIS), enum(DUE)60SPECS = {61 # file: (function, converters of the arguments, tolerance)62 "accrint": ("ACCRINT", [D, D, D, N, N, F, B], 1e-9), "accrintm": ("ACCRINTM", [D, D, N, N, B], 1e-9),63 "coupdays": ("COUPDAYS", [D, D, F, B], 1e-9), "coupdaysbs": ("COUPDAYBS", [D, D, F, B], 1e-9), "coupdaysnc": ("COUPDAYSNC", [D, D, F, B], 1e-9),64 "coupncd": ("COUPNCD", [D, D, F, B], 0), "couppcd": ("COUPPCD", [D, D, F, B], 0), "coupnum": ("COUPNUM", [D, D, F, B], 1e-9),65 "cumipmt": ("CUMIPMT", [N, N, N, N, N, T], 1e-9), "cumprinc": ("CUMPRINC", [N, N, N, N, N, T], 1e-9),66 "db": ("DB", [N, N, N, N, N], 1e-9), "ddb": ("DDB", [N, N, N, N, N], 1e-9), "disc": ("DISC", [D, D, N, N, B], 1e-9),67 "dollarde": ("DOLLARDE", [N, N], 1e-9), "dollarfr": ("DOLLARFR", [N, N], 1e-9),68 "duration": ("DURATION", [D, D, N, N, F, B], 1e-9), "mduration": ("MDURATION", [D, D, N, N, F, B], 1e-9),69 "effect": ("EFFECT", [N, N], 1e-9), "nominal": ("NOMINAL", [N, N], 1e-9),70 "fv": ("FV", [N, N, N, N, T], 1e-9), "pv": ("PV", [N, N, N, N, T], 1e-9), "pmt": ("PMT", [N, N, N, N, T], 1e-9), "nper": ("NPER", [N, N, N, N, T], 1e-9),71 "ipmt": ("IPMT", [N, N, N, N, N, T], 1e-9), "ppmt": ("PPMT", [N, N, N, N, N, T], 1e-9), "ispmt": ("ISPMT", [N, N, N, N], 1e-9),72 "fvschedule": ("FVSCHEDULE", [N, arr], 1e-9), "irr": ("IRR", [arr, N], 1e-7), "mirr": ("MIRR", [arr, N, N], 1e-9), "npv": ("NPV", [N, arr], 1e-9),73 "xirr": ("XIRR", [arr, lambda t: arr(t, serial), N], 1e-6), "rate": ("RATE", [N, N, N, N, T, N], 1e-7),74 "intrate": ("INTRATE", [D, D, N, N, B], 1e-9), "received": ("RECEIVED", [D, D, N, N, B], 1e-9), "pricedisc": ("PRICEDISC", [D, D, N, N, B], 1e-9),75 "pricemat": ("PRICEMAT", [D, D, D, N, N, B], 1e-9), "yielddisc": ("YIELDDISC", [D, D, N, N, B], 1e-9), "yieldmat": ("YIELDMAT", [D, D, D, N, N, B], 1e-9),76 "price": ("PRICE", [D, D, N, N, N, F, B], 1e-9), "sln": ("SLN", [N, N, N], 1e-9), "syd": ("SYD", [N, N, N, N], 1e-9),77 "vdb": ("VDB", [N, N, N, N, N, N, enum(SWITCH)], 1e-9),78 "tbilleq": ("TBILLEQ", [D, D, N], 1e-9), "tbillprice": ("TBILLPRICE", [D, D, N], 1e-9), "tbillyield": ("TBILLYIELD", [D, D, N], 1e-9),79}808182def thin(rows):83 if len(rows) <= LIMIT:84 return rows85 step = len(rows) / float(LIMIT)86 return [rows[int(i * step)] for i in range(LIMIT)]878889cases, counts = [], {}90for name in sorted(SPECS):91 fn, convs, tol = SPECS[name]92 with open(os.path.join(SRC, name + ".test"), encoding="utf-8-sig") as fh:93 rows = [line.strip().split(",") for line in fh if line.strip()]94 for row in thin(rows):95 assert len(row) == len(convs) + 1, (name, row)96 args = [c(x) for c, x in zip(convs, row)]97 expect = ticks_to_serial(row[-1]) if name in ("coupncd", "couppcd") else float(row[-1])98 cases.append({"f": "=%s(%s)" % (fn, ",".join(args)), "expect": expect, "tol": tol, "source": "excel"})99 counts[fn] = min(len(rows), LIMIT)100101# CSV files exported from Excel with a header; "#NUM!" rows are failure cases102for name, fn in (("pduration", "PDURATION"), ("rri", "RRI")):103 with open(os.path.join(SRC, name + ".csv"), encoding="utf-8-sig") as fh:104 rows = [line.strip().split(",") for line in fh if line.strip()][1:]105 for row in rows:106 expect = "#NUM!" if "#NUM!" in row[-1] else float(row[-1])107 cases.append({"f": "=%s(%s)" % (fn, ",".join(num(x) for x in row[:-1])), "expect": expect, "tol": 1e-8, "source": "excel"})108 counts[fn] = len(rows)109110# negative yields: Excel prices "reverse-figured" from YIELD results; the project's own comment says they111# match to 1e-6 only down to about −0.05 %, so only the YIELD direction is used, with yields above −1 %112with open(os.path.join(SRC, "yieldnegative.csv"), encoding="utf-8-sig") as fh:113 rows = [line.strip().split(",") for line in fh if line.strip()][1:]114rows = [r for r in rows if "#NUM!" not in r[-1] and -0.01 < float(r[3]) < 0]115for s, m, rate, yld, red, freq, basis, price in thin(rows)[:LIMIT // 3]:116 def plain(t): return str((datetime.strptime(t, "%m/%d/%Y").date() - EPOCH).days)117 cases.append({"f": "=YIELD(%s,%s,%s,%s,%s,%d,%d)" % (plain(s), plain(m), num(rate), num(price), num(red), FREQ[freq], BASIS[basis]),118 "expect": float(yld), "tol": 1e-6, "source": "excel"})119counts["YIELD"] = min(len(rows), LIMIT // 3)120121# YEARFRAC is not a function of this package: the rows feed the DayCount API test directly122with open(os.path.join(SRC, "yearfrac.test"), encoding="utf-8-sig") as fh:123 rows = [line.strip().split(",") for line in fh if line.strip()]124yearfrac = [[int(serial(a)), int(serial(b)), BASIS[basis], float(v)] for a, b, basis, v in rows]125126doc = {"name": "excel_reference",127 "description": "Results computed by Microsoft Excel, converted from the test data of ExcelFinancialFunctions "128 "(https://github.com/fsprojects/ExcelFinancialFunctions, Copyright 2010-2013, Apache License 2.0). "129 "Changes: dates turned into serial numbers, enumerations into Excel codes, rows into formulas.",130 "license": "Apache-2.0 (http://www.apache.org/licenses/LICENSE-2.0)",131 "data": {}, "cases": cases, "yearfrac": yearfrac}132with open(os.path.join(OUT, "excel_reference.json"), "w") as fh:133 json.dump(doc, fh, separators=(",", ":"))134print(len(cases), "cases;", len(yearfrac), "yearfrac rows")135print(counts)136