Balance Sheet Reconciliation Pack

Balance Sheet Reconciliation Pack

A single bank reconciliation proves one account. A reconciliation pack proves the balance sheet — every asset, liability, and equity account tied to independent support, with nothing skipped and nothing plugged. The pack itself, not any one reconciliation in it, is usually the first document an auditor asks for at the start of fieldwork, because it answers a question no single reconciliation can: is anything missing?

The one rule that matters most

Coverage is not optional, and neither is the proof. An account with a balance and no supporting schedule is not a clean account — it is the highest-risk line in the pack, because nobody has looked at it. And for every account that does have support, the same rule from bank-rec-to-gl applies without exception: never plug. An unexplained difference stays visible as unexplained. A vaguely labeled reconciling item that rolls forward unresolved for months is not timing — it is a finding, and this pack is built to surface it rather than average it away.

Inputs

You need the trial balance and, ideally, the supporting schedule for every account on it. Ask for whatever is missing before starting — do not reconcile a partial chart and call it done.

1. Trial balance — every balance sheet account (asset, liability, equity) for the period, including zero-balance accounts. A zero-balance account with activity during the period is still a completeness question, not a non-event.
2. Supporting schedules / subledger exports — one export per account or account family: AP subledger, AR aging, fixed asset register, prepaid schedule, accrual worksheets, debt schedules, intercompany statements, cap table. If a schedule genuinely does not exist for an account, that is itself the finding — do not silently exclude the account from the pack to make the numbers look cleaner.
3. Prior month's rec pack, if one exists — specifically its open-item list. Without it you cannot tell a reconciling item that just appeared from one that has been rolling forward, unresolved, for six months under a different description.
4. Sign-off log, if the firm tracks one — preparer and reviewer per account, so the pack can report who has and hasn't signed, not just whether the numbers tie.

Confirm before starting: the trial balance totals agree to the balance sheet you're closing, and every schedule is cut off at the same date as the trial balance. A schedule cut three days earlier than the GL will never tie, and the difference will look like an error instead of a timing mismatch in your own inputs.

Step 1 — Build the first pass

python3 scripts/build_recon_pack.py \
  --trial-balance tb.csv \
  --schedules schedules.csv \
  --period 2026-01 \
  --out "Acme - Balance Sheet Recon Pack - 2026-01.xlsx"

Run it with just the trial balance and schedules first, before you have classified anything. For every account it reports the GL balance, the schedule total, and the raw difference. Any account with a balance and no rows in the schedules file comes back NO SCHEDULE — go get that schedule or document why the account is out of scope (dormant, immaterial, pending closure) before treating the pack as complete.

Step 2 — Classify every difference

This is the part that cannot be automated, and the part a reviewer reads. For each account where the raw difference is not zero, write one row per cause into reconciling_items.csv:

Re-run the script with --reconciling-items reconciling_items.csv. Every account where the classified items now fully explain the raw difference moves from INCOMPLETE to EXPLAINED. Anything still showing a nonzero unexplained amount stays INCOMPLETE — that status does not go away until a human explains the number, and it must never be forced to zero by widening a classification to fit.

unidentified is never a resting place. An account with an unidentified reconciling item is escalated in the Exceptions tab regardless of size. The two most common places an unidentified item hides a real problem are suspense/clearing accounts and small equity accounts — precisely the accounts most likely to be skipped because they "don't matter."

Step 3 — Age it, and watch for repeats

