Scanlly / statement_parser.py
VsinghiAssociates's picture
Upload folder using huggingface_hub
2d875c8 verified
Raw History Blame Contribute Delete
63.3 kB
import os import re import json import hashlib from datetime import datetime from io import BytesIO from concurrent.futures import ThreadPoolExecutor try: import pymupdf as fitz except ImportError: import fitz import pdfplumber import pandas as pd import requests GROQ_API_KEY = os.getenv("GROQ_API_KEY", "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") GEMINI_API_KEY = os.getenv("GEMINI_API_KEY", "AQ.Ab8RN6KAedFsuHHpJ3aizbHW7ptE0xf8UPdq8cvLTLdUp8ILrQ") OPENROUTER_API_KEY = os.getenv("OPENROUTER_API_KEY", "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") PROVIDERS = [ {"name": "groq_fast", "kind": "openai", "url": "https://api.groq.com/openai/v1/chat/completions", "api_key": GROQ_API_KEY, "model": "llama-3.1-8b-instant"}, {"name": "groq", "kind": "openai", "url": "https://api.groq.com/openai/v1/chat/completions", "api_key": GROQ_API_KEY, "model": "llama-3.3-70b-versatile"}, {"name": "gemini", "kind": "gemini", "url": "https://generativelanguage.googleapis.com/v1beta/models/{model}:generateContent", "api_key": GEMINI_API_KEY, "model": "gemini-2.0-flash"}, {"name": "openrouter", "kind": "openai", "url": "https://openrouter.ai/api/v1/chat/completions", "api_key": OPENROUTER_API_KEY, "model": "openrouter/free"}, ] DATE_FORMATS = [ "%d/%m/%Y", "%d-%m-%Y", "%d.%m.%Y", "%d/%m/%y", "%d-%m-%y", "%d.%m.%y", "%d-%b-%Y", "%d-%b-%y", "%d %b %Y", "%d %b %y", "%d-%B-%Y", "%d %B %Y", "%Y/%m/%d", "%Y-%m-%d", "%Y.%m.%d", "%d%b%Y", "%d%B%Y" ] def to_float(s): if s is None: return None s_clean = str(s).replace(",", "").replace(" ", "").replace("`", "").strip() if not s_clean or s_clean in ("-", "--", "None", "null", "CR", "DR", "Cr", "Dr"): return None try: return float(s_clean) except ValueError: return None def clean_desc(parts): cleaned = [] for p in parts: if not p: continue p_str = str(p).strip() p_str = re.sub(r"^[|\[{(~#_\\/ -]+", "", p_str) p_str = re.sub(r"[|\]})~#_\\/ -]+$", "", p_str) p_str = re.sub(r"\s+", " ", p_str).strip() if p_str and p_str not in ("-", "--", "|", "/"): cleaned.append(p_str) return " ".join(cleaned) def parse_any_date(d): if not d: return None d_clean = str(d).strip().replace(" ", "-").replace("/", "-").replace(".", "-") for fmt in DATE_FORMATS: try: return datetime.strptime(d.strip(), fmt) except ValueError: continue # Try normalized hyphen format for fmt in ["%d-%m-%Y", "%d-%m-%y", "%d-%b-%Y", "%d-%b-%y", "%Y-%m-%d"]: try: return datetime.strptime(d_clean, fmt) except ValueError: continue return None def assign_debit_credit(rows, lines=None): if not rows: return rows for r in rows: if r.get("description"): r["description"] = clean_desc([str(r["description"])]) parsed_dates = [parse_any_date(r.get("date", "")) for r in rows] valid_dates = [(i, d) for i, d in enumerate(parsed_dates) if d is not None] ascending = True if len(valid_dates) >= 2: if valid_dates[0][1] != valid_dates[-1][1]: ascending = valid_dates[0][1] < valid_dates[-1][1] if ascending: chrono_indices = list(range(len(rows))) else: chrono_indices = list(range(len(rows) - 1, -1, -1)) debit_kw = ["/DR/", " DR", "DR ", "WITHDRAWAL", "TO ", "ATM", "UPI", "POS", "DEBIT", "CHARGES", "FEE", "PAY", "IMPS", "CHQ", "CHEQUE", "EMI", "BILL", "CARD", "TAX", "WDR", "PURCHASE", "COMMISSION", "GST", "TRANSFER TO"] credit_kw = ["/CR/", " CR", "CR ", "DEPOSIT", "BY ", "INTEREST", "SALARY", "CREDIT", "REFUND", "REVERSAL", "DIVIDEND", "CASH DEP", "TRANSFER FROM"] opening_balance = None if lines: header_lines = lines[:80] for line in header_lines: om = re.search(r"(?:opening\s*bal(?:ance)?|bal(?:ance)?\s*b/?f|brought\s*forward|previous\s*balance)\s*:?\s*(-?[\d,]+\.\d{2})", line, re.IGNORECASE) if om: opening_balance = to_float(om.group(1)) break oldest_idx = chrono_indices[0] oldest_row = rows[oldest_idx] prev_balance = opening_balance if prev_balance is None: b1 = oldest_row.get("balance") a1 = oldest_row.get("amount") if a1 is None: a1 = oldest_row.get("debit") if oldest_row.get("debit") is not None else oldest_row.get("credit") desc1 = str(oldest_row.get("description", "")).upper() if b1 is not None and a1 is not None: if oldest_row.get("sign") == "debit" or (oldest_row.get("debit") is not None and oldest_row.get("credit") is None): oldest_row["sign"] = "debit" prev_balance = round(b1 + a1, 2) elif oldest_row.get("sign") == "credit" or (oldest_row.get("credit") is not None and oldest_row.get("debit") is None): oldest_row["sign"] = "credit" prev_balance = round(b1 - a1, 2) else: cand_debit = round(b1 + a1, 2) cand_credit = round(b1 - a1, 2) is_deb = any(kw in desc1 for kw in debit_kw) or desc1.startswith("TO ") or "DR" in desc1 is_cred = any(kw in desc1 for kw in credit_kw) or desc1.startswith("BY ") or "CR" in desc1 if is_deb and not is_cred: oldest_row["sign"] = "debit" prev_balance = cand_debit elif is_cred and not is_deb: oldest_row["sign"] = "credit" prev_balance = cand_credit else: oldest_row["sign"] = "debit" prev_balance = cand_debit for idx in chrono_indices: r = rows[idx] amt = r.get("amount") if amt is None: amt = r.get("debit") if r.get("debit") is not None else r.get("credit") bal = r.get("balance") if r.get("debit") is not None and r.get("credit") is None: r["sign"] = "debit" amt = r["debit"] elif r.get("credit") is not None and r.get("debit") is None: r["sign"] = "credit" amt = r["credit"] if r.get("sign") == "debit": r["debit"], r["credit"] = amt, None elif r.get("sign") == "credit": r["debit"], r["credit"] = None, amt else: if prev_balance is not None and bal is not None and amt is not None: diff = round(bal - prev_balance, 2) if diff > 0.001: r["debit"], r["credit"] = None, amt elif diff < -0.001: r["debit"], r["credit"] = amt, None else: desc = str(r.get("description", "")).upper() if any(kw in desc for kw in debit_kw): r["debit"], r["credit"] = amt, None else: r["debit"], r["credit"] = None, amt else: desc = str(r.get("description", "")).upper() if any(kw in desc for kw in debit_kw): r["debit"], r["credit"] = amt, None else: r["debit"], r["credit"] = None, amt if bal is not None: prev_balance = bal r.pop("sign", None) r.pop("amount", None) return rows # ── Spatial Line Extraction (Grouping Words into Rows) ────────────────────── def extract_spatial_lines_from_page(page, y_tolerance=4.0): """ Groups words extracted from PDF page into visual horizontal lines. Prevents column splitting bugs where Dates and Balances end up in separate blocks. """ words = page.get_text("words") if not words: raw_text = page.get_text("text") or "" return [l.strip() for l in raw_text.splitlines() if l.strip()] lines_dict = {} for w in words: x0, y0, x1, y1, text = w[:5] matched_y = None for y in sorted(lines_dict.keys()): if abs(y - y0) <= y_tolerance: matched_y = y break if matched_y is None: matched_y = y0 lines_dict[matched_y] = [] lines_dict[matched_y].append((x0, text)) result_lines = [] for y in sorted(lines_dict.keys()): line_words = [t[1] for t in sorted(lines_dict[y], key=lambda item: item[0])] result_lines.append(" ".join(line_words).strip()) return result_lines # ── Fast Parallel OCR Engine ─────────────────────────────────────────────── def _setup_tesseract(): tesseract_paths = [ r"C:\Program Files\Tesseract-OCR", r"C:\Program Files (x86)\Tesseract-OCR", os.path.expanduser(r"~\AppData\Local\Programs\Tesseract-OCR"), ] for t_path in tesseract_paths: if os.path.exists(t_path) and t_path not in os.environ.get("PATH", ""): os.environ["PATH"] = t_path + ";" + os.environ.get("PATH", "") t_exe = os.path.join(t_path, "tesseract.exe") if os.path.exists(t_exe): try: import pytesseract pytesseract.pytesseract.tesseract_cmd = t_exe except Exception: pass def _ocr_page_worker(pix_data): w, h, samples = pix_data from PIL import Image import pytesseract img = Image.frombytes("RGB", [w, h], samples) return pytesseract.image_to_string(img, lang="eng", config="--psm 6") def ocr_extract_pdf_lines(pdf_bytes, target_pages=None, password=None, progress_callback=None): """ Ultra-Fast Parallel OCR Engine for scanned PDF statements. Renders pages at 150 DPI and executes Tesseract OCR in parallel across CPU cores. """ _setup_tesseract() lines = [] # Fast Path: Multi-Threaded PyMuPDF Pixmap + PyTesseract try: import pytesseract if progress_callback: progress_callback(25, "Running high-speed multi-threaded OCR...") doc = fitz.open(stream=pdf_bytes, filetype="pdf") if password and doc.is_encrypted: doc.authenticate(password) total_pages = len(doc) indices = target_pages if target_pages else list(range(total_pages)) page_pixmaps = [] for idx in indices: if idx < total_pages: page = doc[idx] pix = page.get_pixmap(dpi=150) page_pixmaps.append((idx, (pix.width, pix.height, pix.samples))) doc.close() workers = min(8, max(1, os.cpu_count() or 4)) with ThreadPoolExecutor(max_workers=workers) as ex: futures = [(idx, ex.submit(_ocr_page_worker, pdata)) for idx, pdata in page_pixmaps] for idx, fut in futures: txt = fut.result() or "" lines.append(f"--- PAGE {idx + 1} ---") for l in txt.splitlines(): cleaned_l = re.sub(r"^[|\[{(~#_\\/ -]+", "", l.strip()) if cleaned_l: lines.append(cleaned_l) if len(re.findall(r"[a-zA-Z0-9]", "".join(lines))) >= 30: return lines except Exception: pass # Fallback Path: ocrmypdf try: import ocrmypdf import tempfile if progress_callback: progress_callback(35, "Running ocrmypdf OCR fallback...") with tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) as in_file, \ tempfile.NamedTemporaryFile(suffix=".pdf", delete=False) as out_file: in_file.write(pdf_bytes) in_path = in_file.name out_path = out_file.name try: ocr_kwargs = { "skip_text": True, "deskew": False, "rotate_pages": False, "oversample": 150, "optimize": 0, "jobs": max(1, os.cpu_count() or 4), "progress_bar": False, } if password: ocr_kwargs["password"] = password ocrmypdf.ocr(in_path, out_path, **ocr_kwargs) with fitz.open(out_path) as ocr_doc: for idx in range(len(ocr_doc)): lines.append(f"--- PAGE {idx + 1} ---") lines.extend(extract_spatial_lines_from_page(ocr_doc[idx])) finally: for p in (in_path, out_path): if os.path.exists(p): try: os.remove(p) except OSError: pass except Exception: pass return lines # ── Text Extraction (PyMuPDF Fast Path) ──────────────────────────────────── def extract_pdf_lines(pdf_bytes, page_numbers=None, password=None, progress_callback=None): lines = [] target_pages = [] try: doc = fitz.open(stream=pdf_bytes, filetype="pdf") if password and doc.is_encrypted: doc.authenticate(password) total_pages = len(doc) target_pages = list(range(total_pages)) if page_numbers: parsed_pages = [] for part in str(page_numbers).split(","): part = part.strip() if not part: continue if "-" in part: try: start_str, end_str = part.split("-", 1) start = int(start_str.strip()) - 1 end = int(end_str.strip()) - 1 if start > end: start, end = end, start parsed_pages.extend(range(max(0, start), min(total_pages, end + 1))) except ValueError: continue else: try: p = int(part) - 1 if 0 <= p < total_pages: parsed_pages.append(p) except ValueError: continue if parsed_pages: target_pages = sorted(list(set(parsed_pages))) for idx in target_pages: page = doc[idx] lines.append(f"--- PAGE {idx + 1} ---") lines.extend(extract_spatial_lines_from_page(page)) doc.close() except Exception: # Fallback to pdfplumber try: pdf_file = BytesIO(pdf_bytes) open_kwargs = {} if password: open_kwargs["password"] = password with pdfplumber.open(pdf_file, **open_kwargs) as pdf: total_pages = len(pdf.pages) target_pages = list(range(total_pages)) for idx in target_pages: lines.append(f"--- PAGE {idx + 1} ---") t = pdf.pages[idx].extract_text() or "" lines.extend([l.strip() for l in t.splitlines() if l.strip()]) except Exception as e: err_msg = str(e).lower() if "password" in err_msg or "encrypted" in err_msg or "protected" in err_msg: raise ValueError("This PDF is password-protected. Please enter the PDF password and try again.") raise e # Check if text is genuine or if it is a scanned image PDF combined_text = "".join(lines).strip() alnum_count = len(re.findall(r"[a-zA-Z0-9]", combined_text)) num_target = len(target_pages) if target_pages else 1 # Accurate Scanned PDF Check: Only trigger OCR if extracted characters are virtually empty (< 35 per page) if alnum_count < (35 * num_target): if progress_callback: progress_callback(20, "Scanned image PDF detected! Extracting via high-speed OCR...") ocr_lines = ocr_extract_pdf_lines(pdf_bytes, target_pages=target_pages, password=password, progress_callback=progress_callback) if len(re.findall(r"[a-zA-Z0-9]", "".join(ocr_lines))) > alnum_count: lines = ocr_lines cleaned_lines = [] for l in lines: if l.startswith("--- PAGE ") and l.endswith(" ---"): cleaned_lines.append(l) else: cleaned = re.sub(r"^[^\w\s/.-]+", "", l.strip()).strip() if cleaned: cleaned_lines.append(cleaned) elif l.strip(): cleaned_lines.append(l.strip()) return cleaned_lines # ── Individual Bank Parsers ──────────────────────────────────────────────── DATE_PATTERN = ( r"(\d{4}[/-]\d{2}[/-]\d{2}|" r"\d{1,2}[/-]\d{1,2}[/-]\d{2,4}|" r"\d{1,2}\s+[A-Za-z]{3,9}\s+\d{2,4}|" r"\d{1,2}-[A-Za-z]{3,9}-\d{2,4}|" r"\d{1,2}\.[A-Za-z]{3,9}\.\d{2,4}|" r"\d{1,2}\.\d{1,2}\.\d{2,4}|" r"\d{1,2}[A-Za-z]{3}\d{2,4})" ) DATE_RE = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN, re.IGNORECASE) NUMBER_RE = re.compile(r"(-?[\d,]+(?:\s*,\s*\d+)*\.\d{2})") MONTH_MAP = { "january": 1, "february": 2, "march": 3, "april": 4, "may": 5, "june": 6, "july": 7, "august": 8, "september": 9, "october": 10, "november": 11, "december": 12, "jan": 1, "feb": 2, "mar": 3, "apr": 4, "jun": 6, "jul": 7, "aug": 8, "sep": 9, "sept": 9, "oct": 10, "nov": 11, "dec": 12 } MONTH_REGEX = r"(?:January|February|March|April|May|June|July|August|September|October|November|December|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Sept|Oct|Nov|Dec)" DATE_AMEX_TEXT_RE = re.compile(r"^(" + MONTH_REGEX + r")\s+(\d{1,2})\b", re.IGNORECASE) def normalize_numbers_in_line(line): line = re.sub(r"(\d+)\s*,\s*(\d+)", r"\1,\2", line) line = re.sub(r"(\d+)\s*\.\s*(\d+)", r"\1.\2", line) return line def parse_icici(lines, pdf_path=None): row_re = re.compile( r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+(-?[\d,]+\.\d{2})\s+(-?[\d,]+\.\d{2})\s*$", re.IGNORECASE ) rows = [] n = len(lines) for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) if not stripped or stripped.startswith("--- PAGE "): continue m = row_re.match(stripped) if m: date = m.group(1) desc_part = m.group(2) amount = to_float(m.group(3)) balance = to_float(m.group(4)) prev_line = lines[i - 1].strip() if i > 0 else "" next_line = lines[i + 1].strip() if i + 1 < n else "" desc_parts = [] if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") and not re.search(r"Balance|Statement|Page|Account", prev_line, re.IGNORECASE): desc_parts.append(prev_line) desc_parts.append(desc_part) if next_line and not DATE_RE.match(next_line) and not next_line.startswith("--- PAGE ") and not NUMBER_RE.search(next_line): desc_parts.append(next_line) rows.append({ "date": date, "description": clean_desc(desc_parts), "amount": amount, "balance": balance, "sign": None, }) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_hdfc(lines, pdf_path=None): row_re = re.compile( r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+(?:(\d{5,})\s+)?" + DATE_PATTERN + r"\s+(-?[\d,]+\.\d{2})\s+(-?[\d,]+\.\d{2})\s*$", re.IGNORECASE ) alt_re = re.compile( r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+(-?[\d,]+\.\d{2})\s+(-?[\d,]+\.\d{2})\s*$", re.IGNORECASE ) rows = [] n = len(lines) for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) if not stripped or stripped.startswith("--- PAGE "): continue m = row_re.match(stripped) if m: date, desc, _ref, _vdate, amount, balance = m.group(1), m.group(2), m.group(3), m.group(4), to_float(m.group(5)), to_float(m.group(6)) prev_line = lines[i - 1].strip() if i > 0 else "" full_desc = clean_desc([prev_line if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") else "", desc]) rows.append({"date": date, "description": full_desc, "amount": amount, "balance": balance, "sign": None}) else: m2 = alt_re.match(stripped) if m2 and not re.search(r"Opening\s*Balance|Statement|Page", stripped, re.IGNORECASE): date, desc, amount, balance = m2.group(1), m2.group(2), to_float(m2.group(3)), to_float(m2.group(4)) prev_line = lines[i - 1].strip() if i > 0 else "" full_desc = clean_desc([prev_line if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") else "", desc]) rows.append({"date": date, "description": full_desc, "amount": amount, "balance": balance, "sign": None}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_sbi(lines, pdf_path=None): rows = [] for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) if not stripped or stripped.startswith("--- PAGE "): continue m = DATE_RE.match(stripped) if not m: continue parts = stripped.split() if len(parts) < 4: continue date = m.group(1) balance = to_float(parts[-1]) if balance is None: continue amount, debit, credit = None, None, None if len(parts) >= 6: cand1 = to_float(parts[-3]) cand2 = to_float(parts[-2]) if cand1 is not None and (cand2 is not None or parts[-2] in ("-", "0", "0.00")): debit = cand1 credit = cand2 if parts[-2] not in ("-", "0", "0.00") else None desc = " ".join(parts[1:-3]) elif cand2 is not None and (cand1 is not None or parts[-3] in ("-", "0", "0.00")): debit = cand1 if parts[-3] not in ("-", "0", "0.00") else None credit = cand2 desc = " ".join(parts[1:-3]) else: amount = to_float(parts[-2]) desc = " ".join(parts[1:-2]) else: amount = to_float(parts[-2]) desc = " ".join(parts[1:-2]) prev_line = lines[i - 1].strip() if i > 0 else "" if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") and not re.search(r"Balance|Statement|Page", prev_line, re.IGNORECASE): desc = clean_desc([prev_line, desc]) rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "debit": debit, "credit": credit, "balance": balance, "sign": None}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_axis(lines, pdf_path=None): row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})(?:\s+\d+)?\s*$", re.IGNORECASE) rows = [] for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) if not stripped or stripped.startswith("--- PAGE "): continue m = row_re.match(stripped) if not m: continue date, tail, amount, balance = m.group(1), m.group(2), to_float(m.group(3)), to_float(m.group(4)) prev_line = lines[i - 1].strip() if i > 0 else "" desc = clean_desc([prev_line if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") else "", tail]) rows.append({"date": date, "description": desc, "amount": amount, "balance": balance, "sign": None}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_kotak(lines, pdf_path=None): row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})(?:\s*\((?:Cr|Dr)\))?\s*$", re.IGNORECASE) rows = [] for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) if not stripped or stripped.startswith("--- PAGE "): continue m = row_re.match(stripped) if not m: continue date, desc_part, amount, balance = m.group(1), m.group(2), to_float(m.group(3)), to_float(m.group(4)) sign = "credit" if "CR" in stripped.upper() else ("debit" if "DR" in stripped.upper() else None) prev_line = lines[i - 1].strip() if i > 0 else "" desc = clean_desc([prev_line if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") else "", desc_part]) rows.append({"date": date, "description": desc, "amount": amount, "balance": balance, "sign": sign}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_pnb(lines, pdf_path=None): row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s*(.*)$", re.IGNORECASE) rows = [] for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) m = row_re.match(stripped) if not m: continue date, amount, balance, trail = m.group(1), to_float(m.group(2)), to_float(m.group(3)), m.group(4) prev_line = lines[i - 1].strip() if i > 0 else "" desc_parts = [prev_line if prev_line and not DATE_RE.match(prev_line) else "", trail] rows.append({"date": date, "description": clean_desc(desc_parts), "amount": amount, "balance": balance, "sign": None}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_ubi(lines, pdf_path=None): row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s+(Cr|Dr)\b", re.IGNORECASE) alt_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s*$", re.IGNORECASE) rows = [] for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) m = row_re.match(stripped) if m: date, desc, amount, balance, balsign = m.group(1), m.group(2), to_float(m.group(3)), to_float(m.group(4)), m.group(5) rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": "credit" if balsign.upper() == "CR" else "debit"}) else: m2 = alt_re.match(stripped) if m2 and not re.search(r"Opening|Statement|Page", stripped, re.IGNORECASE): date, desc, amount, balance = m2.group(1), m2.group(2), to_float(m2.group(3)), to_float(m2.group(4)) rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": None}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_idfc(lines, pdf_path=None): row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(?:" + DATE_PATTERN + r"\s+)?(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s*$", re.IGNORECASE) rows = [] for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) m = row_re.match(stripped) if m: date, desc, amount, balance = m.group(1), m.group(3), to_float(m.group(4)), to_float(m.group(5)) rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": None}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_indusind(lines, pdf_path=None): row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(.*?)\s+([\d,]+\.\d{2})\s+(Dr|Cr)\s+([\d,]+\.\d{2})\s+(Cr|Dr)\s*$", re.IGNORECASE) rows = [] for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) m = row_re.match(stripped) if m: date, desc, amount, txn_sign, balance, _balsign = m.group(1), m.group(2), to_float(m.group(3)), m.group(4), to_float(m.group(5)), m.group(6) rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": "debit" if txn_sign.upper() == "DR" else "credit"}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_idbi(lines, pdf_path=None): row_re = re.compile(r"^(?:\d+\s+)?" + DATE_PATTERN + r"\s+(?:" + DATE_PATTERN + r"\s+)?(.*?)\s+([\d,]+\.\d{2})\s+([\d,]+\.\d{2})\s*$", re.IGNORECASE) rows = [] for i, line in enumerate(lines): stripped = normalize_numbers_in_line(line.strip()) m = row_re.match(stripped) if m: date, desc, amount, balance = m.group(1), m.group(3), to_float(m.group(4)), to_float(m.group(5)) rows.append({"date": date, "description": clean_desc([desc]), "amount": amount, "balance": balance, "sign": None}) if len(rows) >= 2: return assign_debit_credit(rows, lines) return parse_generic_statement(lines) def parse_bob(lines, pdf_path=None): """Bank of Baroda Statement Parser""" return parse_generic_statement(lines) def parse_canara(lines, pdf_path=None): """Canara Bank Statement Parser""" return parse_generic_statement(lines) def parse_hsbc(lines, pdf_path=None): """HSBC Bank Statement Parser""" return parse_generic_statement(lines) def parse_federal(lines, pdf_path=None): """Federal Bank Statement Parser""" return parse_generic_statement(lines) def parse_yesbank(lines, pdf_path=None): """Yes Bank Statement Parser""" return parse_generic_statement(lines) def parse_credit_card(lines, pdf_path=None): """ Dedicated Credit Card Statement Parser. Handles Credit Card statement table formats: - Separate DR Column & CR Column - Single Amount + DR/CR Flag Column - Inline DR/CR Suffixes - Scanned tables with bracket/pipe borders """ if not lines: return [] rows = [] n = len(lines) for i, line in enumerate(lines): stripped = re.sub(r"^[|\[{(~#_\\/ -]+", "", line.strip()) if not stripped or stripped.startswith("--- PAGE "): continue m = DATE_RE.match(stripped) if not m: continue date_str = m.group(1) rest = stripped[m.end():].strip() drcr_match = re.findall(r"\b(DR|CR|Dr|Cr|CRs|oR|pr|pR)\b", rest, re.IGNORECASE) num_matches = NUMBER_RE.findall(rest) if not num_matches: continue floats = [to_float(nm) for nm in num_matches if to_float(nm) is not None] if not floats: continue debit, credit, sign = None, None, None if drcr_match: last_drcr = drcr_match[-1].upper() amt = floats[0] if last_drcr in ("DR", "OR", "PR"): debit = amt sign = "debit" elif last_drcr in ("CR", "CRS"): credit = amt sign = "credit" elif len(floats) >= 2: deb_cand = floats[0] cred_cand = floats[1] if deb_cand != 0 and cred_cand == 0: debit = deb_cand sign = "debit" elif cred_cand != 0 and deb_cand == 0: credit = cred_cand sign = "credit" else: if re.search(r"PAYMENT|REFUND|REVERSAL|CASHBACK|CREDIT", rest, re.IGNORECASE): credit = cred_cand if cred_cand != 0 else deb_cand sign = "credit" else: debit = deb_cand sign = "debit" else: amt = floats[0] if re.search(r"PAYMENT|REFUND|REVERSAL|CASHBACK|CREDIT", rest, re.IGNORECASE): credit = amt sign = "credit" else: debit = amt sign = "debit" desc = rest for nm in num_matches: desc = desc.replace(nm, "") for dc in ["DR", "CR", "Dr", "Cr", "CRs", "oR", "pr", "pR"]: desc = re.sub(r"\b" + dc + r"\b", "", desc, flags=re.IGNORECASE) desc = re.sub(r"\s+", " ", desc).strip() prev_line = lines[i - 1].strip() if i > 0 else "" next_line = lines[i + 1].strip() if i + 1 < n else "" desc_parts = [] if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") and not re.search(r"Statement|Page|Card Number|Total|Minimum|Date|Transaction|Description|Amount|Particulars|Balance|SERIAL", prev_line, re.IGNORECASE): desc_parts.append(prev_line) desc_parts.append(desc) if next_line and not DATE_RE.match(next_line) and not next_line.startswith("--- PAGE ") and not re.search(r"Statement|Page|Card Number|Total|Minimum|Date|Transaction|Description|Amount|Particulars|Balance|SERIAL", next_line, re.IGNORECASE): if not NUMBER_RE.search(next_line): desc_parts.append(next_line) full_desc = clean_desc(desc_parts) if re.search(r"Total Amount Due|Minimum Amount Due|Payment Due Date|Credit Limit|Available Limit", full_desc, re.IGNORECASE): continue rows.append({ "date": date_str, "description": full_desc, "debit": debit, "credit": credit, "balance": None, "sign": sign, }) return assign_debit_credit(rows, lines) def parse_amex(lines, pdf_path=None): """ American Express (Amex) Credit Card Statement Parser. Extracts transactions across multi-page statements: - Spends, Domestic & Overseas purchases, Taxes/GST, Membership Fees, Installments - Payments Received, Refunds, and Credits - Excludes non-transaction summary blocks (e.g. Summary of New Installment Plans Created) - Accurately tracks Opening Balance, debits, credits, and running card balances """ if not lines: return [] stmt_day, stmt_month, stmt_year = None, None, None opening_bal = None closing_bal = None header_text = " ".join(lines[:70]) m_date = re.search(r"\b(\d{2})/(\d{2})/(\d{4})\b", header_text) if m_date: stmt_day, stmt_month, stmt_year = int(m_date.group(1)), int(m_date.group(2)), int(m_date.group(3)) else: m_date2 = re.search(r"(?:Statement\s+includes\s+payments.*?by|Statement\s+Period\s+From.*?to)\s+([A-Za-z]+)\s+(\d{1,2}),?\s+(\d{4})", header_text, re.I) if m_date2: m_name = m_date2.group(1).lower() stmt_day = int(m_date2.group(2)) stmt_month = MONTH_MAP.get(m_name, 1) stmt_year = int(m_date2.group(3)) m_sum = re.search( r"Opening\s*Balance\s*Rs.*?([\d,]+\.\d{2}(?:\s*CR)?)\s*-\s*([\d,]+\.\d{2}(?:\s*CR)?)\s*\+\s*([\d,]+\.\d{2}(?:\s*CR)?)\s*=\s*([\d,]+\.\d{2}(?:\s*CR)?)", header_text, re.I, ) if m_sum: def parse_bal(s): val = to_float(s.replace("CR", "").replace("Cr", "").replace("cr", "")) if val is None: return None return -val if "CR" in s.upper() else val opening_bal = parse_bal(m_sum.group(1)) closing_bal = parse_bal(m_sum.group(4)) else: m_op = re.search(r"Opening\s*Balance\s*Rs\s*[:\n]?\s*([\d,]+\.\d{2}(?:\s*CR)?)", header_text, re.I) if m_op: s = m_op.group(1).upper() val = to_float(s.replace("CR", "")) if val is not None: opening_bal = -val if "CR" in s else val transactions = [] in_details = False in_new_plans_summary = False n = len(lines) i = 0 while i < n: line = lines[i].strip() # Table start markers if re.search(r"Details\s+(?:Foreign\s+Spending\s+)?Amount\s+Rs", line, re.I) or re.search(r"\bDetails\s+Amount\s+Rs\b", line, re.I): in_details = True i += 1 continue # Informational section to skip if re.search(r"Summary of New Installment Plans Created", line, re.I): in_new_plans_summary = True i += 1 continue if re.search(r"Total of New Installment Plans Created", line, re.I): in_new_plans_summary = False i += 1 continue # End markers on current page if any(marker in line for marker in [ "Payment Advice", "Please return this advice", "Payment Information", "Drop Boxes", "Mailing Preference", "Grievances Redressal", "Missing Payments may affect", "CardMember Offers", "Annual Fee", "Interest Free Grace Period", "Sample Interest Illustration", "Most Important Terms & Conditions" ]): in_details = False i += 1 continue if line.startswith("--- PAGE "): in_details = False i += 1 continue if not in_details or in_new_plans_summary: i += 1 continue # Subtotals and Section Headers to ignore if any(sub in line for sub in [ "New domestic transactions for", "New overseas transactions for", "Total of other account transactions", "Total of Installments", "TOTAL OVERSEAS SPEND IN CURRENT MONTH", "OTHER ACCOUNT TRANSACTIONS", "Installment Plan Transactions", "Credit Summary", "Current Rates of Interest", "Monthly Rate", "Statement Period From", "Payment Due Date" ]): i += 1 continue m_dt = DATE_AMEX_TEXT_RE.match(line) if m_dt: month_name = m_dt.group(1).lower() day_num = int(m_dt.group(2)) month_num = MONTH_MAP.get(month_name) txn_year = stmt_year if stmt_month and stmt_year and month_num: if stmt_month < month_num: txn_year = stmt_year - 1 else: txn_year = stmt_year date_str = f"{day_num:02d}/{month_num:02d}/{txn_year}" if txn_year and month_num else f"{day_num:02d}/{month_name}" num_matches = NUMBER_RE.findall(line) if not num_matches: i += 1 continue amt = to_float(num_matches[-1]) if amt is None: i += 1 continue rest = line[m_dt.end():].strip() rest = re.sub(re.escape(num_matches[-1]) + r"\s*$", "", rest).strip() if len(num_matches) > 1: for fnm in num_matches[:-1]: rest = rest.replace(fnm, "").strip() is_cr = bool(re.search(r"\bCR\b", line, re.I) or line.strip().endswith("CR")) desc_parts = [rest] j = i + 1 while j < n: next_l = lines[j].strip() if not next_l or next_l.startswith("--- PAGE "): break if DATE_AMEX_TEXT_RE.match(next_l): break if any(hdr in next_l for hdr in [ "New domestic transactions", "New overseas transactions", "Total of", "OTHER ACCOUNT TRANSACTIONS", "Installment Plan Transactions", "Payment Advice", "Payment Information", "Summary of New", "Drop Boxes", "Please return" ]): break if re.search(r"\bCR\b", next_l, re.I): is_cr = True cleaned_desc_l = re.sub(r"\b(CR|Card\s+Number\s+XXXX-XXXXXX-\d+|SINGAPORE\s+DOLLAR|USD|EUR|GBP|AUD)\b", "", next_l, flags=re.I).strip() if cleaned_desc_l: desc_parts.append(cleaned_desc_l) j += 1 if j - i >= 2: break i = j clean_desc_str = clean_desc(desc_parts) if re.search(r"PAYMENT RECEIVED|TRANSFERRED TO INSTALLMENT PLAN|REFUND|CASHBACK", clean_desc_str, re.I): is_cr = True debit = None if is_cr else amt credit = amt if is_cr else None transactions.append({ "date": date_str, "description": clean_desc_str, "debit": debit, "credit": credit, "balance": None }) continue i += 1 # Running balance calculation if opening_bal is not None: curr_bal = opening_bal for t in transactions: if t.get("credit") is not None: curr_bal -= t["credit"] if t.get("debit") is not None: curr_bal += t["debit"] t["balance"] = round(curr_bal, 2) return transactions # ── Universal Multi-Page Generic Statement Parser (Zero AI Tokens) ──────── def parse_generic_statement(lines, pdf_path=None): """ Universal Pure-Python Multi-Page Bank Statement Parser. Extracts all rows across all pages without dropping transactions. """ if not lines: return [] rows = [] n = len(lines) for i, line in enumerate(lines): raw_clean = re.sub(r"^[|\[{(~#_\\/ -]+", "", line.strip()) if not raw_clean or raw_clean.startswith("--- PAGE "): continue normalized = normalize_numbers_in_line(raw_clean) m = DATE_RE.match(normalized) if not m: continue date_str = m.group(1) num_matches = NUMBER_RE.findall(normalized) if not num_matches: continue floats = [] for nm in num_matches: val = to_float(nm) if val is not None: floats.append(val) if not floats: continue balance = floats[-1] amount, debit, credit, sign = None, None, None, None if len(floats) >= 3: cand_deb = floats[-3] cand_cred = floats[-2] if re.search(r"\bDr\b|/DR/|\bDR\b", normalized, re.IGNORECASE): debit = cand_deb if cand_deb != 0 else cand_cred sign = "debit" elif re.search(r"\bCr\b|/CR/|\bCR\b", normalized, re.IGNORECASE): credit = cand_cred if cand_cred != 0 else cand_deb sign = "credit" else: amount = cand_cred if cand_cred != 0 else cand_deb elif len(floats) == 2: amount = floats[0] balance = floats[1] elif len(floats) == 1: balance = floats[0] desc_line = normalized[m.end():].strip() for nm in num_matches: desc_line = desc_line.replace(nm, "").strip() prev_line = lines[i - 1].strip() if i > 0 else "" next_line = lines[i + 1].strip() if i + 1 < n else "" desc_parts = [] if prev_line and not DATE_RE.match(prev_line) and not prev_line.startswith("--- PAGE ") and not re.search(r"Balance|Statement|Page|Account", prev_line, re.IGNORECASE): desc_parts.append(prev_line) desc_parts.append(desc_line) if next_line and not DATE_RE.match(next_line) and not next_line.startswith("--- PAGE ") and not re.search(r"Balance|Statement|Page|Account", next_line, re.IGNORECASE): if not NUMBER_RE.search(next_line): desc_parts.append(next_line) full_desc = clean_desc(desc_parts) # Skip summary lines if re.search(r"Opening Balance|Balance B/F|Closing Balance|Statement Summary|Total Withdrawal|Total Deposit", full_desc, re.IGNORECASE) and len(floats) == 1: continue rows.append({ "date": date_str, "description": full_desc, "amount": amount, "debit": debit, "credit": credit, "balance": balance, "sign": sign, }) return assign_debit_credit(rows, lines) # ── Bank Parsers Registry & Signatures ───────────────────────────────────── BANK_PARSERS = { "pnb": parse_pnb, "ubi": parse_ubi, "icici": parse_icici, "idfc": parse_idfc, "indusind": parse_indusind, "kotak": parse_kotak, "sbi": parse_sbi, "axis": parse_axis, "hdfc": parse_hdfc, "idbi": parse_idbi, "bob": parse_bob, "canara": parse_canara, "hsbc": parse_hsbc, "federal": parse_federal, "yesbank": parse_yesbank, "creditcard": parse_credit_card, "amex": parse_amex, } BANK_NAMES = { "axis": "Axis Bank", "icici": "ICICI Bank", "sbi": "State Bank of India", "kotak": "Kotak Mahindra Bank", "hdfc": "HDFC Bank", "pnb": "Punjab National Bank", "ubi": "Union Bank of India", "idfc": "IDFC First Bank", "indusind": "IndusInd Bank", "idbi": "IDBI Bank", "bob": "Bank of Baroda", "canara": "Canara Bank", "hsbc": "HSBC Bank", "federal": "Federal Bank", "yesbank": "Yes Bank", "creditcard": "Credit Card Statement Parser", "amex": "American Express", "other": "Other Bank (AI Parser)", } BANK_SIGNATURES = [ (re.compile(r"AMERICAN EXPRESS|americanexpress|AEBC Card", re.IGNORECASE), parse_amex), (re.compile(r"HDFC0\d|HDFC BANK|HO HDFC BANK|SDEC BANK", re.IGNORECASE), parse_hdfc), (re.compile(r"\bPNB\b|PUNB\d|PUNJAB NATIONAL BANK", re.IGNORECASE), parse_pnb), (re.compile(r"UBIN\d|UNION BANK", re.IGNORECASE), parse_ubi), (re.compile(r"ICIC\d|ICICI BANK|khayaal aapka", re.IGNORECASE), parse_icici), (re.compile(r"IDFB\d|IDFC FIRST", re.IGNORECASE), parse_idfc), (re.compile(r"INDB\d|INDUSIND", re.IGNORECASE), parse_indusind), (re.compile(r"KKBK\d|KOTAK", re.IGNORECASE), parse_kotak), (re.compile(r"SBIN\d|STATE BANK OF INDIA", re.IGNORECASE), parse_sbi), (re.compile(r"UTIB\d|AXIS BANK", re.IGNORECASE), parse_axis), (re.compile(r"IBKL\d|IDBI BANK", re.IGNORECASE), parse_idbi), (re.compile(r"BARB0\d|BANK OF BARODA", re.IGNORECASE), parse_bob), (re.compile(r"CNRB0\d|CANARA BANK", re.IGNORECASE), parse_canara), (re.compile(r"HSBC0\d|HSBC BANK|Statement of Accounts\s*Account Statement", re.IGNORECASE), parse_hsbc), (re.compile(r"FDRL0\d|FEDERAL BANK", re.IGNORECASE), parse_federal), (re.compile(r"YESB0\d|YES BANK", re.IGNORECASE), parse_yesbank), (re.compile(r"CREDIT CARD|CARD STATEMENT|MINIMUM AMOUNT DUE|TOTAL AMOUNT DUE|Purchases & Debits", re.IGNORECASE), parse_credit_card), ] def detect_bank_parser(lines): sample = " ".join(lines[:60]) for sig_re, parser_fn in BANK_SIGNATURES: if sig_re.search(sample): return parser_fn return None # ── AI Self-Learning Engine ──────────────────────────────────────────────── def call_llm(provider, prompt, max_tokens=4096): if provider["kind"] == "openai": resp = requests.post( provider["url"], headers={"Authorization": f"Bearer {provider['api_key']}", "Content-Type": "application/json"}, json={"model": provider["model"], "messages": [{"role": "user", "content": prompt}], "temperature": 0, "max_tokens": max_tokens}, timeout=40, ) if resp.status_code != 200: raise RuntimeError(f"{provider['name']} error {resp.status_code}: {resp.text[:300]}") data = resp.json() choices = data.get("choices", []) if not choices: raise ValueError("No choices in LLM response") return choices[0].get("message", {}).get("content") or "" elif provider["kind"] == "gemini": url = provider["url"].format(model=provider["model"]) + f"?key={provider['api_key']}" resp = requests.post( url, json={"contents": [{"parts": [{"text": prompt}]}], "generationConfig": {"temperature": 0, "maxOutputTokens": max_tokens}}, timeout=40, ) if resp.status_code != 200: raise RuntimeError(f"{provider['name']} error {resp.status_code}: {resp.text[:300]}") data = resp.json() candidates = data.get("candidates", []) if not candidates: raise ValueError("No candidates in Gemini response") parts = candidates[0].get("content", {}).get("parts", []) return parts[0].get("text", "") or "" raise ValueError(f"Unknown provider kind: {provider['kind']}") def get_multi_page_sample(lines_input, lines_per_page=35): if not lines_input: return "" if isinstance(lines_input, str): lines_input = lines_input.split("\n") sample = lines_input[:70] return "\n".join(sample) def get_ai_parser(lines_or_sample): if isinstance(lines_or_sample, list): sample_text = get_multi_page_sample(lines_or_sample) all_lines = lines_or_sample else: sample_text = str(lines_or_sample) all_lines = sample_text.split("\n") prompt = f"""You are an expert Python developer and financial statement parser. Below are sample text lines extracted from a bank statement: --- STATEMENT SAMPLE --- {sample_text[:3000]} --- END SAMPLE --- Write a standalone Python function `parse_statement(lines)` that extracts all financial transactions. Return ONLY Python code inside ```python ``` block: ```python def parse_statement(lines): transactions = [] # your logic here return assign_debit_credit(transactions, lines) ``` """ for provider in PROVIDERS: if not provider.get("api_key"): continue try: raw_text = call_llm(provider, prompt, max_tokens=2048) code_match = re.search(r"```python\s*([\s\S]*?)```", raw_text, re.IGNORECASE) if code_match: func_code = code_match.group(1).strip() local_ns = {} safe_ns = { "re": re, "datetime": datetime, "to_float": to_float, "clean_desc": clean_desc, "parse_any_date": parse_any_date, "assign_debit_credit": assign_debit_credit, "len": len, "range": range } exec(func_code, safe_ns, local_ns) if "parse_statement" in local_ns: fn = local_ns["parse_statement"] test_res = fn(all_lines) if isinstance(test_res, list) and len(test_res) > 0: return fn, f"Other Bank ({provider['name'].upper()} AI Parser)", func_code except Exception: continue raise RuntimeError("AI layout learning unavailable. Falling back to universal engine.") # ── Global Caches & Pipeline ─────────────────────────────────────────────── CACHE_FILE = os.path.join(os.path.dirname(__file__), "learned_parsers_cache.json") DYNAMIC_LEARNED_PARSERS = {} PARSED_STATEMENTS_CACHE = {} def load_learned_parsers_from_disk(): if os.path.exists(CACHE_FILE): try: with open(CACHE_FILE, "r", encoding="utf-8") as f: saved = json.load(f) for slug_or_sig, code_str in saved.items(): local_ns = {} safe_ns = { "re": re, "datetime": datetime, "to_float": to_float, "clean_desc": clean_desc, "parse_any_date": parse_any_date, "assign_debit_credit": assign_debit_credit, "len": len, "range": range } exec(code_str, safe_ns, local_ns) if "parse_statement" in local_ns: DYNAMIC_LEARNED_PARSERS[slug_or_sig] = local_ns["parse_statement"] except Exception: pass load_learned_parsers_from_disk() def save_learned_parser_to_disk(slug_or_sig, code_str): saved = {} if os.path.exists(CACHE_FILE): try: with open(CACHE_FILE, "r", encoding="utf-8") as f: saved = json.load(f) except Exception: saved = {} saved[slug_or_sig] = code_str try: with open(CACHE_FILE, "w", encoding="utf-8") as f: json.dump(saved, f, indent=2) except Exception: pass def clear_all_caches(): DYNAMIC_LEARNED_PARSERS.clear() PARSED_STATEMENTS_CACHE.clear() if os.path.exists(CACHE_FILE): try: with open(CACHE_FILE, "w", encoding="utf-8") as f: json.dump({}, f) except Exception: pass def parse_statement_pdf(pdf_bytes, bank_slug=None, page_numbers=None, password=None, progress_callback=None): """ Blazing Fast 4-Layer Statement Parser Pipeline: Layer 1: Instant In-Memory Cache Check. Layer 2: Fast Spatial Line Extraction (PyMuPDF < 0.05s). Layer 3: Native High-Performance Bank Parser / Universal Engine. Layer 4: Intelligent Parallel OCR & AI Layout Fallback. """ file_hash = hashlib.md5(pdf_bytes).hexdigest() cache_key = f"{file_hash}_{bank_slug or ''}_{page_numbers or ''}" if cache_key in PARSED_STATEMENTS_CACHE: cached_df = PARSED_STATEMENTS_CACHE[cache_key] if cached_df is not None and not cached_df.empty: return cached_df.copy() if progress_callback: progress_callback(10, "Extracting PDF text and table layout...") lines = extract_pdf_lines(pdf_bytes, page_numbers=page_numbers, password=password, progress_callback=progress_callback) transactions = [] bank_name = BANK_NAMES.get(bank_slug, bank_slug.upper() if bank_slug else "Unknown") if bank_slug else None # Try matching specific parser first if bank_slug and bank_slug in BANK_PARSERS and bank_slug != "other": parser = BANK_PARSERS[bank_slug] if progress_callback: progress_callback(40, f"Parsing {bank_name} statement...") try: transactions = parser(lines) except Exception: transactions = [] # If no bank selected or parsed 0 rows, auto-detect if not transactions: parser = detect_bank_parser(lines) if parser: detected_name = parser.__name__.replace("parse_", "").upper() try: transactions = parser(lines) if transactions: bank_name = bank_name or detected_name except Exception: transactions = [] # Universal Engine Fallback (Fast, Pure-Python, Multi-Page) if not transactions or len(transactions) <= 2: if progress_callback: progress_callback(60, "Running Universal Statement Engine...") gen_txs = parse_generic_statement(lines) if len(gen_txs) > len(transactions): transactions = gen_txs bank_name = bank_name or "Generic Engine" # AI Layout Revamp Fallback (Only if entries are missing or < 3) if not transactions or len(transactions) <= 2: if progress_callback: progress_callback(75, "Learning custom statement layout...") try: ai_fn, ai_bank_name, func_code = get_ai_parser(lines) ai_txs = ai_fn(lines) if isinstance(ai_txs, list) and len(ai_txs) > len(transactions): transactions = ai_txs bank_name = f"{bank_name or 'Bank'} (AI Revamped)" except Exception: pass df = pd.DataFrame(transactions, columns=["date", "description", "debit", "credit", "balance"]) df.insert(0, "bank_name", bank_name or "Unknown") if not df.empty: if len(PARSED_STATEMENTS_CACHE) > 100: PARSED_STATEMENTS_CACHE.clear() PARSED_STATEMENTS_CACHE[cache_key] = df return df def excel_bytes_from_df(df, filename="statement.xlsx"): buffer = BytesIO() if df is not None and not df.empty: df = df.copy() for col in ["debit", "credit", "balance"]: if col in df.columns: df[col] = pd.to_numeric(df[col], errors="coerce") if "balance" in df.columns and "description" in df.columns: has_opening = any("opening" in str(d).lower() or "b/f" in str(d).lower() for d in df["description"]) if not has_opening and len(df) > 0: first_row = df.iloc[0] first_bal = first_row.get("balance") first_deb = first_row.get("debit") first_cred = first_row.get("credit") if pd.notna(first_bal): bname = str(first_row.get("bank_name", "")).lower() is_cc = "credit card" in bname or "amex" in bname or "american express" in bname if is_cc: if pd.notna(first_cred) and first_cred > 0: open_bal = first_bal + first_cred elif pd.notna(first_deb) and first_deb > 0: open_bal = first_bal - first_deb else: open_bal = first_bal else: if pd.notna(first_cred) and first_cred > 0: open_bal = first_bal - first_cred elif pd.notna(first_deb) and first_deb > 0: open_bal = first_bal + first_deb else: open_bal = first_bal first_date = first_row.get("date", "") bank_name = first_row.get("bank_name", "") open_row = pd.DataFrame([{ "bank_name": bank_name, "date": first_date, "description": "Opening Balance / Balance B/F", "debit": None, "credit": None, "balance": round(float(open_bal), 2) }]) df = pd.concat([open_row, df], ignore_index=True) with pd.ExcelWriter(buffer, engine="openpyxl") as writer: df.to_excel(writer, sheet_name="Transactions", index=False) worksheet = writer.sheets["Transactions"] from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter header_font = Font(name="Calibri", size=11, bold=True, color="FFFFFF") header_fill = PatternFill(start_color="1F497D", end_color="1F497D", fill_type="solid") num_format = "#,##0.00" for col_idx in range(1, len(df.columns) + 1): cell = worksheet.cell(row=1, column=col_idx) cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal="center", vertical="center") for row_idx in range(2, len(df) + 2): for col_idx, col_name in enumerate(df.columns, start=1): cell = worksheet.cell(row=row_idx, column=col_idx) if col_name in ["debit", "credit", "balance"]: if cell.value is not None and isinstance(cell.value, (int, float)): cell.number_format = num_format cell.alignment = Alignment(horizontal="right") elif col_name == "date": cell.alignment = Alignment(horizontal="center") for col in worksheet.columns: max_len = 0 col_letter = get_column_letter(col[0].column) for cell in col: val_str = str(cell.value or "") if len(val_str) > max_len: max_len = len(val_str) worksheet.column_dimensions[col_letter].width = max(max_len + 4, 12) buffer.seek(0) return buffer def excel_bytes_from_multi_statements(file_dfs, combined_df): """ Builds a multi-tab Excel workbook from multiple parsed statements: - Sheet 1: 'Consolidated' with all transactions across all statements - Subsequent Sheets: Individual statement tabs named after each file """ buffer = BytesIO() with pd.ExcelWriter(buffer, engine="openpyxl") as writer: combined_df.to_excel(writer, sheet_name="Consolidated", index=False) used_names = set(["Consolidated"]) for fname, df in file_dfs: base_sname = re.sub(r"[\\/*?:\[\]]", "", fname.replace(".pdf", "").replace(".PDF", "")).strip()[:28] sname = base_sname or "Statement" counter = 1 while sname in used_names: sname = f"{base_sname}_{counter}"[:31] counter += 1 used_names.add(sname) df.to_excel(writer, sheet_name=sname, index=False) from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.utils import get_column_letter header_font = Font(name="Calibri", size=11, bold=True, color="FFFFFF") header_fill = PatternFill(start_color="1F497D", end_color="1F497D", fill_type="solid") num_format = "#,##0.00" for sname in writer.sheets: worksheet = writer.sheets[sname] for col_idx in range(1, worksheet.max_column + 1): cell = worksheet.cell(row=1, column=col_idx) cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal="center", vertical="center") for row_idx in range(2, worksheet.max_row + 1): for col_idx in range(1, worksheet.max_column + 1): cell = worksheet.cell(row=row_idx, column=col_idx) col_header = str(worksheet.cell(row=1, column=col_idx).value or "").lower() if col_header in ["debit", "credit", "balance"]: if cell.value is not None and isinstance(cell.value, (int, float)): cell.number_format = num_format cell.alignment = Alignment(horizontal="right") elif col_header in ["date"]: cell.alignment = Alignment(horizontal="center") for col in worksheet.columns: max_len = 0 col_letter = get_column_letter(col[0].column) for cell in col: val_str = str(cell.value or "") if len(val_str) > max_len: max_len = len(val_str) worksheet.column_dimensions[col_letter].width = max(max_len + 4, 12) buffer.seek(0) return buffer
import os
import re
import json
import hashlib
from datetime import datetime
from io import BytesIO
from concurrent.futures import ThreadPoolExecutor
try:
import pymupdf as fitz
except ImportError:
import fitz
import pdfplumber
import pandas as pd
import requests
GROQ_API_KEY = os.getenv("GROQ_API_KEY", "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")
GEMINI_API_KEY = os.getenv("GEMINI_API_KEY", "AQ.Ab8RN6KAedFsuHHpJ3aizbHW7ptE0xf8UPdq8cvLTLdUp8ILrQ")
OPENROUTER_API_KEY = os.getenv("OPENROUTER_API_KEY", "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")
PROVIDERS = [
{"name": "groq_fast", "kind": "openai", "url": "https://api.groq.com/openai/v1/chat/completions",
"api_key": GROQ_API_KEY, "model": "llama-3.1-8b-instant"},
{"name": "groq", "kind": "openai", "url": "https://api.groq.com/openai/v1/chat/completions",
"api_key": GROQ_API_KEY, "model": "llama-3.3-70b-versatile"},
{"name": "gemini", "kind": "gemini", "url": "https://generativelanguage.googleapis.com/v1beta/models/{model}:generateContent",
"api_key": GEMINI_API_KEY, "model": "gemini-2.0-flash"},
{"name": "openrouter", "kind": "openai", "url": "https://openrouter.ai/api/v1/chat/completions",
"api_key": OPENROUTER_API_KEY, "model": "openrouter/free"},
]
DATE_FORMATS = [
"%d/%m/%Y", "%d-%m-%Y", "%d.%m.%Y",
"%d/%m/%y", "%d-%m-%y", "%d.%m.%y",
"%d-%b-%Y", "%d-%b-%y", "%d %b %Y", "%d %b %y",
"%d-%B-%Y", "%d %B %Y",
"%Y/%m/%d", "%Y-%m-%d", "%Y.%m.%d",
"%d%b%Y", "%d%B%Y"
]
def to_float(s):
if s is None:
return None
s_clean = str(s).replace(",", "").replace(" ", "").replace("`", "").strip()
if not s_clean or s_clean in ("-", "--", "None", "null", "CR", "DR", "Cr", "Dr"):
return None
try:
return float(s_clean)
except ValueError:
return None
def clean_desc(parts):
cleaned = []
for p in parts:
if not p:
continue
p_str = str(p).strip()
p_str = re.sub(r"^[|\[{(~#_\\/ -]+", "", p_str)
p_str = re.sub(r"[|\]})~#_\\/ -]+$", "", p_str)
p_str = re.sub(r"\s+", " ", p_str).strip()
if p_str and p_str not in ("-", "--", "|", "/"):
cleaned.append(p_str)
return " ".join(cleaned)
def parse_any_date(d):
if not d:
return None
d_clean = str(d).strip().replace(" ", "-").replace("/", "-").replace(".", "-")
for fmt in DATE_FORMATS:
try:
return datetime.strptime(d.strip(), fmt)
except ValueError:
continue
# Try normalized hyphen format
for fmt in ["%d-%m-%Y", "%d-%m-%y", "%d-%b-%Y", "%d-%b-%y", "%Y-%m-%d"]:
try:
return datetime.strptime(d_clean, fmt)
except ValueError:
continue
return None
def assign_debit_credit(rows, lines=None):
if not rows:
return rows
for r in rows:
if r.get("description"):
r["description"] = clean_desc([str(r["description"])])
parsed_dates = [parse_any_date(r.get("date", "")) for r in rows]
valid_dates = [(i, d) for i, d in enumerate(parsed_dates) if d is not None]
ascending = True
if len(valid_dates) >= 2:
if valid_dates[0][1] != valid_dates[-1][1]:
ascending = valid_dates[0][1] < valid_dates[-1][1]
if ascending:
chrono_indices = list(range(len(rows)))
else: