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
121
122
123
124
125
126
127
128
129
|
from dataclasses import field, dataclass
from serenitas.ops.trade_dataclasses import Deal, Fund
from serenitas.analytics.dates import prev_business_day, next_business_day
import datetime
from serenitas.utils.exchange import ExchangeMessage
from serenitas.utils.env import DAILY_DIR
import pandas as pd
from serenitas.utils.db import dbconn, dawn_engine
from typing import ClassVar
from .misc import get_dir, dt_from_fname
from .custodians import NT, UMB
@dataclass
class IAMDeal(Deal, deal_type=None, table_name="iam_tickets"):
trade_date: datetime.date = field(metadata={"globeop": "SettlementDate"})
action: str = field(metadata={"globeop": "Action"})
strategy: str = field(metadata={"globeop": "Folder"})
counterparty: str = field(metadata={"globeop": "Counterparty"})
maturity: datetime.date
start_money: float = field(metadata={"globeop": "StartMoney"})
currency: str = field(metadata={"globeop": "Currency"})
booked_offset: bool
uploaded: bool
fund: Fund
dealid: str = field(metadata={"insert": False})
id: int = field(metadata={"insert": False})
def to_globeop(self, action):
obj = super().to_globeop(action)
obj["Deal Type"] = "IamDeal"
obj["ExpirationDate"] = self.trade_date if self.action == "UPDATE" else None
obj["CallNoticeIndicator"] = "24H" if self.action == "NEW" else None
obj["TransactionIndicator"] = ("DEPOSIT" if obj["StartMoney"] > 0 else "LOAN",)
obj["StartMoney"] = abs(obj["StartMoney"])
obj["Folder"] = (
"M_CSH_CASH" if obj["strategy"] == "CSH_CASH" else obj["strategy"]
)
obj["DealFunction"] = "OTC"
obj["MarginType"] = "Variation"
obj["Basis"] = "ACT/360"
return obj
@dataclass
class CashReport:
fund: ClassVar[str]
account_number: ClassVar[str]
date: datetime.date
_conn: ClassVar[dbconn] = dbconn("dawndb")
_staging_queue: ClassVar[set] = set()
_insert_sql = "INSERT INTO cash_balances VALUES (%s, %s, %s, %s, %s, %s) ON CONFLICT DO NOTHING"
def __init_subclass__(cls, fund, account_number):
cls.fund = fund
cls.account_number = account_number
def to_db(self, report_name):
self.download_reports(self.date)
report_dir = get_dir(self.date)
report_dir.mkdir(exist_ok=True, parents=True)
p = max(
[f for f in get_dir(self.date).iterdir() if f.name.startswith(report_name)],
key=dt_from_fname,
default=None,
)
if not p:
raise ValueError(
f"No reports found for fund: {self.fund} date: {self.date}"
)
return p
@classmethod
def commit(cls):
with cls._conn.cursor() as c:
c.executemany(cls._insert_sql, cls._staging_queue)
cls._conn.commit()
class NTCashReport(CashReport, NT, fund="ISOSEL", account_number="ISOS01"):
def to_db(self):
p = super().to_db("cash_")
df = pd.read_csv(p, on_bad_lines="warn")
df = df[df["T-NARR-LONG"] == "CLOSING BALANCE"]
df = df[["Consolidation", "Currency code", "A-TRAN-AMT"]]
df.columns = df.columns.str.replace(" |-|_", "", regex=True).str.lower()
df = df.set_index(["consolidation", "currencycode"])
for row in df.itertuples():
self.stage_from_row(row)
self.commit()
self._staging_queue.clear()
def stage_from_row(self, row):
(account, currency), amount = row
self._staging_queue.add(
(
prev_business_day(self.date),
self.fund,
f"NT Custody Account {self.fund}",
account,
currency,
amount,
)
)
class UMBCashReport(CashReport, UMB, fund="SERCGMAST", account_number="159260.1"):
def to_db(self):
p = super().to_db("umb_")
df = pd.read_excel(p, skiprows=3)
for row in (
df.groupby(["Portfolio #", "Currency"]).sum()["Current Balance"].items()
):
self.stage_from_row(row)
self.commit()
self._staging_queue.clear()
def stage_from_row(self, row):
(account, currency), amount = row
self._staging_queue.add(
(
prev_business_day(self.date),
self.fund,
f"UMB Custody Account {self.fund}",
account,
currency,
amount,
)
)
|
