from dataclasses import dataclass, field, fields, Field from enum import Enum from io import StringIO from headers import DealType, MTM_HEADERS, HEADERS from csv_headers.citco import GIL, GTL from typing import ClassVar, Tuple, Union from decimal import Decimal from typing import Literal import csv import datetime from psycopg.types.numeric import Int2BinaryDumper from psycopg import adapters from serenitas.analytics.dates import ( next_business_day, previous_twentieth, adjust_next_business_day, prev_business_day, ) from serenitas.utils.db2 import dbconn from serenitas.utils.env import DAILY_DIR from serenitas.utils.remote import FtpClient, SftpClient from lru import LRU from psycopg.errors import UniqueViolation import logging import warnings logger = logging.getLogger(__name__) Fund = Literal["SERCGMAST", "BRINKER", "BOWDST"] Portfolio = Literal[ "OPTIONS", "IR", "MORTGAGES", "CURVE", "TRANCHE", "CLO", "HEDGE_MAC" ] # deprecated IG, HY, STRUCTURED _funds = {"BAML": "SERCGMAST", "GS": "BOWDST", "WF": "SERCGMAST"} _fcms = { "Bank of America, N.A.": "BAML", "Goldman Sachs": "GS", "BOA": "BAML", "GOLD": "GS", "Wells Fargo Secs": "WF", } _client_name = {"SERCGMAST": "Serenitas", "BOWDST": "HEDGEMARK", "BRINKER": "LMCG"} class BusDayConvention(str, Enum): modified_following = "Modified Following" following = "Following" modified_preceding = "Modified Preceding" second_day_after = "Second-Day-After" end_of_month = "End-of-Month" DayCount = Literal["ACT/360", "ACT/ACT", "30/360", "ACT/365"] IsdaDoc = Literal["ISDA2014", "ISDA2003Cred"] class Frequency(Enum): Quarterly = 4 Monthly = 12 Ccy = Literal["USD", "CAD", "EUR", "YEN"] SwapType = Literal[ "CD_INDEX", "CD_INDEX_TRANCHE", "CD_BASKET_TRANCHE", "ABS_CDS", "BESPOKE" ] OptionType = Literal["RECEIVER", "PAYER"] ClearingFacility = Literal["ICE-CREDIT", "NOT CLEARED"] CdsStrat = Literal[ "HEDGE_CSO", "HEDGE_CLO", "HEDGE_MAC", "HEDGE_MBS", "SER_IGSNR", "SER_IGMEZ", "SER_IGEQY", "SER_IGINX", "SER_HYSNR", "SER_HYMEZ", "SER_HYEQY", "SER_HYINX", "SER_HYCURVE", "SER_IGCURVE", "SER_ITRXCURVE", "XCURVE", "MBSCDS", "IGOPTDEL", "HYOPTDEL", "HYEQY", "HYMEZ", "HYSNR", "HYINX", "IGEQY", "IGMEZ", "IGSNR", "IGINX", "XOEQY", "XOMEZ", "XOINX", "EUEQY", "EUMEZ", "EUSNR", "EUINX", "BSPK", "*", ] BondStrat = Literal[ "M_STR_MAV", "M_STR_MEZZ", "CSO_TRANCH", "M_CLO_BB20", "M_CLO_AAA", "M_CLO_BBB", "M_MTG_IO", "M_MTG_THRU", "M_MTG_GOOD", "M_MTG_B4PR", "M_MTG_RW", "M_MTG_FP", "M_MTG_LMG", "M_MTG_SD", "M_MTG_PR", "M_MTG_CRT_SD", "CRT_LD", "CRT_LD_JNR", "CRT_SD", "IGNORE", "MTG_REPO", ] SwaptionStrat = Literal[ "IGPAYER", "IGREC", "HYPAYER", "HYREC", "STEEP", "DV01", "HEDGE_MAC", ] SpotStrat = Literal[ "M_STR_MAV", "M_STR_MEZZ", "SER_IRTXCURVE", "M_CSH_CASH", "TCSH", "*" ] AssetClass = Literal["CSO", "Subprime", "CLO", "CRT"] @dataclass class Counterparty: name: str class FrequencyDumper(Int2BinaryDumper): def dump(self, f): return super().dump(f.value) adapters.register_dumper(Frequency, FrequencyDumper) def desc_str(index_type, series, tenor): if index_type in ("IG", "HY", "HYBB"): return f"CDX {index_type} CDSI S{series} {tenor}Y" elif index_type == "XO": return f"ITRX XOVER CDSI S{series} {tenor}Y" elif index_type == "EU": return f"ITRX EUR CDSI S{series} {tenor}Y" def is_default_init_field(cls, attr): match getattr(cls, attr, None): case Field(init=False): return False case _: return True class DealKind: def __class_getitem__(cls, trade_type: str): match trade_type: case "cds": return CDSDeal case "swaption": return SwaptionDeal case "termination": return TerminationDeal case _: return None def get_admin_headers(fund, trade_type): if fund in ("SERCGMAST", "BOWDST", "BRINKER"): try: return HEADERS[trade_type] except: from headers.globeop_upload import globeop_IRS, globeop_TRS return globeop_TRS def get_fname( trade_type: Union[str, Tuple[str, str]], fund: str = "SERCGMAST", ): d = { "bond": "Mortgages", "cds": "CreditDefaultSwapDeal", "swaption": "SwaptionDeal", "future": "Future", "wire": "CashFlowDeal", "spot": "SpotDeal", "fx_swap": "FxSwapDeal", "capfloor": "CapFloor", "repo": "RepoDeal", "termination": "Termination", "trs": "TRS", "irs": "IRS", } trade_tag: str if isinstance(trade_type, tuple): trade_tag = d[trade_type[0]] + trade_type[1] else: trade_tag = d[trade_type] timestamp = datetime.datetime.now() if fund == "BRINKER": return f"LMCG_BBH_SWAP_TRADES_P.{timestamp:%Y%m%d%H%M%S}.csv" elif fund == "SERCGMAST": return f"Serenitas.ALL.{timestamp:%Y%m%d.%H%M%S}.{trade_tag}.csv" elif fund == "BOWDST": return f"Bowdst.ALL.{timestamp:%Y%m%d.%H%M%S}.{trade_tag}.csv" def upload_buf(buf, dest, fund): match fund: case "SERCGMAST": ftp = FtpClient.from_creds("globeop") ftp.client.cwd("incoming") ftp.put(buf, dest) case "BOWDST": sftp = SftpClient.from_creds("hm_globeop") sftp.put(buf, dest) case "BRINKER": sftp = SftpClient.from_creds("bbh") sftp.put(buf, dest) class Deal: _conn: ClassVar = dbconn("dawndb", application_name="autobooker") _registry = {} _table_name: None _sql_fields: ClassVar[list[str]] _sql_insert: ClassVar[str] _sql_select: ClassVar[str] _insert_queue: ClassVar[list] = [] _admin_queue: ClassVar[list] = [] def __class_getitem__(cls, deal_type: DealType): return cls._registry[deal_type] def __init_subclass__( cls, deal_type: DealType, table_name: str, insert_ignore=(), **kwargs ): super().__init_subclass__(**kwargs) cls._registry[deal_type] = cls cls._table_name = table_name insert_columns = [c for c in cls.__annotations__ if c not in insert_ignore] place_holders = ",".join(["%s"] * len(insert_columns)) cls._sql_fields = { c: None for c in cls.__annotations__ if is_default_init_field(cls, c) } cls._sql_insert = f"INSERT INTO {table_name}({','.join(insert_columns)}) VALUES({place_holders})" cls._sql_select = ( f"SELECT {','.join(cls._sql_fields)} FROM {table_name} WHERE id=%s" ) def stage(self): self._insert_queue.append( [ getattr(self, f.name) for f in fields(self) if f.metadata.get("insert", True) ] ) @classmethod def admin_upload(cls, fund, trade_type, upload): if not cls._admin_queue: # early exit return buf = StringIO() csvwriter = csv.writer(buf) headers = get_admin_headers(fund, trade_type) csvwriter.writerow(headers) csvwriter.writerows( [row.get(h, None) for h in headers] for row in cls._admin_queue ) buf = buf.getvalue().encode() fname = get_fname(trade_type, fund) dest = DAILY_DIR / str(datetime.date.today()) / fname dest.parent.mkdir(exist_ok=True) dest.write_bytes(buf) if upload: upload_buf(buf, fname, fund) def admin_stage(self): self._admin_queue.append(self.to_globeop()) @classmethod def commit(cls): with cls._conn.cursor() as c: c.executemany(cls._sql_insert, cls._insert_queue) cls._conn.commit() cls._insert_queue.clear() @classmethod def from_tradeid(cls, trade_id: int): with cls._conn.cursor() as c: c.execute(cls._sql_select, (trade_id,)) r = c.fetchone() return cls(*r) def serialize(self, tag: str): return { f.metadata.get(tag, f.name): getattr(self, f.name) for f in fields(self) } class BbgDeal: _bbg_insert_queue: ClassVar[list] = [] _cache: ClassVar[LRU] = LRU(128) _bbg_sql_insert: ClassVar[str] def __init_subclass__(cls, deal_type, **kwargs): super().__init_subclass__(deal_type, **kwargs) if deal_type == DealType.Bond: cls._bbg_sql_insert = ( f"INSERT INTO bond_tickets VALUES({','.join(['%s'] * 20)})" ) elif deal_type == DealType.CDS: cls._bbg_sql_insert = ( f"INSERT INTO cds_tickets VALUES({','.join(['%s'] * 22)})" ) elif deal_type in (DealType.Fx, DealType.Spot, DealType.FxSwap): cls._bbg_sql_insert = ( f"INSERT INTO fx_tickets VALUES({','.join(['%s'] * 211)})" ) @classmethod def commit(cls): with cls._conn.cursor() as c: try: c.executemany(cls._bbg_sql_insert, cls._bbg_insert_queue) except UniqueViolation as e: logger.warning(e) cls._conn.rollback() else: c.executemany(cls._sql_insert, cls._insert_queue) cls._conn.commit() finally: cls._bbg_insert_queue.clear() cls._insert_queue.clear() @classmethod def process(cls, file_handle, index): for row in csv.DictReader(file_handle): line = {"bbg_ticket_id": index, **row} trade = cls.from_bbg_line(line) trade.stage() type(trade).commit() @classmethod def get_cp_code(cls, bbg_code, code_type): with cls._conn.cursor() as c: c.execute( "SELECT cp_code from bbg_ticket_mapping where bbg_code=%s and code_type=%s", (bbg_code, code_type), ) try: (cp_code,) = c.fetchone() except TypeError: raise ValueError(f"missing {bbg_code} in the db for {code_type}") return cp_code class MTMDeal: _mtm_queue: ClassVar[list] = [] _mtm_headers = None _mtm_sftp = SftpClient.from_creds("mtm") product_type: str def __init_subclass__(cls, deal_type, **kwargs): super().__init_subclass__(deal_type, **kwargs) cls._mtm_headers = MTM_HEADERS[deal_type] if deal_type == DealType.Swaption: cls.product_type = "CDISW" elif deal_type == DealType.CDS: cls.product_type = "TRN" elif deal_type == DealType.Termination: cls.product_type = "TERM" elif deal_type == DealType.TRS: cls.product_type = "CDI" @classmethod def mtm_upload(cls): if not cls._mtm_queue: # early exit return buf = StringIO() csvwriter = csv.writer(buf) csvwriter.writerow(cls._mtm_headers) csvwriter.writerows( [row.get(h, None) for h in cls._mtm_headers] for row in cls._mtm_queue ) buf = buf.getvalue().encode() fname = f"MTM.{datetime.datetime.now():%Y%m%d.%H%M%S}.{cls.product_type.capitalize()}.csv" cls._mtm_sftp.put(buf, fname) dest = DAILY_DIR / str(datetime.date.today()) / fname dest.write_bytes(buf) cls._mtm_queue.clear() def mtm_stage(self): self._mtm_queue.append(self.to_markit()) @classmethod def from_dict(cls, **kwargs): return cls(**{k: v for k, v in kwargs.items() if k in cls._sql_fields}) class Citco: _citco_headers = [] _citco_sftp = SftpClient.from_creds("citco") _submission_queue = [] @classmethod def citco_upload(cls): if not cls._citco_queue: # early exit return buf = StringIO() csvwriter = csv.writer(buf) csvwriter.writerow(cls._citco_headers) for h in cls._citco_queue: _citco_to_action = {"R": "UPDATE", "D": "CANCEL", "N": "NEW"} warnings.warn("we will get rid of overwriting") h["Fund"] = "ISOSEL" identifier = ( "instrument" if cls.file_tag == "i.innocap_serenitas." else "trade" ) unique_id = ( h["UniqueIdentifier"] if cls.file_tag == "i.innocap_serenitas." else h["ClientOrderID"] ) cls._submission_queue.append( [ unique_id, _citco_to_action[ h.get("OrdStatus", "N") ], # We only update trades, not instruments identifier, ] ) csvwriter.writerows( [row.get(h, None) for h in cls._citco_headers] for row in cls._citco_queue ) buf = buf.getvalue().encode() cls._citco_sftp.client.chdir("/incoming") cls._citco_sftp.put(buf, cls.fname()) cls.submission_commit() dest = DAILY_DIR / str(datetime.date.today()) / cls.fname() dest.write_bytes(buf) cls._citco_queue.clear() cls._submission_queue.clear() def citco_stage(self, action="NEW"): self._citco_queue.append(self.to_citco(action)) @classmethod def fname(cls): return f"{cls.file_tag}{datetime.datetime.now():%Y%m%d%H%M%S}.csv" @classmethod def submission_commit(cls): sql_str = "INSERT INTO citco_submission_status (serenitas_id, action, identifier_type) VALUES (%s, %s, %s) " with cls._conn.cursor() as c: c.executemany(sql_str, cls._submission_queue) cls._conn.commit() class CitcoProduct(Citco): _citco_queue: ClassVar[list] = [] _citco_headers = GIL product_key = () file_tag = "i.innocap_serenitas." def __init_subclass__(cls, product_key, **kwargs): cls.product_key = product_key def get_productid(self): filter_clause = " AND ".join([f"{k}=%s" for k in self.product_key]) sql_str = f"SELECT id, dealid, committed FROM {self._table_name} WHERE {filter_clause}" with self._conn.cursor() as c: c.execute( sql_str, tuple([getattr(self, k) for k in self.product_key]), ) if results := c.fetchone(): (self.id, self.dealid, self.committed) = results def to_citco(self, action): obj = self.serialize("citco") obj["Birth_date"] = obj["Birth_date"].strftime("%Y%m%d") obj["Death_date"] = obj["Death_date"].strftime("%Y%m%d") return obj class CitcoTrade(Citco): _citco_queue: ClassVar[list] = [] _citco_headers = GTL file_tag = "innocap_serenitas_trades_" def to_citco(self, action): obj = self.serialize("citco") obj["SettleCurrency"] = "USD" obj["OrdStatus"], obj["ExecTransType"] = self._action_to_citco(action) obj["FillID"] = obj["ClientOrderID"] obj["Trader"] = "DFLT" obj["StrategyCode"] = f"{obj['portfolio']}/{obj['folder']}" obj["TradeDate"] = ( obj["TradeDate"].strftime("%Y%m%d") if obj.get("TradeDate") else None ) obj["SettlementDate"] = ( obj["SettlementDate"].strftime("%Y%m%d") if obj.get("SettlementDate") else None ) obj["FillQty"] = obj.get("OrderQty") obj["FillPrice"] = obj.get("AvgPrice") obj["FXRate"] = 1 return obj @staticmethod def _action_to_citco(action): match action: case "NEW": return ("N", 2) case "UPDATE": return ("R", 0) case "CANCEL": return ("D", 0) @dataclass class CDSDeal( CitcoTrade, BbgDeal, MTMDeal, Deal, deal_type=DealType.CDS, table_name="cds", insert_ignore=("id", "dealid", "factor", "tenor", "redcode"), ): fund: Fund = field(metadata={"mtm": "Account Abbreviation", "citco": "Fund"}) account_code: str cp_code: str = field(metadata={"mtm": "Broker Id", "citco": "ExecutionBroker"}) security_id: str = field(metadata={"mtm": "RED"}) security_desc: str = field(metadata={"citco": "SecurityDescription"}) maturity: datetime.date = field(metadata={"mtm": "Maturity Date"}) currency: Ccy = field( metadata={"mtm": "Currency Code", "citco": "SecurityCurrency"} ) protection: Literal["Buy", "Sell"] notional: float = field(metadata={"mtm": "1st Leg Notional", "citco": "OrderQty"}) fixed_rate: float = field(metadata={"mtm": "1st Leg Rate"}) upfront: float = field(metadata={"mtm": "Initial Payment"}) traded_level: Decimal effective_date: datetime.date = field( default=None, metadata={"mtm": "Effective Date"} ) portfolio: Portfolio = field(default=None) folder: CdsStrat = field(default=None) payment_rolldate: BusDayConvention = BusDayConvention.following day_count: DayCount = "ACT/360" frequency: Frequency = Frequency.Quarterly trade_date: datetime.date = field( default_factory=datetime.date.today(), metadata={"mtm": "Trade Date", "citco": "TradeDate"}, ) upfront_settle_date: datetime.date = field( default_factory=lambda: next_business_day(datetime.date.today()), metadata={"mtm": "First Payment Date", "citco": "SettlementDate"}, ) orig_attach: int = field(default=None, metadata={"mtm": "Attachment Point"}) orig_detach: int = field(default=None, metadata={"mtm": "Exhaustion Point"}) tenor: int = field(init=False, metadata={"insert": False}) attach: int = field(default=None) detach: int = field(default=None) swap_type: SwapType = "CD_INDEX" clearing_facility: ClearingFacility = "ICE-CREDIT" isda_definition: IsdaDoc = "ISDA2014" id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "mtm": "Swap ID", "citco": "ClientOrderID"}, ) initial_margin_percentage: float = field( default=None, metadata={"mtm": "Independent Amount (%)"} ) factor: float = field(default=1.0, init=False, metadata={"insert": False}) redcode: str = field(init=False, metadata={"insert": False}) bbg_ticket_id: str = None def __post_init__(self): start_protection = self.trade_date + datetime.timedelta(days=1) effective_date = previous_twentieth(prev_business_day(start_protection)) self.effective_date = adjust_next_business_day(effective_date) if self.attach: self.factor = (self.detach - self.attach) / ( self.orig_detach - self.orig_attach ) else: with self._conn.cursor() as c: c.execute( "SELECT indexfactor / 100 FROM index_version WHERE redindexcode=%s", (self.security_id,), ) (self.factor,) = c.fetchone() # do something better self.tenor = self.security_desc.rsplit(" ", 1)[1].removesuffix("Y") self.redcode = "_".join((self.security_id, self.tenor)) def to_markit(self): obj = self.serialize("mtm") if obj["Initial Payment"] >= 0: obj["Transaction Code"] = "Receive" else: obj["Transaction Code"] = "Pay" obj["Initial Payment"] = round(abs(obj["Initial Payment"]), 2) obj["Trade ID"] = obj["Swap ID"] obj["Product Type"] = "TRN" obj["Transaction Type"] = "NEW" obj["Protection"] = "Buy" if obj["protection"] == "Buyer" else "Sell" obj["Entity Matrix"] = "Publisher" obj["Definitions Type"] = "ISDA2014Credit" # obj["Independent Amount (%)"] = obj["initial_margin_percentage"] if "ITRX" in obj["security_desc"]: obj["Include Contractual Supplement"] = "Y" obj["Contractual Supplement"] = "StandardiTraxxEuropeTranche" return obj def to_citco(self, action): obj = super().to_citco(action) obj["SecurityType"] = "CDS" obj["AvgPrice"] = ( obj["OrderQty"] / obj["upfront"] / obj["factor"] / 100 ) # Citco looks at factor as 1/100 if obj["protection"] == "Buyer": obj["BuySellShortCover"] = "S" else: obj["BuySellShortCover"] = "B" obj["AvgPrice"] = -obj["AvgPrice"] obj["FillPrice"] = obj["AvgPrice"] if obj["orig_attach"] is not None: # tranche process obj["IDSource"] = "USERID" obj["ExecutionBroker"] = _citco_cp_isda[obj["ExecutionBroker"]] obj["ClearingAgent"] = obj["ExecutionBroker"] obj["SecurityID"] = self.product.dealid else: # cleared cds process obj["IDSource"] = "RED" obj["ExecutionBroker"] = ( _citco_cp_cdea[obj["ExecutionBroker"]] if obj["ExecutionBroker"] != "BSEONY" else "BSEONY" ) # We need to query DB via accounts table here warnings.warn("we will get rid of overwriting") obj["ClearingAgent"] = "BOA_FC" obj["SecurityID"] = self.redcode return obj @property def product(self): return TrancheProduct( underlying_security_id=self.redcode, attach=self.orig_attach, detach=self.orig_detach, death_date=self.maturity, security_desc=f"{self.security_desc} {self.orig_attach}-{self.orig_detach}", ) @classmethod def from_bbg_line(cls, line: dict): if line["Client FCM"] == "": raise ValueError("Trade is unallocated") if line["Coupon"] == "": with cls._conn.cursor() as c: c.execute( "SELECT coupon, index, series, tenor FROM index_desc " "WHERE redindexcode=%s AND maturity =%s", ( line["Red Code"], datetime.datetime.strptime(line["Mat Dt"], "%m/%d/%Y").date(), ), ) coupon, index, series, tenor = c.fetchone() line["Security"] = desc_str(index, series, tenor.removesuffix("yr")) line["Coupon"] = coupon if "Price (Dec)" not in line: # Means this is a BSEF block file line["Price (Dec)"] = line["Price"] line["Quantity"] = float(line["Qty (M)"]) * 1000 values = [line["bbg_ticket_id"]] + [None] * 21 values[14] = _funds[_fcms[line["Client FCM"]]] values[15] = _fcms[line["Client FCM"]] else: values = list(line.values()) cp_code = cls.get_cp_code(line["Brkr"], "CDS") cls._bbg_insert_queue.append(values) return cls( fund=_funds[_fcms[line["Client FCM"]]], folder="*", portfolio="UNALLOCATED", security_id=line["Red Code"], security_desc=line["Security"].removesuffix(" PRC"), traded_level=Decimal(line["Price (Dec)"]), notional=line["Quantity"], fixed_rate=float(line["Coupon"]) * 0.01, trade_date=datetime.datetime.strptime(line["Trade Dt"], "%m/%d/%Y").date(), maturity=datetime.datetime.strptime(line["Mat Dt"], "%m/%d/%Y").date(), currency=line["Curncy"], protection="Buyer" if line["Side"] == "B" else "Seller", upfront=line["Net"], cp_code=cp_code, account_code=_fcms[line["Client FCM"]], bbg_ticket_id=line["bbg_ticket_id"], ) @dataclass class BondDeal( CitcoTrade, BbgDeal, Deal, deal_type=DealType.Bond, table_name="bonds", insert_ignore=("id", "dealid"), ): buysell: bool description: str faceamount: float = field(metadata={"citco": "OrderQty"}) price: float = field(metadata={"citco": "AvgPrice"}) cp_code: str = field(metadata={"citco": "ExecutionBroker"}) cusip: str = None isin: str = None identifier: str = field(default=None, metadata={"citco": "SecurityID"}) trade_date: datetime.date = field( default_factory=datetime.date.today(), metadata={"citco": "TradeDate"} ) settle_date: datetime.date = field( default_factory=lambda: next_business_day(datetime.date.today()), metadata={"citco": "SettlementDate"}, ) folder: BondStrat = field(default=None) portfolio: Portfolio = field(default=None) asset_class: AssetClass = field(default=None) bbg_ticket_id: str = None principal_payment: float = None accrued_payment: float = None current_face: float = None id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "mtm": "Swap ID", "citco": "ClientOrderID"}, ) @classmethod def from_bbg_line(cls, line: dict): with cls._conn.cursor() as c: c.execute( "SELECT asset_class from securities where figi=%s", (line["FIGI"],), ) results = c.fetchone() asset_class = results[0] if results else None cp_code = cls.get_cp_code(line["Brkr"], "BOND") cls._bbg_insert_queue.append(list(line.values())) return cls( faceamount=Decimal(line["Quantity"]), price=Decimal(line["Price (Dec)"]), cp_code=cp_code, cusip=line["Cusip"], identifier=line["Cusip"], trade_date=datetime.datetime.strptime(line["Trade Dt"], "%m/%d/%Y"), settle_date=datetime.datetime.strptime(line["SetDt"], "%m/%d/%Y"), portfolio="UNALLOCATED", description=line["Security"].removesuffix(" Mtge"), buysell=line["Side"] == "B", bbg_ticket_id=line["bbg_ticket_id"], asset_class=asset_class, ) @classmethod def from_allocationid(cls, allocation_id): with cls._conn.cursor() as c: c.execute( "SELECT tradeid, notional from bond_allocation where id=%s", (allocation_id,), ) tradeid, notional = c.fetchone() cls = cls.from_tradeid(tradeid) ratio = notional / cls.faceamount for key in [ "principal_payment", "accrued_payment", "current_face", "net_amount", ]: if key in cls.__dict__.keys(): setattr(cls, key, getattr(cls, key) * ratio) setattr(cls, "faceamount", notional) return cls def to_citco(self, action): obj = super().to_citco(action) obj["SecurityType"] = "CMO" warnings.warn("Hardcoded") obj["ClearingAgent"] = "NT" obj["FXRate"] = 1 obj["BuySellShortCover"] = "B" if obj["buysell"] else "S" obj["IDSource"] = "CUSIP" with self._conn.cursor() as c: c.execute( "SELECT coupon, day_count from securities where identifier=%s", (obj["SecurityID"],), ) obj["Coupon%"], obj["DayCountFraction/RepoCalendar"] = c.fetchone() return obj @dataclass class SwaptionDeal( CitcoTrade, MTMDeal, Deal, deal_type=DealType.Swaption, table_name="swaptions", insert_ignore=("id", "dealid", "factor"), ): buysell: bool fund: Fund = field(metadata={"mtm": "Account Abbreviation", "citco": "Fund"}) cp_code: str = field(metadata={"mtm": "Broker Id", "citco": "ExecutionBroker"}) security_id: str = field(metadata={"mtm": "RED"}) security_desc: str = field(metadata={"citco": "SecurityDescription"}) maturity: datetime.date = field(metadata={"mtm": "Maturity Date"}) currency: Ccy = field( metadata={"mtm": "Currency Code", "citco": "SecurityCurrency"} ) notional: float = field(metadata={"mtm": "1st Leg Notional", "citco": "OrderQty"}) fixed_rate: float = field(metadata={"mtm": "1st Leg Rate"}) strike: float = field(metadata={"mtm": "Strike Price"}) price: float = field(metadata={"citco": "AvgPrice"}) option_type: OptionType expiration_date: datetime.date = field(metadata={"mtm": "Expiration"}) portfolio: Portfolio = field(default=None) folder: SwaptionStrat = field(default=None) trade_date: datetime.date = field( default_factory=datetime.date.today(), metadata={"mtm": "Trade Date", "citco": "TradeDate"}, ) settle_date: datetime.date = field( default_factory=lambda: next_business_day(datetime.date.today()), metadata={"mtm": "Settle Date", "citco": "SettlementDate"}, ) expiration_date: datetime.date = field( metadata={"mtm": "Swaption Expiration Date"}, ) initial_margin_percentage: float = field( default=None, metadata={"mtm": "Independent Amount (%)"} ) id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "mtm": "Swap ID", "citco": "ClientOrderID"}, ) factor: float = field(default=1.0, init=False, metadata={"insert": False}) def __post_init__(self): # will need to filter a bit better, for now, just CDX index swaptions if self.security_desc: with self._conn.cursor() as c: c.execute( "SELECT indexfactor / 100 FROM index_version WHERE redindexcode=%s", (self.security_id,), ) (self.factor,) = c.fetchone() self.tenor = self.security_desc.rsplit(" ", 1)[1].removesuffix("Y") self.redcode = "_".join((self.security_id, self.tenor)) def to_markit(self): obj = self.serialize("mtm") obj["Initial Payment"] = ( round(obj["price"] * obj["1st Leg Notional"] * 0.01, 2) * self.factor ) obj["Trade ID"] = obj["Swap ID"] obj["Product Type"] = self.product_type obj["Transaction Type"] = "NEW" if obj["buysell"]: obj["Transaction Code"] = "Pay" obj["Protection"] = "Buy" if obj["option_type"] == "PAYER" else "Sell" obj["OptionBuySellIndicator"] = "Buy" else: obj["Transaction Code"] = "Receive" obj["Protection"] = "Sell" if obj["option_type"] == "PAYER" else "Buy" obj["OptionBuySellIndicator"] = "Sell" obj["Entity Matrix"] = "Publisher" obj["Clearing House"] = "ICE_FCM_US" obj["Swaption Settlement Type"] = "Physical" obj["Supplement Date"] = datetime.date(2021, 12, 13) obj["Supplement 2 Date"] = datetime.date(2020, 1, 27) if "IG" in obj["security_desc"]: obj["Swaption Quotation Rate Type"] = "Spread" obj["Strike Price"] = obj["Strike Price"] * 0.01 obj["Effective Date"] = obj["Trade Date"] return obj def to_citco(self, action): obj = super().to_citco(action) obj["ExecutionBroker"] = _citco_cp_isda[obj["ExecutionBroker"]] obj["ClearingAgent"] = obj["ExecutionBroker"] obj["SecurityType"] = "BNDOPT" obj["BuySellShortCover"] = "B" if obj["buysell"] == "Buy" else "S" obj["IDSource"] = "USERID" obj["ClearingAgent"] = obj["ExecutionBroker"] obj["SecurityID"] = self.product.dealid return obj @property def product(self): return SwaptionProduct( underlying_security_id=self.redcode, instrument_type="BNDO", callput=self.option_type == "RECEIVER", strike=self.strike, expiration=self.expiration_date, ) @dataclass class TerminationDeal( MTMDeal, Deal, deal_type=DealType.Termination, table_name="terminations", insert_ignore=("id", "dealid", "orig_cp", "currency", "fund", "product_type"), ): partial_termination: bool termination_fee: float = field(metadata={"mtm": "Initial Payment"}) fee_payment_date: datetime.date = field( metadata={"mtm": "Settle Date", "globeop": "FeePaymentDate"} ) termination_cp: str = field(metadata={"mtm": "Broker Id"}) termination_amount: float = field( metadata={"mtm": "1st Leg Notional", "globeop": "TerminationAmount"} ) termination_date: datetime.date = field( default_factory=datetime.date.today(), metadata={"mtm": "Trade Date", "globeop": "TerminationDate"}, ) id: int = field(default=None, metadata={"insert": False}) dealid: str = field(default=None, metadata={"insert": False, "mtm": "Swap ID"}) factor: float = field(default=1, init=False, metadata={"insert": False}) orig_cp: str = field( init=False, metadata={"mtm": "Remaining Party", "insert": False}, ) currency: str = field( init=False, metadata={"mtm": "Currency Code", "insert": False}, ) fund: str = field( init=False, metadata={"mtm": "Account Abbreviation", "insert": False}, ) product_type: str = field( init=False, metadata={"mtm": "Product Type", "insert": False} ) deal_type: str = field( init=False, metadata={"insert": False, "globeop": "DealType"} ) globeop_id: str = field(init=False, default=None, metadata={"globeop": "GoTradeId"}) def __post_init__(self): if self.dealid.startswith("SWPTN"): self.product_type = "CDISW" self.deal_type = "SwaptionDeal" sql_str = ( "SELECT cp_code, currency, fund, globeop_id FROM terminations " "LEFT JOIN swaptions USING (dealid) " "WHERE terminations.id = %s" ) elif self.dealid.startswith("SCCDS"): self.product_type = "TRN" self.deal_type = "CreditDefaultSwapDeal" sql_str = ( "SELECT cp_code, currency, fund, b.globeop_id, " "(detach - attach) / (orig_detach - orig_attach) " "FROM terminations " "LEFT JOIN cds USING (dealid) " "LEFT JOIN LATERAL (" " SELECT globeop_id FROM id_mapping WHERE serenitas_id=cds.id" " ORDER BY date DESC LIMIT 1" ") b ON true " "WHERE terminations.id = %s" ) with self._conn.cursor() as c: c.execute(sql_str, (self.id,)) if self.deal_type == "SwaptionDeal": self.orig_cp, self.currency, self.fund, self.globeop_id = c.fetchone() elif self.deal_type == "CreditDefaultSwapDeal": ( self.orig_cp, self.currency, self.fund, self.globeop_id, self.factor, ) = c.fetchone() def to_markit(self): obj = self.serialize("mtm") if obj["Initial Payment"] >= 0: obj["Transaction Code"] = "Receive" else: obj["Transaction Code"] = "Pay" obj["Initial Payment"] = round(abs(obj["Initial Payment"]), 2) obj["Trade ID"] = obj["Swap ID"] + "-" + str(obj["id"]) obj["Transaction Type"] = ( "Termination" if obj["Remaining Party"] == obj["Broker Id"] else "Assignment" ) obj["Effective Date"] = obj["Trade Date"] + datetime.timedelta(days=1) obj["Product Type"] = obj["product_type"] return obj def to_globeop(self): obj = self.serialize("globeop") obj["TerminationAmount"] *= self.factor obj["FeesPaid"] = ( -obj["termination_fee"] if obj["termination_fee"] < 0 else None ) obj["FeesReceived"] = ( obj["termination_fee"] if obj["termination_fee"] > 0 else None ) obj["Action"] = "UPDATE" obj["Client"] = _client_name[obj["fund"]] obj["SubAction"] = "Termination" if self.termination_cp != self.orig_cp: obj["AssignedCounterparty"] = self.termination_cp obj["PartialTermination"] = "Y" if self.partial_termination else "N" return obj @dataclass class SpotDeal( CitcoTrade, BbgDeal, Deal, deal_type=DealType.Spot, table_name="spots", insert_ignore=("id", "dealid"), ): folder: SpotStrat portfolio: Portfolio spot_rate: float = field(metadata={"citco": "AvgPrice"}) buy_currency: str buy_amount: float sell_currency: str sell_amount: float fund: Fund cp_code: str = field(metadata={"citco": "ExecutionBroker"}) cash_account: str commission_currency: str = "USD" commission: float = None id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "citco": "ClientOrderID"} ) trade_date: datetime.date = field( default_factory=datetime.date.today(), metadata={"citco": "TradeDate"} ) settle_date: datetime.date = field( default_factory=datetime.date.today(), metadata={"citco": "SettlementDate"} ) bbg_ticket_id: str = None @classmethod def from_bbg_line(cls, line: dict): cp_code = cls.get_cp_code(line["Counterparty Deal Code"], "FX") if line["Side"] == "B": key1, key2 = "buy", "sell" else: key1, key2 = "sell", "buy" d = { f"{key1}_currency": line["Currency 1"], f"{key1}_amount": line["Amount Dealt"], f"{key2}_currency": line["Currency 2"], f"{key2}_amount": line["Counter Amount"], } for key in ("Comp Quote 1",): if line[key] == "": line[key] = None cls._bbg_insert_queue.append(list(line.values())) return cls( folder="*", portfolio="UNALLOCATED", cp_code=cp_code, trade_date=datetime.datetime.strptime(line["Date Of Deal"], "%Y%m%d"), settle_date=datetime.datetime.strptime( line["Value Date Period 1 Currency 1"], "%Y%m%d" ), fund=_fx_funds[line["ALOC Account 1"]], spot_rate=line["Exchange Rate Period 1"], cash_account=_fx_accounts[line["ALOC Account 1"]], bbg_ticket_id=line["bbg_ticket_id"], **d, ) def to_citco(self, action): obj = super().to_citco(action) if obj["buy_currency"] == "USD": key1, key2 = "sell", "buy" else: key1, key2 = "buy", "sell" obj["SecurityCurrency"] = obj[f"{key1}_currency"] obj["OrderQty"] = obj[f"{key1}_amount"] obj["FillQty"] = obj["OrderQty"] obj["SecurityType"] = "FX" obj["BuySellShortCover"] = "S" if obj["buy_currency"] == "USD" else "B" obj["IDSource"] = "BLOOMBERG" _citco_currency_mapping = {"EUR": "EURUSD CURNCY"} obj["SecurityID"] = _citco_currency_mapping[obj["SecurityCurrency"]] obj["ClearingAgent"] = "NT" obj["FillFXSettleAmount"] = obj[f"{key2}_amount"] obj["FXRate"] = 1 return obj _fx_funds = {"serenitas": "SERCGMAST", "bowdst": "BOWDST", "baml_fcm": "SERCGMAST"} _fx_accounts = {"serenitas": "V0NSCLMAMB", "bowdst": "751254", "baml_fcm": "V0NSCLMSPT"} class FxDeal(BbgDeal, Deal, table_name=None, deal_type=DealType.Fx): @classmethod def from_bbg_line(cls, line: dict): if line["Deal Type"] in ("4", "2"): return SpotDeal.from_bbg_line(line) else: return FxSwapDeal.from_bbg_line(line) @dataclass class FxSwapDeal( CitcoTrade, BbgDeal, Deal, deal_type=DealType.FxSwap, table_name="fx_swaps", insert_ignore=("id", "dealid"), ): folder: str portfolio: str trade_date: datetime.date = field(metadata={"citco": "TradeDate"}) near_settle_date: datetime.date near_buy_currency: str near_buy_amount: float near_sell_currency: str near_sell_amount: float near_rate: float far_rate: float far_settle_date: datetime.date far_buy_currency: str far_buy_amount: float far_sell_currency: str far_sell_amount: str fund: Fund cp_code: str cash_account: str id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "citco": "ClientOrderID"} ) bbg_ticket_id: str = None @classmethod def from_bbg_line(cls, line: dict): cp_code = cls.get_cp_code(line["Counterparty Deal Code"], "FX") if line["Side"] == "S": key1, key2 = "buy", "sell" else: key1, key2 = "sell", "buy" d = { f"near_{key1}_currency": line["Currency 1"], f"near_{key1}_amount": line["Amount Dealt"], f"far_{key1}_currency": line["Currency 2"], f"far_{key1}_amount": line["Far Counter Amount"], f"near_{key2}_currency": line["Currency 2"], f"near_{key2}_amount": line["Counter Amount"], f"far_{key2}_currency": line["Currency 1"], f"far_{key2}_amount": line["Far Amount Dealt"], } for key in ("Comp Quote 1",): if line[key] == "": line[key] = None cls._bbg_insert_queue.append(list(line.values())) return cls( folder="*", portfolio="UNALLOCATED", cp_code=cp_code, trade_date=datetime.datetime.strptime(line["Date Of Deal"], "%Y%m%d"), near_settle_date=datetime.datetime.strptime( line["Value Date Period 1 Currency 1"], "%Y%m%d" ), far_settle_date=datetime.datetime.strptime( line["Value Date Period 2 Currency 1"], "%Y%m%d" ), fund=_fx_funds[line["ALOC Account 1"]], near_rate=line["Exchange Rate Period 1"], far_rate=line["Exchange Rate Period 2"], cash_account=_fx_accounts[line["ALOC Account 1"]], bbg_ticket_id=line["bbg_ticket_id"], **d, ) def to_citco(self, action): obj = super().to_citco(action) if obj["near_buy_currency"] == "USD": # This is for strict FX Swaps key1, key2 = "buy", "sell" else: key1, key2 = "sell", "buy" obj["SecurityCurrency"] = obj[f"far_{key1}_currency"] obj["OrderQty"] = obj[f"far_{key1}_amount"] obj["SecurityType"] = "FWDFX" obj["AvgPrice"] = obj["far_rate"] obj["BuySellShortCover"] = "B" if obj["near_buy_currency"] == "USD" else "S" obj["IDSource"] = "BLOOMBERG" _citco_currency_mapping = {"EUR": "EURUSD CURNCY"} obj["SecurityID"] = _citco_currency_mapping[obj["SecurityCurrency"]] obj["ClearingAgent"] = "NT" obj["SettlementDate"] = obj["far_settle_date"] obj["FillFXSettleAmount"] = obj[f"far_{key2}_amount"] near_trade = SpotDeal( folder=obj["folder"], portfolio=obj["portfolio"], spot_rate=obj["near_rate"], buy_currency=obj[f"near_{key1}_currency"], buy_amount=obj[f"near_{key1}_amount"], sell_currency=obj[f"near_{key2}_currency"], sell_amount=obj[f"near_{key2}_amount"], fund=obj["fund"], dealid=obj["ClientOrderID"] + "_N", trade_date=datetime.datetime.strptime( obj["TradeDate"], "%Y%m%d" ), # Will be cleaning up with a split function, this is just to run it settle_date=obj["near_settle_date"], cp_code=obj["cp_code"], cash_account=obj["cash_account"], ) obj["ClientOrderID"] = obj["ClientOrderID"] + "_F" obj["FXRate"] = 1 return obj @dataclass class TRSDeal( CitcoTrade, MTMDeal, Deal, deal_type=DealType.TRS, table_name="trs", insert_ignore=("id", "dealid", "orig_cp", "currency", "product_type"), ): fund: str = field( metadata={"mtm": "Account Abbreviation", "globeop": "Fund"}, ) portfolio: str = field(metadata={"globeop": "Portfolio"}) folder: str = field(metadata={"globeop": "Folder"}) cash_account: str = field(metadata={"globeop": "Cash Account"}) cp_code: str = field( metadata={ "globeop": "Counterparty", "mtm": "Broker Id", "citco": "ExecutionBroker", } ) trade_date: datetime.date = field( metadata={"globeop": "Trade Date", "mtm": "Trade Date", "citco": "TradeDate"} ) effective_date: datetime.date = field( init=False, metadata={"mtm": "Effective Date", "citco": "SettlementDate"} ) maturity_date: datetime.date = field(metadata={"mtm": "Maturity Date"}) funding_index: str buysell: bool underlying_security: str price: float = field(metadata={"mtm": "Initial Fixing Amount", "citco": "AvgPrice"}) accrued: float = field(metadata={"mtm": "Initial Payment", "citco": "Fee"}) funding_freq: str funding_daycount: str funding_payment_roll_convention: str funding_arrears: bool asset_freq: str asset_daycount: str asset_payment_roll_convention: str initial_margin_percentage: float = field( metadata={"globeop": "InitialMarginPercent", "mtm": "Independent Amount (%)"} ) notional: float = field(metadata={"mtm": "1st Leg Notional", "citco": "OrderQty"}) currency: str = field( metadata={"mtm": "Currency Code", "citco": "SecurityCurrency"} ) interest_calc_method: str compound_avg_frequency: str fixing_frequency: str cpty_id: str id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={ "insert": False, "mtm": "Swap ID", "globeop": "Deal Id", "citco": "ClientOrderID", }, ) def __post_init__(self): self.effective_date = self.trade_date + datetime.timedelta(days=1) def to_markit(self): _trs_red = {"IBOXHY": "4J623JAA8"} _mtm_index = {"SOFRRATE": "USD-SOFR-COMPOUND"} obj = self.serialize("mtm") obj["Trade ID"] = obj["Swap ID"] obj["Initial Payment Currency"] = obj["Currency Code"] if obj["Initial Payment"] >= 0: obj["Transaction Code"] = "Receive" else: obj["Transaction Code"] = "Pay" obj["Initial Payment"] = round(abs(obj["Initial Payment"]), 2) obj["Product Sub Type"] = "IBOXX" # Hardcoded for now obj["RED"] = _trs_red[obj["underlying_security"]] obj["Transaction Type"] = "New" obj["Protection"] = "Buy" if obj["buysell"] else "Sell" obj["Master Document Date"] = datetime.date(2020, 12, 18) obj["Supplement Date"] = datetime.date(2015, 2, 18) obj["Product Type"] = self.product_type obj["Independent Amount Payer"] = obj["Account Abbreviation"] obj["2nd Leg Index"] = _mtm_index[obj["funding_index"]] obj["2nd Leg Spread"] = 0 obj["2nd Leg Initial Floating Rate"] = 0 return obj def to_globeop(self): obj = self.serialize("globeop") if obj["buysell"]: key1, key2 = "Pay", "Receive" else: key1, key2 = "Receive", "Pay" d = { f"{key1}LegRateType": "Floating", f"{key1}UnderlyingType": "Interest", f"{key1}FloatRate": obj["funding_index"], f"{key1}FixedRate": 0, f"{key1}Daycount": obj["funding_daycount"], f"{key1}Frequency": obj["funding_freq"], f"{key1}EffectiveDate": obj["effective_date"], f"{key1}MaturityDate": obj["maturity_date"], f"{key1}Notional": obj["notional"], f"{key1}PaymentBDC": obj["funding_payment_roll_convention"], f"{key1}Arrears": "Y" if obj["funding_arrears"] else "N", f"{key1}InterestCalcMethod": obj["interest_calc_method"], f"{key1}CompoundAverageFrequency": obj["compound_avg_frequency"], f"{key1}Currency": obj["currency"], f"{key1}FixingFrequency": obj["fixing_frequency"], f"{key2}LegRateType": "Fixed", f"{key2}UnderlyingType": "Bond", f"{key2}UnderlyingSecurity": obj["underlying_security"], f"{key2}Daycount": obj["asset_daycount"], f"{key2}Frequency": obj["asset_freq"], f"{key2}EffectiveDate": obj["effective_date"], f"{key2}MaturityDate": obj["maturity_date"], f"{key2}Notional": obj["notional"], f"{key2}PaymentBDC": obj["asset_payment_roll_convention"], f"{key2}Price": obj["price"], f"{key2}Currency": obj["currency"], } obj["SwapType"] = "TOTAL_RETURN_SWAP" obj["Deal Type"] = "TotalReturnSwapDeal" obj["Action"] = "NEW" # Need to figure this out obj["Client"] = "Serenitas" obj["State"] = "Valid" obj["Custodian"] = "BAC" obj.update(d) return obj def to_citco(self, action): obj = super().to_citco(action) obj["ExecutionBroker"] = _citco_cp_isda[obj["ExecutionBroker"]] obj["ClearingAgent"] = obj["ExecutionBroker"] obj["SecurityType"] = "TRS" obj["BuySellShortCover"] = "B" if obj["buysell"] else "S" obj["IDSource"] = "USERID" obj["Fee"] = -obj["Fee"] if obj["buysell"] else obj["Fee"] obj["SecurityID"] = self.product.dealid return obj @property def product(self): return TRSProduct( birth_date=self.trade_date, death_date=self.maturity_date, underlying_security=self.underlying_security, funding_index=self.funding_index, ) @dataclass class IRSDeal( CitcoTrade, Deal, deal_type=DealType.IRS, table_name="irs", insert_ignore=("id", "dealid", "orig_cp", "product_type"), ): fund: str = field( metadata={"mtm": "Account Abbreviation", "globeop": "Fund"}, ) portfolio: str = field(metadata={"globeop": "Portfolio"}) folder: str = field(metadata={"globeop": "Folder"}) cash_account: str = field(metadata={"globeop": "Cash Account"}) cp_code: str = field( metadata={"globeop": "GiveUpCounterparty", "citco": "ExecutionBroker"} ) trade_date: datetime.date = field( metadata={"globeop": "Trade Date", "citco": "TradeDate"} ) effective_date: datetime.date = field(metadata={"citco": "SettlementDate"}) maturity_date: datetime.date payreceive: bool fixed_rate: float = field(metadata={"citco": "AvgPrice"}) fixed_daycount: str fixed_payment_freq: str fixed_bdc: str notional: float = field(metadata={"citco": "OrderQty"}) float_index: str float_daycount: str float_payment_freq: str float_bdc: str float_arrears: bool float_fixing_freq: str pay_interest_calc_method: str clearing_facility: str = field(metadata={"globeop": "ClearingFacility"}) swap_type: str = field(metadata={"globeop": "SwapType"}) cleared_trade_id: str currency: str = field(metadata={"citco": "SecurityCurrency"}) custodian: int = field( default=None, metadata={"insert": False, "globeop": "Custodian"}, init=False ) id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={ "insert": False, "mtm": "Swap ID", "globeop": "Deal Id", "citco": "ClientOrderID", }, ) def __post_init__(self): with self._conn.cursor() as c: c.execute( "SELECT cp_code from accounts2 where cash_account=%s", (self.cash_account,), ) (self.custodian,) = c.fetchone() def to_globeop(self): obj = self.serialize("globeop") if obj["payreceive"]: key1, key2 = "Receive", "Pay" else: key1, key2 = "Pay", "Receive" d = { f"{key1}LegRateType": "Float", f"{key1}FloatRate": obj["float_index"], f"{key1}Daycount": obj["float_daycount"], f"{key1}Frequency": obj["float_payment_freq"], f"{key1}PaymentBDC": obj["float_bdc"], f"{key1}EffectiveDate": obj["effective_date"], f"{key1}MaturityDate": obj["maturity_date"], f"{key1}Notional": obj["notional"], f"{key1}ResetArrears": "Y" if obj["float_arrears"] else "N", f"{key1}Currency": obj["currency"], f"{key1}FixingFrequency": obj["float_fixing_freq"], f"{key1}InterestCalcMethod": obj["pay_interest_calc_method"], f"{key2}LegRateType": "Fixed", f"{key2}FixedRate": obj["fixed_rate"], f"{key2}Daycount": obj["fixed_daycount"], f"{key2}Frequency": obj["fixed_payment_freq"], f"{key2}PaymentBDC": obj["fixed_bdc"], f"{key2}EffectiveDate": obj["effective_date"], f"{key2}MaturityDate": obj["maturity_date"], f"{key2}Notional": obj["notional"], f"{key2}Currency": obj["currency"], } obj["Deal Type"] = "InterestRateSwapDeal" obj["Action"] = "NEW" # Need to figure this out obj["Client"] = "Serenitas" obj["State"] = "Valid" obj.update(d) return obj def to_citco(self, action): obj = super().to_citco(action) obj["ExecutionBroker"] = _citco_cp_cdea[obj["ExecutionBroker"]] obj["SecurityType"] = "IRS" obj["StrategyCode"] = f"{obj['portfolio']}/{obj['folder']}" obj["FillPrice"] = obj["AvgPrice"] obj["BuySellShortCover"] = "B" if obj["payreceive"] else "S" obj["IDSource"] = "USERID" obj["SecurityID"] = self.product.dealid warnings.warn("Query DB") obj["ClearingAgent"] = "BOA_FC" return obj @property def product(self): return IRSProduct( birth_date=self.trade_date, death_date=self.maturity_date, fixed_rate=self.fixed_rate, float_index=self.float_index, ) from enum import IntEnum class TrancheType(IntEnum): DayCount = 3 @dataclass class TrancheProduct( Deal, CitcoProduct, deal_type=DealType.TrancheProduct, table_name="citco_tranche", product_key=("underlying_security_id", "attach", "detach"), insert_ignore=( "id", "dealid", "birth_date", "death_date", "security_desc", "coupon", "currency", ), ): underlying_security_id: str = field(metadata={"citco": "UnderlyingSecurityId"}) attach: float = field(metadata={"citco": "Attachment_Points"}) detach: float = field(metadata={"citco": "Detachment_Points"}) birth_date: datetime.date = field( default=None, metadata={"insert": False, "citco": "Birth_date"} ) death_date: datetime.date = field( default=None, metadata={"insert": False, "citco": "Death_date"} ) coupon: float = field( default=None, metadata={"insert": False, "citco": "CouponRate"} ) security_desc: str = field( default=None, metadata={"insert": False, "citco": "Sec_Desc"} ) currency: str = field(default=None, metadata={"citco": "LocalCcy", "insert": False}) instrument_type: str = field(default="CDS", metadata={"citco": "InstrumentType"}) underlying_id_source: str = field( default="RED", metadata={"citco": "UnderlyingIDSource"} ) committed: bool = field(default=False) id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "citco": "UniqueIdentifier"} ) def __post_init__(self): if not all( [ self.birth_date, self.death_date, self.coupon, self.security_desc, self.currency, ] ): redcode, tenor = self.underlying_security_id.split("_") tenor_yr = tenor + "yr" sql_str = ( "SELECT issue_date, maturity, coupon, index, series " "FROM index_desc WHERE tenor=%s AND redindexcode=%s" ) with self._conn.cursor() as c: c.execute(sql_str, (tenor_yr, redcode)) ( self.birth_date, self.death_date, self.coupon, index, series, ) = c.fetchone() self.security_desc = ( f"{desc_str(index, series, tenor)} {self.attach}-{self.detach}" ) self.currency = "EUR" if index in ("XO", "EU") else "USD" self.get_productid() def to_citco(self, action): if not self.id: self.stage() self.commit() self.get_productid() obj = super().to_citco(action) obj["Command"] = "N" obj["Active"] = "Y" obj["CouponRate"] = obj["CouponRate"] / 100 obj["SettleDays"] = 3 obj["AccruStartDate"] = obj["Birth_date"] return obj @dataclass class SwaptionProduct( Deal, CitcoProduct, deal_type=DealType.SwaptionProduct, table_name="citco_swaption", product_key=( "underlying_security_id", "strike", "expiration", "callput", "birth_date", "death_date", ), insert_ignore=( "id", "dealid", "security_desc", "currency", ), ): underlying_security_id: str = field(metadata={"citco": "UnderlyingSecurityId"}) security_desc: str = field( init=False, metadata={"insert": False, "citco": "Sec_Desc"} ) currency: str = field( init=False, default=None, metadata={"citco": "LocalCcy", "insert": False} ) instrument_type: str = field(metadata={"citco": "InstrumentType"}) callput: bool strike: float = field(metadata={"citco": "StrikePrice"}) expiration: datetime.date = field(metadata={"citco": "ExpirationDate"}) underlying_id_source: str = field( default="RED", metadata={"citco": "UnderlyingIDSource"} ) birth_date: datetime.date = field(default=None, metadata={"citco": "Birth_date"}) death_date: datetime.date = field(default=None, metadata={"citco": "Death_date"}) committed: bool = field(default=False) id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "citco": "UniqueIdentifier"} ) def __post_init__(self): if self.instrument_type == "BNDO": sql_str = "SELECT issue_date, maturity, coupon, index, series FROM index_desc WHERE tenor='5yr' AND redindexcode=%s" with self._conn.cursor() as c: c.execute(sql_str, (self.underlying_security_id.removesuffix("_5"),)) ( self.birth_date, self.death_date, self.coupon, index, series, ) = c.fetchone() self.security_desc = f"{desc_str(index, series, '5')} {self.expiration}-{self.strike}-{'C' if self.callput else 'P'}-{self.birth_date}-{self.death_date}" self.currency = "EUR" if index in ("XO", "EU") else "USD" elif self.instrument_type == "SWPO": self.security_desc = "" self.get_productid() def to_citco(self): if not self.id: self.stage() self.commit() self.get_productid() obj = super().to_citco(action) if ( self.underlying_id_source == "USERID" ): # Implies this is a Interest Rate Swaption irs = IRSProduct( birth_date=self.birth_date, death_date=self.death_date, fixed_rate=self.strike, float_index=self.underlying_security_id, ) irs.citco_stage() obj["UnderlyingSecurityId"] = irs.dealid obj["Command"] = "N" obj["Active"] = "Y" obj["ExpirationDate"] = obj["ExpirationDate"].strftime("%Y%m%d") obj["Put/CallFlag"] = "C" if obj["callput"] else "P" obj["OptionType"] = "Vanilla European" return obj _citco_frequency = {"Yearly": 1, "Daily": 9, "Quarterly": 3} _citco_bdc = {"Modified Following": 4} _citco_daycount = {"ACT/360": 2} _citco_ratesource = {"SOFRRATE": 17819} _citco_cp_isda = { "MSCSNY": "MS_IS", "GOLDNY": "GS_IS", "BAMSNY": "BOA_IS", "BNPBNY": "BNP_IS", "JPCBNY": "JPM_IS", } _citco_cp_cdea = { "MSCSNY": "MS_CD", "GOLDNY": "GS_CD", "BAMSNY": "BOA_CD", "BNPBNY": "BNP_CD", "JPCBNY": "JPM_CD", "CSFBBO": "CS_CD", "CITINY": "CIT_CD", "BARCNY": "BAR_CD", } @dataclass class IRSProduct( Deal, CitcoProduct, deal_type=DealType.IRSProduct, table_name="citco_irs", product_key=("birth_date", "death_date", "float_index", "fixed_rate"), insert_ignore=("id", "dealid", "security_desc"), ): birth_date: datetime.date = field(metadata={"citco": "Birth_date"}) death_date: datetime.date = field(metadata={"citco": "Death_date"}) fixed_rate: float instrument_type: str = field(default="IRS", metadata={"citco": "InstrumentType"}) active: str = field(default=True, metadata={"citco": "Active"}) fixed_daycount: str = field(default="ACT/360") fixed_payment_freq: str = field(default="Yearly") fixed_bdc: str = field(default="Modified Following") float_index: str = field(default="SOFRRATE") float_daycount: str = field(default="ACT/360") float_payment_freq: str = field(default="Yearly") float_bdc: str = field(default="Modified Following") currency: str = field(default="USD", metadata={"citco": "LocalCcy"}) float_fixing_freq: str = field(default="Daily") pay_interest_calc_method: str = field(default="Compound") committed: bool = field(default=False) security_desc: str = field( init=False, metadata={"insert": False, "citco": "Sec_Desc"}, default=None ) id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "citco": "UniqueIdentifier"} ) def __post_init__(self): self.get_productid() self.security_desc = f"SWAP IRS {self.float_index}-{self.fixed_rate}-{self.birth_date}-{self.death_date}" def to_citco(self): if not self.id: self.stage() self.commit() self.get_productid() obj = super().to_citco(action) d = { "S_P_CurrencyCode": self.currency, "S_P_PaymentFreqID": _citco_frequency[self.fixed_payment_freq], "S_P_RateIndexID": 0, "S_P_AccrualMethodID": _citco_daycount[self.fixed_daycount], "S_P_InterestRate": self.fixed_rate, "S_P_DayConventionID": _citco_bdc[self.fixed_bdc], "S_P_ResetFreqID": 0, "S_R_CurrencyCode": self.currency, "S_R_PaymentFreqID": _citco_frequency[self.float_payment_freq], "S_R_RateIndexID": 28, "S_R_AccrualMethodID": _citco_daycount[self.float_daycount], "S_R_InterestRate": 0, "S_R_DayConventionID": _citco_bdc[self.float_bdc], "S_R_ResetFreqID": _citco_frequency[self.float_fixing_freq], "S_R_RateSource": _citco_ratesource[self.float_index], } obj.update(d) obj["Command"] = "N" obj["Active"] = "Y" if obj["Active"] else "N" obj["PrincipalExchTypeID"] = 1 return obj @dataclass class TRSProduct( Deal, CitcoProduct, deal_type=DealType.TRSProduct, table_name="citco_trs", product_key=("birth_date", "death_date", "funding_index", "underlying_security"), insert_ignore=("id", "dealid", "security_desc"), ): birth_date: datetime.date = field(metadata={"citco": "Birth_date"}) death_date: datetime.date = field(metadata={"citco": "Death_date"}) underlying_security: str = field(metadata={"citco": "UnderlyingSecurityId"}) active: str = field(default=True, metadata={"citco": "Active"}) funding_daycount: str = field(default="ACT/360") funding_freq: str = field(default="Quarterly") funding_payment_roll_convention: str = field(default="Modified Following") asset_daycount: str = field(default="ACT/360") asset_freq: str = field(default="Quarterly") asset_payment_roll_convention: str = field(default="Modified Following") currency: str = field(default="USD", metadata={"citco": "LocalCcy"}) interest_calc_method: str = field(default="Compound") compound_avg_frequency: str = field(default="Daily") fixing_frequency: str = field(default="Daily") committed: bool = field(default=False) instrument_type: str = field(default="TRS", metadata={"citco": "InstrumentType"}) funding_index: str = field(default="SOFRRATE", metadata={}) security_desc: str = field( init=False, metadata={"insert": False, "citco": "Sec_Desc"}, default=None ) id: int = field(default=None, metadata={"insert": False}) dealid: str = field( default=None, metadata={"insert": False, "citco": "UniqueIdentifier"} ) def __post_init__(self): self.get_productid() _citco_trs = {"4J623JAA8": "IBOXHY_TRS"} self.security_desc = f"{_citco_trs[self.underlying_security]}-{self.funding_index}-{self.birth_date}-{self.death_date}" def to_citco(self): if not self.id: self.stage() self.commit() self.get_productid() obj = super().to_citco(action) d = { f"S_P_CurrencyCode": self.currency, f"S_P_PaymentFreqID": _citco_frequency[self.funding_freq], f"S_P_RateIndexID": 28, f"S_P_AccrualMethodID": _citco_daycount[self.funding_daycount], f"S_P_InterestRate": 0, f"S_P_PaymentCalandarID": 3, f"S_P_DayConventionID": _citco_bdc[self.funding_payment_roll_convention], f"S_P_ResetFreqID": _citco_frequency[self.funding_freq], f"S_P_RateSourceID": _citco_ratesource[self.funding_index], f"S_R_CurrencyCode": self.currency, f"S_R_PaymentFreqID": _citco_frequency[self.asset_freq], f"S_R_RateIndexID": 0, f"S_R_AccrualMethodID": _citco_daycount[self.asset_daycount], f"S_R_InterestRate": 0, f"S_R_PaymentCalandarID": 3, f"S_R_DayConventionID": _citco_bdc[self.asset_payment_roll_convention], f"S_R_ResetFreqID": _citco_frequency[self.asset_freq], f"S_R_RateSourceID": 0, } obj.update(d) obj["Command"] = "N" obj["Active"] = "Y" if obj["Active"] else "N" obj["GeneralDirection"] = "F" obj["PrincipalExchTypeID"] = 3 obj["UnderlyingIDSource"] = "RED" return obj