36 Pandas & Numpy Strategies and Backtesting System

36 Pandas & Numpy Strategies and Backtesting System #

Hello everyone, I am Jingxiao.

In the previous lesson, we introduced the data retrieval from exchanges, specifically the retrieval of order book and tick data. In today’s lesson, we will discuss how to test a trading strategy using historical data.

First of all, we need to clarify that for many strategies, the dense order book and tick data we retrieved in the previous lesson cannot be directly used. This is because the data is too dense and contains too many details, and during long-term connections, the network may randomly experience instability, leading to the loss of some tick data. Therefore, we need to perform appropriate cleaning, aggregation, and other operations.

In addition, in order to conduct backtesting, we need a trading strategy and a testing framework. Currently, there are many mature backtesting frameworks available, but for the purpose of learning Python, I have decided to guide you in building a simple backtesting framework and give you a glimpse of the advantages of Pandas in the process.

OHLCV Data #

Some of you who are familiar with stock trading may be aware of K-lines. K-lines, also known as “candlestick charts,” are a type of graph that reflects price trends. They are characterized by recording multiple pieces of information within a line segment, making them easy to read, understand, and practical for technical analysis of stock, futures, precious metals, and cryptocurrency markets. The following is an example of a K-line chart.

K-line chart

In this chart, each small candle represents the opening price (Open), highest price (High), lowest price (Low), and closing price (Close) of a given day, as shown in the second image I provided.

“Small candle” of K-line - OHLC

Similarly, in addition to daily K-lines, there are also weekly K-lines, hourly K-lines, minute K-lines, and so on. So how are these K-lines calculated?

Let’s take an hourly K-line chart as an example. Do you remember the tick data we collected? The tick data represents the price and quantity of each transaction. Starting from 10:00 in the morning, if we start accumulating tick data and take the first transaction from 10:00 as the Open value and the last transaction before 11:00 as the Close value, we can take the highest and lowest prices of the hour as the values for High and Low. By doing this, we can create the shape of the “small candle” that corresponds to that hour.

By including the total trading volume for that hour (Volume), we obtain the OHLCV data.

Therefore, if we continue to collect tick-level raw data, we can aggregate it into higher-level data such as 1-minute K-lines, hourly K-lines, daily K-lines, weekly K-lines, etc. If you are interested in this process, you can try implementing it as today’s homework.

Next, we will use Gemini to obtain the BTC to USD OHLCV data for each hour from 2015 to July 2019 as input for our strategy and backtesting. You can download the data here.

Once the data is downloaded, we can use Pandas to read it, as shown in the code snippet below.

def assert_msg(condition, msg):
    if not condition:
        raise Exception(msg)

def read_file(filename):
    # Get the absolute file path
    filepath = path.join(path.dirname(__file__), filename)

    # Check if the file exists
    assert_msg(path.exists(filepath), "File does not exist")

    # Read the CSV file and return it
    return pd.read_csv(filepath,
                       index_col=0,
                       parse_dates=True,
                       infer_datetime_format=True)

BTCUSD = read_file('BTCUSD_GEMINI.csv')
assert_msg(BTCUSD.__len__() > 0, 'Failed to read file')
print(BTCUSD.head())

This code snippet provides two utility functions:

  • read_file, which uses Pandas to read a CSV file.
  • assert_msg, which is similar to assert. If the condition provided is False, it throws an exception. However, you need to provide a message as a parameter to specify the exception message.

Backtesting Framework #

After discussing the data, let’s move on to the backtesting framework. There are two main types of backtesting frameworks. One type is the vectorized backtesting framework, which is usually built using Pandas and Numpy for the calculation core. The backend is often using MySQL or MongoDB as the data source. This type of framework performs vectorized calculations on OHLC arrays using Pandas and Numpy, allowing backtesting on longer historical data. However, because this type of framework generally only uses OHLC data, the simulation may be rough.

The other type is the event-driven backtesting framework. This type of framework essentially generates events for each tick or order book change, and then passes these events to the strategy for execution. Although this type of framework allows for more flexible strategies, the backtesting speed is slower.

When it comes to learning quantitative trading, using a large and mature backtesting framework is naturally the first choice.

  • For example, Zipline is a popular event-driven backtesting framework with a large community and documentation support.
  • PyAlgoTrade is also an event-driven backtesting framework with relatively complete documentation, integrating the well-known technical analysis library TA-Lib. It is faster and more flexible than Zipline. However, one major drawback is that it does not support the Pandas module and objects.

