Skip to content

data-model.md

Latest commit

 

History

History
551 lines (500 loc) · 22.5 KB

data-model.md

File metadata and controls

551 lines (500 loc) · 22.5 KB
Raw

OpenTradeLab Data Model Specification

1. Input CSV Format Specification

The CSV transaction log must be a UTF-8 encoded text file with a header row containing the following mandatory fields:

Field Name Type Description Example Constraints
transaction_id String Unique transaction identifier TXN-001 Non-empty, unique within the dataset
timestamp String Execution date and time 2026-01-15T09:30:00Z Valid ISO-8601 string, normalized to UTC
symbol String Financial asset ticker AAPL Non-empty alphanumeric string
direction String Order execution side BUY Case-insensitive; must be BUY or SELL
quantity Decimal Number of asset units 10 Positive decimal number ($> 0$)
price Decimal Unit price in execution currency 150.25 Positive decimal number ($> 0$)
fee Decimal Execution commission / fee 1.50 Non-negative decimal number ($\ge 0$); defaults to 0.00 if omitted
currency String Three-letter currency code USD 3-character uppercase ISO currency code

Synthetic CSV Example

transaction_id,timestamp,symbol,direction,quantity,price,fee,currency
TXN-1001,2026-01-15T09:30:00Z,AAPL,BUY,100,150.00,1.00,USD
TXN-1002,2026-01-15T10:00:00Z,MSFT,BUY,50,300.00,1.50,USD
TXN-1003,2026-01-15T14:30:00Z,AAPL,SELL,60,155.00,1.00,USD
TXN-1004,2026-01-16T11:00:00Z,MSFT,SELL,50,295.00,1.50,USD

1.1 Historical Market Quote CSV Format Specification

The historical market quote CSV file contains historical closing prices for portfolio valuation:

Field Name Type Description Example Constraints
symbol String Financial asset ticker AAPL Non-empty alphanumeric string
timestamp String Quote date and time 2026-01-16T16:00:00Z Valid ISO-8601 string, normalized to UTC
close_price Decimal Closing market price 160.00 Positive decimal number ($> 0$)
currency String Three-letter currency code USD 3-character uppercase ISO currency code

Synthetic Quote Example

symbol,timestamp,close_price,currency
AAPL,2026-01-15T16:00:00Z,154.50,USD
AAPL,2026-01-16T16:00:00Z,160.00,USD
MSFT,2026-01-16T16:00:00Z,312.00,USD
AMZN,2026-01-16T16:00:00Z,175.00,USD
GOOG,2026-01-16T16:00:00Z,175.00,USD

1.2 Cash Flow CSV Format Specification

The cash flow CSV file specifies external cash events (opening balances, deposits, and withdrawals):

Field Name Type Description Example Constraints
event_id String Unique cash event identifier CASH-001 Non-empty, unique string
timestamp String Effective date and time 2026-01-15T08:00:00Z Valid ISO-8601 string, normalized to UTC
event_type String Nature of cash movement OPENING_BALANCE Must be OPENING_BALANCE, DEPOSIT, or WITHDRAWAL
amount Decimal Magnitude of cash movement 5000.00 Positive decimal number ($> 0$)
currency String Three-letter currency code USD 3-character uppercase ISO currency code
description String Optional movement description Deposit Freeform text

Synthetic Cash Flow Example

event_id,timestamp,event_type,amount,currency,description
CASH-001,2026-01-15T08:00:00Z,OPENING_BALANCE,100000.00,USD,Initial cash balance
CASH-002,2026-01-16T08:00:00Z,DEPOSIT,5000.00,USD,Additional funding
CASH-003,2026-01-16T15:00:00Z,WITHDRAWAL,2000.00,USD,Owner withdrawal

1.3 Corporate Actions CSV Format Specification

The corporate actions CSV file specifies cash dividend declarations and stock split events:

