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
|
from dataclasses import dataclass
import datetime
from serenitas.ops.trade_dataclasses import Deal, Ccy
from typing import ClassVar
from .custodians import NT, BNY
from .misc import get_dir, dt_from_fname
from dataclasses import field
from csv import DictReader
from functools import partial
_nt_to_currency = {"EURO - EUR": "EUR", "U.S. DOLLARS - USD": "USD"}
@dataclass
class Wire(Deal, table_name="custodian_wires", deal_type="custodian_wires"):
date: datetime.date
fund: ClassVar[str]
entry_date: datetime.date
value_date: datetime.date
pay_date: datetime.date
currency: Ccy
amount: float
wire_details: str
unique_ref: str
dtkey: ClassVar = field(metadata={"insert": False, "select": False})
def __init_subclass__(cls, fund, dtkey, **kwargs):
cls._sql_insert = (
cls._sql_insert.removesuffix("RETURNING *")
+ "ON CONFLICT (unique_ref) DO NOTHING RETURNING *"
)
cls.fund = fund
cls.dtkey = dtkey
def __post_init__(self):
self.amount = self.amount.replace(",", "")
if "(" in self.amount:
self.amount = -float(self.amount[1:-1])
else:
self.amount = float(self.amount)
@classmethod
def get_newest_report(cls, fname, date):
cls.download_reports(date)
p = max(
[f for f in get_dir(date).iterdir() if fname in f.name],
key=partial(dt_from_fname, dt_format=cls.dtkey),
default=None,
)
if not p:
raise ValueError(f"No reports for {cls.fund} on {date}")
return p
@classmethod
def dtkey_fun(cls):
def dtkey_fun(f):
return datetime.datetime.strptime(
f.name.removesuffix(".csv").removesuffix(".xlsx").rsplit("_")[-1],
cls.dtkey,
)
return dtkey_fun
class BowdstWire(Wire, BNY, fund="BOWDST", dtkey="%Y%m%d%H%M%S"):
@classmethod
def from_report_line(cls, line: dict):
return cls(
date=line["Report Run Date"],
entry_date=line["Cash Entry Date"],
value_date=line["Cash Value Date"],
pay_date=line["Settle / Pay Date"],
currency=line["Local Currency Code"],
amount=line["Local Amount"],
wire_details=line["Transaction Description 1"]
if line["Transaction Type Code"] == "CW"
else line["Transaction Description 2"],
unique_ref=line["Reference Number"],
)
@classmethod
def to_db(cls, date):
p = cls.get_newest_report("BowdstWires", date)
with open(p) as fh:
reader = DictReader(fh)
for line in reader:
cls.from_report_line(line).stage()
cls.commit()
class NTWire(Wire, NT, fund="ISOSEL", dtkey="%Y%m%d%H%M"):
@classmethod
def from_report_line(cls, line: dict):
return cls(
date=line["Through date"],
entry_date=line["D-GL-POST"],
value_date=line["D-TRAN-EFF"],
pay_date=line["D-TRAN-EFF"],
currency=_nt_to_currency[line["N-GL-AC30"]],
amount=line["Net amount - local"],
wire_details=line["narrative"],
unique_ref=line["C-EXTL-SYS-TRN-DSC-3"],
)
@classmethod
def to_db(cls, date):
p = cls.get_newest_report("custodian_wires", date)
with open(p) as fh:
reader = DictReader(fh)
for line in reader:
if "sponsor" in line["narrative"].lower():
cls.from_report_line(line).stage()
cls.commit()
|
