How to automate a mortgage pipeline report and pull-through forecast

Build the daily mortgage pipeline report from time-stamped loan and lock snapshots, then apply an approved pull-through model to the eligible locked balance. Publish the forecast alongside actual funding, current positions, and exceptions. The capital markets desk can then inspect why an exposure moved and decide what action to take.

Pull-through is the share of an eligible cohort expected to fund within a defined outcome window. The cohort, cutoff, and window have to travel with the number. Reusing a pull-through percentage across products or channels without checking the underlying mix can change the forecast even when the total locked principal looks stable.

Map the reporting and forecast workflow

What to automate

Capture the loan and lock pipeline at a fixed cutoff

Schedule the report after the loan origination and pricing systems publish their snapshots. Save the original files and manifest, then align events to the desk’s cutoff using source effective times. Store the ingestion time separately so late-arriving changes can be identified. Compare the received file with its expected timestamp and row count. Hold publication when a feed is stale or absent and allow an empty file only when the source confirms the zero population.

Resolve lock versions and explain daily changes

Group lock records by the desk’s durable loan and lock IDs, preserving event IDs and revisions. Use the approved priority rules to select one active lock and distinguish a relock, cancellation, date move, or missing record. Reconcile original and current principal by product, channel, status, and expected funding date. Give the lock desk an unresolved queue for contradictory versions. Publish a daily bridge whose change categories add back to the movement in reportable principal.

Project expected funding with approved pull-through assumptions

Join each eligible lock to the versioned rate table using its approved cohort, cutoff, and outcome window. Apply the documented fallback when the cohort is sparse and attach the fallback reason to the row. Multiply eligible principal by the assigned probability and aggregate by the expected funding date. Quarantine rows without a valid date or rate and show their principal separately. Compare later outcomes with the original eligible snapshot to measure calibration by cohort.

Prepare a review of exposure and confirmed positions

Load the approved exposure view and trade confirmations on compatible product, time, and risk bases before calculating differences. Keep principal and any position sensitivity units in separate columns. Route stale positions, unmatched confirmations, inconsistent units, and threshold breaches to the hedge desk with source references. Give the desk a record of which exceptions it acknowledged. Execution and position changes remain with the authorized desk.

Publish a signed report and backtest against actuals

Publish the accepted version with the cutoff, input files, model version, signed reviewer, and open exceptions. Retain the prior accepted report so Treasury and Finance can explain movement after a rerun or late revision. When the outcome window closes, match the originally eligible loan IDs to funding and disposition events and log missing outcomes separately. Archive the forecast, actuals, and difference on the same measurement basis for desk review.

Implement the loan-level model

Start with one row per loan and lock version, keyed by legal entity, loan ID, and lock ID. Keep the effective timestamp separate from the arrival timestamp. At each run's cutoff, select the latest eligible version that was actually available to the desk. Preserve earlier versions so a late correction does not rewrite the history of a forecast.

Add the lock amount, status, approved product and channel grouping, expected fund date, outcome window, and model version. Use the lender's definitions for fallout, extensions, and relocks. Keep ineligible or unresolved records in a separate exception set. Reconcile counts and principal from input to accepted plus excluded before producing any aggregates.

For a simple baseline, estimate the approved cohort rate from matured historical locks and calculate each loan's expected funded principal as its eligible principal multiplied by that rate. Aggregate by expected fund date or delivery period. Track probabilities and dates separately so a shift to next week is distinguishable from fallout. A desk can compare a more complex model with that baseline on the same historical snapshots and record whether calibration improves.

Walk through a synthetic report

If a subsequent event cancels A200, retain the original forecast and reduce the next run's expected amount by $130,000 under the synthetic eligibility rule. If it only moves A200 to a later week, keep the $130,000 expected amount and move it to the new date. Test both paths. Also inject an outdated lock export, an older version arriving late, and a relock with a reused loan ID. The report should hold on a stale critical feed and explain how each version was resolved.

The $370,000 figure is expected principal. Hedge decisions depend on instrument, pricing and risk exposure, current positions, commitments, and desk policy. Keep the desk's approval, trade placement, and independent reconciliation in their established systems. An assistant can draft explanations of changes from governed drill-down rows and propose items for review.