Obviously, for us Python learners, the first type, the vectorized backtesting framework, is the most suitable project for us to practice. So let’s get started.

First, let’s clarify the backtesting process, which includes the following five steps:

  1. Read OHLC data.
  2. Perform indicator calculations on OHLC.
  3. The strategy generates buy and sell decisions based on the indicators.
  4. Send the orders to the simulated “exchange” for execution.
  5. Finally, analyze the results.

To achieve this, we can roughly extract three classes using the object-oriented programming approach we learned before:

  • ExchangeAPI: Responsible for maintaining account funds and positions, and executing simulated buy and sell orders.
  • Strategy: Responsible for generating indicators based on market information and making buy and sell decisions based on these indicators.
  • Backtest: Contains a strategy object and an exchange object, responsible for iteratively calling the strategy to execute on each data point.

Next, let’s start with the outermost layer, the Backtest class. The advantage of starting with this is that we are thinking from top to bottom and from the outside in. Although we haven’t designed the dependencies yet (Backtest depends on ExchangeAPI and Strategy), we can speculate on the interface they should have. Speculating the interface essentially means speculating the input and output of the program.

This is also what I mentioned at the beginning. When designing a program, you need to think about the input and output of the “black box” from the beginning.

Returning to the Backtest class, we need to know that the output is the final return, so obviously, the input should be the initial capital (cash).

In addition, to simulate more realistically, we also need to consider the transaction fees (commission) of the exchange. The amount of commission depends on the broker or exchange. For example, the brokerage commission for buying and selling stocks may be 0.0007, which is 0.07%. However, in the Bitcoin trading field, the transaction fees are usually slightly higher, around 0.2%. Of course, no matter how high it is, it usually does not exceed 5%. Otherwise, we would all go bankrupt after a few trades, and no one would trade anymore.

Here’s an interesting fact. Regardless of whether the price of cryptocurrency is rising or falling, there is always one party that never loses, and that is the exchange. Because as long as someone trades, they make money.

Back to the point, up to this point, we have determined the input and output of the Backtest class.

The inputs are:

  • OHLC data
  • Initial capital
  • Commission rate
  • ExchangeAPI class
  • Strategy class

The output is:

  • Final remaining market value

You can refer to the following code:

class Backtest:
    """
    Backtest class used for reading historical market data, executing strategies,
    simulating trades, and estimating returns.
    Call Backtest.run during initialization to perform the backtest. 
    Alternatively, call Backtest.optimize to optimize it.
    """

    def __init__(self,
                 data: pd.DataFrame,
                 strategy_type: type(Strategy),
                 broker_type: type(ExchangeAPI),
                 cash: float = 10000,
                 commission: float = .0):
        """
        Construct the backtest object. Required parameters include: historical data,
        strategy object, initial capital, commission rate, etc.
        The initialization process includes checking input types and filling data null values.

        Parameters:
        :param data:            pd.DataFrame        Historical OHLCV data in pandas DataFrame format
        :param broker_type:     type(ExchangeAPI)   Type of exchange API responsible for executing buy/sell operations and maintaining account status
        :param strategy_type:   type(Strategy)      Type of strategy
        :param cash:            float               Initial capital
        :param commission:      float               Commission rate
        """

