Find Hidden Costs Eating 2-5% of Your Gold Trades

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Personal note: Learned this after trying to analyze trades with 
# inconsistent timestamps across three different systems
def load_trade_data(trade_file, market_data_file):
    """
    Load and merge trade executions with market data
    """
    # Load your trade executions
    trades = pd.read_csv(trade_file, parse_dates=['execution_time'])
    
    # Load market reference data (Bloomberg XAUUSD ticks)
    market = pd.read_csv(market_data_file, parse_dates=['timestamp'])
    
    # Merge on nearest timestamp within 1-second window
    # Watch out: Don't use exact match - market data updates every 100ms
    trades_merged = pd.merge_asof(
        trades.sort_values('execution_time'),
        market.sort_values('timestamp'),
        left_on='execution_time',
        right_on='timestamp',
        direction='nearest',
        tolerance=pd.Timedelta('1s')
    )
    
    return trades_merged

# Example usage
df = load_trade_data('gold_trades_oct.csv', 'xauusd_market_data.csv')
print(f"Loaded {len(df)} trades with market benchmarks")

def calculate_execution_costs(df):
    """
    Decompose total execution cost into components
    All costs in basis points (1 bp = 0.01%)
    """
    # Component 1: Quoted spread cost
    # Personal note: This is the only cost most traders track
    df['spread_cost_bp'] = (
        (df['execution_price'] - df['mid_price']) / df['mid_price'] * 10000
    ).abs()
    
    # Component 2: Timing cost (VWAP deviation)
    # Calculate what price WOULD have been at VWAP
    df['vwap_benchmark'] = df.groupby(df['execution_time'].dt.date)['mid_price'].transform('mean')
    df['timing_cost_bp'] = (
        (df['execution_price'] - df['vwap_benchmark']) / df['vwap_benchmark'] * 10000
    )
    
    # Component 3: Market impact (for large trades)
    # Watch out: Only applies to trades >$5M or >5% of hourly volume
    df['size_usd'] = df['quantity_oz'] * df['execution_price']
    df['market_impact_bp'] = np.where(
        df['size_usd'] > 5_000_000,
        (df['size_usd'] / 1_000_000) * 0.8,  # 0.8 bp per $1M
        0
    )
    
    # Component 4: Information leakage (price movement before execution)
    # Compare price 5min before trade request vs execution
    df['info_leakage_bp'] = (
        (df['execution_price'] - df['price_5min_before']) / df['price_5min_before'] * 10000
    ).clip(lower=0)  # Only count adverse movements
    
    # Component 5: Clearing and settlement (fixed per trade)
    df['clearing_cost_bp'] = 2.5  # Average LBMA clearing cost
    
    # Total all-in cost
    df['total_cost_bp'] = (
        df['spread_cost_bp'] + 
        df['timing_cost_bp'].abs() + 
        df['market_impact_bp'] + 
        df['info_leakage_bp'] + 
        df['clearing_cost_bp']
    )
    
    return df

# Apply to your data
df = calculate_execution_costs(df)

# Summary stats
print("\n=== Cost Breakdown (basis points) ===")
print(f"Average spread cost:        {df['spread_cost_bp'].mean():.2f} bp")
print(f"Average timing cost:        {df['timing_cost_bp'].abs().mean():.2f} bp")
print(f"Average market impact:      {df['market_impact_bp'].mean():.2f} bp")
print(f"Average info leakage:       {df['info_leakage_bp'].mean():.2f} bp")
print(f"Average clearing cost:      {df['clearing_cost_bp'].mean():.2f} bp")
print(f"─" * 45)
print(f"TOTAL AVERAGE COST:         {df['total_cost_bp'].mean():.2f} bp")
print(f"\nOn $20M traded: ${(df['total_cost_bp'].mean() / 10000 * 20_000_000):,.0f}")

