The one indicator I still trust for bias only

[PTScalper]

4. Unleashing the AI on the Order Book

With this schema in place, you can pass the new DataFrame to Ollama. You can now prompt your "Quantitative Risk Manager" CrewAI agent with targeted microstructure questions:

"Analyze the correlation between widened spreads (over 1.5 pips) and the 'Exhaustion' regime. Are my setups triggering during toxic liquidity sweeps?"

"Compare the TopAskVolume to TopBidVolume during 'Chop' regime rejections. Is there a hidden directional bias in the limit orders even when the HTF moving average is flat?"

[PTScalper]

To force the LLM to analyze the microstructure data, you must explicitly define how to interpret spread widening and order book imbalances within the Agent's backstory and the Task descriptions. Without this framing, the LLM will just summarize the numbers without understanding the mechanics of a liquidity sweep or toxic order flow.

Here is the updated Python architecture. The SQL query is expanded, and the CrewAI prompts are heavily engineered to focus on order book pressure and liquidity conditions.

[PTScalper]

The Microstructure AI Pipeline

Code: Select all

import pyodbc
import pandas as pd
from crewai import Agent, Task, Crew, Process
from langchain_community.llms import Ollama

# 1. Extract Expanded Data
conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=AlgoTelemetry;"
    "Trusted_Connection=yes;"
)
conn = pyodbc.connect(conn_str)

# Query now includes Spread and Order Book Volume
query = """
    SELECT Timestamp, Symbol, Direction, Regime, Reason, 
           SpreadInPips, TopAskVolume, TopBidVolume
    FROM TradeRejections 
    WHERE CAST(Timestamp AS DATE) = CAST(GETUTCDATE() AS DATE)
"""
df = pd.read_sql(query, conn)
conn.close()

# Optional: Pre-calculate Book Imbalance for the LLM to make its job easier
if not df.empty:
    df['BookImbalance'] = (df['TopBidVolume'] - df['TopAskVolume']) / (df['TopBidVolume'] + df['TopAskVolume'] + 1)
    df['LiquidityState'] = df.apply(lambda x: "Toxic/Thin" if x['SpreadInPips'] > 1.5 else "Normal", axis=1)

rejection_log_text = df.to_string(index=False)

# 2. Initialize Local LLM 
local_llm = Ollama(model="llama3") # or deepseek-coder

# 3. Microstructure-Aware Agents
quant_agent = Agent(
    role="Quantitative Microstructure Analyst",
    goal="Identify toxic liquidity sweeps and order book imbalances at the exact moment of rejected entries.",
    backstory="""You are a high-frequency trading analyst. You do not just look at price; you look at the tape. 
    You analyze 'SpreadInPips', 'TopAskVolume', and 'TopBidVolume'. 
    - If spread is wide and volume is thin, you flag it as a 'Toxic Liquidity Sweep'. 
    - If bid volume massively outweighs ask volume during a rejected short, you flag it as 'Trading into a Buy Wall'.
    Your job is to tell the trader HOW the market was trying to trap them.""",
    verbose=True,
    allow_delegation=False,
    llm=local_llm
)

psych_agent = Agent(
    role="Trading Psychologist",
    goal="Translate microstructure traps into behavioral strictures.",
    backstory="""A performance coach for professional scalpers. You use the quant's data on spreads and volume to diagnose impatience. 
    If the trader is executing during high-spread/thin-book environments, they are experiencing FOMO during volatility spikes rather than waiting for structural confirmation.""",
    verbose=True,
    allow_delegation=False,
    llm=local_llm
)

# 4. Targeted Microstructure Tasks
analyze_data_task = Task(
    description=f"""Analyze the following rejected trades log from today's session.
    Data:
    {rejection_log_text}
    
    Required Analysis:
    1. Did the trader attempt to execute during widened spreads (> 1.5 pips)? Identify the exact times.
    2. Analyze the TopAskVolume vs TopBidVolume. Was the trader repeatedly trying to buy into heavy Ask resistance or sell into heavy Bid support?
    3. Correlate the 'Regime' with the 'LiquidityState'. Are 'Exhausted' regimes showing signs of toxic sweeps?
    """,
    expected_output="A bulleted breakdown of the order book imbalances, spread anomalies, and liquidity traps the trader avoided.",
    agent=quant_agent
)

