Automated Options Trading Backtesting

I started this blog in 2013 as a way to capture my ideas for building an automated option trading system (Option Algo System - OAS).  At that time, I had already built version 1 of an option trading backtesting system (Option Strategy Backtester - OSB) and I was researching how to best integrate my backtesting system (written in Java) with an auto trading system.

Needless to say, as I worked towards the auto trading goal I had to refactor/redesign my backtesting system to utilize its strategies in live trading.  I'm now on version 3 of the backtesting system (OSB) and the architecture of the software has changed significantly.  Version 4 will be a radical architecture change as the system moves from strategies defined in property files to strategies defined in a database, and updated in the user interface you see taking shape on this blog.  Also the output will move from being auto-generated in CSV files to output also being stored in a database (MySQL).

The data architecture of the trading system (OAS) is still similar to the diagram below.

I've left a few details (columns) off of some of the tables in order to show all of the tables and their relationships in the diagram.  For example, security_option will have many more columns.  The equivalent table in the backtesting system (OSB) currently has 22 columns.  That means for every option, there are 22 attributes.  The two tables outlined in blue have been built in the auto trading system (OAS).  In order to have the auto trading system (OAS) be minimally integrated with the backtesting system (OSB) the tables in red will need to be built along with the associated Java code.  The tables not outlined, along with the associated Java code, will be built next.  The last tables/code to be built are those outlined in green.

Until the first step of the integration is complete (tables in red), I will post some backtesting results from OSB version 3.  I will shoot for weekly posts of backtesting results interspersed with OAS development posts.  If you're not interested in the system development, but you're interested in options trading, I will start to post for you soon.

My first post with backtesting results will be next week (after I download the options data through this week for RUT, SPX, and NDX).  This first backtesting post will cover a standard iron condor using monthly options exprirations, with end-of-day (EOD) option data for the three vehicles (RUT, SPX, NDX).  I will show the results for three or four different short strike deltas iron condors with a fixed days-to-expiration (DTE) trade start.  More details in the actual post ...

Other Trading System Functionality

At server start up, core services will be started:

• Servlet Container (Tomcat)
• Confirm JMS provider is active (ActiveMQ)
• User Service
• Account Service
• Strategy Service (scan database for strategies to load)
• Securities Service
• Position Service
• Order Service
• Transaction Service
• Metrics Service
• Risk Service
• Communications Service
• Authentication Service (authentication and authorization)
• Audit Service
• Run every XX minutes.  Compare order quantity with transaction quantity and alert if different.
• Will need to be run on start up to determine if there are issues.
• At regular intervals, account quantity should be reconciled with transaction quantities.

                                                                                                                  

Starting of strategies will follow this general flow:

1. Scan the strategy_run table for strategies with their load set to true.
2. Does a position exist in the position table?  
1. Iterate through the the position table corresponding to this run id, and sum the quantities for each security/option in the position_details table.  Do all quantities = 0 -->
1. yes - then will activate in the create-new-trade state
2. no - then will activate in the managing-trade state.  Is trade complete -->
1. yes - manage complete trade
2. no - determine what components need to be built and continue building trade - send alert - trade incomplete

                                                                                                                  

The StrategyService sends orders to the OrderService, and the OrderService receives fills and order status from broker.  These orders are associated with a strategy_run_id and a position_id, which can be resolved to an account, userid, and strategy.

When a fill occurs, an entry is created in the transaction table by the TransactionService.  A fill alert is sent via JMS to the StrategyService.  The StrategyService will call the PositionService to update the position_details table.  The StrategyService will also update it's positions map from the db by requesting an updated list of positions from the PositionService.  In backtest mode, an order is converted to a transaction automatically.

Order status alerts are sent by the OrderService to the StrategyService.  A strategy may act on an order status update by cancelling or replacing orders.

The trade_summary table is update at trade closure.  The trade_details_by_day table is updated daily.  These updates are handled by the MetricsService.

                                                                                                                  

Many of the tables will have CRUD functionality provided by a browser based UI:

• users
• user_details
• user_email
• user_phone
• user_address
• account
• strategy
• strategy_settings
• strategy_run
• account_strategy_run

                                                                                                                  

Additionally, the browser based UI will provide the following functionality:

• Account Overview
•     Can view orders associated with the account
•     Can view fills associated with the account
•     Can view transactions associated with the account
•     Can view all securities in the account
•     Can view the current cash balance in the account

• System Overview
•     Positions and/or transactions by strategy type or name
•     Positions and/or transactions by security
•     Positions and or transactions by account, then strategy, etc

                                                                                                                  

Next steps:

• Technology POC: Twitter Bootstrap and Thymeleaf with Spring MVC
• Technology POC: Spring Security with Spring MVC
• Code DAOs for Hibernate and Spring JDBC
• Code Spring Services
• Code CRUD pages for the tables in the ERM diagrams
• Refactor the Strategy Engine to use the above

                                                                                                                  

Future blog post will be on the topics above as well as Tomcat, ActiveMQ, MySQL, Maven, and Eclipse.  I may include some trading posts as well.

Data Model Details

The tables in the model include the user tables that are populated when a new user is created in the system.  A user name is created by the UserService during the user registration phase.

• users
• user_details
• user_email
• user_phone
• user_address

