forex-scalping.com

Logging EA trades to SQLite instead of a CSV file

CSV logs are fine until you have a few months of them. Then you're merging files, fixing broken rows when the EA crashed mid write, and writing scripts to answer simple questions. For anyone running EAs seriously, a small SQLite database is a cleaner home for trade logs.

MQL5 has built in database functions, DatabaseOpen, DatabaseExecute and DatabasePrepare, so an EA can write straight to a SQLite file without any external library. Python reads the same file with the standard sqlite3 module, and so do plenty of free viewer tools.

A minimal setup is one table for trades and one for events. Trades get the ticket, symbol, direction, entry time and price, exit time and price, stop, target, spread at entry, slippage and the setup tag. Events get everything else that matters: the EA starting, rejected orders, spread filter blocks, news pauses.

The big advantage is querying. Average slippage by hour of day, results by setup tag, how many entries the spread filter blocked last month. Each is a single query instead of a spreadsheet session.

Wrap writes in transactions and keep them short, so a crash can't leave half a row. And keep the database file outside the terminal's tester folder, or the tester will happily overwrite it during a backtest.

Write the spread and the time to the millisecond at the moment the order is sent, and again at the moment the fill comes back. That gives you latency and spread changes between decision and execution. It's information you can't recover later from the broker's history, which only shows the fill. After a few weeks you'll have a clear picture of how much your fills cost on each pair and session, and whether the problem in a bad week was the strategy or just the execution environment that week. Over time it becomes the most honest record you have of what your broker actually delivers.

Locking can bite if several EAs write to the same file. SQLite handles multiple writers by locking the whole file during a write. With short transactions this is usually fine for a handful of EAs, but if one EA holds a long transaction the others will wait or fail. Either give each EA its own database file and combine them later in Python, or make every write tiny and retry on a busy error. One file per EA is simpler, and joining them is trivial. A few lines of Python with pandas read_sql will pull them all into one frame in seconds.

Add a schema version table from the first day. You will change the columns at some point, adding a field for a new filter or a new tag. Without a version number, old and new rows get mixed up and your queries break in confusing ways. With a version, your Python analysis can check it and handle older rows correctly. It sounds like over engineering for a trade log, but a two column table now saves an annoying cleanup a few months down the line. Adding a column later is then just a migration step rather than a mystery.

Back up the file regularly, and do it properly. Copying a SQLite file while an EA is writing to it can produce a corrupt copy. The safe ways are the sqlite3 backup command, the backup API in Python, or briefly pausing the EA writes. A nightly backup to a different folder or machine is enough for most people. Losing months of execution data because a VPS disk failed is the kind of thing you only need to experience once to never skip backups again. Test a restore once too, so you know the backup actually opens.

Write ahead logging mode is worth turning on as well. With PRAGMA journal_mode=WAL, readers do not block the writer, so a Python script reading the database for a live dashboard will not cause an EA write to fail. It still allows only one writer at a time, so the one file per EA advice holds. Also store the times in UTC with the server offset in a separate column. When you later join files from a New York close broker and a UTC broker, aligning the Asian morning trades is much easier when nothing has been converted twice.