A nightly AI report that summarizes my bot’s trades and risk

— Represents individual fills from execution venues
CREATE TABLE executions (
id SERIAL PRIMARY KEY,
strategy_id VARCHAR(50) NOT NULL,
symbol VARCHAR(20) NOT NULL,
side VARCHAR(10) NOT NULL, — 'BUY' or 'SELL'
quantity NUMERIC NOT NULL,
price NUMERIC NOT NULL,
arrival_price NUMERIC NOT NULL, — Price of book mid when signal generated
fee NUMERIC NOT NULL,
executed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

— Captured every 5 minutes to analyze intra-day risk metrics
CREATE TABLE portfolio_snapshots (
id SERIAL PRIMARY KEY,
timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
total_equity NUMERIC NOT NULL,
margin_used NUMERIC NOT NULL,
free_margin NUMERIC NOT NULL,
unrealized_pnl NUMERIC NOT NULL,
positions JSONB NOT NULL — Holds active positions: {"BTC-USDT": 1.2, "SOL-USDT": -45.0}
);

import os
import sys
import json
from datetime import datetime, timedelta
import pandas as pd
import numpy as np
import psycopg2
from pydantic import BaseModel, Field
from openai import OpenAI

# Define structured output schema for the AI Report
class RiskAssessment(BaseModel):
risk_level: str = Field(description="System risk level: GREEN, YELLOW, or RED")
leverage_warning: bool = Field(description="True if margin usage exceeded 60% of total equity")
anomalous_behavior: str = Field(description="Description of any anomalous trading behavior, or 'None'")

class TradeSummary(BaseModel):
executive_summary: str = Field(description="A concise 2-3 sentence overview of the day's performance.")
execution_quality_analysis: str = Field(description="Analysis of slippage, execution speed, and fee impact.")
risk_and_drawdown_analysis: str = Field(description="Analysis of intra-day exposure, margin utilization, and drawdowns.")
action_items: list[str] = Field(description="Specific, actionable suggestions for system tuning or risk mitigation.")

class NightlyReportPayload(BaseModel):
metadata: dict = Field(description="Date, run time, and strategy identifiers.")
assessment: RiskAssessment
summary: TradeSummary

# Database Extraction Layer
def fetch_trading_data(conn_str: str, lookback_hours: int = 24) -> tuple[pd.DataFrame, pd.DataFrame]:
start_time = datetime.utcnow() – timedelta(hours=lookback_hours)

exec_query = """
SELECT strategy_id, symbol, side, quantity, price, arrival_price, fee, executed_at
FROM executions
WHERE executed_at >= %s;
"""

snapshot_query = """
SELECT timestamp, total_equity, margin_used, free_margin, unrealized_pnl, positions
FROM portfolio_snapshots
WHERE timestamp >= %s
ORDER BY timestamp ASC;
"""

with psycopg2.connect(conn_str) as conn:
df_execs = pd.read_sql_query(exec_query, conn, params=(start_time,))
df_snapshots = pd.read_sql_query(snapshot_query, conn, params=(start_time,))

return df_execs, df_snapshots

# Analytics Engine
def compute_metrics(df_execs: pd.DataFrame, df_snapshots: pd.DataFrame) -> dict:
if df_execs.empty:
return {"error": "No executions found in the last 24 hours."}

# Calculate execution-level metrics (Slippage in basis points)
# Slippage = (Executed Price – Arrival Price) / Arrival Price (adjusted for side)
df_execs['slippage_pct'] = (df_execs['price'] – df_execs['arrival_price']) / df_execs['arrival_price']
df_execs.loc[df_execs['side'] == 'SELL', 'slippage_pct'] = –df_execs['slippage_pct']
df_execs['slippage_bps'] = df_execs['slippage_pct'] * 10000

total_slippage_usd = (df_execs['price'] – df_execs['arrival_price']) * df_execs['quantity']
total_slippage_usd = total_slippage_usd.where(df_execs['side'] == 'BUY', –total_slippage_usd).sum()

mean_slippage_bps = df_execs['slippage_bps'].mean()
total_fees = df_execs['fee'].sum()
total_volume = (df_execs['price'] * df_execs['quantity']).sum()

# Analyze portfolio risk and drawdowns from snapshots
if not df_snapshots.empty:
df_snapshots['total_equity'] = df_snapshots['total_equity'].astype(float)
df_snapshots['margin_used'] = df_snapshots['margin_used'].astype(float)

peak_equity = df_snapshots['total_equity'].cummax()
drawdowns = (df_snapshots['total_equity'] – peak_equity) / peak_equity
max_drawdown = drawdowns.min() * 100 # percentage

peak_margin_utilization = (df_snapshots['margin_used'] / df_snapshots['total_equity']).max() * 100
ending_equity = df_snapshots['total_equity'].iloc[–1]
starting_equity = df_snapshots['total_equity'].iloc[0]
net_pnl = ending_equity – starting_equity
net_pnl_pct = (net_pnl / starting_equity) * 100
else:
max_drawdown = 0.0
peak_margin_utilization = 0.0
net_pnl = 0.0
net_pnl_pct = 0.0
ending_equity = 0.0

return {
"trade_count": len(df_execs),
"total_volume_usd": float(total_volume),
"net_pnl_usd": float(net_pnl),
"net_pnl_pct": float(net_pnl_pct),
"max_drawdown_pct": float(max_drawdown),
"peak_margin_utilization_pct": float(peak_margin_utilization),
"average_slippage_bps": float(mean_slippage_bps),
"total_slippage_usd": float(total_slippage_usd),
"total_fees_usd": float(total_fees),
"ending_equity": float(ending_equity)
}

# LLM Orchestrator
def generate_ai_report(metrics: dict, raw_execs_summary: str) -> NightlyReportPayload:
client = OpenAI(api_key=os.environ.get("OPENAI_API_KEY"))

prompt = f"""
You are an expert quantitative risk manager and execution analyst.
Review the following trading performance and risk metrics calculated from our systems over the last 24 hours.

=== SYSTEM METRICS ===
{json.dumps(metrics, indent=2)}

=== RAW EXECUTION SAMPLE ===
{raw_execs_summary}

Provide an analytical trade summary and risk reporting breakdown.
Be direct, clinical, and precise. Focus on execution inefficiencies, excess risk taking, margin issues, or systemic issues.
Do not use generic fluff. Refer to specific values where appropriate.
"""

response = client.beta.chat.completions.parse(
model="gpt-4o-2024-08-06",
messages=[
{"role": "system", "content": "You analyze algorithmic trading system executions and output clean, structured risk profiles."},
{"role": "user", "content": prompt}
],
response_format=NightlyReportPayload
)

return response.choices[0].message.parsed

# Helper to format output to beautiful Markdown
def compile_markdown_report(report: NightlyReportPayload) -> str:
risk_emoji = "🟢" if report.assessment.risk_level == "GREEN" else "🟡" if report.assessment.risk_level == "YELLOW" else "🔴"

md = f"""# {risk_emoji} Nightly AI Trading & Risk Report
**Date:** {report.metadata.get('date', datetime.utcnow().strftime('%Y-%m-%d'))} | **Risk Status:** {report.assessment.risk_level}

## Executive Summary
{report.summary.executive_summary}

—

## Risk & Execution Health Check
* **Leverage Warning Triggered:** `{"YES" if report.assessment.leverage_warning else "NO"}`
* **Anomalous Behavior Flagged:** *{report.assessment.anomalous_behavior}*

### Execution Quality Analysis
{report.summary.execution_quality_analysis}

### Portfolio & Risk Analysis
{report.summary.risk_and_drawdown_analysis}

—

## Action Items
{chr(10).join([f"- [ ] {item}" for item in report.summary.action_items])}
"""
return md

if __name__ == "__main__":
# Example execution configuration
DATABASE_URL = os.environ.get("DATABASE_URL", "postgresql://trader:password@localhost:5432/trading_db")

try:
raw_execs, raw_snapshots = fetch_trading_data(DATABASE_URL)
computed = compute_metrics(raw_execs, raw_snapshots)

# Prepare a highly condensed version of raw executions for LLM context context reduction
sample_cols = ['strategy_id', 'symbol', 'side', 'quantity', 'price', 'arrival_price']
exec_summary = raw_execs[sample_cols].head(25).to_string() if not raw_execs.empty else "No trades"

ai_payload = generate_ai_report(computed, exec_summary)
ai_payload.metadata = {"date": datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S UTC")}

markdown_output = compile_markdown_report(ai_payload)

# Output directly to stdout for cron capture or notification pipelining
print(markdown_output)

except Exception as e:
print(f"CRITICAL ERROR generating nightly report: {str(sys.exc_info())}", file=sys.stderr)
sys.exit(1)

# 🟡 Nightly AI Trading & Risk Report
**Date:** 2024-10-27 00:00:12 UTC | **Risk Status:** YELLOW

## Executive Summary
Today's trading closed with a net profit of $1,421.12 (+1.34%), but this was accompanied by severe execution slippage in mid-cap tokens and an unacceptable max drawdown of -4.12% during the 14:00 UTC market cascade. While raw PnL is positive, structural execution issues and extreme leverage spikes warrant warning flags.

—

## Risk & Execution Health Check
* **Leverage Warning Triggered:** `YES`
* **Anomalous Behavior Flagged:** *Excessive slippage detected on SOL-USDC perpetual positions between 14:15 and 14:35 UTC.*

### Execution Quality Analysis
Average execution slippage today spiked to **4.82 bps**, totaling **$412.50** in unforced losses. This is double our historical baseline of 2.1 bps. The raw execution logs reveal that our mean reversion strategy executed a series of buy orders for `SOL-USDC` during peak volatility without waiting for order book depth to recover. Total trading fees reached **$310.40**, bringing total drag to **$722.90**. Our liquidity-taking logic is execution-inefficient when hourly volume crosses the 95th percentile.

### Portfolio & Risk Analysis
While our ending equity rests at a healthy **$107,412.50**, the portfolio was exposed to massive intraday risk. Margin utilization spiked to **64.2%** during the liquidations at 14:00 UTC, triggering our automated system-wide leverage warning. The portfolio drawdown bottomed at **-4.12%**, which is close to our hard stop-loss trigger of -5.0%. This indicates that the current dynamic position sizing is failing to scale down contracts quickly enough as volatility rises.

—

## Action Items
– [ ] Implement an execution lock or increase minimum order interval to 2,000ms when standard deviation of order book bid-ask spread exceeds 0.15% over a 5-minute rolling window.
– [ ] Reduce the default maximum position size multiplier on SOL-USDC by 20% to prevent margin utilization from breaching the 60% threshold during high-volatility hours.
– [ ] Transition the execution engine from taking immediate liquidity to using passive post-only limit orders for trades exceeding 500 units.