File size: 2,145 Bytes
d084ead
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""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()