diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/pnl_explain.py | 156 |
1 files changed, 123 insertions, 33 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index c768052d..49a699f0 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -159,18 +159,20 @@ def get_tranche_pv2( conn: connection, **kwargs ): - df = pd.read_sql_query("SELECT date, tranche_id AS id, clean_nav, accrued, folder " - "FROM tranche_risk " - "JOIN cds ON tranche_id=id " - "WHERE date BETWEEN %s and %s AND fund=%s", - conn, - params=(start_date, end_date, fund), - parse_dates=("date", "maturity"), - index_col=["date", "id"]) + df = pd.read_sql_query( + "SELECT date, tranche_id AS id, clean_nav, accrued, folder " + "FROM tranche_risk " + "JOIN cds ON tranche_id=id " + "WHERE date BETWEEN %s and %s AND fund=%s", + conn, + params=(start_date, end_date, fund), + parse_dates=("date", "maturity"), + index_col=["date", "id"], + ) df = df.sort_index() strategies = df.folder df = df[["clean_nav", "accrued"]] - df -= df.groupby(level="id")[["clean_nav", "accrued"]].shift(fill_value=0.) + df -= df.groupby(level="id")[["clean_nav", "accrued"]].shift(fill_value=0.0) df = df.drop(start_date) with conn.cursor() as c: c.execute( @@ -184,7 +186,9 @@ def get_tranche_pv2( "AND fund=%s", (start_date, end_date, fund), ) - df_terminations = pd.DataFrame.from_records(c, columns=[desc.name for desc in c.description]) + df_terminations = pd.DataFrame.from_records( + c, columns=[desc.name for desc in c.description] + ) with conn.cursor() as c: c.execute( "SELECT trade_date AS date, id, " @@ -195,14 +199,17 @@ def get_tranche_pv2( "AND swap_type='CD_INDEX_TRANCHE' AND fund=%s", (start_date, end_date, fund), ) - df_upfronts = pd.DataFrame.from_records(c, columns=[desc.name for desc in c.description]) + df_upfronts = pd.DataFrame.from_records( + c, columns=[desc.name for desc in c.description] + ) for df_temp in (df_terminations, df_upfronts): df_temp.date = pd.to_datetime(df_temp.date) df_temp.set_index(["date", "id"], inplace=True) daily = pd.concat([df_terminations, df_upfronts], axis=1).sum(axis=1) - daily.name = 'daily' + daily.name = "daily" return pd.concat([df, daily], axis=1).join(strategies).sort_index() + def get_pv(**kwargs): if kwargs.pop("pnl_type") == "swaption": return get_swaption_pv(**kwargs) @@ -210,6 +217,59 @@ def get_pv(**kwargs): return get_tranche_pv(**kwargs) +def bond_pnl( + start_date: datetime.date, + end_date: datetime.date, + fund: str, + conn: connection, + strat: str, + **kwargs +): + start_df = pd.read_sql_query( + "SELECT * from risk_positions(%s, %s, %s)", + conn, + params=(start_date, strat, fund), + index_col=["identifier"], + ) + end_df = pd.read_sql_query( + "SELECT * from risk_positions(%s, %s, %s)", + conn, + params=(end_date, strat, fund), + index_col=["identifier"], + ) + cash_flow = pd.read_sql_query( + "SELECT * from factors_history where " + "last_pay_date >= %s and last_pay_date < %s", + conn, + params=(start_date, end_date), + index_col=["identifier"], + ) + trades = pd.read_sql_query( + "SELECT * from bonds where " + "trade_date >= %s and trade_date < %s " + "and asset_class = %s and fund = %s", + conn, + params=(start_date, end_date, strat, fund), + index_col=["identifier"], + ) + start_df = start_df.merge(cash_flow, how="left", on="identifier") + start_df.interest = start_df.interest * start_df.notional / 100 + start_df.principal = start_df.principal * start_df.notional / 100 + buys = trades[trades.buysell == True] + sells = trades[trades.buysell == False] + + pnl = ( + (end_df.usd_market_value.sum() + end_df.int_acc.sum()) + - (start_df.usd_market_value.sum() + start_df.int_acc.sum()) + + start_df.interest.sum() + + start_df.principal.sum() + + (sells.principal_payment.sum() + sells.accrued_payment.sum()) + - (buys.principal_payment.sum() + buys.accrued_payment.sum()) + ) + + return pd.DataFrame({"strat": strat, "pnl": pnl}, index=[end_date]) + + if __name__ == "__main__": import argparse from utils.db import dbconn @@ -240,7 +300,7 @@ if __name__ == "__main__": action="store", default="tranche", dest="pnl_type", - help="instrument for which we want the pnl (one of 'tranche' or 'swaption')", + help="instrument for which we want the pnl ('tranche', 'swaption', 'bond', 'hedge')", ) parser.add_argument( "-f", @@ -250,26 +310,56 @@ if __name__ == "__main__": dest="fund", help="fund we run the pnl for", ) - args = parser.parse_args() - swaption_strats = ("IGOPTDEL", "HYOPTDEL") - tranche_strats = ("IGINX", "HYINX", "XOINX") - if args.pnl_type == "tranche": - index_strats = tranche_strats - else: - index_strats = swaption_strats - df_index = get_index_pv( - args.start_date, args.end_date, args.fund, dawndb, index_strats + parser.add_argument( + "-b", + "--bond_type", + action="store", + dest="bond_type", + help="bond type for which we want the pnl ('Subprime', 'CLO', 'CRT')", ) + args = parser.parse_args() + strats = {} + strats["swaption"] = ("IGOPTDEL", "HYOPTDEL") + strats["hedge"] = ("HEDGE_MBS", "HEDGE_CLO", "HEDGE_MAC") + strats["tranche"] = ("IGINX", "HYINX", "XOINX") + if args.pnl_type in ("tranche", "swaption"): + df_instrument = get_pv(conn=dawndb, **vars(args)) + pnl_instrument = df_instrument.pv.diff() + df_instrument.daily + if args.pnl_type not in ("bond"): + df_index = get_index_pv( + args.start_date, args.end_date, args.fund, dawndb, strats[args.pnl_type] + ) + pnl_index = df_index.pv.diff() + df_index.daily - df_instrument = get_pv(conn=dawndb, **vars(args)) - - pnl_index = df_index.pv.diff() + df_index.daily - pnl_instrument = df_instrument.pv.diff() + df_instrument.daily - pnl = pd.concat([pnl_index, pnl_instrument], keys=["index", args.pnl_type], axis=1) - print( - pd.concat( - [pnl.sum(axis=1), pnl.sum(axis=1).cumsum()], - axis=1, - keys=["daily", "cumulative"], + if args.pnl_type in ("tranche", "swaption"): + pnl = pd.concat( + [pnl_index, pnl_instrument], keys=["index", args.pnl_type], axis=1 ) - ) + print( + pd.concat( + [pnl.sum(axis=1), pnl.sum(axis=1).cumsum()], + axis=1, + keys=["daily", "cumulative"], + ) + ) + elif args.pnl_type not in ("bond"): + print( + pd.concat( + [pnl_index, pnl_index.cumsum()], axis=1, keys=["daily", "cumulative"], + ) + ) + else: + dates = pd.date_range(args.start_date, args.end_date) + df_bond = pd.DataFrame() + if args.bond_type is None: + bonds = ["Subprime", "CLO", "CRT"] + else: + bonds = [args.bond_type] + for asset_class in bonds: + for s, e in zip(dates.shift(-1), dates): + df_bond = df_bond.append( + bond_pnl(s.date(), e.date(), args.fund, dawndb, asset_class) + ) + df_bond.index.name = "date" + pnl = df_bond.groupby("date").sum() + print(pd.concat([pnl, pnl.cumsum()], axis=1, keys=["daily", "cumulative"],)) |