diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/pnl_explain.py | 44 |
1 files changed, 31 insertions, 13 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index b2122669..ba5657a1 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -1,13 +1,25 @@ import pandas as pd -from functools import reduce -from position import get_list from db import dbengine -from dates import bus_day, imm_dates +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")): + """ retrieve daycount and paydelay for a given identifier""" + conn = engine.raw_connection() + with conn.cursor() as c: + c.execute("SELECT day_count, pay_delay FROM securities WHERE identifier=%s", + (identifier,)) + try: + a, b = c.fetchone() + except TypeError: + conn.commit() + return None, None + conn.commit() + return a, b + def pnl_explain(identifier, start_date = None, end_date = None, engine = dbengine("dawndb")): """ if start_date is None, pnl since inception""" @@ -17,9 +29,9 @@ def pnl_explain(identifier, start_date = None, end_date = None, 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']) - + 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] @@ -42,20 +54,25 @@ def pnl_explain(identifier, start_date = None, end_date = None, 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['clean_nav'] = daily.price/100 * daily.factor * 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['accrued'] = days_accrued.dt.days/360*daily.coupon/100*daily.factor * \ - daily.faceamount + 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)) + daily[['realized_pnl', 'realized_accrued']] = (daily[['realized_pnl', 'realized_accrued']]. + fillna(value=0)) 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['unrealized_accrued'] = daily.accrued.diff() + daily.realized_accrued return daily[['clean_nav', 'accrued', 'unrealized_pnl', 'realized_pnl', 'unrealized_accrued', 'realized_accrued']].iloc[1:,] @@ -176,11 +193,12 @@ def cds_explain_strat(strat, start_date, end_date, engine = dbengine("dawndb")): 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) df = pd.concat(df) df_agg = df.groupby(level=1).sum() - cds_df = cds_explain_strat(['SER_IGINX', 'SER_IGMEZ'], None, '2015-12-08', engine) + cds_df = cds_explain_strat(['SER_IGINX', 'SER_IGMEZ'], None, '2015-12-16', 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') |