Validate against the desk's current report

Replay a sample of normal and volatile days using information available at each original cutoff. Compare population and principal, forecast error by funding window, publication time, active review minutes, and the number of exceptions that needed correction. Compare like-for-like coverage. Treat an unavailable or rejected report as a recorded outcome.

Fannie Mae documents mandatory and best-efforts whole loan commitments, including how the execution terms differ. Its commitment pipeline documentation describes actions available for different states. Those details illustrate why the report needs a commitment type and status separate from the lender's internal lock and forecast. Fannie Mae whole loan execution · Fannie Mae commitment pipeline

Build the forecast and daily reconciliation jobs

1. Collect loan and lock changes separately. Materialize loan_event(loan_id, version, occurred_at, ingested_at, state, principal_cents, expected_funding_date) and lock_event(lock_id, loan_id, version, occurred_at, ingested_at, status). Keep the source sequence and any relock predecessor. The source-of-truth lock for one eligible loan is the desk-approved current relationship at the report cutoff. Never join all locks to one current loan row.
2. Freeze the input manifest before running. Give each source a freshness threshold and completeness expectation. A late file can amend a later run and must leave the original published result reproducible. Build an explicit unknown bucket for unclassified status and route missing or competing active locks to the lock desk.
3. Store model rates with their training cutoff. Use completed funding outcomes only when they would have been available by the model cutoff. Retain the denominator, cohort dimensions, fallback reason and version. Exclude or separately classify open observations under a documented maturity rule. Avoid using a later-funded flag in historical feature input.
4. Publish one row per eligible loan per run. Store principal, rate, expected funded amount, expected week, cutoff, run ID and model version. Separately compute the desk’s hedge-exposure measures on an agreed basis. An aggregate cash forecast cannot be used as a hedge order.
5. Bridge to the previous run. Compare by loan key. Classify newly eligible, cancelled, principal changed, probability changed, expected date moved and unexplained changes. Reconcile the bridge to the exact total delta and group timing-only moves by both affected weeks. Keep an unmatched confirmations queue separate from the forecast.

Reproduce an as-of forecast without future leakage

Run in Python 3. In this fixture the source event becomes visible at the supplied timestamp. In production apply both source-effective and ingestion cutoffs and validate that versions form a consistent history.

from decimal import Decimal

events = [
    ("A100", 1, "09:00", "locked", 300_000, "0.80", "week-1"),
    ("A200", 1, "09:01", "locked", 200_000, "0.65", "week-1"),
    ("A200", 2, "11:00", "cancelled", 200_000, "0.65", "week-1"),
]

def forecast(cutoff):
    current = {}
    for event in events:
        loan_id, version, visible_at, *_ = event
        prior = current.get(loan_id)
        if visible_at <= cutoff and (prior is None or prior[1] < version):
            current[loan_id] = event
    result = {}
    for _, _, _, state, principal, rate, week in current.values():
        if state == "locked":
            result[week] = result.get(week, Decimal(0)) + principal * Decimal(rate)
    return result

assert forecast("10:00") == {"week-1": Decimal("370000")}
assert forecast("12:00") == {"week-1": Decimal("240000")}

For parallel runs, compare active loan count, principal, eligible lock count and expected amount independently before checking totals by week. Score forecast error only against matured actuals and keep a zero-volume cohort distinct from a failed ingest.

Implement the work step by step

Build a dated, desk-approved view. Separate snapshot and lock history, model revisions, exposure units, and confirmed position records. Trade selection and execution stay in the existing authorized workflow.

CM-01 · Produce pipeline reports

