diff options
Diffstat (limited to 'python/exploration')
| -rw-r--r-- | python/exploration/curve_trades.py | 76 |
1 files changed, 71 insertions, 5 deletions
diff --git a/python/exploration/curve_trades.py b/python/exploration/curve_trades.py index b6d35d3d..3c0a25ad 100644 --- a/python/exploration/curve_trades.py +++ b/python/exploration/curve_trades.py @@ -11,25 +11,29 @@ import numpy as np import matplotlib.pyplot as plt from statsmodels.sandbox.regression.predstd import wls_prediction_std +from scipy.interpolate import interp1d -_engine = dbengine('serenitasdb') +serenitasdb = dbengine('serenitasdb') +dawndb = dbengine('dawndb') def on_the_run(index): - r = _engine.execute("SELECT max(series) FROM index_version WHERE index=%s", + r = serenitasdb.execute("SELECT max(series) FROM index_version WHERE index=%s", (index,)) series, = r.fetchone() return series -def curve_spread_diff(index='IG', rolling=6): +def curve_spread_diff(index='IG', rolling=6, years=3, percentage=False, percentage_base='5yr'): otr = on_the_run(index) ## look at spreads df = get_index_quotes(index, list(range(otr - rolling, otr + 1)), - tenor=['3yr', '5yr', '7yr', '10yr']) + tenor=['3yr', '5yr', '7yr', '10yr'], years=years) spreads = df.groupby(level=['date', 'tenor']).nth(-1)['closespread'].unstack(-1) spreads_diff = spreads.diff(axis=1) del spreads_diff['3yr'] spreads_diff.columns = ['3-5', '5-7', '7-10'] spreads_diff['5-10'] = spreads_diff['5-7'] + spreads_diff['7-10'] + if percentage is True: + spreads_diff = spreads.apply(lambda df: df/df[percentage_base], axis = 1) return spreads_diff def spreads_diff_table(spreads_diff): @@ -133,7 +137,6 @@ def cross_series_curve(index='IG', rolling=6): agg(lambda df: (1 + df).prod() - 1)) plt.plot(monthly_returns_cross_series) - def forward_loss(index='IG'): start_date = (pd.Timestamp.now() - pd.DateOffset(years=3)).date() @@ -238,3 +241,66 @@ def spot_forward(index='IG', series=None, tenors=['3yr', '5yr', '7yr', '10yr']): df = df_0.append(df) df['maturity'] = [b_index.trade_date, maturity_1yr] + b_index.maturities return df.reset_index().set_index('maturity') + +def curve_pos(trade_date, index='IG'): + + ''' + Input trade_date and index + Returns a Portfolio of curve trades ''' + + sql_string = "SELECT * FROM cds where trade_date < %s" + df = pd.read_sql_query(sql_string, dawndb, parse_dates=['trade_date', 'maturity'], + params=[trade_date]) + if index is 'IG': + df = df[df['folder'] == 'SER_IGCURVE'] + elif index is 'HY': + df = df[df['folder'] == 'SER_HYCURVE'] + else: + df = df[df['folder'] == 'SER_ITRXCURVE'] + df.notional = df.apply(lambda x: x.notional * -1 if x.protection == 'Buyer' else x.notional, axis = 1) + df = df.groupby(['security_id', 'maturity']).sum()['notional'] + df = df.iloc[df.nonzero()[0]].reset_index() + + sql_string = "SELECT * FROM index_maturity LEFT JOIN index_version USING (index, series)" + lookup_table = pd.read_sql_query(sql_string, serenitasdb, parse_dates=['maturity']) + + df = df.merge(lookup_table, left_on=['security_id','maturity'], right_on=['redindexcode', 'maturity']) + + indices = [] + sql_string = "SELECT closespread FROM index_quotes where index = %s and series = %s and tenor = %s and date = %s" + for i, row in df[['index', 'tenor', 'series', 'notional']].iterrows(): + temp = Index.from_name(row['index'], row.series, row.tenor) + temp.value_date = trade_date.date() + if row.notional > 0: + temp.direction = 'Seller' + temp.notional = abs(row.notional) + spread_df = pd.read_sql_query(sql_string, serenitasdb, + params=[row['index'], row.series, row.tenor, trade_date.date()]) + temp.spread = spread_df.iloc[0][0] + indices.append(temp) + + return Portfolio(indices) + +def curve_shape(trade_date, index = 'IG', percentile=.95): + + ''' + Returns a function to linearly interpolate between the curve based on maturity (in years)''' + + curve_shape = curve_spread_diff(index, 10, 5, True) + steepness = (curve_shape['10yr']/curve_shape['3yr']) + series = on_the_run(index) + + sql_string = "SELECT closespread FROM index_quotes where index = %s and series = %s and tenor = %s and date = %s" + spread_df = pd.read_sql_query(sql_string, serenitasdb, + params=[index, series, '5yr', trade_date.date()]) + sql_string = "SELECT tenor, maturity FROM index_maturity where index = %s and series = %s" + lookup_table = pd.read_sql_query(sql_string, serenitasdb, parse_dates=['maturity'], params=[index, series]) + + df = curve_shape[steepness == steepness.quantile(percentile, 'nearest')] + df = df * spread_df.iloc[0][0]/df['5yr'][0] + df = df.stack().rename('spread') + df = df.reset_index().merge(lookup_table, on=['tenor']) + df['year_frac'] = (df.maturity - pd.to_datetime(trade_date)).dt.days/365 + return interp1d(np.hstack([0, df.year_frac]), np.hstack([0, df.spread])) + + |