diff options
Diffstat (limited to 'python/pnl_explain.py')
| -rw-r--r-- | python/pnl_explain.py | 119 |
1 files changed, 40 insertions, 79 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index f398ae6d..e21d19b4 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -68,7 +68,7 @@ def get_swaption_pv( end_date: datetime.date, fund: str, conn: connection, - **kwargs + **kwargs, ): dr = pd.bdate_range(start_date, end_date, freq=bus_day) pv = [] @@ -113,7 +113,7 @@ def get_tranche_pv( end_date: datetime.date, fund: str, conn: connection, - **kwargs + **kwargs, ): dr = pd.bdate_range(start_date, end_date, freq=bus_day) pv = [] @@ -173,7 +173,7 @@ def get_tranche_pv2( end_date: datetime.date, fund: str, conn: connection, - **kwargs + **kwargs, ): df = pd.read_sql_query( "SELECT date, tranche_id AS id, clean_nav, accrued, folder " @@ -235,27 +235,39 @@ def get_pv(**kwargs): else: return get_bond_pv(**kwargs) + def get_bond_pv( start_date: datetime.date, end_date: datetime.date, fund: str, conn: connection, - asset_class: Union[None,str], - **kwargs + asset_class: Union[None, str], + **kwargs, ): dr = pd.bdate_range(start_date - bus_day, end_date, freq=bus_day) - dfs = {} + dfs, dfs_1 = {}, {} for d in dr: dfs[d] = pd.read_sql_query( - "SELECT identifier, notional, usd_market_value, int_acc " + "SELECT identifier, usd_market_value, int_acc " "FROM risk_positions(%s, %s, %s)", conn, params=(d.date(), asset_class, fund), index_col=["identifier"], ) + dfs_1[d] = pd.read_sql_query( + "SELECT identifier, notional " "FROM list_positions(%s, %s, False, %s)", + conn, + params=(d.date(), asset_class, fund), + index_col=["identifier"], + ) positions = pd.concat(dfs, names=["date", "identifier"]) - positions[["usd_market_value", "int_acc"]] -= \ - positions.groupby(level="identifier")[["usd_market_value", "int_acc"]].shift(fill_value=0.0) + notionals = pd.concat(dfs_1, names=["date", "identifier"]) + positions = positions.merge( + notionals, how="left", left_index=True, right_index=True + ) + positions[["usd_market_value", "int_acc"]] -= positions.groupby(level="identifier")[ + ["usd_market_value", "int_acc"] + ].shift(fill_value=0.0) positions = positions.drop(start_date - bus_day) cashflows = pd.read_sql_query( "SELECT identifier, last_pay_date AS date, interest, principal " @@ -266,93 +278,42 @@ def get_bond_pv( ) trades = pd.read_sql_query( "SELECT trade_date AS date, identifier, " - "CASE WHEN buysell THEN -principal_payment ELSE principal_payment END, " - "CASE WHEN buysell THEN -accrued_payment ELSE accrued_payment END, " + "sum(CASE WHEN buysell THEN -principal_payment ELSE " + "principal_payment END) as principal_payment, " + "sum(CASE WHEN buysell THEN -accrued_payment ELSE " + "accrued_payment END) as accrued_payment, " "asset_class " "FROM bonds WHERE trade_date BETWEEN %s AND %s " - f"AND fund=%s", + f"AND fund=%s " + "group by date, identifier, asset_class", conn, params=(start_date, end_date, fund), index_col=["date", "identifier"], ) if asset_class is not None: trades = trades[trades.asset_class == asset_class] - trades.drop("asset_class", axis=1) + trades.drop("asset_class", axis=1, inplace=True) df = pd.concat([positions, cashflows, trades], axis=1) - df.interest *= df.notional /100 - df.principal *= df.principal / 100 + df.interest *= df.notional / 100 + df.principal *= df.notional / 100 return df -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.loc[trades.buysell] - sells = trades.loc[~trades.buysell] - - 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]) +def get_pnl(df_instrument, asset_class: Literal["bond", "tranche", "swaption"]): + if asset_class == "bond": + return df_instrument.drop("notional", axis=1).groupby("date").sum().sum(axis=1) + elif asset_class == "tranche": + return df_instrument.pv.diff() + df_instrument[["upfront", "accrued"]].sum( + axis=1 + ) + elif asset_class == "swaption": + return df_instrument.pv.diff() + df_instrument.daily -def get_pnl(df_instrument, asset_class: Literal['bond', 'tranche', 'swaption']): - if asset_class == "bond": - return (df_instrument. - drop("notional", axis=1). - groupby("date"). - sum(). - sum(axis=1)) - elif asset_class == "tranche": - return df_instrument.pv.diff() + df_instrument[ - ["upfront", "accrued"] - ].sum(axis=1) - elif asset_class == "swaption": - return df_instrument.pv.diff() + df_instrument.daily def cumulative_from_daily(df): return pd.concat([df, df.cumsum()], axis=1, keys=["daily", "cumulative"]) + if __name__ == "__main__": import argparse from utils.db import dbconn |