1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
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
import datetime
dawndb = dbengine('dawndb')
serenitasdb = dbengine('serenitasdb')
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']])
shock = pd.DataFrame.from_records(shock, columns=['index', 'percent', 'shock'], index=['index', 'percent'])
index['IG'].spread = 62
index['HY'].price = 107.5
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,))
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)
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 =
|