behavioral_review_task = Task(
    description="""Review the microstructure analysis provided by the Quant Analyst. 
    Provide a brutal assessment of the trader's execution timing. Are they jumping into volatility spikes (widened spreads)? Are they fighting the order book?
    Provide ONE strict rule for tomorrow's session based on these specific liquidity mistakes.""",
    expected_output="A concise psychological diagnosis regarding execution impatience and one actionable rule for reading the tape tomorrow.",
    agent=psych_agent
)

# 5. Execute the Workflow
trading_review_crew = Crew(
    agents=[quant_agent, psych_agent],
    tasks=[analyze_data_task, behavioral_review_task],
    process=Process.sequential
)

print("Starting Microstructure AI Analysis...")
result = trading_review_crew.kickoff()

print("\n### DAILY MICROSTRUCTURE DEBRIEF ###\n")
print(result)

[PTScalper]

Prompting Strategy

Data Pre-processing: Pandas calculates a basic BookImbalance ratio before feeding the text to the LLM. LLMs are notoriously bad at doing raw math on large datasets, so handing it the pre-calculated imbalance ratio (-1.0 to 1.0) drastically improves the agent's deductive reasoning.

Defined Terminology: The Quant Agent's backstory explicitly defines what constitutes a "Toxic Liquidity Sweep" and a "Buy Wall." You must give the LLM the exact trading vocabulary you want it to use, otherwise, it will default to generic financial advice.

[PTScalper]

And for you traders, who want to make some visualisation i prepared this:

Plotly is the industry standard for this type of quantitative visualization because it renders interactive HTML charts. When you are analyzing scalping rejections, you need to be able to zoom into specific millisecond clusters to see the exact sequence of a liquidity sweep.

This script queries your local MS SQL database, calculates the normalized order book imbalance (-1.0 to 1.0), and generates a dual-pane interactive dashboard. It links the X-axes so that zooming in on a spread spike instantly zooms to the corresponding order book state.

[PTScalper]

The Plotly Microstructure Dashboard

Code: Select all

import pyodbc
import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import webbrowser
import os

# 1. Database Connection & Data Extraction
conn_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=AlgoTelemetry;"
    "Trusted_Connection=yes;"
)

try:
    conn = pyodbc.connect(conn_str)
    query = """
        SELECT Timestamp, Symbol, Direction, Regime, Reason, 
               SpreadInPips, TopAskVolume, TopBidVolume
        FROM TradeRejections 
        WHERE CAST(Timestamp AS DATE) = CAST(GETUTCDATE() AS DATE)
        ORDER BY Timestamp ASC
    """
    df = pd.read_sql(query, conn)
    conn.close()
except Exception as e:
    print(f"Database connection failed: {e}")
    exit()

if df.empty:
    print("No rejected trades logged for today.")
    exit()

# 2. Data Processing: Calculate Normalized Book Imbalance
# +1.0 = 100% Bid (Extreme Buy Wall) | -1.0 = 100% Ask (Extreme Sell Wall)
df['TotalVolume'] = df['TopBidVolume'] + df['TopAskVolume']
df['BookImbalance'] = (df['TopBidVolume'] - df['TopAskVolume']) / df['TotalVolume'].replace(0, 1)

# Color mapping for the Regimes
regime_colors = {
    'Bullish': '#008080',   # Teal
    'Bearish': '#800000',   # Maroon
    'Chop': '#808080',      # Grey
    'Exhausted': '#DAA520'  # Goldenrod
}
df['Color'] = df['Regime'].map(regime_colors).fillna('#FFFFFF')

# 3. Build the Dual-Pane Subplot
fig = make_subplots(
    rows=2, cols=1, 
    shared_xaxes=True, 
    vertical_spacing=0.05,
    row_heights=[0.5, 0.5],
    subplot_titles=("Spread Spikes (Liquidity Sweeps)", "Order Book Imbalance (Bid vs Ask)")
)

