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
|
from serenitas.utils.db import dbconn
import pandas as pd
from pandas.tseries.offsets import BDay
import datetime
from io import StringIO
from exchangelib import FileAttachment
from serenitas.utils.exchange import ExchangeMessage
from serenitas.utils import SerenitasFileHandler
import logging
import argparse
import numpy as np
parser = argparse.ArgumentParser(description="determine sender destination")
parser.add_argument("--globeop", help="send to globeop")
parser.add_argument(
"--debug", "-d", action="store_true", default=False, help="log to the console"
)
args = parser.parse_args()
if args.debug:
logging.basicConfig()
logger = logging.getLogger(__name__)
if not logger.handlers:
fh = SerenitasFileHandler("mismatched_trades.log")
logger.addHandler(fh)
logger.setLevel(logging.WARNING)
conn = dbconn("dawndb")
cob = (datetime.date.today() - BDay(1)).date()
dates = pd.date_range(end=cob, periods=50)
df = pd.concat(
[
pd.read_sql_query(
"SELECT *, notional * factor AS db_notional "
"FROM list_cds_marks(%s, NULL, 'BOWDST')",
conn,
params=(d,),
index_col=["security_desc"],
)
for d in dates.date
],
keys=dates,
)
mask = np.isclose(df.globeop_notional, df.db_notional, atol=1e-2, rtol=0.0)
inaccurate_balances = df[~mask].loc[
pd.Timestamp(cob), ["tenor", "security_id", "globeop_notional", "db_notional"]
]
accurate_balances = df[mask]
for row in inaccurate_balances.itertuples():
subject = f"ACTION REQUESTED: Missing Trades {row.Index} RED: {row.security_id}"
try:
last_date = accurate_balances.xs(row.Index, level="security_desc").index[-1]
except IndexError:
logger.warning(f"No matches for {row.security_desc}")
continue
buf = StringIO()
output = pd.read_sql_query(
"SELECT * FROM cds WHERE trade_date > %s and trade_date <= %s "
"AND fund = 'BOWDST' AND security_desc = %s "
"AND orig_detach IS NULL AND orig_attach IS NULL ORDER BY trade_date DESC",
conn,
params=(last_date, cob, row.Index),
)
if not output[output.trade_date >= cob - BDay(1)].empty:
logger.warning(
f"Mismatch balance {row.Index} {row.globeop_notional} :{row.db_notional} Trades Recently"
)
continue
output.to_csv(buf, index=False)
attachments = [
FileAttachment(name=f"{row.Index}.csv", content=buf.getvalue().encode())
]
buf.close()
msg = (
"Good morning!\n\n"
f"We notice a difference in our notional ({row.db_notional:,.2f}) versus yours ({row.globeop_notional:,.2f}) "
f"for security {row.Index} RED: {row.security_id}.\n"
f"Our notionals last matched on {last_date} for ${row.globeop_notional:,.2f}.\n\n"
"Please see attached all the trades since we last matched. Please ensure they match your data.\n\n"
"Thanks for your help!\n\n"
"Flint"
)
em = ExchangeMessage()
if args.globeop:
recipients = (
"caagtradecapture@bnymellon.com",
"hm-operations@bnymellon.com",
"caagprim@bnymellon.com",
"julie.picariello@bnymellon.com",
"sa1futures.optionsprocessing@bnymellon.com",
)
cc_recipients = (
"fyu@lmcg.com",
"Bowdoin-Ops@LMCG.com",
)
else:
recipients = ("fyu@lmcg.com",)
cc_recipients = ()
em.send_email(
subject,
msg,
to_recipients=recipients,
cc_recipients=cc_recipients,
attach=attachments,
)
|
