# SPL quantitative practice series: backtesting routine

One essential step in quantitative trading is backtesting. Any strategy needs to be validated with historical data before it can be used for paper trading or even real trading. This article will introduce how to conduct backtesting in SPL.

Without further ado, let’s get straight to the point.

Suppose we have the following trade data generated according to a certain strategy:

In this table, ‘ts_code’ refers to the stock code; ‘trade_date’ refers to the trade date; ‘shares’ refers to the number of shares traded, and ‘flag’ refers to the trade flag (1 denotes buy, -1 denotes sell).

Now we need to conduct a backtest on the strategy based on the trade data and the following daily market data:

Because some stocks may involve cash dividend, stock dividend, etc., their prices and trading volume need to be adjusted accordingly (such operation is hereinafter referred to as ‘adjustment’). There are two types of adjustments: forward adjusting and backward adjusting. Since this is a backtest task, we use the backward adjusting method. As for the specific steps, it will not be introduced in detail here. If you are interested, refer to the article Adjustment of Stock Price.

Common backtest indicators:

1. Cumulative rate of return: the ratio of return on the last day to the invested capital.

2. Annualized rate of return: (cumulative rate of return + 1)(252/number of trading days)-1, where ‘’ is the power exponent symbol; 252 is an assumed fixed number of trading days in a year; number of trading days is the interval between the start and end time of the strategy.

3. Annual volatility: daily volatility *(square root of 252), where the daily volatility is the standard deviation of daily rate of return, which can be used to measure risk.

4. Sharpe ratio: the ratio of the annualized rate of return minus risk-free rate of return to the annual volatility, which is used to measure the risk-reward ratio of stock portfolio or fund.

5. Maximum drawdown: the ratio of the difference between the highest rate of return and the subsequent lowest rate of return to the highest rate of return plus 1.

6. Invested cash: all cash (including commission) required to complete these transactions.

7. Total assets: cash available after completing these transactions plus stock value.

8. Stock holding ratio: the ratio of the existing stock value to total assets.

9. Profit times, losses times: the profit times and the losses times in these transactions. As long as the income increases after a stock is sold, it is considered profit once. Conversely, if the income decreases, it is considered loss once.

SPL code:

Explanation on key codes:

A1: The trade_data refers to the trade signal and the number of shares. Because this is backtest method, this data is used as a parameter for the ease of calling by other programs. The trade_data in this example is as follows:

A2: The end_date refers to the end date of transaction. The reason for taking it as a parameter is to facilitate the subsequent retrieval of daily market data.

A10: Add the adjustment factor and forward and backward adjusting close prices for each stock. See below for details:

A12: Associate the trading data with market data by trade date. The results are as follows:

When associating by a date when no transaction occurs, the number of shares and trading flag are null.

A13: Add the holding amount, cash amount, commission, and income.

In this code, holding amount = number of shares held * backward adjusting close price; number of shares held = existing number of shares + number of shares traded * trade flag.

Cash amount: for buy operation, cash amount = available cash - holding amount; for sell operation, cash amount = available cash + backward adjusting close price * number of shares sold.

Commission:

Commission for sell operation: max (buy amount * 0.03%, 5) + buy amount *(0.001%+0.05%).

In these two formulas, 0.03% represents the max commission rate charged by the broker; 5 represents the min commission, that is, when the commission is less than 5, it will be charged at USD 5; 0.001% represents the transfer fee; and 0.05% represents the stamp duty (charged only when selling).

Income: holding amount + cash amount – commission.

Note: Since we assume the initial cash amount is 0, the cash amount and income may be negative.

Part of the data is shown below:

A15: Summarize the holding amount, income, and required cash by day.

Calculate all holding stocks by day. The holding amount and income are both simple summation calculation. The required cash = commission - cash amount (calculating this way is because that the commission is positive, while the cash amount may be negative), and then sum. If there is a buy action on a day, the buy flag (buy) = 1, otherwise buy = 0; if there is a sell action on a day, the sell flag (sell) = -1, otherwise sell = 0. Calculation results:

A16: The max required cash in A15 is exactly the amount of capital that needs to be invested in these transaction processes.

In general, we also want to see a trend chart for rate of return since the transaction. Below we will introduce the data required for this chart.

In A15, the daily profit and buy/sell point have been calculated. We just need to divide the daily profit by the invested capital to obtain the daily rate of return, and convert the trade_date to date format.

SPL code: