【Quant(8)】Backtesting by MACD Indicator

Use free database to do backtesting

TEJ 台灣經濟新報

Published in

TEJ-API Financial Data Analysis

6 min readOct 5, 2021

Highlights

Difficulty：★★☆☆☆
MACD backtesting and identification of buying and selling points
Advice: This article demonstrates the backtesting practice by using technical analysis indicator MACD and visualizing the results. Also, this article is intended to provide readers the framework and the aspects that need to be considered while doing the backtesting, instead of promoting the usefulness of certain indicators. If readers are interested in other indicators, please refer to 【Quant(2)】- Technical Analysis and come up with your own trading strategies !

Preface

MACD, standing for moving average convergence divergence, can be used to identify the medium-term and long-term trend of stock price. If the fast line (DIF) crosses from below to above the slow line(MACD), indicating there’s an upside momentum. On the contrary, when the fast line crosses from above to below the slow line, the stock price is more likely to go up in the future. Since this kind of indicator can produce clear signals, it’s proper to backtest the performance of strategies based on it.

An effective backtesting should not only have clear signals, but also have to take the timing of the occurrence of signals, transaction fee and the minimum charge of it, and transfer tax into consideration. When calculating the return of the strategy, cash position in hand should also be considered to have a more realistic performance evaluation of participating in the stock market.

The Editing Environment and Modules Required

Windows OS and Jupyter Notebook

#Basic function
import numpy as np
import pandas as pd#Visualization
import plotly.graph_objects as go
from plotly import subplots#TEJ
import tejapi
tejapi.ApiConfig.api_key = "Your Key"
tejapi.ApiConfig.ignoretz = True

Database Used

Trial Database : the code of the database is ‘ TRAIL/TAPRCD ’, covering the unadjusted stock price of listed firms in 2020

Data Processing

Step 1. Obtain the stock price

stock_data = tejapi.get('TRAIL/TAPRCD',
            coid= '3481',
            mdate={'gte': '2020-01-01', 'lte': '2020-12-31'},
            opts={'columns': ['coid', 'mdate', 'open_d','close_d']},
            chinese_column_name=True,
            paginate=True)

We choose Innolux Corporation (3481) as an example and the columns include opening price, which is used to calculate buying and selling price, and closing price that will produce buy and sell signal.

Step 2. Generate signal

stock_data['12_ema'] = stock_data['收盤價(元)'].ewm(span = 12).mean()
stock_data['26_ema'] = stock_data['收盤價(元)'].ewm(span = 26).mean()
stock_data['dif'] = stock_data['12_ema']  - stock_data['26_ema']
stock_data['macd'] = stock_data['dif'].ewm(span = 9).mean()

Then we use the closing price to calculate the MACD. First of all, ewm().mean() is applied to produce 12-day and 26-day exponential moving averages. The difference between these two averages is fast line (DIF). If we further take 9-day exponential moving average of fast line, then we get the slow line (MACD)

stock_data['買賣股數'] = 0 #golden cross
stock_data['買賣股數'] = np.where((stock_data['dif'].shift(1)>stock_data['macd'].shift(1)) & (stock_data['dif'].shift(2)<stock_data['macd'].shift(2)), 1000, stock_data['買賣股數'])#death cross
stock_data['買賣股數'] = np.where((stock_data['dif'].shift(1)<stock_data['macd'].shift(1)) & (stock_data['dif'].shift(2)>stock_data['macd'].shift(2)), -1000, stock_data['買賣股數'])

Once the fast line and slow line are calculated, then we can start to build the signals of buying and selling. If the DIF was still lower than the MACD two days ago, but suddenly surpassed the MACD yesterday, meaning there was a ‘golden cross’ yesterday. Since the buying signal popped up after the market closed, we’ll buy 1000 shares at opening price the next day. Similarly, if death cross occurs, we are going to sell 1000 shares at opening price the next day.

stock_data['手續費'] = stock_data['開盤價(元)']* abs(stock_data['買賣股數'])*0.001425
stock_data['手續費'] = np.where((stock_data['手續費']>0)&(stock_data['手續費'] < 20), 20, stock_data['手續費'])
stock_data['證交稅'] = np.where(stock_data['買賣股數']<0, stock_data['開盤價(元)']* abs(stock_data['買賣股數'])*0.003, 0)
stock_data['摩擦成本'] = (stock_data['手續費'] + stock_data['證交稅']).apply(np.floor)

Here comes the friction costs which include transaction fees(0.1425%) and transfer tax (0.3%) in Taiwan. When we buy the stock, the cost incurred is the transaction fee. It’s worth noting here that there’s a minimum charge of 20 NTD for transaction fees if the dealers don’t offer any discount. While we sell the stock, what we need to afford is both transaction fees and transfer fee

Calculate the Return

stock_data['股票價值'] = stock_data['買賣股數'].cumsum() * stock_data['收盤價(元)']
stock_data['現金價值'] = 10000 - stock_data['摩擦成本'] + (stock_data['開盤價(元)']* -stock_data['買賣股數']).cumsum() 
stock_data['資產價值'] = stock_data['股票價值'] + stock_data['現金價值']

Suppose we have 10,000 NTD at the beginning. Total asset value equals stock position plus cash position. The value of stock position fluctuates with the number of holding shares and stock price; while the value of cash position changes with buying or selling actions, and also the friction costs incurred.

stock_data['當日價值變動(%)'] = (stock_data['資產價值']/stock_data['資產價值'].shift(1) - 1)*100
stock_data['累計報酬(%)'] = (stock_data['資產價值']/10000 - 1)*100

Next we can calculate the daily change in total asset value and cumulative return

Visualization

Step 1. Observe the buying and selling points (The code can be seen in source code)

Here we can see this strategy does capture some trend and earn the profits. However, it’s risky when stock price barely changes in some periods and keep triggering buying and selling points

Step 2. Strategy performance (The code can be seen in source code)

As we can see in the picture, the daily total asset value changes within 8%. If there’s no change, it means the stock price remains the same or we only hold cash. The cumulative return is around 63% at the end, better than the 2020 Taiwan stock market performance.

Conclusion

Readers may find out that this kind of strategy may result in overtrading and incur lots of friction costs, which leads to its performance could be worse than buy and holding strategy. The amount of beginning capital also shows the importance of asset allocation. If we hold too much cash in hand, the overall return will decrease, but the daily volatility could be lower as well. In fact, to get closer to the strategy performance in real life, we should also consider the dividend or other corporate policies that affect stock price. The reason to choose unadjusted stock price in this article is because we want to show the buying and selling points at that time. Furthermore, we may short the stock for some firms with regard to this strategy, then we also have to consider margin issues. But for Innolux Corporation, we have no shorting action. If readers want to backtest longer periods or access more data, welcome to TEJ E-Shop to choose your own optimal plan!