Skip to content

Generating Compliance Reports

CalcBridge transforms compliance test results into professional, trustee-ready reports. Generate comprehensive documentation in PDF or Excel format with a single API call or button click.

Report Types

Compliance Report

The primary compliance report includes:

  • Executive Summary - Overall compliance status, pass/fail counts, critical issues
  • OC/IC Tests - Detailed coverage test results with calculations
  • WARF Analysis - Weighted Average Rating Factor breakdown
  • Concentration Tests - Obligor and industry concentration results
  • Recommendations - Action items based on test results

Reconciliation Report

For data reconciliation and variance analysis:

  • Data Summary - Record counts and totals
  • Variance Analysis - Differences from expected values
  • Missing Data - Identification of gaps
  • Recommendations - Data quality improvements

Generating Reports via API

Create a Compliance Report

curl -X POST https://api.calcbridge.io/api/v1/reports/compliance \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "workbook_id": "550e8400-e29b-41d4-a716-446655440000",
    "format": "pdf",
    "filename": "CLO_2024-1_Compliance_Report",
    "options": {
      "include_header": true,
      "include_metadata": true,
      "date_format": "%Y-%m-%d",
      "float_precision": 2,
      "sanitize_pii": true
    }
  }'

Response:

{
  "id": "report_abc123",
  "report_type": "compliance",
  "status": "completed",
  "format": "pdf",
  "title": "Compliance Report - CLO 2024-1",
  "file_size_bytes": 245678,
  "download_url": "/api/v1/reports/report_abc123/download",
  "created_at": "2024-01-15T10:30:00Z",
  "completed_at": "2024-01-15T10:30:05Z",
  "error_message": null
}

Download the Report

curl -X GET "https://api.calcbridge.io/api/v1/reports/report_abc123/download" \
  -H "Authorization: Bearer $TOKEN" \
  -o "CLO_2024-1_Compliance_Report.pdf"

Report Templates and Customization

Available Export Formats

Format Description Best For
PDF Professional formatted document Trustee submissions, archives
XLSX Excel spreadsheet with data Analysis, data manipulation

Export Options

Customize report output with these options:

{
  "options": {
    "include_header": true,
    "include_metadata": true,
    "date_format": "%Y-%m-%d",
    "datetime_format": "%Y-%m-%d %H:%M:%S",
    "float_precision": 6,
    "null_representation": "",
    "xlsx_include_styles": true,
    "xlsx_freeze_header": true,
    "xlsx_auto_width": true,
    "sanitize_pii": true,
    "classification_threshold": "CONFIDENTIAL",
    "redaction_strategy": "MASK"
  }
}
Option Type Default Description
include_header boolean true Include column headers
include_metadata boolean true Include report metadata
date_format string %Y-%m-%d Date formatting
datetime_format string %Y-%m-%d %H:%M:%S Datetime formatting
float_precision integer 6 Decimal places for numbers
null_representation string "" How to display null values
xlsx_include_styles boolean true Apply styling to Excel
xlsx_freeze_header boolean true Freeze header row
xlsx_auto_width boolean true Auto-fit column widths
sanitize_pii boolean true Redact PII data
classification_threshold string CONFIDENTIAL Minimum level to redact
redaction_strategy string MASK How to redact (MASK, REMOVE, HASH)

Report Sections

Executive Summary

{
  "section": "executive_summary",
  "content": {
    "overall_status": "warning",
    "tests_passed": 22,
    "tests_failed": 1,
    "tests_warning": 1,
    "total_tests": 24,
    "pass_rate": 91.7,
    "portfolio_summary": {
      "total_par": "500000000.00",
      "total_mv": "487500000.00",
      "avg_price": "97.50",
      "loan_count": 245
    }
  }
}

OC/IC Tests Section

{
  "section": "oc_ic_tests",
  "content": {
    "tests": [
      {
        "test_name": "Senior OC Ratio",
        "status": "pass",
        "actual_value": "122.50",
        "threshold_value": "120.00",
        "cushion": "2.50",
        "cushion_pct": "2.08"
      },
      {
        "test_name": "Mezzanine OC Ratio",
        "status": "pass",
        "actual_value": "110.25",
        "threshold_value": "108.00",
        "cushion": "2.25",
        "cushion_pct": "2.08"
      }
    ],
    "summary": {
      "total": 6,
      "passed": 6,
      "failed": 0
    }
  }
}

WARF Analysis Section

