diff options
Diffstat (limited to 'python/pnl_explain.py')
| -rw-r--r-- | python/pnl_explain.py | 16 |
1 files changed, 11 insertions, 5 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py index 322d8ac4..83718e1c 100644 --- a/python/pnl_explain.py +++ b/python/pnl_explain.py @@ -324,10 +324,13 @@ def get_bond_pv( 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) + positions = positions.drop(start_date - bus_day, level=0) cashflows = pd.read_sql_query( - "SELECT identifier, prev_cpn_date AS date, interest, principal " - "FROM factors_history WHERE last_pay_date BETWEEN %s AND %s", + "SELECT b.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), index_col=["date", "identifier"], @@ -339,8 +342,8 @@ def get_bond_pv( "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 " - "AND fund=%s " + "FROM bond_trades WHERE trade_date BETWEEN %s AND %s " + f"AND fund=%s " "group by date, identifier, asset_class", conn, params=(start_date, end_date, fund), @@ -350,6 +353,8 @@ def get_bond_pv( 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 @@ -390,6 +395,7 @@ if __name__ == "__main__": from serenitas.utils.db import dbconn from itertools import chain + serenitas.analytics._local = False dawndb = dbconn("dawndb") parser = argparse.ArgumentParser() parser.add_argument("start_date", type=datetime.datetime.fromisoformat) |