Level Up Your Algorithmic Trading Bot with Postgres

Building an algorithmic trading bot often feels like taking a stab in the dark. You find a strategy, you spend some time/money building it, then you let it run and hope like crazy that it actually works.

Hopefully, you don’t lose too much money in the process.

Hopefully, you’ve got everything right.

Hopefully, it won’t take too long to figure out.

It doesn’t have to be that way.

Over the next few chapters, I’ll show you how to add methods to take your trading bot to the next level. Using practical development approaches such as trading bot strategy backtesting, trading bot trade analysis, and automated reporting, I’ll show you how to ensure you understand where your algorithmic trading bot can be improved.

The start of this process is building a way to record trading bot activity. In this chapter, I’ll show you how to use an open-source and widely used database system called PostgreSQL (also known as Postgres) to do exactly this.

P.S. I’ve had a few people reach out lately to ask if I’d be able to help them build their own trading bot. Use this link if this is you.

The Purpose of Postgres

Why Implement a Database?

Implementing a database system into your trading bot is a powerful way to level up your algorithmic trading bot. Doing so allows you to:

1. Review trade and decision history
2. Integrate non-MetaTrader data
3. Backtest your strategy ideas
4. Analyze trading bot effectiveness

This chapter prepares your trading bot to undertake all these actions and more.

Common Information Model

A Common Information Model (CIM) is a development technique to ensure data format consistency. Doing so early and often ensures that your trading bot:

1. Uses the same data format to make decisions across every exchange, strategy, and interaction
2. Makes data analysis easy

For this book, an constantly updated list of CIM entries can be found on the GitHub project here.

Why Postgres?

While there are many excellent database solutions, I’ll be using PostgreSQL (aka Postgres) to demonstrate. No matter what solution you’re looking for, some of the requirements you need are:

1. Scales easily
2. Cross Platform and multi-cloud
3. Free version available and maintained
4. Integrates efficiently with Python Dataframes and Python 3

Postgres fulfills these requirements.

What is PostgreSQL?

PostgreSQL bills itself as “The World’s Most Advanced Open Source Relational Database”. It is a well-used, highly performant database that can easily store millions of rows of data using the Structured Query Language syntax.

Postgres itself has been around for many years. It is used by many companies large and small and supports an open-source, free-to-use model. Should your trading bot reach a more professional level, it is easy to move this to a paid version.

Connect PostgreSQL to Your Trading Bot

I’ll start by ensuring that Postgres is connected to your trading bot.

Setup Postgres

1. Download and install Postgres on the computer of your choice (link to Windows install in the resources section). If you’re using a non-cloud instance, you can use this link to access their downloads.
2. Make sure you include the tools and drivers you’ll need, such as PostgreSQL Server, PgAdmin 4, and Command Line Tools.
3. Record your server, database, port, username, and password. I called my database trading_bot_db

Update Settings File

1. Navigate to your settings.json
2. Add a new object called postgres as follows:

"postgres": { 
    "host": "your_hostname", 
    "database": "your_database", 
    "user": "your_username", 
    "password": "your_secret_password", 
    "port": "port_your_db_listens_on" 
}

example_settings.json in the project GitHub demonstrates what this looks like.

Create SQL Library

1. Create a folder called sql_lib in your trading bot
2. Add the file sql_interaction.py

Import Python Packages

Import the following packages to your project:

1. psycopg2 — link to a useful how to guide
2. psycopg2.extras

Create a Postgres Connection Object

Create a connection function in sql_interaction.py. Note the use of the try-except statement:

def postgres_connect(project_settings): 
    """ 
    Function to connect to PostgreSQL database 
    :param project_settings: json object 
    :return: connection object 
    """ 
    # Define the connection 
    try: 
        conn = psycopg2.connect( 
            database=project_settings['postgres']['database'], 
            user=project_settings['postgres']['user'], 
            password=project_settings['postgres']['password'], 
            host=project_settings['postgres']['host'], 
            port=project_settings['postgres']['port'] 
        ) 
        return conn 
    except Exception as e: 
        print(f"Error connecting to Postgres: {e}") 
        return False

Check that It Works

Before going to the next statement, check that the connection works. To do this, update your main.py so that your __main__ looks like this:

# Press the green button in the gutter to run the script. 
if __name__ == '__main__': 
    # Import project settings 
    project_settings = get_project_settings(import_filepath=import_filepath) 
    # Connect to Postgres 
    sql_connect = sql_interaction.postgres_connect(project_settings=project_settings) 
    print(sql_connect)

