Ran-Vain — Spark × Claude Desktop Bridge

Ask Claude "What is my unrealized P&L?" and get a live, data-driven answer sourced directly from Spark — in under two seconds.

---

What Is This?

Ran-Vain bridges Spark (a professional ASX trading platform) with Claude Desktop using Anthropic's Model Context Protocol (MCP). Spark delivers live market data via Dynamic Data Exchange (DDE) — a Windows-native protocol that Excel understands but AI systems don't. This project builds a four-layer translation stack so Claude can answer natural language trading questions using live ASX data.

---

Architecture

Spark (live ASX feed via DDE)
        │
        ▼
dde_client.py       ← polls 6 stocks every 2 seconds
        │
   ┌────┴────┐
   ▼         ▼
Redis      SQLite    ← real-time snapshots + historical data
   └────┬────┘
        ▼
api/main.py          ← 15 authenticated REST endpoints (FastAPI)
        │
        ▼
spark_mcp.py         ← 18 Claude tools (MCP server over stdio)
        │
        ▼
Claude Desktop       ← natural language query interface

Layer	Component	Responsibility
1 — DDE Listener	dde_client.py	Subscribes to Spark DDE feeds; writes to Redis & SQLite
2 — Data Store	Redis + SQLite	Redis for real-time snapshots; SQLite for historical data
3 — API Layer	api/main.py	15 authenticated REST endpoints
4 — MCP Server	spark_mcp.py	18 Claude tools registered with Claude Desktop

---

Prerequisites

• Windows 10 or 11 (DDE is Windows-only)
• Spark — installed and logged in
• Python 3.11 — python.org/downloads
• Claude Desktop — claude.ai/download
• Docker Desktop — for Redis

---

Installation

Step 1 — Virtual environment

cd D:\spark-dde-bot
python -m venv venv
venv\Scripts\Activate.ps1

Step 2 — Dependencies

pip install pywin32 redis loguru python-dotenv fastapi uvicorn httpx mcp

Step 3 — Configure .env

REDIS_URL=redis://localhost:6379/0
SQLITE_PATH=dde_listener/spark_data.db
API_BASE=http://localhost:8000
API_KEY=your-strong-api-key-here
DDE_POLL_MS=2000

Step 4 — Start Redis

docker-compose up -d

Step 5 — Seed dummy data (first run only)

python seed_dummy_data.py

Step 6 — Start the DDE listener (open Spark first)

python dde_listener\dde_client.py

Step 7 — Start the API

uvicorn api.main:app --port 8000 --reload

Step 8 — Register MCP with Claude Desktop

Edit claude_desktop_config.json:

{
  "mcpServers": {
    "spark-trader": {
      "command": "D:\\spark-dde-bot\\venv\\Scripts\\python.exe",
      "args": ["D:\\spark-dde-bot\\mcp_server\\spark_mcp.py"],
      "env": {
        "API_BASE": "http://localhost:8000",
        "API_KEY": "your-strong-api-key-here"
      }
    }
  }
}

Step 9 — Restart Claude Desktop

Fully close and reopen Claude Desktop. The hammer icon in the toolbar confirms the MCP server is connected.

---

Startup Checklist

#	Action	Verification
1	Start Spark and log in	Live market data visible in watchlist
2	Start Redis via Docker	docker ps shows redis container running
3	Activate venv	Terminal prompt shows (venv)
4	Run dde_client.py	Logs show: Snapshot updated — 6 stocks
5	Run uvicorn	http://localhost:8000 returns {status: ok}
6	Open Claude Desktop	Hammer icon visible in toolbar
7	Test: "Show my positions"	Claude returns 6 stocks with live P&L

---

Project Structure

D:\spark-dde-bot\
├── .env                          # Secrets and config (never commit this)
├── claude_desktop_config.json    # Claude Desktop MCP registration
├── docker-compose.yml            # Redis container
├── seed_dummy_data.py            # One-time dummy data seeder
│
├── dde_listener/
│   ├── dde_client.py             # DDE → Redis/SQLite listener
│   ├── spark_data.db             # SQLite database
│   └── logs/                     # Rotating logs (7-day retention)
│
├── api/
│   └── main.py                   # FastAPI — all 15 endpoints
│
├── mcp_server/
│   └── spark_mcp.py              # MCP server — 18 Claude tools
│
└── venv/                         # Python 3.11 virtual environment

---

API Endpoints

