diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/pnl_explain.py | 60 |
1 files changed, 60 insertions, 0 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py new file mode 100644 index 00000000..2c93cbd7 --- /dev/null +++ b/python/pnl_explain.py @@ -0,0 +1,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') |