If all goes well, you should get a line printed to your terminal similar to this:

<connection object at 0x000001E06A151140; dsn: 'user=xxxx password=xxx dbname=trading_bot_db host=xxxx port=xxxx', closed: 0>

Interact with Postgres

As a relational database system, Postgres uses a series of tables to store rows of data. These tables are stored in a database.

To store/retrieve data from a Postgres database, you need to define the:

1. Database
2. Table
3. Rows to interact with

In the previous section, you created a connection to a Postgres Database. Let’s expand that functionality to:

1. Create tables
2. Insert rows of data into the table

SQL Execute Function

Interacting with Postgres is performed through the execute part of a Postgres connection.

Create a function called sql_execute in sql_interaction:

def sql_execute(sql_query, project_settings): 
    """ 
    Function to execute SQL statements 
    :param sql_query: String 
    :return: Boolean 
    """ 
    # Create a connection 
    conn = postgres_connect(project_settings=project_settings) 
    # Execute the query 
    try: 
        # Create the cursor 
        cursor = conn.cursor() 
        # Execute the cursor query 
        cursor.execute(sql_query) 
        # Commit the changes 
        conn.commit() 
        return True 
    except (Exception, psycopg2.Error) as e: 
        print(f"Failed to execute query: {e}") 
        return Exception 
    finally: 
        # If conn has completed, close 
        if conn is not None: 
            conn.close()

Create Table Function

Create a function in sql_interaction for table creation, assuming the following:

1. Auto increment is always added
2. Table name is provided as variable
3. Other table details will be provided when the function runs

Here’s the code:

# Function to create a table 
def create_sql_table(table_name, table_details, project_settings): 
    """ 
    Function to create a table in SQL 
    :param table_name: String 
    :param table_details: String 
    :param project_settings: JSON Object 
    :return: Boolean 
    """ 
    # Create the query string 
    sql_query = f"CREATE TABLE {table_name} (id SERIAL PRIMARY KEY, {table_details})" 
    # Execute the query 
    return sql_execute(sql_query=sql_query, project_settings=project_settings)

Create Trade and Paper Tables

A Postgres table uses columns to define the data that each row will contain. Functionally, this is very similar to an Excel spreadsheet — each column has a heading that defines what exists in the rows under it. The only difference is that in Postgres you must also define the exact data type to be entered.

To define the columns to use for your Python Trading Bot, I’ll use the CIM for live_trade_table and paper_trade_table:

{ 
    "live_trade_table": { 
        "strategy": "String defining strategy", 
        "exchange": "String defining the exchange being used", 
        "trade_type": "String of the type of trade: BUY / SELL / BUY_STOP / SELL_STOP", 
        "trade_stage": "Stage of trade: order / position", 
        "symbol": "String of the symbol", 
        "volume": "Float of the volume", 
        "stop_loss": "Float of the stop loss value", 
        "take_profit": "Float of the take profit value", 
        "comment": "String of the comment", 
        "status": "String of the status: CANCELLED / PLACED ", 
        "price": "Float of the executed price", 
        "order_id": "String of a unique identifier for the order" 
    }, 
    "paper_trade_table": { 
        "strategy": "String defining strategy", 
        "exchange": "String defining the exchange being used", 
        "trade_type": "String of the type of trade: BUY / SELL / BUY_STOP / SELL_STOP", 
        "trade_stage": "Stage of trade: order / position", 
        "symbol": "String of the symbol", 
        "volume": "Float of the volume", 
        "stop_loss": "Float of the stop loss value", 
        "take_profit": "Float of the take profit value", 
        "comment": "String of the comment", 
        "status": "String of the status: CANCELLED / PLACED ", 
        "price": "Float of the executed price", 
        "order_id": "String of a unique identifier for the order" 
    } 
}

The CIM shows that all trade_tables have a common set of columns. Therefore, a function called create_trade_table can be created. Add this function to sql_interaction :