• CM-1.1.1 Confirm scheduled loan snapshot arrived. Read the entitled LOS reporting API, replica view or managed file, then register cutoff, provenance and pagination status. Read Read-only LOS connection or approved workbook export, snapshot job status. Record Time-stamped, immutable loan snapshot with source manifest. Owner Review delivery exceptions in the common queue. Check Replay a known multi-page snapshot and prove the approved file counts and cutoff match.
• CM-1.1.2 Confirm companion feeds use the same cutoff. Align registered file versions and effective times, and gate stale or incomplete companion data. Read PPE lock export or API, LOS cutoff, approved reporting calendar. Record Feed readiness and as-of status per report run. Owner Investigate mismatches routed as data exceptions. Check Hold publication on a simulated stale PPE file or missing run and distinguish late from empty.
• CM-1.2.1 Check loan-level data quality before rollup. Run contract checks and reconcile record counts and balances by channel and status with thresholds. Read Raw snapshot, lock history export, approved keys, thresholds and exclusion rules. Record Quality results and quarantined rows with source pointers. Owner Triage only queue items with an accountable source owner. Check Seed missing IDs, duplicate locks, stale timestamps and bad joins and prove expected flags.
• CM-1.2.2 Produce the approved pipeline views. Populate the current workbook template or governed dataset with approved rollups for locked balance, stage, aging and expiry. Read Validated snapshots, workbook/query model, authorized recipient template. Record Report artifacts keyed to run and model version. Owner Own definition changes under report model maintenance. Check Compare to accepted as-of runs across totals, null handling and drill-through rows.
• CM-1.2.3 Resolve a failed source-data check. Provide before/after field evidence, route the issue, verify corrected ingestion and selectively replay with audit. Read Failed rule, source row lineage, LOS correction or documented override. Record Resolved exception and replay receipt or approved exclusion. Owner Source owner corrects source records and analyst accepts the disposition. Check Prove a corrected record clears once and the run preserves its prior version.
• CM-1.3.1 Review material changes against prior run. Highlight cohort changes above approved thresholds and supply drill-through evidence. Read Current and prior comparable report, thresholds, saved comments. Record Reviewed movements or next action. Owner Interpret movements and decide what to escalate. Check Historical replay identifies an agreed material movement and explains contributing rows.
• CM-1.3.2 Publish the approved report and retain evidence. Publish on schedule after checks, restrict recipients, and store the run ID and access manifest. Read Report artifact, permissions map, channel and retention policy. Record Recipient-visible report and publication audit. Owner Approve audience and pause exceptional publication. Check Unauthorized recipient is denied. Retry produces no duplicate release.

CM-02 · Maintain the pipeline reporting model

• CM-2.1.1 Approve definition and business purpose of a report change. Turn the request into draft definitions, decision examples and a short approval record. Read Change ticket, current metric definitions and reference sample reports. Record Approved grain, cutoff, definition and acceptance cases. Owner Choose definitions and approve material semantic changes. Check Requester independently classifies examples of expired, relocked and cancelled loans.
• CM-2.1.2 Map current workbook logic to canonical fields. Parse the current model and compare its fields and joins with the approved source dictionary. Read Workbook, Power Query or SQL definitions, versioned field dictionary, approved LOS samples. Record Field map and dependency impact list. Owner Confirm ambiguous formulas, date semantics and exclusions. Check Trace a custom field through the workbook and verify null, typed and time-zone behavior.
• CM-2.1.3 Review access and source contract changes. Diff the field/access contract and generate least-privilege grant and rollback checklists. Read Field map, connector scope, tenant entitlement, PII classification. Record Approved source and access contract change. Owner Approve grants and validate vendor and privacy restrictions. Check Existing extracts work with the new scope and prohibited fields are unavailable.
• CM-2.2.1 Implement model and report logic change. Generate versioned transformation, template and tests from the accepted mapping. Read Approved specifications, field map and existing SQL/workbook model. Record Reviewable model patch and updated report template. Owner Review generated logic and any judgment calls in business rules. Check Regression fixtures cover the new rule and known edge cases. Unaffected reports stay within agreed tolerances.
• CM-2.2.2 Replay representative reports in parallel. Run both versions on the same inputs and surface changed records, totals and dependency effects. Read Historical immutable snapshots, prior version, proposed version, acceptance cases. Record Comparison log and signed issue dispositions. Owner Explain intended differences and stop on unexpected variance. Check Prove identical source cuts and intentional differences only, including cutoff changes.
• CM-2.3.1 Accept new business definition and output. Assemble sample reports, test outcomes and request decisions in an approval packet. Read Side-by-side outputs, change notes and stakeholder sign-offs. Record Dated release decision. Owner Accept or reject changes and coordinate downstream definitions. Check Retrieve approval and reproduce the accepted output version.
• CM-2.3.2 Promote the approved model and confirm recovery path. Promote a signed version, monitor its first run and provide an automated rollback option. Read Signed model patch, production connection, last good version. Record Production release record, smoke test and rollback reference. Owner Authorize production access and handle an abnormal rollout. Check Run production smoke test and prove rollback restores the previous model.
• CM-2.3.3 Validate a changed metric in downstream planning reports. Identify downstream uses and generate same-cutoff comparison and version history for planning consumers. Read Released metric definition, sample outputs, FP&A planning report and dependency mapping. Record Consumer signoff and updated planning reference where applicable. Owner Confirm whether the new definition affects a planning baseline, historical restatement or caveat. Check Verify the downstream version points to the signed metric definition and compare totals on a shared cutoff.

