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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
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",
"barclays",
)
funds = ("Serenitas", "Brinker", "BowdSt", "Selene")
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 funds:
cp_mod.download_files(em, fund=fund)
workdate = prev_business_day(args.workdate)
cp_dict = {
"Serenitas": {
"fcms": ("baml_fcm", "wells"),
"isda_cps": (
"baml_isda",
"jpm",
"ms",
"gs",
"bnp",
"cs",
"citi",
"barclays",
),
},
"Brinker": {"fcms": (), "isda_cps": ("ms", "gs")},
"BowdSt": {
"fcms": ("gs_fcm",),
"isda_cps": ("ms", "bnp", "gs", "baml_isda", "cs", "jpm"),
},
"Selene": {
"fcms": ("baml_fcm",),
"isda_cps": ("baml_isda", "gs", "ms", "jpm", "barclays", "bnp"),
},
}
def run_collateral(cp, fund, positions, positions_rates, workdate, engine):
cp_mod = import_module("." + cp, "collateral")
lookback = 0
while lookback < 2:
try:
return cp_mod.collateral(
workdate,
positions,
engine=engine,
fund=fund,
positions_irs=positions_rates,
)
except FileNotFoundError as e:
logger.warning(e)
lookback += 1
workdate = prev_business_day(workdate)
except ValueError as e:
logger.error(f"{fund} ({cp}) : {str(e)}")
break
else:
break
df = {}
fcm_mapping = {
("baml_fcm", "Serenitas"): "BAML",
("wells", "Serenitas"): "WF",
("gs_fcm", "BowdSt"): "GS",
("baml_fcm", "Selene"): "BOAFC",
}
fund_mapping = {
"Serenitas": "SERCGMAST",
"Brinker": "BRINKER",
"BowdSt": "BOWDST",
"Selene": "ISOSEL",
}
for fund in funds:
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], fund_mapping[fund]),
index_col=["security_id", "maturity"],
parse_dates=["maturity"],
)
positions_rates = pd.read_sql_query(
"SELECT maturity_date, (fixed_rate /100)::NUMERIC(4, 4) as fixed_rate, float_index, currency, roll_day, notional, folder "
"FROM list_ir_swap_positions(%s, %s)",
dawn_engine,
params=(
workdate,
fund_mapping[fund],
),
index_col=["maturity_date", "fixed_rate", "roll_day", "float_index"],
parse_dates=["maturity_date"],
)
df[(fund, fcm.upper())] = run_collateral(
fcm, fund, positions, positions_rates, workdate, dawn_engine
)
for cp in cp_dict[fund]["isda_cps"]:
df[(fund, cp.upper())] = run_collateral(
cp, fund, bilat_positions, None, 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"]]
sql_str = (
"INSERT INTO strategy_im VALUES(%s, %s, %s, %s, %s, %s) "
"ON CONFLICT (date, fund, broker, strategy, currency) DO UPDATE "
"SET amount=EXCLUDED.amount"
)
with dawn_engine.connect() as conn:
conn.execute(sql_str, list(df.itertuples(index=False)))
if args.send_email:
send_email(workdate, df[df.fund == "SERCGMAST"].drop("fund", axis=1))
|
