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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
|
import datetime
import re
from typing import ClassVar
from dataclasses import dataclass
import pandas as pd
from pandas.errors import EmptyDataError
from serenitas.ops.dataclass_mapping import Fund
from serenitas.analytics.dates import prev_business_day
from serenitas.analytics.exceptions import MissingDataError
from serenitas.utils.env import DAILY_DIR
from .misc import get_dir, Custodian
from .base import Report
@dataclass
class WireReport(
Report,
table_name="custodian_wires",
columns=(
"date",
"fund",
"custodian",
"entry_date",
"pay_date",
"value_date",
"currency",
"amount",
"wire_details",
"unique_ref",
),
):
date: datetime.date
fund: Fund
custodian: ClassVar[Custodian]
_registry: ClassVar[dict] = {}
def __init_subclass__(cls, custodian, **kwargs):
cls.custodian = custodian
cls._registry[custodian] = cls
def get_report(self):
report_dir = get_dir(self.date)
pattern = f"{self.custodian}_WIRE_{self.fund}_"
reports = [
f
for f in report_dir.iterdir()
if f.name.startswith(pattern) and self.get_ts(f.name).date() == self.date
]
p = max(
reports,
key=lambda f: self.get_ts(f.name),
default=None,
)
if p:
return p
else:
raise MissingDataError(
f"Report not ready {self.date}: {self.custodian} {self.fund}"
)
@staticmethod
def get_ts(s):
m = re.search(r"\d{12}", s)
return datetime.datetime.strptime(m[0], "%Y%m%d%H%M")
class BNYWireReport(WireReport, custodian="BNY", dtkey="%Y%m%d%H%M%S"):
def __iter__(self):
try:
df = pd.read_csv(self.get_report(), thousands=",")
except EmptyDataError as exc:
raise EmptyDataError(
f"File received no wires for {self.fund}: {self.custodian} {self.date}"
) from exc
df["Local Amount"] = df["Local Amount"].apply(
lambda s: "-" + s[1:-1] if s.startswith("(") else s
)
df["Local Amount"] = pd.to_numeric(
df["Local Amount"].str.replace(",", "")
) # Not sure how to avoid this with the thousands
df["Wire Details"] = df.apply(
lambda x: x["Transaction Description 1"]
if x["Transaction Type Code"] == "CW"
else x["Transaction Description 2"],
axis=1,
)
return (
(self.date, self.fund, self.custodian, *t)
for t in df[
[
"Cash Entry Date",
"Settle / Pay Date",
"Cash Value Date",
"Local Currency Code",
"Local Amount",
"Wire Details",
"Reference Number",
]
].itertuples(index=False)
)
@staticmethod
def get_ts(s):
m = re.search(r"\d{12}", s)
return datetime.datetime.strptime(m[0], "%Y%m%d%H%M%S")
class NTWireReport(WireReport, custodian="NT"):
def __iter__(self):
df = pd.read_csv(self.get_report())
_ccy_mapping = {"EURO - EUR": "EUR", "U.S. DOLLARS - USD": "USD"}
df["Currency"] = df["N-GL-AC30"].apply(lambda x: _ccy_mapping[x])
return (
(self.date, self.fund, self.custodian, *t)
for t in df[
[
"D-GL-POST",
"D-TRAN-EFF",
"D-TRAN-EFF",
"Currency",
"Net amount - local",
"narrative",
"C-EXTL-SYS-TRN-DSC-3",
]
].itertuples(index=False)
if "sponsor" in t.narrative.lower()
)
class UMBWireReport(WireReport, custodian="UMB"):
# UMB has no unique identifier. We just delete and add back
def __post_init__(self):
with self._conn.cursor() as c:
c.execute(
"DELETE FROM custodian_wires WHERE date=%s AND fund=%s AND custodian=%s",
(
self.date,
self.fund,
self.custodian,
),
)
self._conn.commit()
def __iter__(self):
df = pd.read_excel(self.get_report(), skiprows=3)
df["index"] = df.index
df["Unique Ref"] = df.apply(
lambda x: f'{x["Transaction Date"]}-{x["index"]}', axis=1
)
if not df.iloc[0]["Transaction Date"].startswith("No records"):
return (
(
self.date,
self.fund,
self.custodian,
*t,
)
for t in df[
[
"Transaction Date",
"Transaction Date",
"Transaction Date",
"Local Currency Code",
"Net Amount",
"Transaction Description",
"Unique Ref",
]
].itertuples(index=False)
)
else:
raise EmptyDataError(
f"File received no wires for {self.fund}: {self.custodian} {self.date}"
)
class SCOTIAWireReport(WireReport, custodian="SCOTIA"):
def __iter__(self):
df = pd.read_excel(self.get_report(), skipfooter=2)
df["Amount"] = df.apply(
lambda x: x["Cr Amount"] if x["Dr/Cr"] == "Cr" else -x["Dr Amount"], axis=1
)
return (
(self.date, self.fund, self.custodian, *t)
for t in df[
[
"Posting Date",
"Value Date",
"Value Date",
"Curr.",
"Amount",
"Reference Data",
"Bank Ref.",
]
].itertuples(index=False)
)
def get_report(self):
REPORT_DIR = DAILY_DIR / "Selene" / "Scotia_reports"
return next(
REPORT_DIR.glob(
f"IsoSelene_{prev_business_day(self.date):%d-%b-%Y}_*_xlsx.JOAAPKO3.JOAAPKO1"
)
)
|
