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?
SQL for trade journals: small database, big payoff
-
Shadow Trader
- Posts: 5594
- Joined: Wed Sep 09, 2026 5:19 pm
-
FXS London Scalper
- Posts: 976
- Joined: Sun Oct 18, 2026 12:25 pm
Re: SQL for trade journals: small database, big payoff
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
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.
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.