diff options
| -rw-r--r-- | python/globeop_monitor.py | 42 | ||||
| -rw-r--r-- | python/lmcg_monitor.py | 50 | ||||
| -rw-r--r-- | python/position_file_bowdst.py | 10 | ||||
| -rw-r--r-- | python/report_ops/__main__.py | 17 | ||||
| -rw-r--r-- | python/report_ops/misc.py | 10 | ||||
| -rw-r--r-- | python/report_ops/utils.py | 38 | ||||
| -rw-r--r-- | python/report_ops/wires.py | 39 | ||||
| -rw-r--r-- | python/strat_cash_realloc.py | 57 | ||||
| -rw-r--r-- | sql/dawn.sql | 11 |
9 files changed, 199 insertions, 75 deletions
diff --git a/python/globeop_monitor.py b/python/globeop_monitor.py index cdd0dbb2..ae7fea86 100644 --- a/python/globeop_monitor.py +++ b/python/globeop_monitor.py @@ -1,8 +1,12 @@ import datetime import logging +from dataclasses import replace from serenitas.analytics.dates import prev_business_day from serenitas.utils.db import dbconn +from serenitas.ops.trade_dataclasses import WireDeal +from serenitas.ops.funds import Service from report_ops.utils import GFSMonitor, check_cleared_cds +from collateral.common import CASH_STRATEGY_MAPPING, STRATEGY_CASH_MAPPING def check_gfs(date, fund, conn): @@ -20,6 +24,42 @@ def check_gfs(date, fund, conn): GFSMonitor._staging_queue.clear() +def reallocate_strategy_cash(date, fund, conn): + service = Service[fund] + with conn.cursor() as c: + c.execute( + "SELECT 1 FROM wires WHERE trade_date=%s AND fund=%s AND author='auto'", + ( + date, + fund, + ), + ) + if c.fetchone(): + return + c.execute( + "SELECT * FROM list_orphaned_cash(%s, %s) WHERE abs(amount) > 1", + ( + date, + fund, + ), + ) + for row in c: + if row.folder not in CASH_STRATEGY_MAPPING: + obj = WireDeal(**row._asdict()) + offset = replace( + obj, folder=STRATEGY_CASH_MAPPING[obj.folder], amount=-obj.amount + ) + obj.stage() + offset.stage() + if not WireDeal._insert_queue: + return + for wire in WireDeal.commit(returning=True): + service.push_trade(wire, "NEW") + buf, dest = service.build_buffer("wire") + service.upload(buf, dest.name) + service().clear() + + if __name__ == "__main__": import argparse @@ -40,3 +80,5 @@ if __name__ == "__main__": ): check_gfs(args.cob, fund, conn) check_cleared_cds(args.cob, fund, conn) + for fund in ("BOWDST",): + reallocate_strategy_cash(args.cob, fund, conn) diff --git a/python/lmcg_monitor.py b/python/lmcg_monitor.py new file mode 100644 index 00000000..5f8230cd --- /dev/null +++ b/python/lmcg_monitor.py @@ -0,0 +1,50 @@ +import datetime +import argparse +from serenitas.analytics.dates import prev_business_day +from serenitas.utils.db import dbconn +from serenitas.utils.exchange import ExchangeMessage +from report_ops.utils import CDXNotionalMonitor + + +def monitor_scotia(date, conn): + with conn.cursor() as c: + c.execute( + "SELECT 1 FROM cash_balances cb WHERE fund='ISOSEL' AND account_number = '476960681512' AND date=%s;" + ) + if not (_ := c.fetchone()): + em = ExchangeMessage() + em.send_email( + f"*Action Requested* Scotia Balance Missing {date}", + "Please enter missing cash balance to the blotter.", + to_recipients=("Nyops@lmcg.com",), + ) + + +def monitor_cds_notional(fund, date, conn): + with conn.cursor() as c: + c.execute( + "SELECT *, notional as serenitas_notional, globeop_notional / factor as admin_notional FROM list_cds_marks(%s, NULL, %s) WHERE abs((notional * factor) - globeop_notional) > 100;", + (date, fund), + ) + for row in c: + d = row._asdict() + d["difference"] = d["serenitas_notional"] - d["globeop_notional"] + CDXNotionalMonitor.stage(d) + CDXNotionalMonitor.email(fund) + CDXNotionalMonitor._staging_queue.clear() + + +if __name__ == "__main__": + parser = argparse.ArgumentParser() + parser.add_argument( + "cob", + nargs="?", + type=datetime.date.fromisoformat, + default=prev_business_day(datetime.date.today()), + help="working date", + ) + args = parser.parse_args() + conn = dbconn("dawndb") + monitor_scotia(args.cob, conn) + for fund in ("SERCGMAST", "BOWDST", "ISOSEL"): + monitor_cds_notional(fund, args.cob, conn) diff --git a/python/position_file_bowdst.py b/python/position_file_bowdst.py index 6b34b8b4..00b30e5c 100644 --- a/python/position_file_bowdst.py +++ b/python/position_file_bowdst.py @@ -2,20 +2,20 @@ from report_ops.sma import build_position_file import argparse from serenitas.utils.remote import Client from serenitas.utils.exchange import ExchangeMessage, FileAttachment -from report_ops.misc import _recipients, _cc_recipients +from report_ops.misc import _monthend_nav_recipients, _cc_recipients import datetime from serenitas.analytics.dates import prev_business_day asset_splits = { - "OTC": [ + "OTC": ( "future", "tranche", "ir_swaption", "cdx_swaption", "irs", "cdx", - ], - "BOND": ["bond"], + ), + "BOND": ("bond",), } @@ -40,7 +40,7 @@ def main(cob, fund, upload): em.send_email( subject=f"Position_files for Bowdoin Street as of {cob}", body=f"Please see monthend positions for Bowdoin Street as of {cob}. They have been uploaded to the SFTP as well.", - to_recipients=_recipients[fund], + to_recipients=_monthend_nav_recipients[fund], cc_recipients=_cc_recipients[fund], reply_to=_cc_recipients[fund], attach=attachments, diff --git a/python/report_ops/__main__.py b/python/report_ops/__main__.py index a5172a24..856fe928 100644 --- a/python/report_ops/__main__.py +++ b/python/report_ops/__main__.py @@ -97,11 +97,18 @@ if args.isosel_reports: logger.warning(e) if args.wire_reports: - for fund in ("BOWDST", "ISOSEL"): - try: - Wire[fund].to_db(args.date) - except ValueError as e: - logger.warning(e) + for fund, custodians in _fund_custodians.items(): + for custodian in custodians: + report = Wire[ + ( + fund, + custodian, + ) + ] + try: + report.to_db(args.date) + except ValueError as e: + logger.warning(e) if args.send_to_custodians: for account in ( diff --git a/python/report_ops/misc.py b/python/report_ops/misc.py index 3492baf1..684ea6dd 100644 --- a/python/report_ops/misc.py +++ b/python/report_ops/misc.py @@ -43,6 +43,16 @@ _sma_recipients = { "BRINKER": ("CSG.Team.Armata@bbh.com",), } +_monthend_nav_recipients = { + "BOWDST": _recipients["BOWDST"] + + ( + "Hedgemark.FA@sscinc.com", + "HM-acctgprim@bnymellon.com", + "catherine.porter@bnymellon.com", + "Meghana.shroff@bnymellon.com", + ), +} + _settlement_recipients = { "BOWDST": _sma_recipients["BOWDST"], "SERCGMAST": ( diff --git a/python/report_ops/utils.py b/python/report_ops/utils.py index 80bc72d5..035b2e13 100644 --- a/python/report_ops/utils.py +++ b/python/report_ops/utils.py @@ -102,7 +102,7 @@ def check_cleared_cds(date, fund, conn): _tolerance = {"IG": 0.10, "HY": 0.20, "EU": 0.20, "XO": 0.30} with conn.cursor() as c: c.execute( - "SELECT *, abs(price-globeop_price) AS difference FROM list_cds_marks(%s, NULL, %s)", + "SELECT *, abs(price-globeop_price) AS difference FROM list_cds_marks(%s, NULL, %s) WHERE abs((notional * factor) - globeop_notional) < 100;", (date, fund), ) for row in c: @@ -350,6 +350,42 @@ class CDXQuoteMonitor( ) +class CDXNotionalMonitor( + Monitor, + headers=( + "security_desc", + "security_id", + "maturity", + "admin_notional", + "serenitas_notional", + "difference", + ), + num_format=[("{0:,.2f}", 3), ("{0:,.2f}", 4), ("{0:,.2f}", 5)], +): + @classmethod + def email(cls, fund): + if not cls._staging_queue: + return + cls._em.send_email( + f"CDX Notional Mismatches: {fund}", + HTMLBody( + f""" +<html> + <head> + <style> + table, th, td {{ border: 1px solid black; border-collapse: collapse;}} + th, td {{ padding: 5px; }} + </style> + </head> + <body> + Good morning,<br><br>Mismatched cleared cds notional mismatches below:<br><br>{cls.to_tabulate()} + </body> +</html>""" + ), + to_recipients=_cc_recipients[fund], + ) + + class SettlementMonitor( Monitor, headers=("date", "account", "currency", "projected_balance"), diff --git a/python/report_ops/wires.py b/python/report_ops/wires.py index 50f8284d..a2f5ce1d 100644 --- a/python/report_ops/wires.py +++ b/python/report_ops/wires.py @@ -1,8 +1,9 @@ from dataclasses import dataclass +from typing import Literal import datetime from serenitas.ops.trade_dataclasses import Deal, Ccy from typing import ClassVar -from .custodians import NT, BNY +from .custodians import NT, BNY, UMB from .misc import get_dir, dt_from_fname from dataclasses import field from csv import DictReader @@ -10,11 +11,14 @@ from functools import partial _nt_to_currency = {"EURO - EUR": "EUR", "U.S. DOLLARS - USD": "USD"} +CUSTODIAN = Literal["UMB", "NT", "BNY"] + @dataclass class Wire(Deal, table_name="custodian_wires", deal_type="custodian_wires"): date: datetime.date - fund: ClassVar[str] + fund: ClassVar[CUSTODIAN] + custodian: ClassVar[str] entry_date: datetime.date value_date: datetime.date pay_date: datetime.date @@ -24,13 +28,19 @@ class Wire(Deal, table_name="custodian_wires", deal_type="custodian_wires"): unique_ref: str dtkey: ClassVar = field(metadata={"insert": False, "select": False}) - def __init_subclass__(cls, fund, dtkey, **kwargs): + def __init_subclass__(cls, fund, custodian, dtkey, **kwargs): cls._sql_insert = ( cls._sql_insert.removesuffix("RETURNING *") + "ON CONFLICT (unique_ref) DO NOTHING RETURNING *" ) cls.fund = fund - cls._registry[fund] = cls + cls.custodian = custodian + cls._registry[ + ( + fund, + custodian, + ) + ] = cls cls.dtkey = dtkey def __post_init__(self): @@ -53,7 +63,7 @@ class Wire(Deal, table_name="custodian_wires", deal_type="custodian_wires"): return p -class BowdstWire(Wire, BNY, fund="BOWDST", dtkey="%Y%m%d%H%M%S"): +class BowdstBNYWire(Wire, BNY, fund="BOWDST", custodian="BNY", dtkey="%Y%m%d%H%M%S"): @classmethod def from_report_line(cls, line: dict): return cls( @@ -79,7 +89,7 @@ class BowdstWire(Wire, BNY, fund="BOWDST", dtkey="%Y%m%d%H%M%S"): cls.commit() -class SeleneWire(Wire, NT, fund="ISOSEL", dtkey="%Y%m%d%H%M"): +class SeleneNTWire(Wire, NT, fund="ISOSEL", custodian="NT", dtkey="%Y%m%d%H%M"): @classmethod def from_report_line(cls, line: dict): return cls( @@ -102,3 +112,20 @@ class SeleneWire(Wire, NT, fund="ISOSEL", dtkey="%Y%m%d%H%M"): if "sponsor" in line["narrative"].lower(): cls.from_report_line(line).stage() cls.commit() + + +class SerenitasUMBWire( + Wire, UMB, fund="SERCGMAST", custodian="UMB", dtkey="%Y%m%d%H%M" +): + @classmethod + def from_report_line(cls, line: dict): + return + + @classmethod + def to_db(cls, date): + # conn = cls._conn + # with conn.cursor() as c: + # c.execute("DELETE FROM custodian_wires WHERE date=%s AND fund=%s AND custodian=%s", (date, cls.fund, cls.custodian,)) + # conn.commit() + # the unique ref will be generated from the row + the date and reports will only be delivered at 7pm daily + pass diff --git a/python/strat_cash_realloc.py b/python/strat_cash_realloc.py deleted file mode 100644 index 0bfafce0..00000000 --- a/python/strat_cash_realloc.py +++ /dev/null @@ -1,57 +0,0 @@ -from dataclasses import replace -from serenitas.ops.funds import Bowdst -from serenitas.ops.trade_dataclasses import WireDeal -import datetime -from serenitas.analytics.dates import prev_business_day -from collateral.common import CASH_STRATEGY_MAPPING, STRATEGY_CASH_MAPPING - - -def generate_csv(date, fund="BOWDST"): - with WireDeal._conn.cursor() as c: - c.execute( - "SELECT 1 FROM wires WHERE trade_date=%s AND fund=%s AND author='auto'", - ( - date, - fund, - ), - ) - if c.fetchone(): - return - c.execute( - "SELECT * FROM list_orphaned_cash(%s, %s) WHERE abs(amount) > 1", - ( - date, - fund, - ), - ) - for row in c: - if row.folder not in CASH_STRATEGY_MAPPING: - obj = WireDeal(**row._asdict()) - offset = replace( - obj, folder=STRATEGY_CASH_MAPPING[obj.folder], amount=-obj.amount - ) - obj.stage() - offset.stage() - if not WireDeal._insert_queue: - return - for wire in WireDeal.commit(returning=True): - Bowdst.staging_queue.append(wire.to_globeop("NEW")) - buf, dest = Bowdst.build_buffer("wire") - Bowdst.upload(buf, dest.name) - Bowdst().clear() - - -if __name__ == "__main__": - import argparse - - parser = argparse.ArgumentParser() - parser.add_argument( - "workdate", - nargs="?", - type=datetime.date.fromisoformat, - default=prev_business_day(datetime.date.today()), - help="working date", - ) - args = parser.parse_args() - - generate_csv(args.workdate) diff --git a/sql/dawn.sql b/sql/dawn.sql index baaf386e..b80ac5d4 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3094,6 +3094,12 @@ AS SELECT trades.settle_date, FROM cds_trades WHERE cds_trades.orig_attach IS NOT NULL UNION + SELECT tranche_id::TEXT AS id, tc.date AS settle_date, + cds.fund, cds.cp_code, 'TRANCHE_PAYMENT' AS asset_class, + 'Bilateral Trade'::text AS account, tc.currency, + (COALESCE(tc.principal,0) + COALESCE(tc.accrued,0))::numeric(11,2) AS payment_amount + FROM tranche_cashflows tc LEFT JOIN cds ON tranche_id=cds.id + UNION SELECT swaptions.id::text AS id, swaptions.settle_date, swaptions.fund, @@ -3997,7 +4003,8 @@ CREATE TABLE custodian_wires( currency currency, amount float8 NOT NULL, wire_details text, - unique_ref text PRIMARY KEY); + unique_ref text PRIMARY KEY, + custodian custodian NOT NULL); @@ -4193,6 +4200,8 @@ FROM strategy_im si WHERE si.fund=p_fund AND si.date<=p_date ORDER BY date DESC) LEFT JOIN portfolio_folder_mapping pfm ON pfm.clean_folder=si.strategy::TEXT WHERE RANK=1 and abs(amount) >= .01; END $$ LANGUAGE plpgsql; +CREATE TYPE custodian AS ENUM('BNY', 'UMB', 'NT'); + CREATE TABLE iams ( id int4 NOT NULL GENERATED ALWAYS AS IDENTITY, |