# --- Top Pane: Spread Scatter Plot ---
# We use a scatter plot so you can hover over individual rejected ticks
for regime in df['Regime'].unique():
    df_sub = df[df['Regime'] == regime]
    fig.add_trace(
        go.Scatter(
            x=df_sub['Timestamp'],
            y=df_sub['SpreadInPips'],
            mode='markers+lines',
            name=f"Spread ({regime})",
            marker=dict(size=8, color=df_sub['Color'].iloc[0], line=dict(width=1, color='white')),
            line=dict(width=1, color='rgba(255,255,255,0.2)'),
            customdata=df_sub[['Direction', 'Reason']],
            hovertemplate=
                "<b>Time:</b> %{x}<br>" +
                "<b>Spread:</b> %{y} pips<br>" +
                "<b>Attempted:</b> %{customdata[0]}<br>" +
                "<b>Reason:</b> %{customdata[1]}<extra></extra>"
        ),
        row=1, col=1
    )

# Add a horizontal line for your "Toxic Spread" threshold (e.g., 1.5 pips)
fig.add_hline(y=1.5, line_dash="dash", line_color="red", annotation_text="Toxic Spread Threshold", row=1, col=1)

# --- Bottom Pane: Imbalance Bar Chart ---
# Green bars = Bid Heavy (Buyers stacking), Red bars = Ask Heavy (Sellers stacking)
fig.add_trace(
    go.Bar(
        x=df['Timestamp'],
        y=df['BookImbalance'],
        name="Book Imbalance",
        marker_color=['#008080' if val > 0 else '#800000' for val in df['BookImbalance']],
        customdata=df[['TopBidVolume', 'TopAskVolume']],
        hovertemplate=
            "<b>Imbalance:</b> %{y:.2f}<br>" +
            "<b>Bid Vol (Support):</b> %{customdata[0]}<br>" +
            "<b>Ask Vol (Resistance):</b> %{customdata[1]}<extra></extra>"
    ),
    row=2, col=1
)

# 4. Layout & Formatting
fig.update_layout(
    title_text="Post-Session Microstructure Analysis",
    template="plotly_dark",
    hovermode="x unified",
    height=800,
    showlegend=True,
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)

fig.update_yaxes(title_text="Spread (Pips)", row=1, col=1)
fig.update_yaxes(title_text="Imbalance Ratio", range=[-1.1, 1.1], row=2, col=1)
fig.update_xaxes(title_text="Execution Timestamp", row=2, col=1)

# 5. Render to an interactive HTML file and open it
output_file = "microstructure_dashboard.html"
fig.write_html(output_file)
webbrowser.open('file://' + os.path.realpath(output_file))

[PTScalper]

How to Read the Dashboard

When the script opens the HTML file in your browser, look for vertical alignment between the two panes:

1.) The Trap: If you see a cluster of points on the top chart breaching the red "Toxic Spread" line, look directly below it.

2.) The Mechanics: If the bar chart below it shows a massive red spike (heavy Ask volume), institutional algorithms pulled bid liquidity, widened the spread, and stacked the ask to force a localized price drop.

3.) The Save: If your C# bot rejected a Buy order at this exact millisecond, you successfully avoided buying into a manufactured sweep.

[PTScalper]

FastAPI is the optimal choice here. Since you are building an asynchronous, low-latency trading infrastructure, FastAPI’s ASGI architecture aligns perfectly with that mindset, and its strong typing feels much closer to C# than Flask does.

By moving the database query inside the API route, the dashboard will dynamically fetch the latest MS SQL records and regenerate the Plotly HTML every time you hit refresh in your browser.

[PTScalper]

The FastAPI Microstructure App

Save this as dashboard_app.py. You will need to install the server dependencies first: pip install fastapi uvicorn.

Code: Select all

import pyodbc
import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from fastapi import FastAPI
from fastapi.responses import HTMLResponse
import uvicorn

app = FastAPI(title="Microstructure Telemetry")