{
  "section": "warf_analysis",
  "content": {
    "current_warf": "2650",
    "max_warf": "2850",
    "warf_cushion": "200",
    "top_contributors": [
      {
        "cusip": "12345ABC",
        "issuer": "XYZ Corp",
        "contribution": "45.5"
      },
      {
        "cusip": "67890DEF",
        "issuer": "ABC Holdings",
        "contribution": "38.2"
      }
    ]
  }
}

Concentration Tests Section

{
  "section": "concentration_tests",
  "content": {
    "tests": [
      {
        "test_name": "Single Obligor - ACME Corp",
        "status": "pass",
        "actual_value": "8.23",
        "threshold_value": "10.00",
        "cushion": "1.77",
        "cushion_pct": "17.7"
      }
    ],
    "summary": {
      "total": 8,
      "passed": 8,
      "failed": 0,
      "warning": 0
    }
  }
}

Recommendations Section

{
  "section": "recommendations",
  "content": {
    "recommendations": [
      {
        "priority": "high",
        "test_name": "CCC Bucket",
        "action": "Immediate action required: CCC Bucket has failed. Current value 8.12% breaches threshold 7.50%."
      },
      {
        "priority": "medium",
        "test_name": "WAL",
        "action": "Monitor closely: WAL is approaching threshold. Current cushion is 3.6%."
      }
    ],
    "high_priority_count": 1,
    "medium_priority_count": 1
  }
}

PDF Report Structure

The PDF compliance report follows a professional layout:

+----------------------------------------------------------+
|                   [Company Logo]                          |
|                                                          |
|            COMPLIANCE REPORT                             |
|            CLO 2024-1                                    |
|            As of January 15, 2024                        |
+----------------------------------------------------------+
|                                                          |
|  EXECUTIVE SUMMARY                                       |
|  ==================                                      |
|                                                          |
|  Overall Status: WARNING                                 |
|                                                          |
|  +------------+------------+------------+               |
|  |  PASSED    |  WARNING   |  FAILED    |               |
|  |    22      |     1      |     1      |               |
|  +------------+------------+------------+               |
|                                                          |
|  Pass Rate: 91.7%                                        |
|                                                          |
|  Portfolio Summary:                                      |
|  - Total Par: $500,000,000                              |
|  - Loan Count: 245                                       |
|  - Average Price: 97.50                                  |
|                                                          |
+----------------------------------------------------------+
|                                                          |
|  OC/IC TESTS                                            |
|  ===========                                             |
|                                                          |
|  | Test              | Value   | Limit   | Cushion |    |
|  |-------------------|---------|---------|---------|    |
|  | Senior OC         | 122.50% | 120.00% | 2.50%   |    |
|  | Mezzanine OC      | 110.25% | 108.00% | 2.25%   |    |
|  | Senior IC         | 165.00% | 150.00% | 15.00%  |    |
|                                                          |
+----------------------------------------------------------+
|                                                          |
|  WARF ANALYSIS                                          |
|  =============                                           |
|                                                          |
|  Current WARF: 2,650                                     |
|  Maximum WARF: 2,850                                     |
|  WARF Cushion: 200                                       |
|                                                          |
|  Top Contributors to WARF:                               |
|  1. XYZ Corp (45.5)                                      |
|  2. ABC Holdings (38.2)                                  |
|                                                          |
+----------------------------------------------------------+
|                                                          |
|  CONCENTRATION TESTS                                    |
|  ===================                                     |
|                                                          |
|  8 of 8 tests passing                                   |
|                                                          |
|  Top 5 Obligors:                                        |
|  1. ACME Corp: 8.23% (limit: 10%)                       |
|  2. Beta Inc: 7.15% (limit: 10%)                        |
|                                                          |
+----------------------------------------------------------+
|                                                          |
|  RECOMMENDATIONS                                        |
|  ===============                                         |
|                                                          |
|  HIGH PRIORITY:                                         |
|  - CCC Bucket breach requires immediate attention       |
|                                                          |
|  MEDIUM PRIORITY:                                       |
|  - Monitor WAL trend closely                            |
|                                                          |
+----------------------------------------------------------+
|                                                          |
|  Generated by CalcBridge | Confidential                 |
|  Report ID: report_abc123                               |
|                                                          |
+----------------------------------------------------------+

Excel Report Structure

Excel reports include multiple worksheets:

Worksheet Contents
Summary Executive summary and key metrics
OC_IC_Tests Coverage test results table
WARF_Analysis WARF breakdown and contributors
Concentration Obligor and industry concentration
All_Tests Complete test results
Recommendations Action items
Metadata Report generation details

Excel Styling Options

