diff options
| -rw-r--r-- | python/pnl_explain.py | 31 |
1 files changed, 15 insertions, 16 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index 83718e1c..c00c2348 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -304,18 +304,18 @@ def get_bond_pv( ): dr = pd.bdate_range(start_date - bus_day, end_date, freq=bus_day) dfs, dfs_1 = {}, {} - for d in dr.date: + for d in dr: dfs[d] = pd.read_sql_query( - "SELECT identifier, usd_market_value, int_acc " + "SELECT figi AS identifier, usd_market_value, int_acc " "FROM risk_positions(%s, %s, %s)", conn, - params=(d, asset_class, fund), + 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)", + "SELECT figi AS identifier, notional FROM list_positions(%s, %s, False, %s)", conn, - params=(d, asset_class, fund), + params=(d.date(), asset_class, fund), index_col=["identifier"], ) positions = pd.concat(dfs, names=["date", "identifier"]) @@ -326,35 +326,34 @@ def get_bond_pv( ].shift(fill_value=0.0) positions = positions.drop(start_date - bus_day, level=0) cashflows = pd.read_sql_query( - "SELECT b.identifier, prev_cpn_date AS date, interest, principal " + "SELECT identifier, prev_cpn_date AS date, interest, principal " "FROM factors_history fh " - "left join (select identifier, figi from securities) b " - "on fh.identifier = b.figi " "WHERE last_pay_date BETWEEN %s AND %s", conn, params=(start_date, end_date), + parse_dates=["date"], index_col=["date", "identifier"], ) trades = pd.read_sql_query( - "SELECT trade_date AS date, identifier, " + "SELECT trade_date AS date, figi AS identifier, " "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 bond_trades WHERE trade_date BETWEEN %s AND %s " - f"AND fund=%s " - "group by date, identifier, asset_class", + "securities.asset_class " + "FROM bond_trades " + "LEFT JOIN securities USING (identifier) " + "WHERE trade_date BETWEEN %s AND %s AND fund=%s " + "GROUP BY date, figi, securities.asset_class", conn, params=(start_date, end_date, fund), + parse_dates=["date"], index_col=["date", "identifier"], ) if asset_class is not None: trades = trades[trades.asset_class == asset_class] trades.drop("asset_class", axis=1, inplace=True) df = positions.join([cashflows, trades]) - df = positions.join(cashflows) - df = df.join(trades) df.interest *= df.notional / 100 df.principal *= df.notional / 100 return df @@ -448,7 +447,7 @@ if __name__ == "__main__": "hedge": ("HEDGE_MBS", "HEDGE_CLO", "HEDGE_MAC"), "tranche": ("IGINX", "HYINX", "XOINX", "EUINX"), "curve": ("SER_ITRXCURVE", "SER_IGCURVE", "SER_HYCURVE"), - "bond": (), + "bond": ("HEDGE_MBS",), } strats["cleared"] = tuple(chain.from_iterable(strats.values())) df_index = get_index_pv( |