Pass --prior-pack (last period's open-item list) so aging is computed from the item's true first-appeared date, not from today just because it was retyped this period.

• Over 90 days — investigate; find out why it hasn't resolved.
• Over 180 days — presumptively stale. Ask whether it needs a correcting entry now rather than another period of "still working on it."
• Same item, 3+ consecutive packs (RECURRING) — this is the single most useful signal in the pack. A reconciling item that reappears identically, period after period, is almost never timing. It is a systemic posting error, a broken integration, or a control gap that keeps recreating the same difference. Name it in the memo; don't let it age quietly.

Step 4 — Sign-off

Pass --signoff with preparer and reviewer per account. An account is only ready to sign if its status is TIED or EXPLAINED and both a preparer and a reviewer (with a reviewer date) are on file. A tied account with no reviewer is not done — it is unreviewed, and the Completeness Summary reports it that way rather than folding it into the "clean" count.

Step 5 — Deliver

Workbook tabs:

1. Completeness Summary — the chart-wide dashboard: accounts covered vs. not, tied vs. explained vs. incomplete, total unexplained difference across the whole chart (must be zero), sign-off rate, and every account ranked by absolute GL balance so the biggest accounts get triaged first. This is the tab a controller or audit manager opens first.
2. Reconciliations — a signable four-line proof per account (GL balance, schedule total, reconciling items, unexplained difference) with preparer/reviewer blocks, exactly like a standalone bank reconciliation, repeated for the full chart.
3. Open Item List — every reconciling item across every account: description, amount, classification, age, bucket, recurring flag, whether a JE is required, and owner.
4. Proposed Journal Entries — debit/credit ready to post for every item flagged je_required, pulled straight from the classified reconciling items.
5. Aging — per-account totals bucketed 0–30 through 180+, with recurring items counted per account.
6. Exceptions & Memo — accounts with no schedule, accounts still incomplete, every unidentified item, and every recurring item, plus a standing note that a pack covering "most" of the chart is not a complete pack.

Then write a short memo in chat: period, accounts in chart, coverage rate, tie-out rate, sign-off rate, total unexplained difference (zero, or the pack is incomplete), and any recurring or unidentified items. Lead with exceptions if there are any — coverage gaps and unidentified items first, then the rest.

Where it stops

It computes coverage, ties balances to schedules, ages open items, and tells you exactly which accounts and which dollars are unexplained. It does not decide why a difference exists — that classification is the reviewer's judgment, the same as in bank-rec-to-gl. It also does not build the underlying schedules (the AP subledger, the fixed asset register, the AR aging) — those come from their own source systems or their own skills; this pack consumes them and proves the whole chart against them.

Security posture

Fully local: openpyxl only. No network calls, no uploads, no telemetry. Inputs are read-only. Intermediate files stay in the working directory and can be shredded after delivery.

Dependencies

pip install openpyxl

---

Built by Adopt AI — free to use and modify.

---

Appendix — bundled files

If you installed the .skill package these files are already in place and you can ignore this appendix. If you copied the skill as text, create the files below at the paths shown, alongside your SKILL.md. The skill will not run without them.

scripts/build_recon_pack.py

#!/usr/bin/env python3
"""
Build a balance sheet reconciliation pack: one reconciliation per account in
the trial balance, an aged open-item list across the whole chart, proposed
journal entries, and a completeness dashboard showing which accounts are
covered, tied out, and signed off.

This is bank-rec-to-gl's discipline (match, classify, age, never plug)
applied to every balance sheet account, not just cash. What is different
here is the completeness question: an account with a balance and no
supporting schedule is itself a finding, and the dashboard exists to make
that finding impossible to miss.

Usage:
    python3 build_recon_pack.py \
        --trial-balance tb.csv \
        --schedules schedules.csv \
        --reconciling-items reconciling_items.csv \
        --prior-pack prior_pack.csv \
        --signoff signoff.csv \
        --period 2026-01 \
        --out "Acme - Balance Sheet Recon Pack - 2026-01.xlsx"

Input CSVs:

--trial-balance (required)
    account_id, account_name, account_type, gl_balance, owner
      account_type = asset | liability | equity
      Only balance sheet accounts belong in this file. Bring the full chart,
      including zero-balance accounts — a zero-balance account with prior
      activity is still a completeness question.

--schedules (required)
    account_id, item_date, description, amount, reference
      One row per line of the supporting schedule or subledger detail for
      that account (AP subledger detail, fixed asset register, prepaid
      schedule, accrual detail, etc). schedule_total per account is the sum
      of amount. An account present in the trial balance with no rows here
      is reported as NO SCHEDULE, not silently skipped.

--reconciling-items (optional, produced after reviewing the first pass)
    account_id, description, amount, classification, je_required,
    expected_resolution, owner, first_appeared_date
      classification = timing_gl_ahead | timing_subledger_ahead |
                        schedule_error | gl_error | unrecorded_activity |
                        unidentified
      amount is signed so that gl_balance - schedule_total - sum(amount for
      that account) nets to zero once every real difference is explained.
      Leave this out on the first run: the script will report the raw,
      unexplained difference per account so you know what needs
      classifying. Re-run with this file to get the final workpaper.

--prior-pack (optional)
    account_id, description, amount, classification, first_appeared_date,
    status
      Last period's open items. Used to (a) age items that are still open
      by their true first-appeared date rather than today, and (b) flag an
      item that has appeared, unresolved, in three or more consecutive
      packs as RECURRING — the classic sign of a systemic posting error
      rolling forward unexamined rather than genuine timing.

--signoff (optional)
    account_id, preparer, preparer_date, reviewer, reviewer_date

Money is Decimal throughout. Nothing is rounded. The script never nets an
unexplained difference to zero on your behalf — see "The one rule" in
SKILL.md.
"""

from __future__ import annotations

import argparse
import csv
import sys
from collections import defaultdict
from datetime import date, datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path

try:
    from openpyxl import Workbook
    from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
    from openpyxl.utils import get_column_letter
except ImportError:
    sys.exit("openpyxl is required.  pip install openpyxl")

ZERO = Decimal("0.00")
MONEY = "#,##0.00;[Red](#,##0.00)"
DATEF = "yyyy-mm-dd"

HDR_FILL = PatternFill("solid", fgColor="1F3864")
HDR_FONT = Font(bold=True, color="FFFFFF")
SUB_FILL = PatternFill("solid", fgColor="D9E2F3")
OK_FONT = Font(bold=True, color="006100")
BAD_FONT = Font(bold=True, color="9C0006")
BAD_FILL = PatternFill("solid", fgColor="FFC7CE")
WARN_FILL = PatternFill("solid", fgColor="FFEB9C")
TOP = Border(top=Side(style="thin"))
DBL = Border(top=Side(style="thin"), bottom=Side(style="double"))

ACCOUNT_TYPES = {"asset", "liability", "equity"}

AGE_BUCKETS = [
    ("0-30", 0, 30),
    ("31-60", 31, 60),
    ("61-90", 61, 90),
    ("91-180", 91, 180),
    ("180+", 181, None),
]

RECURRING_THRESHOLD = 3  # consecutive packs, including this one


# ------------------------------------------------------------------ parsing

def dec(raw) -> Decimal:
    if raw is None:
        return ZERO
    s = str(raw).strip()
    if s in ("", "-", "--", "n/a", "N/A", "None"):
        return ZERO
    neg = s.startswith("(") and s.endswith(")")
    s = s.strip("()").replace(",", "").replace("$", "").strip()
    if s.endswith("-"):
        neg, s = True, s[:-1]
    try:
        v = Decimal(s)
    except InvalidOperation:
        raise ValueError(f"Unparseable amount: {raw!r}")
    return -v if neg else v


def pdate(raw, default: date | None = None) -> date | None:
    s = str(raw or "").strip()
    if not s:
        return default
    for fmt in ("%Y-%m-%d", "%m/%d/%Y", "%m/%d/%y"):
        try:
            return datetime.strptime(s, fmt).date()
        except ValueError:
            continue
    raise ValueError(f"Unrecognized date {raw!r}. Prefer ISO YYYY-MM-DD.")


def read_csv(path: Path | None) -> list[dict]:
    if path is None:
        return []
    if not path.exists():
        sys.exit(f"File not found: {path}")
    with path.open(newline="", encoding="utf-8-sig") as fh:
        return list(csv.DictReader(fh))


# ------------------------------------------------------------------ model

class Account:
    def __init__(self, row: dict, order: int):
        self.account_id = str(row.get("account_id", "")).strip()
        if not self.account_id:
            raise ValueError(f"trial-balance row {order}: account_id is required")
        self.account_name = str(row.get("account_name", "")).strip() or self.account_id
        self.account_type = str(row.get("account_type", "")).strip().lower()
        if self.account_type not in ACCOUNT_TYPES:
            raise ValueError(
                f"trial-balance row {order} ({self.account_id}): account_type must be "
                f"asset, liability, or equity, got {row.get('account_type')!r}"
            )
        self.gl_balance = dec(row.get("gl_balance"))
        self.owner = str(row.get("owner", "")).strip()
        self.order = order

        self.schedule_items: list[dict] = []
        self.reconciling_items: list[dict] = []
        self.prior_items: list[dict] = []
        self.signoff: dict | None = None

    @property
    def schedule_total(self) -> Decimal:
        return sum((it["amount"] for it in self.schedule_items), ZERO)

    @property
    def has_schedule(self) -> bool:
        return bool(self.schedule_items)

    @property
    def raw_difference(self) -> Decimal:
        """GL balance less what the supporting schedule says it should be."""
        return self.gl_balance - self.schedule_total

    @property
    def explained(self) -> Decimal:
        return sum((it["amount"] for it in self.reconciling_items), ZERO)

    @property
    def unexplained(self) -> Decimal:
        return self.raw_difference - self.explained

    @property
    def status(self) -> str:
        if not self.has_schedule and self.gl_balance != ZERO:
            return "NO SCHEDULE"
        if self.raw_difference == ZERO:
            return "TIED"
        if self.unexplained == ZERO:
            return "EXPLAINED"
        return "INCOMPLETE"

    @property
    def ready_to_sign(self) -> bool:
        if self.status not in ("TIED", "EXPLAINED"):
            return False
        if not self.signoff:
            return False
        return bool(self.signoff.get("preparer") and self.signoff.get("reviewer")
                    and self.signoff.get("reviewer_date"))


def load_accounts(tb_path: Path) -> dict[str, Account]:
    rows = read_csv(tb_path)
    if not rows:
        sys.exit(f"No rows in {tb_path}")
    accounts: dict[str, Account] = {}
    for i, row in enumerate(rows, start=1):
        acct = Account(row, i)
        if acct.account_id in accounts:
            raise ValueError(f"trial-balance row {i}: duplicate account_id {acct.account_id}")
        accounts[acct.account_id] = acct
    return accounts


def attach_schedules(accounts: dict[str, Account], path: Path | None) -> list[str]:
    warnings = []
    for i, row in enumerate(read_csv(path), start=1):
        account_id = str(row.get("account_id", "")).strip()
        acct = accounts.get(account_id)
        if acct is None:
            warnings.append(f"schedules row {i}: account_id {account_id!r} is not in the trial balance, skipped")
            continue
        acct.schedule_items.append({
            "item_date": pdate(row.get("item_date")),
            "description": str(row.get("description", "")).strip(),
            "amount": dec(row.get("amount")),
            "reference": str(row.get("reference", "")).strip(),
        })
    return warnings


def attach_reconciling_items(accounts: dict[str, Account], path: Path | None) -> list[str]:
    warnings = []
    for i, row in enumerate(read_csv(path), start=1):
        account_id = str(row.get("account_id", "")).strip()
        acct = accounts.get(account_id)
        if acct is None:
            warnings.append(f"reconciling-items row {i}: account_id {account_id!r} is not in the trial balance, skipped")
            continue
        acct.reconciling_items.append({
            "description": str(row.get("description", "")).strip(),
            "amount": dec(row.get("amount")),
            "classification": str(row.get("classification", "")).strip() or "unidentified",
            "je_required": str(row.get("je_required", "")).strip().lower() in ("y", "yes", "true", "1"),
            "expected_resolution": str(row.get("expected_resolution", "")).strip(),
            "owner": str(row.get("owner", "")).strip(),
            "first_appeared_date": pdate(row.get("first_appeared_date")),
        })
    return warnings


def attach_prior_pack(accounts: dict[str, Account], path: Path | None) -> list[str]:
    warnings = []
    for i, row in enumerate(read_csv(path), start=1):
        account_id = str(row.get("account_id", "")).strip()
        acct = accounts.get(account_id)
        if acct is None:
            warnings.append(f"prior-pack row {i}: account_id {account_id!r} is not in the trial balance, skipped")
            continue
        acct.prior_items.append({
            "description": str(row.get("description", "")).strip(),
            "amount": dec(row.get("amount")),
            "classification": str(row.get("classification", "")).strip(),
            "first_appeared_date": pdate(row.get("first_appeared_date")),
            "status": str(row.get("status", "")).strip().lower() or "open",
            "streak": int(row.get("streak", 1) or 1),
        })
    return warnings


def attach_signoff(accounts: dict[str, Account], path: Path | None) -> list[str]:
    warnings = []
    for i, row in enumerate(read_csv(path), start=1):
        account_id = str(row.get("account_id", "")).strip()
        acct = accounts.get(account_id)
        if acct is None:
            warnings.append(f"signoff row {i}: account_id {account_id!r} is not in the trial balance, skipped")
            continue
        acct.signoff = {
            "preparer": str(row.get("preparer", "")).strip(),
            "preparer_date": pdate(row.get("preparer_date")),
            "reviewer": str(row.get("reviewer", "")).strip(),
            "reviewer_date": pdate(row.get("reviewer_date")),
        }
    return warnings


def match_prior(acct: Account, item: dict) -> dict | None:
    for prior in acct.prior_items:
        if (prior["status"] == "open"
                and prior["description"] == item["description"]
                and prior["amount"] == item["amount"]):
            return prior
    return None


def enrich_aging(accounts: dict[str, Account], today: date) -> None:
    """Resolves each reconciling item's true first-appeared date and streak
    against the prior pack, so an item outstanding for six months doesn't
    silently reset to age zero because it was re-typed this period."""
    for acct in accounts.values():
        for item in acct.reconciling_items:
            prior = match_prior(acct, item)
            if prior:
                item["first_appeared_date"] = item["first_appeared_date"] or prior["first_appeared_date"]
                item["streak"] = prior["streak"] + 1
            else:
                item.setdefault("streak", 1)
                item["first_appeared_date"] = item["first_appeared_date"] or today
            item["age_days"] = (today - item["first_appeared_date"]).days
            item["recurring"] = item["streak"] >= RECURRING_THRESHOLD


def age_bucket(days: int) -> str:
    for label, lo, hi in AGE_BUCKETS:
        if hi is None:
            if days >= lo:
                return label
        elif lo <= days <= hi:
            return label
    return AGE_BUCKETS[-1][0]


# ------------------------------------------------------------------ excel helpers

def style_header(ws, ncols: int, row: int = 1) -> None:
    for c in range(1, ncols + 1):
        cell = ws.cell(row=row, column=c)
        cell.fill, cell.font = HDR_FILL, HDR_FONT
        cell.alignment = Alignment(vertical="center", wrap_text=True)
    ws.row_dimensions[row].height = 28


def autosize(ws, widths: dict[int, int]) -> None:
    for col, w in widths.items():
        ws.column_dimensions[get_column_letter(col)].width = w


def status_font(status: str) -> Font:
    return {"TIED": OK_FONT, "EXPLAINED": OK_FONT}.get(status, BAD_FONT)


# ------------------------------------------------------------------ tabs

def write_summary(wb, accounts: list[Account], period: str) -> None:
    ws = wb.active
    ws.title = "Completeness Summary"
    ws.cell(row=1, column=1, value=f"BALANCE SHEET RECONCILIATION PACK — {period}").font = Font(bold=True, size=14)

    total = len(accounts)
    covered = sum(1 for a in accounts if a.has_schedule)
    tied = sum(1 for a in accounts if a.status == "TIED")
    explained = sum(1 for a in accounts if a.status == "EXPLAINED")
    incomplete = sum(1 for a in accounts if a.status == "INCOMPLETE")
    no_schedule = sum(1 for a in accounts if a.status == "NO SCHEDULE")
    signed = sum(1 for a in accounts if a.ready_to_sign)
    total_unexplained = sum((a.unexplained for a in accounts if a.status == "INCOMPLETE"), ZERO)

    r = 3
    stats = [
        ("Accounts in chart", total),
        ("Covered by a supporting schedule", f"{covered} / {total}"),
        ("Tied (GL = schedule exactly)", tied),
        ("Explained (difference fully classified)", explained),
        ("Incomplete (unexplained difference remains)", incomplete),
        ("No schedule provided", no_schedule),
        ("Signed off, preparer and reviewer", f"{signed} / {total}"),
        ("Total unexplained difference across the chart", float(total_unexplained)),
    ]
    for label, value in stats:
        ws.cell(row=r, column=1, value=label).font = Font(bold=True)
        cell = ws.cell(row=r, column=2, value=value)
        if label.startswith("Total unexplained"):
            cell.number_format = MONEY
            cell.font = OK_FONT if total_unexplained == ZERO else BAD_FONT
            if total_unexplained != ZERO:
                cell.fill = BAD_FILL
        r += 2

    r += 1
    ws.cell(row=r, column=1, value="Every account, sorted by absolute GL balance — triage the biggest first").font = Font(bold=True, size=12)
    r += 1
    headers = ["Account", "Name", "Type", "GL Balance", "Schedule Total", "Difference", "Status", "Signed Off"]
    for col, h in enumerate(headers, start=1):
        hc = ws.cell(row=r, column=col, value=h)
        hc.fill, hc.font = HDR_FILL, HDR_FONT
    r += 1
    header_row = r - 1

    for acct in sorted(accounts, key=lambda a: abs(a.gl_balance), reverse=True):
        ws.cell(row=r, column=1, value=acct.account_id)
        ws.cell(row=r, column=2, value=acct.account_name)
        ws.cell(row=r, column=3, value=acct.account_type)
        for col, val in ((4, acct.gl_balance), (5, acct.schedule_total), (6, acct.raw_difference)):
            c = ws.cell(row=r, column=col, value=float(val))
            c.number_format = MONEY
        sc = ws.cell(row=r, column=7, value=acct.status)
        sc.font = status_font(acct.status)
        if acct.status in ("INCOMPLETE", "NO SCHEDULE"):
            sc.fill = BAD_FILL
        ws.cell(row=r, column=8, value="Yes" if acct.ready_to_sign else "No")
        r += 1

    last = r - 1
    ws.freeze_panes = f"A{header_row + 1}"
    ws.auto_filter.ref = f"A{header_row}:H{last}"
    autosize(ws, {1: 14, 2: 32, 3: 10, 4: 16, 5: 16, 6: 14, 7: 13, 8: 11})


def write_reconciliations(wb, accounts: list[Account]) -> None:
    ws = wb.create_sheet("Reconciliations")
    ws.column_dimensions["A"].width = 44
    ws.column_dimensions["B"].width = 18
    ws.column_dimensions["C"].width = 44
    ws.column_dimensions["D"].width = 18
    r = 1

    for acct in sorted(accounts, key=lambda a: a.order):
        ws.cell(row=r, column=1, value=f"{acct.account_id} — {acct.account_name} ({acct.account_type})").font = Font(bold=True, size=13)
        r += 1
        rows = [
            ("Balance per general ledger", acct.gl_balance),
            ("Balance per supporting schedule", acct.schedule_total),
            ("Difference", acct.raw_difference),
        ]
        for label, val in rows:
            ws.cell(row=r, column=1, value=label)
            c = ws.cell(row=r, column=2, value=float(val))
            c.number_format = MONEY
            if label == "Difference":
                c.border = TOP
                ws.cell(row=r, column=1).border = TOP
            r += 1

        if acct.reconciling_items:
            ws.cell(row=r, column=1, value="Reconciling items:").font = Font(italic=True)
            r += 1
            for item in acct.reconciling_items:
                ws.cell(row=r, column=1, value=f"  {item['description']} ({item['classification']})")
                c = ws.cell(row=r, column=2, value=float(item["amount"]))
                c.number_format = MONEY
                r += 1

        ws.cell(row=r, column=1, value="UNEXPLAINED DIFFERENCE").font = Font(bold=True)
        uc = ws.cell(row=r, column=2, value=float(acct.unexplained))
        uc.number_format = MONEY
        uc.font = OK_FONT if acct.unexplained == ZERO else BAD_FONT
        uc.border = DBL
        ws.cell(row=r, column=1).border = DBL
        if acct.unexplained != ZERO:
            uc.fill = BAD_FILL
        r += 1

        if not acct.has_schedule and acct.gl_balance != ZERO:
            nc = ws.cell(row=r, column=1, value="NO SUPPORTING SCHEDULE WAS PROVIDED FOR THIS ACCOUNT")
            nc.font = BAD_FONT
            r += 1

        ws.cell(row=r, column=1, value="Status:").font = Font(bold=True)
        sc = ws.cell(row=r, column=2, value=acct.status)
        sc.font = status_font(acct.status)
        r += 1

        prep = acct.signoff.get("preparer") if acct.signoff else ""
        prep_date = acct.signoff.get("preparer_date") if acct.signoff else None
        rev = acct.signoff.get("reviewer") if acct.signoff else ""
        rev_date = acct.signoff.get("reviewer_date") if acct.signoff else None
        ws.cell(row=r, column=1, value="Prepared by:")
        ws.cell(row=r, column=2, value=prep or "(unsigned)")
        ws.cell(row=r, column=3, value="Date:")
        dc = ws.cell(row=r, column=4, value=prep_date)
        if prep_date:
            dc.number_format = DATEF
        r += 1
        ws.cell(row=r, column=1, value="Reviewed by:")
        ws.cell(row=r, column=2, value=rev or "(unsigned)")
        ws.cell(row=r, column=3, value="Date:")
        dc = ws.cell(row=r, column=4, value=rev_date)
        if rev_date:
            dc.number_format = DATEF
        r += 3


def write_open_items(wb, accounts: list[Account]) -> None:
    ws = wb.create_sheet("Open Item List")
    headers = ["Account", "Account Name", "Description", "Amount", "Classification",
               "First Appeared", "Age (days)", "Bucket", "Recurring", "JE Required",
               "Expected Resolution", "Owner"]
    ws.append(headers)
    style_header(ws, len(headers))

    for acct in sorted(accounts, key=lambda a: a.order):
        for item in acct.reconciling_items:
            ws.append([
                acct.account_id, acct.account_name, item["description"], float(item["amount"]),
                item["classification"], item["first_appeared_date"], item["age_days"],
                age_bucket(item["age_days"]), "Yes" if item["recurring"] else "No",
                "Yes" if item["je_required"] else "No", item["expected_resolution"], item["owner"],
            ])

    last = ws.max_row
    for row in ws.iter_rows(min_row=2, max_row=last):
        row[3].number_format = MONEY
        if row[5].value:
            row[5].number_format = DATEF
        if row[4].value == "unidentified":
            for cell in row:
                cell.fill = BAD_FILL
        elif row[8].value == "Yes":
            for cell in row:
                cell.fill = WARN_FILL

    if last == 1:
        ws.cell(row=2, column=1, value="None — every account tied to its schedule with no reconciling items.")

    ws.freeze_panes = "A2"
    if last > 1:
        ws.auto_filter.ref = f"A1:L{last}"
    autosize(ws, {1: 12, 2: 28, 3: 40, 4: 14, 5: 20, 6: 14, 7: 11, 8: 9, 9: 10, 10: 11, 11: 18, 12: 16})


def write_proposed_jes(wb, accounts: list[Account]) -> None:
    ws = wb.create_sheet("Proposed Journal Entries")
    headers = ["Account", "Account Name", "Description", "Debit", "Credit", "Classification", "Owner"]
    ws.append(headers)
    style_header(ws, len(headers))

    for acct in sorted(accounts, key=lambda a: a.order):
        for item in acct.reconciling_items:
            if not item["je_required"]:
                continue
            debit = float(item["amount"]) if item["amount"] > ZERO else None
            credit = float(-item["amount"]) if item["amount"] < ZERO else None
            ws.append([acct.account_id, acct.account_name, item["description"], debit, credit,
                       item["classification"], item["owner"]])

    last = ws.max_row
    for row in ws.iter_rows(min_row=2, max_row=last):
        row[3].number_format = MONEY
        row[4].number_format = MONEY
    if last == 1:
        ws.cell(row=2, column=1, value="None flagged as requiring a journal entry this period.")
    ws.freeze_panes = "A2"
    autosize(ws, {1: 12, 2: 28, 3: 40, 4: 14, 5: 14, 6: 20, 7: 16})


def write_aging(wb, accounts: list[Account]) -> None:
    ws = wb.create_sheet("Aging")
    headers = ["Account", "Account Name"] + [b[0] for b in AGE_BUCKETS] + ["Total", "Stale (180+)", "Recurring items"]
    ws.append(headers)
    style_header(ws, len(headers))

    for acct in sorted(accounts, key=lambda a: a.order):
        if not acct.reconciling_items:
            continue
        buckets = defaultdict(lambda: ZERO)
        recurring = 0
        for item in acct.reconciling_items:
            buckets[age_bucket(item["age_days"])] += item["amount"]
            if item["recurring"]:
                recurring += 1
        total = sum(buckets.values(), ZERO)
        row = [acct.account_id, acct.account_name] + [float(buckets[b[0]]) for b in AGE_BUCKETS] + [float(total), None, recurring]
        ws.append(row)

    last = ws.max_row
    stale_col = 2 + len(AGE_BUCKETS) + 2  # position of "Stale (180+)"
    for row_idx in range(2, last + 1):
        for col in range(3, 3 + len(AGE_BUCKETS) + 1):
            ws.cell(row=row_idx, column=col).number_format = MONEY
        stale_val = ws.cell(row=row_idx, column=2 + len(AGE_BUCKETS) + 1).value
        ws.cell(row=row_idx, column=stale_col, value="Yes" if stale_val else "").font = BAD_FONT
    if last == 1:
        ws.cell(row=2, column=1, value="No open reconciling items to age.")
    ws.freeze_panes = "A2"
    autosize(ws, {1: 12, 2: 28})


def write_exceptions(wb, accounts: list[Account]) -> None:
    ws = wb.create_sheet("Exceptions & Memo")
    ws.column_dimensions["A"].width = 100
    r = 1
    ws.cell(row=r, column=1, value="EXCEPTIONS AND CONTROL OBSERVATIONS").font = Font(bold=True, size=13)
    r += 2

    no_schedule = [a for a in accounts if a.status == "NO SCHEDULE"]
    incomplete = [a for a in accounts if a.status == "INCOMPLETE"]
    unidentified = [(a, it) for a in accounts for it in a.reconciling_items if it["classification"] == "unidentified"]
    recurring = [(a, it) for a in accounts for it in a.reconciling_items if it["recurring"]]

    def section(title: str, lines: list[str], empty: str) -> None:
        nonlocal r
        ws.cell(row=r, column=1, value=title).font = Font(bold=True, size=12)
        r += 1
        if not lines:
            ws.cell(row=r, column=1, value=empty)
            r += 2
            return
        for line in lines:
            cell = ws.cell(row=r, column=1, value=f"- {line}")
            cell.font = BAD_FONT
            r += 1
        r += 1

    section(
        "Accounts with a balance and no supporting schedule",
        [f"{a.account_id} {a.account_name}: GL balance {a.gl_balance:,.2f} — auditors ask for this first" for a in no_schedule],
        "None. Every non-zero account has a supporting schedule.",
    )
    section(
        "Accounts with an unexplained difference (incomplete — do not sign)",
        [f"{a.account_id} {a.account_name}: unexplained {a.unexplained:,.2f}" for a in incomplete],
        "None. Every difference is fully classified.",
    )
    section(
        "Unidentified reconciling items (never bury — escalate regardless of size)",
        [f"{a.account_id} {a.account_name}: {it['description']} ({it['amount']:,.2f})" for a, it in unidentified],
        "None.",
    )
    section(
        "Recurring identical items (same item open 3+ consecutive packs — systemic, not timing)",
        [f"{a.account_id} {a.account_name}: {it['description']} ({it['amount']:,.2f}), open {it['streak']} periods" for a, it in recurring],
        "None.",
    )

    ws.cell(row=r, column=1, value=(
        "Note on completeness: a reconciliation pack that covers most of the chart is not a "
        "complete pack. The accounts most often left out — suspense, clearing, intercompany "
        "settlement, small equity accounts — are also the ones an auditor samples first, "
        "precisely because they are usually skipped."
    )).font = Font(italic=True)


# ------------------------------------------------------------------ main

def parse_args():
    ap = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    ap.add_argument("--trial-balance", required=True)
    ap.add_argument("--schedules", required=True)
    ap.add_argument("--reconciling-items")
    ap.add_argument("--prior-pack")
    ap.add_argument("--signoff")
    ap.add_argument("--period", default="")
    ap.add_argument("--out", required=True)
    ap.add_argument("--as-of", help="ISO date to treat as 'today' for aging; defaults to the real today")
    return ap.parse_args()


def main() -> int:
    args = parse_args()
    today = pdate(args.as_of, date.today()) if args.as_of else date.today()

    accounts = load_accounts(Path(args.trial_balance))
    warnings = []
    warnings += attach_schedules(accounts, Path(args.schedules))
    warnings += attach_reconciling_items(accounts, Path(args.reconciling_items) if args.reconciling_items else None)
    warnings += attach_prior_pack(accounts, Path(args.prior_pack) if args.prior_pack else None)
    warnings += attach_signoff(accounts, Path(args.signoff) if args.signoff else None)

    for w in warnings:
        print(f"warn: {w}")

    enrich_aging(accounts, today)

    account_list = list(accounts.values())
    incomplete = [a for a in account_list if a.status == "INCOMPLETE"]
    no_schedule = [a for a in account_list if a.status == "NO SCHEDULE"]

    print("=" * 68)
    print("BALANCE SHEET RECONCILIATION PACK")
    print("=" * 68)
    print(f"Accounts in chart      : {len(account_list)}")
    print(f"Covered by a schedule  : {sum(1 for a in account_list if a.has_schedule)}")
    print(f"Tied                   : {sum(1 for a in account_list if a.status == 'TIED')}")
    print(f"Explained              : {sum(1 for a in account_list if a.status == 'EXPLAINED')}")
    print(f"Incomplete             : {len(incomplete)}")
    print(f"No schedule            : {len(no_schedule)}")
    if incomplete:
        print("\n*** INCOMPLETE — unexplained difference remains ***")
        for a in incomplete:
            print(f"  {a.account_id:<14} {a.account_name:<32} unexplained {a.unexplained:>14,.2f}")
    if no_schedule:
        print("\n*** NO SCHEDULE PROVIDED ***")
        for a in no_schedule:
            print(f"  {a.account_id:<14} {a.account_name:<32} GL balance  {a.gl_balance:>14,.2f}")

    wb = Workbook()
    write_summary(wb, account_list, args.period or today.isoformat()[:7])
    write_reconciliations(wb, account_list)
    write_open_items(wb, account_list)
    write_proposed_jes(wb, account_list)
    write_aging(wb, account_list)
    write_exceptions(wb, account_list)
    wb.active = 0

    out = Path(args.out)
    if incomplete or no_schedule:
        out = out.with_name(out.stem + " [INCOMPLETE]" + out.suffix)
    out.parent.mkdir(parents=True, exist_ok=True)
    wb.save(str(out))

    print(f"\nWorkbook: {out}")
    return 1 if (incomplete or no_schedule) else 0


if __name__ == "__main__":
    sys.exit(main())