In retail banking, credit underwriting, and personal finance apps, raw bank statements are the single richest source of truth—and the biggest compliance liability.
Whenever a customer uploads a CSV or PDF bank statement, that file contains Personally Identifiable Information (PII): full names, account numbers, UPI IDs, IFSC codes, and mobile numbers. Writing these raw files to temporary disk storage (tmp/) creates unnecessary GDPR/DPDP compliance risks and security leaks.
Having spent over 9 years in retail and MSME credit underwriting, I wanted a clean, dependency-light Python pipeline that:
- Processes uploaded bank statements strictly in-memory (
io.BytesIO) with zero disk writes. - Automatically masks sensitive PII using regex patterns before any downstream analytics or LLM processing.
- Computes core underwriting and cash-flow health metrics (such as FOIR — Fixed Obligation to Income Ratio, bounce/penalty fee detection, and net monthly surplus).
Here is how you can build this standalone pipeline using only Python's standard library and pandas.
1. Zero-Disk In-Memory PII Masker (io.BytesIO)
Instead of saving uploaded bytes to disk, keep the file stream inside RAM and sanitize sensitive fields on the fly:
python
import io
import re
import pandas as pd
PII_PATTERNS = {
"ACCOUNT_NO": re.compile(r"\b\d{9,18}\b"),
"IFSC_CODE": re.compile(r"\b[A-Z]{4}0[A-Z0-9]{6}\b"),
"UPI_ID": re.compile(r"\b[\w.\-]+@[a-zA-Z]{2,}\b"),
"PHONE_IN": re.compile(r"\b(?:\+91[\-\s]?)?[6-9]\d{9}\b"),
"PAN_CARD": re.compile(r"\b[A-Z]{5}[0-9]{4}[A-Z]\b"),
}
def mask_pii_text(text: str) -> str:
if not isinstance(text, str):
return text
cleaned = text
for label, pattern in PII_PATTERNS.items():
cleaned = pattern.sub(f"[REDACTED_{label}]", cleaned)
return cleaned
def sanitize_statement_in_memory(raw_bytes: bytes) -> pd.DataFrame:
# Read strictly from RAM without touching disk
buffer = io.BytesIO(raw_bytes)
df = pd.read_csv(buffer)
for col in df.select_dtypes(include=["object"]).columns:
df[col] = df[col].apply(mask_pii_text)
return df
Why this matters: Even if logs are inspected or downstream analytics pipelines store processed DataFrames, raw account numbers, PANs, and UPI handles never leave volatile memory.
2. Automated Fee Leakage & FOIR Underwriting Check
Once the statement ledger is sanitized, you can run deterministic cash-flow checks—flagging hidden bank penalties (minimum balance charges, ECS/NACH return fees, duplicate debits) and calculating borrower repayment capacity:
Python
def analyze_cashflow_and_foir(df: pd.DataFrame, existing_monthly_emi: float) -> dict:
"""
Expects columns: ['description', 'credit', 'debit']
"""
df["credit"] = pd.to_numeric(df["credit"], errors="coerce").fillna(0.0)
df["debit"] = pd.to_numeric(df["debit"], errors="coerce").fillna(0.0)
total_inflow = df["credit"].sum()
total_outflow = df["debit"].sum()
# Flag penalty / bounce / unexpected bank fee rows
fee_keywords = r"charges|penalty|min bal|ecs return|nach return|bounce|late fee"
fee_rows = df[df["description"].str.contains(fee_keywords, case=False, na=False)]
total_fee_leakage = fee_rows["debit"].sum()
# Fixed Obligation to Income Ratio (FOIR)
foir_pct = (existing_monthly_emi / total_inflow * 100) if total_inflow > 0 else 0.0
return {
"total_inflow": round(total_inflow, 2),
"total_outflow": round(total_outflow, 2),
"net_surplus": round(total_inflow - total_outflow, 2),
"detected_fee_leakage": round(total_fee_leakage, 2),
"foir_percentage": round(foir_pct, 2),
"underwriting_flag": "HIGH_RISK" if foir_pct > 55 or total_fee_leakage > 0 else "HEALTHY",
}
In real-world retail and MSME underwriting, catching even two NACH/ECS bounce entries or a >55% FOIR early saves hours of manual statement scrutiny.
Explore 126+ Ready-to-Run Finance & Quant Scripts
I built this script as part of PyQuant Library—an open, browser-accessible collection of 126+ standalone, dependency-light Python scripts and interactive financial calculators covering:
Data Automation & FinOps: Zero-disk PII statement sanitizers, PDF table extractors, and Parquet tick-data compressors.
Credit Risk & Corporate Finance: FOIR/DSCR underwriting models, 5-Year DCF, Altman Z-Score, Beneish M-Score, and Basel III Expected Loss (PD × LGD × EAD).
Algorithmic Trading & Derivatives: Vectorized indicators, Black-Scholes Option Greeks, NSE Option Chain PCR/Max Pain scrapers, and GARCH(1,1) volatility models.
You can browse the full code, test calculators live in your browser, or download .py files directly here:
Live Library: https://pyquant-library.netlify.app
GitHub Repository: https://github.com/ayushman4you-ctrl/awesome-python-quant-scripts
Interactive Statement Diagnostic Tool (MoneyXray): https://moneyxray-frontend.vercel.app
What underwriting rules or bank statement anomalies do you automate most often in your Python pipelines? Let me know in the comments!
Top comments (0)