diff options
| -rw-r--r-- | python/interest_statement.py | 58 |
1 files changed, 58 insertions, 0 deletions
diff --git a/python/interest_statement.py b/python/interest_statement.py new file mode 100644 index 00000000..2a800850 --- /dev/null +++ b/python/interest_statement.py @@ -0,0 +1,58 @@ +from serenitas.utils.db import dbconn + +conn = dbconn("dawndb") +import pandas as pd +from pandas.tseries.offsets import BDay +import numpy as np + +df_rates = pd.read_sql_query( + "SELECT date, rate FROM rates where name='FED_FUND'", + conn, + parse_dates=["date"], + index_col=["date"], +).sort_index() +df_balances = pd.read_sql_query( + "SELECT * FROM strategy_im WHERE fund='SERCGMAST'", + conn, + parse_dates=["date"], + index_col=["date"], +).sort_index() +df_balances[["broker", "strategy"]] = df_balances[["broker", "strategy"]].astype( + "category" +) + + +def f(df_balances, df_rates, broker, start_date, end_date): + df = ( + df_balances[df_balances.broker == broker] + .set_index("strategy", append=True)["amount"] + .unstack("strategy") + ) + df[df.isnull()] = 0.0 + drange = pd.date_range(pd.Timestamp(start_date) - BDay(), end_date) + rates = df_rates.reindex(drange, method="ffill") / 100 / 360 + df = df.reindex(drange, method="ffill") + if broker in ["BAML_ISDA", "CITI"]: + d = {} + for strat in df: + s = df.loc[start_date:, strat] + ir_bal = 0.0 + for bal, r in zip(s.values, rates.loc[start_date:, "rate"].values): + bal += ir_bal + ir_bal += bal * r + d[strat] = ir_bal + return pd.Series(d, name="amount").to_frame() + else: + return ( + (df.loc[start_date:] * rates.loc[start_date:].values) + .sum() + .to_frame(name="amount") + ) + + +dfs = {} +for cp in ("GS", "MS", "BAML_ISDA", "CITI", "CS", "BNP"): + dfs[cp] = f(df_balances, df_rates, cp, "2020-11-01", "2020-11-30") +df = pd.concat(dfs, names=["broker"]) +df = df[df.amount != 0.0] +df.amount *= -1.0 |