diff options
| -rw-r--r-- | python/book_bbg.py | 2 | ||||
| -rw-r--r-- | python/db_csvheader.py | 21 | ||||
| -rw-r--r-- | python/headers/bond_upload.py | 163 | ||||
| -rw-r--r-- | python/headers/globeop_upload.py | 269 | ||||
| -rw-r--r-- | python/headers/mtm_upload.py | 201 | ||||
| -rw-r--r-- | python/markit_tranche_quotes.py | 6 | ||||
| -rw-r--r-- | python/position.py | 6 | ||||
| -rw-r--r-- | python/trade_dataclasses.py | 19 | ||||
| -rw-r--r-- | sql/dawn.sql | 3 |
9 files changed, 667 insertions, 23 deletions
diff --git a/python/book_bbg.py b/python/book_bbg.py index 0de463d1..786631ed 100644 --- a/python/book_bbg.py +++ b/python/book_bbg.py @@ -10,7 +10,7 @@ logger = logging.getLogger(__name__) def get_bbg_id(s): - if m := re.match("(CDX|BOND)-[^_]*_([^$]*)", s): + if m := re.match("(CDX|BOND)(?:BLOCK)-[^_]*_([^$]*)", s): return m.groups() if "DEAL" in s: return "FX", s.split("_")[3] diff --git a/python/db_csvheader.py b/python/db_csvheader.py index 4f286584..6b9d4f66 100644 --- a/python/db_csvheader.py +++ b/python/db_csvheader.py @@ -11,17 +11,16 @@ if __name__ == "__main__": ) args = parser.parse_args() conn = dbconn("dawndb") - with open(f"/home/serenitas/flint/{args.fname}.csv") as csvfile: + file_name, header_name = args.fname.split(".") + with open(f"/home/serenitas/flint/{header_name}.csv") as csvfile: csvreader = csv.reader(csvfile) for row in csvreader: - sql_str = "INSERT INTO csv_templates values (%s, %s) ON CONFLICT DO NOTHING" - with conn.cursor() as c: - c.execute(sql_str, (args.fname, row)) - c.execute( - "SELECT headers FROM csv_templates where template_name=%s", - (args.fname,), - ) - for row in c: - print(row.headers) - conn.commit() + row = [header.replace("\n", "") for header in row] + try: + with open(f"headers/{file_name}.py", "a") as template_file: + template_file.write(f"\n{header_name}={row}") + except: + with open(f"headers/{file_name}.py", "w") as template_file: + template_file.write(f"{header_name}={row}") + csvfile.close() break diff --git a/python/headers/bond_upload.py b/python/headers/bond_upload.py new file mode 100644 index 00000000..c6ca15f1 --- /dev/null +++ b/python/headers/bond_upload.py @@ -0,0 +1,163 @@ +bbh_bonds = [ + "Function of Instruction", + "Client Reference Number", + "Previous Reference Number", + "Account Number", + "Transaction Type", + "Place of Settlement/Country", + "Place of Safekeeping", + "Trade Date", + "Settlement Date", + "Security ID", + "Security Description", + "Unit / Original Face Amount", + "Currency", + "Unit Price Amount", + "Net Amount", + "Trading Broker Type/ID", + "Trading Broker Description", + "Beneficiary of Securities Account", + "Clearing Broker ID / Type", + "Clearing Broker Description", + "Clearing Agent Account", + "Stamp Duty Code", + "Stamp Duty Amount", + "Special Settlement Type", + "Special Indicator #1", + "Special Indicator #2", + "Registration Details", + "Special Instruction", + "Originator of Message", + "Current Face/Amortize Value", + "Principal Amount", + "Interest Amount", + "Other Fees Amount", + "Commission Amount", + "SEC Fees Amount", + "Transaction Tax Amount", + "Withholding Tax Amount", + "Exchange Rate", + "Resulting Currency", + "Resulting Amount", + "FX Currency", + "Pool Reference Number", + "Total Group Number", + "Trade Number", + "Repo Term Date (REPO only)", + "Repo Amount (REPO only)", + "Repo Reference Number (REPO only)", + "Repo Rate (REPO Only)", + "Ticker (CPF and CRF Only)", + "Strike Price (CPF and CRF Only)", + "Expiration Date (CPF and CRF Only)", + "Broker Number (CPF and CRF Only)", + "Broker Account (CPF and CRF Only)", + "Contract Size (Option Contract and Future Contract Only)", + "Place of Trade Narrative", + "Common Reference", + "Partial Settlement Allowed", + "Partial Settlement Tolerance", + "No Automatic Market Claim", + "Corporate Action Coupon Option", + "Triparty Collateral Segregation", + "FX Cancel - For CANC instructions only", + "Fund Accounting Only Trade (RPTO)", + "Custody Only Trade (NACT)", +] + +bbh_swap = [ + "Deal Type", + "Deal Id", + "Action", + "Client", + "Fund", + "Portfolio", + "Folder", + "Custodian", + "Cash Account", + "Counterparty", + "Comments", + "State", + "Trade Date", + "Reserved", + "Reserved", + "Reserved", + "Notional", + "PremiumSettlementDate", + "ExpirationDate", + "PremiumCurrency", + "PercentageOfPremium", + "ExerciseType", + "Reserved", + "SettlementMode", + "SettlementRate", + "Transaction Indicator", + "InitialMargin", + "InitialMarginPercentage", + "InitialMarginCurrency", + "ReceiveLegRateType", + "ReceiveFloatRate", + "ReceiveFirstCouponDate", + "ReceiveFirstCouponRate", + "ReceiveFixedRate", + "ReceiveDaycount", + "ReceiveFrequency", + "ReceivePaymentRollConvention", + "ReceiveEffectiveDate", + "ReceiveMaturityDate", + "ReceiveNotional", + "ReceiveArrears", + "ReceiveAdjusted", + "ReceiveCompound", + "ReceiveCurrency", + "PayLegRateType", + "PayFloatRate", + "PayFirstCouponDate", + "PayFirstCouponRate", + "PayFixedRate", + "PayDaycount", + "PayFrequency", + "PayPaymentRollConvention", + "PayEffectiveDate", + "PayMaturityDate", + "PayNotional", + "PayArrears", + "PayAdjusted", + "PayCompound", + "PayCurrency", + "RegenerateCashFlow", + "GiveUpBroker", + "ClientReference", + "ReceiveDiscountCurve", + "ReceiveForwardCurve", + "PayDiscountCurve", + "PayForwardCurve", + "ReceiveFixingFrequency", + "ReceiveInterestCalcMethod", + "ReceiveCompoundAverageFrequency", + "PayFixingFrequency", + "PayInterestCalcMethod", + "PayCompoundAverageFrequency", + "SwapType", + "AttachmentPoint", + "ExhaustionPoint", + "UnderlyingInstrument", + "AssociatedDealType", + "AssociatedDealId", + "CounterpartyReference", + "PremiumSettlementCurrency", + "PremiumSettlementAmount", + "ReceiveIMM Period", + "PayIMMPeriod", + "Reserved", + "ClearingFacility", + "Strike", + "CcpTradeRef", + "BreakClauseFrequency", + "BlockId", + "BlockAmount", + "Cross Currency Premium Payment", + "Premium Payment Amount", + "Netting Id", + "BreakClauseDate", +] diff --git a/python/headers/globeop_upload.py b/python/headers/globeop_upload.py new file mode 100644 index 00000000..db5de036 --- /dev/null +++ b/python/headers/globeop_upload.py @@ -0,0 +1,269 @@ +globeop_TRS = [ + "DealType", + "DealId", + "Action", + "Client", + "Reserved", + "Reserved", + "Strategy ", + "Custodian", + "CashAccount", + "Counterparty", + "Comments", + "State", + "TradeDate", + "Reserved", + "Reserved", + "ReceiveLegRateType", + "ReceiveUnderlyingType", + "ReceiveUnderlyingSecurity", + "ReceiveUnderlyingDescription", + "ReceiveFloatRate", + "ReceiveFirstCouponDate", + "ReceiveFirstCouponRate", + "ReceiveFixedRate", + "ReceiveDaycount", + "ReceiveFrequency", + "ReceivePaymentBDC", + "ReceiveEffectiveDate", + "ReceiveMaturityDate", + "ReceiveNotional", + "ReceivePrice", + "ReceiveArrears", + "Reserved", + "Reserved", + "ReceiveCurrency", + "Reserved", + "ReceiveSpread", + "PayLegRateType", + "PayUnderlyingType", + "PayUnderlyingSecurity", + "PayUnderlyingDescription", + "PayFloatRate", + "PayFirstCouponDate", + "PayFirstCouponRate", + "PayFixedRate", + "PayDaycount", + "PayFrequency", + "PayPaymentBDC", + "PayEffectiveDate", + "PayMaturityDate", + "PayNotional", + "PayPrice", + "PayArrears", + "Reserved", + "Reserved", + "PayCurrency", + "Reserved", + "PaySpread", + "Reserved", + "InitialMargin", + "InitialMarginPercent", + "InitialMarginCurrency", + "ClientReference", + "CcpTradeRef", + "BlockId", + "BlockAmount", + "Netting Id", + "ExchangeRate", + "ReceiveQuantity", + "PayQuantity", + "ReceiveAccrued", + "PayAccrued", + "ReceiveNotionalExchange", + "PayNotionalExchange", + "ReceiveResetLag", + "PayResetLag", + "Reserved", + "Reserved", + "Reserved", + "Reserved", + "ReceiveCalendar", + "PayCalendar", + "ReceiveInterestCalcMethod", + "PayInterestCalcMethod", + "ReceiveCompoundAverageFrequency", + "PayCompoundAverageFrequency", + "ReceiveFixingFrequency", + "PayFixingFrequency", + "ReceiveStubLocation", + "ReceiveBeginFloatRate1", + "ReceiveBeginFloatRate2", + "ReceiveEndFloatRate1", + "ReceiveEndFloatRate2", + "PayStubLocation", + "PayBeginFloatRate1", + "PayBeginFloatRate2", + "PayEndFloatRate1", + "PayEndFloatRate2", + "Fees", + "Fee Payment Dates", + "Fee Comments", + "ExecutionDateTimeStamp", + "FeeTypes", + "FeeCurrencies", + "ReceivePaymentAt", + "PayPaymentAt", + "SwapType", + "Reserved1", + "ReceiveAccrualBDC", + "PayAccrualBDC", + "ReceiveMaturityBDC", + "PayMaturityBDC", + "ReceiveRollConvention", + "PayRollConvention", + "ReceivePaymentLag", + "PayPaymentLag", + "ReceiveSettlementCurrency", + "PaySettlementCurrency", + "Collateralized", + "TradeDateFX", +] + +globeop_IRS = [ + "DealType", + "TradeId", + "ActionId", + "ClientId", + "Fund", + "Portfolio", + "StrategyId", + "CustodianId", + "CashAccountId", + "CounterpartyId", + "Comments", + "StateId", + "TradeDate", + "Reserved3", + "Reserved4", + "RecLegType", + "RecIndex", + "RecFirstCpnDate", + "RecFirstCpnRate", + "RecFixedRate", + "RecDayCount", + "RecPaymentFreq", + "ReceivePaymentBDC", + "RecEffectiveDate", + "RecMaturityDate", + "RecNotional", + "RecArrears", + "Reserved5", + "RecCompound", + "RecCurrency", + "Reserved6", + "PayLegType", + "PayIndex", + "PayFirstCpnDate", + "PayFirstCpnRate", + "PayFixedRate", + "PayDayCount", + "PayPaymentFreq", + "PayPaymentBDC", + "PayEffectiveDate", + "PayMaturityDate", + "PayNotional", + "PayArrears", + "Reserved7", + "PayCompound", + "PayCurrency", + "Reserved8", + "InitialMargin", + "InitialMarginPercent", + "InitialMarginCcy", + "CalendarPay", + "CalendarReceive", + "Reserved9", + "RecFloatingRateSpread", + "RecFixingFreq", + "RecInterestCalcMethod", + "Reserved10", + "PayFloatingRateSpread", + "PayFixingFreq", + "PayInterestCalcMethod", + "Reserved11", + "GiveUpBroker", + "RecBrokenPeriod", + "RecBeginFloatRate1", + "RecBeginFloatRate2", + "RecEndFloatRate1", + "RecEndFloatRate2", + "PayBrokenPeriod", + "PayBeginFloatRate1", + "PayBeginFloatRate2", + "PayEndFloatRate1", + "PayEndFloatRate2", + "Reserved12", + "Reserved13", + "SwapType", + "InflationMarketConv", + "ClientRef", + "Reserved14", + "Reserved15", + "Reserved16", + "Reserved17", + "Reserved18", + "Reserved19", + "RecResetLag", + "PayResetLag", + "RecExchangeAmount", + "PayExchangeAmount", + "AssociatedDealType", + "AssociatedDealId", + "ClearingFacility", + "CcpTradeRef", + "BreakClauseFreq", + "BlockId", + "BlockAmount", + "UpfrontFee", + "UpfrontFeePayDate", + "UpfrontFeeComment", + "UpfrontFeeCurrency", + "NettingId", + "BreakClauseDate", + "Reserved20", + "IndexLevel", + "TradeDateTime", + "ReceivePaymentLag", + "PayPaymentLag", + "ReceiveRateMultiplier", + "PayRateMultiplier", + "ReceiveRateCap", + "PayRateCap", + "ReceiveRateFloor", + "PayRateFloor", + "ReceiveRollConvention", + "PayRollConvention", + "ReceiveAccrualBDC", + "PayAccrualBDC", + "ReceiveMaturityBDC", + "PayMaturityBDC", + "ReceivePaymentAt", + "PayPaymentAt", + "ReceiveClientMargin", + "PayClientMargin", + "Resvered21", + "ReceiveRateCutOff", + "PayRateCutOff", + "ReceiveInflationLag", + "PayInflationLag", + "ReceiveSettlementCurrency", + "PaySettlementCurrency", + "CounterpartyReference", + "ReceiveInflationReference", + "PayInflationReference", + "Collateralized", + "InitialFXRate", + "TradeDateFX", + "ReceiveFixingSource", + "PayFixingSource", + "ReceiveFxFixingLag", + "PayFxFixingLag", + "ReceiveFxFixingCalendar", + "PayFxFixingCalendar", + "SEFFlag", + "ReceiveObservationShift", + "PayObservationShift", + "ReceiveCashFlowStubType", + "PayCashFlowStubType", +] diff --git a/python/headers/mtm_upload.py b/python/headers/mtm_upload.py new file mode 100644 index 00000000..d5619616 --- /dev/null +++ b/python/headers/mtm_upload.py @@ -0,0 +1,201 @@ +mtm_term = [ + "Swap ID", + "Allocation ID", + "Description", + "Broker Id", + "DTCC CounterParty ID", + "Trade ID", + "Trade Date", + "Effective Date", + "Settle Date", + "Maturity Date", + "Account Abbreviation", + "1st Leg Notional", + "Currency Code", + "1st Leg Rate", + "Initial Payment", + "Initial Payment Currency", + "Payment Frequency Description", + "Original Issue Date", + "Interest Payment Method Description", + "First Payment Date", + "Product Type", + "Product Sub Type", + "Transaction Type", + "Protection", + "Transaction Code", + "Remaining Party", + "DTCC Remaining CounterParty ID", +] +mtm_swaption = [ + "Swap ID", + "Broker Id", + "Trade ID", + "Trade Date", + "Settle Date", + "Supplement Date", + "Supplement 2 Date", + "Maturity Date", + "Account Abbreviation", + "1st Leg Notional", + "Currency Code", + "1st Leg Rate", + "Initial Payment Currency", + "Initial Payment", + "Product Type", + "Transaction Type", + "Transaction Code", + "Independent Amount (%)", + "RED", + "Issuer Name", + "Entity Matrix", + "Definitions Type", + "Swaption Expiration Date", + "Strike Price", + "Swaption Settlement Type", + "Master Document Date", + "OptionBuySellIndicator", + "Clearing House", + "Protection", + "Swaption Quotation Rate Type", + "Effective Date", +] + +mtm_cds = [ + "Swap ID", + "Allocation ID", + "Description", + "Broker Id", + "DTCC CounterParty ID", + "Trade ID", + "Trade Date", + "Effective Date", + "Settle Date", + "Maturity Date", + "Account Abbreviation", + "1st Leg Notional", + "Currency Code", + "1st Leg Rate", + "Initial Payment", + "Initial Payment Currency", + "Original Issue Date", + "Interest Payment Method Description", + "First Payment Date", + "Product Type", + "Product Sub Type", + "Transaction Type", + "Protection", + "Transaction Code", + "Remaining Party", + "DTCC Remaining CounterParty ID", + "Independent Amount (%)", + "Independent Amount ($)", + "RED", + "Issuer Name", + "Settlement Amount", + "Trader", + "Executing Broker", + "Dealer Trade ID", + "Notes", + "Parent Transaction Code", + "Parent Trade Date", + "Parent Notional", + "Parent Currency Code", + "Parent Net Amount", + "Parent Effective Date", + "Parent First Payment Date", + "Parent Settle Date", + "ComplianceHubAction", + "DTCC Ineligible", + "Master Document Date", + "Master Document Version", + "Include Contractual Supplement", + "Contractual Supplement", + "Supplement Date", + "Entity Matrix", + "Entity Matrix Date", + "Modified Equity Delivery", + "Calculation Agent Business Center", + "Calculation Agent", + "Attachment Point", + "Exhaustion Point", + "Strategy", + "First Payment Period Accrual Start Date", + "TieOut Ineligible", + "Electronic Consent Ineligible", + "External OMS ID", + "Independent Amount Currency", + "Independent Amount Payer", + "Trade Revision", + "Alternate Swap ID", + "Alternate Trade ID", + "Definitions Type", +] +mtm_trs = [ + "Swap ID ", + "Allocation ID", + "Description ", + "Broker Id ", + "DTCC CounterParty ID", + "Trade ID ", + "Trade Date ", + "Effective Date", + "Settle Date", + "Maturity Date ", + "Account Abbreviation ", + "1st Leg Notional", + "Currency Code ", + "Initial Payment", + "Initial Payment Currency", + "Original Issue Date", + "Interest Payment Method Description", + "Product Type ", + "Product Sub Type", + "Transaction Type ", + "Protection", + "Transaction Code", + "Remaining Party ", + "DTCC Remaining CounterParty ID", + "Independent Amount (%)", + "Independent Amount ($)", + "RED", + "Issuer Name", + "Settlement Amount", + "Trader", + "Dealer Trade ID", + "Notes", + "Parent Transaction Code", + "Parent Trade Date", + "Parent Notional", + "Parent Currency Code", + "Parent Net Amount", + "Parent Effective Date", + "Parent First Payment Date", + "Parent Settle Date", + "ComplianceHubAction", + "DTCC Ineligible", + "Master Document Date", + "Master Document Type", + "Master Document Version", + "", + "", + "Annex Date", + "Supplement Date", + "Documentation Type", + "Calculation Agent Business Center", + "", + "Strategy", + "Electronic Consent Ineligible", + "External OMS ID", + "Traded Rate/Price", + "Independent Amount Currency", + "Independent Amount Payer", + "Trade Revision", + "Alternate Swap ID", + "Alternate Trade ID", + "Definitions Type", + "Initial Fixing Amount", + "2nd Leg Index", + "2nd Leg Spread", + "2nd Leg Initial Floating Rate", +] diff --git a/python/markit_tranche_quotes.py b/python/markit_tranche_quotes.py index 611ed129..894ec56a 100644 --- a/python/markit_tranche_quotes.py +++ b/python/markit_tranche_quotes.py @@ -54,6 +54,7 @@ headers = [h.lower() for h in next(f).strip().split(",")] count = 0 for d in csv.DictReader(f, fieldnames=headers): + d["quotedate"] = datetime.datetime.strptime(d["time"], "%m/%d/%Y %H:%M:%S") d["quotedate"] = d["quotedate"].replace(tzinfo=pytz.UTC) d["index"] = index_mapping[d["ticker"]] @@ -81,7 +82,10 @@ for d in csv.DictReader(f, fieldnames=headers): else: d["indexrefprice"] = ref for k in ["_bid", "_mid", "_ask"]: - d["upfront" + k] = d["price" + k] + if d["price" + k]: + d["upfront" + k] = d["price" + k] + elif d["spread" + k]: + d["upfront" + k] = d["spread" + k] d["spread" + k] = ( 0 if d["series"] in [9, 10] and d["attachment"] == 10 else 500 ) diff --git a/python/position.py b/python/position.py index 45dcecad..02499164 100644 --- a/python/position.py +++ b/python/position.py @@ -230,7 +230,7 @@ def update_swaption_vol( match vol_type: case "N": db_vol_type = "Normal" - ticker_pattern = "USSV{:0>2}{} {} Curncy" + ticker_pattern = "USSN{:0>2}{} {} Curncy" case "V": db_vol_type = "LogNormal" ticker_pattern = "USSV{:0>2}{} {} Curncy" @@ -283,8 +283,8 @@ def update_cash_rates(conn, session, start_date: datetime.date = None): "FEDL01 Index": "FED_FUND", "US0001M Index": "1M_LIBOR", "US0003M Index": "3M_LIBOR", - "SOFRRATE Index": "SOFR_RATE", - "SOFRINDX Index": "SOFR_INDEX", + "SOFRRATE Index": "SOFRRATE", + "SOFRINDX Index": "SOFRINDX", } if start_date is None: data = retrieve_data( diff --git a/python/trade_dataclasses.py b/python/trade_dataclasses.py index f99dad2e..43d92da3 100644 --- a/python/trade_dataclasses.py +++ b/python/trade_dataclasses.py @@ -30,10 +30,7 @@ Portfolio = Literal[ "OPTIONS", "IR", "MORTGAGES", "CURVE", "TRANCHE", "CLO", "HEDGE_MAC" ] # deprecated IG, HY, STRUCTURED -_funds = { - "SERENITAS_CGMF": "SERCGMAST", - "BOWDOINST": "BOWDST", -} +_funds = {"BAML": "SERCGMAST", "GS": "BOWDST", "WF": "SERCGMAST"} _fcms = { "Bank of America, N.A.": "BAML", "Goldman Sachs": "GS", @@ -501,6 +498,8 @@ class CDSDeal( @classmethod def from_bbg_line(cls, line: dict): + if "SEQ#" in line and line["Brkr"] != "BSEF": + raise ValueError("Ignoring file, we have an allocation file") if line["Coupon"] == "": with cls._conn.cursor() as c: c.execute( @@ -514,10 +513,18 @@ class CDSDeal( coupon, index, series, tenor = c.fetchone() line["Security"] = desc_str(index, series, tenor.removesuffix("yr")) line["Coupon"] = coupon + if line["Brkr"] == "BSEF": # BSEF means CDX BLOCK + line["Price (Dec)"] = line["Price"] + line["Quantity"] = 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 = line.values() cp_code = cls.get_cp_code(line["Brkr"], "CDS") - cls._bbg_insert_queue.append(list(line.values())) + cls._bbg_insert_queue.append(list(values)) return cls( - fund=_funds[line["Account"]], + fund=_funds[_fcms[line["Client FCM"]]], folder="*", portfolio="UNALLOCATED", security_id=line["Red Code"], diff --git a/sql/dawn.sql b/sql/dawn.sql index 48a9b622..3d6aa8ad 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2088,7 +2088,7 @@ CREATE TYPE portfolio AS ENUM('CASH', 'CLO', 'CURVE', 'GFS_HELPER_BUSINESS_UNIT' 'SERCGLLC__SERCGLLC', 'SERCGLTD__SERCGLTD', 'SER_TEST__SER_TEST', 'STRUCTURED', 'IR', 'TRANCHE'); -CREATE TYPE fund AS ENUM('SERCGLLC', 'SERCGLTD', 'SERCGMAST', 'SER_TEST', 'BRINKER', 'BOWDST') +CREATE TYPE fund AS ENUM('SERCGLLC', 'SERCGLTD', 'SERCGMAST', 'SER_TEST', 'BRINKER', 'BOWDST', 'LIMBO', 'ISOSEL') CREATE TYPE strategy AS ENUM( -- CLO portfolio @@ -3111,6 +3111,7 @@ CREATE TABLE trs ( trade_date date NOT NULL, effective_date date NOT NULL, maturity_date date NOT NULL, + settle_date date NOT NULL, funding_index cash_rate NOT NULL DEFAULT 'SOFRRATE'::cash_rate, buysell bool NOT NULL, underlying_security varchar(32) NOT NULL, |