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
|
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
import gpg
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
def dt_from_fname(f):
return datetime.datetime.strptime(
f.name.removesuffix(".csv").rsplit("_")[-1], "%Y%m%d%H%M"
)
@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")
def __init_subclass__(cls, fund, account_number):
cls.fund = fund
cls.account_number = account_number
class IsoselCashReport(CashReport, fund="ISOSEL", account_number="ISOS01"):
@classmethod
def download_reports(cls, date=datetime.date.today()):
em = ExchangeMessage()
for msg in em.get_msgs(path=["SeleneOps", "Passport"]):
for attach in msg.attachments:
message_time = attach.last_modified_time
if attach.name == "Attachment1.pgp" and message_time.date() == date:
dest = get_dir(message_time.date(), archived=False)
dest.mkdir(exist_ok=True, parents=True)
with attach.fp as fp:
plaintext, result, verify_result = gpg.Context().decrypt(
fp.read(), passphrase="Serenitas1"
)
fname = (
"custodian_wires"
if "custodian" in verify_result.file_name
else "cash"
)
dest = dest / f"{fname}_{message_time:%Y%m%d%H%M}.csv"
with open(dest, "w") as csvFile:
text = plaintext.decode("utf-8").replace("\t", ",")
csvFile.write(text)
def to_db(self):
self.download_reports(self.date)
p = max(
[
f
for f in get_dir(next_business_day(self.date)).iterdir()
if "cash" in f.name
],
key=dt_from_fname,
)
if not p:
raise ValueError(
f"No reports found for fund: {self.fund} date: {self.date}"
)
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", "Account name"]]
df = df.reset_index(drop=True).rename(
{
"Consolidation": "account_number",
"Currency code": "currency_code",
"Account name": "account_name",
"A-TRAN-AMT": "balance",
},
axis=1,
)
df["date"] = self.date
df["fund"] = self.fund
with self._conn.cursor() as c:
c.execute(
"DELETE FROM cash_balances WHERE fund=%s AND date=%s AND account_number=%s",
(
self.fund,
self.date,
self.account_number,
),
)
self._conn.commit()
df.to_sql("cash_balances", index=False, if_exists="append", con=dawn_engine)
|
