PorchPass Strategic Operations — Capital Markets Data Infrastructure

DQ Dashboard Engine v3.1

A 2,846-line Python ETL system that extracts, validates, snapshots, and transforms raw portfolio data from a 313K-cell Excel workbook into investor-grade delinquency analytics — with zero manual intervention.

2,846

Lines of Python

313,493

Source Cells Processed

126,199

Live Excel Formulas

Extraction Modules

Source Worksheets

413

Loans Tracked

Source System

Master Engine Workbook: RCU_Feb2026_Engine

The 35-sheet, 3.8 MB Excel workbook that serves as the single source of truth for the Rogue Credit Union portfolio. The DQ Engine reads from 9 structured input tabs auto-populated by 126,199 live formulas.

Total Worksheets

313,493

Cells with Data

126,199

Live Formulas

📊 Workbook Sheet Architecture (Top 12 by Cell Count)

Sheet Name	Purpose	Cells	Formulas	Rows x Cols
Loan Sale Data	Master loan tape — 92 fields per loan	30,142	27,862	443 x 92
Rogue	Payment transaction ledger	48,230	13,068	3,275 x 17
Rogue Data Dump	Raw servicer data import (02-15-2026)	33,501	0	409 x 88
Loan Sale Data (PRE)	Pre-transformation loan tape	28,212	19,577	440 x 84
P2 Originated Loans	Phase 2 originated, serviced loans	26,368	797	399 x 76
Trial Balance Nov2025	Trial balance reconciliation	25,196	22,742	400 x 68
Midwest Validation	Servicer validation crosscheck	17,820	17,776	405 x 44
Midwest TBAL	Midwest trial balance	19,296	0	402 x 50
Midwest	Midwest servicer data	16,872	4,067	582 x 31
PorchPass	Internal payment tracking	13,213	6,811	2,768 x 17
MAIC Analytics	Portfolio analytics & DQ calculations	~8,500	~6,200	710 x 34
+ 24 additional sheets	Config, QA, Servicing, Mapping, Calendar...	~46,643	~7,299	—

▼ DQ Engine Reads 9 Structured Tabs via openpyxl ▼

🔌 Engine Input Tabs (Extracted by dq_engine_v3_1.py)

1_KPI_Strip — 40 fields

2_DQ_Trend_Chart — 12 fields

3_DPD_Distribution — 25 fields

4_Vintage_Segmentation — dynamic

5_Tables_Narrative — 12 + 5 arrays

6_Note_Rate_Bands — 18-col matrix

7_Weighted_Avg_Stats — 6 metrics

8_Land_Type — 18-col matrix

9_Credit_Score_Tier — 19-col matrix

Core Architecture

End-to-End ETL Pipeline

Complete data flow: Raw Excel → 9 Extractors → Validation → JSON Snapshot → Template/Export/Dashboard.

Extraction Engine

9 Specialized Data Extractors

Each extractor uses hardcoded cell-coordinate maps to pull exact values from the master workbook. Every field traces back to a specific Excel cell address.

① _extract_kpi() — 40-Field KPI Strip Tab 1

Maps 40 JSON keys to exact Excel row/column coordinates. Each DQ tier captures Rate, Count, UPB, UPB Abbreviated, UPB%, and Tooltip — 6 fields each for DQ30+, DQ60+, Demanded, NTV, Repo, Performing plus metadata.

dq_engine_v3_1.pyLines 385-405
# Cell coordinate map: (json_key, excel_row) -- all read from Column D
KPI_MAP = [
    ("DQ30_Rate_Pct",6), ("DQ30_Loan_Count",7), ("DQ30_UPB",8),
    ("DQ30_UPB_Abbrev",9), ("DQ30_UPB_Pct",10), ("DQ30_Tooltip",11),
    # ... DQ60 (rows 13-18), Demanded (20-25), NTV (27-32),
    # ... Repo (34-39), Performing (41-46), Metadata (49-52)
    ("Performing_Rate_Pct",41), ("Performing_Loan_Count",42),
    ("Performing_UPB",43), ... ("Total_Loan_Count",52),
]

def _extract_kpi(ws):
    return {k: safe_val(ws.cell(row=r, column=4).value) for k,r in KPI_MAP}

def _extract_kpi_ref(ws):  # Reference column (Column C = prior period)
    return {k: safe_val(ws.cell(row=r, column=3).value) for k,r in KPI_MAP}