All endpoints require the X-API-Key header. Full docs at http://localhost:8000/docs.

Endpoint	Source	Description
GET /quotes	Redis	All stocks — live prices, bid, ask, change, volume
GET /quotes/{symbol}	Redis	Single stock live quote
GET /movers/up?n=5	Redis	Top N gainers by % change
GET /movers/down?n=5	Redis	Top N losers by % change
GET /movers/volume	Redis	Stocks ranked by volume
GET /summary	Redis	Count of stocks up / down / flat
GET /historical/{symbol}	SQLite	Price history over last N hours
GET /historical/{symbol}/range	SQLite	High / low / avg / peak volume
GET /positions	SQLite + Redis	All positions with live P&L
GET /positions/{symbol}	SQLite + Redis	Single position with live P&L
GET /pnl/today	SQLite	Today's P&L snapshot
GET /pnl/history?days=30	SQLite	Daily P&L history with win rate
GET /orders	SQLite	Order history — filterable
GET /orders/{symbol}	SQLite	All orders for a specific stock
GET /analytics/exposure	SQLite + Redis	Portfolio exposure % breakdown
GET /analytics/best-performer	SQLite	Highest unrealized P&L position
GET /analytics/worst-performer	SQLite	Lowest unrealized P&L position
GET /analytics/drawdown?days=7	SQLite	Max drawdown from peak P&L

---

Claude Tools (18)

Category	Tools
Live Quotes	get_all_quotes, get_quote, get_top_gainers, get_top_losers, get_by_volume, get_market_summary
Historical	get_historical, get_price_range
Positions	get_positions, get_position
P&L	get_today_pnl, get_pnl_history
Orders	get_orders, get_orders_by_symbol
Analytics	get_portfolio_exposure, get_best_performer, get_worst_performer, get_drawdown

---

Example Queries

"Show my open positions"
"What is my P&L today?"
"Which stocks are up today?"
"Show my BHP orders this month"
"What is my portfolio exposure?"
"What was my best trading day?"
"What is my drawdown this week?"
"Which stock has the most volume?"
"Summarise my month and tell me my biggest risk"

---

Security

• API key authentication on every endpoint via X-API-Key header
• FastAPI binds to localhost:8000 only — not network-exposed by default
• All secrets stored in .env — nothing hardcoded
• All endpoints are read-only GET — no write operations

Before production: change the API key to a strong random value (32+ characters), add .env to .gitignore, enable Redis password auth, and add HTTPS via nginx if exposing beyond localhost.

---

Known Limitations

Limitation	Detail
Windows only	DDE is Windows-exclusive — the listener cannot run on macOS or Linux
Dummy data	Positions, orders, and P&L use seeded synthetic data pending broker API integration
Fixed watchlist	Hardcoded to 6 symbols (BHP, FMG, CBA, NAB, PLS, WOW)
Pull-only	Claude queries are on-demand — no price alerts or real-time push
Read-only	Cannot place, modify, or cancel orders

---

Troubleshooting

Symptom	Resolution
DDE connect failed on all symbols	Open Spark and log in before running dde_client.py
Redis connection refused	Run docker-compose up -d and check REDIS_URL in .env
FastAPI returns 503	DDE listener isn't running or hasn't completed first poll — wait 2 seconds
No hammer icon in Claude Desktop	Check paths in claude_desktop_config.json and fully restart Claude Desktop
FastAPI returns 403	API_KEY in .env must match the key in claude_desktop_config.json
P&L shows dummy values	Expected — replace seeded data with real broker data when ready

---

Tech Stack

Technology	Version	Purpose
Python	3.11.9	Primary runtime
pywin32	306	Windows DDE client
Redis	7.x	Real-time price snapshots
SQLite	Built-in	Historical data store
FastAPI	0.110+	REST API framework
uvicorn	0.29+	ASGI server
httpx	0.27+	Async HTTP client
MCP SDK	Latest	Anthropic Model Context Protocol
loguru	0.7+	Structured logging
Docker	Latest	Containerised Redis

---

Roadmap

• Replace dummy positions/P&L with live broker API or CSV export
• Register dde_client.py as a Windows Service for auto-start and crash recovery
• Read watchlist symbols from a config file or database table
• Use Spark's Watchlist DDE topic to query all symbols in a single DDE call
• Add price alert monitoring that Claude can query
• Expand beyond 6 ASX symbols

---

Ran-Vain is a read-only analytics and query tool. It does not place, modify, or cancel orders.