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
|
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
conn = dbconn("dawndb")
cob = (datetime.date.today() - BDay(1)).date()
dates = [str((cob - datetime.timedelta(days=days))) for days in range(60)]
# dates = [str((cob - BDay(days)).date()) for days in range(60)]
df_rates = 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
}
)
df = df_rates.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:
print(len(csv[pd.to_datetime(csv["trade_date"]) >= (cob - BDay(1))].index))
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,
)
|
