【Application(9)】Brinson Model Performance attribution
Analyze the source of portfolio performance
Highlights
- Difficulty：★★☆☆☆
- Performance attribution refers to the excess return between portfolio and its assigned benchmark into the detail return. The detail return can be used to quantify the impact of the portfolio’s industries, stock-selection timing and stock selection ability on return and risk.
- Linking to code
Preface
Portfolio’s performance is affected by many factors. It is not easy for us to clearly distinguish whether the performance is due to the rise in the market, the stock selection ability of traders, or the proper allocation of assets or industries? Therefore, we can use the performance attribution proposed by Brinson (1985) to disassemble the excess return between the investment portfolio and its assigned benchmark into the selection effect, the interaction effect and the allocation effect. Moreover, performance attribution allows us to look across a specific time horizon and identify which investment decisions have either added or detracted value from the portfolio, relative to its benchmark.
This article use the Taiwan 50 Index as the benchmark of the 00881 ETF, and analyze the performance attribution of the 00881 ETF through the Brinson model.
The Editing Environment and Modules Required
Windows OS and Jupyter Notebook
# 功能模組
import pandas as pd
import numpy as np
import statsmodels.api as sm
import matplotlib.pyplot as plt
from scipy.stats import wilcoxon# TEJ API
import tejapi
tejapi.ApiConfig.api_key = 'Your key'Database Used
- List of ETF companies: the code of the database is (TWN/AEHOLD)
- List of Index companies: the code of the database is (TWN/AIDXS)
- Security Characteristics Document: the code of the database is (TWN/EWNPRCSTD)
- Adjusted daily stock price: the code of the database is (TWN/APRCD1)
Data Processing
Step 1. Import ETF companies and Taiwan 50 Index companies
We import the 00881 ETF and Taiwan 50 Index stocks updated daily from September to November 2021 from the TEJ database, which can save us a lot of time to inquiry from securities investment trust that issued the ETF. If you want to use a large number of ETF stocks, the advantage of using TEJ database is more obvious.
#%% 匯入 TEJ資料
etf = tejapi.get('TWN/AEHOLD',
coid = '00881',
mdate= {'gte': '2021-09-01','lte':'2021-11-30'},
opts={'columns':['mdate', 'no','pct']},
chinese_column_name=True,paginate=True)# 標竿指數：臺50指數
benchmark = tejapi.get('TWN/AIDXS',
coid = 'TWN50',
mdate= {'gte': '2021-09-01','lte':'2021-11-30'},
opts={'columns':['mdate','key3','mv_pct']},
chinese_column_name=True,paginate=True)etf = etf[~etf['標的名稱'].isin(['申贖應付款','保證金','現金'])]
etf['證券碼'] = etf['標的名稱'].str[0:4]
etf['證券碼'] = np.where(etf['證券碼'] == 'TX 台','Y9999',etf['證券碼'])
etf['年'] = etf['日期'].dt.year
etf['月'] = etf['日期'].dt.month
etf = etf.drop_duplicates(subset=['年','月','證券碼'], keep='first')benchmark['證券碼'] = benchmark['成份股'].str[0:4]
benchmark['年'] = benchmark['年月日'].dt.year
benchmark['月'] = benchmark['年月日'].dt.month
benchmark = benchmark.drop_duplicates(subset=['年','月','證券碼'], keep='first')etf.head(10)
Step 2. Import ETF industries belong by companies and the unadjusted stock price
We organize the 00881 ETF and Taiwan 50 Index stocks into a list data type, and retrieve the adjusted stock price and name of the industry of required stocks from the TEJ database.
# 獲得 etf與 benchmark的代碼
coid_list = etf['證券碼'].unique().tolist()
coid_list.append('Y9999')
coid_list = coid_list + benchmark['證券碼'].unique().tolist()# 抓取公司的產業名稱
code = tejapi.get("TWN/EWNPRCSTD",
coid = coid_list,
paginate=True,
opts={'columns':['coid', 'coid_name','tseindnm']},
chinese_column_name=True)code.head(5)
We directly get stock monthly return through TEJ database.
# 股價
price = tejapi.get('TWN/AAPRCM1',
coid = coid_list,
mdate= {'gte': '2021-09-01','lte':'2021-11-30'},
opts={'columns':['coid', 'mdate','roi']},
chinese_column_name=True,
paginate=True)price['年'] = price['年月'].dt.year
price['月'] = price['年月'].dt.monthprice.head(5)
Step 3. Combine data by industry name
We merge the industry names into the Dataframe of 00881 ETF and Taiwan 50 Index. If there is a difference between the industry of the 00881 ETF and the stocks of the Taiwan 50 Index, we replace the industry name of the industry difference between the stocks of the 00881 ETF and the Taiwan 50 Index to ensure the 00881 ETF stocks industry names corresponding completely to the stocks of the Taiwan 50 Index.
# 合併產業名稱
etf = pd.merge(etf ,code , how = 'left' , on = ['證券碼'])
etf = pd.merge(etf ,price ,how = 'left' , left_on=['年','月','證券碼'], right_on=['年','月','證券代碼'])benchmark = pd.merge(benchmark ,code , how = 'left' , on = ['證券碼'])
benchmark = pd.merge(benchmark ,price ,how = 'left' ,
left_on=['年','月','年月日','證券碼'], right_on=['年','月','年月日','證券代碼'])# 處理產業不一致問題
# 若 benchmark的產業種類沒有在 etf的產業種類中找到，則 benchmark中特殊的產業改成其他
benchmark['TSE產業名'] = np.where(benchmark['TSE產業名'].isin(etf['TSE產業名'].unique().tolist()),
benchmark['TSE產業名'],'其他')# 若 etf的產業種類沒有在 benchmark的產業種類中找到，則 etf中特殊的產業改成其他
etf['TSE產業名'] = np.where(etf['TSE產業名'].isin(benchmark['TSE產業名'].unique().tolist()),
etf['TSE產業名'],'其他')
Step 4. Caculate 00881 ETF and Taiwan 50 Index’s monthly return
We calculate the industry monthly return and industry weight for each industry in the 00881 ETF and Taiwan 50 Index.
etf = etf.sort_values(by=['年','月','TSE產業名','證券代碼']).reset_index(drop=True) # 排序年月日
etf['TSE產業名'] = np.where(etf['TSE產業名'].isna(),'其他' ,etf['TSE產業名'])etf['權重'] = etf['權重'] * 0.01etf['產業權重'] = etf.groupby(['TSE產業名','年','月'])['權重'].transform('sum')
etf['實際當月報酬率'] = etf['權重'] * etf['當月報酬率']
etf['產業當月報酬率'] = etf.groupby(['TSE產業名','年','月'])['實際當月報酬率'].transform('sum') / etf['產業權重']etf['實際產業當月報酬率'] = etf['產業當月報酬率'] * etf['產業權重']
etf['ETF 當月報酬率'] = etf.groupby(['年','月'])['實際當月報酬率'].transform('sum')etf = etf[['年','月','TSE產業名','標的名稱','權重','當月報酬率','產業權重','產業當月報酬率']]benchmark = benchmark.sort_values(by=['年','月','TSE產業名','證券代碼']).reset_index(drop=True) # 排序年月日
benchmark = benchmark[['年月日','TSE產業名','成份股','證券代碼','年','月','前日市值比重','當月報酬率']]benchmark['前日市值比重'] = benchmark['前日市值比重'] * 0.01
benchmark['產業權重'] = benchmark.groupby(['TSE產業名','年','月'])['前日市值比重'].transform('sum')benchmark['實際當月報酬率'] = benchmark['前日市值比重'] * benchmark['當月報酬率']
benchmark['產業當月報酬率'] = benchmark.groupby(['TSE產業名','年','月'])['實際當月報酬率'].transform('sum') \
/ benchmark['產業權重']benchmark['實際產業當月報酬率'] = benchmark['產業當月報酬率'] * benchmark['產業權重']
benchmark['ETF 當月報酬率'] = benchmark.groupby(['年','月'])['實際當月報酬率'].transform('sum')benchmark.head(5)
Performance attribution
As shown in the table below, we can attribute excess return to allocation effects (Q2-Q1), selection effects (Q3-Q1), and interaction effects (Q4-Q3+Q2-Q1). Allocation effect mainly measures the impact of asset category, country, and industry contribution on excess return; selection effect mainly measures the impact selecting indivisual security with respect to a benchmark; and interactive effects are often incorporated into allocation effect or selection effect.
table = pd.merge(etf ,benchmark ,how = 'left' , on=['年','月','TSE產業名'])table['配置效果'] = (table['投組權重'] - table['標竿權重']) * \
(table['標竿當月報酬率'] - sum(table[:7]['標竿權重'] * table[:7]['標竿當月報酬率']))table['選擇效果'] = table['標竿權重'] * (table['投組當月報酬率'] - table['標竿當月報酬率'])
table['交互效果'] = (table['投組權重'] - table['標竿權重']) * (table['投組當月報酬率'] - table['標竿當月報酬率'])
table['主動報酬'] = table['配置效果'] + table['選擇效果'] + table['交互效果']table.loc['合計',:] = table.sum(axis=0)
table.loc['合計','投組當月報酬率'] = sum(table[:7]['投組權重'] * table[:7]['投組當月報酬率'])
table.loc['合計','標竿當月報酬率'] = sum(table[:7]['標竿權重'] * table[:7]['標竿當月報酬率'])table = (table * 100).round(2)
tableWe calculated the performance attribution in November and found the 00881 ETF has a more precise stock selection ability, and its selection effect have 1.61%. However, the 00881 ETF aims for semiconductor, Internet, and electric vehicle stocks, the 00881 ETF and the Taiwan 50 Index are both nearly 60% of the weight is in the semiconductor industry, and the industry overlap is high. Therefore, the allocation effect have only 0.47%.
Visualization
We draw a three-month radar chart of the excess return of each industry, and analyze the excess return contributed by each industry every month, and find that the industry’s contribution to the excess return of the investment group will fluctuate over time. However, return fluctuation is normal, we should pay attention to whether the excess return of the portfolio can be greater than 0 consistently.
fig = go.Figure()for date in etf['月'].unique():
table = pd.merge(etf ,benchmark ,how = 'left' , on=['年','月','TSE產業名'])
table = table[table['月'] == date]
table = table.drop(['年','月'], axis=1)
table = table.set_index(['TSE產業名'])
table = table.sort_values(by=['投組權重'], ascending=False)
table = table.fillna(0)
table['配置效果'] = (table['投組權重'] - table['標竿權重']) * \
(table['標竿當月報酬率'] - sum(table[:7]['標竿權重'] * table[:7]['標竿當月報酬率']))
table['選擇效果'] = table['標竿權重'] * (table['投組當月報酬率'] - table['標竿當月報酬率'])
table['交互效果'] = (table['投組權重'] - table['標竿權重']) * (table['投組當月報酬率'] - table['標竿當月報酬率'])
table['主動報酬'] = table['配置效果'] + table['選擇效果'] + table['交互效果']
table.loc['合計',:] = table.sum(axis=0)
table.loc['合計','投組當月報酬率'] = sum(table[:7]['投組權重'] * table[:7]['投組當月報酬率'])
table.loc['合計','標竿當月報酬率'] = sum(table[:7]['標竿權重'] * table[:7]['標竿當月報酬率'])
table = (table * 100).round(2)
fig.add_trace(go.Scatterpolar(r= table.loc['半導體':'其他','主動報酬'].tolist(),
theta= table.drop(['合計']).index,
fill='toself',
name=str(date) + '月'))fig.show()
Conclusion
Performance attribution can be uesd to clarify whether the stock selection and industry allocation are right, and can be used as a reference for future investment analysis and investment decisions. Finally, we recommend readers to use the ETF and index companies provided by the TEJ database, so that we can obtain the list stocks of the index in different periods and match the industries to which the constituent stocks belong, and analyze the performance attribution of different investment portfolios.
It is for learning purposes only and does not take into account your individual needs, investment objectives and specific financial circumstances. Investment involves risk. Past performance is not indicative of future performance. Readers are requested to use their personal independent thinking skills to make investment decisions on their own. If losses are incurred due to relevant suggestions, it will not be involved with the author.
Source Code
Related Link
You could give us encouragement by …
We will share financial database applications every week.
If you think today’s article is good, you can click on the applause icon once.
If you think it is awesome, you can hold the applause icon until 50 times.
If you have any feedback, please feel free to leave a comment below.