After a user is created, an account is registered for that user by the AccountService.  This table is a place holder at this point and will be refactored when connections to a broker are built.  This table should be adequate during the first stages of system development.  An account can be either regt or pm margined, and either funded or paper/simulation.

• account

As strategies are developed, the strategy and strategy_settings tables are populated.  An entry in the strategy_run is associated with a specific set of settings, a specific account, and specific date range.  Multiple runs, can use the same set of strategy settings.  The allocation amount from an account to a specific run is set in the account_strategy_run table.  The allocation amount cannot be greater than the sum of the margin required by the strategy versus the account cash available.  All of these updates are handled by the StrategyService.

• strategy
• strategy_settings
• strategy_run
• account_strategy_run

A position is a set of options strikes, for example an iron condor, for specific expiration month.  A new position (PositionService) and security_option(s) (SecuritiesService) are created by the StrategyService just prior to the first order being sent by the StrategyService.  An order value object is created and sent via JMS to the OrderService.  The OrderService creates an entry in the order table and sends the order to the broker connector via JMS.  The OrderService monitors the status of orders and will update/cancel orders.  Order status alerts are sent by the OrderService to the StrategyService. 

• position
• security_option
• order - composed of options_security entities, and may result in multiple fills/transactions

The OrderService will match fills to orders and will send fills via JMS to the TransactionService.  The TransactionService will update the fill and transaction tables.  The TransactionService will send a fill alert via JMS to the StrategyService .  The StrategyService will then call the PositionService to update the position_details table.

• fill - composed of options_security entities
• transaction - composed of options_security entities, and associated with an order and position
• position_details - composed of transaction entities

The MetricsService will update the trade_summary and trade_details_by_day tables, as well as query these tables for data to display in charts.

• trade_summary
• trade_details_by_day

The next post will contain some ideas about other functionality required by the system.

Another Data Model Update

After additional review of my notes and thinking through the use cases a bit more, I've updated the ERM diagram for the trading system.  The current version is below.

Trading System V1 - ERM Diagram - Third Draft

The system will use Spring Security, and the associated tables are below.  The users table in the diagram above is the same users table shown in the next table.  Persistent logins will not be used, but the table is still included in the diagram.

Trading System V1 Security - ERM Diagram

I suspect I will have at least one more update to the trading system diagram, but the structure is getting pretty close.  I will describe how the tables will be used in one of the next posts.

Trading System V1 Data Model Thoughts Continued

After further review of the design notes I've kept over the last few months, I made some updates to the data model I posted yesterday.  Here's the new model created in MySQL Workbench:

Trading System V1 - ERM Diagram - Second Draft

Trading System V1 Thoughts

Using the data model from the revised backtesting system as a starting point, I've started planning the data model and Java component interaction / logical architecture.  The first draft of the ERM diagram is below:

Trading System V1 - ERM Diagram

The security_option table is a placeholder for a parent table called security.  The SecurityOption will be a subclass of the Security abstract class in code.

Backtesting System V2 Thoughts

During the last few months, I've been studying how to retrofit my backtesting system to to work as plug-in strategy engines for a live trading system.  Commercial algorithmic systems have had a big impact on the design and architecture of the new system.

A table structure for storing the back test results was one of the first focus areas.  At this time, all of the back test results are automatically dumped to CSV files at the end of each run.  Three files are generated:

• Trade summary - performance metrics for each trade at trade closure.
• For example, if one multi-legged trade is entered per monthly expiration over a two year period, then there will be 24 summary lines in this file.
• Trade summary by day - metrics for each trade by day.
• For example, if the days in trade (DIT) is 30 then each rade will have 30 lines in this file.  For two years of monthly expiration trades, there will be 24 x 30 lines in this file.
• Trade blotter - list of all of the transactions for each trade (all legs - entry, adjustment, exit).

The first iteration for storing the results of both live and backtested strategy performance looked like the ERM diagram below:

More Thoughts On High Level System Requirements

I've started with three simultaneous projects:

• Building a backtester that can use both end-of-day (EOD) data and 15 minute data.

• Using java for the algo components
• EOD data from http://www.ivolatility.com/
• 15-minute data from http://www.livevol.com/
• The data was loaded into a MySQL database with one table per symbol
• The 15-minute data is requiring between 10GB and 25GB per table for five years of data.
• The EOD data is requiring between 100MB and 700MB per table for two to five years of data
• Also created tables:
• containing the dates of market holidays -- will need to update this table annually -- possibly
• containing options expiration dates for indexes for the last five years
• Besides the MySQL Workbench, I am also using HeidiSQL and Navicat for MySQL.

• Building a proof of concept (POC) of the end-to-end infrastructure required to run a live trading algorithm.  So far, I have built components to test

• SSL from the browser to Node.js
• Client certificates
• Browser authentication with Node.js using the Passport.js module integrating with MySQL
• Streaming data from a java process to RabbitMQ to Node.js to a Dojo table in a browser.
• Java polling for data from Yahoo
• Java streaming data via a DDE connection from ThinkOrSwim 

• Building the trading system.

• Just in the design phase at this point.  Will need at least the following components:
• User Interface (Node.js)
• Strategy Manager
• Position Manager
• Risk Manager
• Broker Connector (streaming and FIX)
• DB Connector (to MySQL)
• Order Manager
• Account Manager
• Communications Manager
• Message Bus - (RabbitMQ or similar)