In a large NBFC, a Regional Business Head or Zonal Head manages thousands of loan accounts across dozens of branches and field executives. Every morning, the same questions come up:
"Which accounts should my team prioritize today?" "Which executive is underperforming and why?" "How many accounts from last year's advances have gone delinquent?" "Show me customers who haven't paid in 3 months in the Western region."
Getting answers meant raising a request to an analyst, waiting for a report, and then asking a follow-up. The cycle repeated for every business question, every day.
Leaders were dependent on coordinators and analysts for information that should have been at their fingertips.
CollectionIQ is a self-serve portfolio intelligence platform built for collection leaders. Upload your monthly LCC Excel extract and the entire portfolio becomes queryable, visual, and explainable, without writing a single formula or waiting for a report.
It answers questions in plain English, surfaces risks automatically, and generates board-ready analysis on demand.
Live Link : collectioniq.streamlit.app
No data? No setup? Click Fill Sample Data on the landing page. It fetches a real LCC extract directly from this repository and builds the full dashboard instantly. No file upload, no API key, no configuration needed.
Landing Page - Upload regional files or load sample data instantly
Dashboard - KPIs and portfolio health with Month-on-Month movement
Portfolio Analysis - DPD bucket distribution, branch collection %, arrears exposure
|
Executive Scorecard - Every field executive ranked by collection %, strike rate, NPA count and roll rates
|
Smart Alerts - 6 automatic risk flags with SOH exposure and recommended actions
|
|
Bucket Migration - Roll-forward / roll-backward rates and the prev-month → curr-month migration matrix
|
Monthly Portfolio Intelligence Report - board-ready HTML report with narrative, rankings and action plan
|
AI Query Assistant - Plain English queries powered by Gemini 2.5 Flash-Lite + LangGraph
|
AI Query summary - top regions, branches, executives and bucket distribution for the matched accounts
|
AI Queried table Observations - domain-aware narrative generated for every query result
|
| Role | How They Use It |
|---|---|
| Regional Business Head | Monitor region-wide collection efficiency, bucket migration, and executive rankings |
| Zonal Head | Compare branch performance, identify concentration risk, track NPA formation |
| Business Unit Head | Query specific customer segments, get prioritized action lists, analyse new advances |
Plain English Query Engine : Ask any question in NBFC language. Get back a filtered loan table, a ranked executive comparison, or a single stat answer. Multi-step questions (for example "customers per branch with more than 3 loans" or "fleet owners who have not paid this month") are answered by a composable step-plan engine that chains group-by, conditional counts, derive, sort and limit to any depth. When a question is genuinely ambiguous, the agent asks a short clarifying question with 3 to 5 options instead of guessing. Every result includes AI observations and an Excel download.
Multi-Region File Support : Upload multiple regional LCC files at once for a unified view. Supports .xlsx, .xls, and .xlsb with automatic deduplication and datetime normalisation across files.
Robust Column Normalisation : A four-pass pipeline handles truncated headers, trailing spaces, and capitalisation differences. Verified at 100% accuracy across all 85 expected columns. Multi-sheet workbooks auto-detect the LCC sheet.
Automated Priority Action List : Seven-tier business priority framework ranks accounts by impact. Non-starters, easy settlements, insurance arrears, co-lending risk, and NPA accounts each get their own actionable tier.
Field Executive Performance Scorecard : Every executive ranked by collection %, strike rate, NPA count, SMA-2 count, and bucket roll rates using quartile-based tiers relative to the current portfolio. Toggle the ranking metric between Collection % and Strike % to see who's actually current on installment obligation this month, not just who collected the most.
Bucket Migration Analysis : Two months of data reveal exactly how accounts moved between DPD buckets, the NPA formation rate, and which executives are improving or deteriorating.
6 Smart Risk Alerts : Pure pandas, no LLM, always accurate:
| Alert | Severity | What It Catches |
|---|---|---|
| Non Starters | Critical | Never paid 1st EMI |
| Co-lending at Risk | Critical | Partner bank exposure showing delinquency |
| High Arrears - Loan at Risk | Critical | Inst+Exp+BC arrears exceed 50% of original loan |
| Insurance-Driven Delinquency | High | EMI paid but insurance charge causing false delinquency |
| Recent Advances at Risk | High | Loans under 12 months already delinquent |
| Easy Settlements | Medium | Closing arrears under ₹1,000 |
SOH as the True Exposure Metric : Uses Sum of Hire (POS + Closing Arrears) instead of POS alone. For MAT and S&S accounts where POS = 0, SOH correctly reflects what is actually owed.
Monthly Portfolio Intelligence Report : Board-ready, fully self-contained HTML report built from up to 18 independently toggleable sections, verdict-first so a lead reads the AI narrative and five-point action plan before scrolling into supporting detail. Beyond branch and executive league tables, it includes embedded charts (bucket-distribution waterfall, region→branch concentration treemap, Collection% vs NPA% branch quadrant), month-over-month NPA/SMA-2 movement broken out by region/branch/executive, early-warning risk indicators, segment-wise NPA breakdown, top at-risk accounts, fleet exposure, repossession candidates, a good-customer retention list, and a rescued-vs-slipped executive recovery leaderboard, every number pulled from the same analysis/ functions the dashboard tabs use, so a report figure and a dashboard figure never disagree. Email-safe layout. Generate once, send to any number of recipients without regenerating.
Before CollectionIQ, every portfolio question meant raising a request, waiting for an analyst, and asking a follow-up. Each loop took hours to a day.
With CollectionIQ, the same question is answered in under 30 seconds by the leader directly.
| Before | After |
|---|---|
| Analyst dependency for every query | Self-serve in plain English |
| Manual Excel work for breakdowns | Instant filtered results with AI observations |
| Subjective account prioritisation | Seven-tier data-driven priority framework |
| Month-end reports for portfolio health | Real-time bucket migration and alerts on every upload |
CollectionIQ runs two independent AI pipelines orchestrated with LangGraph, one for answering queries in real time, one for generating the monthly portfolio report.
Every question typed in plain English flows through a LangGraph state machine built on a "plan, then compile" design. A Logical Planner agent never writes execution logic itself, it emits a declarative intent (which filters, which metrics, which dimensions) chosen from a fixed registry vocabulary, and a deterministic pandas compiler turns that into an executable step-plan. A clear query goes through the fast-path view lookup or the compiler; an ambiguous query gets a clarifying question first.
flowchart TD
User(["Plain English Query\ne.g. customers per branch with more than 3 loans"])
User --> LP
subgraph QP [" AI Query Pipeline (LangGraph) "]
direction TB
LP["Logical Planner Agent\nGemini 2.5 Flash-Lite\n\nReads the registry vocabulary: concepts, metrics,\nentities, dimensions, pre-built views\nEmits a declarative intent, never raw execution code\nAsks to clarify when materially ambiguous"]
CL["Clarify\n\nAmbiguous query becomes a question plus\n3 to 5 options, user picks, query re-runs"]
VW["Fast-Path View\nPandas, no LLM\n\nServes a pre-computed analysis/ result when the\nplanner matched one, reusing the dashboard's own cache\nFalls through to the compiler on any failure"]
CV["Compiler and Validator\nDeterministic, one LLM repair on failure\n\nLowers the intent into an ordered pandas step-plan\nChecks every column and name against the real schema"]
EX["Data Executor\nPandas\n\nStep-plan engine, aggregation, priority framework\nComputes KPIs and rankings"]
IG["Insight Generator Agent\nGemini 2.5 Flash-Lite\n\nReads computed KPIs, rankings and rows\nGenerates domain-aware observations"]
LP -->|ambiguous| CL
LP -->|view matched| VW --> IG
LP -->|else| CV --> EX --> IG
VW -.falls through on failure.-> CV
end
CL --> RQ["Clarifying question\nuser answers, query re-runs"]
IG --> R1["Loan Table\nFiltered customer records"]
IG --> R2["Ranked / Aggregated Table\nOne row per group, including nested plans"]
IG --> R3["Single Stat\nDirect answer with supporting context"]
The step-plan engine (agents/plan_executor.py) exists because a single GROUP BY counts rows per group and cannot express nested analytics. A plan is an ordered list of steps (group_aggregate with an optional conditional where, filter, derive, sort, limit); each step transforms the previous step's table, so arbitrary depth composes without special-casing. Only whitelisted operations run, never arbitrary code, and every derive expression is checked against a disallowed-pattern list before it reaches pandas.
The vocabulary the Logical Planner picks from lives in registry/: ontology.py defines named filter concepts (for example easy settlement, co-lending at risk) and named metrics (including registered percentage metrics like strike rate and hard bucket percentage), semantic_model.py defines the grain entities (loan, customer, executive, branch, region) and group-by dimensions, and views.py defines the pre-built fast-path views (executive scorecard, roll-rate matrix, top delinquent accounts, and more) that let a common question skip the general compiler entirely and return an answer that is numerically identical to what the dashboard tabs already show.
Triggered on demand. Runs fully autonomously - no user input needed after clicking Generate.
flowchart TD
Trigger(["Generate Monthly Report"])
Trigger --> PA
subgraph RP [" Report Pipeline (LangGraph) "]
direction TB
PA["Portfolio Analyzer\nPandas\n\nComputes up to 18 toggleable report sections\nHealth · Verdict · Risk signals · Bucket & NPA movement\nEmbedded charts · Region/segment breakdowns\nAccount lists · Branch & executive leaderboards"]
RN["Risk Narrator\nGemini 2.5 Flash-Lite\n\nWrites 6-8 bullet-point executive narrative\nGenerates 5 prioritized action items with owner and timeline"]
RB["Report Builder\nPython\n\nAssembles fully self-contained HTML report\nTable-based layout · Email-safe · No external CSS"]
ED["Email Dispatcher\nSMTP\n\nSends report as body and attachment\nFires only if SMTP is configured in .env"]
PA --> RN --> RB --> ED
end
RB --> DL["Download HTML Report"]
ED --> EM["Email to Configured Recipients"]
Both pipelines operate on the same in-memory DataFrame loaded from the Excel upload. No database, no cloud storage. Data never leaves the machine.
flowchart LR
XL["LCC Excel File\n.xlsx / .xls / .xlsb\nSingle or multiple regional files"] --> VAL["Validation\nSchema check · Column normalisation\nMulti-sheet detection · Date parsing"]
VAL --> BK["Bucketing\nDPD bucket assignment\nSTD · 1-30 · SMA-1 · SMA-2 · NPA\nSOH = POS + Closing Arrears"]
BK --> KPI["KPI Computation\nCollection % · SOH · Arrears · MoM delta"]
KPI --> DF[("In-Memory\nDataFrame")]
DF --> QP2["Query Pipeline"]
DF --> RP2["Report Pipeline"]
DF --> DB["Dashboard\nKPIs · Charts · Alerts · Scorecard"]
CollectionIQ/
├── app.py # Main Streamlit app, all UI layout and state
├── graph.py # AI query pipeline (LangGraph state machine)
├── utils.py # Data loading, column normalisation, metrics, charts
├── smart_alerts.py # 6 rule-based risk alerts (pure pandas, no LLM)
├── config.py # Model name, thresholds, and other tuned constants
│
├── agents/
│ ├── logical_planner.py # Query to declarative intent (IR-1), the live planner
│ ├── data_executor.py # Priority framework, KPI/ranking computation
│ ├── plan_executor.py # Composable step-plan engine + plan validator
│ ├── insight_generator.py # AI observations on query results
│ └── domain_expert.py # Priority framework text and snapshot-date context
│
├── compiler/
│ ├── core.py # Lowers a declarative intent into a pandas step-plan
│ └── measures.py # Measure kinds: additive, ratio, count_ratio, and more
│
├── registry/
│ ├── ontology.py # Named filter concepts, named metrics, priority rules
│ ├── semantic_model.py # Grain entities and group-by dimension aliases
│ └── views.py # Fast-path pre-built view catalog
│
├── analysis/
│ ├── portfolio_intelligence.py # Pulse KPIs, top accounts, fleet, risk indicators, and more
│ ├── executive_scorecard.py # Per-executive KPIs with quartile tier ranking
│ └── roll_rate.py # Bucket migration matrix and roll-rate KPIs
│
├── ui/
│ ├── tabs/ # One module per dashboard tab, including ai_query.py
│ ├── components.py # Shared KPI cards, download buttons, safe table rendering
│ └── landing.py # Upload page and sample-data loader
│
├── report_agent/
│ ├── graph.py # Report pipeline (LangGraph)
│ ├── charts.py # Plotly figure -> embedded base64 PNG (kaleido)
│ ├── sections/ # 18 independently toggleable report sections,
│ │ # each a thin wrapper around an analysis/ function
│ └── nodes/
│ ├── portfolio_analyzer.py # Dispatches enabled_sections to sections/
│ ├── risk_narrator.py # AI executive narrative and action plan
│ ├── report_builder.py # Assembles the verdict-first, email-safe HTML report
│ └── email_dispatcher.py # SMTP delivery
│
├── sample_data/
│ ├── Current_Month_Demo.xlsx # Sample LCC extract, current month
│ └── Previous_Month_Demo.xlsx # Sample LCC extract, previous month
│
└── requirements.txt
| Layer | Technology | Role |
|---|---|---|
| UI and Dashboard | Streamlit | Interactive web interface, session state, multi-file upload |
| AI Models | Google Gemini 2.5 Flash-Lite | All LLM agents across both pipelines (query and report) |
| Agent Orchestration | LangGraph | Stateful multi-agent graph with conditional routing, fast-path views, and clarification |
| Data Processing | Pandas | Filtering, aggregation, bucketing, KPI computation |
| Charts | Plotly + Kaleido | Interactive dashboard charts; Kaleido renders three of them to embedded PNG for the HTML report |
| AI SDK | google-genai | Gemini API with retry and exponential backoff |
| Report Delivery | Python smtplib | SMTP email with HTML body and attachment |
| Observability | LangSmith | Query tracing and result quality feedback |
| Excel Formats | openpyxl · xlrd · pyxlsb | Handles .xlsx, .xls, and .xlsb with serial-date correction |
| Date Handling | python-dateutil | Relative date resolution for time-based queries |
The domain knowledge layer: NBFC terminology, loan status values, strike rate definition, SOH calculation, priority framework, insurance delinquency logic - is embedded in the agent system prompts and verified against real portfolio data. The AI understands the difference between a RUN account, a MAT account, and an S&S account without any fine-tuning. Business context is injected at query time, making it straightforward to extend with new domain rules.
Correctness is enforced outside the model, not by model depth. The LLM only translates a question into a declarative intent picked from a fixed registry vocabulary; a deterministic compiler turns that into a pandas step-plan, and pure pandas computes every number. A validator checks the plan against the actual columns and gives the planner one repair attempt with the exact error text before giving up with a clear message. When a question is materially ambiguous, the agent asks a clarifying question instead of assuming. The aim is general, composable reasoning rather than a hardcoded answer per question.
Both pipelines are stateless between runs. Each query or report generation starts fresh, no stale context, no memory leak, no shared state between users.