DEV Community

Propfirmkey
Propfirmkey

Posted on

Building a Trading Journal with Python and SQLite

#python #sqlite #trading #tutorial

Every serious trader needs a journal. Here's how to build a simple but effective one using Python and SQLite.

Why a Trading Journal?

Tracking your trades helps identify patterns in your behavior. Are you overtrading on Mondays? Do you perform better in certain market conditions? Data answers these questions.

Setup 

import sqlite3
from datetime import datetime

def init_db():
    conn = sqlite3.connect('trading_journal.db')
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS trades (
        id INTEGER PRIMARY KEY,
        date TEXT,
        instrument TEXT,
        direction TEXT,
        entry_price REAL,
        exit_price REAL,
        size REAL,
        pnl REAL,
        notes TEXT,
        setup_type TEXT,
        emotion TEXT
    )''')
    conn.commit()
    return conn
Enter fullscreen mode Exit fullscreen mode

Adding Trades 

def add_trade(conn, trade_data):
    c = conn.cursor()
    c.execute('''INSERT INTO trades
        (date, instrument, direction, entry_price, exit_price,
         size, pnl, notes, setup_type, emotion)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
        trade_data)
    conn.commit()
Enter fullscreen mode Exit fullscreen mode

Analytics

The real value is in the analysis:

def win_rate(conn):
    c = conn.cursor()
    total = c.execute('SELECT COUNT(*) FROM trades').fetchone()[0]
    wins = c.execute('SELECT COUNT(*) FROM trades WHERE pnl > 0').fetchone()[0]
    return (wins / total * 100) if total > 0 else 0

def best_setup(conn):
    c = conn.cursor()
    return c.execute('''
        SELECT setup_type,
               AVG(pnl) as avg_pnl,
               COUNT(*) as count
        FROM trades
        GROUP BY setup_type
        ORDER BY avg_pnl DESC
    ''').fetchall()

def performance_by_day(conn):
    c = conn.cursor()
    return c.execute('''
        SELECT strftime('%w', date) as day_of_week,
               AVG(pnl) as avg_pnl,
               COUNT(*) as trades
        FROM trades
        GROUP BY day_of_week
        ORDER BY day_of_week
    ''').fetchall()
Enter fullscreen mode Exit fullscreen mode

Emotion Tracking

This is often overlooked but critical. Add an emotion field (calm, anxious, confident, frustrated) and correlate it with PnL:

def emotion_analysis(conn):
    c = conn.cursor()
    return c.execute('''
        SELECT emotion,
               AVG(pnl) as avg_pnl,
               COUNT(*) as count
        FROM trades
        WHERE emotion IS NOT NULL
        GROUP BY emotion
        ORDER BY avg_pnl DESC
    ''').fetchall()
Enter fullscreen mode Exit fullscreen mode

Most traders find they perform significantly worse when trading from frustration or overconfidence.

Going Further

If you're trading with a prop firm, tracking becomes even more important since you have strict drawdown rules. I wrote about choosing the right prop firm — matching your trading style to the right firm's rules can make or break your evaluation.

The full source code for this journal is straightforward to extend — add charts with matplotlib, export to CSV, or build a Flask dashboard on top.

What metrics do you track in your trading journal?

Top comments (0)