diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/report_ops/queries.py | 17 | ||||
| -rw-r--r-- | python/report_ops/sma.py | 62 |
2 files changed, 47 insertions, 32 deletions
diff --git a/python/report_ops/queries.py b/python/report_ops/queries.py new file mode 100644 index 00000000..e1ec9c9c --- /dev/null +++ b/python/report_ops/queries.py @@ -0,0 +1,17 @@ +BOND_QUERY = "SELECT * FROM risk_positions(%s, null, %s) " + +FUTURE_QUERY = ( + "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_QUERY = "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_master trb left join cds on trade_id=id WHERE date=%s AND trb.fund=%s;" + +CDX_SWAPTION_QUERY = "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_QUERY = "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_QUERY = "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, %s) cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;" + +IRS_QUERY = "SELECT isr.pv, irs.*, accounts2.name FROM ir_swap_risk isr LEFT JOIN irs ON id=swp_id LEFT JOIN accounts2 USING (cash_account) WHERE date=%s AND irs.fund=%s;" diff --git a/python/report_ops/sma.py b/python/report_ops/sma.py index b1c0322c..99a4f90b 100644 --- a/python/report_ops/sma.py +++ b/python/report_ops/sma.py @@ -1,16 +1,28 @@ import datetime +import csv + +from exchangelib import FileAttachment +import pandas as pd +from io import StringIO +from typing import ClassVar 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, get_dir -from exchangelib import FileAttachment -import pandas as pd -from io import StringIO -from typing import ClassVar from .headers import get_position_headers -import csv +from .queries import ( + BOND_QUERY, + FUTURE_QUERY, + TRANCHE_QUERY, + CDX_SWAPTION_QUERY, + IR_SWAPTION_QUERY, + CDX_QUERY, + IRS_QUERY, +) def build_position_file( @@ -144,28 +156,6 @@ product_name_mapping = { } -def get_position_query(asset_class): - match asset_class: - case "bond": - return "SELECT * FROM risk_positions(%s, null, %s) " - case "future": - return ( - "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';" - ) - case "tranche": - return "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_master trb left join cds on trade_id=id WHERE date=%s AND trb.fund=%s;" - case "cdx_swaption": - return "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;" - case "ir_swaption": - return "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;" - case "cdx": - return "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, %s) cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;" - case "irs": - return "SELECT isr.pv, irs.*, accounts2.name FROM ir_swap_risk isr LEFT JOIN irs ON id=swp_id LEFT JOIN accounts2 USING (cash_account) WHERE date=%s AND irs.fund=%s;" - - def get_path(cob, fund): match fund: case "ISOSEL": @@ -217,17 +207,15 @@ class PositionReport(Deal, deal_type=None, table_name=None): 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 = get_position_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,) + params = (cob, fund) c.execute(cls._query, params) for row in c: yield cls.from_query(row._asdict(), cob, fund) @@ -270,6 +258,8 @@ class PositionReport(Deal, deal_type=None, table_name=None): class BondPosition(PositionReport, asset_class="bond"): + _query = BOND_QUERY + @classmethod def from_query(cls, d: dict, cob, fund): d = super().from_query(d, cob, fund) @@ -289,6 +279,8 @@ class BondPosition(PositionReport, asset_class="bond"): class FuturePosition(PositionReport, asset_class="future"): + _query = FUTURE_QUERY + @classmethod def from_query(cls, d: dict, cob, fund): d = super().from_query(d, cob, fund) @@ -305,6 +297,8 @@ class FuturePosition(PositionReport, asset_class="future"): class TranchePosition(PositionReport, asset_class="tranche"): + _query = TRANCHE_QUERY + @classmethod def from_query(cls, d: dict, cob, fund): d = super().from_query(d, cob, fund) @@ -352,14 +346,16 @@ class SwaptionPosition: class IRSwaptionPosition(SwaptionPosition, PositionReport, asset_class="ir_swaption"): - pass + _query = IR_SWAPTION_QUERY class CDXSwaptionPosition(SwaptionPosition, PositionReport, asset_class="cdx_swaption"): - pass + _query = CDX_SWAPTION_QUERY class CDXPosition(PositionReport, asset_class="cdx"): + _query = CDX_QUERY + @classmethod def from_query(cls, d: dict, cob, fund): d = super().from_query(d, cob, fund) @@ -385,6 +381,8 @@ class CDXPosition(PositionReport, asset_class="cdx"): class IRSPosition(PositionReport, asset_class="irs"): + _query = IRS_QUERY + @classmethod def from_query(cls, d: dict, cob, fund): d = super().from_query(d, cob, fund) |