Field Name Type Description Example Constraints
event_id String Unique event identifier DIV-001 Non-empty unique string
timestamp String Ex-date for dividend or effective split date 2026-05-01T12:00:00Z Valid ISO-8601 string, normalized to UTC
event_type String Type of corporate action CASH_DIVIDEND Must be CASH_DIVIDEND or STOCK_SPLIT
symbol String Asset symbol AAPL Non-empty uppercase alphanumeric string
amount Decimal Dividend per share or split ratio R 0.50 Positive decimal number ($> 0$)
currency String Currency code USD 3-letter ISO code (mandatory for dividends)
payment_date String Cash payment date for dividends 2026-05-02T14:00:00Z Required for dividends; must be $\ge$ timestamp
description String Optional description Q1 dividend Freeform text

Synthetic Corporate Actions Example

event_id,timestamp,event_type,symbol,amount,currency,payment_date,description
DIV-001,2026-05-01T12:00:00Z,CASH_DIVIDEND,AAPL,0.50,USD,2026-05-02T14:00:00Z,AAPL Q1 Dividend
SPLIT-001,2026-05-02T09:00:00Z,STOCK_SPLIT,AMZN,2.0,USD,,AMZN 2-for-1 Forward Split

2. Validation Taxonomy & Error Handling

Invalid input rows are never silently dropped. Each invalid record produces a structured error record containing:

  • row_number: 1-based index corresponding to the line in the input file (header is row 1, first data row is 2).
  • transaction_id: Identifier if parseable, or null.
  • field: The specific field that failed validation, or row for structural failures.
  • error_type: Standardized machine-readable error code.
  • message: Diagnostic description explaining the rejection.
  • raw_data: Key-value map of the unparsed row strings.

Error Codes

  • MISSING_FIELD: A required column is missing from the header or a row contains an empty required cell.
  • INVALID_TIMESTAMP: The timestamp cannot be parsed as a valid ISO-8601 datetime.
  • INVALID_DIRECTION: The direction is not BUY or SELL.
  • INVALID_NUMERIC: Quantity, price, or fee cannot be parsed as a valid decimal number.
  • NON_POSITIVE_VALUE: Quantity or price is $\le 0$.
  • NEGATIVE_FEE: Fee is $< 0$.
  • DUPLICATE_TRANSACTION_ID: The transaction_id has already been observed in a preceding row.
  • INVALID_CURRENCY: The currency is missing or does not conform to the 3-letter ISO format.
  • INSUFFICIENT_HOLDINGS: A SELL transaction specifies a quantity greater than currently available in long inventory.
  • CURRENCY_MISMATCH: A transaction or quote currency does not match the configured portfolio base currency.
  • DUPLICATE_QUOTE: A quote with the exact same (symbol, timestamp) key was already observed in a preceding row.
  • DUPLICATE_EVENT_ID: A cash event ID has already been observed in a preceding row.
  • DUPLICATE_OPENING_BALANCE: More than one OPENING_BALANCE event was supplied (only one opening balance is permitted).
  • INVALID_EVENT_TYPE: An unrecognized or unsupported cash event type was provided.
  • INSUFFICIENT_CASH: A cash outflow (BUY trade or WITHDRAWAL) exceeds the available cash balance in a cash account.
  • DUPLICATE_CORPORATE_ACTION: A corporate action for the same symbol and effective date was already observed.
  • INVALID_DATE_SEQUENCE: A dividend payment_date is earlier than its ex-dividend date.

3. Core Analytical Models

Transaction

  • transaction_id: str
  • timestamp: datetime (UTC)
  • symbol: str (uppercase)
  • direction: Literal["BUY", "SELL"]
  • quantity: Decimal
  • price: Decimal
  • fee: Decimal
  • currency: str (uppercase)
  • row_number: int (1-based source CSV line index for error traceability)

Quote

  • symbol: str (uppercase)
  • timestamp: datetime (UTC)
  • close_price: Decimal ($> 0$)
  • currency: str (uppercase 3-letter ISO)
  • row_number: int (1-based source CSV line index for error traceability)

