Trustee Tapes¶

Trustee tapes are periodic statement files from deal trustees containing loan-level portfolio data. They are the authoritative source of record for a CLO's portfolio composition and serve as the basis for reconciliation against the manager's internal books.

Upload Process¶

Supported Formats¶

Trustee tapes are uploaded as Excel files. The system:

1. Validates the file format and structure
2. Parses using trustee-specific normalization rules
3. Extracts loan-level data (CUSIP, par, price, ratings, spread)
4. Stores normalized data for reconciliation

File Requirements¶

Trustee-Specific Parsing¶

Each trustee has distinct file layouts. The parser normalizes all formats into a common loan schema using the alias profile system.

US Bank¶

Standard CLO trustee report format with: - Loan identifier columns (CUSIP, LoanX ID) - Financial data (par, price, spread, rates) - Rating columns (Moody's, S&P, Fitch) - Borrower and industry classification

Deutsche Bank¶

European trustee format with: - ISIN-based identification - Spread and rate conventions - Industry and country mappings

Wilmington Trust¶

US trustee format similar to US Bank with: - Customized column naming - Additional reference fields

BNY Mellon¶

US trustee format with: - "Current Par" naming convention for par value - Abbreviated rating column headers ("Mdy", "S&P") - Borrower-centric naming

Loan Data Model¶

Each parsed loan includes:

Data Field Reference¶

This section provides a comprehensive reference for every field that appears in trustee tapes, grouped by the compliance tests they feed into. Use this reference when building parsers, configuring alias profiles, or debugging reconciliation discrepancies.

Loan Identification Fields¶

These fields uniquely identify a loan position within the portfolio.

# Loan matching priority
def match_loan(tape_loan, internal_loan):
    if tape_loan.cusip and internal_loan.cusip:
        return tape_loan.cusip == internal_loan.cusip
    elif tape_loan.isin and internal_loan.isin:
        return tape_loan.isin == internal_loan.isin
    elif tape_loan.loanx_id and internal_loan.loanx_id:
        return tape_loan.loanx_id == internal_loan.loanx_id
    else:
        # Fuzzy match on name + maturity as last resort
        return fuzzy_match(tape_loan, internal_loan)

Financial Fields (OC/IC Tests)¶

These fields feed directly into overcollateralisation and interest coverage calculations. They are the most reconciliation-sensitive fields -- even small discrepancies can affect compliance test results.

OC Ratio = Adjusted Collateral Balance / Tranche Outstanding Balance

Adjusted Collateral Balance =
    Sum of all loan par values
  - Defaulted asset haircuts
  - CCC excess haircuts (par above the CCC bucket limit, valued at market)
  - Discount obligations (if purchase price < threshold)
  + Principal cash

Credit Fields (WARF/CCC Tests)¶

These fields determine the credit quality profile of the portfolio and feed into rating-dependent compliance tests.

# Composite rating calculation
def calculate_composite_rating(
    moodys: str | None,
    sp: str | None,
    fitch: str | None,
    methodology: str,
) -> str:
    ratings = [r for r in [moodys, sp, fitch] if r is not None]

    if methodology == "worst_of":
        return max(ratings, key=rating_to_numeric)
    elif methodology == "middle_of_three" and len(ratings) == 3:
        return sorted(ratings, key=rating_to_numeric)[1]
    elif methodology == "moodys_primary":
        return moodys or sp or fitch
    elif methodology == "sp_primary":
        return sp or fitch or moodys

Maturity Fields (WAL Test)¶

These fields determine the time profile of the portfolio and feed into the Weighted Average Life calculation.

WAL = Sum(Principal Payment(i) * Time to Payment(i)) / Total Principal

Where:
- Principal Payment(i) = expected principal received at time i
- Time to Payment(i) = years from measurement date to payment date

Concentration Fields¶

These fields are used to compute concentration metrics across obligors, industries, and geographies.

Settlement and Trade Fields¶

These fields track the settlement status of positions and are important for reconciliation timing differences.

Trustee Format Variations¶

Field names vary significantly across trustees. This is the core reason CalcBridge's alias profile system exists. The following table maps common concepts to their actual column names in each trustee's file format.

Identifier Fields¶

Financial Fields¶

Rating Fields¶

Classification Fields¶

Date Fields¶

{
  "profile_name": "bny_mellon_standard",
  "trustee": "bny_mellon",
  "mappings": {
    "Current Par": "par_value",
    "Borrower": "borrower_name",
    "Mdy": "rating_moodys",
    "S&P": "rating_sp",
    "Spread": "spread",
    "Maturity": "maturity_date"
  }
}

European Trustee Considerations¶

European trustee tapes have several characteristics that differ from US tapes and require specific parser handling.

ISIN as Primary Identifier¶

European tapes may not include a CUSIP at all. The ISIN is the primary identifier, and CalcBridge's matching logic must handle this.

Multi-Currency Positions¶

Some European CLOs hold GBP-denominated loans within a EUR-denominated deal. The trustee tape may include a currency column, and par values must be converted to the deal currency for compliance calculations.

Euribor Conventions¶

European tapes reference Euribor rather than SOFR. The base rate field may contain values like EURIBOR 3M or EUR3M, which must be normalised.

Different Date Formats¶

European tapes sometimes use DD/MM/YYYY rather than MM/DD/YYYY. The parser must detect and handle both conventions.

Data Quality Indicators¶

Trustee tapes sometimes contain data quality issues that CalcBridge must handle gracefully.

Parser Architecture¶

CalcBridge's trustee tape parser follows a pipeline architecture:

flowchart LR
    A[Raw Excel<br/>File] --> B[Trustee<br/>Detection]
    B --> C[Alias Profile<br/>Loading]
    C --> D[Column<br/>Mapping]
    D --> E[Type<br/>Conversion]
    E --> F[Validation<br/>Rules]
    F --> G[Normalized<br/>Loan Schema]

    style B fill:#DBEAFE,stroke:#3B82F6
    style D fill:#DBEAFE,stroke:#3B82F6
    style G fill:#DCFCE7,stroke:#22C55E

1. Trustee Detection -- Identify the trustee from file structure or user selection
2. Alias Profile Loading -- Load the appropriate alias profile for the detected trustee
3. Column Mapping -- Map trustee-specific column names to normalized field names
4. Type Conversion -- Convert string values to appropriate types (dates, decimals, ratings)
5. Validation Rules -- Apply data quality checks and flag issues
6. Normalized Output -- Produce a consistent loan schema for reconciliation

# Parser pipeline example
class TrusteeTapeParser:
    def parse(
        self,
        file_path: str,
        trustee: str,
        statement_date: date,
    ) -> ParseResult:
        # Load raw data
        df = pd.read_excel(file_path)

        # Load alias profile
        profile = self.alias_registry.get_profile(trustee)

        # Map columns
        df = df.rename(columns=profile.mappings)

        # Convert types
        df = self.type_converter.convert(df, profile.type_hints)

        # Validate
        issues = self.validator.validate(df, statement_date)

        # Build normalized loan objects
        loans = [NormalizedLoan(**row) for row in df.to_dict("records")]

        return ParseResult(loans=loans, issues=issues)

Related Documentation¶

• Discrepancies - Reconciliation issue management
• Reconciliation API - REST endpoints
• Alias Profiles - Header normalization configuration
• Regional Differences - European vs US trustee format differences