diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/citco_ops/utils.py | 54 | ||||
| -rw-r--r-- | python/innocap_bond_setts.py | 29 |
2 files changed, 70 insertions, 13 deletions
diff --git a/python/citco_ops/utils.py b/python/citco_ops/utils.py index 450b2204..0c42306b 100644 --- a/python/citco_ops/utils.py +++ b/python/citco_ops/utils.py @@ -10,10 +10,19 @@ from psycopg.errors import UniqueViolation from exchangelib import HTMLBody from tabulate import tabulate from functools import lru_cache +from serenitas.analytics.dates import next_business_day +from decimal import Decimal +import math logger = logging.getLogger(__name__) +def next_business_days(date, offset): + for i in range(offset): + date = next_business_day(date) + return date + + def get_file_status(s): is_processed, fname_short = s.rsplit("_", 1) is_processed = is_processed.rsplit("-")[1] == "PROCESSED" @@ -58,6 +67,11 @@ def instrument_table(instrument_id): return "citco_trs" +def round_up(n, decimals=0): + multiplier = 10**decimals + return math.ceil(n * multiplier) / multiplier + + @dataclass class CitcoSubmission(Deal, deal_type=None, table_name="citco_submission"): fname: str = field() @@ -218,19 +232,47 @@ class Payment: class PaymentSettlement(Payment): @classmethod - def email_innocap(cls, date): + def email_innocap(cls, date, account_balance): if not cls._insert_queue: return + cls.subtract_cash_balance(account_balance) + move_cash = "" + for currency in ("USD", "EUR"): + biggest_deficit = min( + list( + map( + lambda x: int(x.amount) if x.currency == currency else 0, + cls._insert_queue, + ) + ) + ) + if biggest_deficit < 0: + move_cash += f"\n\n***Please move ${round_up(abs(biggest_deficit), -6):,.2f} {currency} to Northern Trust from Scotia and confirm when done.***" em = ExchangeMessage() em.send_email( - f"Payment Settlements Bond/FX NT: ISOSEL {date}", - "Good morning, \n\nWe have the following amounts settling in the next 4 calendar days at Northern Trust: (Positive Amounts = Receive, Negative Amounts=Pay)\n\n" + f"{'*ACTION REQUESTED* ' if move_cash else ''}Payment Settlements Bond/FX NT: ISOSEL {date}", + "Good morning, \n\nProjected Balances at Northern Trust: (Positive Amounts = Positive Balance, Negative Amounts = Negative Balance)\n\n" + "\n".join( settlement.to_email_format() for settlement in cls._insert_queue - ), - to_recipients=_recipients["ISOSEL"], - cc_recipients=("Selene-Ops@lmcg.com",), + ) + + move_cash, + to_recipients=("fyu@lmcg.com",), + # cc_recipients=("Selene-Ops@lmcg.com",), ) + cls._insert_queue.clear() + + @classmethod + def stage_payment(cls, settlements, date): + for row in settlements: + cls._insert_queue.append(cls(date, row.currency, row.payment_amount)) + + @classmethod + def subtract_cash_balance(cls, account_balance): + overdraft = [] + for settlement in cls._insert_queue: + settlement.amount = Decimal(account_balance[settlement.currency]) - ( + -settlement.amount + ) class GFSMonitor(Payment): diff --git a/python/innocap_bond_setts.py b/python/innocap_bond_setts.py index 37101b80..8fd10db2 100644 --- a/python/innocap_bond_setts.py +++ b/python/innocap_bond_setts.py @@ -1,6 +1,15 @@ import datetime from serenitas.utils.db import dbconn -from citco_ops.utils import PaymentSettlement +from citco_ops.utils import PaymentSettlement, next_business_days +from serenitas.analytics.dates import prev_business_day + + +def get_cash_balance(account_number, date, 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_number, date)) + return {row.currency_code: row.balance for row in c} + if __name__ == "__main__": import argparse @@ -15,11 +24,17 @@ if __name__ == "__main__": ) args = parser.parse_args() conn = dbconn("dawndb") - sql_str = "SELECT settle_date, 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 settle_date, currency;" + sql_str = "SELECT settle_date, 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 AND currency=%s group by settle_date, currency;" + cash_balances = get_cash_balance("ISOS01", prev_business_day(args.date), conn) with conn.cursor() as c: for fund in ("ISOSEL",): - c.execute( - sql_str, (fund, args.date, args.date + datetime.timedelta(days=4)) - ) - PaymentSettlement.stage_payment(c) - PaymentSettlement.email_innocap(args.date) + for date in ( + args.date, + next_business_days(args.date, 1), + next_business_days(args.date, 2), + ): + for currency in ("USD", "EUR"): + params = (fund, args.date, date, currency) + c.execute(sql_str, params) + PaymentSettlement.stage_payment(c, date) + PaymentSettlement.email_innocap(args.date, cash_balances) |