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
|
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
fh = SerenitasFileHandler("mismatched_trades.log")
logger = logging.getLogger("notify_bowdst")
logger.addHandler(fh)
logger.setLevel(logging.WARNING)
conn = dbconn("dawndb")
cob = (datetime.date.today() - BDay(1)).date()
dates = [str((cob - datetime.timedelta(days=days))) for days in range(60)]
df = pd.concat(
{
date: pd.read_sql_query(
f"""SELECT *, notional * factor as db_notional FROM list_cds_marks( %(date)s , NULL, 'BOWDST');""",
conn,
params={"date": str(date)},
)
for date in dates
}
).droplevel(level=1)
data = df[df.index == str(cob)][
["tenor", "security_desc", "security_id", "globeop_notional", "db_notional"]
]
inaccurate_balances = data[data["db_notional"] != data["globeop_notional"]]
for row in inaccurate_balances.itertuples():
subject = (
f"ACTION REQUESTED: Missing Trades {row.security_desc} RED: {row.security_id}"
)
new_df = df[df["security_desc"] == row.security_desc]
try:
data = new_df[new_df["globeop_notional"] == new_df["db_notional"]].iloc[0, :]
security_desc = data.security_desc
date = data.name
globeop_notional = data.globeop_notional
except IndexError:
print("No matches")
continue
buf = StringIO()
csv = pd.read_sql_query(
f"""SELECT * FROM cds WHERE trade_date > %(date)s and fund = 'BOWDST' and security_desc = %(security_desc)s and orig_detach is null and orig_attach is null order by trade_date desc""",
conn,
params={"date": date, "security_desc": security_desc},
)
if len(csv[pd.to_datetime(csv["trade_date"]) >= (cob - BDay(1))].index) != 0:
logger.error(
f"Mismatch balance {row.security_desc} {row.globeop_notional} :{row.db_notional} Trades Recently"
)
continue
csv.to_csv(buf)
attachments = [
FileAttachment(name=f"{security_desc}.csv", content=buf.getvalue().encode())
]
buf.close()
msg = (
f"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.security_desc} RED: {row.security_id}.\n"
f"Our notionals last matched on {date} for ${globeop_notional:,.2f}.\n\n"
f"Please see attached all the trades since we last matched. Please ensure they match your data.\n\n"
f"Thanks for your help!\n\n"
f"Flint"
)
em = ExchangeMessage()
em.send_email(
subject,
msg,
to_recipients=("fyu@lmcg.com",),
# to_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",),
attach=attachments,
)
|
