A concise companion to the calculation workbook. Use the workbook as the first read for input, formula, and source review.
Tests calculated{len(rows)}
Breaches{breach_count}
Tight passes{tight_count}
Reliance gate: Calculated headroom must be reconciled to operative definitions, amendments or waivers, and applicable covenant certificates before decision use.
Calculated tests
Covenant
Period
Actual
Threshold
Headroom
Cushion
Status
{table_rows}
Calculation source: {escape(str(source_path))}. Backing calculation rows are retained as support data.
"""
path.write_text(document, encoding="utf-8")
REQUIRED = {"covenant", "period", "test_type", "actual", "threshold"}
MAX_TYPES = {"max_ratio", "max_amount"}
MIN_TYPES = {"min_ratio", "min_amount"}
def to_float(value: str) -> float:
text = str(value).strip().replace(",", "").replace("x", "").replace("%", "")
if text == "":
raise ValueError("blank numeric field")
return float(text)
def status_from(headroom: float) -> str:
if headroom < 0:
return "breach"
if headroom == 0:
return "at_threshold"
return "pass"
def main() -> int:
parser = argparse.ArgumentParser(
description="Calculate covenant headroom from covenant test CSV."
)
parser.add_argument("csv_path", help="Input covenant test CSV")
parser.add_argument("--outdir", default="output/headroom", help="Output directory")
args = parser.parse_args()
path = Path(args.csv_path)
outdir = Path(args.outdir)
outdir.mkdir(parents=True, exist_ok=True)
with path.open(newline="", encoding="utf-8") as f:
reader = csv.DictReader(f)
headers = set(reader.fieldnames or [])
missing = REQUIRED - headers
if missing:
raise SystemExit(f"Missing required columns: {', '.join(sorted(missing))}")
rows = list(reader)
out_rows: list[dict[str, str]] = []
for idx, row in enumerate(rows, start=1):
test_type = row["test_type"].strip().lower()
actual = to_float(row["actual"])
threshold = to_float(row["threshold"])
if test_type in MAX_TYPES:
headroom = threshold - actual
elif test_type in MIN_TYPES:
headroom = actual - threshold
else:
raise SystemExit(f"Row {idx}: unsupported test_type '{row['test_type']}'")
cushion_pct = (headroom / abs(threshold)) if threshold else 0.0
status = status_from(headroom)
out_rows.append(
{
"covenant": row["covenant"],
"period": row["period"],
"test_type": test_type,
"actual": f"{actual:.6g}",
"threshold": f"{threshold:.6g}",
"headroom": f"{headroom:.6g}",
"cushion_pct": f"{cushion_pct:.2%}",
"status": status,
"units": row.get("units", ""),
"definition_basis": row.get("definition_basis", ""),
"source": row.get("source", ""),
"notes": row.get("notes", ""),
}
)
csv_dir = support_dir(outdir)
out_path = csv_dir / "covenant_headroom.csv"
headers = [
"covenant",
"period",
"test_type",
"actual",
"threshold",
"headroom",
"cushion_pct",
"status",
"units",
"definition_basis",
"source",
"notes",
]
with out_path.open("w", newline="", encoding="utf-8") as f:
writer = csv.DictWriter(f, fieldnames=headers)
writer.writeheader()
writer.writerows(out_rows)
breach_count = sum(1 for r in out_rows if r["status"] == "breach")
tight_count = sum(
1
for r in out_rows
if r["status"] != "breach"
and abs(float(r["headroom"])) <= 0.1 * max(abs(float(r["threshold"])), 1.0)
)
workbook_path = outdir / "covenant_headroom.xlsx"
write_cover_first_workbook(
workbook_path,
[
["Covenant Headroom"],
["Tests calculated", len(out_rows)],
["Breaches", breach_count],
["Tight passes", tight_count],
["First read", "Use this workbook first. CSV output is support/import material."],
],
{
"Headroom": dict_rows_to_sheet(out_rows, headers),
"Sources": [
["source", "path"],
["input_csv", str(path)],
["support_csv", str(out_path)],
],
},
)
report_path = outdir / "covenant_headroom_report.html"
_write_headroom_summary(report_path, out_rows, breach_count, tight_count, path)
write_artifact_manifest(
outdir,
"covenant-package-analyzer",
"workbook",
workbook_path,
companion_deliverables=[
artifact_item(
report_path,
"companion_deliverable",
"html",
"Standalone HTML covenant headroom summary.",
True,
True,
)
],
support_artifacts=[
artifact_item(
out_path,
"support_artifact",
"csv",
"Raw covenant headroom rows.",
False,
True,
"CSV is import/audit support; workbook is first read.",
),
],
blocked_or_partial_status={
"status": "partial",
"reason": "Headroom uses provided covenant definitions and must be reconciled to governing documents.",
"missing_inputs": [
"Operative covenant definitions",
"Amendments/waivers",
"Source covenant certificates",
],
},
)
print(f"[OK] Wrote {workbook_path}, {report_path}, and support CSV {out_path}")
return 0
if __name__ == "__main__":
raise SystemExit(main())