Trading Day Calendar and Non-Trading Day Handling

Overview

Sable tracks US market trading days via the sable.calendar_date table. This is critical for returns calculations because P&L data exists for every calendar day, but returns should only be calculated for actual trading days.

The Calendar Table

sable.calendar_date

Column	Type	Description
calendar_date	DATE	The date
is_weekend	BOOLEAN	Saturday or Sunday
is_market_holiday	BOOLEAN	NYSE market holiday
is_business_day	BOOLEAN	Actual trading day (not weekend, not holiday)
holiday_name	TEXT	e.g., "Christmas Day", "Thanksgiving Day"

US market holidays are tracked through 2030.

Business Day Mapping

sable.mv_business_day_mapping - Materialized view for fast prior business day lookups:

SELECT
    calendar_date AS current_date,
    LAG(calendar_date) OVER (ORDER BY calendar_date) AS prior_business_day
FROM sable.calendar_date
WHERE is_business_day = TRUE
  AND deleted_at IS NULL

Refresh when new business days are added:

REFRESH MATERIALIZED VIEW sable.mv_business_day_mapping;

The Problem: P&L on Non-Trading Days

sable.pnl_daily_v2 contains entries for every calendar day, including weekends and holidays. On non-trading days:

• daily_pnl is often 0, but not always
• Dividends, corporate actions, and FX adjustments can occur
• Including these as "0% return days" corrupts calculations

The Solution: Sum P&L Across Gaps

For a 3-day weekend (Friday holiday):

Day	Trading?	P&L
Thursday	Yes	Thursday's P&L
Friday	No (holiday)	May have dividends
Saturday	No	Usually 0
Sunday	No	Usually 0
Monday	Yes	Monday's P&L

Correct Thursday→Monday return:

Return = (Fri_PnL + Sat_PnL + Sun_PnL + Mon_PnL) / Thursday_End_MV

Implementation Pattern

WITH trading_days AS (
  SELECT calendar_date
  FROM sable.calendar_date
  WHERE is_business_day = true
    AND calendar_date >= '2023-01-01'
),
trading_day_ranges AS (
  SELECT 
    calendar_date as trading_date,
    -- Range starts day after prior trading day
    LAG(calendar_date) OVER (ORDER BY calendar_date) + 1 as pnl_start_date,
    calendar_date as pnl_end_date
  FROM trading_days
)
SELECT 
  r.trading_date,
  p.entity_id,
  SUM(p.daily_pnl) as period_pnl,
  -- Denominator is prior trading day's ending market value
  prior_nav.nav as denominator
FROM trading_day_ranges r
JOIN sable.pnl_daily_v2 p 
  ON p.pnl_date BETWEEN r.pnl_start_date AND r.pnl_end_date
  AND p.is_active = TRUE
LEFT JOIN sable.nav_daily_v2 prior_nav
  ON prior_nav.entity_id = p.entity_id
  AND prior_nav.pnl_date = r.pnl_start_date - 1
GROUP BY r.trading_date, p.entity_id, prior_nav.nav

Key Principles

1. Identify trading days from sable.calendar_date where is_business_day = true
2. Sum ALL P&L from (prior_trading_day + 1) through current_trading_day
3. Divide by prior trading day's ending market value
4. No P&L is lost - dividends, corporate actions, adjustments all captured

Alternative: Use BTIG Performance Data

sable.perf_daily_v2 contains pre-calculated returns from BTIG that already handle trading days correctly. However, this only covers 4 accounts (ARP, AV7K, BFF, XPB006152).

For full account coverage, use the pattern above with pnl_daily_v2 + calendar_date.

Related Tables

Table	Description
sable.calendar_date	Trading day calendar
sable.mv_business_day_mapping	Prior business day lookup
sable.pnl_daily_v2	Daily P&L (all calendar days)
sable.perf_daily_v2	BTIG performance (trading days only)
gold.dietz_daily	Returns calculations