Trade (Closed Execution Lot)

  • symbol: str
  • buy_transaction_id: str
  • sell_transaction_id: str
  • quantity: Decimal
  • buy_price: Decimal
  • sell_price: Decimal
  • buy_fee: Decimal
  • sell_fee: Decimal
  • gross_pnl: Decimal ($(\text{sell_price} - \text{buy_price}) \times \text{quantity}$)
  • net_pnl: Decimal ($\text{gross_pnl} - \text{buy_fee} - \text{sell_fee}$)
  • return_pct: Decimal ($\frac{\text{net_pnl}}{\text{buy_price} \times \text{quantity} + \text{buy_fee}} \times 100$)

Position (Ending Open Inventory)

  • symbol: str
  • quantity: Decimal
  • cost_basis: Decimal
  • average_cost_per_share: Decimal

PortfolioMetrics

  • total_transactions: int (count of valid transactions processed)
  • total_buys: int
  • total_sells: int
  • total_volume: Decimal ($\sum \text{quantity} \times \text{price}$)
  • total_fees: Decimal ($\sum \text{fee}$)
  • realized_pnl: Decimal ($\sum \text{net_pnl}$)
  • closed_trades_count: int
  • winning_trades_count: int (trades with net_pnl > 0)
  • losing_trades_count: int (trades with net_pnl < 0)
  • breakeven_trades_count: int (trades with net_pnl == 0)
  • win_rate: Decimal | None ($\frac{\text{winning_trades}}{\text{closed_trades}}$)
    • If closed_trades_count == 0: None (null in JSON).
    • If closed_trades_count > 0: rounded to 4 decimal places (e.g. 0.5000 = 50.00%).
  • profit_factor: Decimal | None ($\frac{\sum_{\text{winning}} \text{net_pnl}}{\sum_{\text{losing}} |\text{net_pnl}|}$)
    • Net Profit Factor: Ratio of aggregate net gains from winning trades to aggregate absolute net losses from losing trades.
    • If gross_losses > 0: rounded to 2 decimal places.
    • If gross_losses == 0 and gross_gains > 0: None (null in JSON, represented as N/A).
    • If gross_losses == 0 and gross_gains == 0: None (null in JSON).
    • If gross_losses > 0 and gross_gains == 0: Decimal("0.00") ("0.00" in JSON).
  • open_positions: list[Position] (sorted deterministically by symbol)

ValuedPosition

  • symbol: str
  • quantity: Decimal
  • cost_basis: Decimal
  • average_cost_per_share: Decimal
  • market_price: Decimal | None
  • quote_timestamp: datetime | None
  • quote_age_seconds: int | None
  • market_value: Decimal | None ($\text{quantity} \times \text{market_price}$)
  • unrealized_pnl: Decimal | None ($\text{market_value} - \text{cost_basis}$)
  • unrealized_return_pct: Decimal | None ($\frac{\text{unrealized_pnl}}{\text{cost_basis}} \times 100$)
  • status: Literal["OK", "MISSING_QUOTE", "STALE_QUOTE", "CURRENCY_MISMATCH"]
  • warnings: list[str]

PortfolioValuation

  • valuation_at: datetime (UTC point-in-time)
  • status: Literal["COMPLETE", "INCOMPLETE", "NO_OPEN_POSITIONS"]
  • max_quote_age_seconds: int (staleness threshold)
  • total_positions_market_value: Decimal | None (sum of position market values when complete)
  • total_unrealized_pnl: Decimal | None (sum of position unrealized P&L when complete)
  • positions: list[ValuedPosition]
  • warnings: list[str]
  • disclaimer: str

CashLedgerSummary

  • currency: str
  • opening_balance: Decimal | None
  • opening_timestamp: datetime | None
  • ending_balance: Decimal (point-in-time cash balance)
  • as_of: datetime
  • total_deposits: Decimal
  • total_withdrawals: Decimal
  • total_buy_outflows: Decimal
  • total_sell_inflows: Decimal
  • total_fees_paid: Decimal
  • net_cash_flow: Decimal (period change: $\text{ending_balance} - \text{opening_balance}$)
  • entries_count: int
  • status: Literal["COMPLETE", "DEFICIT"]