:param commission:       float               The transaction fee rate for each trade. For example, if the fee is 2%, the value here is 0.02
"""

assert_msg(issubclass(strategy_type, Strategy), 'strategy_type is not a Strategy type')
assert_msg(issubclass(broker_type, ExchangeAPI), 'strategy_type is not a Strategy type')
assert_msg(isinstance(commission, Number), 'commission is not a float')

data = data.copy(False)

# If there is no Volumn column, fill it with NaN
if 'Volume' not in data:
    data['Volume'] = np.nan

# Verify the format of OHLC data
assert_msg(len(data.columns & {'Open', 'High', 'Low', 'Close', 'Volume'}) == 5,
           ("The format of the 'data' input is incorrect. At least these columns are required: "
            "'Open', 'High', 'Low', 'Close'"))

# Check for missing values
assert_msg(not data[['Open', 'High', 'Low', 'Close']].max().isnull().any(),
    ('Some OHLC data contains missing values, please remove those rows or fill in with interpolated data. '))

# If the market data is not sorted by time, sort it now
if not data.index.is_monotonic_increasing:
    data = data.sort_index()

# Initialize the exchange object and the strategy object using the data
self._data = data  # type: pd.DataFrame
self._broker = broker_type(data, cash, commission)
self._strategy = strategy_type(self._broker, self._data)
self._results = None

def run(self):
    """
    Run the backtest. Returns `pd.Series` with results and statistics.

    Keyword arguments are interpreted as strategy parameters.
    """
    strategy = self._strategy
    broker = self._broker

    # Initialize the strategy
    strategy.init()

    # Set the start and end positions of the backtest
    start = 100
    end = len(self._data)

    # Main loop of the backtest, update market state and execute strategy
    for i in range(start, end):
        # Move the market state to the i-th time step first, then execute the strategy
        broker.next(i)
        strategy.next(i)

    # After the strategy is executed, calculate the results and return
    self._results = self._compute_result(broker)
    return self._results

def _compute_result(self, broker):
    s = pd.Series()
    s['Initial Value'] = broker.initial_cash
    s['Final Value'] = broker.market_value
    s['Profit'] = broker.market_value - broker.initial_cash
    return s

This code is a bit long, but the core is actually two parts.

  • Initialization function (__init__): Accept necessary parameters, perform simple data cleaning, sorting, and validation on the OHLC data. The data we download from different sources may have different formats, and the sorting method may also be from the front to the back. Therefore, here we set the data to be sorted in ascending order of time.
  • Execution function (run): This is the main loop of the backtest framework, with the core parts being updating the market and updating the strategy’s time. After iterating through all the historical data, it calculates the profit and returns it.

You may have noticed that we haven’t defined the structure of the strategy and exchange API yet. However, through the execution function of the backtest, we can determine the interface format of these two classes.

The interface format of the strategy class (Strategy) is:

  • Initialization function init(): Calculate indicators based on historical data.
  • Step function next(): Based on the current time and indicators, decide the buy/sell operations and send them to the exchange class for execution.

The interface format of the exchange class (ExchangeAPI) is:

  • Step function next(): Update the latest price based on the current time.
  • Buy operation buy(): Buy assets.
  • Sell operation sell(): Sell assets.

Trading Strategy #

Next, let’s look at the trading strategy. The development of a trading strategy is a very complex discipline. To achieve our learning objectives, let’s consider a simple strategy - the moving average crossover strategy.

To understand this strategy, let’s first understand what is called a Simple Moving Average (SMA, hereinafter referred to as SMA). We know that the average of a sequence of N numbers, x[0], x[1], …, x[N], is the sum of these N numbers divided by N.

Now, I assume a relatively small number K, much smaller than N. We use a sliding window of size K on the original array. By calculating the average of each K elements within the window, we can obtain the SMA of the original array with a window size of K.

SMA essentially smoothes the original array. For example, if the price of a stock fluctuates significantly, then by smoothing with SMA, we will get the effect shown in the following chart.

SMA of a certain investment product, with a window size of 50

As you can see, the larger the window size, the smoother the SMA, and the slower the changes; conversely, if the SMA is relatively small, the short-term changes will be reflected more quickly in the SMA.

Therefore, we wonder if we can set two indicators for the price of an investment product? These two indicators are a small window SMA and a large window SMA.

  • If the small window SMA curve crosses or breaks through the large window SMA from below, it indicates that the price of this investment product is rising rapidly in the short term, and this trend is strong, which may be a buying signal.
  • Conversely, if the large window SMA crosses the small window SMA from below, it indicates that the price of the investment product is falling rapidly in the short term, and we should consider selling.

The following chart illustrates these two situations.

After understanding the concepts and principles here, the subsequent operations should not be difficult. Using Pandas, we can easily calculate SMA and SMA crossovers. For example, you can use the following two utility functions:

def SMA(values, n):
    """
    Calculate Simple Moving Average
    """
    return pd.Series(values).rolling(n).mean()

def crossover(series1, series2) -> bool:
    """
    Check if two series cross at the end
    :param series1:  Series 1
    :param series2:  Series 2
    :return:         True if crossover, else False
    """
    return series1[-2] < series2[-2] and series1[-1] > series2[-1]

As shown in the code, for an input array, the rolling(k) function of Pandas can conveniently calculate the SMA array with a window size of K; to check whether two SMAs cross at a certain point in time, you only need to look at the last two elements of each array.

Based on this, we can develop a simple strategy. The following code represents the core idea of the strategy, with detailed comments that you should have no problem understanding:

def next(self, tick):
    # Buy all if the fast line just crosses the slow line
    if crossover(self.sma1[:tick], self.sma2[:tick]):
        self.buy()

    # Sell all if the slow line just crosses the fast line
    elif crossover(self.sma2[:tick], self.sma1[:tick]):
        self.sell()

    # Otherwise, do nothing at this point in time.
    else:
        pass

After explaining the core idea of the strategy, let’s start building the framework of the strategy class.

First, we need to consider that the strategy class Strategy should be an inheritable class and should include some fixed interfaces. This way, the backtester can easily call them.

Therefore, we can define an abstract class Strategy, which includes two interface methods: init and next, corresponding to the indicator calculation and stepping function we mentioned earlier. However, note that abstract classes cannot be instantiated. So, we must define a concrete subclass that implements both the init and next methods.

You can refer to the implementation of this class in the following code:

import abc
import numpy as np
from typing import Callable

class Strategy(metaclass=abc.ABCMeta):
    """
    Abstract strategy class for defining trading strategies.

    To define your own strategy class, you need to inherit this base class and implement two abstract methods:
    Strategy.init
    Strategy.next
    """
    def __init__(self, broker, data):
        """
        Constructs a strategy object.
        """

@params broker: ExchangeAPI    The trading API interface used for simulating trading
@params data:   list           Market data
"""
self._indicators = []
self._broker = broker  # type: _Broker
self._data = data  # type: _Data
self._tick = 0

