diff options
Diffstat (limited to 'python/pnl_explain.py')
| -rw-r--r-- | python/pnl_explain.py | 101 |
1 files changed, 59 insertions, 42 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index c923fc1e..1c96deda 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -28,12 +28,13 @@ def pnl_explain(identifier, start_date = None, end_date = None, 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] + if start_date is None: + start_date = trades.trade_date.min() ## 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, @@ -41,58 +42,64 @@ def pnl_explain(identifier, start_date = None, end_date = None, factors = factors.set_index('prev_cpn_date', drop=False) daycount, delay = get_daycount(identifier, engine) - df = (marks[['price']]. - join([factors, trades[['principal_payment', 'accrued_payment', 'faceamount']]], + join([factors[['prev_cpn_date', 'coupon', 'factor']], + trades[['principal_payment', 'accrued_payment', 'faceamount']]], how='outer')) + factors = factors.set_index('last_pay_date') + df = df.join(factors[['principal', 'losses', 'interest']], 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: 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') + + ## overwrite the factor to 1 in case of zero factor bond + df['orig_factor'] = df['factor'] if identifier.endswith('_A'): - df.loc[df.price.notnull() & (df.factor==0),'factor'] = 1 - df.loc[df.price.notnull() & (df.factor==0),'factor'] = 1 + df.loc[df.price.notnull() & (df.factor==0), 'factor'] = 1 + 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 = keys1 + ['faceamount', 'orig_factor'] + df1 = df.reindex(df.index.union(dates), keys, method='ffill') keys = ['losses', 'principal','interest', 'accrued_payment', 'principal_payment'] - df2 = df.reindex(dates, keys, fill_value=0) + df2 = df.reindex(df.index.union(dates), keys, fill_value=0) daily = pd.concat([df1, df2], axis = 1) + daily = daily[(start_date-1):end_date] 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['realized_accrued'] = daily.interest/100 * daily.faceamount - if identifier.endswith('_A'): - daily['accrued'] = 0 + ## realized pnl due to factor change + daily['realized_pnl'] = daily.price/100 * daily.factor.diff() * daily.faceamount.shift() + ## realized pnl due to principal payment + if delay: + daily['realized_pnl'] = (daily['realized_pnl']. + add(daily.principal/100 * daily.faceamount.shift(delay, 'D'), + fill_value=0)) + else: + daily['realized_pnl'] = (daily['realized_pnl']. + add(daily.principal/100 * daily.faceamount.shift(), + fill_value=0)) + if delay: + daily['realized_accrued'] = daily.interest/100 * daily.faceamount.shift(delay, 'D') else: - daily['accrued'] = yearfrac(daily.prev_cpn_date, daily.index.to_series(), daycount) * \ - daily.coupon/100*daily.factor * daily.faceamount - 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_accrued'] = daily.interest/100 * daily.faceamount.shift() + daily['realized_accrued'] = daily['realized_accrued'].fillna(value=0) + daily['accrued'] = yearfrac(daily.prev_cpn_date, daily.index.to_series(), daycount) * \ + daily.coupon/100 * daily.orig_factor * daily.faceamount + daily['unrealized_accrued'] = daily.accrued.diff() + daily.realized_accrued cols = ['unrealized_pnl', 'realized_pnl', 'realized_accrued', 'clean_nav'] daily[cols] = daily[cols].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:,] + 'realized_accrued']] def pnl_explain_list(id_list, start_date = None, end_date = None, engine = dbengine("dawndb")): return {(identifier, strategy): pnl_explain(identifier, start_date, end_date, engine) @@ -212,18 +219,28 @@ if __name__=="__main__": engine = dbengine("dawndb") 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() + # 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() - 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') + subprime_list = get_list_range(engine, '2015-09-30', '2015-10-31', 'Subprime') + df_subprime = pnl_explain_list(subprime_list[['identifier', 'strategy']].to_records(index=False), + '2015-09-30', '2015-10-31', engine) + df_subprime = pd.concat(df_subprime, names=['identifier', 'strategy', 'date']) + # monthly_pnl = (df_subprime.reset_index('strategy', drop=True). + # reset_index('identifier'). + # groupby('identifier'). + # resample('M', how='sum')) + # ## daily pnl by strategy + #df_agg = df_subprime.groupby(level=['date', 'strategy']).sum() + # ## monthly pnl by strategy + # df_monthly = df_agg.reset_index('strategy').groupby('strategy').resample('M', 'sum') + # df_monthly = df_monthly.swaplevel('strategy', 'date').sort_index() + # monthly_pnl = df_monthly.groupby(level='date')[['unrealized_accrued', 'unrealized_pnl', 'realized_pnl']].sum().sum(axis=1) + # # 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') |