diff options
Diffstat (limited to 'python/exploration/portfolio_var.py')
| -rw-r--r-- | python/exploration/portfolio_var.py | 97 |
1 files changed, 62 insertions, 35 deletions
diff --git a/python/exploration/portfolio_var.py b/python/exploration/portfolio_var.py index 22879a20..5b0006f9 100644 --- a/python/exploration/portfolio_var.py +++ b/python/exploration/portfolio_var.py @@ -1,7 +1,8 @@ -from index_data import get_index_quotes, index_returns +from index_data import get_index_quotes, index_returns, _serenitas_engine from analytics import Swaption, BlackSwaption, Index, VolatilitySurface, Portfolio from db import dbengine, dbconn from pandas.tseries.offsets import BDay +import numpy as np import pandas as pd import math @@ -10,45 +11,71 @@ import datetime dawndb = dbengine('dawndb') serenitasdb = dbengine('serenitasdb') -## look at returns -index, df, df1, returns = {}, {}, {}, {} -shock = [] -shock_values = [.95,.05] +def get_spreads(): + ## look at returns + index, df, df1, returns = {}, {}, {}, {} + shock = [] + shock_values = [.95,.05] -for ind in ['HY','IG']: - index[ind] = Index.from_name(ind, 28, '5yr') - df[ind] = index_returns(index=ind, series=[23, 24, 25, 26, 27, 28], tenor=['3yr', '5yr', '7yr', '10yr'], per = 22) - #df1[ind] = get_index_quotes(index=ind, series=[23, 24, 25, 26, 27, 28], tenor=['3yr', '5yr', '7yr', '10yr']) - returns[ind] = df[ind].price_return.unstack(-1).dropna().groupby(level='date').nth(-1) - for percent in shock_values: #.95 = ~2 st dev tightening, .05 = widening - shock.append([ind, percent, returns[ind].quantile(percent)['5yr']]) + for ind in ['HY','IG']: + index[ind] = Index.from_name(ind, 28, '5yr') + df[ind] = index_returns(index=ind, series=[23, 24, 25, 26, 27, 28], tenor=['3yr', '5yr', '7yr', '10yr'], per = 22) + #df1[ind] = get_index_quotes(index=ind, series=[23, 24, 25, 26, 27, 28], tenor=['3yr', '5yr', '7yr', '10yr']) + returns[ind] = df[ind].price_return.unstack(-1).dropna().groupby(level='date').nth(-1) + for percent in shock_values: #.95 = ~2 st dev tightening, .05 = widening + shock.append([ind, percent, returns[ind].quantile(percent)['5yr']]) -shock = pd.DataFrame.from_records(shock, columns=['index', 'percent', 'shock'], index=['index', 'percent']) + shock = pd.DataFrame.from_records(shock, columns=['index', 'percent', 'shock'], index=['index', 'percent']) -index['IG'].spread = 62 -index['HY'].price = 107.5 + index['IG'].spread = 62 + index['HY'].price = 107.5 -#get position by strategy folder -trade_date = datetime.date.today() - BDay(2) -df = pd.read_sql_query("SELECT security_id, notional * case when protection = 'Buyer' then -1 else 1 end as notional, " \ - "maturity, folder FROM cds where trade_date < %s", - dawndb, params = (trade_date,), parse_dates=['maturity']) -df = df.groupby(['security_id','maturity','folder']).agg(sum) -df = df[df.notional!=0].reset_index() +def get_pos(): + #get position by strategy folder + trade_date = datetime.date.today() - BDay(1) + df = pd.read_sql_query("SELECT security_id, notional * case when protection = 'Buyer' then -1 else 1 end as notional, " \ + "maturity, folder FROM cds where trade_date < %s and termination_amount is NULL", + dawndb, params = (trade_date,), parse_dates=['maturity']) + df = df.groupby(['security_id','maturity','folder']).agg(sum) + df = df[df.notional!=0].reset_index() -df1 = pd.read_sql_query("SELECT redindexcode as security_id, index, series, indexfactor from index_version where lastdate = 'infinity'", serenitasdb) -df2 = pd.read_sql_query("SELECT * from index_maturity", serenitasdb, parse_dates=['maturity']) -df3 = pd.read_sql_query("SELECT index, series, tenor, duration " \ - " FROM index_quotes where date = %s", - dawndb, params = (trade_date,)) + df1 = pd.read_sql_query("SELECT redindexcode as security_id, index, series, indexfactor from index_version where lastdate = 'infinity'", serenitasdb) + df2 = pd.read_sql_query("SELECT * from index_maturity", serenitasdb, parse_dates=['maturity']) + df3 = pd.read_sql_query("SELECT index, series, tenor, duration " \ + " FROM index_quotes where date = %s", + dawndb, params = (trade_date,)) -df = df.merge(df1, on='security_id') -df = df.merge(df2, how='inner', on=['index','maturity', 'series']) -df = df.merge(df3, on=['index','series','tenor']) + df = df.merge(df1, on='security_id') + df = df.merge(df2, how='inner', on=['index','maturity', 'series']) + df = df.merge(df3, on=['index','series','tenor']) -df['ontr_notional'] = df.apply(lambda df: df.notional * df.indexfactor/100 * df.duration/(index[df['index']].DV01/index[df['index']].notional*10000), axis = 1) -for percent in shock_values: - df[percent] = df.apply(lambda df: df['ontr_notional'] * shock.loc[df['index'],percent], axis = 1) + df['ontr_notional'] = df.apply(lambda df: df.notional * df.indexfactor/100 * df.duration/(index[df['index']].DV01/index[df['index']].notional*10000), axis = 1) + for percent in shock_values: + df[percent] = df.apply(lambda df: df['ontr_notional'] * shock.loc[df['index'],percent], axis = 1) -df1 = df.groupby(['folder']).agg(sum)[[.95,.05]] -df1 = df1.min(axis = 1) + df1 = df.groupby(['folder']).agg(sum)[[.95,.05]] + df1 = df1.min(axis = 1) + +def on_the_run(index): + r = _serenitas_engine.execute("SELECT max(series) FROM index_version WHERE index=%s", + (index,)) + series, = r.fetchone() + return series + +def rel_spread_diff(index='HY', rolling=10): + otr = on_the_run(index) + ## look at spreads + df = get_index_quotes(index, list(range(otr - rolling, otr + 1)), + tenor=['3yr', '5yr', '7yr', '10yr'], years=5) + + duration = df.duration.xs(('5yr',28), level = ['tenor','series'])[-1] + + df = df.xs('5yr', level = 'tenor')['closespread'].groupby(['date','series']).last() + df = df.loc['2013-01-15':] + curr_spread = df.iloc[-1] + df = df.unstack(-1).pct_change(freq = '22B').stack() + + return df.groupby('date').last(), curr_spread, duration + +for x, y in stress.iteritems(): + portf.ref = |