def I(self, func: Callable, *args) -> np.ndarray:
    """
    Calculate buy and sell indicator vectors. The indicator vector is an array that corresponds to the length of historical data;
    It is used to determine whether to "buy" or "sell" at this time point.

    For example, calculating the moving average:
    def init():
        self.sma = self.I(utils.SMA, self.data.Close, N)
    """
    value = func(*args)
    value = np.asarray(value)
    assert_msg(value.shape[-1] == len(self._data.Close), 'The length of the indicator must be the same as the length of the data')

    self._indicators.append(value)
    return value

@property
def tick(self):
    return self._tick

@abc.abstractmethod
def init(self):
    """
    Initialize the strategy. Called once during strategy backtesting/execution to initialize the internal state of the strategy.
    Auxiliary parameters of the strategy can also be pre-calculated here. For example, based on historical market data:
    Calculate the indicator vector for buying and selling;
    Train the model/initialize model parameters
    """
    pass

@abc.abstractmethod
def next(self, tick):
    """
    Step function that executes the strategy at the tick step. tick represents the current "time". For example, data[tick] is used to access the current market price.
    """
    pass

def buy(self):
    self._broker.buy()

def sell(self):
    self._broker.sell()

@property
def data(self):
    return self._data

In order to facilitate accessing members, we also define some Python properties. At the same time, our buy and sell requests are issued by the strategy class and executed by the exchange API, so our strategy class depends on the ExchangeAPI class.

Now, with this framework, implementing the moving average crossover strategy is very simple. You just need to define the logic for calculating the fast and slow moving averages in the init function; at the same time, complete the crossover detection and buy-sell calls in the next function. For the specific implementation, you can refer to the following code:

