Home Trading Strategy Backtest Modelling Bid/Offer Spread In Equities Trading Strategy Backtest

Modelling Bid/Offer Spread In Equities Trading Strategy Backtest

by s666

In this blog post I wanted to run a couple of quick experiments to see how clearly I was able to highlight the importance of incorporating various elements and components into a backtest that I admittedly often overlook in most of my posts – that is I make the assumption that they will be dealt with by the reader at some point down the line, but choose not to include them for sake of simplicity.

That probably isn’t a great way to proceed without at least explicitly demonstrating just how important these “auxiliary” factors can be. Well actually, perhaps “auxiliary” isn’t a great label as again, for a backtest to be considered valid all these elements do need to be accounted for – elements such as brokerage costs/commissions, slippage, bid/offer spread, liquidity/order book depth and so on.

In this post I will concentrate on the difference in outcomes that result from simply incorporating a more realistic way of accounting for the bid/offer spread when trading equities. I will be using minute bar data, with each minute containing information such as the opening bid/offer prices, the closing bid/offer prices and corresponding size available at those prices, the actual trades that took place and in what kind of size, the maximum and minimum spread recorded in that minute and so on. The full list of data points are as follows:

The particular data set I am using was sourced from www.AlgoSeek.com – they provide a wide range of financial asset pricing data, covering stocks, ETFs, equity indices, options and futures also providing a choice across different granularity and depth. The data I am working with falls under the “stocks minute bars” data set, although level 2/tick data, trades and quotes, trades only, minute bars and even an option for “custom bars” exist if you have a particularly unique requirement.

Once subscribed its a relatively painless process to link up to their API download via an Amazon Web Server (AWS) account – I hooked up using the command line and was able to download data by running a simple line or two in the terminal.

So the plan for this experiment is to create a very simple, moving average cross over strategy based on a simple short 50-period moving average and a simple long 200-period moving average. The logic is simply to be long when the short moving average is above the long moving average, and vice versa be short when the short moving average is below the long moving average. This way, the strategy calls for us to be either long or short at all times (except at the very start before our first entry signal of course!).

The data from AlgoSeek actually includes info for periods outside of the main 9:30 am – 4 pm trading day so I have chosen to extract only the data for each day that falls between those two times, i.e. data that corresponds to when the cash equity markets are officially open and trading normally.

