aboutsummaryrefslogtreecommitdiffstats
path: root/python/report_ops
diff options
context:
space:
mode:
Diffstat (limited to 'python/report_ops')
-rw-r--r--python/report_ops/__main__.py23
-rw-r--r--python/report_ops/custodians.py77
-rw-r--r--python/report_ops/headers.py36
-rw-r--r--python/report_ops/misc.py12
-rw-r--r--python/report_ops/sma.py278
5 files changed, 389 insertions, 37 deletions
diff --git a/python/report_ops/__main__.py b/python/report_ops/__main__.py
index 68708109..194dd536 100644
--- a/python/report_ops/__main__.py
+++ b/python/report_ops/__main__.py
@@ -1,9 +1,19 @@
from serenitas.analytics.dates import prev_business_day
-from serenitas.utils.db import dbconn
+from serenitas.utils.exchange import ExchangeMessage
import logging
import argparse
import datetime
-from .sma import IsoselSMA, BowdstSMA
+from .sma import (
+ IsoselSMA,
+ BowdstSMA,
+ PositionReport,
+ BondPosition,
+ FuturePosition,
+ TranchePosition,
+ CDXPosition,
+ IRSwaptionPosition,
+ CDXSwaptionPosition,
+)
from .cash import NTCashReport, UMBCashReport, BNYCashReport
from .admin import AccruedReport, AllReport
from .wires import BowdstWire, NTWire
@@ -92,9 +102,12 @@ if args.wire_reports:
logger.info(e)
if args.send_to_custodians:
- conn = dbconn("dawndb")
- for account in ("UMB", "BBH"):
+ em = ExchangeMessage()
+ for account in (
+ "BBH",
+ "UMB",
+ ):
try:
- upload_to_custodian(account, args.date, conn, not args.no_upload)
+ upload_to_custodian(account, args.date, not args.no_upload, em)
except ValueError as e:
logger.info(e)
diff --git a/python/report_ops/custodians.py b/python/report_ops/custodians.py
index a82f2b15..e225bb4d 100644
--- a/python/report_ops/custodians.py
+++ b/python/report_ops/custodians.py
@@ -1,49 +1,72 @@
-from serenitas.utils.exchange import ExchangeMessage
+from serenitas.utils.exchange import ExchangeMessage, FileAttachment
from serenitas.utils.env import DAILY_DIR
import warnings
import datetime
-from .misc import get_dir
+from .misc import get_dir, _recipients, _cc_recipients
import gpg
from serenitas.ops.trade_dataclasses import BondDeal
from serenitas.ops.funds import Service
from typing import ClassVar
from dataclasses import dataclass
-_sql = (
- "INSERT INTO bond_csv_upload (allocationid, identifier, principal, interest) SELECT id, identifier, principal_payment, "
- "accrued_payment FROM bond_trades WHERE trade_date=%s AND account=%s AND tradeid IS NOT NULL ON CONFLICT DO NOTHING RETURNING allocationid;"
-)
-_bond_query = "SELECT * FROM bond_trades WHERE id in %s;"
-
-
-def upload_to_custodian(account, trade_date, conn, upload):
- _fund = {"BBH": "BRINKER", "UMB": "UMB"}
- custodian = Service[_fund[account]]
- with conn.cursor() as c:
+def upload_to_custodian(account, trade_date, upload, em):
+ _service = {"BBH": "BRINKER", "UMB": "UMB"}
+ custodian = Service[_service[account]]
+ conn = BondDeal._conn
+ with conn.cursor() as c, conn.cursor() as d:
c.execute(
- _sql,
+ "SELECT * FROM bond_trades WHERE trade_date=%s AND account=%s",
(
trade_date,
- "BAC" if account == "UMB" else account,
+ account,
),
)
- tids = tuple(row.allocationid for row in c)
- if not tids:
- return
- c.execute(_bond_query, (tids,))
for row in c:
- trade = BondDeal.from_dict(**row._asdict(), scaled=True)
- match account:
- case "BBH":
- custodian.staging_queue.append(trade.to_bbh("NEW"))
- case "UMB":
- custodian.staging_queue.append(trade.to_umb("NEW"))
+ d.execute(
+ "SELECT identifier, principal_payment, accrued_payment FROM bond_csv_upload WHERE allocationid=%s FOR UPDATE",
+ (row.id,),
+ )
+ if old_row := d.fetchone():
+ if any(
+ [
+ old_row.identifier != row.identifier,
+ abs(old_row.principal_payment - row.principal_payment) > 1e-2,
+ abs(old_row.accrued_payment, row.accrued_payment) > 1e-2,
+ ]
+ ):
+ old_trade = BondDeal.from_dict(
+ row._asdict() | old_row._asdict(), scaled=True
+ )
+ custodian.push_trade(old_trade, "CANCEL")
+ d.execute(
+ "UPDATE bond_csv_upload SET identifier=%s, principal_payment=%s, accrued_payment=%s WHERE allocationid=%s",
+ (
+ row.identifier,
+ row.principal_payment,
+ row.accrued_payment,
+ row.id,
+ ),
+ )
+ else:
+ continue
+ trade = BondDeal.from_dict(row._asdict(), scaled=True)
+ custodian.push_trade(trade, "NEW")
+ if not custodian.staging_queue:
+ return
buf, dest = custodian.build_buffer("bond")
custodian.staging_queue.clear()
conn.commit()
if upload:
custodian.upload(buf, dest.name, confirm=account != "UMB")
+ em.send_email(
+ f"{account}: Bond Positions Uploaded for {trade_date}",
+ "Hi, \nWe've just uploaded the positions via SFTP. File receipt attached to this email",
+ _recipients.get(account, _cc_recipients[custodian.name]),
+ cc_recipients=_cc_recipients[custodian.name],
+ reply_to=_cc_recipients[custodian.name],
+ attach=(FileAttachment(name=dest.name, content=buf),),
+ )
@dataclass
@@ -144,7 +167,3 @@ class BNY(Custodian, account="BONY2"):
p.parent.mkdir(parents=True, exist_ok=True)
if not p.exists():
p.write_bytes(attach.content)
-
-
-class BBH(Custodian, account="BBH"):
- pass
diff --git a/python/report_ops/headers.py b/python/report_ops/headers.py
new file mode 100644
index 00000000..b563e1d6
--- /dev/null
+++ b/python/report_ops/headers.py
@@ -0,0 +1,36 @@
+POSITION_HEADERS = [
+ "Client Name",
+ "Fund Name",
+ "Counterparty",
+ "AccountNumber",
+ "COB Date",
+ "SecurityDescription",
+ "Prime Broker",
+ "Product Type",
+ "Unique Deal ID",
+ "TransactionIndicator (Buy/Sell)",
+ "PutCall Indicator (Call/Put)",
+ "CapFloorIndicator",
+ "CurrencyPair",
+ "DealCurrencyA",
+ "DealCurrencyB",
+ "NotionalA",
+ "NotionalB",
+ "OriginalPrice",
+ "Strike",
+ "FixedRate",
+ "Quantity",
+ "Start Date",
+ "Effective Date",
+ "Maturity Date",
+ "Underlying Maturity",
+ "RecPayFixed",
+ "Underlying (ISIN / CUSP / RED CODES)",
+ "Underlying Desc",
+ "Exercise Type",
+ "MTM Currency",
+ "MTM Valuation",
+ "MarketPrice",
+ "COB Date",
+ "Clearing House Name",
+]
diff --git a/python/report_ops/misc.py b/python/report_ops/misc.py
index 6d435efe..da7d61ac 100644
--- a/python/report_ops/misc.py
+++ b/python/report_ops/misc.py
@@ -12,13 +12,20 @@ _recipients = {
"mbisoye@sscinc.com",
"hedgemark.lmcg.ops@sscinc.com",
"hm-operations@bnymellon.com",
+ "Hedgemark.OTC@sscinc.com",
),
"SERCGMAST": (
"SERENITAS.FA@sscinc.com",
"SERENITAS.ops@sscinc.com",
),
"BAML_FCM": ("footc_margin_csr_amrs@bofa.com",),
+ "GS_FCM": (
+ "Susan.Olesky@ny.email.gs.com",
+ "Divyanshi.Girotra@gs.com",
+ "gs-margin-calls-dcs@ny.email.gs.com",
+ ),
"NYOPS": ("nyops@lmcg.com",),
+ "UMB": ("lmcgcustody@umb.com",),
}
_sma_recipients = {
@@ -38,10 +45,13 @@ _cc_recipients = {
"ISOSEL": ("selene-ops@lmcg.com",),
"BOWDST": ("bowdoin-ops@lmcg.com",),
"SERCGMAST": ("nyops@lmcg.com",),
+ "BRINKER": ("nyops@lmcg.com",),
}
-def get_dir(workdate: datetime.date, archived=True) -> pathlib.Path:
+def get_dir(
+ workdate: datetime.date = datetime.date.today(), archived=True
+) -> pathlib.Path:
p = DAILY_DIR / str(workdate) / "Reports"
if not p.exists() and archived:
p = (
diff --git a/python/report_ops/sma.py b/python/report_ops/sma.py
index 9a3bf77a..83d400c5 100644
--- a/python/report_ops/sma.py
+++ b/python/report_ops/sma.py
@@ -1,12 +1,39 @@
import datetime
-from dataclasses import dataclass
+from dataclasses import dataclass, field
from serenitas.utils.db import dbconn
from serenitas.utils.exchange import ExchangeMessage
-from .misc import _sma_recipients, _cc_recipients
+from serenitas.utils.misc import rename_keys
+from serenitas.ops.trade_dataclasses import Deal
+from .misc import _sma_recipients, _cc_recipients, get_dir
from exchangelib import FileAttachment
import pandas as pd
from io import StringIO
from typing import ClassVar
+from .headers import POSITION_HEADERS
+from io import StringIO
+import csv
+from serenitas.utils.env import DAILY_DIR
+
+
+def build_position_file(
+ cob,
+ fund,
+ asset_classes: list = [
+ "bond",
+ "future",
+ "tranche",
+ "ir_swaption",
+ "cdx_swaption",
+ "irs",
+ "cdx",
+ ],
+):
+ for asset_class in asset_classes:
+ for position in PositionReport[asset_class].gen_positions(cob, fund):
+ PositionReport.staging_queue.append(position.to_position())
+ buf, dest = PositionReport.build_buffer(cob, fund)
+ PositionReport.staging_queue.clear()
+ return buf, dest
@dataclass
@@ -71,3 +98,250 @@ class IsoselSMA(SMA, fund="ISOSEL"):
class BowdstSMA(SMA, fund="BOWDST"):
pass
+
+
+_sql_query = {
+ "bond": "SELECT * FROM risk_positions(%s, null, %s) ",
+ "future": (
+ "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, account_code, dealid, buysell, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures "
+ "WHERE trade_date <= %s AND fund=%s) "
+ "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity, account_code, dealid, buysell FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';"
+ ),
+ "tranche": "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, trb.notional * trb.tranche_factor as active_notional, cds.* FROM tranche_risk_isosel trb left join cds on trade_id=id WHERE date=%s",
+ "cdx_swaption": "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, %s) spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;",
+ "ir_swaption": "SELECT abs(spr.notional) AS active_notional, spr.nav as serenitas_nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, %s) spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;",
+ "cdx": "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, %s) cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;",
+ "irs": "SELECT isr.pv, irs.*, accounts2.name FROM ir_swap_risk isr LEFT JOIN irs ON id=swp_id LEFT JOIN accounts2 USING (cash_account) WHERE date=%s AND irs.fund=%s;",
+}
+
+_fund_custodian = {"BOWDST": "BONY2", "ISOSEL": "NT"}
+_fund_client = {"BOWDST": "Hedgemark", "ISOSEL": "Innocap"}
+_fund_fcm = {"BOWDST": "GS_FCM", "ISOSEL": "BOA_FC"}
+
+
+def get_path(cob, fund):
+ match fund:
+ case "ISOSEL":
+ filepath_pattern = "Innocap_{fund}_positions_{cob:%Y-%m-%d}.csv"
+ case _:
+ filepath_pattern = "{fund}_positions_{cob:%Y%m%d}.csv"
+ return get_dir() / filepath_pattern.format(fund=fund, cob=cob)
+
+
+@dataclass
+class PositionReport(Deal, deal_type=None, table_name=None):
+ client_name: str = field(metadata={"position": "Client Name"})
+ fund: str = field(metadata={"position": "Fund Name"})
+ cp_code: str = field(metadata={"position": "Counterparty"})
+ dealid: str = field(metadata={"position": "Unique Deal ID"})
+ buysell: bool
+ currency: str = field(metadata={"position": "DealCurrencyA"})
+ notional: float = field(metadata={"position": "NotionalA"})
+ cob: datetime.date = field(metadata={"position": "COB Date"})
+ identifier: str = field(
+ metadata={"position": "Underlying (ISIN / CUSP / RED CODES)"}
+ )
+ start_date: datetime.date = field(default=None, metadata={"position": "Start Date"})
+ effective_date: datetime.date = field(
+ default=None, metadata={"position": "Effective Date"}
+ )
+ maturity: datetime.date = field(
+ default=None, metadata={"position": "Maturity Date"}
+ )
+ description: str = field(default=None, metadata={"position": "Underlying Desc"})
+ local_market_value: str = field(
+ default=None, metadata={"position": "Local Market Value"}
+ )
+ mtm_currency: str = field(default=None, metadata={"position": "MTM Currency"})
+ mtm_valuation: float = field(default=None, metadata={"position": "MTM Valuation"})
+ fixed_rate: float = field(default=None, metadata={"position": "FixedRate"})
+ putcall: bool = None
+ strike: float = field(default=None, metadata={"position": "Strike"})
+ underlying_maturity: datetime.date = field(
+ default=None, metadata={"position": "Underlying Maturity"}
+ )
+ exercise_type: str = field(default=None, metadata={"position": "Exercise Type"})
+ clearing_house: str = field(
+ default=None, metadata={"position": "Clearing House Name"}
+ )
+ account: str = field(default=None, metadata={"position": "AccountNumber"})
+ primebroker: str = field(default=None, metadata={"position": "Prime Broker"})
+ price: float = field(default=None, metadata={"position": "MarketPrice"})
+ staging_queue: ClassVar = []
+ asset_class: ClassVar[str] = field(metadata={"position": "Product Type"})
+ _query: ClassVar[str]
+
+ def __init_subclass__(cls, asset_class, **kwargs):
+ cls.asset_class = asset_class
+ cls._query = _sql_query[asset_class]
+ cls._registry[asset_class] = cls
+
+ @classmethod
+ def gen_positions(cls, cob, fund):
+ with cls._conn.cursor() as c:
+ params = (cob, fund) if cls not in (TranchePosition,) else (cob,)
+ c.execute(cls._query, params)
+ for row in c:
+ yield cls.from_query(row._asdict(), cob, fund)
+
+ @classmethod
+ def build_buffer(cls, cob, fund):
+ buf = StringIO()
+ csvwriter = csv.writer(buf)
+ csvwriter.writerow(POSITION_HEADERS)
+ csvwriter.writerows(
+ [[obj.get(h) for h in POSITION_HEADERS] for obj in cls.staging_queue]
+ )
+ buf = buf.getvalue().encode()
+ dest = get_path(cob, fund)
+ dest.parent.mkdir(exist_ok=True)
+ dest.write_bytes(buf)
+ return buf, dest
+
+ def from_query(d, cob, fund):
+ d["client_name"] = _fund_client[fund]
+ d["fund"] = fund
+ d["cob"] = cob
+ return d
+
+ def to_position(self):
+ obj = self.serialize("position")
+ obj["Product Type"] = self.asset_class
+ match self.asset_class:
+ case "irs":
+ obj["TransactionIndicator (Buy/Sell)"] = (
+ "Pay Fixed" if self.buysell else "Receive Fixed"
+ )
+ case _:
+ obj["TransactionIndicator (Buy/Sell)"] = (
+ "Buy" if self.buysell else "Sell"
+ )
+ return obj
+
+
+class BondPosition(PositionReport, asset_class="bond"):
+ @classmethod
+ def from_query(cls, d: dict, cob, fund):
+ d = super().from_query(d, cob, fund)
+ rename_keys(
+ d,
+ {
+ "usd_market_value": "mtm_valuation",
+ },
+ )
+ for key in ("account", "primebroker", "cp_code"):
+ d[key] = _fund_custodian[fund]
+ d["dealid"] = "Aggregated"
+ d["buysell"] = True
+ d["currency"] = "USD"
+ return cls.from_dict(**d)
+
+
+class FuturePosition(PositionReport, asset_class="future"):
+ @classmethod
+ def from_query(cls, d: dict, cob, fund):
+ d = super().from_query(d, cob, fund)
+ rename_keys(
+ d,
+ {
+ "bbg_ticker": "identifier",
+ "cash_account": "account",
+ "security_desc": "description",
+ "account_code": "primebroker",
+ },
+ )
+ return cls.from_dict(**d)
+
+
+class TranchePosition(PositionReport, asset_class="tranche"):
+ @classmethod
+ def from_query(cls, d: dict, cob, fund):
+ d = super().from_query(d, cob, fund)
+ rename_keys(
+ d,
+ {
+ "active_notional": "notional",
+ "trade_date": "start_date",
+ "security_desc": "description",
+ "mtm": "mtm_valuation",
+ "security_id": "identifier",
+ },
+ )
+ d["primebroker"] = "Bilateral"
+ d["buysell"] = d["protection"] == "Buyer"
+ return cls.from_dict(**d)
+
+
+class SwaptionPosition:
+ @classmethod
+ def from_query(cls, d: dict, cob, fund):
+ d = super().from_query(d, cob, fund)
+ rename_keys(
+ d,
+ {
+ "active_notional": "notional",
+ "trade_date": "start_date",
+ "effectivedate": "effective_date",
+ "nav": "MTM Valuation",
+ "expiration_date": "Underlying Maturity",
+ "security_id": "identifier",
+ "security_desc": "description",
+ },
+ )
+ d["primebroker"] = "Bilateral"
+ return cls.from_dict(**d)
+
+
+class IRSwaptionPosition(SwaptionPosition, PositionReport, asset_class="ir_swaption"):
+ pass
+
+
+class CDXSwaptionPosition(SwaptionPosition, PositionReport, asset_class="cdx_swaption"):
+ pass
+
+
+class CDXPosition(PositionReport, asset_class="cdx"):
+ @classmethod
+ def from_query(cls, d: dict, cob, fund):
+ d = super().from_query(d, cob, fund)
+ rename_keys(
+ d,
+ {
+ "effectivedate": "effective_date",
+ "security_desc": "description",
+ "security_id": "identifier",
+ "name": "primebroker",
+ },
+ )
+ d["FixedRate"] = d["coupon"] * 100
+ d["buysell"] = d["notional"] > 0
+ d["notional"] = abs(d["notional"]) * d["factor"]
+ d["mtm"] = d["clean_nav"] + d["accrued"]
+ d["cp_code"] = _fund_fcm[fund]
+ d["primebroker"] = _fund_fcm[fund]
+ d["currency"] = "EUR" if d["index"] in ("EU", "XO") else "USD"
+ d["clearing_house"] = "ICE"
+ d["dealid"] = "Aggregated"
+ return cls.from_dict(**d)
+
+
+class IRSPosition(PositionReport, asset_class="irs"):
+ @classmethod
+ def from_query(cls, d: dict, cob, fund):
+ d = super().from_query(d, cob, fund)
+ rename_keys(
+ d,
+ {
+ "trade_date": "start_date",
+ "effectivedate": "effective_date",
+ "pv": "mtm_valuation",
+ "maturity_date": "maturity",
+ "float_index": "identifier",
+ "swap_type": "description",
+ "payreceive": "buysell",
+ "cash_account": "account",
+ },
+ )
+ d["clearing_house"] = "ICE"
+ d["primebroker"] = _fund_fcm[fund]
+ return cls.from_dict(**d)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-29 17:13:23 +0000