Live Extracted Values (2026-02 MTD FINAL):

3.87%

DQ30+ Rate

DQ30+ Loans

$1.67M

DQ30+ UPB

95.4%

Performing

③ _extract_dpd() — 25-Field DPD Distribution Tab 3

dq_engine_v3_1.pyLines 422-436
DPD_MAP = [
    ("Current_0DPD_Count",6), ("Current_0DPD_UPB",7), ("Current_0DPD_UPB_Pct",8),
    ("DPD_1_29_Count",10), ("DPD_1_29_UPB",11), ("DPD_1_29_UPB_Pct",12),
    ("DPD_30_59_Count",14), ("DPD_30_59_UPB",15), ("DPD_30_59_UPB_Pct",16),
    ("DPD_60_89_Count",18), ("DPD_60_89_UPB",19), ("DPD_60_89_UPB_Pct",20),
    ("DPD_90_119_Count",22), ("DPD_90_119_UPB",23), ("DPD_90_119_UPB_Pct",24),
    ("DPD_120_Plus_Count",26), ("DPD_120_Plus_UPB",27), ("DPD_120_Plus_UPB_Pct",28),
    ("Paid_Off_Count",30), ("Paid_Off_Pct",31),
    ("Paid_Ahead_Count",33), ("Paid_Ahead_UPB",34), ("Paid_Ahead_UPB_Pct",35),
    ("Total_Portfolio_Loans",37),
]

⑥ _extract_matrix_full() — Dynamic 18-Column Matrix Tabs 6, 8, 9

A single polymorphic extractor handles three matrix tabs (Note Rate Bands, Land Type, Credit Score Tier). Dynamically discovers column headers at row 2, then iterates all data rows capturing every cell by header name.

dq_engine_v3_1.pyLines 492-514
def _extract_matrix_full(ws):
    headers = []
    for c in range(1, ws.max_column + 1):
        v = ws.cell(row=2, column=c).value
        if v: headers.append(str(v))
        else: break
    rows = []
    for r in range(3, ws.max_row + 1):
        a = ws.cell(row=r, column=1).value
        if a is None: continue
        row_data = {"_label": safe_val(a)}
        for ci, hdr in enumerate(headers):
            row_data[hdr] = safe_val(ws.cell(row=r, column=ci+1).value)
        if str(a).upper() == "TOTAL": row_data["_is_total"] = True
        rows.append(row_data)
    return {"_title": safe_val(ws.cell(1,1).value), "_headers": headers, "rows": rows}

④ _extract_vintage() — Dynamic Label Parsing Tab 4

dq_engine_v3_1.pyLines 439-466
def _extract_vintage(ws):
    lc, dq = {}, {}
    for row in range(5, ws.max_row + 1):
        b = ws.cell(row=row, column=2).value
        s = str(b).strip()
        if s.startswith("Loan Count"):
            label = re.sub(r'^Loan Count\s*[-\u2014]+\s*', '', s).strip()
            lc[label] = safe_val(d)
        elif s.startswith("DQ30+"):
            label = re.sub(r'^DQ30\+\s*Rate\s*[-\u2014]+\s*', '', s).strip()
            dq[label] = safe_val(d)
    return {"loan_counts": lc, "dq30_rates": dq}

VINTAGE_ORDER = ["Nov 24","Dec 24","Jan 25",...,"Jun 26"] # Chronological sort

Data Quality

Cross-Tab Validation Engine

Every snapshot runs through 12 automated QC checks before persistence. Cross-references data between tabs to catch inconsistencies that would undermine investor confidence.

🔎 validate_snapshot() — 12 QC Checks

dq_engine_v3_1.pyLines 1212-1293
# CHECK 1: Tab Presence (9/9 expected)
tabs_present = [t for t in ALL_EXPECTED_TABS
                if t in period_data and period_data[t]]

# CHECK 4-9: Rate sanity (must be 0.0 - 1.0)
for key in ["DQ30_Rate_Pct", "DQ60_Rate_Pct",
            "Performing_Rate_Pct", ...]:
    ok = 0 <= val <= 1.0

# CHECK 10: Cross-tab consistency
# Tab1.DQ30_Count == Tab3(30-59 + 60-89 + 90-119 + 120+)
dpd_sum = dpd_30_59 + dpd_60_89 + dpd_90_119 + dpd_120
match = (dq30_kpi == dpd_sum)

