Michael's blog

Life-long Learning

0%

【How 2】 Set Up Trading API Template In Python - Build Local Storage For Storing Trades

Posted on Edited on In , Views: Valine:

Now we come to the third part of this series. In this post, I’m going to show you how I design and build my local database to store IBKR trades and other necessary information for generating meaningful indicators to review our strategy performance.

If you enjoy reading this and my other articles, feel free to join Medium membership program to read more about Quantitative Trading Strategy.

Previous researches

Why do we need to build this capability ourselves?

We have most of our functions ready in our previous two posts except the def get_transaction() function. Most of the brokers would provide the function to retrieve historic transactions for at least 60 days. However, Interactive Brokers doesn’t support the functionality to retrieve the historic trades and portfolio performance from it. The reason I want this function supported is that I need to:

  1. Use the historic portfolio performance to compare with the benchmark evaluating KPIs and see whether my trading strategy is successful or not.
  2. In the CPPI strategy we talked about before, the B and E ratio calculation depends on the previous day’s maximum portfolio value. Therefore we need to persist it so that we won’t lose it every time we restart our trading script.
  3. I would like to take the impact of the commission into account. Since Interactive Brokers won’t save my trading records any longer, I would need to save those trading records on my local DB so that I get to keep track of the commission spent on this strategy.

To address the requirements that I put together above, building a database on the local machine is imperative. Below, I’m going to put down my solution into two sections:

  • Design DB schema
  • Implement DB-related capabilities

Also, in terms of which DB should be used here, SQL such as MySql or NoSQL like MongoDB will be too complicated and way too powerful. Therefore, I simply pick sqlite3 to create easy-to-use local storage.

Design DB schema

We are going to create three tables, and each of them is going to address the requirements that we raised above respectively.

  • IB_SQLITE_CPPI_TBL_NAME
    • The only critical variable here is the MAX_ASSET. This is a value to keep track of the max portfolio value and calculate the CPPI E_ratio and B_ratio. If you want to know why we need this variable tracked in the data table, you can check out this post
  • IB_SQLITE_TRANSACTION_TBL_NAME
    • This table is basically recording the daily performance of our portfolio and market benchmark. We have PORTFOLIO_CLOSE_VALUE, SPY_CLOSE_PRICE, and COMMISSION, where the commission is a sum added up from the IB_SQLITE_ORDER_TBL_NAME.
  • IB_SQLITE_ORDER_TBL_NAME
    • This table is meant to record all the orders placed. I extracted the following information from the ib.trades() response and tuck them into the table: symbol, order_id, action (buy or sell), quantity, order status, commission cost, and the account number.

DB schema of three tables

Implement DB-related capabilities

Private and public functions for managing our DB


Here I separated the functions into two groups. The first part of functions is the private functions that conduct database operations such as connecting to the database, creating the table, checking whether the table exists or not, and so on. This provides the minimum capability for managing the database. The second part of the functions is public functions that use private functions to interact with the specified data table in the database.

Below are the private sqlite3 DB functions:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
########################################################################
# Sqlite3 private functions
########################################################################
@contextmanager
def sqlite_connect(self):
    dirs = os.path.dirname(os.path.abspath(__file__))
    try:
        db_path = os.path.join(dirs, IB_SQLITE_DB_NAME)
        conn = sqlite3.connect(db_path)
        print(f'Sqlite connection established')
        yield conn
        conn.close()
        print(f'Sqlite connection closed')
    except OSError as e:
        print(f'We are having an OS error')

def __sqlite_create_table(self, conn=None, tbl_name=None):
    if not tbl_name or not conn:
        return False

    if tbl_name == IB_SQLITE_TRANSACTION_TBL_NAME:
        conn.execute(f'''CREATE TABLE {IB_SQLITE_TRANSACTION_TBL_NAME}
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            CREATE_TIME DATETIME NOT NULL,
            PORTFOLIO_CLOSE_VALUE FLOAT NOT NULL,
            SPY_CLOSE_PRICE FLOAT NOT NULL,
            COMMISSION FLOAT NOT NULL);
        ''')
    elif tbl_name == IB_SQLITE_CPPI_TBL_NAME:
        conn.execute(f'''CREATE TABLE {IB_SQLITE_CPPI_TBL_NAME}
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            CREATE_TIME DATETIME NOT NULL,
            MAX_ASSET FLOAT NOT NULL,
            E_RATIO FLOAT NOT NULL,
            B_RATIO FLOAT NOT NULL);
        ''')
    elif tbl_name == IB_SQLITE_ORDER_TBL_NAME:
        conn.execute(f'''CREATE TABLE {IB_SQLITE_ORDER_TBL_NAME}
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            CREATE_TIME DATETIME NOT NULL,
            SYMBOL TEXT NOT NULL,
            ORDER_ID TEXT NOT NULL UNIQUE,
            ACTION TEXT NOT NULL,
            QUANTITY INT NOT NULL,
            ORDER_STATUS TEXT NOT NULL,
            COMMISSION FLOAT NOT NULL,
            ACCOUNT TEXT NOT NULL);
        ''')

    return True

