diff options
| -rw-r--r-- | python/mark_backtest.py | 78 | ||||
| -rw-r--r-- | python/mark_backtest_underpar.py | 94 | ||||
| -rw-r--r-- | python/marks_to_HY.py | 26 |
3 files changed, 79 insertions, 119 deletions
diff --git a/python/mark_backtest.py b/python/mark_backtest.py deleted file mode 100644 index 970e8b6f..00000000 --- a/python/mark_backtest.py +++ /dev/null @@ -1,78 +0,0 @@ -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 - -df = (pd.read_sql_table('external_marks_mapped',dbengine('dawndb'), - parse_dates=['date']). - sort_values('date')) -df1 = pd.read_sql_query("select trade_date, cusip as identifier, principal_payment, price from bonds where buysell = 'f'" - ,dbengine('dawndb'), - parse_dates=[ - 'trade_date' - ]) -df1 = df1.sort_values('trade_date') -d = {} -for source, g in df.groupby('source'): - d[source] = pd.merge_asof(df1, g, left_on='trade_date', right_on= 'date', by='identifier') -df2 = pd.concat(d, join='inner') -df3 = df2.dropna(axis=0, subset=['mark']) -df3 = df3.reset_index(level=1, drop=True) -df4 = df3.set_index(['trade_date','identifier', 'source', 'principal_payment']) -df4 = df4.groupby(level=['trade_date', 'identifier','source', 'principal_payment']).mean() -df4 = df4.set_index('price', append=True) -df5 = df4.unstack(-3) -df5.columns = df5.columns.droplevel() -#df6 = df5.reset_index(col_level=1, drop=True) -df6 = df5.sortlevel(0, ascending = False) -df6 = df6.reset_index() - -r2 = {} -stderr = {} -parameters = {} -#for g in df5['mark']: -for col in df6.columns[4:]: - regmodel = gls(formula= "df6['price'] ~df6[col]", data = df6).fit() - r2[col] = regmodel.rsquared - stderr[col] = regmodel.bse - parameters[col] = regmodel.params - -r2 = pd.Series(r2, name= 'R2') -r2.index.name = 'Source' -r2.reset_index -r2 = pd.DataFrame(r2) -stderr = pd.DataFrame(stderr) -parameters = pd.DataFrame(parameters) - -df7 = df6 -for col in df7.columns[4:]: - df7[col] = (df7.price - df7[col])/df7[col] - -filter = 2 -begindate = '2015-01-01' -enddate = '2017-01-01' -df8 = df7[(df7['trade_date']>begindate) & (df7['trade_date']<enddate)] -difference= pd.DataFrame(df8[df8<filter].mean(), columns=['Average']) -difference['Std Dev'] = df8[df8<filter].std() -difference.reset_index() - -writer = pd.ExcelWriter('/home/serenitas/edwin/Subprime_ModelandBacktest_outputonly.xlsx') -df6.to_excel(writer, sheet_name = 'BackTesting', header = True, na_rep = '', startrow= 1, startcol= 1) -r2.to_excel(writer, sheet_name = 'regression', header = True) -parameters.to_excel(writer, sheet_name = 'regression', header = True, startrow= 0, startcol= 5) -difference[2:].to_excel(writer, sheet_name = 'Diff', header = True, startrow= 1, startcol= 1) -df7.to_excel(writer, sheet_name = 'Diff', header = True, startrow= 15, startcol= 1) -writer.save() - -#fig = plt.plot(figsize=(12,8)) -#fig = sm.graphics.plot_partregress_grid(regmodel,fig=fig) - - -#df6 = pd.DataFrame() -#for g in df5['mark']: -# d[g] = (df5['price'] - df5['mark'][g])/df5['mark'][g] -#df6.from_dict(d) - diff --git a/python/mark_backtest_underpar.py b/python/mark_backtest_underpar.py index 3d1239a2..c6128b13 100644 --- a/python/mark_backtest_underpar.py +++ b/python/mark_backtest_underpar.py @@ -7,59 +7,95 @@ import statsmodels.api as sm from statsmodels.formula.api import gls import seaborn as sb -df = pd.read_sql_table('external_marks_mapped',dbengine('dawndb'), +df = pd.read_sql_table('external_marks_mapped', dbengine('dawndb'), parse_dates=['date']) df = df[df.source.notnull()] 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, principal_payment, price " \ + +df_trades = {} +df_trades['sell'] = pd.read_sql_query("select trade_date, identifier, faceamount, principal_payment, price " \ "from bonds where buysell = 'f'" - ,dbengine('dawndb'), - parse_dates=['trade_date']) -df_trades = df_trades.sort_values('trade_date') -df_wide = pd.merge_asof(df_trades, df_wide, left_on='trade_date', right_on='date', by='identifier') -d = {} + , dbengine('dawndb'), + parse_dates=['trade_date']) +df_trades['buy'] = pd.read_sql_query("select trade_date, identifier, faceamount, principal_payment, price " \ + "from bonds where buysell = 't'" + , dbengine('dawndb'), + parse_dates=['trade_date']) +for direction, trades in df_trades.items(): + df_trades[direction] = trades.sort_values('trade_date') + +df_sell_wide = pd.merge_asof(df_trades['sell'], df_wide, left_on='trade_date', right_on='date', by='identifier') +df_wide_temp = df_wide.set_index('date').shift(periods = -1, freq= 'M', axis = 1).reset_index() +df_buy_wide = pd.merge_asof(df_trades['buy'], df_wide_temp + , left_on='trade_date', right_on='date', by='identifier') + +d_sell = {} +d_buy = {} +df_long = {} + for source, g in df.groupby('source'): - d[source] = pd.merge_asof(df_trades, g, left_on='trade_date', right_on='date', by='identifier') -df_long = pd.concat(d, join='inner') -df_long = df_long.reset_index() -df_long = df_long.dropna(subset=['price','mark']) + d_sell[source] = pd.merge_asof(df_trades['sell'], g, left_on='trade_date', right_on='date', by='identifier') + d_sell[source]['dir'] = 'sell' + d_buy[source] = pd.merge_asof(df_trades['buy'], g.set_index('date').shift(periods = -1, freq= 'BM', axis = 1).reset_index(), left_on='trade_date', right_on='date', by='identifier') + d_buy[source]['dir'] = 'buy' + +#add a column saying it is buy or sell + +df_long = pd.concat(d_sell, join='inner') +df_long = df_long.append(pd.concat(d_buy, join='inner')) +df_long = df_long.reset_index().dropna(subset=['price','mark']) +df_long['difference'] = (df_long['price'] - df_long['mark'])/df_long['mark'] #filtering diff_threshold = 5 begindate = '2013-01-01' enddate = '2018-01-01' + df_long = df_long[df_long.identifier.str.len() == 9] df_long = df_long[(df_long['trade_date'] > begindate) & (df_long['trade_date'] < enddate)] -#difference= df_long[df_long['<filter].groupby('level_0').[.mean(), columns=['Average']) + #difference['Std Dev'] = df_long[df_long<filter].std() #pretty plot -#dotsize = pd.Series.tolist(df_long['level_0'].apply(lambda x: 400 if x == 'MANAGER' else 20)) #df_long['dotsize'] = df_long['level_0'].apply(lambda x: 400 if x == 'MANAGER' else 20) -df_long['dotsize'] = df_long['level_0'].apply(lambda x: 400 if x == 'MANAGER' else 20) + plt.switch_backend('Agg') sb.set_style("whitegrid") sb.set_context("notebook") -regplot = sb.lmplot(x='mark', y='price', hue = 'source', data=df_long, fit_reg=False, legend_out=True, aspect = 2.1, size = 4, scatter_kws={"s": df_long['dotsize']}) -regplot.set(ylim=(0, 105), xlim=(0, 105)) -#plt.legend(bbox_to_anchor=(1.05, 1), loc=2, borderaxespad=0.) -#regplot.fig.savefig("/home/serenitas/edwin/fig1.png") - #plt.switch_backend('Qt4Agg') order = ['MANAGER','BROKER','BVAL','IDC','MARKIT','PB','PRICESERVE','PRICINGDIRECT','REUTERS','S&P'] sb.set_palette(sb.hls_palette(10, l=.4, s=.8)) -g = 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, size = 4, hue_order = order) -g.set(ylim=(0, 105), xlim=(0, 105)) -ax = g.map(plt.scatter, 'mark', 'price').add_legend() -ax.fig.savefig("/home/serenitas/edwin/fig2.png") + +g_sell = sb.FacetGrid(df_long[df_long.dir == 'sell'], hue='source', hue_kws={'s':[50] + [20]*9, 'marker': ["o"]+["s"]*9, 'alpha': [1]+[.4]*9}, legend_out=True, aspect = 2.1, size = 4, hue_order = order) +g_sell.set(ylim=(0, 105), xlim=(0, 105)) +ax_sell = g_sell.map(plt.scatter, 'mark', 'price').add_legend() + +g_buy = sb.FacetGrid(df_long[df_long.dir == 'buy'], hue='source', hue_kws={'s':[50] + [20]*9, 'marker': ["o"]+["s"]*9, 'alpha': [1]+[.4]*9}, legend_out=True, aspect = 2.1, size = 4, hue_order = order) +g_buy.set(ylim=(0, 105), xlim=(0, 105)) +ax_buy = g_buy.map(plt.scatter, 'mark', 'price').add_legend() + +ax_sell.fig.savefig("/home/serenitas/edwin/backtest_sales.png") +ax_buy.fig.savefig("/home/serenitas/edwin/backtest_buys.png") + +params = {} +error = {} +trade_PNL = {} +trade_PNL_stat = {} #fit all the models at once -params = (df_long. - groupby('source'). - apply(lambda df: gls('price~mark', df).fit().params)) -df_long['diff'] = (df_long['price'] - df_long['mark'])/df_long['mark'] -error = [df_long[df_long['diff'] < diff_threshold].groupby('source').mean()['diff'], - df_long[df_long['diff'] < diff_threshold].groupby('source').std()['diff']] +for direction, df_temp in df_long[(df_long.difference < diff_threshold)].groupby('dir'): + params[direction] = (df_temp. + groupby('source'). + apply(lambda df: gls('price~mark', df).fit().params)) + error[direction] = [df_temp.groupby('source').mean()['difference'], + df_temp.groupby('source').std()['difference']] + trade_PNL[direction] = df_temp.groupby(['date', 'source']).apply( + lambda df: sum(df.principal_payment * df.difference)/ + sum(df.principal_payment)) + trade_PNL_stat[direction] = [trade_PNL[direction].groupby(level = 'source').mean(), + trade_PNL[direction].groupby(level = 'source').std()] + +buychart = trade_PNL['buy'].unstack(-1).plot() diff --git a/python/marks_to_HY.py b/python/marks_to_HY.py index ff4e72ea..f20f7711 100644 --- a/python/marks_to_HY.py +++ b/python/marks_to_HY.py @@ -4,6 +4,7 @@ import numpy as np import statsmodels.api as sm from statsmodels.formula.api import ols from pandas.tseries.offsets import MonthEnd +from scipy.interpolate import interp1d import datetime from pandas.tseries.offsets import * import bbg_helpers @@ -12,14 +13,15 @@ import bbg_helpers #import matplotlib #matplotlib.use("Q4Agg") #to prevent conflict with PyQT4 -def monthlySpreadDiff(index="IG", tenor="5yr", period="1yr"): +def monthlySpreadDiff(index="IG", tenor="5yr"): date = (pd.Timestamp.now() - pd.DateOffset(years=4)).date() - sql_str = "SELECT date, series, closespread AS spread FROM index_quotes " \ + sql_str = "SELECT date, series, version, closespread AS spread FROM index_quotes " \ "WHERE index=%s and date>=%s and tenor = %s" df = pd.read_sql_query(sql_str, dbengine('serenitasdb'), parse_dates=['date'], index_col=['date', 'series'], params=(index.upper(), date, "5yr")) - df1 = df.unstack(level=1).resample('1m').last().diff(periods = period) - df1 = df1.stack(level = 1).groupby(level = 'date').last() + df1 = df.groupby(level = ['date','series']).last() + df1 = df1.unstack(level = 1).resample('1m').last().diff() + df1 = df1.stack(level = 1).groupby(level = 'date').last()['spread'] return df1 def nearestDate(base, dates): @@ -115,7 +117,6 @@ df1.adjpv= df1.adjpv.where(df1.adjpv.notnull(), df1.pv) df1['spread'] = (df1['adjpv']*100-df1['mark'])/df1['moddur']*100 df1 = df1.set_index(['monthend_date','identifier','source']) -return df1 #difference = difference in spread #Filter @@ -125,7 +126,7 @@ DurationFilter = 1.5 begindate = '2014-01-01' enddate = '2017-01-01' interval = 1 -HYDifference = monthlySpreadDiff(index = 'HY', period = interval) +HYDifference = monthlySpreadDiff(index = 'HY') difference = df1[abs(df1['spread'])<SpreadFilter].groupby(level=['identifier','source'])['pv','moddur','spread'].diff(periods = interval) difference = difference[abs(difference['spread'])<SpreadDiffFilter] difference = difference[abs(difference['moddur'])<DurationFilter] @@ -133,15 +134,15 @@ difference.spread = difference.spread.astype(np.float64) #method 1: for all sources - average through Time avgchange1 = pd.DataFrame(difference.groupby(level=['monthend_date']).mean()['spread']) -avgchange1 = avgchange1.merge(HYDifference, left_index = True, right_index= True) -avgchange1 = avgchange1.rename(columns={'spread_x': 'marks', 'spread_y':'HY'}) +avgchange1 = avgchange1.join(HYDifference, rsuffix = 'HY') avgchange1 = avgchange1[begindate:enddate] -regmodel1 = sm.OLS(avgchange1['marks'],avgchange1['HY']).fit() -ratio1 = (avgchange1['HY']/avgchange1['marks']).mean() +regmodel1 = sm.OLS(avgchange1['spread'],avgchange1['spreadHY']).fit() +ratio1 = (avgchange1['spreadHY']/avgchange1['spread']).mean() #method 2: look at it source by source avgchange2 = pd.DataFrame(difference.groupby(level=['monthend_date','source']).mean()['spread']) avgchange2 = avgchange2.unstack(level =1) +HYDifference = HYDifference.to_frame() HYDifference.columns = pd.MultiIndex.from_arrays([['Index'],['HY']]) avgchange2 = avgchange2.merge(HYDifference, left_index = True, right_index= True) regmodel2 ={} @@ -161,12 +162,13 @@ for source in regmodel2: difference1 = difference.reset_index().set_index('monthend_date') #difference1 = difference1.loc[difference1['source'].isin(['PRICINGDIRECT','PRICESERVE'])] #difference1 = difference1[(difference1['source'] == 'PRICESERVE')] +HYDifference.columns = pd.MultiIndex.from_arrays([('HY',)]) difference1 = difference1.merge(HYDifference, left_index = True, right_index= True) regmodel3 = {} Rsquared3 = {} params3 = {} for source, g in difference1.groupby('source'): - regmodel3[source] = sm.OLS(g['spread'],g[('Index', 'HY')], missing = 'drop').fit() + regmodel3[source] = sm.OLS(g['spread'],g['HY'], missing = 'drop').fit() Rsquared3[source] = regmodel3[source].rsquared params3[source] = regmodel3[source].params #regmodel3.summary() @@ -174,7 +176,7 @@ for source, g in difference1.groupby('source'): #method 4: Let's just look at how spread changes within each source (i.e. see if spread widening ==> lower marks for most/all bonds?) #difference.xs('PRICESERVE', level = 'source', drop_level=False) #difference2[difference2['source']=='BVAL'].to_clipboard() -difference4 = difference1.rename(columns = {('Index', 'HY'): "HY Spread"}) +difference4 = difference1.rename(columns = {('HY'): "HY Spread"}) difference4['HY vs mark'] = difference4['spread'] - difference4['HY Spread'] difference4.index = difference4.index.rename('monthend_date') difference4 = difference4.set_index('source', append = True) |