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
|
import datetime
import pandas as pd
from dates import bus_day
from env import DAILY_DIR
from exchange import ExchangeMessage
from exchangelib import FileAttachment
from io import StringIO
from remote import SftpClient
from typing import Tuple
from utils.db import dbconn
def download_messages(em):
for msg in em.get_msgs(
20,
path=["BowdoinOps", "Reports"],
subject__startswith="Document(s) from Reporting",
):
if msg.sender == "notify@bnymellon.com":
for attach in msg.attachments:
fname = attach.name
if fname.endswith("csv") and fname.startswith("Asset Detail"):
date = datetime.datetime.strptime(
fname.split("_")[1].split(".")[0], "%d %b %Y"
).date()
p = DAILY_DIR / str(date) / "Reports" / fname
if not p.parent.exists():
p.parent.mkdir(parents=True)
if not p.exists():
p.write_bytes(attach.content)
def cmp_positions(cob: datetime.date, df_blotter: pd.DataFrame) -> pd.DataFrame:
workdate = (cob + bus_day).date()
p = DAILY_DIR / str(workdate) / "Reports" / f"Asset Detail_{workdate:%d %b %Y}.csv"
df = pd.read_csv(p, thousands=",")
df = df[df["Asset Type"] == "FIXED INCOME SECURITIES"]
df = df.set_index("CUSIP")
df = df[["Shares/Par", "Base Price", "Local Market Value"]]
for col in df.select_dtypes(include=["object"]).columns:
df[col] = df[col].apply(lambda s: s[1:-1] if s.startswith("(") else s)
df[col] = pd.to_numeric(df[col].str.replace(",", ""))
check = df_blotter.join(df)
return check
def get_positions(
cob: datetime.date,
) -> Tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame]:
dawndb = dbconn("dawndb")
df_blotter = pd.read_sql_query(
"SELECT * FROM risk_positions(%s, NULL, %s)",
dawndb,
params=(cob, "BOWDST"),
index_col=["identifier"],
)
cds_positions = pd.read_sql_query(
"SELECT * FROM list_cds_marks_pre(%s, NULL, %s)",
dawndb,
params=(cob, "BOWDST"),
index_col=["security_id"],
)
tranche_positions = pd.read_sql_query(
"SELECT id, security_id, security_desc, maturity, a.notional, "
"protection, orig_attach, orig_detach, tranche_factor, clean_nav, "
"accrued, cp_code, cpty_id from list_cds(%s, %s) a "
"LEFT JOIN tranche_risk ON id=tranche_id AND date=%s "
"WHERE orig_attach IS NOT NULL",
dawndb,
params=(cob, "BOWDST", cob),
index_col=["id"],
)
return df_blotter, cds_positions, tranche_positions
def send_email(
em,
workdate: datetime.date,
df_bonds: pd.DataFrame,
df_cds: pd.DataFrame,
df_tranches: pd.DataFrame,
):
attachments = []
for name, df in zip(("bonds", "cds", "tranches"), (df_bonds, df_cds, df_tranches)):
buf = StringIO()
df.to_csv(buf)
attachments.append(
FileAttachment(
name=f"{workdate} {name}.csv", content=buf.getvalue().encode()
)
)
buf.close()
em.send_email(
f"{workdate} EOD positions",
"",
to_recipients=("reconfiles@bnymellon.com", "hm-operations@bnymellon.com"),
cc_recipients=("bowdoin-ops@lmcg.com",),
attach=attachments,
)
if __name__ == "__main__":
import argparse
parser = argparse.ArgumentParser()
parser.add_argument(
"cob",
nargs="?",
type=datetime.date.fromisoformat,
default=(datetime.date.today() - bus_day).date(),
help="close of business date",
)
args = parser.parse_args()
em = ExchangeMessage()
download_messages(em)
df_bonds, df_cds, df_tranches = get_positions(args.cob)
send_email(em, args.cob, df_bonds, df_cds, df_tranches)
# sftp = SftpClient.from_creds("gs")
|
