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
|
import pandas as pd
from functools import reduce
from position import get_list
from sqlalchemy import create_engine
def pnl_explain(engine, identifier, start_date = None, end_date = None):
""" if start_date is None, pnl since inception"""
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.bdate_range(start_date, end_date)
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))
if __name__=="__main__":
engine = create_engine("postgresql://dawn_user@debian/dawndb")
workdate = pd.datetime.today()
clo_list = get_list(engine, workdate, 'Subprime')
df = pnl_explain_list(engine, clo_list.identifier.tolist(), '2015-10-30', '2015-11-30')
|
