diff options
| -rw-r--r-- | python/pnl_explain.py | 52 |
1 files changed, 36 insertions, 16 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index ba5657a1..9851ee76 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -1,9 +1,10 @@ +import numpy as np import pandas as pd + from db import dbengine from dates import bus_day, imm_dates, yearfrac -import numpy as np - from psycopg2.extensions import register_adapter, AsIs + register_adapter(np.int64, lambda x: AsIs(x)) def get_daycount(identifier, engine = dbengine("dawndb")): @@ -24,21 +25,28 @@ def pnl_explain(identifier, start_date = None, end_date = None, engine = dbengine("dawndb")): """ 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']) + params=(identifier,), parse_dates=['trade_date', 'settle_date']) + for key in ['faceamount', 'principal_payment', 'accrued_payment']: + trades.loc[~trades.buysell, key] = -trades[key][~trades.buysell] + + ## take care of multiple trades settling on the same date + trades = (trades. + groupby('settle_date')[['faceamount', 'principal_payment', 'accrued_payment']]. + sum()) + 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']) factors = factors.set_index('prev_cpn_date', drop=False) daycount, delay = get_daycount(identifier, engine) - for key in ['faceamount', 'principal_payment', 'accrued_payment']: - trades.loc[~trades.buysell, key] = -trades[key][~trades.buysell] + df = (marks[['price']]. join([factors, trades[['principal_payment', 'accrued_payment', 'faceamount']]], how='outer')) df.sort_index(inplace=True) + if start_date is None: start_date = trades.trade_date.ix[trades.index.min()] if end_date is None: @@ -62,11 +70,16 @@ def pnl_explain(identifier, start_date = None, end_date = None, daily['accrued'] = yearfrac(daily.prev_cpn_date, daily.index.to_series(), daycount) * \ daily.coupon/100*daily.factor * daily.faceamount daily['unrealized_accrued'] = daily.accrued.diff() + daily.realized_accrued - # for some reason .shift(0, bus_day) doesn't work (but would work on an index) - daily['realized_accrued'] = (daily.loc[daily.realized_accrued>0, 'realized_accrued']. - shift(delay, 'D').shift(-1, bus_day).shift(1, bus_day)) - daily['realized_pnl'] = (daily.loc[daily.realized_pnl>0, 'realized_pnl']. - shift(delay, 'D').shift(-1, bus_day).shift(1, bus_day)) + if delay >0: + ## we shift cashflows by delay 'D', and then move it to the next business day + ## for some reason .shift(0, bus_day) doesn't work (but would work on an index) + if not daily.loc[daily.realized_accrued>0, 'realized_accrued'].empty: + daily['realized_accrued'] = (daily.loc[daily.realized_accrued>0, 'realized_accrued']. + shift(delay, 'D').shift(-1, bus_day).shift(1, bus_day)) + if not daily.loc[daily.realized_pnl>0, 'realized_pnl'].empty: + daily['realized_pnl'] = (daily.loc[daily.realized_pnl>0, 'realized_pnl']. + shift(delay, 'D').shift(-1, bus_day).shift(1, bus_day)) + daily[['realized_pnl', 'realized_accrued']] = (daily[['realized_pnl', 'realized_accrued']]. fillna(value=0)) extra_pnl = daily.clean_nav.diff() - daily.principal_payment @@ -191,14 +204,21 @@ def cds_explain_strat(strat, start_date, end_date, engine = dbengine("dawndb")): return pd.concat(df) if __name__=="__main__": - workdate = pd.datetime.today() engine = dbengine("dawndb") - from position import get_list - clo_list = get_list(engine, workdate, 'CLO') - df = pnl_explain_list(clo_list.identifier.tolist(), None, '2015-11-30', engine) + from position import get_list_range + ## CLO + clo_list = get_list_range(engine, '2015-01-01', '2015-12-31', 'CLO') + df = pnl_explain_list(clo_list.identifier.tolist(), '2015-01-01', '2015-12-31', engine) + df = pd.concat(df) + df_agg = df.groupby(level=1).sum() + ## subprime + subprime_list = get_list_range(engine, '2015-01-01', '2015-12-31', 'Subprime') + df = pnl_explain_list(subprime_list.identifier.tolist(), '2015-01-01', '2015-12-31', engine) df = pd.concat(df) df_agg = df.groupby(level=1).sum() - cds_df = cds_explain_strat(['SER_IGINX', 'SER_IGMEZ'], None, '2015-12-16', engine) + df_agg[['realized_accrued','unrealized_accrued', + 'realized_pnl', 'unrealized_pnl']].sum(axis=1).cumsum().plot(x_compat=True) + cds_df = cds_explain_strat(['SER_IGINX', 'SER_IGMEZ'], None, None, engine) #cds_df = cds_explain_strat(['SER_HYMEZ'], None, '2015-03-10', engine) #cds_df2 = cds_explain_strat('SER_IGCURVE', None, None, engine) #cds_df = cds_explain('HY', 21, '5yr', 25, 35, '2014-07-18') |