PortfolioEquity

  • as_of: datetime
  • status: Literal["COMPLETE", "INCOMPLETE", "CASH_UNMODELED"]
  • currency: str
  • cash_balance: Decimal | None
  • positions_market_value: Decimal | None
  • portfolio_equity: Decimal | None ($\text{cash_balance} + \text{positions_market_value}$)
  • realized_pnl: Decimal
  • unrealized_pnl: Decimal | None
  • warnings: list[str]
  • disclaimer: str

CashDividend

  • event_id: str
  • symbol: str
  • ex_date: datetime (UTC)
  • payment_date: datetime (UTC, $\ge \text{ex_date}$)
  • amount_per_share: Decimal ($&gt; 0$)
  • currency: str (uppercase 3-letter ISO)

StockSplit

  • event_id: str
  • symbol: str
  • timestamp: datetime (UTC)
  • split_ratio: Decimal ($&gt; 0$, $R &gt; 1$ for forward split, $R &lt; 1$ for reverse split)

CorporateActionsSummary

  • total_dividend_income: Decimal (sum of paid dividends $\le \text{as_of}$)
  • pending_dividend_receivable: Decimal (sum of entitled dividends where $\text{ex_date} \le \text{as_of} &lt; \text{payment_date}$)
  • dividends: list[ProcessedDividend]
  • splits: list[ProcessedStockSplit]
  • warnings: list[str]

SubperiodReturn

  • start_date: datetime (UTC)
  • end_date: datetime (UTC)
  • start_equity: Decimal
  • end_equity_pre_flow: Decimal
  • external_cash_flow: Decimal
  • end_equity_post_flow: Decimal
  • subperiod_return_pct: Decimal

TimeWeightedReturn

  • status: Literal["COMPLETE", "INCOMPLETE", "CASH_UNMODELED", "INSUFFICIENT_DATA"]
  • twr_pct: Decimal | None
  • annualized_twr_pct: Decimal | None
  • subperiods_count: int
  • subperiods: list[SubperiodReturn]
  • warnings: list[str]

MoneyWeightedReturn

  • status: Literal["COMPLETE", "INCOMPLETE", "CASH_UNMODELED", "INSUFFICIENT_DATA"]
  • mwr_pct: Decimal | None (annualized XIRR)
  • cash_flows_count: int
  • terminal_value: Decimal | None
  • converged: bool
  • iterations: int
  • warnings: list[str]

DrawdownMetrics

  • status: Literal["COMPLETE", "INCOMPLETE", "INSUFFICIENT_DATA"]
  • max_drawdown_pct: Decimal | None
  • peak_date: datetime | None
  • peak_equity: Decimal | None
  • trough_date: datetime | None
  • trough_equity: Decimal | None
  • recovery_date: datetime | None
  • current_drawdown_pct: Decimal | None
  • series: list[DrawdownPoint]
  • warnings: list[str]

CapitalReconciliation

  • opening_cash: Decimal | None
  • net_deposits_withdrawals: Decimal
  • total_external_capital: Decimal
  • realized_trade_pnl: Decimal
  • dividend_income: Decimal
  • gross_unrealized_pnl: Decimal | None
  • open_lot_acquisition_fees: Decimal
  • reconciled_equity: Decimal | None
  • reported_equity: Decimal | None
  • reconciliation_difference: Decimal | None
  • is_balanced: bool
  • explanation: str

PortfolioPerformanceReport

  • as_of: datetime (UTC)
  • status: Literal["COMPLETE", "INCOMPLETE", "CASH_UNMODELED"]
  • time_weighted_return: TimeWeightedReturn
  • money_weighted_return: MoneyWeightedReturn
  • drawdown: DrawdownMetrics
  • capital_reconciliation: CapitalReconciliation
  • warnings: list[str]

BenchmarkObservation

  • benchmark_id: str (non-empty identifier, e.g., "SPY", "SPX")
  • timestamp: datetime (UTC normalized)
  • close_level: Decimal (strictly positive, e.g., 485.00)
  • currency: str (ISO 3-letter currency code, e.g., "USD")
  • is_total_return: bool (indicates whether observation series includes reinvested dividends)
  • row_number: int (1-indexed CSV line number)

