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
|
import datetime
from io import BytesIO
from serenitas.utils.env import DAILY_DIR
from serenitas.ops.citco import GIL, GTL
from serenitas.utils.remote import Client
from serenitas.utils.db import dbconn
from serenitas.analytics.dates import prev_business_day, next_business_day
from report_ops.sma import build_position_file
from report_ops.utils import SettlementMonitor
settlement_sum_query = "SELECT currency, sum(payment_amount) as payment_amount FROM payment_settlements ps2 WHERE fund=%s AND asset_class in ('BOND', 'SPOT') AND settle_date BETWEEN %s AND %s group by currency;"
_account_fund = {"ISOS01": "ISOSEL"}
def concat_csv(file_type, date):
match file_type:
case "trade":
file_tag = "innocap_serenitas_trades"
case "instrument":
file_tag = "i.innocap_serenitas"
fname = f"Innocap_ISOSEL_{file_type}_{date:%Y-%m-%d}.csv"
buf = BytesIO()
buf.write((",".join(GTL if file_type == "trade" else GIL) + "\n").encode())
for f in (DAILY_DIR / str(date)).iterdir():
if f.is_file() and f.name.startswith(file_tag):
with f.open("rb") as fh:
next(fh) # Don't concat headers
buf.write(fh.read())
return buf.getvalue(), fname
def upload_citco_files(date, upload):
for file_type in ("trade", "instrument"):
buf, fname = concat_csv(file_type, date)
dest = DAILY_DIR / str(date) / fname
dest.write_bytes(buf)
if upload:
innocap_sftp = Client.from_creds("innocap", folder="Innocap")
innocap_sftp.put(buf, fname)
def upload_position_files(date, fund, upload):
buf, dest = build_position_file(
date,
fund,
)
if upload:
client = Client.from_creds("innocap", folder="Innocap")
client.put(buf, dest.name)
client = Client.from_creds("citco")
client.put(buf, dest.name)
def get_cash_balance(date, account, conn):
with conn.cursor() as c:
sql_str = "SELECT balance, currency_code from cash_balances WHERE account_number=%s AND date=%s"
c.execute(sql_str, (account, date))
return {row.currency_code: row.balance for row in c}
def monitor_cash_balances(date, account, conn, upload):
cob = prev_business_day(date)
fund = _account_fund[account]
if cash_balances := get_cash_balance(cob, account, conn):
for projection_date in (date, next_business_day(date)):
with conn.cursor() as c:
c.execute(settlement_sum_query, (fund, date, projection_date))
for row in c:
projection_balance = (
cash_balances[row.currency] + row.payment_amount
)
if projection_balance < 0:
SettlementMonitor.stage(
{
"date": projection_date,
"account": account,
"currency": row.currency,
"projected_balance": projection_balance,
}
)
SettlementMonitor.email(fund)
SettlementMonitor._staging_queue.clear()
else:
raise ValueError(f"No cash balances parsed for account {account}: {cob}")
if __name__ == "__main__":
import argparse
parser = argparse.ArgumentParser()
parser.add_argument(
"workdate",
nargs="?",
type=datetime.date.fromisoformat,
default=datetime.date.today(),
help="file transfer date",
)
parser.add_argument(
"--no-upload",
"-n",
action="store_true",
default=False,
help="uploads to citco and innocap",
)
args = parser.parse_args()
cob = prev_business_day(args.workdate)
conn = dbconn("dawndb")
upload_citco_files(cob, not args.no_upload)
upload_position_files(cob, "ISOSEL", not args.no_upload)
monitor_cash_balances(args.workdate, "ISOS01", conn, not args.no_upload)
|