# CHECK 12: Null field scan
for key in critical_fields:
    if kpi.get(key) is None:
        null_fields.append(f"1_KPI_Strip.{key}")

📄 Live Validation Report (2026-02_MTD_W4)

VALIDATION_2026-02_MTD_W4.txt

VALIDATION REPORT: 2026-02_MTD_W4 Generated: 2026-02-22 07:50:47 Result: 11 passed, 1 failed ============================================================ [OK] Tabs present: 9/9 tabs [OK] Total Loan Count exists: Value: 413 [OK] DQ30+ Count exists: Value: 16 [OK] DQ30_Rate_Pct in range: 0.0387 [OK] DQ60_Rate_Pct in range: 0.0339 [OK] Performing_Rate_Pct in range: 0.9540 [OK] Demanded_Rate_Pct in range: 0.0121 [OK] NTV_Rate_Pct in range: 0.0218 [OK] Repo_Rate_Pct in range: 0.0073 [!!] Cross-tab: Tab1 DQ30+ Count vs Tab3 bucket sum: KPI=16, Tab3 sum=19 MISMATCH [OK] Cross-tab: Tab1 Total vs Tab3 Total: KPI=413, Tab3=413 [OK] Null field scan: No critical nulls detected FAILED -- 11/12 checks

🎯 RAG Threshold Intelligence

dq_engine_v3_1.pyLines 1388-1399
RAG_THRESHOLDS = {
    "DQ30_Rate_Pct":       ("lower_is_better",  0.05, 0.10),   # <5% green, >10% red
    "DQ60_Rate_Pct":       ("lower_is_better",  0.03, 0.07),   # <3% green, >7% red
    "Performing_Rate_Pct": ("higher_is_better", 0.90, 0.80),   # >90% green, <80% red
    "DQ30_Loan_Count":     ("lower_is_better",  20,   40),
    "DQ60_Loan_Count":     ("lower_is_better",  10,   25),
    "Demanded_Loan_Count": ("lower_is_better",  5,    15),
    "NTV_Loan_Count":      ("lower_is_better",  3,    10),
    "Repo_Loan_Count":     ("lower_is_better",  2,    8),
}

3.87%

DQ30+ -- GREEN (<5%)

3.39%

DQ60+ -- AMBER (3-7%)

95.4%

Performing -- GREEN (>90%)

DQ30 Count -- GREEN (<20)

Persistence Layer

JSON Ledger Database

Central datastore holding all historical snapshots with auto-rotating backups (last 10 copies retained before every write).

🗃 Ledger Structure (Production Data)

dq_ledger.json51,129 bytes
{
  "metadata": {
    "version": "3.1",
    "period_count": 3,
    "periods_available": ["2026-01_EOM", "2026-02_MTD", "2026-02_MTD_W3"]
  },
  "periods": {
    "2026-02_MTD": {
      "_status": "FINAL",
      "source_file": "RCU_Feb2026_Engine - v1.xlsx",
      "1_KPI_Strip": {
        "DQ30_Rate_Pct": 0.0387409200968523,
        "DQ30_Loan_Count": 16,
        "DQ30_UPB": 1666547.59,
        "Performing_Rate_Pct": 0.9539951573849879,
        "Total_Loan_Count": 413
        // ... 35 more KPI fields
      },
      "3_DPD_Distribution": {
        "Current_0DPD_Count": 352,
        "Current_0DPD_UPB": 45255316.51
        // ... 22 more DPD fields
      },
      "7_Weighted_Avg_Portfolio_Stats": {
        "WAC": 0.0986, "Average_FICO": 618,
        "Average_LTV": 0.847
      }
    }
  }
}

🔄 Auto-Backup & Snapshot Diff

dq_engine_v3_1.pyLines 181-196
def save_ledger(ledger):
    # Auto-backup before every write
    if os.path.exists(LEDGER_PATH):
        ts = datetime.now().strftime("%Y%m%d_%H%M%S")
        shutil.copy2(LEDGER_PATH,
            f"_backups/ledger_backup_{ts}.json")
        # Rotate: keep only last 10
        bkps = sorted(glob.glob("ledger_backup_*.json"))
        for old in bkps[:-10]: os.remove(old)

