diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/position_file_bowdst.py | 144 |
1 files changed, 105 insertions, 39 deletions
diff --git a/python/position_file_bowdst.py b/python/position_file_bowdst.py index 534f5a9b..2d45ead0 100644 --- a/python/position_file_bowdst.py +++ b/python/position_file_bowdst.py @@ -1,52 +1,82 @@ from serenitas.utils.db import dbconn -import datetime +import datetime import csv from io import StringIO from pathlib import Path from process_queue import rename_keys - def process_upload(trades, asset_type): buf = StringIO() csvwriter = csv.writer(buf) csvwriter.writerow(HEADERS[asset_type]) csvwriter.writerows(build_line(trade, asset_type) for trade in trades) buf = buf.getvalue().encode() - dest = Path('/home/serenitas/flint/test.csv') + dest = Path("/home/serenitas/flint/{asset_type}.csv") dest.write_bytes(buf) + def build_line(obj, asset_type): return [obj.get(h, None) for h in HEADERS[asset_type]] -#variables -date = datetime.date(2021,10,28) +# variables -dawndb = dbconn('dawndb') +date = datetime.date(2021, 11, 9) +dawndb = dbconn("dawndb") -HEADERS = { - "bond": - [ - "AccountNumber", - "COB Date", - "Prime Broker", - "SecurityType", - "CUSIP", - "ISIN", - "SEDOL", - "SecurityDescription", - "Position", - "MarketPrice", - "Currency", - "Base Market Value", - "Local Market Value", - "Fx Rate" - ], - } +HEADERS = { + "bond": [ + "AccountNumber", + "COB Date", + "Prime Broker", + "SecurityType", + "CUSIP", + "ISIN", + "SEDOL", + "SecurityDescription", + "Position", + "MarketPrice", + "Currency", + "Base Market Value", + "Local Market Value", + "Fx Rate", + ], + "otc": [ + "Client Name", + "Fund Name", + "Counterparty", + "Product Type", + "Unique Deal ID", + "TransactionIndicator (Buy/Sell)", + "PutCall Indicator (Call/Put)", + "CapFloorIndicator", + "CurrencyPair", + "DealCurrencyA", + "DealCurrencyB", + "NotionalA", + "NotionalB", + "OriginalPrice", + "Strike", + "FixedRate", + "Quantity", + "Start Date", + "Effective Date", + "Maturity Date", + "Underlying Maturity", + "RecPayFixed", + "Underlying (ISIN / CUSP / RED CODES)", + "Underlying Desc", + "Exercise Type", + "MTM Currency", + "MTM Valuation", + "COB Date", + "Clearing House Name", + ], +} with dawndb.cursor() as c: @@ -54,21 +84,57 @@ with dawndb.cursor() as c: trades = [] 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', - }) + 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'] + obj["Fx Rate"] = obj["Local Market Value"] / obj["Base Market Value"] except ZeroDivisionError: - obj['Fx Rate'] = 1 - obj['AccountNumber'] = "TELHEEACPB" - obj['Prime Broker'] = 'TEST' - obj['COB Date'] = date + obj["Fx Rate"] = 1 + obj["AccountNumber"] = "TELHEEACPB" + obj["Prime Broker"] = "TEST" + obj["COB Date"] = date trades.append(obj) - process_upload(trades, 'bond') + process_upload(trades, "bond") + c.execute( + "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, cds.* FROM tranche_risk_bowdst trb left join cds on trade_id=id WHERE date=%s", + (date,), + ) + trades = [] + for row in c: + obj = row._asdict() + obj["Client Name"] = "HEDGEMARK" + obj["Fund Name"] = "BOS_PAT_BOWDOIN" + obj["Product Type"] = "Credit Index Tranche" + # obj['TransactionIndicator (Buy/Sell)'] = 'Buyer' if obj['notional'] > 0 else 'Seller' + obj["MTM Currency"] = "USD" + obj["COB Date"] = date + obj["Clearing House Name"] = "Bilateral" + rename_keys( + obj, + { + "dealid": "Unique Deal ID", + "cp_code": "Counterparty", + "currency": "DealCurrencyA", + "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", + "protection": "TransactionIndicator (Buy/Sell)", + "mtm": "MTM Valuation", + }, + ) + trades.append(obj) + process_upload(trades, "otc") |