Generating NTD Revenue Reports from Fare Ledgers

This page builds a National Transit Database-style directly-generated fare revenue report for a single reporting period straight from the audited ledger: aggregate every fare entry by mode and fare category, apply the period cutoff, and reconcile the report total back to the ledger sum with Decimal before anything is filed. It is the concrete task behind the Compliance Reporting Automation component inside the Revenue Reconciliation & Settlement pipeline, written for the revenue analysts and Python developers who have to hand the FTA a fare revenue figure that foots to the cent and survives an audit. The report is a pure function of the ledger, so the same period always regenerates to the same number.

NTD Report Flow

The generator selects entries whose economic date falls inside the reporting period, buckets them into the report’s mode-and-category grid, then computes an independent control total over the same selection. The report only ships when the grid total equals the control total. The flow below traces one period from ledger to a reconciled, filable report:

Step 1 — Model the Reporting Grid

NTD directly-generated fare revenue is reported by mode. Model each ledger entry as carrying a mode and a fare category, and define the report as a grid keyed on (mode, category). Keep the amount a Decimal in minor units and the economic date on the entry, because the cutoff keys on when the fare happened, not when it was booked.

from __future__ import annotations

from dataclasses import dataclass
from datetime import datetime
from decimal import Decimal


@dataclass(frozen=True)
class LedgerFareEntry:
    entry_key: str
    mode: str              # NTD mode code, e.g. 'MB' bus, 'HR' heavy rail
    category: str          # 'adult_full', 'senior', 'student', 'transfer_credit'
    amount_minor: Decimal  # signed minor units; refunds/credits negative
    occurred_utc: datetime # economic date used for the period cutoff


@dataclass(frozen=True)
class NtdReportLine:
    mode: str
    category: str
    revenue_minor: Decimal
    entry_count: int


@dataclass(frozen=True)
class NtdRevenueReport:
    period_label: str
    lines: tuple[NtdReportLine, ...]
    total_minor: Decimal
    ledger_control_minor: Decimal

    def dollars(self) -> Decimal:
        """Report revenue in dollars for the NTD form (2 dp, exact)."""
        return (self.total_minor / Decimal("100")).quantize(Decimal("0.01"))

Modeling revenue as a signed Decimal is what lets refunds and transfer credits net correctly against fares without a second code path: a negative amount_minor reduces its grid cell and the control total identically, so the two can never diverge on sign handling.

Step 2 — Aggregate with the Period Cutoff

The cutoff is a half-open interval [start, end) in the agency’s reporting timezone. Aggregation is a single pass: for each entry inside the window, add its amount to its grid cell and to an independent control total. Everything is Decimal; a float amount raises rather than silently rounds.

import logging
from datetime import timezone
from typing import Iterable

logger = logging.getLogger("transit.ntd_report")


class NtdReconciliationError(Exception):
    """Raised when the report grid does not tie back to the ledger control sum."""


def generate_ntd_report(
    entries: Iterable[LedgerFareEntry],
    *,
    period_label: str,
    period_start: datetime,
    period_end: datetime,
    report_tz: timezone = timezone.utc,
) -> NtdRevenueReport:
    if period_start.tzinfo is None or period_end.tzinfo is None:
        raise ValueError("period bounds must be timezone-aware")
    if period_end <= period_start:
        raise ValueError("period_end must be after period_start")

    grid: dict[tuple[str, str], Decimal] = {}
    counts: dict[tuple[str, str], int] = {}
    control = Decimal("0")

    for entry in entries:
        if not isinstance(entry.amount_minor, Decimal):
            raise TypeError(f"amount for {entry.entry_key} must be Decimal")
        occurred_local = entry.occurred_utc.astimezone(report_tz)
        if not (period_start <= occurred_local < period_end):
            continue  # half-open window: entry at exactly period_end is next period
        key = (entry.mode, entry.category)
        grid[key] = grid.get(key, Decimal("0")) + entry.amount_minor
        counts[key] = counts.get(key, 0) + 1
        control += entry.amount_minor

    lines = tuple(
        NtdReportLine(mode=m, category=c, revenue_minor=grid[(m, c)], entry_count=counts[(m, c)])
        for (m, c) in sorted(grid)
    )
    grid_total = sum((ln.revenue_minor for ln in lines), Decimal("0"))

    if grid_total != control:
        raise NtdReconciliationError(
            f"{period_label}: grid total {grid_total} != control {control}")

    logger.info("NTD report %s: %d lines, total_minor=%s", period_label, len(lines), grid_total)
    return NtdRevenueReport(
        period_label=period_label,
        lines=lines,
        total_minor=grid_total,
        ledger_control_minor=control,
    )

Step 3 — Reconcile Back to the Ledger

The in-function check proves the grid is internally consistent. The final tie-out proves the report matches the ledger the rest of the settlement pipeline believes in — the balance the ledger module computes for the same period, independently of this generator.