def __sqlite_is_table_exist(self, conn=None, tbl_name=None):
    if not tbl_name or not conn:
        return False

    c = conn.cursor()

    c.execute(f'''SELECT count(name)
        FROM sqlite_master
        WHERE type="table" AND name="{tbl_name}";
    ''')

    if c.fetchone()[0]==1 :
        # Table exists
        return True
    else :
        # Table does not exist
        return False

def __sqlite_query_data(self, conn=None, tbl_name=None):
    if not conn or not tbl_name:
        return None

    if not self.__sqlite_is_table_exist(conn, tbl_name):
        self.__sqlite_create_table(conn, tbl_name)

    df = pd.read_sql_query(f'SELECT * from {tbl_name};', conn)
    return df

def __sqlite_insert_record(self, conn=None, sql=None, value_tuple: tuple=None, tbl_name=None):
    if not self.__sqlite_is_table_exist(conn, tbl_name):
        self.__sqlite_create_table(conn, tbl_name)

    if not sql:
        raise RuntimeError(f'SQL string is empty')

    conn.execute(sql, value_tuple)

    conn.commit()
    return True

As for the public functions in our script, they provide support for our trading script so that it can achieve the purpose we want it to.

First of all, these two functions are for us to retrieve data from the corresponding data table and return in pd.DataFrame() format.

1
2
3
4
5
6
7
8
9
def get_transactions(self):
    with self.sqlite_connect() as conn:
        df = self.__sqlite_query_data(conn, IB_SQLITE_TRANSACTION_TBL_NAME)
    return df

def get_cppi_variables(self):
    with self.sqlite_connect() as conn:
        df = self.__sqlite_query_data(conn, IB_SQLITE_CPPI_TBL_NAME)
    return df

Secondly, we created three functions for handling parsing the corresponding API responses into the data format we need. Therefore, this part of the functions involves interacting with the Interactive Brokers API, fetching data from sqlite3 local database, and processing the data accordingly.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
def update_orders_in_db(self):
    sql = f'''INSERT OR IGNORE INTO {IB_SQLITE_ORDER_TBL_NAME} (CREATE_TIME, SYMBOL, ORDER_ID, ACTION, QUANTITY, ORDER_STATUS, COMMISSION, ACCOUNT) VALUES (?, ?, ?, ?, ?, ?, ?, ?);'''

    with self.sqlite_connect() as conn:
        trades = self.client.trades()
        for trade in trades:
            perm_id = trade.order.permId
            qty = trade.order.filledQuantity
            symbol = trade.contract.symbol
            action = trade.order.action
            commission = sum([fill.commissionReport.commission for fill in trade.fills])
            status = trade.orderStatus.status
            exec_time = trade.log[0].time
            account = trade.order.account
            self.__sqlite_insert_record(
                conn,
                sql,
                (exec_time, symbol, perm_id, action, qty, status, commission, account),
                IB_SQLITE_ORDER_TBL_NAME
            )
    logger.logger.debug(f'Database {IB_SQLITE_ORDER_TBL_NAME} updated')

def update_transactions_in_db(self):
    sql = f'''INSERT OR IGNORE INTO {IB_SQLITE_TRANSACTION_TBL_NAME} (CREATE_TIME, PORTFOLIO_CLOSE_VALUE, SPY_CLOSE_PRICE, COMMISSION) VALUES (?,?,?,?);'''

    # Portfolio value
    portfolio_value = 0
    for account in self.accounts:
        data = self.client.accountValues(account)
        for row in data:
            if row.tag in ['TotalCashBalance', 'StockMarketValue'] and row.currency == self.currency:
                portfolio_value += float(row.value)

    # SPY close value
    benchmark_value = self.get_last_price_from_quote('SPY')

    # Update the latest commission
    commission = self.get_commission_from_db(1)

    with self.sqlite_connect() as conn:
        self.__sqlite_insert_record(
            conn,
            sql,
            (datetime.now(), portfolio_value, benchmark_value, commission),
            IB_SQLITE_TRANSACTION_TBL_NAME
        )
    logger.logger.debug(f'Database {IB_SQLITE_TRANSACTION_TBL_NAME} updated')

