1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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
|