def generate_dashboard_html():
    # 1. Fetch live data on every page load
    conn_str = (
        "DRIVER={ODBC Driver 17 for SQL Server};"
        "SERVER=localhost;"
        "DATABASE=AlgoTelemetry;"
        "Trusted_Connection=yes;"
    )
    
    try:
        conn = pyodbc.connect(conn_str)
        query = """
            SELECT Timestamp, Symbol, Direction, Regime, Reason, 
                   SpreadInPips, TopAskVolume, TopBidVolume
            FROM TradeRejections 
            WHERE CAST(Timestamp AS DATE) = CAST(GETUTCDATE() AS DATE)
            ORDER BY Timestamp ASC
        """
        df = pd.read_sql(query, conn)
        conn.close()
    except Exception as e:
        return f"<h3>Database connection failed: {e}</h3>"

    if df.empty:
        return "<h3 style='color:white; font-family:sans-serif;'>No rejected trades logged for today. Excellent discipline.</h3>"

    # 2. Process Data
    df['TotalVolume'] = df['TopBidVolume'] + df['TopAskVolume']
    df['BookImbalance'] = (df['TopBidVolume'] - df['TopAskVolume']) / df['TotalVolume'].replace(0, 1)

    regime_colors = {
        'Bullish': '#008080', 'Bearish': '#800000', 
        'Chop': '#808080', 'Exhausted': '#DAA520'
    }
    df['Color'] = df['Regime'].map(regime_colors).fillna('#FFFFFF')

    # 3. Build Plotly Figure
    fig = make_subplots(
        rows=2, cols=1, shared_xaxes=True, vertical_spacing=0.05,
        row_heights=[0.5, 0.5],
        subplot_titles=("Spread Spikes (Liquidity Sweeps)", "Order Book Imbalance (Bid vs Ask)")
    )

    # Top Pane: Spread
    for regime in df['Regime'].unique():
        df_sub = df[df['Regime'] == regime]
        fig.add_trace(
            go.Scatter(
                x=df_sub['Timestamp'], y=df_sub['SpreadInPips'],
                mode='markers+lines', name=f"Spread ({regime})",
                marker=dict(size=8, color=df_sub['Color'].iloc[0], line=dict(width=1, color='white')),
                line=dict(width=1, color='rgba(255,255,255,0.2)'),
                customdata=df_sub[['Direction', 'Reason']],
                hovertemplate="<b>Time:</b> %{x}<br><b>Spread:</b> %{y} pips<br><b>Attempted:</b> %{customdata[0]}<br><b>Reason:</b> %{customdata[1]}<extra></extra>"
            ), row=1, col=1
        )
    fig.add_hline(y=1.5, line_dash="dash", line_color="red", row=1, col=1)

    # Bottom Pane: Imbalance
    fig.add_trace(
        go.Bar(
            x=df['Timestamp'], y=df['BookImbalance'], name="Book Imbalance",
            marker_color=['#008080' if val > 0 else '#800000' for val in df['BookImbalance']],
            customdata=df[['TopBidVolume', 'TopAskVolume']],
            hovertemplate="<b>Imbalance:</b> %{y:.2f}<br><b>Bid Vol:</b> %{customdata[0]}<br><b>Ask Vol:</b> %{customdata[1]}<extra></extra>"
        ), row=2, col=1
    )

    fig.update_layout(
        title_text="Post-Session Microstructure Analysis", template="plotly_dark",
        hovermode="x unified", height=800, showlegend=True,
        paper_bgcolor="#111111", plot_bgcolor="#111111" # Force dark background for full page
    )

    # 4. Return raw HTML string instead of writing to a file
    return fig.to_html(full_html=True, include_plotlyjs='cdn')

@app.get("/", response_class=HTMLResponse)
async def serve_dashboard():
    return generate_dashboard_html()

if __name__ == "__main__":
    # Runs the ASGI server locally on port 8050
    uvicorn.run("dashboard_app:app", host="127.0.0.1", port=8050, reload=True)

[PTScalper]

Running It as a Background Service

Because you are likely running this alongside other local services like Ollama, you do not want to keep a command prompt open all day just to host the dashboard.

Execute this in your terminal to start the server:
python dashboard_app.py

You can now view the dashboard at any time by navigating to [http://127.0.0.1:8050](http://127.0.0.1:8050) in your browser. To make this permanent, you can wrap the python execution command in a simple .bat script and drop it into your Windows Startup folder (shell:startup), ensuring the telemetry dashboard is always silently available in the background whenever your workstation is on.