DIFF_2026-02_MTD_20260221_220534.txt

SNAPSHOT DIFF: 2026-02_MTD (old) -> (new) ============================================================ DQ30+ Rate................. 0.0411 >>> 0.0387 DQ30+ Count................ 17 >>> 16 DQ30+ UPB.................. $1,734,201 >>> $1,666,548 DQ60+ Rate................. 0.0339 (no change) Performing Rate............ 0.9540 (no change) Total Loans................ 413 (no change) Total changes: 3

Output Generation

Pixel-Perfect Template Builder

574 lines generating N-column Excel templates (2-12 periods) with exact cell styling, number formats, color-coded input cells, and dynamic note generation.

📄 build_template() — 574 Lines of Formatting Logic

dq_engine_v3_1.pyLines 633-1207
# Number format constants
FMT_PCT  = '0.00%'         # DQ rates
FMT_UPB  = '"$"#,##0'      # Dollar amounts
FMT_DATE = 'd-mmm-yy'     # Report dates

# 18-column matrix header format map
HEADER_FORMATS = {
    "Loan Count":   None,
    "% of Loans":   '#0.0%;\\-#.0%;\\-',
    "UPB ($)":      '\\$#,###;"-$"#,###;\\-',
    "WAC Rate":     '#0.000%;\\-#0.000%;\\-',
    "%Del30":       '#0.00%;[Red]\\-#0.00%;\\-',
    "%Del60":       '#0.00%;[Red]\\-#0.00%;\\-',
    // ... 12 more header-specific formats
}

# Color scheme (matching original manual template)
YELLOW_INPUT  = PatternFill("FFFF00")  # User input cells
BLUE_REF      = PatternFill("DCE6F1")  # Reference data
DARK_NAVY_HDR = PatternFill("1E293B")  # Section headers

State Machine

Snapshot Lifecycle & Readiness Scoring

🔄 DRAFT → FINAL → ARCHIVED

DRAFT

Quick-look, freely overwritten

→

FINAL

Board-ready, requires "OVERWRITE"

→

ARCHIVED

MTD superseded by EOM

Weekly sub-periods (W1-W4) for intra-month tracking. MTD→EOM promotion auto-archives all MTD variants with hard copies in 6_archived_periods/.

📈 Readiness Scoring Algorithm

dq_engine_v3_1.pyLines 2592-2623
overall = (
    tab_score    * 0.30 +  # 30% tab completeness
    val_score    * 0.35 +  # 35% validation pass rate
    status_score * 0.15 +  # 15% DRAFT vs FINAL
    null_score   * 0.20    # 20% null field penalty
)
if overall >= 90: "READY for investor consumption"
elif overall >= 70: "MOSTLY READY"
else: "NOT READY"

90%

Feb 2026 MTD — Readiness Assessment

Tab Completeness

9/9

Validation Checks

11/12

Status

FINAL

Critical Nulls

Historical Analytics

7-Sheet Historical Export Engine

Generates a comprehensive Excel workbook with 7 analytical sheets featuring MoM delta calculations, RAG status coloring, and trend arrows.

📊 export_ledger_excel() Sheet Architecture

#	Sheet	Content	Features
1	Executive Summary	19 headline metrics x N periods	RAG (G/A/R) + MoM + trend arrows
2	KPI History	All 40 KPI fields x N periods	$, %, integer formatting
3	DPD History	25 DPD bucket fields x N periods	UPB + Pct formats
4	Portfolio Stats	WAC, WALA, WARM, FICO, LTV, DTI	Metric-specific formats
5	Vintage Analysis	Loan counts + DQ rates by vintage	Chronological sorting
6	DQ Trend	DQ30/60/120+ rate tracking	Multi-period comparison
7	Narratives	Watch Items, Strengths, Exec Summary	Wrap text, section-grouped

dq_engine_v3_1.pyMoM Change Engine (Lines 1441-1463)
def _calc_mom_change(old_val, new_val, fmt_type):
    delta = n - o
    if fmt_type == FMT_PCT:
        pp = delta * 100
        return (f"{arrow}{abs(pp):.2f}pp", delta < 0)  # lower = improvement
    elif fmt_type == FMT_UPB:
        return (f"{arrow}${abs(delta):,.0f}", delta < 0)

