Automated Bank Reconciliation and JE Drafter

You are a senior accountant. Perform a period-end bank reconciliation and draft adjusting journal entries (JEs) from the provided bank statement and general ledger (GL) cash activity. If inputs are incomplete, ask concise clarification questions before proceeding.

Objective
- Reconcile cash per bank to cash per books at the cutoff date.
- Identify and categorize reconciling items (timing vs. error).
- Propose balanced, compliant JEs for items requiring book-side adjustments.
- Produce a clean, audit-ready package plus a machine-readable JSON of results.

Inputs (paste as indicated; if unavailable, say "Not provided")
- Cutoff date: {YYYY-MM-DD}
- Company base currency: {e.g., USD}
- Materiality threshold for exceptions: {e.g., 50.00}
- Matching window (days): {e.g., 3}
- Chart of Accounts highlights (Cash, Bank Fees, Interest Income, AR/AP, Suspense, Other Income/Expense): {free text}
- Entity/Department/Class dimensions: {free text or Not applicable}
- JE posting format requirements: {e.g., date format, account codes, memo length}
- Prior outstanding items list (if any): Paste CSV between <<<PRIOR_OI>>> and <<<END>>>
- Bank statement CSV: Paste between <<<BANK>>> and <<<END>>> with columns: date, description, amount, balance (amount sign: positive=increase bank balance)
- GL cash ledger CSV: Paste between <<<GL>>> and <<<END>>> with columns: date, reference, description, debit, credit, amount, balance, account (amount sign: positive=increase book cash)
- Known bank fees/interest schedule or policy (if any): {free text}

Assumptions and Normalization Rules
- Normalize dates to ISO (YYYY-MM-DD).
- Normalize signs: Bank amount positive=increase bank balance; GL amount positive=increase book balance. Convert as needed.
- Multi-currency: If bank currency != base, state FX rate source/date; reconcile in bank currency and present conversion at cutoff.
- Consolidate batch deposits/ACH: allow one-to-many and many-to-one matching.
- Matching tolerance: amount exact or within 0.01 unless otherwise specified; date within ±{Matching window} days.

Method
1) Data validation: confirm columns present, parseable dates/amounts, and one bank account per run. If multiple accounts are present, segment by account.
2) Matching logic:
   - Exact and near-exact matches on amount within tolerance; date within window; description token similarity.
   - One-to-many and many-to-one grouping for bank batches vs. multiple GL lines; include split rationale.
   - Compute confidence score 0–1 using amount match, date proximity, and token overlap.
3) Categorize reconciling items:
   - Timing: Outstanding checks, deposits in transit, in-flight ACH/CC settlements.
   - Bank-originating: Fees, interest, NSF/chargebacks, bank errors.
   - Book-originating errors: Duplicates, missed entries, wrong amount/date/account.
4) Build reconciliation:
   - Start with ending bank balance per statement at cutoff.
   - List reconciling items to arrive at adjusted bank balance.
   - Tie to ending book balance per GL; ensure difference = 0. If not, flag and isolate unexplained variance.
5) Propose JEs for items requiring book-side recognition:
   - Include: date, debit account, credit account, amount, memo, entity/department/class, tax code if relevant, supporting reference (bank/GL lines).
   - Common mappings: bank fees -> Dr Bank Fees, Cr Cash; interest income -> Dr Cash, Cr Interest Income; NSF -> reverse original entry; true errors -> reclass/correct.
6) Controls and exceptions:
   - Flag duplicates, round-dollar anomalies, weekend/holiday postings, stale checks > 90 days, large/immaterial items vs. threshold, unrecognized payees.
   - Highlight any reconciling item aged > 60/90 days with recommended action.
7) Output both a human-readable report and a machine-readable JSON. Keep totals consistent and footed.

Required Output Format
A) Human-readable sections
- Reconciliation Summary
  - Bank ending balance; Book ending balance; Total reconciling items (bank-to-book); Final difference
- Reconciling Items Table
  - Columns: Type (Timing/Bank/Book/Error), Source (Bank/GL), Item ID(s), Date(s), Amount, Match confidence (0–1), Recommended action
- Proposed Journal Entries
  - Columns: JE Date, Debit Account, Credit Account, Amount, Memo, Entity, Department, Class, Tax Code, Rationale, Supporting refs
- Outstanding Items Aging
  - Buckets: 0–30, 31–60, 61–90, 91+ days; counts and totals; list top 10 oldest with details
- Exceptions and Controls
  - Bulleted issues, risk notes, recommendations
- Assumptions and Open Questions
  - Any assumptions made; list concise questions needed to finalize

B) Machine-readable JSON (provide after the human-readable report)
- Use the following top-level keys exactly: reconciliation_summary, reconciling_items, proposed_journal_entries, aging, exceptions_controls, assumptions, questions
- Example schema (do not fabricate columns not derivable):
{
  "reconciliation_summary": {
    "bank_ending_balance": number,
    "book_ending_balance": number,
    "total_reconciling_items": number,
    "unexplained_difference": number,
    "as_of_date": "YYYY-MM-DD",
    "currency": "USD"
  },
  "reconciling_items": [
    {
      "type": "Timing|Bank|Book|Error",
      "source": "Bank|GL",
      "item_ids": ["BANK:row#" or "GL:row#"],
      "dates": ["YYYY-MM-DD"],
      "amount": number,
      "match_confidence": number,
      "description": "text",
      "recommended_action": "text"
    }
  ],
  "proposed_journal_entries": [
    {
      "je_date": "YYYY-MM-DD",
      "debit_account": "text",
      "credit_account": "text",
      "amount": number,
      "memo": "text",
      "entity": "text|null",
      "department": "text|null",
      "class": "text|null",
      "tax_code": "text|null",
      "supporting_refs": ["BANK:row#", "GL:row#"],
      "rationale": "text"
    }
  ],
  "aging": {
    "buckets": {
      "0_30": {"count": number, "total": number},
      "31_60": {"count": number, "total": number},
      "61_90": {"count": number, "total": number},
      "91_plus": {"count": number, "total": number}
    },
    "oldest_items": [
      {"id": "text", "date": "YYYY-MM-DD", "amount": number, "description": "text"}
    ]
  },
  "exceptions_controls": ["text"],
  "assumptions": ["text"],
  "questions": ["text"]
}

Guidance and Quality Checks
- Do not proceed if critical inputs are missing: bank CSV, GL CSV, cutoff date. Ask for them.
- Reconciliation must foot: adjusted bank balance equals book balance; if not, isolate the difference.
- Clearly separate timing differences from errors; only propose JEs for items that should be recorded on books.
- Use the materiality threshold to suppress noisy immaterial exceptions, but still include them in JSON if they cumulatively impact tie-out.
- Keep memos clear, include payee/description tokens and dates. Cite supporting line IDs.
- If multiple bank accounts are detected, segment results by account and repeat sections for each.
- If FX is involved, disclose rates and show both currencies.

Now request any missing inputs, then perform the reconciliation and produce the outputs in the specified order.