Spaces:
Running
Running
File size: 4,416 Bytes
0ebef7d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 | """
make_sample_pdf.py — build a realistic bank-statement PDF from the sample xlsx.
This gives us a *test input* (a text-based PDF that looks like a real statement)
so we can prove the Python-first converter works end-to-end.
It reads the transactions out of Sample-CSV-Bank-Statement.xlsx, sorts them
chronologically, recomputes a clean running balance from a $0.00 opening, and
renders a header + a ruled transaction table.
"""
from __future__ import annotations
import datetime as dt
from pathlib import Path
import openpyxl
from reportlab.lib import colors
from reportlab.lib.pagesizes import A4
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.lib.units import mm
from reportlab.platypus import (
SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle,
)
HERE = Path(__file__).resolve().parent
XLSX = HERE.parent / "Sample-CSV-Bank-Statement.xlsx"
OUT_PDF = HERE / "sample_statement.pdf"
def _to_float(v):
if v is None or v == "":
return None
try:
return float(str(v).replace(",", "").replace("$", "").strip())
except ValueError:
return None
def load_transactions(xlsx_path: Path):
wb = openpyxl.load_workbook(xlsx_path, data_only=True)
ws = wb.active
rows = list(ws.iter_rows(values_only=True))
# find the header row (the one containing "Date")
header_idx = next(
i for i, r in enumerate(rows)
if r and any(str(c).strip().lower() == "date" for c in r if c is not None)
)
header = [str(c).strip().lower() if c is not None else "" for c in rows[header_idx]]
col = {name: header.index(name) for name in ("date", "description", "debit", "credit") if name in header}
txns = []
for r in rows[header_idx + 1:]:
if not r or r[col["date"]] is None:
continue
d = r[col["date"]]
if isinstance(d, dt.datetime):
date = d.date()
else:
continue
txns.append({
"date": date,
"description": str(r[col["description"]] or "").strip(),
"debit": _to_float(r[col["debit"]]),
"credit": _to_float(r[col["credit"]]),
})
txns.sort(key=lambda t: t["date"])
return txns
def build_pdf(txns, out_path: Path, opening: float = 0.00):
styles = getSampleStyleSheet()
doc = SimpleDocTemplate(str(out_path), pagesize=A4,
topMargin=18 * mm, bottomMargin=18 * mm,
leftMargin=16 * mm, rightMargin=16 * mm)
# recompute a clean running balance so the statement reconciles
running = opening
table_rows = [["Date", "Description", "Debit", "Credit", "Balance"]]
for t in txns:
running += (t["credit"] or 0.0) - (t["debit"] or 0.0)
table_rows.append([
t["date"].strftime("%d/%m/%Y"),
t["description"],
f"${t['debit']:,.2f}" if t["debit"] else "",
f"${t['credit']:,.2f}" if t["credit"] else "",
f"${running:,.2f} CR",
])
closing = running
period = f"{txns[0]['date'].strftime('%d/%m/%Y')} to {txns[-1]['date'].strftime('%d/%m/%Y')}"
story = [
Paragraph("<b>Macquarie Cash Management Account</b>", styles["Title"]),
Spacer(1, 6),
Paragraph("Account: 123456789 - CASH MANAGEMENT ACCOUNT", styles["Normal"]),
Paragraph("Account Holder: ABC Super Fund", styles["Normal"]),
Paragraph(f"Statement Period: {period}", styles["Normal"]),
Paragraph(f"Opening Balance: ${opening:,.2f}", styles["Normal"]),
Paragraph(f"Closing Balance: ${closing:,.2f}", styles["Normal"]),
Spacer(1, 12),
]
tbl = Table(table_rows, colWidths=[24 * mm, 70 * mm, 24 * mm, 24 * mm, 28 * mm])
tbl.setStyle(TableStyle([
("GRID", (0, 0), (-1, -1), 0.5, colors.grey),
("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#1f3a5f")),
("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
("FONTSIZE", (0, 0), (-1, -1), 8),
("ALIGN", (2, 0), (-1, -1), "RIGHT"),
("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
]))
story.append(tbl)
doc.build(story)
return closing
if __name__ == "__main__":
txns = load_transactions(XLSX)
closing = build_pdf(txns, OUT_PDF)
print(f"Wrote {OUT_PDF}")
print(f"{len(txns)} transactions, closing balance ${closing:,.2f}")
|