We start as always with our module imports and I also set the styling for my notebook using a module named “jupyterthemes” – it can be found here if at all interested (https://github.com/dunovank/jupyter-themes).

I then read in the relevant csv file containing data relating to the “SH” stock – i.e. the ProShares Short S&P500.

The below is what I am presented with when displaying the top 5 rows of the DataFrame.

# Import necessary modules
import os
import pandas as pd
import numpy as np

import matplotlib as mpl
import matplotlib.pyplot as plt
import seaborn as sns
from jupyterthemes import jtplot

%matplotlib inline

jtplot.style()
jtplot.figsize(x=12., y=8.)

import warnings
warnings.filterwarnings("ignore")



stocks_data_folder = r'G:\PFF\AlgoSeek\Data\post\stocks'

stock = 'SH'

df = pd.read_csv(os.path.join(stocks_data_folder,stock+'.csv'), parse_dates=True, index_col=0)
df.index.name = 'Date_'

df = df.between_time('9:40', '15:50')

Next, I create a new column to hold the spread between the bid and offer prices at each minute bar – I have chosen to use the “OpenAskPrice” and “OpenBidPrice” for this calculation. I don’t see the choice between using either “Open” or “Close” values as having much of an effect on the ending results. I also then calculate the average bid/offer spread over the entire period, and finally plot a chart showing the “LastTradePrice” from each minute bar over the whole period also ( I have renamed this column “Close” for ease of use later on FYI). One thing to note perhaps is the decision to use the “.ffill()” method on the “Close” column; this was simply based on the logic that most minute bars will have a valid price already, and any that don’t will be filled with the last available price from the previous minute bar (or from the bar that last saw a valid trade).

I had debated as to whether to calculate value based on the “OpenAskPrice” and “OpenBidPrice” instead, thinking it may be a more realistic option – however I noticed that there were some instances where the bid/offer spread jumped dramatically (to an anomalous level) which was generating false trade signals and skewing the backtest results.

df['Close'] = df['LastTradePrice']

df['Spread'] = df['OpenAskPrice'] - df['OpenBidPrice']

df['Close'].ffill(inplace=True)

average_spread = round(df['Spread'].mean(),4)

print(f'Average spread: {average_spread}')

df['Close'].plot()
plt.title('Price Chart for ProShares Short S&P500 (SSH)')
plt.xlabel('Date')
plt.ylabel('Price')
plt.show()

The below code implements the logic outlined above, and records the resulting “strategy returns” – that is the daily market returns are adjusted to represent whether our holding is currently long or short and are then stored as our strategy returns. Finally, a chart is plotted to display the strategy vs market returns.

sma = 50
lma = 200

#create moving averages columns
df['sma'] = np.round(df['Close'].rolling(window=sma).mean(),2)
df['lma'] = np.round(df['Close'].rolling(window=lma).mean(),2)

#create column with moving average spread differential
df['sma-lma'] = df['sma'] - df['lma']

#set desired number of points as threshold for spread difference and create column containing strategy 'Stance'
X = 0.00
df['Stance'] = np.where(df['sma-lma'] >= X, 1, np.nan)
df['Stance'] = np.where(df['sma-lma'] < -X, -1, df['Stance'])
df.dropna(subset=['Stance'],inplace=True)

#identify trade times when the position switches between long and short
df['Trade'] = (df['Stance'] != df['Stance'].shift())
#drop the first row as it will show a True value but no trade was made
df = df.iloc[1:]
no_trades = df['Trade'].sum()
print(f'Number of trades: {no_trades}')

#create columns containing daily market log returns and strategy daily log returns
df['Market Log Returns'] = np.log(df['Close'] / df['Close'].shift(1))
df['Strategy Log Returns'] = df['Market Log Returns'] * df['Stance'].shift(1)

#create columns containing daily market arithmetic returns and strategy daily arithmetic returns
df['Market Returns'] = df['Close'].pct_change()
df['Strategy Returns'] = df['Market Returns'] * df['Stance'].shift(1)

#show chart of equity curve
(1 + df[['Market Returns','Strategy Returns']]).cumprod().plot()
plt.title('Moving Average Strategy Performance vs ProShares Short S&P500 (SSH)')
plt.xlabel('Date')
plt.ylabel('Equity Index')
plt.show()

So from initial inspection, it seems that although neither the stock price returns, nor our strategy returns ended the period in positive territory our strategy fared slightly better. The strategy ended down around 8% while the stock price fell around 17.5% over the same period. Well, not ideal but better than it could be right? Maybe.

Let’s now incorporate some logic into our backtest to account for the fact that we don’t get to actually execute our trades at the “Close” price – i.e. at the last traded price. We have to cross the bid/offer spread – if we are selling we have to hit the bid and if we are buying, likewise we have to hit the offer (assuming we are using market orders that is).

The code below works this concept in by identifying whether we are buying or selling and then either adds or subtracts half the average spread (which itself was calculated as the simple average of the spreads recorded throughout the backtest period). The values are rounded to the nearest 0.01 to mimic the fact the market prices for the ProShares Short S&P500 move in 1 cent increments.

OK so let’s run the code below and see what effect that has on our strategy performance and returns.

df_trades_mean = df[df['Trade']].copy()
df_trades_mean['TradePrice'] = df_trades_mean['Close']

df_trades_mean['TradeExecutionPrice'] = np.where(df_trades_mean['Stance'] == 1, 
                                            round(df_trades_mean['Close'] + (average_spread/2),2), 
                                            round(df_trades_mean['Close'] - (average_spread/2),2))

df_trades_mean['TradePriceLogRets'] = np.log(df_trades_mean['TradePrice']).diff() * df_trades_mean['Stance'].shift(1)
df_trades_mean['TradeExecutionPriceLogRets'] = np.log(df_trades_mean['TradeExecutionPrice']).diff() * df_trades_mean['Stance'].shift(1)

df_trades_mean['MarketRets'] = df_trades_mean['TradePrice'].pct_change()
df_trades_mean['TradePriceRets'] = df_trades_mean['TradePrice'].pct_change() * df_trades_mean['Stance'].shift(1)
df_trades_mean['TradeExecutionPriceRets'] = df_trades_mean['TradeExecutionPrice'].pct_change() * df_trades_mean['Stance'].shift(1)

(1 + df_trades_mean[['TradeExecutionPriceRets','TradePriceRets', 'MarketRets']]).cumprod().plot()
plt.show()

Well, that’s a pretty dramatic change! Not only is our strategy now significantly worse in terms of performance, but it is also actually now far worse than a simple buy and hold strategy. Now, this simple strategy generated 882 trade signals across the year, which by any one’s reasoning is a lot of trades, especially when considering we are trading cash equities which generally come with higher trading costs than some other assets. Still, the difference is marked! It looks like a completely different strategy and one that one would be wise to dispose of into the nearest rubbish bin (or “trash can” for our dear US readers) at the earliest opportunity!!

So maybe it’s not fair to just naively apply the average spread to each and every one of our trades – is there a way we could replicate the stochastic natures of the spread itself? The easiest way would be to generate a random sample of spreads based on the empirical distribution of spreads actually observed over the period in question. The code below does just this and creates an array of spread values through the use of random sampling with replacement from an array of historically observed spread values.

df_trades_sample = df[df['Trade']].copy()
df_trades_sample['TradePrice'] = df_trades_sample['Close']

df_trades_sample['TradeExecutionPrice'] = np.where(df_trades_sample['Stance'] == 1, 
                                            df_trades_sample['Close'] + (np.random.choice(df['Spread'], len(df_trades_sample), replace=True)/ 2), 
                                            df_trades_sample['Close'] - (np.random.choice(df['Spread'], len(df_trades_sample), replace=True)/ 2))

df_trades_sample['TradePriceLogRets'] = np.log(df_trades_sample['TradePrice']).diff() * df_trades_sample['Stance'].shift(1)
df_trades_sample['TradeExecutionPriceLogRets'] = np.log(df_trades_sample['TradeExecutionPrice']).diff() * df_trades_sample['Stance'].shift(1)

df_trades_sample['MarketRets'] = df_trades_sample['TradePrice'].pct_change()
df_trades_sample['TradePriceRets'] = df_trades_sample['TradePrice'].pct_change() * df_trades_sample['Stance'].shift(1)
df_trades_sample['TradeExecutionPriceRets'] = df_trades_sample['TradeExecutionPrice'].pct_change() * df_trades_sample['Stance'].shift(1)

(1 + df_trades_sample[['TradeExecutionPriceRets','TradePriceRets', 'MarketRets']]).cumprod().plot()
plt.show()

So this looks slightly kinder on our strategy backtest results, however, the outcome is still firmly in “unsuccessful” territory to the extent that it can still be deemed to be unsalvagable.

One final approach worth looking at is of course just to simulate the strategy buys and sells based on the actual bid/offer prices available at the time. This is a legitimate approach, however, it should be noted that this implicitly assumes there is enough size/volume available on the order book at that price to fill your required order. For that reason this method creates, by definition an overly optimistic, “best case” scenario that would only be realised if there was ALWAYS enough size available to absorb your trades at the prevailing bid/offer price, for each and every trade made in the backtest.

Let’s see what the results look like.

df_trades_bo2 = df[df['Trade']].copy()
df_trades_bo2['TradePrice'] = df_trades_bo2['Close']

df_trades_bo2['TradeExecutionPrice'] = np.where(df_trades_bo2['Stance'] == 1, df_trades_bo2['OpenAskPrice'], df_trades_bo2['OpenBidPrice'])

# df_trades_bo2['TradePriceLogRets'] = np.log(df_trades_bo2['TradePrice']).diff() * df_trades_bo2['Stance'].shift(1)
# df_trades_bo2['TradeExecutionPriceLogRets'] = np.log(df_trades_bo2['TradeExecutionPrice']).diff() * df_trades_bo2['Stance'].shift(1)

df_trades_bo2['MarketRets'] = df_trades_bo2['TradePrice'].pct_change()
df_trades_bo2['TradePriceRets'] = df_trades_bo2['TradePrice'].pct_change() * df_trades_bo2['Stance'].shift(1)
df_trades_bo2['TradeExecutionPriceRets'] = df_trades_bo2['TradeExecutionPrice'].pct_change() * df_trades_bo2['Stance'].shift(1)

(1 + df_trades_bo2[['TradeExecutionPriceRets','TradePriceRets', 'MarketRets']]).cumprod().plot()
plt.show()

So the results are slightly kinder yet again, as a direct result of the implicit over-optimism mentioned just previously. The strategy now ends up the period down around 25% as opposed to down 28% for the randomly sampled spread approach and down 45% for our model which applied half the average observed spread to each trade.

I’m a believer in erring on the side of caution in general, and especially so when evaluating strategy backtests etc.

Granted this was a very simplistic, “toy” example which admittedly generated a very large number of trade signals, which would be a red flag straight away when your underlying asset happens to be an equity – they ain’t cheap to trade in and out of, never mind the fact that they aren’t always available for shorting purposes which can torpedo a proposed “long/short” strategy before you even get to square one. Even so, hopefully, the above can help demonstrate just how large an effect these things can have, things that are often seen as “secondary” in nature when creating/optimising/evaluating a proposed strategy.

This kind of analysis and ability to include and model the bid/offer spread and slippage etc relies on the availability of high quality, granular and in-depth data; without it the task becomes significantly harder and subject to far higher degrees of randomness and error, and a corresponding fall in confidence one can attach to the final backtest results.

Until next time…

You may also like

Leave a Reply