"""Fix extracted Excel files: convert trailing CR balances into negatives. Scans all .xlsx files in `data/output` and writes a `.fixed.xlsx` copy with any `balance` or `balance_after` columns normalized. Usage: python scripts/fix_extracted_balances.py """ from pathlib import Path import re from openpyxl import load_workbook ROOT = Path(__file__).resolve().parent.parent OUT_DIR = ROOT / "data" / "output" CR_RE = re.compile(r"(?i)\s*CR\s*$") def fix_workbook(path: Path) -> Path: wb = load_workbook(path) changed = False for name in wb.sheetnames: ws = wb[name] # find header row (assume first row) headers = [str(c.value).strip() if c.value is not None else "" for c in ws[1]] bal_cols = [i for i, h in enumerate(headers) if h and "balance" in h.lower()] if not bal_cols: continue for row in ws.iter_rows(min_row=2, max_col=ws.max_column): for ci in bal_cols: cell = row[ci] v = cell.value if v is None: continue s = str(v).strip() if CR_RE.search(s): cleaned = CR_RE.sub("", s).strip() cleaned = cleaned.replace(",", "") if cleaned and not cleaned.startswith("-"): cell.value = "-" + cleaned else: cell.value = cleaned changed = True if changed: out = path.with_name(path.stem + ".fixed" + path.suffix) wb.save(out) return out return path def main(): if not OUT_DIR.exists(): print("No data/output directory found; nothing to do.") return files = list(OUT_DIR.glob("*.xlsx")) if not files: print("No xlsx files in data/output") return for f in files: out = fix_workbook(f) if out != f: print(f"Fixed: {f.name} -> {out.name}") else: print(f"No changes: {f.name}") if __name__ == "__main__": main()