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
|
from collateral.baml_isda import download_from_secure_id
from bs4 import BeautifulSoup
import logging
import pandas as pd
from serenitas.utils.env import DAILY_DIR
from urllib.parse import urlsplit, parse_qs, urlunsplit
from serenitas.utils.db import dawn_engine
import datetime
from serenitas.analytics.dates import prev_business_day
from collateral.baml_isda import read_secure_message
logger = logging.getLogger(__name__)
def download_messages(em):
# Need to fix path
for msg in em.get_msgs(path=["NYops", "Mercury"], subject__startswith="Positions-"):
if msg.sender == "mercury-reports@baml.com":
date = msg.subject.split("-", 1)[1]
save_dir = DAILY_DIR / date / "Reports"
if msg.body.body_type == "HTML":
read_secure_message(msg, save_dir, logger)
else:
for attach in msg.attachments:
if attach.name.endswith("csv"):
p = save_dir / attach.name
if not p.exists():
p.write_bytes(attach.content)
def load_cash_report(workdate: datetime.date):
path = DAILY_DIR / str(workdate) / "Reports" / f"Positions-{workdate}.csv"
df = pd.read_csv(
path,
index_col=False,
usecols=[
"Asset Class",
"Local Market Value",
"Local Currency",
"Account Name",
"Portfolio ID",
"Information As Of",
],
)
df = (
df[df["Asset Class"] == "CASH AND EQUIVALENTS"]
.groupby(
["Local Currency", "Account Name", "Portfolio ID", "Information As Of"]
)
.sum()
)
df = df.reset_index().rename(
{
"Portfolio ID": "account_number",
"Local Currency": "currency_code",
"Account Name": "account_name",
"Local Market Value": "balance",
"Information As Of": "date",
},
axis=1,
)
df["date"] = pd.to_datetime(df["date"])
df["fund"] = "SERCGMAST"
df.to_sql("cash_balances", dawn_engine, if_exists="append", index=False)
if __name__ == "__main__":
import argparse
from serenitas.utils.exchange import ExchangeMessage
parser = argparse.ArgumentParser()
parser.add_argument(
"workdate",
nargs="?",
type=datetime.date.fromisoformat,
default=datetime.date.today(),
help="working date",
)
args = parser.parse_args()
em = ExchangeMessage()
download_messages(em)
load_cash_report(args.workdate)
|
