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
|
import logging
import pandas as pd
from serenitas.utils.env import DAILY_DIR
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):
for msg in em.get_msgs(
path=["NYops", "Mercury"], count=20, subject__startswith="Positions-"
):
if msg.sender == "mercury-reports@baml.com":
date = msg.subject.split("-", 1)[1]
save_dir = DAILY_DIR / date / "Reports"
for attach in msg.attachments:
if attach.name.endswith("html"):
read_secure_message(msg, save_dir, logger)
continue
if attach.name.endswith("csv"):
p = save_dir / attach.name
p.parent.mkdir(exist_ok=True, parents=True)
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",
],
)
df = (
df[df["Asset Class"] == "CASH AND EQUIVALENTS"]
.groupby(["Local Currency", "Account Name", "Portfolio ID"])
.sum()
)
df["date"] = prev_business_day(workdate)
df = df.reset_index().rename(
{
"Portfolio ID": "account_number",
"Local Currency": "currency_code",
"Account Name": "account_name",
"Local Market Value": "balance",
},
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)
|