CM-04 · Manage rate locks

• CM-4.1.1 Match incoming request to authoritative loan and request IDs. Capture existing approved request events, match identity and route incomplete or duplicate submissions. Read LOS lock request API/export, PPE queue, consented ID match keys. Record Matched lock request or deduplicated exception. Owner Handle ambiguous requests under the lock exception workflow. Check Retry an identical request and confirm only one in-scope case exists.
• CM-4.1.2 Verify quoted terms against lock policy. Cross-check same-time quote, eligibility, price, duration and expiry and produce the evidence trace. Read PPE historical pricing and eligibility, LOS lock request and versioned policy. Record Verified standard lock packet or exception reason. Owner Review only decisions requiring lender discretion or exception approval. Check Replay standard and quote-changed cases and verify only the eligible result proceeds.
• CM-4.1.3 Confirm authorized lock status and receipt. Read authoritative status and reference IDs from LOS and PPE, and alert on missing or contradictory confirmations. Read LOS confirmed lock status, PPE receipt and eligible investor confirmation where required. Record Authoritative status timeline, receipt and follow-up task if missing. Owner Retain approval and transaction authority in native systems unless separately authorized. Check A PPE submission without an authoritative receipt remains pending and never shows as confirmed.
• CM-4.2.1 Monitor expirations and unresolved requests. Consolidate reminders and owner assignments using agreed cutoff, holiday and expiry rules. Read Open lock statuses, calendar, expiry and routing rules. Record Owner queue for approaching expiries and unresolved cases. Owner Contact owners or handle policy exceptions and choose next action. Check Confirm holidays and late-day cutoffs classify known expiries and no duplicate reminder is sent.
• CM-4.2.2 Investigate extension, re-lock, cancellation or pricing change. Retrieve prior/current/worst-case quotes where enabled, calculate approved terms and prepare the evidence packet. Read LOS event history, PPE historical quote, lock policy and requester notes. Record Proposed amendment with amount, terms, evidence and justification. Owner Verify the right option and resolve ambiguity with the requester. Check Compare extension and re-lock scenarios with accepted native outcomes and test missing quotes.
• CM-4.2.3 Authorize out-of-policy or high-value lock exception. Route a complete packet to the required approver and preserve decision/conditions. Read Exception packet, impact, mandate and approver matrix. Record Dated authorized decision or documented decline. Owner Approve or decline under delegated authority. Check Prove a breached approval limit blocks downstream write and retains an audit trail.
• CM-4.2.4 Post approved lock change and verify corrected record. Prepare the action or send through an explicitly approved native route, then reconcile status and generate notification evidence. Read Signed amendment, native LOS/PPE writeback access, current version and requester. Record Verified amendment and acknowledgment trail. Owner Complete or supervise writeback according to authority and verify ambiguous confirmations. Check Test stale loan version, duplicate amendment and partial writeback to prove safe recovery.
• CM-4.2.5 Own lock-state integration failure. Correlate event IDs, route system failures to integration owners and replay after authorization. Read Event log, entitlement, source and destination lock history. Record Resolved integration incident with readback evidence. Owner Repair credentials, vendor configuration or other source integration defects. Check Simulate out-of-order lock events and prove authoritative state survives a replay.

