diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/report_ops/sma.py | 119 |
1 files changed, 118 insertions, 1 deletions
diff --git a/python/report_ops/sma.py b/python/report_ops/sma.py index 9a3bf77a..bd3ce547 100644 --- a/python/report_ops/sma.py +++ b/python/report_ops/sma.py @@ -1,12 +1,15 @@ import datetime -from dataclasses import dataclass +from dataclasses import dataclass, field from serenitas.utils.db import dbconn from serenitas.utils.exchange import ExchangeMessage +from serenitas.utils.misc import rename_keys +from serenitas.ops.trade_dataclasses import Deal from .misc import _sma_recipients, _cc_recipients from exchangelib import FileAttachment import pandas as pd from io import StringIO from typing import ClassVar +from .headers import POSITION_HEADERS @dataclass @@ -71,3 +74,117 @@ 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, 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 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;", +} + +_fund_custodian = {"BOWDST": "BONY2", "ISOSEL": "NT"} +_fund_client = {"BOWDST": "Hedgemark", "ISOSEL": "Innocap"} + + +@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_date: datetime.date = field( + default=None, metadata={"position": "Maturity Date"} + ) + description: str = field(default=None, metadata={"position": "Underlying Desc"}) + 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"}) + 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] + + @classmethod + def stage_positions(cls, cob, fund): + with cls._conn.cursor() as c: + c.execute(cls._query, (cob, fund)) + for row in c: + position = cls.from_query(row._asdict(), cob, fund) + position.serialize("position") + + def from_query(d, cob, fund): + d["client_name"] = _fund_client[fund] + d["fund"] = fund + d["cob"] = cob + return d + + +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, + { + "local_market_value": "Local Market Value", + "usd_market_value": "MTM Valuation", + }, + ) + for key in ("account", "primebroker", "cp_code"): + d[key] = _fund_custodian[fund] + d["dealid"] = "Aggregated" + d["buysell"] = "B" + d["currency"] = "USD" + return cls.from_dict(**d) + + +class FuturePosition(PositionReport, asset_class="future"): + pass + + +class TranchePosition(PositionReport, asset_class="tranche"): + pass + + +class IRSwaptionPosition(PositionReport, asset_class="ir_swaption"): + pass + + +class CDXSwaptionPosition(PositionReport, asset_class="cdx_swaption"): + pass + + +class CDXPosition(PositionReport, asset_class="cdx"): + pass |