Then I discovered Flex Queries — IB's most underrated feature — and wrote a Python script that pulls everything automatically. Now my reports generate themselves every Monday morning before I wake up.
Here's how to set it up from scratch.
What Is a Flex Query?
A Flex Query is IB's customizable reporting API. Instead of clicking through Account Management to download generic statements, you define exactly what data you want — trades, cash flows, positions, performance — and IB returns it as structured XML or CSV.
The key difference from the standard Activity Statement:
| Feature | Activity Statement | Flex Query |
|---|---|---|
| Customizable fields | ❌ Fixed format | ✅ Pick exactly what you need |
| API access | ❌ Manual download only | ✅ Programmatic via token |
| Output format | PDF / CSV | XML / CSV |
| Date range | Preset periods | Any custom range |
| Automation | Not possible | ✅ Cron-friendly |
Step 1: Create a Flex Query in Account Management
1. Log in to Client Portal → Performance & Reports → Flex Queries
2. Click Create under "Activity Flex Query" (or "Trade Confirmation Flex Query" for trade-only data) 3. Give it a name likepython_weekly_report
Selecting the Right Sections
For a weekly trading report, I select these sections:
Trades:- Symbol, Date/Time, Quantity, Price, Commission, Realized P&L, Currency
- Type, Date, Amount, Currency (catches deposits, withdrawals, dividends)
- Symbol, Quantity, Market Value, Average Cost, Unrealized P&L
- Net Liquidation Value, Cash Balance
Setting the Delivery Format
- Format: XML (easier to parse in Python than CSV for nested data)
- Period: Last 7 Calendar Days (for weekly reports)
- Date Format: yyyyMMdd
Step 2: Generate Your Flex Query Token
1. Still in Flex Queries page, click Activity Flex Query Token (right side)
2. Click Generate 3. Copy the token — it's a long alphanumeric string⚠️ This token gives read access to your account data. Store it securely — never commit it to Git or paste it in public code.
# Store in environment variables or a config file with restricted permissions
# chmod 600 ~/.ib_flex_config
FLEX_TOKEN = "your_token_here"
QUERY_ID = "your_query_id_here"
Step 3: Python Script to Fetch Flex Query Data
Here's the actual script I use. It's deliberately simple — no heavy frameworks, just requests and xml.etree.
#!/usr/bin/env python3
"""
IB Flex Query Reporter
Fetches weekly trading report via IB Flex Web Service.
"""
import os
import time
import requests
import xml.etree.ElementTree as ET
from datetime import datetime
# Configuration
FLEX_TOKEN = os.environ.get("IB_FLEX_TOKEN")
QUERY_ID = os.environ.get("IB_FLEX_QUERY_ID")
# IB Flex Web Service endpoints
REQUEST_URL = "https://gdcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.SendRequest"
FETCH_URL = "https://gdcdyn.interactivebrokers.com/Universal/servlet/FlexStatementService.GetStatement"
def request_statement():
"""Step 1: Request the statement. Returns a reference code."""
params = {
"t": FLEX_TOKEN,
"q": QUERY_ID,
"v": "3",
}
resp = requests.get(REQUEST_URL, params=params, timeout=30)
resp.raise_for_status()
root = ET.fromstring(resp.text)
status = root.find(".//Status")
if status is not None and status.text == "Success":
ref_code = root.find(".//ReferenceCode").text
print(f"Statement requested. Reference: {ref_code}")
return ref_code
else:
error = root.find(".//ErrorMessage")
raise RuntimeError(f"Request failed: {error.text if error is not None else 'Unknown error'}")
def fetch_statement(ref_code, max_retries=5, wait_seconds=10):
"""Step 2: Poll until the statement is ready, then download it."""
params = {
"t": FLEX_TOKEN,
"q": ref_code,
"v": "3",
}
for attempt in range(max_retries):
resp = requests.get(FETCH_URL, params=params, timeout=60)
resp.raise_for_status()
# Check if it's still generating
if "FlexStatementResponse" in resp.text:
root = ET.fromstring(resp.text)
status = root.find(".//Status")
if status is not None and status.text != "Success":
print(f" Attempt {attempt + 1}: Still generating, waiting {wait_seconds}s...")
time.sleep(wait_seconds)
continue
# We got the actual statement
return resp.text
raise TimeoutError(f"Statement not ready after {max_retries} attempts")
def parse_trades(xml_text):
"""Extract trade data from the Flex Query XML."""
root = ET.fromstring(xml_text)
trades = []
for trade in root.iter("Trade"):
trades.append({
"symbol": trade.get("symbol"),
"date": trade.get("tradeDate"),
"quantity": float(trade.get("quantity", 0)),
"price": float(trade.get("tradePrice", 0)),
"commission": float(trade.get("ibCommission", 0)),
"realized_pnl": float(trade.get("fifoPnlRealized", 0)),
"currency": trade.get("currency"),
})
return trades
def parse_account_info(xml_text):
"""Extract account summary fields."""
root = ET.fromstring(xml_text)
info = {}
for item in root.iter("AccountInformation"):
info["account_id"] = item.get("accountId", "")
for item in root.iter("EquitySummaryInBase"):
info["net_liquidation"] = item.get("total", "N/A")
return info
def generate_report(trades):
"""Generate a simple text report from parsed trades."""
if not trades:
return "No trades in this period."
report_lines = [
f"Weekly Trading Report — {datetime.now().strftime('%Y-%m-%d')}",
"=" * 50,
"",
]
total_pnl = 0
total_commission = 0
for t in trades:
direction = "BUY" if t["quantity"] > 0 else "SELL"
report_lines.append(
f" {t['date']} | {t['symbol']} | {direction} {abs(t['quantity'])} "
f"@ {t['price']} | P&L: {t['realized_pnl']:.2f} | Comm: {t['commission']:.2f}"
)
total_pnl += t["realized_pnl"]
total_commission += t["commission"]
report_lines.extend([
"",
"-" * 50,
f"Total Realized P&L: {total_pnl:.2f}",
f"Total Commissions: {total_commission:.2f}",
f"Net P&L: {total_pnl + total_commission:.2f}",
f"Trade Count: {len(trades)}",
])
return "\n".join(report_lines)
if __name__ == "__main__":
print("Requesting Flex Query statement...")
ref = request_statement()
print("Fetching statement (may take a minute)...")
xml_data = fetch_statement(ref)
trades = parse_trades(xml_data)
report = generate_report(trades)
print("\n" + report)
# Optionally save to file
outfile = f"ib_report_{datetime.now().strftime('%Y%m%d')}.txt"
with open(outfile, "w") as f:
f.write(report)
print(f"\nReport saved to {outfile}")
Step 4: Understanding IB's Two-Step API Flow
This trips up most people. IB's Flex Web Service uses a two-step async process:
1. Request (SendRequest) — you submit your token + query ID. IB returns a reference code, not the actual data.
GetStatement) — you poll with the reference code until the statement is ready.
The gap between steps is usually 5-30 seconds, but during market hours it can take longer. My script retries 5 times with 10-second waits, which has been reliable for over 6 months of daily runs.
Common Errors and Fixes
| Error Code | Meaning | Fix |
|---|---|---|
| 1003 | Token expired | Regenerate in Account Management |
| 1004 | Invalid query ID | Check the query still exists |
| 1005 | Too many requests | Wait 60 seconds, IB rate-limits to ~1 req/sec |
| 1018 | Statement generation failed | Retry; if persistent, simplify your query |
1005 rate limit is the most annoying one. If you're running multiple queries (e.g., trades + performance + positions as separate queries), add a 2-second delay between requests.
Step 5: Schedule It with Cron
I run my report script every Monday at 7 AM Singapore time:
# crontab -e
0 23 * * 0 cd /path/to/project && source .venv/bin/activate && python flex_report.py >> logs/flex_report.log 2>&1
(Sunday 23:00 UTC = Monday 07:00 SGT)
Tips for Production Reliability
1. Token rotation: Flex tokens don't expire automatically, but regenerate every 90 days as a habit.
2. Error alerting: Pipe failures to Telegram or email. A silent failure means you're flying blind. 3. Backup the XML: Save raw responses before parsing. When you want to add new fields later, you can reprocess old data.
# Save raw XML alongside parsed report
with open(f"raw/flex_{datetime.now().strftime('%Y%m%d')}.xml", "w") as f:
f.write(xml_data)
If you're already running automated trading on IB (like my USDJPY momentum strategy), automated reporting is the natural next step. You want your entire pipeline — signal generation, execution, and reporting — running without manual intervention.
Going Further: Integrating with a Database
Once you have the XML parsing working, the next level is storing trades in a SQLite database for historical analysis:
import sqlite3
def store_trades(trades, db_path="trading.db"):
conn = sqlite3.connect(db_path)
conn.execute("""
CREATE TABLE IF NOT EXISTS flex_trades (
id INTEGER PRIMARY KEY AUTOINCREMENT,
symbol TEXT,
trade_date TEXT,
quantity REAL,
price REAL,
commission REAL,
realized_pnl REAL,
currency TEXT,
fetched_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
for t in trades:
conn.execute("""
INSERT INTO flex_trades (symbol, trade_date, quantity, price, commission, realized_pnl, currency)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", (t["symbol"], t["date"], t["quantity"], t["price"], t["commission"], t["realized_pnl"], t["currency"]))
conn.commit()
conn.close()
With a local database, you can run queries like:
-- Monthly P&L summary
SELECT strftime('%Y-%m', trade_date) as month,
SUM(realized_pnl) as total_pnl,
SUM(commission) as total_comm,
COUNT(*) as trade_count
FROM flex_trades
GROUP BY month
ORDER BY month DESC;
This is exactly how I track my strategy's performance over time. The combination of Flex Queries feeding into SQLite gives you a lightweight but powerful reporting stack — no paid dashboards needed.
For the complete Python API setup (connecting to IB Gateway, placing orders, handling callbacks), check out my IB Python API automated trading guide.
FAQ
How often can I run Flex Queries via the API?
IB rate-limits Flex Web Service requests to approximately 1 request per second per token. In practice, I run 2-3 queries daily (trades, positions, performance) without hitting limits. If you need real-time data, use the TWS API instead — Flex Queries are designed for end-of-day and historical reporting.
Do I need a specific IB account type for Flex Query API access?
No. Flex Queries are available on all Interactive Brokers account types — Individual, Joint, IRA, and even paper trading accounts. You do need to enable "Flex Web Service" in Account Management under Settings → Reporting, but there's no minimum balance or subscription required.
Can I get real-time positions from Flex Queries?
Not truly real-time. Flex Query data has a delay — typically 15-30 minutes for trade confirmations, and end-of-day for full activity statements. For live position data, you need the TWS/IB Gateway API. I use both: the TWS API for live trading and Flex Queries for daily/weekly reporting.
What's the difference between Activity Flex Query and Trade Confirmation Flex Query?
Activity Flex Query covers everything: trades, cash transactions, positions, dividends, fees, interest, and account info. Trade Confirmation Flex Query only includes executed trades with confirmation details. For automated reporting, start with Activity — it's more versatile. You can always filter down in your Python code.
Can I use Flex Queries with IB's paper trading account?
Yes, and I'd recommend it for testing your script. Paper trading accounts have their own Flex Query setup in Account Management. The API endpoints and flow are identical — just use your paper account's token and query ID. Once your script works reliably on paper, switch to your live account credentials.
---
Wrapping Up
Flex Queries turned my weekly "log in and download reports" ritual into a zero-effort automated pipeline. The two-step async flow is a bit unusual, but once you've got the Python script working, it's rock solid — mine has been running for over 6 months without a single failure.
If you're running any kind of systematic strategy on IB, automating your reporting isn't optional — it's the difference between knowing your numbers and guessing. Start with the script above, customize the query fields to match what you actually need, and schedule it.
Ready to get started? Open an Interactive Brokers account if you don't have one — you can test Flex Queries on a paper account before going live.
---
*Affiliate disclosure: Some links in this article are referral links. If you sign up through them, you may receive a bonus and I may earn a commission — at no extra cost to you. I only recommend services I actively use.*
*Risk warning: Trading involves substantial risk of loss. Past performance does not guarantee future results. The code in this article is for educational purposes — test thoroughly before using with real money.*