def _get_trend_arrow(old_val, new_val, lower_is_better):
    if lower_is_better:
        if n < o: return ("↓", True)   # down = improvement
    else:
        if n > o: return ("↑", True)   # up = improvement

Live Production Output

Engine Terminal Captures

$ python3 dq_engine_v3_1.py

╔═══════════════════════════════════════════════╗ ║ DQ DASHBOARD ENGINE v3.1 -- PorchPass ║ ║ Rogue Portfolio -- Strategic Operations ║ ╠═══════════════════════════════════════════════╜ ║ [1] 📸 Take a New Snapshot ║ ║ [2] 📄 Generate Data Template ║ ║ [3] 📊 View Snapshot Status Dashboard ║ ║ [4] 📤 Export Full History to Excel ║ ║ [5] 🗑 Delete a Snapshot ║ ║ [6] 🔄 Update/Re-snapshot Existing Period ║ ║ [7] 📥 Import Narratives ║ ║ [8] 📋 Monthly Digest ║ ║ [9] 📖 Help & Reference Guide ║ ║ [0] 🚪 Exit ║ ╚═══════════════════════════════════════════════╝ 📚 Ledger: 3 periods | Latest: Feb 2026 MTD_W3

Option [3] -- Status Dashboard

============================================================ 📊 SNAPSHOT STATUS DASHBOARD ============================================================ -- 2026 -- [ 1] 2026-01_EOM -- Jan 2026 EOM [FINAL] [ 2] 2026-02_MTD -- Feb 2026 MTD [FINAL] [ 3] 2026-02_MTD_W3 -- Feb 2026 MTD W3 [DRAFT] Snapshot files on disk: 5 Generated templates: 2 📄 Rogue_DQ_Dashboard_2026-02_MTD_Input_Template.xlsx 📄 Rogue_DQ_Dashboard_2026-02_MTD_W3_Input_Template.xlsx ✅ [ISSUES] No outstanding data quality issues.

End-to-End Integration

Complete Pipeline: Engine to Board-Ready Dashboard

🔗 System Interconnection Map

RCU_Feb2026_Engine.xlsx

35 sheets | 313K cells | 126K formulas

-->

dq_engine_v3_1.py

2,846 lines | 9 extractors

-->

dq_ledger.json

Validated snapshots | 51KB

-->

DQ Dashboard HTML

Board-ready | Chart.js

📈 Live Portfolio KPIs (Feb 2026 MTD)

413

Total Loans

$51.8M

Total UPB

3.87%

DQ30+ Rate

3.39%

DQ60+ Rate

394

Performing (95.4%)

9.86%

WAC

618

Avg FICO

84.7%

Avg LTV

Technical Specifications

System Architecture Summary

⚙ Engine Specifications

Engine Version	v3.1 (Gap Closure)
Lines of Code	2,846
Language	Python 3.x + openpyxl
Extraction Modules	9
Validation Checks	12
Menu Operations	9
Template Builder	574 lines, 11 output tabs
Export Engine	7 analytical sheets
Audit Gaps Resolved	14/14

📁 6-Folder Architecture

DQ_Dashboard_Engine/Directory
DQ_Dashboard_Engine/
|-- dq_engine_v3_1.py       # 2,846 lines
|-- dq_ledger.json          # 51KB JSON DB
|-- known_issues.json       # Issue tracker
|-- 1_engine_input/         # Drop .xlsx here
|-- 2_snapshots/            # Year/month org
|   |-- 2026/01.Jan/ 02.Feb/
|   |-- _backups/           # Last 10 copies
|-- 3_templates_for_html/   # .xlsx templates
|-- 4_history_export/       # 7-sheet exports
|-- 5_change_log/           # Diffs, validation
|-- 6_archived_periods/     # Permanent MTD copies

🛠 v3.0 → v3.1: All 14 Audit Gaps Resolved

E1 Vintage + DQ Trend sheets

E2 MoM Change Calculations

E3 RAG Status + Trend Arrows

F1 Validation Reports

F2 Issue Tracker + Auto-Clear

F3 Graceful Tab Handling

H1 Multi-Period Smart Sort

H2 MTD→EOM Promotion

I4 Monthly Digest + Readiness

C3 Snapshot Diff on Overwrite

G2 Colorama Terminal Styling

C1 DRAFT/FINAL Tagging

C2 Weekly Sub-Periods (W1-W4)

P4 Year-Grouped Navigation