def create_trade_table(table_name, project_settings): 
    """ 
    Function to create a trade table in SQL 
    :param table_name: string 
    :param project_settings: JSON Object 
    :return: Boolean 
    """ 
    # Define the table according to the CIM: https://github.com/jimtin/python_trading_bot/blob/master/common_information_model.json 
    table_details = f"strategy VARCHAR(100) NOT NULL," \ 
                    f"exchange VARCHAR(100) NOT NULL," \ 
                    f"trade_type VARCHAR(50) NOT NULL," \ 
                    f"trade_stage VARCHAR(50) NOT NULL," \ 
                    f"symbol VARCHAR(50) NOT NULL," \ 
                    f"volume FLOAT4 NOT NULL," \ 
                    f"stop_loss FLOAT4 NOT NULL," \ 
                    f"take_profit FLOAT4 NOT NULL," \ 
                    f"price FLOAT4 NOT NULL," \ 
                    f"comment VARCHAR(250) NOT NULL," \ 
                    f"status VARCHAR(100) NOT NULL," \ 
                    f"order_id VARCHAR(100) NOT NULL" 
    # Pass to Create Table function 
    return create_sql_table(table_name=table_name, table_details=table_details, project_settings=project_settings)

Now, you can update your main.py to create both tables:

# Press the green button in the gutter to run the script. 
if __name__ == '__main__': 
    # Import project settings 
    project_settings = get_project_settings(import_filepath=import_filepath) 
    # Create a paper_trade_table 
    sql_interaction.create_trade_table("paper_trade_table", project_settings) 
    # Create a live_trade_table 
    sql_interaction.create_trade_table("live_trade_table", project_settings)

P.S. If you’re wondering why there is an extensive focus on table creation and SQL interaction, all will be made clear when we build a better backtester together.

Insert Rows Into Table

Building on the sql_execute function, the final function in this section is called insert_trade_action. This function inserts a row of data for a trade action. Here’s the code:

def insert_trade_action(table_name, trade_information, project_settings): 
    """ 
    Function to insert a row of trade data 
    :param table_name: String 
    :param trade_information: Dictionary 
    :return: Bool 
    """ 
    # Make sure that only valid tables entered 
    if table_name == "paper_trade_table" or table_name == "live_trade_table": 
        # Make trade_information shorter 
        ti = trade_information 
        # Construct the SQL Query 
        sql_query = f"INSERT INTO {table_name} (strategy, exchange, trade_type, trade_stage, symbol, volume, stop_loss, " \ 
                    f"take_profit, price, comment, status, order_id) VALUES (" \ 
                    f"'{ti['strategy']}'," \ 
                    f"'{ti['exchange']}'," \ 
                    f"'{ti['trade_type']}'," \ 
                    f"'{ti['trade_stage']}'," \ 
                    f"'{ti['symbol']}'," \ 
                    f"{ti['volume']}," \ 
                    f"{ti['stop_loss']}," \ 
                    f"{ti['take_profit']}," \ 
                    f"{ti['price']}," \ 
                    f"'{ti['comment']}'," \ 
                    f"'{ti['status']}'," \ 
                    f"'{ti['order_id']}'" \ 
                    f")" 
        # Execute the query 
        return sql_execute(sql_query=sql_query, project_settings=project_settings) 
    else: 
        # Return an exception 
        return Exception # Custom Error Handling Coming Soon

Make It So

Now, update your main to insert a fake trade row. You’re welcome to use your own value — my fake trade row states that I’ve successfully place an order for BTCUSD at $18,501.21 with a Stop Loss of $17,000.50 and Take Proft of $25,000.30 using my TestStrategy.

if __name__ == '__main__': 
    # Import project settings 
    project_settings = get_project_settings(import_filepath=import_filepath) 
    # Define a fake trade row 
    trade_row = { 
        "strategy": "TestStrategy", 
        "exchange": "MT5", 
        "trade_type": "BUY_STOP", 
        "trade_stage": "ORDER", 
        "symbol": "BTCUSD", 
        "volume": 1.0, 
        "stop_loss": 17000.50, 
        "take_profit": 25000.30, 
        "price": 18501.21, 
        "comment": "Test Trade, ignore", 
        "status": "SUCCESS", 
        "order_id": "test_order" 
    } 
    # Add a row to paper_trade_table 
    sql_interaction.insert_trade_action("paper_trade_table", trade_row, project_settings)

In the next episode, I’ll show you how to connect this functionality to your trading bot.

Support Me

Support me by using my Medium referral link to divert a portion of your subscription to me, following me on Medium, signing up to my email distribution, or by sharing my articles with your network. You can also find me on LinkedIn, Twitter, and Instagram.

If you’d like to discuss building a trading bot, contact me on Upwork to see how I can help.

Resources

Links

1. Project GitHub
2. Spreads In Finance: The Multiple Meanings in Finance Explained
3. Symbol_Info_Tick MetaTrader 5 Python API
4. MetaTrader Python Package (PyPi)
5. PostgreSQL
6. PostgreSQL Install Instructions
7. example_settings.json
8. Psycopg2 install
9. Project Common Information Model
10. Introduction to SQL