CM-05 · Forecast fundings and pull-through

• CM-5.1.1 Build eligible forecast population at the cutoff. Apply approved point-in-time eligibility and join existing forecast/pipeline records to source balances and stage. Read Curated LOS snapshot, loan purpose/channel, current status, lock/funding event history and model versions. Record Eligible forecast population with explicit inclusion reasons. Owner Resolve population exceptions and agree inclusion rules with Treasury and hedge owners. Check Replay a period with transfers, re-locks and cancellations and prove no double counting or lookahead.
• CM-5.1.2 Generate expected funding amounts and dates. Use authorized native estimates when available, calculate expected funding by cohort/date and explain run-to-run movement. Read LOS milestone and scheduled dates, historical funding actuals, existing forecast output/coefficients, holiday calendar. Record Versioned loan/cohort expected funding schedule and uncertainty notes. Owner Retain approved model assumptions and review significant model overrides. Check Validate on frozen historical as-of inputs, reconcile date buckets to cohort totals and validate override trace.
• CM-5.1.3 Calculate or ingest hedge pull-through by cohort. Reconcile coverage of native model results and, when approved, compute weighted locked exposure from policy-approved probabilities. Read Locked loan snapshot, existing hedge platform model output, segment mapping and pull-through rules. Record Expected funded locked balance by risk cohort with version and override flags. Owner Approve cohort fallbacks, model parameters and material overrides with business/risk owners. Check Check weighted balance equals loan balance × probability per approved sample and that ineligible loans are excluded.
• CM-5.2.1 Explain funding and pull-through movements. Show contributions from volume, stage, date, probability and override changes, then retain comments and approved overrides. Read Forecast versions, historical actuals, override register and pipeline movement detail. Record Reviewed forecast and documented exceptions. Owner Determine whether a large movement merits action or overrides and record the decision. Check Reproduce explanation for a known large movement from dated source snapshots.
• CM-5.2.2 Confirm the cash-planning handoff. Deliver approved date buckets and variance context into the existing Treasury view and flag data or timing gaps. Read Approved funding forecast, bank/warehouse position feed or Treasury export and funding calendar. Record Acknowledged forecast handoff with timing and cash-gap flags. Owner Make liquidity, draw or funding decisions in Treasury's own process. Check Treasury traces a material date change to contributing loans and acknowledges the handoff.
• CM-5.3.1 Backtest forecast calibration and timing. Automate point-in-time joins, holdout runs, calibration and drift analysis by channel, product and cohort. Read Immutable historical forecast/LOS snapshots, realized funding and fallout, approved cohort thresholds. Record Backtest and drift evidence with selected challenge cases. Owner Interpret sample sufficiency, data shifts and modeling limitations. Check Prove no post-cutoff fields enter historical estimates and compare to baseline for out-of-time samples.
• CM-5.3.2 Approve model or fallback changes. Compile proposed parameters and effective dates into a controlled release package and route signatures. Read Backtest, proposed parameters, model inventory and governance matrix. Record Signed model or fallback release and effective dates. Owner Own assumptions, thresholds and rollout decisions under lender policy. Check Withdraw an approved model version in test and prove rollbacks restore prior forecast behavior.
• CM-5.3.3 Review model challenge and governance evidence. Assemble drift, benchmark, lineage and override evidence with approvals and remediation tracking. Read Model inventory, outcomes, backtests, override policy and independent challenge checklist. Record Independent decision, conditions and review evidence. Owner Decide independently under policy whether the model or fallback is acceptable. Check Trace a model override and document a failed challenge without changing production parameters.

CM-06 · Monitor and adjust hedges