def reconcile_to_ledger(report: NtdRevenueReport, ledger_period_total_minor: Decimal) -> None:
    """Assert the report ties back to the ledger's own period total. The
    ledger total is computed by the ledger module, not by this generator,
    so agreement is a genuine cross-check rather than a tautology."""
    if report.total_minor != ledger_period_total_minor:
        raise NtdReconciliationError(
            f"{report.period_label}: report {report.total_minor} "
            f"!= ledger {ledger_period_total_minor} "
            f"(delta {report.total_minor - ledger_period_total_minor})")
    logger.info("%s reconciled to ledger at %s minor", report.period_label, report.total_minor)

Reporting the signed delta on failure is deliberate: the difference is the size and direction of the discrepancy, which is the first thing an analyst needs to localize the missing or duplicated revenue.

Validation & Test Cases

The tests cover a normal period that ties out across two modes, and a late adjustment that lands in a closed period and forces a restatement whose regenerated total still reconciles.

from decimal import Decimal
from datetime import datetime, timezone

def _utc(y, mo, d, h=12, mi=0):
    return datetime(y, mo, d, h, mi, tzinfo=timezone.utc)

jul_start, jul_end = _utc(2026, 7, 1, 0, 0), _utc(2026, 8, 1, 0, 0)

entries = [
    LedgerFareEntry("e1", "MB", "adult_full", Decimal("290"), _utc(2026, 7, 3)),
    LedgerFareEntry("e2", "MB", "senior",     Decimal("110"), _utc(2026, 7, 4)),
    LedgerFareEntry("e3", "HR", "adult_full", Decimal("340"), _utc(2026, 7, 9)),
    LedgerFareEntry("e4", "HR", "adult_full", Decimal("340"), _utc(2026, 7, 20)),
    # A tap on Aug 1 00:00 is the NEXT period, not July (half-open window).
    LedgerFareEntry("e5", "MB", "adult_full", Decimal("290"), _utc(2026, 8, 1, 0, 0)),
]

# Normal case: July report excludes the Aug 1 tap and ties out.
report = generate_ntd_report(entries, period_label="2026-07",
                             period_start=jul_start, period_end=jul_end)
assert report.total_minor == Decimal("1080")   # 290 + 110 + 340 + 340
assert report.dollars() == Decimal("10.80")
reconcile_to_ledger(report, ledger_period_total_minor=Decimal("1080"))

# Edge case: a late refund for a July heavy-rail fare arrives in September,
# but its economic date is in July. Book it as a compensating entry dated
# in July, regenerate July, and the restated report still reconciles.
late_adjustment = LedgerFareEntry("e3c", "HR", "adult_full", Decimal("-340"), _utc(2026, 7, 9))
restated = generate_ntd_report(entries + [late_adjustment], period_label="2026-07-R1",
                               period_start=jul_start, period_end=jul_end)
assert restated.total_minor == Decimal("740")   # 1080 - 340
reconcile_to_ledger(restated, ledger_period_total_minor=Decimal("740"))

# The Aug 1 boundary tap belongs to August, proving the half-open cutoff.
aug = generate_ntd_report(entries, period_label="2026-08",
                          period_start=_utc(2026, 8, 1), period_end=_utc(2026, 9, 1))
assert aug.total_minor == Decimal("290")

The restatement case is the one auditors probe. The refund’s economic date is July 9, so booking it as a compensating entry dated in July and regenerating pulls it into the July restatement automatically — the -340 nets the original 340 down to zero for that fare, the restated total drops to 740, and the tie-out to the ledger’s own recomputed July total still holds. The late adjustment never contaminates September, because the cutoff keys on occurred_utc, not arrival time.

Edge Cases & Gotchas

• Half-open windows only. The tap at exactly period_end is next period’s. Inclusive-both-ends double-counts a boundary day across the year; the Aug 1 assertion above locks this in.
• Restate, never overwrite. A closed period that gains a correction gets a new labeled report (2026-07-R1), preserving what was originally filed. NTD submissions have a restatement path for exactly this.
• Economic date, not booking date. A September refund for a July fare is July revenue. Keying the cutoff on occurred_utc is what makes restatement land in the right period.
• Sign discipline. Refunds and transfer credits are negative Decimals that net against fares in both the grid cell and the control total, so they can never split the tie-out.
• Verify the chain first. Aggregating a tampered ledger yields a self-consistent but false filing; run the ledger’s hash-chain verification before generating.

Integration Note

This report is one leaf of the parent Compliance Reporting Automation component, which wraps this generator in scheduling, restatement handling, and archival. Its closest sibling is Designing Append-Only Fare Ledgers with Hash Chaining: that guide builds the tamper-evident ledger this report reads, and its verify() walk is the precondition that makes this filing defensible — a report aggregated from an unverified chain proves nothing, because the source could have been edited after the fact.

FAQ

Related

• Compliance Reporting Automation — the parent component that schedules, restates, and archives this report.
• Designing Append-Only Fare Ledgers with Hash Chaining — the tamper-evident ledger this report reads and verifies.
• Ledger & Audit Trail Design — the audit-trail design that guarantees the report’s source is immutable.
• Daily Variance Reconciliation — the daily check that keeps the ledger externally accurate before a period closes.

Part of Compliance Reporting Automation, within Revenue Reconciliation & Settlement.