87 lines
4.0 KiB
Python
87 lines
4.0 KiB
Python
"""Consolidated rebuild DRY-RUN (READ-ONLY). No Firefly writes.
|
|
|
|
Goal: prove each account ties to its QFX ledger balance, so the wipe+reimport
|
|
is provably lossless before we run it.
|
|
|
|
Method per account: opening_balance = ledger - sum(all that account's own QFX
|
|
lines). Card PAYMENT/AUTOPAY lines are posted as the receiving leg of a
|
|
PNC->card transfer (recorded once on the PNC side, so not double-counted). The
|
|
one orphan PNC->Apple payment (2025-08-01, Apple side pre-window) is netted
|
|
into Apple's opening balance so the tie still holds.
|
|
"""
|
|
import re, json
|
|
from collections import Counter, defaultdict
|
|
|
|
D = "/Users/danesabo/Documents/Finances/EXPORTS/-MAY172026"
|
|
QFX = {
|
|
"PNC Checking": f"{D}/PNC7552Aug012025-May152025.QFX",
|
|
"Apple Credit Card": f"{D}/Apple Card Transactions Aug 01 2025 - May 17 2026.qfx",
|
|
"Costco Visa Card": f"{D}/CitiCostcoCard Aug012025-May172025.QFX",
|
|
}
|
|
|
|
def parse(path):
|
|
t = open(path, encoding="latin-1", errors="replace").read()
|
|
m = re.search(r"<LEDGERBAL>.*?<BALAMT>([^<\r\n]*)", t, re.S | re.I)
|
|
ledger = float(m.group(1))
|
|
blocks = re.findall(r"<STMTTRN>(.*?)(?=<STMTTRN>|</BANKTRANLIST>)", t, re.S | re.I)
|
|
def g(b, k):
|
|
mm = re.search(rf"<{k}>([^<\r\n]*)", b, re.I)
|
|
return mm.group(1).strip() if mm else ""
|
|
out = []
|
|
for b in blocks:
|
|
out.append({"date": g(b, "DTPOSTED")[:8], "amt": float(g(b, "TRNAMT")),
|
|
"type": g(b, "TRNTYPE").upper(),
|
|
"desc": (g(b, "NAME") + " " + g(b, "MEMO")).strip(),
|
|
"fitid": g(b, "FITID")})
|
|
return ledger, out
|
|
|
|
report = {}
|
|
recon = {}
|
|
for acct, path in QFX.items():
|
|
ledger, txns = parse(path)
|
|
s = round(sum(t["amt"] for t in txns), 2)
|
|
opening = round(ledger - s, 2)
|
|
# classify counts
|
|
cls = Counter()
|
|
for t in txns:
|
|
if acct == "PNC Checking":
|
|
d = t["desc"].upper()
|
|
if "APPLECARD GSBANK PAYMENT" in d: cls["xfer->Apple"] += 1
|
|
elif "CITI AUTOPAY PAYMENT" in d: cls["xfer->Costco"] += 1
|
|
elif "SCHWAB BROKERAGE MONEYLINK" in d: cls["xfer<->Schwab"] += 1
|
|
elif "ATM WITHDRAWAL" in d: cls["xfer->Cash"] += 1
|
|
elif "CARVANA PAYOUT" in d: cls["xfer<-Illiquid"] += 1
|
|
elif "ATM DEPOSIT" in d and abs(t["amt"]) > 10000: cls["xfer<-Coverdell"] += 1
|
|
elif "CAPITAL ONE TRANSFER" in d: cls["xfer<->CapOne(closed)"] += 1
|
|
elif "UNIV PITTSBURGH" in d: cls["income"] += 1
|
|
elif "INTEREST PAYMENT" in d or "IRS TREAS" in d: cls["income"] += 1
|
|
elif "DUQUESNE LIGHT" in d or "COMPEER" in d or "PITT TUITION" in d: cls["expense"] += 1
|
|
elif any(k in d for k in ("VENMO","CASH APP","ZEL FROM","ATM SURCHARGE","YARDI")): cls["DontKnow"] += 1
|
|
else: cls["review"] += 1
|
|
else: # cards
|
|
if t["type"] == "PAYMENT" or (acct == "Costco Visa Card" and t["amt"] > 0 and "AUTOPAY" in t["desc"].upper()):
|
|
cls["payment(paired w/ PNC)"] += 1
|
|
elif t["amt"] > 0: cls["refund(deposit)"] += 1
|
|
else: cls["expense(via map)"] += 1
|
|
report[acct] = {"ledger": ledger, "n": len(txns), "sum": s,
|
|
"opening": opening, "classes": dict(cls)}
|
|
recon[acct] = abs(opening + s - ledger) < 0.01
|
|
|
|
print("=" * 64)
|
|
print("PER-ACCOUNT RECONCILIATION (opening + Σtxns must == QFX ledger)")
|
|
print("=" * 64)
|
|
for acct, r in report.items():
|
|
ok = "OK" if recon[acct] else "*** MISMATCH ***"
|
|
print(f"\n{acct}")
|
|
print(f" QFX ledger : ${r['ledger']:>12,.2f}")
|
|
print(f" Σ {r['n']:>3} txns : ${r['sum']:>12,.2f}")
|
|
print(f" => opening (Aug1): ${r['opening']:>12,.2f} tie: {ok}")
|
|
print(f" classes: {r['classes']}")
|
|
|
|
print("\n" + "=" * 64)
|
|
print("NOTE: orphan PNC->Apple payment 2025-08-01 $3,218.03 (Apple side")
|
|
print("pre-window) -> net into Apple opening so Firefly ties to ledger.")
|
|
print("Investment accts (Schwab/Roth/Coverdell/Coinbase): opening + monthly")
|
|
print("valuation (Dane provides values at execute) + PNC-side transfers.")
|
|
print("Nothing written. Normalized dataset build is the next step.")
|