1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
import pandas as pd
from db import dbengine
import matplotlib
import numpy as np
import matplotlib.pyplot as plt
import statsmodels.api as sm
from statsmodels.formula.api import gls
import seaborn as sb
import globeop_reports as ops
def get_mark_df(asset_class = 'Subprime'):
#Filter marks > 1000 where the marks are weird...
df_external_marks = pd.read_sql_query("select * from external_marks_mapped where mark < 1000"
, dbengine('dawndb'))
df_trades = pd.DataFrame()
for date in df_external_marks.date.unique():
df_temp= pd.read_sql_query("select description, identifier, usd_market_value/price*100 as curr_ntl from risk_positions(%s, %s) where price >0 and length(identifier) = 9 "
, dbengine('dawndb'), params = [date, asset_class])
df_temp['date'] = date
df_trades = df_trades.append(df_temp)
df = df_trades.merge(df_external_marks).dropna()
return df.set_index(['date','identifier'])
def calc_mark_diff(df, sources= ['PRICESERVE', 'PRICINGDIRECT','BVAL','MARKIT','BROKER', 'REUTERS', 'S&P', 'IDC']):
#All Sources (including manager...?!) - average, manager mark only, median, closest
g = df.groupby(level = ['date','identifier'])
diff = g.mean()
diff = diff.join(df[df.source == 'MANAGER']['mark'], rsuffix = '_manager')
diff = diff.join(g.median()['mark'], rsuffix = '_median_all')
temp = g.apply(closest)
temp = temp.rename('mark_closest_all')
diff = diff.join(temp)
#Filtered Sources - mean, median, remove max min
df_filtered = df[df.source.isin(sources)]
g1 = df_filtered.groupby(level = ['date','identifier'])
diff = diff.join(g1.mean()['mark'], rsuffix = '_filtered_mean')
diff = diff.join(g1.median()['mark'], rsuffix = '_filtered_median')
diff = diff.join(g1.mark.apply(remove_max_min), rsuffix = '_filtered_no_max_min')
#calculate difference: negative means Serenitas marks higher
diff = diff.multiply(diff.curr_ntl/100, axis = 'index')
del diff['curr_ntl']
diff = diff.rename(columns = {'mark':'mark_mean_all'})
diff = diff.apply(lambda x: (x-x.mark_manager), axis = 1)
return diff.groupby(level = 'date').sum()
def closest(x):
if x.mark.count() > 1:
x['dist'] = abs(x.mark - x.mark[x.source == 'MANAGER'])
return x.mark[x.dist == x.dist[x.dist>0].min()].iloc[0]
else:
return x.mark[0]
def remove_max_min(x):
if x.count() >= 4:
return (x.sum() - x.max() - x.min())/(x.count() -2)
else:
return x.mean()
def diff_by_source(df):
#diff_by_source: input get_mark_df(), calculate the pricing by source
df = df.drop('description', 1)
df = df.set_index(['source'], append=True).apply(lambda x: x.curr_ntl * x.mark/100, axis = 1)
df = df.groupby(level =['date','identifier','source']).mean()
df = df.unstack(-1).apply(lambda x: (x-x.MANAGER), axis = 1)
return df.groupby(level = 'date').sum()
def diff_by_source_percentage(df):
df = diff_by_source(df)
df = df.join(ops.get_net_navs()['endbooknav'])
df = df.apply(lambda x: (x/x.endbooknav), axis = 1)
del df['endbooknav']
return df
def count_sources(df):
#input get_mark_df(), plot count of each source
g2 = df.set_index('source', append=True).groupby(level = ['date','source'])
# there are a good amount of Bloomberg duplicates, not a big deal but should clean them up
g2['mark'].count().unstack(-1).plot()
def alt_navs():
navs = ops.get_net_navs()
df = calc_mark_diff(get_mark_df())
end_nav, beg_nav, returns, nav_100 = pd.DataFrame(), pd.DataFrame(), pd.DataFrame(), pd.DataFrame(index = df.index, columns=df.columns)
for col in df.columns:
end_nav[col] = df[col] + navs.endbooknav
beg_nav[col] = end_nav[col].shift(1) + navs.net_flow.shift(1)
beg_nav[col].iloc[0] = 12500000
returns[col] = (end_nav[col] - navs.incentive)/beg_nav[col] -1
for i, row in returns.dropna().reset_index().iterrows():
nav_100.iloc[i] = 100 if i == 0 else nav_100.iloc[i-1]
nav_100.iloc[i] = nav_100.iloc[i] * (1 + returns.iloc[i])
return returns, nav_100
def annual_performance(nav_100):
perf = nav_100.groupby(pd.Grouper(freq = 'A')).last()
perf_ann = perf/perf.shift(1) - 1
perf_ann['2013'] = perf['2013']/100-1
return perf_ann
def alt_nav_impact():
navs = ops.get_net_navs()
df = calc_mark_diff(get_mark_df())
df = df.join(navs.endbooknav)
return df.iloc[-1]/df.iloc[-1]['endbooknav']
def back_test(begindate = '2013-01-01', enddate = '2018-01-01', sell_price_threshold = 200):
df = pd.read_sql_query("SELECT * FROM external_marks_mapped WHERE source IS NOT NULL", dbengine('dawndb'),
parse_dates=['date'])
df_wide = (pd.pivot_table(df, 'mark', ['identifier', 'date'], 'source').reset_index().sort_values('date'))
df_trades = pd.read_sql_query("select trade_date, identifier, price, buysell from bonds",
dbengine('dawndb'), parse_dates=['trade_date'])
df_trades.sort_values('trade_date', inplace = True)
df_sell_wide = pd.merge_asof(df_trades[df_trades.buysell == False], df_wide, left_on='trade_date', right_on='date', by='identifier').drop('date', 1)
df_long = df_sell_wide.set_index(['trade_date','identifier','price','buysell']).stack()
df_long = df_long.reset_index().rename(columns={'level_4': 'source', 0:'mark'})
df_long['difference'] = (df_long['price'] - df_long['mark'])/df_long['mark']
#filtering
df_long = df_long[df_long.identifier.str.len() == 9]
df_long = df_long[df_long.price < sell_price_threshold]
df_long = df_long[(df_long['trade_date'] > begindate) & (df_long['trade_date'] < enddate)]
df_long.loc[df_long.source == 'MANAGER','source'] = 'LMCG'
return df_long
def stats(df_long, diff_threshold = 5):
g = df_long[df_long.difference < diff_threshold].groupby('source')
#fit all the models at once
params = g.apply(lambda df: gls('price~mark', df).fit().params)
error = pd.DataFrame([g.difference.mean(),g.difference.std()])
error.index = ['average', 'standard deviation']
return params, error
def pretty_plot(df_long):
#plt.switch_backend('Agg')
sb.set_style("whitegrid")
sb.set_context("notebook")
order = ['LMCG','BROKER','BVAL','IDC','MARKIT','PRICESERVE',
'PRICINGDIRECT','REUTERS','S&P']
sb.set_palette(sb.hls_palette(10, l=.4, s=.8))
grid = sb.FacetGrid(df_long, hue='source', hue_kws={'s':[50] + [20]*9,
'marker': ["o"]+["s"]*9, 'alpha': [1]+[.4]*9}, legend_out=True,
aspect=2.1, height= 4, hue_order=order)
grid.set(ylim=(0, 105), xlim=(0, 105))
ax = grid.map(plt.scatter, 'mark', 'price').add_legend()
ax.set_axis_labels('Mark', 'sale price')
plt.plot([100, 0], [100, 0], color="black", lw=2, linestyle='solid')
ax.fig.savefig("/home/serenitas/edwin/PythonGraphs/backtest.png")
|