```python
from utils import assert_msg, crossover, SMA

class SmaCross(Strategy):
    # The window size of the small window SMA used to calculate the fast line
    fast = 10

    # The window size of the large window SMA used to calculate the slow line
    slow = 20

    def init(self):
        # Calculate the fast line and slow line at each moment in history
        self.sma1 = self.I(SMA, self.data.Close, self.fast)
        self.sma2 = self.I(SMA, self.data.Close, self.slow)

    def next(self, tick):
        # If the fast line has just crossed above the slow line, buy all
        if crossover(self.sma1[:tick], self.sma2[:tick]):
            self.buy()

        # If the slow line has just crossed above the fast line, sell all
        elif crossover(self.sma2[:tick], self.sma1[:tick]):
            self.sell()

        # Otherwise, no operation is performed at this moment.
        else:
            pass

Simulated Trading #

At this point, we are almost done with our backtesting. Victory is within reach, let’s keep going!

As mentioned earlier, the exchange is responsible for simulating trades, and the basis of the simulation is the current market price. Here, we can use the Close price from the OHLC data as the price at that moment.

Furthermore, to simplify the design, we assume that buying and selling operations use all the available funds and positions in the current account, and that the market capacity is large enough. This way, our order requests can be executed immediately and completely.

Let’s not forget about the major part - transaction fees. Considering the existence of transaction fees, how should we write the core buy and sell functions?

Let’s think about this together. Suppose we have 1000.0 yuan at the moment and the price of BTC is 100.00 yuan (of course, this is a hypothetical situation), and the transaction fee is 1%. How many BTC can we buy?

We can use the following algorithm:

Quantity bought = Invested funds * (1.0 - Transaction fee) / Price

So in this case, you would be able to get 9.9 BTC.

Similarly, the settlement method for selling is as follows:

Proceeds from selling = Held quantity * Price * (1.0 - Transaction fee)

Finally, you can refer to the following code to implement the simulated exchange class:

from utils import read_file, assert_msg, crossover, SMA

class ExchangeAPI:
    def __init__(self, data, cash, commission):
        assert_msg(0 < cash, "Initial cash quantity should be greater than 0, input cash quantity: {}".format(cash))
        assert_msg(0 <= commission <= 0.05, "Reasonable commission rate should not exceed 5%, input rate: {}".format(commission))
        self._inital_cash = cash
        self._data = data
        self._commission = commission
        self._position = 0
        self._cash = cash
        self._i = 0

    @property
    def cash(self):
        """
        :return: Returns the current account cash quantity
        """
        return self._cash

    @property
    def position(self):
        """
        :return: Returns the current account position
        """
        return self._position

    @property
    def initial_cash(self):
        """
        :return: Returns the initial cash quantity
        """
        return self._inital_cash

    @property
    def market_value(self):
        """
        :return: Returns the current market value
        """
        return self._cash + self._position * self.current_price

    @property
    def current_price(self):
        """
        :return: Returns the current market price
        """
        return self._data.Close[self._i]

    def buy(self):
        """
        Buy all with the remaining funds in the current account based on the market price
        """
        self._position = float(self._cash / (self.current_price * (1 + self._commission)))
        self._cash = 0.0

    def sell(self):
        """
        Sell the remaining holdings in the current account
        """
        self._cash += float(self._position * self.current_price * (1 - self._commission))
        self._position = 0.0

    def next(self, tick):
        self._i = tick

In this code, current_price can be used conveniently to obtain the market price at the current moment, and market_value can be used to obtain the current total market value. During initialization, we check if the commission rate and input cash quantity are within a reasonable range.

With all these pieces in place, we can now simulate backtesting!

First, let’s set the initial funding to $10,000.00 and the exchange commission rate to 0%. Can you guess how much money we should have now if we followed the SMA to buy and sell from 2015 until now?

def main():
    BTCUSD = read_file('BTCUSD_GEMINI.csv')
    ret = Backtest(BTCUSD, SmaCross, ExchangeAPI, 10000.0, 0.00).run()
    print(ret)

if __name__ == '__main__':
    main()

And the answer is revealed - the program will output:

Initial market value: 10000.000000
Final market value: 576361.772884
Return: 566361.772884

Wow, at the end, we would have $570,000, which means a whopping 57 times increase! Absolutely amazing. But wait, this 0% commission rate is a bit unrealistic because it never happens in real life. Let’s set a more realistic value, say 0.3‰, and give it a try:

Initial market value: 10000.000000
Final market value: 2036.562001
Return: -7963.437999

What?! We ended up losing money and only have $2000 left! Is this for real?

Yes, it is real, and it is also not real.

When I say “real”, I mean if you really use simple methods like SMA cross to trade, the transaction friction and slippage factors can indeed make your high-frequency strategies lose money.

And when I say “not real”, I mean that this simulation method is very crude. The real market conditions are not as ideal as this - for example, buy and sell requests are never executed immediately, and our trading in the market does not affect the market price, etc. These ideal conditions are simply not possible. So, in many cases, backtesting always makes money, but live trading loses money immediately.

Summary #

In this lesson, we built upon the previous one and introduced the classification of backtesting frameworks and the format of data. We also guided you through the process of building a simple backtesting system from scratch. You can put together the code snippets from today to create a simplified example of a backtesting system. In addition, we implemented a simple trading strategy and ran backtests on real historical data. We observed that the addition of transaction fees significantly changed the profitability of the strategy.

Reflection Question #

Finally, I’ll leave you with a reflection question. Previously, we discussed how to fetch tick data. Can you generate 5-minute, hourly, and daily OHLCV data based on the fetched tick data? Feel free to write down your answers and questions in the comments section. Also, feel free to share this article with others.