diff options
Diffstat (limited to 'python/ops')
| -rw-r--r-- | python/ops/__init__.py | 0 | ||||
| -rw-r--r-- | python/ops/file_gen.py | 273 | ||||
| -rw-r--r-- | python/ops/funds.py | 17 | ||||
| -rw-r--r-- | python/ops/process_queue.py | 445 | ||||
| -rw-r--r-- | python/ops/trade_dataclasses.py | 1899 |
5 files changed, 2628 insertions, 6 deletions
diff --git a/python/ops/__init__.py b/python/ops/__init__.py new file mode 100644 index 00000000..e69de29b --- /dev/null +++ b/python/ops/__init__.py diff --git a/python/ops/file_gen.py b/python/ops/file_gen.py new file mode 100644 index 00000000..1986ba0d --- /dev/null +++ b/python/ops/file_gen.py @@ -0,0 +1,273 @@ +import datetime +from serenitas.utils.misc import rename_keys + +from pyisda.date import previous_twentieth +from quantlib.time.api import pydate_from_qldate, UnitedStates, Days, Date +from serenitas.analytics.dates import bus_day +from .headers import get_headers + + +def get_effective_date(d, swaption_type): + if swaption_type == "CD_INDEX_OPTION": + return previous_twentieth(d + datetime.timedelta(days=1)) + else: + cal = UnitedStates() + return pydate_from_qldate(cal.advance(Date.from_datetime(d), 2, Days)) + + +_client_name = {"SERCGMAST": "Serenitas", "BOWDST": "HEDGEMARK", "BRINKER": "LMCG"} + + +def build_line(obj, trade_type="bond", fund="SERCGMAST"): + obj["Client"] = _client_name[fund] + # Bowdst Globeop has a special short name + obj["fund"] = "BOS_PAT_BOWDOIN" if fund == "BOWDST" else fund + obj["State"] = "Valid" + rename_cols = { + "fund": "Fund", + "action": "Action", + "dealid": "Deal Id", + "folder": "Folder", + "custodian": "Custodian", + "cash_account": "Cash Account", + "cp_code": "Counterparty", + "identifier": "GlopeOp Security Identifier", + "cusip": "CUSIP", + "isin": "ISIN", + "description": "Security Description", + "accrued": "Accrued", + "price": "Price", + "faceamount": "FaceAmount", + "trade_date": "Trade Date", + "settle_date": "Settlement Date", + "effective_date": "EffectiveDate", + "maturity": "MaturityDate", + "currency": "Currency", + "fixed_rate": "FixedRate", + "payment_rolldate": "PaymentRollDateConvention", + "day_count": "DayCount", + "protection": "Protection", + "security_id": "UnderlyingSecurityId", + "security_desc": "UnderlyingSecurityDescription", + "upfront": "UpfrontFee", + "upfront_settle_date": "UpfrontFeePayDate", + "swap_type": "SwapType", + "orig_attach": "AttachmentPoint", + "orig_detach": "ExhaustionPoint", + "clearing_facility": "Clearing Facility", + "isda_definition": "ISDADefinition", + "expiration_date": "ExpirationDate", + "portfolio": "Portfolio", + "settlement_type": "SettlementMode", + "principal_payment": "PrincipalPayment", + "accrued_payment": "AccruedPayment", + "current_face": "CurrentFace", + } + rename_cols[ + "curr_notional" if fund in ("SERCGMAST", "BOWDST") else "notional" + ] = "Notional" + rename_keys(obj, rename_cols) + if trade_type in ("bond", "swaption", "future"): + obj["Transaction Indicator"] = "Buy" if obj["buysell"] else "Sell" + if trade_type == "bond": + obj["Deal Type"] = "MortgageDeal" + obj["Portfolio"] = "MORTGAGES" + obj["Delivery"] = "S" + # zero coupon bond + if obj["CUSIP"] != obj["GlopeOp Security Identifier"]: + obj["CUSIP"] = None + elif trade_type == "swaption": + obj["Deal Type"] = "SwaptionDeal" + obj["ExerciseType"] = "European" + rename_keys( + obj, + { + "Settlement Date": "PremiumSettlementDate", + "notional": "Notional", + "initial_margin_percentage": "InitialMarginPercentage", + }, + ) + obj["PremiumSettlementAmount"] = ( + obj["Price"] * obj["Notional"] * obj.get("factor", 1.0) * 0.01 + ) + obj["PremiumSettlementCurrency"] = obj["Currency"] + obj["RegenerateCashFlow"] = "N" + for direction in ["Pay", "Receive"]: + obj[direction + "MaturityDate"] = obj["MaturityDate"] + obj[direction + "Currency"] = obj["Currency"] + obj[direction + "Notional"] = obj["Notional"] + obj[direction + "EffectiveDate"] = get_effective_date( + obj["ExpirationDate"], obj["SwapType"] + ) + if obj["SwapType"] == "CD_INDEX_OPTION": + for direction in ["Pay", "Receive"]: + obj[direction + "Daycount"] = "ACT/360" + obj[direction + "Frequency"] = "Quarterly" + obj[direction + "PaymentRollConvention"] = "Following" + + for leg_type in ["Receive", "Pay"]: + obj[leg_type + "LegRateType"] = "Fixed" + if obj["option_type"] == "PAYER": + obj["ReceiveFixedRate"] = 0.0 + obj["PayFixedRate"] = obj["FixedRate"] + elif obj["option_type"] == "RECEIVER": + obj["PayFixedRate"] = 0.0 + obj["ReceiveFixedRate"] = obj["FixedRate"] + elif obj["SwapType"] == "SWAPTION": + for direction in ["Pay", "Receive"]: + obj[direction + "PaymentRollConvention"] = "ModifiedFollowing" + if obj["option_type"] == "RECEIVER": + fixed, floating = "Receive", "Pay" + else: + fixed, floating = "Pay", "Receive" + # fixed leg + obj[fixed + "Frequency"] = "Yearly" + obj[fixed + "Daycount"] = "ACT/360" + obj[fixed + "FixedRate"] = obj["strike"] + obj[fixed + "LegRateType"] = "Fixed" + obj[fixed + "InterestCalcMethod"] = "Simple Interest" + # floating leg + obj[floating + "Frequency"] = "Yearly" + obj[floating + "Daycount"] = "ACT/360" + obj[floating + "LegRateType"] = "Float" + obj[floating + "FloatRate"] = "SOFRINDX" + obj[floating + "InterestCalcMethod"] = "Simple Interest" + + else: + raise ValueError( + "'SwapType' needs to be one of 'CD_INDEX_OPTION' or 'SWAPTION'" + ) + + obj["PremiumCurrency"] = obj["Currency"] + if obj["InitialMarginPercentage"]: + obj["InitialMarginCurrency"] = obj["Currency"] + obj["UnderlyingInstrument"] = obj.pop("UnderlyingSecurityId") + if obj["SwapType"] == "CD_INDEX_OPTION": + obj["Strike"] = obj.pop("strike") + + elif trade_type == "cds": + freq = {4: "Quarterly", 12: "Monthly"} + obj["Deal Type"] = "CreditDefaultSwapDeal" + obj["PaymentFrequency"] = freq[obj["frequency"]] + obj["InitialMarginPercentage"] = obj.pop("initial_margin_percentage") + if obj["InitialMarginPercentage"]: + obj["InitialMarginCurrency"] = obj["Currency"] + if obj["Clearing Facility"] is None: + obj["Clearing Facility"] = "NOT CLEARED" + + elif trade_type == "future": + obj["Deal Type"] = "FutureDeal" + rename_keys( + obj, + { + "commission": "Commission", + "quantity": "Quantity", + "swap_type": "Swap Type", + "bbg_ticker": "Bloomberg Ticker", + "Currency": "Trade Currency", + "exchange": "Exchange", + }, + ) + elif trade_type == "wire": + obj["Deal Type"] = "CashFlowDeal" + obj["Transaction Type"] = "Transfer" + obj["Instrument Type"] = "Cashflow" + obj["Settlement Date"] = obj["Trade Date"] + strat_portfolio_map = { + "IGOPTDEL": "OPTIONS", + "COCSH": "OPTIONS", + "IGINX": "TRANCHE", + "BSPK": "TRANCHE", + "TCSH": "TRANCHE", + "SER_ITRXCURVE": "SERG__CURVE", + "XCURVE": "SERG__CURVE", + "M_CSH_CASH": "CASH", + "CVECSH": "SERG__CURVE", + "SER_ITRXCVCSH": "SERG__CURVE", + } + obj["Portfolio"] = strat_portfolio_map.get(obj["Folder"]) + rename_keys(obj, {"amount": "Amount"}) + + elif trade_type == "spot": + standard_settle = (obj["Trade Date"] + 2 * bus_day).date() + if obj["Settlement Date"] > standard_settle: + obj["Deal Type"] = "ForwardDeal" + fx_rate = "Forward Rate" + else: + obj["Deal Type"] = "SpotDeal" + fx_rate = "Spot Rate" + rename_keys( + obj, + { + "commission": "Commission", + "commission_currency": "Commission Currency", + "sell_currency": "Sell Currency", + "sell_amount": "Sell Amount", + "buy_currency": "Buy Currency", + "buy_amount": "Buy Amount", + "spot_rate": fx_rate, + }, + ) + elif trade_type == "fx_swap": + obj["Deal Type"] = "FxSwapDeal" + obj["Action"] = "NEW" + rename_keys( + obj, + { + "near_rate": "Near Side Currency Rate", + "near_settle_date": "Near Side Settlement Date", + "near_buy_currency": "Near Side Buy Currency", + "near_buy_amount": "Near Side Buy Amount", + "near_sell_currency": "Near Side Sell Currency", + "near_sell_amount": "Near Side Sell Amount", + "far_rate": "Far Side Rate", + "far_settle_date": "Far Side Settlement Date", + "far_buy_currency": "Far Side Buy Currency", + "far_buy_amount": "Far Side Buy Amount", + "far_sell_currency": "Far Side Sell Currency", + "far_sell_amount": "Far Side Sell Amount", + }, + ) + elif trade_type == "repo": + obj["Deal Type"] = "RepoDeal" + obj["OpenRepo"] = "Y" if obj["open_repo"] else "N" + rename_keys( + obj, + { + "weighted_amount": "WeightedAmount", + "repo_rate": "RepoRate", + "transaction_indicator": "TransactionIndicator", + }, + ) + elif trade_type == "capfloor": + obj["Deal Type"] = "CapFloorDeal" + obj["PaymentBDC"] = obj["bdc_convention"] + obj["AccrualBDC"] = obj["bdc_convention"] + obj["MaturityBDC"] = "NONE" + obj["TransactionIndicator"] = "Buy" if obj["buysell"] else "Sell" + # missing data : 'Adjusted', 'RollConvention', 'Calendar', 'Arrears', 'Collateralized', 'MaturityBDC' + rename_keys( + obj, + { + "comments": "Comments", + "floating_rate_index_desc": "FloatingRateIndex", + "cap_or_floor": "CapOrFloor", + "amount": "Notional", + "strike": "Strike", + "value_date": "ValueDate", + "expiration_date": "MaturityDate", + "premium_percent": "PremiumPercent", + "pricing_type": "PricingType", + "payment_frequency": "PaymentFrequency", + "fixing_frequency": "FixingFrequency", + "day_count_convention": "Basis", + "bdc_convention": "PaymentBDC", + "payment_mode": "PaymentMode", + "payment_at_beginning_or_end": "PaymentAtBeginningOrEnd", + "initial_margin_percentage": "InitialMarginPercentage", + "intial_margin_currency": "InitialMarginCurrency", + "reset_lag": "ResetLag", + "swap_type": "SwapType", + }, + ) + return obj diff --git a/python/ops/funds.py b/python/ops/funds.py index bfc3750d..aaea047e 100644 --- a/python/ops/funds.py +++ b/python/ops/funds.py @@ -5,6 +5,8 @@ from serenitas.utils.remote import FtpClient, SftpClient from serenitas.utils.exchange import ExchangeMessage, FileAttachment from io import StringIO from typing import Tuple, Union +from serenitas.utils.env import DAILY_DIR +from .file_gen import get_headers, build_line class Fund: @@ -23,8 +25,11 @@ class Fund: def build_buffer(cls, trade_type): buf = StringIO() csvwriter = csv.writer(buf) - csvwriter.writerow(get_headers(trade_type, cls.name)) - csvwriter.writerows(cls.staged_queue) + headers = get_headers(trade_type, cls.name) + csvwriter.writerow(headers) + csvwriter.writerows( + [[obj.get(h) for h in headers] for obj in cls.stating_queue] + ) buf = buf.getvalue().encode() dest = cls.get_filepath(DAILY_DIR, trade_type) dest.parent.mkdir(exist_ok=True) @@ -36,8 +41,8 @@ class Fund: cls.headers = get_headers(trade_type, cls.name) @classmethod - def stage(cls, trade, trade_type): - cls.staged_queue.append(build_line(trade, trade_type, cls.name)) + def stage(cls, trade, *, trade_type, **kwargs): + cls.stating_queue.append(build_line(trade, trade_type, cls.name)) @classmethod def get_filepath( @@ -110,5 +115,5 @@ class Bowdst(Fund, fund_name="BOWDST"): class Selene(Fund, fund_name="ISOSEL"): @classmethod - def stage(cls, trade, action="NEW"): - cls.staged_queue.append(trade.to_citco(action)) + def stage(cls, trade, *, action="NEW", **kwargs): + cls.stating_queue.append(trade.to_citco(action)) diff --git a/python/ops/process_queue.py b/python/ops/process_queue.py new file mode 100644 index 00000000..8d88bda3 --- /dev/null +++ b/python/ops/process_queue.py @@ -0,0 +1,445 @@ +import argparse +import blpapi +import logging +import psycopg +import pathlib +import re +import redis +import sys + +from serenitas.analytics.api import CreditIndex + +try: + from serenitas.utils.env import DAILY_DIR +except KeyError: + sys.exit("Please set path of daily directory in 'SERENITAS_DAILY_DIR'") + +from collections import defaultdict +from pickle import dumps, loads +from serenitas.analytics.bbg_helpers import init_bbg_session, retrieve_data + +from serenitas.utils import get_redis_queue +from tabulate import tabulate +from .funds import Fund +from .trade_dataclasses import DealKind + + +def groupby(p: redis.client.Pipeline, key: str, trade_key: str): + d = defaultdict(list) + for buf in p.lrange(key, 0, -1): + trade = loads(buf) + d[trade[trade_key]].append(trade) + return d + + +def get_trades(p: redis.client.Pipeline, key: str): + for tradeid, trades in groupby(p, key, "id").items(): + if len(trades) == 1: + yield trades[0] + else: + if trades[-1]["action"] == "CANCEL": + continue + if trades[0]["action"] == "NEW": + trades[-1]["action"] = "NEW" + yield trades[-1] + if trades[-1]["action"] == "UPDATE": + yield trades[-1] + + +def process_indicative( + p: redis.client.Pipeline, + trade_type: str, + upload: bool, + session: blpapi.session.Session, + conn: psycopg.Connection, +) -> None: + process_fun = globals().get( + f"{trade_type}_trade_process", lambda conn, session, trade: trade + ) + for trade in get_trades(p, trade_type): + process_fun(conn, session, trade) + fund = trade["fund"] + if trade.get("upload", True) and ( + fund in ("SERCGMAST", "BOWDST") or trade_type in ("cds", "swaption") + ): + p.rpush(f"{trade_type}_upload", dumps(trade)) + if trade.get("swap_type", None) in ( + "CD_INDEX_OPTION", + "CD_INDEX_TRANCHE", + "BESPOKE", + ): + DealKind[trade_type].from_dict(**trade).mtm_stage() + if Deal := DealKind[trade_type]: + Deal.mtm_upload() + p.delete(trade_type) + + +def process_upload( + p: redis.client.Pipeline, + trade_type: str, + upload: bool, +) -> None: + key = f"{trade_type}_upload" + for fund_name, l in groupby(p, key, "fund").items(): + fund = Fund[fund_name]() + for trade in l: + fund.stage(trade, trade_type=trade_type) + buf, dest = fund.build_buffer(trade_type) + if upload: + fund.upload(buf, dest.name) + p.delete(key) + + +def terminate_list( + p: redis.client.Pipeline, + key: str, + upload: bool, + conn: psycopg.connection, + base_dir: pathlib.Path = DAILY_DIR, +): + trade_type, fund, _ = key.split("_") + terms = [] + for term in p.lrange(key, 0, -1): + termination = loads(term) + DealKind["termination"].from_dict(**termination).mtm_stage() + try: + terms.append(termination.to_globeop()) + except TypeError as e: + logging.error(e) + return + DealKind["termination"].mtm_upload() + if upload and terms: + f = Fund[fund]() + f.staging_queue = terms + dest = f.get_filepath(base_dir, (trade_type, "A")) + buf = f.build_buffer("termination") + f.upload(buf, dest) + p.delete(key) + + +def get_bbg_data( + conn, + session, + identifier, + cusip=None, + isin=None, + settle_date=None, + asset_class=None, + **kwargs, +): + fields = ["MTG_FACTOR_SET_DT", "INT_ACC", "ISSUER"] + fields_dict = { + "Mtge": ["MTG_FACE_AMT", "START_ACC_DT"], + "Corp": ["AMT_ISSUED", "PREV_CPN_DT"], + } + with conn.cursor() as c: + c.execute( + "SELECT identifier FROM securities WHERE identifier=%s", (identifier,) + ) + if not c.fetchone(): + fields += [ + "MATURITY", + "CRNCY", + "NAME", + "FLOATER", + "FLT_SPREAD", + "CPN", + "CPN_TYP", + "CPN_FREQ", + "FIRST_CPN_DT", + "MTG_PAY_DELAY", + "DAY_CNT_DES", + "NOMINAL_PAYMENT_DAY", + "ISSUE_DT", + "RESET_IDX", + "ID_BB_GLOBAL", + ] + + cusip_or_isin = cusip or isin + for bbg_type in ["Mtge", "Corp"]: + bbg_id = cusip_or_isin + " " + bbg_type + data = retrieve_data( + session, + [bbg_id], + fields + fields_dict[bbg_type], + overrides={"SETTLE_DT": settle_date} if settle_date else None, + ) + if data[bbg_id]: + break + else: + logging.error(f"{cusip_or_isin} not in bloomberg") + return + + bbg_data = data[bbg_id] + if bbg_data.get("MTG_FACTOR_SET_DT", 0) == 0: + bbg_data["MTG_FACTOR_SET_DT"] = 1 + bbg_data["INT_ACC"] = 0 + if len(fields) > 3: # we don't have the data in the securities table + sql_fields = [ + "identifier", + "cusip", + "isin", + "description", + "face_amount", + "maturity", + "floater", + "spread", + "coupon", + "frequency", + "day_count", + "first_coupon_date", + "pay_delay", + "currency", + "bbg_type", + "asset_class", + "start_accrued_date", + "issuer", + "reset_index", + "coupon_type", + "payment_day", + "issue_date", + "figi", + ] + placeholders = ",".join(["%s"] * len(sql_fields)) + columns = ",".join(sql_fields) + + sqlstr = ( + f"INSERT INTO securities({columns}) VALUES({placeholders}) " + "ON CONFLICT (identifier) DO NOTHING" + ) + isfloater = bbg_data["FLOATER"] == "Y" + pay_delay = bbg_data.get("MTG_PAY_DELAY", 0) + day_count = bbg_data.get("DAY_CNT_DES") + if m := re.match(r"[^(\s]+", day_count): + day_count = m.group(0) + if isinstance(pay_delay, str): + pay_delay = int(pay_delay.split(" ")[0]) + with conn.cursor() as c: + c.execute( + sqlstr, + ( + identifier, + cusip, + isin, + bbg_data["NAME"], + bbg_data.get("MTG_FACE_AMT") or bbg_data.get("AMT_ISSUED"), + bbg_data.get("MATURITY"), + isfloater, + bbg_data.get("FLT_SPREAD") if isfloater else None, + bbg_data.get("CPN") if not isfloater else None, + bbg_data.get("CPN_FREQ"), + day_count, + bbg_data.get("FIRST_CPN_DT"), + pay_delay, + bbg_data.get("CRNCY"), + bbg_type, + asset_class, + bbg_data.get("START_ACC_DT") or bbg_data.get("PREV_CPN_DT"), + bbg_data["ISSUER"], + bbg_data.get("RESET_IDX"), + bbg_data["CPN_TYP"], + bbg_data["NOMINAL_PAYMENT_DAY"], + bbg_data["ISSUE_DT"], + bbg_data["ID_BB_GLOBAL"], + ), + ) + conn.commit() + return bbg_data + + +def bond_trade_process(conn, session, trade): + bbg_data = get_bbg_data(conn, session, **trade) + currentface = trade["CurrentFace"] = ( + trade["faceamount"] * bbg_data["MTG_FACTOR_SET_DT"] + ) + accrued_payment = trade["AccruedPayment"] = ( + bbg_data["INT_ACC"] * currentface / 100.0 + ) + principal_payment = trade["PrincipalPayment"] = currentface * trade["price"] / 100.0 + if trade["accrued"] is None: + trade["accrued"] = bbg_data["INT_ACC"] + else: + if trade["accrued"] != bbg_data["INT_ACC"]: + logging.error( + f"{trade['accrued']} does not match bbg amount of {bbg_data['INT_ACC']}" + ) + + with conn.cursor() as c: + c.execute( + "UPDATE bonds SET principal_payment = %s, accrued_payment = %s, accrued=%s " + "WHERE id = %s", + (principal_payment, accrued_payment, trade["accrued"], int(trade["id"])), + ) + # mark it at buy price + if trade["buysell"]: + sqlstr = "INSERT INTO marks VALUES(%s, %s, %s) ON CONFLICT DO NOTHING" + with conn.cursor() as c: + c.execute( + sqlstr, (trade["trade_date"], trade["identifier"], trade["price"]) + ) + conn.commit() + return trade + + +def is_tranche_trade(trade): + return trade["swap_type"] in ("CD_INDEX_TRANCHE", "BESPOKE") + + +def swaption_trade_process(conn, session, trade): + sqlstr = ( + "SELECT indexfactor/100 FROM index_version " + "WHERE redindexcode=%(security_id)s" + ) + try: + with conn.cursor() as c: + c.execute(sqlstr, trade) + (factor,) = c.fetchone() + except ValueError as e: + logging.error(e) + return trade + except TypeError: + # factor missing, probably IR swaption + pass + else: + trade["factor"] = factor + finally: + if trade["option_type"] == "RECEIVER": + trade["OptionType"] = "Call" + elif trade["option_type"] == "PAYER": + trade["OptionType"] = "Put" + return trade + + +def cds_trade_process(conn, session, trade): + sqlstr = ( + "SELECT indexfactor/100 FROM index_version " + "WHERE redindexcode=%(security_id)s" + ) + try: + with conn.cursor() as c: + c.execute(sqlstr, trade) + (factor,) = c.fetchone() + except ValueError: + bbg_data = get_bbg_data( + conn, + session, + trade["security_id"], + isin=trade["security_id"], + asset_class="Subprime", + ) + + factor = bbg_data["MTG_FACTOR_SET_DT"] + if is_tranche_trade(trade): + tranche_factor = (trade["attach"] - trade["detach"]) / ( + trade["orig_attach"] - trade["orig_detach"] + ) + trade["curr_notional"] = trade["notional"] * tranche_factor + trade["Factor"] = tranche_factor + else: + trade["curr_notional"] = trade["notional"] * factor + trade["Factor"] = factor + if trade["upfront"]: + return trade + index = CreditIndex( + redcode=trade["security_id"], + maturity=trade["maturity"], + notional=trade["notional"], + value_date=trade["trade_date"], + ) + index.direction = trade["protection"] + with conn.cursor() as c: + if trade["traded_level"]: + if not is_tranche_trade(trade): + index.ref = float(trade["traded_level"]) + trade["upfront"] = -index.pv + else: + accrued = index._accrued * trade["fixed_rate"] + match index.index_type: + case "HY": + dirty_price = float(trade["traded_level"]) + accrued + trade["upfront"] = ( + -(100 - dirty_price) + * index.notional + * trade["Factor"] + * 0.01 + ) + case "EU" | "XO" if trade["orig_attach"] in (6, 12, 35): + if trade["orig_attach"] == 6: + index.recovery = 0.0 + index.spread = float(trade["traded_level"]) + trade["upfront"] = ( + -index._pv * trade["notional"] * trade["Factor"] + ) + case _: + dirty_protection = float(trade["traded_level"]) - accrued + trade["upfront"] = ( + -dirty_protection * index.notional * trade["Factor"] * 0.01 + ) + c.execute( + "UPDATE cds SET upfront=%s WHERE dealid=%s", + (trade["upfront"], trade["dealid"]), + ) + + else: + index.pv = -trade["upfront"] + trade["traded_level"] = index.ref + c.execute( + "UPDATE cds SET traded_level=%s WHERE dealid=%s", + (trade["traded_level"], trade["dealid"]), + ) + conn.commit() + return trade + + +def print_trade(trade): + d = trade.copy() + d["buysell"] = "Buy" if d["buysell"] else "Sell" + return tabulate((k, v) for k, v in d.items()) + + +if __name__ == "__main__": + import os + + os.environ["SERENITAS_APP_NAME"] = "process_queue" + from functools import partial + from serenitas.utils.pool import dawn_pool + + parser = argparse.ArgumentParser() + parser.add_argument( + "-n", "--no-upload", action="store_true", help="do not upload to Globeop" + ) + args = parser.parse_args() + r = get_redis_queue() + with dawn_pool.connection() as conn, init_bbg_session() as session: + for trade_type in [ + "cds", + "swaption", + "repo", + "future", + "wire", + "spot", + "fx_swap", + "capfloor", + ]: + p_list = partial( + process_indicative, + trade_type=trade_type, + upload=not args.no_upload, + session=session, + conn=conn, + ) + r.transaction(p_list, trade_type) + p_upload = partial( + process_upload, + trade_type=trade_type, + upload=not args.no_upload, + ) + r.transaction(p_upload, trade_type) + + for trade_type in ("cds", "swaption", "capfloor"): + for fund in ("SERCGMAST", "BOWDST", "BRINKER"): + key = f"{trade_type}_{fund}_termination" + t_list = partial( + terminate_list, key=key, upload=not args.no_upload, conn=conn + ) + r.transaction(t_list, key) diff --git a/python/ops/trade_dataclasses.py b/python/ops/trade_dataclasses.py new file mode 100644 index 00000000..c3d732e1 --- /dev/null +++ b/python/ops/trade_dataclasses.py @@ -0,0 +1,1899 @@ +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 |