diff options
| -rw-r--r-- | python/position_file_bowdst.py | 253 | ||||
| -rw-r--r-- | python/report_ops/__main__.py | 20 | ||||
| -rw-r--r-- | python/report_ops/misc.py | 5 | ||||
| -rw-r--r-- | python/report_ops/sma.py | 233 |
4 files changed, 294 insertions, 217 deletions
diff --git a/python/position_file_bowdst.py b/python/position_file_bowdst.py index 7e2ecc07..3fcc1d7d 100644 --- a/python/position_file_bowdst.py +++ b/python/position_file_bowdst.py @@ -1,224 +1,51 @@ -import datetime -import csv -from io import StringIO -from serenitas.utils.misc import rename_keys -from serenitas.utils.remote import SftpClient -from serenitas.utils.env import DAILY_DIR -from pandas.tseries.offsets import MonthEnd +from report_ops.sma import build_position_file +import argparse +from serenitas.utils.remote import Client from serenitas.utils.exchange import ExchangeMessage, FileAttachment -from csv_headers.globeop_upload import POSITION_HEADERS +from report_ops.misc import _recipients, _cc_recipients +import datetime from serenitas.analytics.dates import prev_business_day -_otc_queries = { - "Tranche": "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, trb.notional * trb.tranche_factor as active_notional, cds.*, COALESCE(nextredindexcode, security_id) AS redindexcode FROM tranche_risk_bowdst trb left join cds on trade_id=id LEFT JOIN index_version_markit ivm ON security_id=redindexcode WHERE date=%s;", - "CDXSwaption": "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav as nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, 'BOWDST') spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", - "IRSwaption": "SELECT abs(spr.notional) AS active_notional, spr.nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, 'BOWDST') spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", - "CDX": "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, 'BOWDST') cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;", -} - - -def build_line(obj, asset_type): - return [obj.get(h, None) for h in POSITION_HEADERS[asset_type]] - - -def process_upload(positions, upload): - attachments = [] - if upload: - sftp = SftpClient.from_creds("hm_globeop", folder="incoming") - for asset_type, trades in positions.items(): - buf = StringIO() - csvwriter = csv.writer(buf) - csvwriter.writerow(POSITION_HEADERS[asset_type]) - csvwriter.writerows(build_line(trade, asset_type) for trade in trades) - buf = buf.getvalue().encode() - timestamp = datetime.datetime.now() - fname = f"HEDGEMARK.POSITION.BOS_PAT_BOWDOIN.{timestamp:%Y%m%d.%H%M%S}.{asset_type.capitalize()}Deal.PositionsAsOf{args.date}.csv" - if upload: - sftp.put(buf, fname) - base_dir = DAILY_DIR / f"{timestamp:%Y-%m-%d}" - base_dir.mkdir(exist_ok=True, parents=True) - dest = base_dir / fname - dest.write_bytes(buf) - attachments.append(FileAttachment(name=fname, content=buf)) +def main(cob, fund, upload): + buf, dest = build_position_file( + cob, fund, ["bond", "future", "tranche", "ir_swaption", "cdx_swaption"] + ) if upload: + client = Client.from_creds("hm_globeop") + client.put(buf, dest.name) em = ExchangeMessage() - recipients = ( - "hm-operations@bnymellon.com", - "hedgemark.lmcg.ops@sscinc.com", - "Hedgemark.OTC@sscinc.com", - "catherine.porter@bnymellon.com", - "shkumar@sscinc.com", - ) - cc_recipients = ("bowdoin-ops@lmcg.com",) - subject = f"Position_files for Bowdoin Street as of {args.date}" - body = f"Please see monthend positions for Bowdoin Street as of {args.date}. They have been uploaded to the SFTP as well." em.send_email( - subject, body, recipients, cc_recipients=cc_recipients, attach=attachments - ) - - -def positions_bond(conn, date): - with conn.cursor() as c: - c.execute("SELECT * FROM risk_positions(%s, null, 'BOWDST') ", (date,)) - for row in c: - obj = row._asdict() - rename_keys( - obj, - { - "identifier": "CUSIP", - "description": "SecurityDescription", - "notional": "Position", - "price": "MarketPrice", - "local_market_value": "Local Market Value", - "usd_market_value": "Base Market Value", - }, - ) - try: - obj["Fx Rate"] = obj["Local Market Value"] / obj["Base Market Value"] - except ZeroDivisionError: - obj["Fx Rate"] = 1 - obj["AccountNumber"] = "319478" - obj["Prime Broker"] = "BONY" - obj["COB Date"] = date - obj["Currency"] = "USD" - obj["SecurityType"] = "Bond" - obj["CurrentFace"] = obj["Position"] * obj["factor"] - yield obj - - -def positions_future(conn, date): - with conn.cursor() as c: - c.execute( - "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures " - "WHERE fund='BOWDST' AND trade_date <= %s) " - "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';", - (date,), + subject=f"Position_files for Bowdoin Street as of {cob}", + body=f"Please see monthend positions for Bowdoin Street as of {cob}. They have been uploaded to the SFTP as well.", + to_recipients=_cc_recipients[fund], + cc_recipients=_cc_recipients[fund], + reply_to=_cc_recipients[fund], + attach=[FileAttachment(name=dest.name, content=buf)], ) - for row in c: - obj = row._asdict() - rename_keys( - obj, - { - "bbg_ticker": "BBGTicker", - "notional": "Quantity", - "cp_code": "Prime Broker", - "cash_account": "AccountNumber", - "security_desc": "SecurityDescription", - "currency": "Currency", - "maturity": "MaturityDate", - }, - ) - obj["COB Date"] = date - obj["SecurityType"] = "Futures" - yield obj -def _otc_serialize(obj, product_type, date): - rename_keys( - obj, - { - "dealid": "Unique Deal ID", - "cp_code": "Counterparty", - "currency": "DealCurrencyA", - "active_notional": "NotionalA", - "fixed_rate": "FixedRate", - "trade_date": "Start Date", - "effective_date": "EffectiveDate", - "maturity": "Maturity Date", - "security_id": "Underlying (ISIN / CUSP / RED CODES)", - "security_desc": "Underlying Desc", - "mtm": "MTM Valuation", - "strike": "Strike", - "annexdate": "EffectiveDate", - "expiration_date": "Underlying Maturity", - "nav": "MTM Valuation", - }, - ) - data = { - "Client Name": "HEDGEMARK", - "Fund Name": "BOS_PAT_BOWDOIN", - "Product Type": "Credit Index Tranche", - "MTM Currency": "USD", - "COB Date": date, - } - obj.update(data) - if product_type == "Tranche": - obj["Underlying (ISIN / CUSP / RED CODES)"] = obj["redindexcode"] - obj["Product Type"] = "Credit Index Tranche" - obj["TransactionIndicator (Buy/Sell)"] = ( - "B" if obj["protection"] == "Buyer" else "S" - ) - elif product_type in ("CDXSwaption", "IRSwaption"): - obj["Product Type"] = ( - "CD Swaption" if product_type == "CDXSwaption" else "Swaption" - ) - obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["buysell"] else "S" - obj["PutCall Indicator (Call/Put)"] = ( - "P" if obj["option_type"] == "PAYER" else "C" - ) - obj["Exercise Type"] = "European" - elif product_type == "CDX": - obj["Product Type"] = "Credit Index" - obj["Counterparty"] = "GS" - obj["Unique Deal ID"] = obj[ - "Underlying (ISIN / CUSP / RED CODES)" - ] # Different from rest, we will override - obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["notional"] > 0 else "S" - obj["DealCurrencyA"] = "EUR" if obj["index"] in ("EU", "XO") else "USD" - obj["NotionalA"] = abs(obj["notional"]) * obj["factor"] - obj["Start Date"] = date - obj["MTM Valuation"] = obj["clean_nav"] + obj["accrued"] - obj["Clearing House Name"] = "ICE" - obj["FixedRate"] = obj["coupon"] * 100 - obj["Effective Date"] = obj["effectivedate"] - return obj - - -def positions_otc(conn, date): - with conn.cursor() as c: - for product_type, sql_query in _otc_queries.items(): - c.execute(sql_query, (date,)) - for row in c: - yield _otc_serialize(row._asdict(), product_type, date) - - -if __name__ == "__main__": - import argparse - from serenitas.utils.db import dbconn - - parser = argparse.ArgumentParser( - description="Generate position files for Bowdoin Street" - ) - parser.add_argument( - "date", - nargs="?", - type=datetime.date.fromisoformat, - default=prev_business_day((datetime.date.today().replace(day=1))), - ) - parser.add_argument( - "--product", - nargs="+", - choices=["bond", "future", "otc"], - default=["bond", "future", "otc"], - help="list of products to generate position files for", - ) - parser.add_argument( - "--no-upload", - "-n", - action="store_true", - default=False, - help="uploads to globeop", - ) - args = parser.parse_args() - conn = dbconn("dawndb") - positions = { - p: list(globals()[f"positions_{p}"](conn, args.date)) for p in args.product - } - if ( - not prev_business_day(datetime.date.today()) == args.date and not args.no_upload - ): # We only want to upload if the previous business day was monthend - pass - else: - process_upload(positions, not args.no_upload) +parser = argparse.ArgumentParser( + description="Generate position files for Bowdoin Street" +) +parser.add_argument( + "date", + nargs="?", + type=datetime.date.fromisoformat, + default=prev_business_day((datetime.date.today().replace(day=1))), +) +parser.add_argument( + "--no-upload", + "-n", + action="store_true", + default=False, + help="uploads to globeop", +) +args = parser.parse_args() +if ( + not prev_business_day(datetime.date.today()) == args.date and not args.no_upload +): # We only want to upload if the previous business day was monthend + pass +else: + main(args.date, "BOWDST", not args.no_upload) diff --git a/python/report_ops/__main__.py b/python/report_ops/__main__.py index 15828715..516b73c6 100644 --- a/python/report_ops/__main__.py +++ b/python/report_ops/__main__.py @@ -4,11 +4,22 @@ from serenitas.utils.db import dbconn import logging import argparse import datetime -from .sma import IsoselSMA, BowdstSMA +from .sma import ( + IsoselSMA, + BowdstSMA, + PositionReport, + BondPosition, + FuturePosition, + TranchePosition, + CDXPosition, + IRSwaptionPosition, + CDXSwaptionPosition, +) from .cash import NTCashReport, UMBCashReport, BNYCashReport from .admin import AccruedReport, AllReport from .wires import BowdstWire, NTWire from .custodians import upload_to_custodian +from serenitas.utils.remote import Client logger = logging.getLogger(__name__) @@ -38,6 +49,13 @@ parser.add_argument( action="store_true", help="upload trade files to notify custodians", ) + +parser.add_argument( + "-sp", + "--send_positions", + action="store_true", + help="upload position files to fund admins", +) parser.add_argument( "-n", "--no-upload", action="store_true", help="do not upload, just create files" ) diff --git a/python/report_ops/misc.py b/python/report_ops/misc.py index 76ec9cbb..da7d61ac 100644 --- a/python/report_ops/misc.py +++ b/python/report_ops/misc.py @@ -12,6 +12,7 @@ _recipients = { "mbisoye@sscinc.com", "hedgemark.lmcg.ops@sscinc.com", "hm-operations@bnymellon.com", + "Hedgemark.OTC@sscinc.com", ), "SERCGMAST": ( "SERENITAS.FA@sscinc.com", @@ -48,7 +49,9 @@ _cc_recipients = { } -def get_dir(workdate: datetime.date, archived=True) -> pathlib.Path: +def get_dir( + workdate: datetime.date = datetime.date.today(), archived=True +) -> pathlib.Path: p = DAILY_DIR / str(workdate) / "Reports" if not p.exists() and archived: p = ( diff --git a/python/report_ops/sma.py b/python/report_ops/sma.py index 9a3bf77a..16c58675 100644 --- a/python/report_ops/sma.py +++ b/python/report_ops/sma.py @@ -1,12 +1,27 @@ import datetime -from dataclasses import dataclass +from dataclasses import dataclass, field from serenitas.utils.db import dbconn from serenitas.utils.exchange import ExchangeMessage -from .misc import _sma_recipients, _cc_recipients +from serenitas.utils.misc import rename_keys +from serenitas.ops.trade_dataclasses import Deal +from .misc import _sma_recipients, _cc_recipients, get_dir from exchangelib import FileAttachment import pandas as pd from io import StringIO from typing import ClassVar +from .headers import POSITION_HEADERS +from io import StringIO +import csv +from serenitas.utils.env import DAILY_DIR + + +def build_position_file(cob, fund, asset_classes: list): + for asset_class in asset_classes: + for position in PositionReport[asset_class].gen_positions(cob, fund): + PositionReport.staging_queue.append(position.to_position()) + buf, dest = PositionReport.build_buffer(cob, fund) + PositionReport.staging_queue.clear() + return buf, dest @dataclass @@ -71,3 +86,217 @@ class IsoselSMA(SMA, fund="ISOSEL"): class BowdstSMA(SMA, fund="BOWDST"): pass + + +_sql_query = { + "bond": "SELECT * FROM risk_positions(%s, null, %s) ", + "future": ( + "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, account_code, dealid, buysell, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures " + "WHERE trade_date <= %s AND fund=%s) " + "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity, account_code, dealid, buysell FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';" + ), + "tranche": "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, trb.notional * trb.tranche_factor as active_notional, cds.* FROM tranche_risk_isosel trb left join cds on trade_id=id WHERE date=%s", + "cdx_swaption": "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, %s) spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", + "ir_swaption": "SELECT abs(spr.notional) AS active_notional, spr.nav as serenitas_nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, %s) spr LEFT JOIN swaptions ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;", + "cdx": "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, %s) cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;", + "ir": "SELECT * FROM ir_swap_risk isr LEFT JOIN irs ON id=swp_id WHERE date=%s AND fund=%s;", +} + +_fund_custodian = {"BOWDST": "BONY2", "ISOSEL": "NT"} +_fund_client = {"BOWDST": "Hedgemark", "ISOSEL": "Innocap"} +_fund_fcm = {"BOWDST": "GS_FCM", "ISOSEL": "BOA_FC"} + + +def get_path(cob, fund): + match fund: + case "ISOSEL": + filepath_pattern = "Innocap_{fund}_positions_{cob:%Y%m%d}.csv" + case _: + filepath_pattern = "{fund}_positions_{cob:%Y%m%d}.csv" + return get_dir() / filepath_pattern.format(fund=fund, cob=cob) + + +@dataclass +class PositionReport(Deal, deal_type=None, table_name=None): + client_name: str = field(metadata={"position": "Client Name"}) + fund: str = field(metadata={"position": "Fund Name"}) + cp_code: str = field(metadata={"position": "Counterparty"}) + dealid: str = field(metadata={"position": "Unique Deal ID"}) + buysell: bool + currency: str = field(metadata={"position": "DealCurrencyA"}) + notional: float = field(metadata={"position": "NotionalA"}) + cob: datetime.date = field(metadata={"position": "COB Date"}) + identifier: str = field( + metadata={"position": "Underlying (ISIN / CUSP / RED CODES)"} + ) + start_date: datetime.date = field(default=None, metadata={"position": "Start Date"}) + effective_date: datetime.date = field( + default=None, metadata={"position": "Effective Date"} + ) + maturity: datetime.date = field( + default=None, metadata={"position": "Maturity Date"} + ) + description: str = field(default=None, metadata={"position": "Underlying Desc"}) + local_market_value: str = field( + default=None, metadata={"position": "Local Market Value"} + ) + mtm_currency: str = field(default=None, metadata={"position": "MTM Currency"}) + mtm_valuation: float = field(default=None, metadata={"position": "MTM Valuation"}) + fixed_rate: float = field(default=None, metadata={"position": "FixedRate"}) + putcall: bool = None + strike: float = field(default=None, metadata={"position": "Strike"}) + underlying_maturity: datetime.date = field( + default=None, metadata={"position": "Underlying Maturity"} + ) + exercise_type: str = field(default=None, metadata={"position": "Exercise Type"}) + clearing_house: str = field( + default=None, metadata={"position": "Clearing House Name"} + ) + account: str = field(default=None, metadata={"position": "AccountNumber"}) + primebroker: str = field(default=None, metadata={"position": "Prime Broker"}) + price: float = field(default=None, metadata={"position": "MarketPrice"}) + staging_queue: ClassVar = [] + asset_class: ClassVar[str] = field(metadata={"position": "Product Type"}) + _query: ClassVar[str] + + def __init_subclass__(cls, asset_class, **kwargs): + cls.asset_class = asset_class + cls._query = _sql_query[asset_class] + cls._registry[asset_class] = cls + + @classmethod + def gen_positions(cls, cob, fund): + with cls._conn.cursor() as c: + params = (cob, fund) if cls not in (TranchePosition,) else (cob,) + c.execute(cls._query, params) + for row in c: + yield cls.from_query(row._asdict(), cob, fund) + + @classmethod + def build_buffer(cls, cob, fund): + buf = StringIO() + csvwriter = csv.writer(buf) + csvwriter.writerow(POSITION_HEADERS) + csvwriter.writerows( + [[obj.get(h) for h in POSITION_HEADERS] for obj in cls.staging_queue] + ) + buf = buf.getvalue().encode() + dest = get_path(cob, fund) + dest.parent.mkdir(exist_ok=True) + dest.write_bytes(buf) + return buf, dest + + def from_query(d, cob, fund): + d["client_name"] = _fund_client[fund] + d["fund"] = fund + d["cob"] = cob + return d + + def to_position(self): + obj = self.serialize("position") + obj["Product Type"] = self.asset_class + return obj + + +class BondPosition(PositionReport, asset_class="bond"): + @classmethod + def from_query(cls, d: dict, cob, fund): + d = super().from_query(d, cob, fund) + rename_keys( + d, + { + "usd_market_value": "mtm_valuation", + }, + ) + for key in ("account", "primebroker", "cp_code"): + d[key] = _fund_custodian[fund] + d["dealid"] = "Aggregated" + d["buysell"] = True + d["currency"] = "USD" + return cls.from_dict(**d) + + +class FuturePosition(PositionReport, asset_class="future"): + @classmethod + def from_query(cls, d: dict, cob, fund): + d = super().from_query(d, cob, fund) + rename_keys( + d, + { + "bbg_ticker": "identifier", + "cash_account": "account", + "security_desc": "description", + "account_code": "primebroker", + }, + ) + return cls.from_dict(**d) + + +class TranchePosition(PositionReport, asset_class="tranche"): + @classmethod + def from_query(cls, d: dict, cob, fund): + d = super().from_query(d, cob, fund) + rename_keys( + d, + { + "active_notional": "notional", + "trade_date": "start_date", + "security_desc": "description", + "mtm": "mtm_valuation", + "security_id": "identifier", + }, + ) + d["primebroker"] = "Bilateral" + d["buysell"] = d["protection"] == "Buyer" + return cls.from_dict(**d) + + +class SwaptionPosition: + @classmethod + def from_query(cls, d: dict, cob, fund): + d = super().from_query(d, cob, fund) + rename_keys( + d, + { + "active_notional": "notional", + "trade_date": "start_date", + "effectivedate": "effective_date", + "nav": "MTM Valuation", + "expiration_date": "Underlying Maturity", + "security_id": "identifier", + "security_desc": "description", + }, + ) + d["primebroker"] = "Bilateral" + return cls.from_dict(**d) + + +class IRSwaptionPosition(SwaptionPosition, PositionReport, asset_class="ir_swaption"): + pass + + +class CDXSwaptionPosition(SwaptionPosition, PositionReport, asset_class="cdx_swaption"): + pass + + +class CDXPosition(PositionReport, asset_class="cdx"): + @classmethod + def from_query(cls, d: dict, cob, fund): + d = super().from_query(d, cob, fund) + rename_keys( + d, + { + "effectivedate": "effective_date", + "security_desc": "description", + "security_id": "identifier", + }, + ) + d["FixedRate"] = d["coupon"] * 100 + d["buysell"] = d["notional"] > 0 + d["notional"] = abs(d["notional"]) * d["factor"] + d["mtm"] = d["clean_nav"] + d["accrued"] + d["cp_code"] = _fund_fcm[fund] + d["currency"] = "EUR" if d["index"] in ("EU", "XO") else "USD" + d["clearing_house"] = "ICE" + d["dealid"] = "Aggregated" + return cls.from_dict(**d) |