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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
|
import pandas as pd
from importlib import import_module
from serenitas.utils import SerenitasFileHandler
from serenitas.utils.db import dawn_engine, dbconn
from .common import get_bilateral_trades, send_email, prev_business_day
import argparse
import datetime
import logging
fh = SerenitasFileHandler("collateral_calc.log")
logger = logging.getLogger("collateral")
if not logger.handlers:
logger.addHandler(fh)
logger.setLevel(logging.WARNING)
parser = argparse.ArgumentParser()
parser.add_argument(
"workdate",
nargs="?",
type=datetime.date.fromisoformat,
default=datetime.date.today(),
)
parser.add_argument(
"-d", "--download", action="store_true", help="download counterparty reports"
)
parser.add_argument(
"-s", "--send-email", action="store_true", help="send email to Globeop"
)
args = parser.parse_args()
counterparties = (
"citi",
"baml_isda",
"ms",
"gs",
"bnp",
"baml_fcm",
"wells",
"cs",
"gs_fcm",
"jpm",
)
if args.download:
from serenitas.utils.exchange import ExchangeMessage
em = ExchangeMessage()
for cp in counterparties:
cp_mod = import_module(f".{cp}", "collateral")
for fund in ("Serenitas", "Brinker", "BowdSt"):
cp_mod.download_files(em, fund=fund)
workdate = prev_business_day(args.workdate)
cp_dict = {
"Serenitas": {
"fcms": ("baml_fcm", "wells"),
"isda_cps": ("citi", "baml_isda", "jpm", "ms", "gs", "bnp", "cs"),
},
"Brinker": {"fcms": (), "isda_cps": ("ms", "gs")},
"BowdSt": {
"fcms": ("gs_fcm",),
"isda_cps": ("ms", "bnp", "gs", "baml_isda", "cs", "jpm"),
},
}
def run_collateral(cp, fund, positions, workdate, engine):
cp_mod = import_module("." + cp, "collateral")
lookback = 0
while lookback < 2:
try:
return cp_mod.collateral(workdate, positions, engine=engine, fund=fund)
except FileNotFoundError as e:
logger.info(e)
lookback += 1
workdate = prev_business_day(workdate)
except ValueError as e:
logger.error(f"{fund} ({cp}) : {e}")
break
else:
break
df = {}
fcm_mapping = {"baml_fcm": "BAML", "wells": "WF", "gs_fcm": "GS"}
fund_mapping = {"Serenitas": "SERCGMAST", "Brinker": "BRINKER", "BowdSt": "BOWDST"}
for fund in ("Serenitas", "Brinker", "BowdSt"):
bilat_positions = get_bilateral_trades(workdate, fund_mapping[fund], dawn_engine)
for fcm in cp_dict[fund]["fcms"]:
positions = pd.read_sql_query(
"SELECT security_id, security_desc, maturity, "
"folder, notional, currency "
"FROM list_cds_positions_by_strat_fcm(%s, %s, %s)",
dawn_engine,
params=(workdate, fcm_mapping[fcm], fund_mapping[fund]),
index_col=["security_id", "maturity"],
)
df[(fund, fcm.upper())] = run_collateral(
fcm, fund, positions, workdate, dawn_engine
)
for cp in cp_dict[fund]["isda_cps"]:
df[(fund, cp.upper())] = run_collateral(
cp, fund, bilat_positions, workdate, dawn_engine
)
df = pd.concat(df, names=["fund", "broker", "strategy"]).reset_index()
df.strategy = df.strategy.str.replace("^(M_|SER_)?", "", 1, regex=True)
df["fund"] = df.fund.map(fund_mapping)
df = df[["date", "broker", "strategy", "Amount", "Currency", "fund"]]
conn = dbconn("dawndb")
sql_str = (
"INSERT INTO strategy_im VALUES(%s, %s, %s, %s, %s, %s) "
"ON CONFLICT (date, strategy, broker, fund) DO UPDATE "
"SET currency=EXCLUDED.currency, amount=EXCLUDED.amount"
)
with conn.cursor() as c:
for t in df.itertuples(index=False):
c.execute(sql_str, t)
conn.commit()
conn.close()
if args.send_email:
send_email(workdate, df[df.fund == "SERCGMAST"].drop("fund", axis=1))
|
