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
|
from dataclasses import field, dataclass
from serenitas.ops.trade_dataclasses import Deal, Fund
from serenitas.analytics.dates import prev_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
@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
def download_report(self):
em = ExchangeMessage()
for msg in em.get_msgs(path=["SeleneOps", "Passport"]):
for attach in msg.attachments:
message_date = attach.last_modified_time.date()
if (
attach.name == "Attachment1.pgp"
and prev_business_day(message_date) == self.date
):
dest = (
DAILY_DIR
/ str(message_date)
/ "Reports"
/ f"{message_date:%Y%m%d}_ISOSEL.csv"
)
dest.parent.mkdir(exist_ok=True, parents=True)
with attach.fp as fp, dest.open("w") as csvfh:
plaintext, result, verify_result = gpg.Context().decrypt(
fp.read(), passphrase="Serenitas1", sink=csvfh
)
return dest
raise ValueError(f"No reports found for fund: {self.fund} date: {self.date}")
def to_db(self):
df = pd.read_csv(self.download_report(), on_bad_lines="warn", sep="\t")
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)
class IsoselCashReport(CashReport, fund="ISOSEL", account_number="ISOS01"):
pass
|
