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
|
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
import sys
def send_email(inaccurate_balances, accurate_balances, logger, conn, cob, args):
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,
)
def main():
parser = argparse.ArgumentParser(description="determine sender destination")
parser.add_argument("--globeop", action="store_true", 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.INFO)
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.date(),),
index_col=["security_desc"],
)
for d in dates
],
keys=dates,
)
mask = np.isclose(df.globeop_notional, df.db_notional, atol=1e-2, rtol=0.0)
try:
inaccurate_balances = df[~mask].loc[
pd.Timestamp(cob),
["tenor", "security_id", "globeop_notional", "db_notional"],
]
except KeyError:
logger.info("All Balances Matching")
sys.exit()
accurate_balances = df[mask]
send_email(inaccurate_balances, accurate_balances, logger, conn, cob, args)
logger.info("Program executed.")
if __name__ == "__main__":
main()
|
