diff options
Diffstat (limited to 'python/book_bbg.py')
| -rw-r--r-- | python/book_bbg.py | 41 |
1 files changed, 27 insertions, 14 deletions
diff --git a/python/book_bbg.py b/python/book_bbg.py index 0c4978d0..7d345789 100644 --- a/python/book_bbg.py +++ b/python/book_bbg.py @@ -9,6 +9,7 @@ from serenitas.utils.db import dbconn fund_dictionary = {"SERENITAS_CGMF": "SERCGMAST", "BOWDOINST": "BOWDST"} fcm_dictionary = {"Bank of America, N.A.": "BAML", "Goldman Sachs": "GS"} +counterparty_dictionary = {"BNP PARIB.": "BNPBNY"} def download_files(date): @@ -25,13 +26,13 @@ def download_files(date): modification_time = datetime.datetime.fromtimestamp( f.st_mtime, tz=datetime.timezone.utc ).astimezone(est) - if not local_file.exists() and (modification_time.date() == date): + if notlocal_file.exists() and (modification_time.date() == date): sftp.client.get(f"{src}/{f.filename}", localpath=local_file) downloaded_files.append(local_file) return downloaded_files -def get_bbg_data(bbg_id, trade_date): +def get_bbg_data(bbg_id, trade_date, conn): try: _, indextype, _, series, tenor = bbg_id.split() except ValueError: @@ -45,49 +46,61 @@ def get_bbg_data(bbg_id, trade_date): "WHERE index=%s and series=%s and tenor=%s " " and lastdate >=%s ORDER BY version" ) - db = get_db() - with db.cursor() as c: + with conn.cursor() as c: c.execute(sql_str, (indextype, series, tenor, trade_date)) redcode, maturity, coupon = c.fetchone() return str(maturity), redcode, coupon def cdx_booking_process(path): + conn = dbconn("dawndb") reader = csv.DictReader(open(path)) - for csvdict in reader: + trades = [] + for csv_dict in reader: rename_keys( csv_dict, { "Curncy": "currency", - "Quantity": "notional", + # 'Net': 'upfront' }, ) - csv_dict["security_desc"] = csv_dict["security_desc"].replace(" PRC", "") + csv_dict["upfront"] = float(csv_dict["Net"]) + csv_dict["security_desc"] = csv_dict["Security"].replace(" PRC", "") csv_dict["traded_level"] = float(csv_dict["Price (Dec)"]) csv_dict["notional"] = float(csv_dict["Quantity"]) csv_dict["trade_date"] = datetime.datetime.strptime( - csv_dict["Trade Dt"], "%Y-%m-%d" + csv_dict["Trade Dt"], "%m/%d/%Y" ) csv_dict["upfront_settle_date"] = datetime.datetime.strptime( - csv_dict["SetDt"], "%Y-%m-%d" + csv_dict["SetDt"], "%m/%d/%Y" ) csv_dict["protection"] = "Buyer" if csv_dict["Side"] == "B" else "Seller" csv_dict["account_code"] = fcm_dictionary[csv_dict["Client FCM"]] csv_dict["fund"] = fund_dictionary[csv_dict["Account"]] csv_dict["action"] = "NEW" - csv_dict["folder"] = None - csv_dict["cp_code"] = None + csv_dict["folder"] = "*" + csv_dict["cp_code"] = counterparty_dictionary[csv_dict["BrkrName"]] csv_dict["payment_rolldate"] = "Following" csv_dict["day_count"] = "ACT/360" csv_dict["frequency"] = 4 csv_dict["swap_type"] = "CD_INDEX" - csv_dict["portfolio"] = None + csv_dict["portfolio"] = "UNALLOCATED" + csv_dict["clearing_facility"] = "ICE-CREDIT" ( csv_dict["maturity"], csv_dict["security_id"], csv_dict["fixed_rate"], - ) = get_bbg_data(csv_dict["security_desc"], csv_dict["trade_date"]) + ) = get_bbg_data(csv_dict["security_desc"], csv_dict["trade_date"], conn) csv_dict["effective_date"] = datetime.date(2021, 12, 20) + trades.append(csv_dict) + + with conn.cursor() as c: + c.executemany( + "INSERT INTO cds (action, folder, cp_code, trade_date, effective_date, maturity, currency, payment_rolldate, notional, fixed_rate, day_count, frequency, protection, security_id, security_desc, upfront, upfront_settle_date, swap_type, clearing_facility, portfolio, fund) " + "VALUES (%(action)s, %(folder)s, %(cp_code)s, %(trade_date)s, %(effective_date)s, %(maturity)s, %(currency)s, %(payment_rolldate)s, %(notional)s, %(fixed_rate)s, %(day_count)s, %(frequency)s, %(protection)s, %(security_id)s, %(security_desc)s, %(upfront)s, %(upfront_settle_date)s, %(swap_type)s, %(clearing_facility)s, %(portfolio)s, %(fund)s);", + trades, + ) + conn.commit() def book_trades(date): @@ -99,4 +112,4 @@ def book_trades(date): if __name__ == "__main__": - book_trades(datetime.date(2022, 2, 4)) + book_trades(datetime.date(2022, 2, 7)) |