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
|
import datetime
import pandas as pd
from env import DAILY_DIR
from exchange import ExchangeMessage
from utils.db import dbconn
em = ExchangeMessage()
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)
workdate = datetime.date.today()
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"]]
df["Local Market Value"] = pd.to_numeric(df["Local Market Value"].str.replace(",", ""))
dawndb = dbconn("dawndb")
df_blotter = pd.read_sql_query(
"SELECT * FROM risk_positions(%s, NULL, %s)",
dawndb,
params=(workdate, "BOWDST"),
index_col=["identifier"],
)
check = df.join(df_blotter)
|
