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
|
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
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"
soup = BeautifulSoup(msg.body, features="lxml")
a = soup.find("a")
url = urlsplit(a["href"])
query = parse_qs(url.query)
base_url = urlunsplit(url[:2] + ("",) * 3)
try:
download_from_secure_id(
query["id"][0], query["brand"][0], save_dir, base_url
)
except ValueError as e:
logger.error(e)
continue
def load_cash_report(workdate: datetime.date):
path = DAILY_DIR / str(workdate) / "Reports" / f"Positions-{workdate}.csv"
df = pd.read_csv(
path,
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 = df.reset_index().rename(
{
"Portfolio ID": "account_number",
"Local Currency": "currency_code",
"Account Name": "account_name",
"Local Market Value": "balance",
},
axis=1,
)
df["fund"] = "SERCGMAST"
df["date"] = workdate
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)
|
