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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
import pandas as pd
from functools import reduce
from position import get_list
from sqlalchemy import create_engine
from dates import bus_day, imm_dates
def pnl_explain(identifier, start_date = None, end_date = None,
uri = 'postgresql://dawn_user@debian/dawndb'):
""" if start_date is None, pnl since inception"""
engine = create_engine(uri)
trades = pd.read_sql_query("SELECT * FROM bonds where identifier=%s", engine,
params=(identifier,), parse_dates=['trade_date', 'settle_date'],
index_col=['settle_date'])
marks = pd.read_sql_query("SELECT * FROM marks where identifier=%s", engine,
params=(identifier,), parse_dates = ['date'], index_col='date')
factors = pd.read_sql_query("SELECT * FROM factors_history where identifier=%s", engine,
params=(identifier,), parse_dates = ['last_pay_date', 'prev_cpn_date'],
index_col=['last_pay_date'])
for key in ['faceamount', 'principal_payment', 'accrued_payment']:
trades.loc[~trades.buysell, key] = -trades[key][~trades.buysell]
df = (marks[['price']].join(factors, how='outer').
join(trades[['principal_payment', 'accrued_payment', 'faceamount']], how='outer'))
df.sort_index(inplace=True)
if start_date is None:
start_date = trades.index.min()
if end_date is None:
end_date = pd.datetime.today()
dates = pd.date_range(start_date, end_date, freq = bus_day)
keys1 = ['price','factor', 'coupon', 'prev_cpn_date']
df[keys1] = df[keys1].fillna(method='ffill')
keys2 = ['losses', 'principal','interest', 'faceamount','accrued_payment', 'principal_payment']
df[keys2] = df[keys2].fillna(value=0)
df.faceamount = df.faceamount.cumsum()
keys = keys1 + ['faceamount']
df1 = df.reindex(dates, keys, method='ffill')
keys = ['losses', 'principal','interest', 'accrued_payment', 'principal_payment']
df2 = df.reindex(dates, keys, fill_value=0)
daily = pd.concat([df1, df2], axis = 1)
daily['unrealized_pnl'] = daily.price.diff() * daily.factor.shift()/100 * daily.faceamount
daily['realized_pnl'] = (daily.price/100*daily.factor.diff()+daily.principal/100) * daily.faceamount
daily['clean_nav'] = daily.price/100 * daily.factor * daily.faceamount
daily['realized_accrued'] = daily.interest/100 * daily.faceamount
days_accrued = daily.index - daily.prev_cpn_date
daily['unrealized_accrued'] = days_accrued.dt.days/360*daily.coupon/100*daily.factor*daily.faceamount
extra_pnl = daily.clean_nav.diff() - daily.principal_payment
daily.loc[daily.principal_payment>0 , 'unrealized_pnl'] += extra_pnl[daily.principal_payment>0]
daily.loc[daily.principal_payment<0, 'realized_pnl'] += extra_pnl[daily.principal_payment<0]
daily['realized_accrued'] -= daily.accrued_payment
daily['accrued'] = daily.unrealized_accrued.diff() + daily.realized_accrued
return daily[['unrealized_pnl', 'realized_pnl', 'unrealized_accrued', 'realized_accrued', 'accrued']].iloc[1:,]
def pnl_explain_list(engine, id_list, start_date = None, end_date = None):
return reduce(lambda x,y: x.add(y, fill_value=0),
(pnl_explain(engine, identifier, start_date, end_date) for identifier in id_list))
def cds_explain(engine, index, series, tenor, attach = None, detach = None,
start_date = None, end_date = None):
factors = pd.read_sql_query("SELECT * FROM index_desc WHERE index=%s AND series=%s AND tenor=%s "\
"ORDER BY lastdate",
engine, parse_dates=['lastdate'],
index_col='lastdate', params = (index, series, tenor))
if attach is None:
quotes = pd.read_sql_query("SELECT * from index_quotes WHERE index=%s AND series=%s AND tenor=%s " \
"ORDER BY date",
engine, parse_dates=['date'],
index_col='date', params = (index, series, tenor))
else:
quotes = pd.read_sql_query("SELECT quotedate, upfront_mid AS closeprice, tranche_spread " \
"FROM markit_tranche_quotes JOIN index_version " \
"USING (basketid) WHERE index=%s AND series=%s " \
"AND tenor=%s AND attach=%s AND detach=%s " \
"ORDER by quotedate",
engine, parse_dates=['quotedate'], index_col='quotedate',
params = (index, series, tenor, attach, detach))
if start_date is None:
start_date = quotes.index.min()
if end_date is None:
end_date = pd.datetime.today()
coupon = 0.01
dates = pd.date_range(start_date, end_date, freq = bus_day)
yearfrac = imm_dates(start_date, end_date)
yearfrac = yearfrac.to_series().reindex(dates, method='ffill')
yearfrac = yearfrac.index-yearfrac
yearfrac = (yearfrac.dt.days+1)/360
yearfrac.name = 'yearfrac'
quotes = quotes.reindex(dates, method='ffill')
recovery = -factors.indexfactor.diff()-factors.cumulativeloss.diff()
recovery.name = 'recovery'
recovery = recovery.shift(-1)/100
recovery = recovery.reindex(dates, fill_value=0).shift()
df = (quotes.
join(factors[['indexfactor']], how='left').
join(recovery).join(yearfrac))
df.indexfactor = df.indexfactor.bfill()/100
df.loc[df.indexfactor.isnull(), 'indexfactor'] = factors.indexfactor.iat[-1]/100
df['unrealized_accrued'] = df.yearfrac*coupon*df.indexfactor
df['accrued'] = df.unrealized_accrued.diff()
df['realized_accrued'] = -df.accrued.where(df.accrued<0, 0)
df.accrued = df.accrued.where(df.accrued>0, df.unrealized_accrued)
df.loc[df.realized_accrued>0, 'realized_accrued'] += df.loc[df.realized_accrued>0, 'accrued']
df['unrealized_pnl'] = df.closeprice.diff() * df.indexfactor.shift()/100
df['realized_pnl'] = df.closeprice/100*df.indexfactor.diff()+df.recovery
return df
if __name__=="__main__":
# workdate = pd.datetime.today()
# clo_list = get_list(workdate, 'Subprime')
# df = pnl_explain_list(engine, clo_list.identifier.tolist(), '2015-10-30', '2015-11-30')
engine = create_engine("postgresql://serenitas_user@debian/serenitasdb")
df = cds_explain(engine, 'IG', 9, '10yr')
|
