Advertisement IC Markets

SQL for trade journals: small database, big payoff

Discuss 1-minute to 15-minute price action setups, fading intraday momentum, key support/resistance zones, and proven short-term trading methodologies.
Post Reply
Shadow Trader
Posts: 5594
Joined: Wed Sep 09, 2026 5:19 pm

SQL for trade journals: small database, big payoff

Post by 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?
Recommended broker for automated trading & scalping IC Markets
FXS London Scalper
Posts: 976
Joined: Sun Oct 18, 2026 12:25 pm

Re: SQL for trade journals: small database, big payoff

Post by FXS London Scalper »

Shadow Trader wrote: 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.
SQLite is the right size for this. One design tip that pays off later: store entry and exit times in UTC and add a separate session column computed on insert (Asia, London, overlap, NY). Then "how do my 08:00–09:00 London trades compare with the overlap" is one GROUP BY, and DST changes never corrupt the session split.
FXS Official Team | London Session Desk
Amanhasnoname
Posts: 2060
Joined: Fri Oct 30, 2026 9:45 am

Re: SQL for trade journals: small database, big payoff

Post by Amanhasnoname »

I moved to SQLite about two years ago when my spreadsheet passed six thousand rows and started freezing every time I sorted it. The switch took one weekend. I wrote a short Python script that reads the platform's statement export, strips out deposits and balance lines, and inserts each trade into a single table. Now I just run it every Friday and the new week lands in the file in a couple of seconds.

The query that surprised me most was the simplest one, net result grouped by day of the week. I had always believed Mondays were my weakest day, but the numbers showed Thursdays were clearly worse, mostly because I traded far more often on Thursdays and the extra trades were small losers once commission was included. Nothing about it felt different while I was trading.

The second most useful one counts trades taken within ten minutes of a losing trade and compares their results with everything else. Mine came out clearly negative, which was the push I needed to start enforcing a short break after every stop.
Post Reply