• CM-6.1.1 Check hedge platform input readiness. Check freshness and cutoff alignment through entitled feeds, and link broken inputs to accountable owners. Read Market data feed with usage entitlement, LOS/hedge model output, blotter, inventory and commitment feeds. Record Risk review readiness and input manifest. Owner Decide whether to suspend action when a critical market or source feed is stale. Check Inject stale market marks or a delayed trade feed and verify the decision gate blocks.
• CM-6.1.2 Assess net exposure and limits by hedge strategy. Ingest native risk outputs, normalize approved risk buckets and alert on threshold breaches with drill-through. Read Hedge platform exposure/marks, forward commitments, funded inventory, open positions and policy limits. Record Time-stamped exposure, coverage and limits view. Owner Choose hedge methodology and interpret model risk and unusual markets. Check Replay a position near a limit and match native exposures at the same cutoff and market marks.
• CM-6.1.3 Review material coverage or profit sensitivity change. Present explained changes from pipeline moves, revised pull-through, market moves and trades, with scenarios if approved. Read Exposure and risk-limit output, prior review, current market data and existing scenarios. Record Action, hold or escalation logged with position evidence. Owner Decide business action within authority and record material changes. Check Verify a known market move and loan event are separately attributable and displayed at the correct time.
• CM-6.2.1 Prepare hedge adjustment or rebalance proposal. Create decision packet and proposed ticket from approved native calculations, then run size, mandate and reference checks. Read Native hedge platform, policy instruments, market marks, counterparties and trade limits. Record Proposed ticket, residual exposure and approval requirement. Owner Determine instrument and size based on product and policy. A native hedge/forward is conditional on eligibility and mandate. Check Replay a proposed adjustment and independently verify risk moves in the intended direction.
• CM-6.2.2 Authorize a trade under the delegation matrix. Route an approval when required and retain version, authority evidence and status. Read Proposed ticket, mandate, delegation matrix and counterparty limits. Record Approved, delegated or declined proposal with audit record. Owner Keep discretionary hedge decision and approval with authorized personnel. Check Reject a proposal exceeding mandate. Do not allow stale approvals to release revised ticket.
• CM-6.2.3 Execute through authorized channel and capture fill. Prepare ticket, ingest venue/advisor response and verify each fill against the approved ticket. Native execution is separately authorized. Read Approved ticket, execution venue or hedge advisor, native fill and timestamps. Record Authoritative fill or pending/rejected order and updated blotter reference. Owner Retain trade placement authority and supervise incomplete, rejected or partial fills. Check A partial fill updates only executed quantity and an ambiguous acknowledgment never duplicates the order.
• CM-6.2.4 Escalate a hedge policy breach. Package position, timestamps, exposure, approvals and response SLA in the existing issue queue. Read Breach evidence, approved limits and escalation/authority policy. Record Documented exception, disposition and subsequent readback. Owner Make independent risk decision and authorize response or continued monitoring. Check Replay a breached limit, prove segregation of duties and show the breach stays open until approved resolution.

CM-07 · Reconcile hedge positions and trades

