diff options
| -rw-r--r-- | python/exploration/portfolio_var.py | 97 | ||||
| -rw-r--r-- | python/externalmarksbackfill.py | 1 | ||||
| -rw-r--r-- | python/globeop_reports.py | 24 |
3 files changed, 87 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 = diff --git a/python/externalmarksbackfill.py b/python/externalmarksbackfill.py index 12ca5022..1304a7e8 100644 --- a/python/externalmarksbackfill.py +++ b/python/externalmarksbackfill.py @@ -97,6 +97,7 @@ def get_globs(): return chain.from_iterable(globs) settings = { + 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170801.20170831.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170701.20170731.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170601.20170630.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170501.20170531.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), diff --git a/python/globeop_reports.py b/python/globeop_reports.py index 1d798677..3e7eb3df 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -66,3 +66,27 @@ def get_net_navs(): df = pd.read_csv('/home/serenitas/edwin/Python/subscription_fee_data.csv', parse_dates=['date'], index_col =['date']) df.index = df.index.to_period('M').to_timestamp('M') return df.join(nav) + +def calc_trade_performance_stats(): + df = trade_performance().set_index('trade_date') + df.days_held = df.days_held.dt.days + df['winners'] = df.apply(lambda df: True if df.percent_gain > 0 else False, axis = 1) + df['curr_face'] = df.principal_payment/(df.price/100) + + index = ['All','2017','2016','2015','2014','2013'] + results = pd.DataFrame(index = index) + + win_per = len(df[df.winners].index)/len(df) + loss_per = 1- win_per + temp = {} + temp1 = {} + for x, df1 in df.groupby('winners'): + for y, df2 in df1.groupby(pd.TimeGrouper(freq='A')): + import pdb; pdb.set_trace() + y = y.date().year + results.loc[y] = df2[df2.days_held.notnull()].mean()[['curr_face','initialinvestment', 'days_held']] + results.loc[] = len(df2[df2.winners == x].index)/len(df) + + + df[df.days_held.notnull()]['days_held'].groupby(pd.TimeGrouper(freq='A')).mean() + |