BenchmarkComparisonReport

  • benchmark_id: str
  • status: Literal["COMPLETE", "INCOMPLETE", "INSUFFICIENT_DATA", "CURRENCY_MISMATCH"]
  • measurement_interval_start: datetime | None
  • measurement_interval_end: datetime | None
  • benchmark_start_level: Decimal | None
  • benchmark_end_level: Decimal | None
  • benchmark_return_pct: Decimal | None
  • portfolio_twr_pct: Decimal | None
  • excess_return_pct: Decimal | None
  • beta: Decimal | None
  • alpha_periodic_pct: Decimal | None
  • alpha_annualized_pct: Decimal | None
  • observations_count: int
  • return_type_label: str ("TOTAL_RETURN" or "PRICE_RETURN")
  • warnings: list[str]
  • disclaimer: str

PeriodFrequency

  • DAILY = "DAILY"
  • MONTHLY = "MONTHLY"

PeriodReturnStatus

  • COMPLETE = "COMPLETE"
  • INCOMPLETE = "INCOMPLETE"
  • INSUFFICIENT_DATA = "INSUFFICIENT_DATA"
  • MISSING_DATA = "MISSING_DATA"
  • STALE_DATA = "STALE_DATA"
  • CURRENCY_MISMATCH = "CURRENCY_MISMATCH"
  • MISMATCHED_INTERVAL = "MISMATCHED_INTERVAL"

PeriodReturnObservation

  • period_start: datetime (UTC)
  • period_end: datetime (UTC)
  • return_pct: Decimal | None
  • currency: str
  • status: PeriodReturnStatus
  • source: str ("PORTFOLIO" or "BENCHMARK")
  • metadata: dict[str, Any]
  • warnings: list[str]

PeriodReturnSeries

  • frequency: PeriodFrequency
  • series_type: str ("PORTFOLIO" or "BENCHMARK")
  • observations: list[PeriodReturnObservation]
  • status: PeriodReturnStatus
  • currency: str
  • benchmark_id: str | None
  • return_type_label: str | None
  • missing_periods_count: int
  • warnings: list[str]

AlignedPeriodReturnObservation

  • period_start: datetime (UTC)
  • period_end: datetime (UTC)
  • portfolio_return_pct: Decimal | None
  • benchmark_return_pct: Decimal | None
  • excess_return_pct: Decimal | None
  • status: PeriodReturnStatus
  • portfolio_status: PeriodReturnStatus
  • benchmark_status: PeriodReturnStatus
  • warnings: list[str]

AlignedPeriodReturnSeries

  • frequency: PeriodFrequency
  • currency: str
  • benchmark_id: str
  • return_type_label: str
  • observations: list[AlignedPeriodReturnObservation]
  • status: PeriodReturnStatus
  • aligned_observations_count: int
  • missing_portfolio_periods_count: int
  • missing_benchmark_periods_count: int
  • warnings: list[str]

PeriodicReturnReport

  • frequency: PeriodFrequency
  • portfolio_series: PeriodReturnSeries
  • benchmark_series: PeriodReturnSeries | None
  • aligned_series: AlignedPeriodReturnSeries | None
  • status: PeriodReturnStatus
  • warnings: list[str]

RiskMetricsStatus

  • COMPLETE = "COMPLETE"
  • INCOMPLETE = "INCOMPLETE"
  • INSUFFICIENT_DATA = "INSUFFICIENT_DATA"
  • INSOLVENT = "INSOLVENT"
  • ZERO_VOLATILITY = "ZERO_VOLATILITY"
  • ZERO_DOWNSIDE = "ZERO_DOWNSIDE"
  • ZERO_DRAWDOWN = "ZERO_DRAWDOWN"
  • CURRENCY_MISMATCH = "CURRENCY_MISMATCH"
  • MIXED_FREQUENCY = "MIXED_FREQUENCY"

HistoricalVaRMetrics

  • confidence_level: Decimal
  • tail_probability: Decimal
  • holding_period_days: int
  • var_pct: Decimal | None
  • var_return_pct: Decimal | None
  • expected_shortfall_pct: Decimal | None
  • tail_observations_count: int
  • status: RiskMetricsStatus
  • warnings: list[str]

