from index_data import get_index_quotes, index_returns import pandas as pd import math from scipy.stats.mstats import zscore import matplotlib.pyplot as plt index = 'IG' on_the_run = 28 def curve_spread_diff(index = 'IG', on_the_run = 28): ## look at spreads df = get_index_quotes(index, list(range(on_the_run-6,on_the_run+1)), tenor=['3yr', '5yr', '7yr', '10yr']) spreads = df.groupby(level=['date', 'tenor']).nth(-1)['closespread'].unstack(-1) # remove 'yr' spreads.columns = [int(col[:-2]) for col in spreads.columns] spreads = spreads.sort_index(1) spreads_diff = spreads.diff(axis=1) spreads_diff = spreads_diff.filter([5, 7, 10]) spreads_diff['5-10'] = spreads_diff[7] + spreads_diff[10] spreads_diff.columns = ['3-5', '5-7', '7-10', '5-10'] return spreads_diff def spreads_diff_table(spreads_diff): df = pd.DataFrame() df['min'] = spreads_diff.min() df['max'] = spreads_diff.max() df['average'] = spreads_diff.mean() df['current'] = spreads_diff.iloc[-1] df['zscore'] = pd.Series(zscore(spreads_diff)[-1], index = df.index) pd.DataFrame(zscore(spreads_diff), index=spreads_diff.index, columns=spreads_diff.columns).plot() return df def theta_matrix_by_series(index = 'IG', on_the_run = 28): df = get_index_quotes(index, list(range(on_the_run-6,on_the_run+1)), tenor=['3yr', '5yr', '7yr', '10yr']) df['theta_per_dur'] = df.theta2/df.duration2 theta_matrix = df.groupby(level=['date', 'tenor','series']).nth(-1)['theta_per_dur'] theta_matrix_1 = theta_matrix.xs(theta_matrix.index.max()[0], level = 0).unstack(0) return theta_matrix_1[['3yr', '5yr', '7yr', '10yr']] def theta_ratio_within_series(index = 'IG', on_the_run = 28): df = get_index_quotes(index, list(range(on_the_run-6,on_the_run+1)), tenor=['3yr', '5yr', '7yr', '10yr']) r = {} for i,g in df.groupby(level=['date', 'series']): five_yr = g.xs('5yr', level = 'tenor')['duration'] r[i] = g.duration/five_yr[0] df1 = pd.concat(r) dftemp= pd.DataFrame(df1.groupby(level=['date', 'tenor','series']).nth(-1).rename('ratio_to_5yr')) df2 = df.groupby(level=['date', 'tenor','series']).nth(-1).merge(dftemp, left_index=True, right_index=True) return df2.unstack(-2) def curve_3_5_10(theta_ratio_within_series): df = theta_ratio_within_series #buy 3y, sell 5y, buy 10y df['3_5_10'] = - df.theta2['3yr'] / df.ratio_to_5yr['3yr'] \ + 2 * df.theta2['5yr'] \ - df.theta2['10yr'] / df.ratio_to_5yr['10yr'] df['3_5_10'].dropna().unstack(-1).plot() def curve_5_10(theta_ratio_within_series): df = theta_ratio_within_series #buy sell 5y, buy 10y df['5_10'] = df.theta2['5yr'] - df.theta2['10yr'] / df.ratio_to_5yr['10yr'] df['5_10'].dropna().unstack(-1).plot() def on_the_run_theta(index = 'IG', on_the_run = 28): df = get_index_quotes(index, list(range(on_the_run-6,on_the_run+1)), tenor=['3yr', '5yr', '7yr', '10yr']) df['theta_per_dur'] = df.theta2/df.duration2 theta_matrix = df.groupby(level=['date', 'tenor']).nth(-1)['theta_per_dur'] theta_matrix.unstack(-1).plot() def curve_returns(index = 'IG', on_the_run = 28): ## look at returns df = index_returns(index= index, series=list(range(on_the_run-6,on_the_run+1)), tenor=['3yr', '5yr', '7yr', '10yr']) ## on-the-run returns returns = df.price_return.unstack(-1).dropna().groupby(level='date').nth(-1) strategy = ['510', '710', '3510'] strategies_return = pd.DataFrame() strategies_return[strategy[0]] = 1.78 * returns['5yr'] - returns['10yr'] strategies_return[strategy[1]] = 1.33 * returns['7yr'] - returns['10yr'] strategies_return[strategy[2]] = -2 * returns['3yr']+ 3 * returns['5yr'] - 1 * returns['10yr'] strategies_return_monthly = pd.DataFrame() for strat in strategy: strategies_return_monthly[strat] = strategies_return[strat].groupby(pd.TimeGrouper(freq='M')).agg(lambda df:(1+df).prod()-1) results = pd.DataFrame() sharpe = {} monthly_sharpe = {} for strat in strategy: sharpe[strat] = strategies_return[strat].mean()/strategies_return[strat].std()*math.sqrt(252) monthly_sharpe[strat] = strategies_return_monthly[strat].mean()/strategies_return_monthly[strat].std()*math.sqrt(12) worst_drawdown = {} for strat in strategy: worst_drawdown[strat] = strategies_return[strat].nsmallest(10).mean() results = results.append(sharpe, ignore_index=True) results = results.append(monthly_sharpe, ignore_index=True) results = results.append(worst_drawdown, ignore_index=True) results['results'] = ['Sharpe','Monthly Sharpe','Mean Worst 10 Days Drawdown'] return results.set_index('results') def cross_series_curve(index = 'IG', on_the_run = 28): df = index_returns(index= index, series=list(range(on_the_run-6,on_the_run+1)), tenor=['3yr', '5yr', '7yr', '10yr']) ## look cross series - 3y to 5y returns1 = df.xs(['5yr', index], level = ['tenor','index']).price_return.unstack(-1) price_diff = pd.DataFrame() for ind in list(range(on_the_run-2, on_the_run+1)): price_diff[ind] = returns1[ind] - 1.6* returns1[ind - 4] price_diff = price_diff.stack().groupby(level = 'date').nth(-1) monthly_returns_cross_series = price_diff.groupby(pd.TimeGrouper(freq='M')).agg(lambda df:(1+df).prod()-1) plt.plot(monthly_returns_cross_series) def forward_loss(): from db import dbengine, dbconn serenitasdb = dbengine('serenitasdb') index = 'IG' start_date = (pd.Timestamp.now() - pd.DateOffset(years=3)).date() df = pd.read_sql_query("SELECT date, index, series, tenor, duration, closespread, closespread*duration/100 as indexel " \ "FROM index_quotes WHERE index=%s AND date >= %s " \ "ORDER BY date DESC, series ASC, duration ASC", serenitasdb, parse_dates=['date'], params=[index, start_date]) df1 = pd.read_sql_query("select index, series, tenor, maturity from index_maturity", serenitasdb, parse_dates=['maturity']) df = df.merge(df1, on=['index','series','tenor']) df = df.set_index(['date','index', 'maturity']).dropna() df = df.groupby(level=['date','index', 'maturity']).nth(-1) # annual change, to take out some noise df['fwd_loss_rate'] = df.indexel.diff(2)/df.duration.diff(2)