def analyze_cost_drivers(df):
    """
    Find patterns in execution costs
    """
    # By counterparty/dealer
    print("\n=== Cost by Counterparty ===")
    counterparty_costs = df.groupby('counterparty').agg({
        'total_cost_bp': ['mean', 'std', 'count'],
        'size_usd': 'sum'
    }).round(2)
    counterparty_costs.columns = ['Avg Cost (bp)', 'Std Dev', 'Trades', 'Total Volume ($)']
    counterparty_costs = counterparty_costs.sort_values('Avg Cost (bp)', ascending=False)
    print(counterparty_costs.head())
    
    # By time of day (gold has specific liquidity patterns)
    df['hour'] = df['execution_time'].dt.hour
    print("\n=== Cost by Hour (NYC time) ===")
    hourly_costs = df.groupby('hour')['total_cost_bp'].agg(['mean', 'count'])
    hourly_costs.columns = ['Avg Cost (bp)', 'Trade Count']
    print(hourly_costs.round(2))
    
    # Flag outlier trades (>2 std dev above mean)
    cost_threshold = df['total_cost_bp'].mean() + (2 * df['total_cost_bp'].std())
    outliers = df[df['total_cost_bp'] > cost_threshold].sort_values('total_cost_bp', ascending=False)
    
    print(f"\n=== Top 5 Most Expensive Trades ===")
    print(f"(Out of {len(outliers)} outliers above {cost_threshold:.1f} bp threshold)")
    expensive_cols = ['execution_time', 'counterparty', 'size_usd', 'total_cost_bp', 'timing_cost_bp']
    print(outliers[expensive_cols].head().to_string())
    
    return counterparty_costs, hourly_costs, outliers

# Run the analysis
cp_costs, hr_costs, outliers = analyze_cost_drivers(df)

# Calculate potential savings
current_total = df['total_cost_bp'].mean() * df['size_usd'].sum() / 10000
best_performer_cost = cp_costs['Avg Cost (bp)'].min()
potential_total = best_performer_cost * df['size_usd'].sum() / 10000
potential_savings = current_total - potential_total

print(f"\n💡 If all trades matched best counterparty execution:")
print(f"   Current costs: ${current_total:,.0f}")
print(f"   Potential costs: ${potential_total:,.0f}")
print(f"   SAVINGS: ${potential_savings:,.0f} ({(potential_savings/current_total*100):.1f}%)")

def create_cost_report(df, output_file='gold_execution_report.xlsx'):
    """
    Generate executive summary for weekly review
    """
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        # Summary tab
        summary = pd.DataFrame({
            'Metric': [
                'Total Trades',
                'Total Volume ($)',
                'Avg Trade Size ($)',
                'Total All-in Cost ($)',
                'Total Cost (bp)',
                'Spread Cost (bp)',
                'Timing Cost (bp)',
                'Market Impact (bp)',
                'Info Leakage (bp)',
                'Clearing Cost (bp)'
            ],
            'Value': [
                len(df),
                f"${df['size_usd'].sum():,.0f}",
                f"${df['size_usd'].mean():,.0f}",
                f"${(df['total_cost_bp'].mean() / 10000 * df['size_usd'].sum()):,.0f}",
                f"{df['total_cost_bp'].mean():.2f}",
                f"{df['spread_cost_bp'].mean():.2f}",
                f"{df['timing_cost_bp'].abs().mean():.2f}",
                f"{df['market_impact_bp'].mean():.2f}",
                f"{df['info_leakage_bp'].mean():.2f}",
                f"{df['clearing_cost_bp'].mean():.2f}"
            ]
        })
        summary.to_excel(writer, sheet_name='Summary', index=False)
        
        # Counterparty analysis
        cp_costs, _, _ = analyze_cost_drivers(df)
        cp_costs.to_excel(writer, sheet_name='By Counterparty')
        
        # Time analysis
        _, hr_costs, _ = analyze_cost_drivers(df)
        hr_costs.to_excel(writer, sheet_name='By Hour')
        
        # Outlier trades
        _, _, outliers = analyze_cost_drivers(df)
        outliers[['execution_time', 'counterparty', 'size_usd', 'total_cost_bp']].to_excel(
            writer, sheet_name='Outlier Trades', index=False
        )
    
    print(f"\n✅ Report saved to {output_file}")
    print(f"   Share with trading desk for weekly review")

# Generate the report
create_cost_report(df)