{
  "xlsx_include_styles": true,
  "xlsx_freeze_header": true,
  "xlsx_auto_width": true,
  "xlsx_conditional_formatting": {
    "status_column": {
      "pass": {"background": "#D4EDDA", "font_color": "#155724"},
      "warning": {"background": "#FFF3CD", "font_color": "#856404"},
      "fail": {"background": "#F8D7DA", "font_color": "#721C24"}
    }
  }
}

Scheduled Report Delivery

Creating a Report Schedule

Automate report generation and delivery:

curl -X POST https://api.calcbridge.io/api/v1/reports/schedules \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Monthly Trustee Report",
    "report_type": "compliance",
    "workbook_id": "550e8400-e29b-41d4-a716-446655440000",
    "format": "pdf",
    "schedule": "0 6 1 * *",
    "timezone": "America/New_York",
    "delivery": {
      "email": {
        "recipients": ["trustee@bank.com", "compliance@example.com"],
        "subject": "Monthly Compliance Report - CLO 2024-1",
        "body": "Please find attached the monthly compliance report."
      },
      "sftp": {
        "host": "sftp.trustee.com",
        "path": "/reports/",
        "credentials_id": "cred_xyz789"
      }
    },
    "enabled": true
  }'

Common Schedule Patterns

Use Case Schedule (Cron) Description
Daily 0 6 * * * Every day at 6 AM
Weekly 0 6 * * 1 Every Monday at 6 AM
Monthly 0 6 1 * * First of month at 6 AM
Quarterly 0 6 1 1,4,7,10 * First of quarter at 6 AM

Delivery Channels

Email Delivery:

{
  "email": {
    "recipients": ["trustee@bank.com"],
    "cc": ["internal@example.com"],
    "subject": "{{report_name}} - {{date}}",
    "body": "Please find attached the compliance report for {{workbook_name}}.",
    "attach_report": true
  }
}

SFTP Delivery:

{
  "sftp": {
    "host": "sftp.trustee.com",
    "port": 22,
    "path": "/reports/{{year}}/{{month}}/",
    "filename": "{{workbook_name}}_{{date}}.pdf",
    "credentials_id": "cred_xyz789"
  }
}

Cloud Storage:

{
  "s3": {
    "bucket": "your-reports-bucket",
    "path": "compliance/{{year}}/{{month}}/",
    "credentials_id": "aws_cred_123"
  }
}

Listing and Managing Reports

List All Reports

curl -X GET "https://api.calcbridge.io/api/v1/reports" \
  -H "Authorization: Bearer $TOKEN" \
  -d "skip=0&limit=20&report_type=compliance&sort_by=created_at&sort_dir=desc"

Response:

{
  "items": [
    {
      "id": "report_abc123",
      "report_type": "compliance",
      "status": "completed",
      "format": "pdf",
      "title": "Compliance Report - CLO 2024-1",
      "file_size_bytes": 245678,
      "download_url": "/api/v1/reports/report_abc123/download",
      "created_at": "2024-01-15T10:30:00Z",
      "completed_at": "2024-01-15T10:30:05Z",
      "error_message": null
    }
  ],
  "total": 45,
  "skip": 0,
  "limit": 20
}

Report Status Values

Status Description
pending Report generation queued
processing Report being generated
completed Report ready for download
failed Generation failed (see error_message)

Data Privacy and Redaction

PII Handling

CalcBridge automatically handles sensitive data in reports:

{
  "sanitize_pii": true,
  "classification_threshold": "CONFIDENTIAL",
  "redaction_strategy": "MASK"
}

Redaction Strategies:

Strategy Example Description
MASK ***-**-1234 Partial masking
REMOVE [REDACTED] Complete removal
HASH a1b2c3... One-way hash

Classification Levels

Level Includes
PUBLIC No redaction
INTERNAL Internal identifiers
CONFIDENTIAL Names, addresses
RESTRICTED SSN, account numbers

Best Practices

Report Generation Best Practices

  1. Generate reports after test runs - Ensure data is fresh

  2. Use consistent naming - Include date and deal name

  3. Archive reports - Maintain historical records for audit

  4. Review before distribution - Verify accuracy of key figures

  5. Use scheduled delivery - Automate routine reports

  6. Enable PII redaction - Protect sensitive data

Troubleshooting

Common Issues

Report generation fails:

  • Check that the workbook exists and has data
  • Verify you have permission to access the workbook
  • Review error message in response

Download returns 404:

  • Report may have expired (default: 7 days)
  • Report generation may have failed
  • Check report status before downloading

Formatting issues:

  • Verify export options are valid
  • Check date format strings
  • Ensure float_precision is appropriate

Ready to automate your trustee reporting?

Get Started with CalcBridge