PortfolioRiskReport

  • status: RiskMetricsStatus
  • frequency: PeriodFrequency
  • annualization_factor: int
  • risk_free_rate_ann_pct: Decimal
  • target_mar_ann_pct: Decimal
  • sortino_denominator_convention: str ("N")
  • observations_total: int
  • observations_used: int
  • partial_observations_excluded: int
  • invalid_observations_excluded: int
  • mean_return_periodic_pct: Decimal | None
  • mean_return_annualized_pct: Decimal | None
  • volatility_periodic_pct: Decimal | None
  • volatility_annualized_pct: Decimal | None
  • downside_deviation_periodic_pct: Decimal | None
  • downside_deviation_annualized_pct: Decimal | None
  • downside_deviation_n1_periodic_pct: Decimal | None
  • sharpe_ratio: Decimal | None
  • sortino_ratio: Decimal | None
  • sortino_ratio_n1: Decimal | None
  • calmar_ratio: Decimal | None
  • max_twr_drawdown_pct: Decimal | None
  • cagr_pct: Decimal | None
  • calendar_days: float | None
  • var_95: HistoricalVaRMetrics | None
  • var_99: HistoricalVaRMetrics | None
  • warnings: list[str]
  • disclaimers: list[str]

4. JSON Report Schema Specification (schema_version: "1.0.0")

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "OpenTradeLabPortfolioReport",
  "type": "object",
  "properties": {
    "schema_version": { "type": "string", "enum": ["1.0.0"] },
    "generated_at": { "type": "string", "format": "date-time" },
    "source_file": { "type": "string" },
    "summary": {
      "type": "object",
      "properties": {
        "total_records_processed": { "type": "integer" },
        "valid_records_count": { "type": "integer" },
        "invalid_records_count": { "type": "integer" }
      },
      "required": ["total_records_processed", "valid_records_count", "invalid_records_count"]
    },
    "portfolio": {
      "type": "object",
      "properties": {
        "base_currency": { "type": "string" },
        "metrics": {
          "type": "object",
          "properties": {
            "total_transactions": { "type": "integer" },
            "total_buys": { "type": "integer" },
            "total_sells": { "type": "integer" },
            "total_volume": { "type": "string" },
            "total_fees": { "type": "string" },
            "realized_pnl": { "type": "string" },
            "closed_trades_count": { "type": "integer" },
            "winning_trades_count": { "type": "integer" },
            "losing_trades_count": { "type": "integer" },
            "breakeven_trades_count": { "type": "integer" },
            "win_rate": { "type": ["string", "null"] },
            "profit_factor": { "type": ["string", "null"] }
          },
          "required": [
            "total_transactions",
            "total_buys",
            "total_sells",
            "total_volume",
            "total_fees",
            "realized_pnl",
            "closed_trades_count",
            "winning_trades_count",
            "losing_trades_count",
            "breakeven_trades_count"
          ]
        },
        "open_positions": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": {
              "symbol": { "type": "string" },
              "quantity": { "type": "string" },
              "cost_basis": { "type": "string" },
              "average_cost_per_share": { "type": "string" }
            },
            "required": ["symbol", "quantity", "cost_basis", "average_cost_per_share"]
          }
        }
      },
      "required": ["base_currency", "metrics", "open_positions"]
    },
    "validation_errors": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "row_number": { "type": "integer" },
          "transaction_id": { "type": ["string", "null"] },
          "field": { "type": "string" },
          "error_type": { "type": "string" },
          "message": { "type": "string" },
          "raw_data": { "type": "object" }
        },
        "required": ["row_number", "field", "error_type", "message", "raw_data"]
      }
    },
    "valuation": { "type": "object" },
    "cash_ledger": { "type": "object" },
    "equity": { "type": "object" },
    "corporate_actions": { "type": "object" },
    "performance": { "type": "object" },
    "benchmark": { "type": "object" },
    "periodic_returns": { "type": "object" },
    "risk": { "type": "object" }
  },
  "required": ["schema_version", "generated_at", "source_file", "summary", "portfolio", "validation_errors"]
}