diff options
| -rw-r--r-- | python/pnl_explain.py | 61 |
1 files changed, 39 insertions, 22 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index 56e35634..3178e7a4 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -225,19 +225,16 @@ def get_tranche_pv2( conn, params=(start_date, end_date, fund), parse_dates=("date", "maturity"), - index_col=["date", "id"], + index_col=["date", "id", "folder"], ) df = df.sort_index() - strategies = df.folder df = df[["clean_nav", "accrued"]] with conn.cursor() as c: c.execute( - "SELECT termination_date AS date, cds.id, " - "termination_fee *(CASE WHEN currency='USD' " - "THEN 1. ELSE eurusd END) AS amount " + "SELECT termination_date AS date, cds.id, folder, currency, " + "termination_fee AS principal " "FROM terminations " "JOIN cds USING (dealid) " - "LEFT JOIN fx ON termination_date=date " "WHERE termination_date > %s AND termination_date <=%s " "AND fund=%s", (start_date, end_date, fund), @@ -247,9 +244,9 @@ def get_tranche_pv2( ) with conn.cursor() as c: c.execute( - "SELECT trade_date AS date, id, " - "upfront * (CASE WHEN currency='USD' THEN 1. ELSE eurusd END) AS amount " - "FROM cds LEFT JOIN fx ON date=trade_date " + "SELECT trade_date AS date, id, folder, currency, " + "upfront AS principal " + "FROM cds " "WHERE trade_date BETWEEN %s AND %s " "AND swap_type='CD_INDEX_TRANCHE' AND fund=%s", (start_date, end_date, fund), @@ -259,29 +256,49 @@ def get_tranche_pv2( ) 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) - principal = pd.concat([df_terminations, df_upfronts], axis=1).sum(axis=1) - principal.name = "principal" + df_temp.set_index(["date", "id", "folder", "currency"], inplace=True) + principal = pd.concat([df_terminations, df_upfronts], axis=0) df_cashflows = pd.read_sql_query( - "SELECT date, tranche_id AS id, principal * coalesce(fx, 1.) AS principal, " - "accrued * coalesce(fx, 1.) AS accrued " - "FROM tranche_cashflows " - "LEFT JOIN (" - " SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx " - "USING (date, currency) " + "SELECT date, tranche_id AS id, folder, tc.currency, principal, accrued " + "FROM tranche_cashflows tc " "LEFT 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"], - index_col=["date", "id"], + index_col=["date", "id", "folder", "currency"], ) # force to float in case of empty dataframe (otherwise it's object) - df_cashflows = df_cashflows.astype("float") - df_cashflows = principal.to_frame().add(df_cashflows, fill_value=0.0) + df_cashflows[["principal", "accrued"]] = df_cashflows[ + ["principal", "accrued"] + ].astype("float") + df_cashflows = pd.concat([df_cashflows, principal]) df_cashflows = df_cashflows.fillna(0.0) + df_cashflows = df_cashflows.groupby(["date", "id", "folder", "currency"]).sum() df_cashflows = df_cashflows.rename(columns={"accrued": "realized_accrued"}) - return pd.concat([df, df_cashflows], axis=1).join(strategies).sort_index() + fx = pd.read_sql_query( + "SELECT date, eurusd FROM fx WHERE date BETWEEN %s AND %s", + conn, + params=(start_date, end_date), + parse_dates=["date"], + index_col=["date"], + ) + df_cashflows = pd.merge( + df_cashflows.reset_index(["id", "folder", "currency"]), + fx, + on="date", + how="left", + ) + df_cashflows[df_cashflows.currency == "EUR"] = df_cashflows[ + df_cashflows.currency == "EUR" + ].assign( + realized_accrued=lambda x: x.realized_accrued * x.eurusd, + principal=lambda x: x.principal * x.eurusd, + ) + df_cashflows = df_cashflows.set_index(["id", "folder"], append=True).drop( + ["eurusd", "currency"], axis=1 + ) + return pd.concat([df, df_cashflows], axis=1).reset_index(["folder"]).sort_index() def get_fx_pv(start_date: datetime.date, end_date: datetime.date, fund: str, **kwargs): |