Say Hi!

I love hearing from my readers, so feel free to reach out. It means a ton to me when you clap for my articles or drop a friendly comment — it helps me know that my content is helping.

• Discord
• Telegram
• TradeOxy

❤

Complete Code

import psycopg2
import psycopg2.extras


# Function to connect to PostgreSQL database
def postgres_connect(project_settings):
    """
    Function to connect to PostgreSQL database
    :param project_settings: json object
    :return: connection object
    """
    # Define the connection
    try:
        conn = psycopg2.connect(
            database=project_settings['postgres']['database'],
            user=project_settings['postgres']['user'],
            password=project_settings['postgres']['password'],
            host=project_settings['postgres']['host'],
            port=project_settings['postgres']['port']
        )
        return conn
    except Exception as e:
        print(f"Error connecting to Postgres: {e}")
        return False


# Function to execute SQL
def sql_execute(sql_query, project_settings):
    """
    Function to execute SQL statements
    :param sql_query: String
    :return: Boolean
    """
    # Create a connection
    conn = postgres_connect(project_settings=project_settings)
    # Execute the query
    try:
        print(sql_query)
        # Create the cursor
        cursor = conn.cursor()
        # Execute the cursor query
        cursor.execute(sql_query)
        # Commit the changes
        conn.commit()
        return True
    except (Exception, psycopg2.Error) as e:
        print(f"Failed to execute query: {e}")
        return Exception
    finally:
        # If conn has completed, close
        if conn is not None:
            conn.close()


# Function to create a table
def create_sql_table(table_name, table_details, project_settings):
    """
    Function to create a table in SQL
    :param table_name: String
    :param table_details: String
    :param project_settings: JSON Object
    :return: Boolean
    """
    # Create the query string
    sql_query = f"CREATE TABLE {table_name} (id SERIAL PRIMARY KEY, {table_details})"
    # Execute the query
    return sql_execute(sql_query=sql_query, project_settings=project_settings)


# Function to create a trade table
def create_trade_table(table_name, project_settings):
    """
    Function to create a trade table in SQL
    :param table_name: string
    :param project_settings: JSON Object
    :return: Boolean
    """
    # Define the table according to the CIM: https://github.com/jimtin/python_trading_bot/blob/master/common_information_model.json
    table_details = f"strategy VARCHAR(100) NOT NULL," \
                    f"exchange VARCHAR(100) NOT NULL," \
                    f"trade_type VARCHAR(50) NOT NULL," \
                    f"trade_stage VARCHAR(50) NOT NULL," \
                    f"symbol VARCHAR(50) NOT NULL," \
                    f"volume FLOAT4 NOT NULL," \
                    f"stop_loss FLOAT4 NOT NULL," \
                    f"take_profit FLOAT4 NOT NULL," \
                    f"price FLOAT4 NOT NULL," \
                    f"comment VARCHAR(250) NOT NULL," \
                    f"status VARCHAR(100) NOT NULL"
    # Pass to Create Table function
    return create_sql_table(table_name=table_name, table_details=table_details, project_settings=project_settings)


# Function to insert a trade action into SQL database
def insert_trade_action(table_name, trade_information, project_settings):
    """
    Function to insert a row of trade data
    :param table_name: String
    :param trade_information: Dictionary
    :return: Bool
    """
    # Make sure that only valid tables entered
    if table_name == "paper_trade_table" or table_name == "live_trade_table":
        # Make trade_information shorter
        ti = trade_information
        # Construct the SQL Query
        sql_query = f"INSERT INTO {table_name} (strategy, exchange, trade_type, trade_stage, symbol, volume, stop_loss, " \
                    f"take_profit, price, comment, status) VALUES (" \
                    f"'{ti['strategy']}'," \
                    f"'{ti['exchange']}'," \
                    f"'{ti['trade_type']}'," \
                    f"'{ti['trade_stage']}'," \
                    f"'{ti['symbol']}'," \
                    f"{ti['volume']}," \
                    f"{ti['stop_loss']}," \
                    f"{ti['take_profit']}," \
                    f"{ti['price']}," \
                    f"'{ti['comment']}'," \
                    f"'{ti['status']}'" \
                    f")"
        # Execute the query
        return sql_execute(sql_query=sql_query, project_settings=project_settings)
    else:
        # Return an exception
        return Exception # Custom Error Handling Coming Soon