• CM-7.1.1 Capture independent position and trade files. Read approved internal and counterparty files/API independently, register checksums and timestamps, and validate file completeness. Read Risk platform/blotter export, independent counterparty confirmation and position report via entitled API or managed file. Record Immutable, separately sourced daily trade and position evidence. Owner Check missing confirmation or unauthorized delivery with the owner. Check Hold the close control on a missing independent file or if both sources resolve to the same upstream export.
• CM-7.1.2 Reconcile trade life cycle against counterparty confirmations. Match on approved IDs plus terms, show side/size/price/settlement differences and quarantine ambiguous matches. Read Internal tickets and native fills, counterparty confirmations, allocations and matching policy. Record Matched trade ledger and unresolved differences. Owner Resolve unresolved cases through the trade exception queue. Check Test partial fill, corrected trade, late confirmation and unmatched confirmation with duplicate IDs.
• CM-7.1.3 Tie open position and marks to daily roll-forward. Roll forward by product and settlement period, compare to separately sourced positions and expose break/mark tolerance. Read Prior approved positions, reconciled trade activity, independent position file, approved marks and policy. Record Position roll-forward, mark check and reconciliation status. Owner Investigate unexplained amounts and confirm authorized adjustments. Check Replay a close with maturing positions, partial close-outs and settlement changes and prove the roll-forward balances.
• CM-7.2.1 Investigate a trade or position difference. Build an evidence packet, propose likely match or break cause, and route to responsible staff with age and impact. Read Trade blotter, counterparty confirmation, risk platform, adjustment policy and aging rules. Record Assigned break with evidence and recommended disposition. Owner Verify cause and maintain independent reconciliation. No self-approved corrections. Check Reproduce a known trade size or settlement difference and verify the right owner and priority.
• CM-7.2.2 Correct authorized trade record and confirm revised position. Prepare approved correction, collect authoritative updated evidence and re-run affected balances and matches. Read Signed trade correction request, counterparty update, native trade record and authorization. Record Approved source correction and revised matching or rerun record. Owner Work with counterparty, authorize within mandate and verify correction when needed. Check An amended source confirmation clears only the corresponding open break and retains old values in the audit.
• CM-7.2.3 Approve daily position control and unresolved-break status. Build daily signoff packet and track open breaks, aging and actual independent approval. Read Daily trade and position reconciliations, break register, sign-off matrix. Record Signed daily position evidence or flagged incomplete control. Owner Sign off only where independent, or route to the designated independent approver. Check A material unresolved break blocks an unqualified sign-off and trading/approval roles follow policy.
• CM-7.2.4 Decide disposition of material or overdue reconciliation break. Compile independent and trading-side evidence and route under the risk matrix with an immutable decision log. Read Break register, independent counterparty evidence, corrected trade record and policy thresholds. Record Risk disposition, responsible owner and closure requirement. Owner Decide or escalate independently and follow through on closing evidence. Check Seed a material open break and prove an unauthorized approver cannot close it.
• CM-7.3.1 Provide reconciled trade/position support to Accounting. Package signed reconciliation evidence and tie it to the accounting handoff with discrepancies and period cutoff visible. Read Signed daily records, month-end positions, confirmed activity and accounting cutoff calendar. Record Accounting handoff, traceable exceptions and receipt. Owner Approve ledger entries in the separate accounting process and resolve material breaks with the owners. Check Accounting selects a trade and traces it to independent confirmation and correct period without recreating the source match.

Build it yourself with AI

Build a replayable report with CSVs and Python or SQL

Start a scratch directory with fabricated loan_events.csv, lock_events.csv, approved_rates.csv, funding_actuals.csv, and a desk_rules.md written with the desk analyst. Pin a report cutoff, eligible lock statuses, the cohort keys, and the outcome window. Keep any position inputs in a separate file with units and effective times.

Prompt 1. Turn the desk’s rules into a contract

Read desk_rules.md and the sample event headers. Design the output grain and joins for an as-of lock snapshot. Explain which IDs and effective timestamps control duplicates and relocks. Give me an exception file contract for stale feeds, a conflicting active lock, an uncovered cohort, and missing outcome records. Ask the desk to resolve any rule that is absent.

Prompt 2. Generate the report and its tests

Write a local Python or SQL workflow that uses only the supplied eligibility rules and approved_rates.csv. Output current_locks.csv, daily_bridge.csv, forecast.csv, and exceptions.csv. Keep the input version, cutoff, model version, rate source, and exception reason on the output. Compare with the prior snapshot and test that bridge categories sum to the principal change. Never estimate a rate that the supplied rules do not cover.

Prompt 3. Prepare the desk’s daily review

Use the generated CSVs to draft a review checklist that separates source freshness, changes in locked principal, projected funding, and exceptions for the hedge desk. Link each claim to a CSV row or total. Provide a backtest procedure against funding_actuals.csv using the original cutoff and eligibility basis. Leave signoff and trading actions with their existing owners.

Try it. At the fixed cutoff, use eligible loans A100 and A200 with $300,000 at 0.80 and $200,000 at 0.65. The report should show $500,000 of eligible principal and $370,000 of expected funding. Add a prior version of A100 and a later cancellation beyond the cutoff. The first run stays at $370,000 and the next report explains the cancellation. Ask the desk analyst to compare the script with accepted daily runs before distributing it.

Need help automating this?

Explore the related mortgage lending solution for connected loan data, exception review and repeatable lender workflows. Explore Mage's mortgage lending workflows.