def update_cppi_variables_in_db(self, max_asset, E, B):
    sql = f'''INSERT OR IGNORE INTO {IB_SQLITE_CPPI_TBL_NAME} (CREATE_TIME, MAX_ASSET, E_RATIO, B_RATIO) VALUES (?,?,?,?);'''

    with self.sqlite_connect() as conn:
        self.__sqlite_insert_record(
            conn,
            sql,
            (datetime.now(), max_asset, E, B),
            IB_SQLITE_CPPI_TBL_NAME
        )

Lastly, this is the function to achieve the goal for me to calculate the commission sum on the day (or for multiple days).

1
2
3
4
5
6
7
8
def get_commission_from_db(self, time_delta:int=0) -> float:
    with self.sqlite_connect() as conn:
        df = self.__sqlite_query_data(conn, IB_SQLITE_ORDER_TBL_NAME)

    if df.empty:
        return 0
    else:
        return df[(datetime.now(self.timezone) - pd.to_datetime(df['CREATE_TIME'], utc=False)) < timedelta(days=time_delta)]['COMMISSION'].sum()

My strategy report card

In the last part of this post, I’ll show you the portfolio performance metrics that I plan using to evaluate the trading strategy with the data stored in our local database. You can also modify the DB schema, record the information you need, and come up with important and helpful for you to evaluate the effectiveness of your trading script.

  1. Sharpe Ratio (SR)
  2. Total return
  3. Annualized return
  4. Variance
  5. Max Drawdown (MDD)
  6. Trading fee spent
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
def get_strategy_report(self, config=None, verbose=False):
    final = self.get_transactions()

    if verbose is True:
        print(final)

    if tmp.empty:
        ret_data['Version'] = '1.0'
        ret_data['SR/Portfolio'] = 0
        ret_data['SR/Benchmark'] = 0
        ret_data['Total Return/Portfolio'] = 0
        ret_data['Total Return/Benchmark'] = 0
        ret_data['Annualized Return/Portfolio'] = 0
        ret_data['Annualized Return/Benchmark'] = 0
        ret_data['Variance/Portfolio'] = 0
        ret_data['Variance/Benchmark'] = 0
        ret_data['MDD/Portfolio'] = 0
        ret_data['MDD/Benchmark'] = 0
        ret_data['Trading fee'] = 0
        ret_data['Trading fee ratio'] = 0
    else:
        ret_data['Version'] = '1.0'
        ret_data['SR/Portfolio'] = tmp.loc[:, 'PORTFOLIO_CLOSE_VALUE'].mean() / tmp.loc[:, 'PORTFOLIO_CLOSE_VALUE'].std()
        ret_data['SR/Benchmark'] = tmp.loc[:, 'SPY_CLOSE_PRICE'].mean() / tmp.loc[:, 'SPY_CLOSE_PRICE'].std()
        ret_data['Total Return/Portfolio'] = (tmp.loc[:, 'PORTFOLIO_CLOSE_VALUE'].iloc[-1] / c['init_cash']) - 1
        ret_data['Total Return/Benchmark'] = (tmp.loc[:, 'SPY_CLOSE_PRICE'].iloc[-1] / tmp.loc[:, 'SPY_CLOSE_PRICE'].iloc[0]) - 1
        ret_data['Annualized Return/Portfolio'] = (1 + ret_data['Total Return/Portfolio'])**(365/(datetime.today() - pd.to_datetime(c['start_date'])).days) - 1
        ret_data['Annualized Return/Benchmark'] = (1 + ret_data['Total Return/Benchmark'])**(365/(datetime.today() - pd.to_datetime(c['start_date'])).days) - 1
        ret_data['Variance/Portfolio'] = tmp.loc[:, 'PORTFOLIO_CLOSE_VALUE'].var()
        ret_data['Variance/Benchmark'] = tmp.loc[:, 'SPY_CLOSE_PRICE'].var()
        ret_data['MDD/Portfolio'] = self.__calculate_mdd(tmp.loc[:, 'PORTFOLIO_CLOSE_VALUE'])
        ret_data['MDD/Benchmark'] = self.__calculate_mdd(tmp.loc[:, 'SPY_CLOSE_PRICE'])
        ret_data['Trading fee'] = tmp.loc[:, 'COMMISSION'].sum()
        ret_data['Trading fee ratio'] = ret_data['Trading fee'] / tmp.loc[:, 'PORTFOLIO_CLOSE_VALUE'].iloc[-1]

    return ret_data

That’s it! I know it’s a bit too much code and too little talk in this post, but a good trading strategy should always include a performance evaluation to know whether this strategy is still in effect. This is the last bit of what I built in my API template so I hope it would help people who want to build their own API templates.

Disclaimer: Nothing herein is financial advice or even a recommendation to trade real money. Many platforms exist for simulated trading (paper trading) which can be used for building and developing the strategies discussed. Please use common sense and consult a professional before trading or investing your hard-earned money.

Enjoy reading? Some donations would motivate me to produce more quality content