DEV Community

ayushman4you-ctrl
ayushman4you-ctrl

Posted on

How to Build a Privacy-First Bank Statement PII Masker & Cash-Flow Analyzer in Python (Zero-Disk RAM Processing)

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:

  1. Processes uploaded bank statements strictly in-memory (io.BytesIO) with zero disk writes.
  2. Automatically masks sensitive PII using regex patterns before any downstream analytics or LLM processing.
  3. 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!
Enter fullscreen mode Exit fullscreen mode

Top comments (0)