SQL for trade journals: small database, big payoff

[Shadow Trader]

Spreadsheets handle a journal well up to a few thousand trades. Beyond that, or when analysis gets complicated, a small database helps. SQLite is free, needs no server, and stores everything in a single file.

A basic design uses a trades table with columns for ID, open time, close time, pair, direction, size, entry, exit, commission, swap, strategy tag, and notes. A second table for daily summaries or for execution quality metrics is optional.

Once loaded, questions become one-line queries. Average result by hour of day. Expectancy by strategy and session. Worst five days. Win rate after two consecutive losses. Joins with a spread log reveal how costs varied.

Importing platform exports into the database takes a short script. Many people use Python to read the file, clean it and write to SQLite.

The learning curve for basic SQL is gentle, and it pays back quickly. A habit of asking a new question of the data each week turns a pile of records into a tool.

A few starter queries to try: average net result grouped by hour of the day; the number of trades and expectancy per setup tag; the longest losing streak and when it occurred; the maximum drawdown computed with a running total; comparison of results on days with and without high-impact news. Each is a short statement that returns a table, and each tends to prompt another question.

Has a database replaced the spreadsheet, and which query turned out to be the most revealing?