diff options
| -rw-r--r-- | python/bbg_helpers.py | 101 | ||||
| -rw-r--r-- | python/bbg_index_quotes.py | 54 | ||||
| -rw-r--r-- | python/bbg_prices.py | 101 | ||||
| -rw-r--r-- | python/calibrate_tranches_BC.py | 173 | ||||
| -rw-r--r-- | python/cds_curve.py | 81 | ||||
| -rw-r--r-- | python/cds_rebook.py | 99 | ||||
| -rw-r--r-- | python/exchange.py | 37 | ||||
| -rw-r--r-- | python/external_deriv_marks.py | 30 | ||||
| -rw-r--r-- | python/globeop_reports.py | 292 | ||||
| -rw-r--r-- | python/gmail_helpers.py | 171 | ||||
| -rw-r--r-- | python/handle_default.py | 76 | ||||
| -rw-r--r-- | python/load_globeop_report.py | 204 | ||||
| -rw-r--r-- | python/load_refentity.py | 168 | ||||
| -rw-r--r-- | python/markit_red.py | 121 | ||||
| -rw-r--r-- | python/parse_citi_pdf.py | 43 | ||||
| -rw-r--r-- | python/parse_gs.py | 122 | ||||
| -rw-r--r-- | python/position.py | 355 | ||||
| -rw-r--r-- | python/process_queue.py | 939 | ||||
| -rw-r--r-- | python/sftp.py | 19 | ||||
| -rw-r--r-- | python/swaption_quotes.py | 101 | ||||
| -rw-r--r-- | python/task_runner.py | 16 | ||||
| -rw-r--r-- | python/test_hyoption.py | 16 |
22 files changed, 2089 insertions, 1230 deletions
diff --git a/python/bbg_helpers.py b/python/bbg_helpers.py index 6205f7ab..1abac308 100644 --- a/python/bbg_helpers.py +++ b/python/bbg_helpers.py @@ -7,7 +7,8 @@ import datetime logger = logging.getLogger(__name__) -BBG_IP = ["guillaume-pc", "edwin-pc", '192.168.0.10', '192.168.0.12'] +BBG_IP = ["guillaume-pc", "edwin-pc", "192.168.0.10", "192.168.0.12"] + @contextmanager def init_bbg_session(ip_list, port=8194): @@ -33,8 +34,9 @@ def init_bbg_session(ip_list, port=8194): finally: session.stop() + def append_overrides(request, d): - overrides = request.getElement('overrides') + overrides = request.getElement("overrides") for k, v in d.items(): o = overrides.appendElement() o.setElement("fieldId", k) @@ -47,7 +49,7 @@ def append_overrides(request, d): def event_loop(session, request): session.sendRequest(request) # Process received events - while(True): + while True: # We provide timeout to give the chance to Ctrl+C handling: ev = session.nextEvent(500) if ev.eventType() in [blpapi.Event.PARTIAL_RESPONSE, blpapi.Event.RESPONSE]: @@ -76,10 +78,14 @@ def get_pythonvalue(e): def field_array_todf(field): - df = pd.DataFrame.from_dict([{str(e.name()): get_pythonvalue(e) for e in f.elements()} \ - for f in field.values()]) + df = pd.DataFrame.from_dict( + [ + {str(e.name()): get_pythonvalue(e) for e in f.elements()} + for f in field.values() + ] + ) if "date" in df: - df = df.set_index('date') + df = df.set_index("date") return df @@ -101,7 +107,9 @@ def process_reference_msg(msg): if not field.isValid(): logger.info(f"Invalid field: {str(field)}") elif field.isArray(): - row[str(field.name())] = field_array_todf(field) #to convert dates to timestamps + row[str(field.name())] = field_array_todf( + field + ) # to convert dates to timestamps else: row[str(field.name())] = get_pythonvalue(field) data[securityName] = row @@ -113,9 +121,17 @@ def process_intraday_tick_msg(msg): return field_array_todf(tickdata) -def retrieve_data(session, securities, fields=[], overrides={}, - start_date=None, end_date=None, frequency="DAILY", - options={}, event_types=["TRADE"]): +def retrieve_data( + session, + securities, + fields=[], + overrides={}, + start_date=None, + end_date=None, + frequency="DAILY", + options={}, + event_types=["TRADE"], +): """ Convenience function to retrieve data from the Bloomberg API. @@ -154,7 +170,7 @@ def retrieve_data(session, securities, fields=[], overrides={}, else: request.set("security", securities) else: - if hasattr(securities, '__iter__'): + if hasattr(securities, "__iter__"): for security in securities: request.append("securities", security) else: @@ -171,12 +187,12 @@ def retrieve_data(session, securities, fields=[], overrides={}, if start_date: if request.asElement().name() == blpapi.Name("IntradayTickRequest"): - start_date = timezone('America/New_York').localize(start_date) - start_date = start_date.astimezone(timezone('GMT')) + start_date = timezone("America/New_York").localize(start_date) + start_date = start_date.astimezone(timezone("GMT")) request.set("startDateTime", start_date) if end_date: - end_date = timezone('America/New_York').localize(end_date) - end_date = end_date.astimezone(timezone('GMT')) + end_date = timezone("America/New_York").localize(end_date) + end_date = end_date.astimezone(timezone("GMT")) request.set("endDateTime", end_date) else: request.set("startDate", "{:%Y%m%d}".format(start_date)) @@ -185,8 +201,8 @@ def retrieve_data(session, securities, fields=[], overrides={}, data = {} for msg in event_loop(session, request): - if msg.hasElement('responseError'): - logger.error(msg.getElement('responseError').getElementAsString('message')) + if msg.hasElement("responseError"): + logger.error(msg.getElement("responseError").getElementAsString("message")) continue if msg.messageType() == blpapi.Name("ReferenceDataResponse"): data.update(process_reference_msg(msg)) @@ -196,8 +212,8 @@ def retrieve_data(session, securities, fields=[], overrides={}, data.update(process_intraday_tick_msg(msg)) if request.asElement().name() == blpapi.Name("IntradayTickRequest"): df = pd.DataFrame(data) - if 'time' in df: - df.time = df.time.dt.tz_convert('America/New_York') + if "time" in df: + df.time = df.time.dt.tz_convert("America/New_York") return df else: return data @@ -205,26 +221,37 @@ def retrieve_data(session, securities, fields=[], overrides={}, if __name__ == "__main__": testdate = pd.datetime(2013, 1, 1) - hist_securities = ['CADUSD Curncy', "EURUSD Curncy"] - hist_fields = ['PX_LAST'] - securities = ['004421BW2 Mtge', '75157EAE2 Mtge', 'XS0295516776 Mtge'] - fields = ['CUR_CPN', 'START_ACC_DT'] + hist_securities = ["CADUSD Curncy", "EURUSD Curncy"] + hist_fields = ["PX_LAST"] + securities = ["004421BW2 Mtge", "75157EAE2 Mtge", "XS0295516776 Mtge"] + fields = ["CUR_CPN", "START_ACC_DT"] with init_bbg_session(BBG_IP) as session: - hist_data = retrieve_data(session, securities, hist_fields, start_date=testdate.date(), - frequency="MONTHLY") - overrides={'SETTLE_DT': testdate} + hist_data = retrieve_data( + session, + securities, + hist_fields, + start_date=testdate.date(), + frequency="MONTHLY", + ) + overrides = {"SETTLE_DT": testdate} ref_data = retrieve_data(session, securities, fields, overrides=overrides) struct_data = retrieve_data(session, securities, ["HIST_CASH_FLOW"]) - spx_ndx_monthly = retrieve_data(session, ["SPX Index", "NDX Index"], - fields=["PX_LAST"], - start_date=datetime.date(2012, 1, 1), - options={'periodicityAdjustment': 'ACTUAL'}, - frequency="MONTHLY") - trace_data = retrieve_data(session, "BNCMT 2007-1 A5@TRAC Mtge", - start_date=datetime.datetime(2018, 5, 18, 9), - end_date=datetime.datetime(2018, 9, 18, 9)) + spx_ndx_monthly = retrieve_data( + session, + ["SPX Index", "NDX Index"], + fields=["PX_LAST"], + start_date=datetime.date(2012, 1, 1), + options={"periodicityAdjustment": "ACTUAL"}, + frequency="MONTHLY", + ) + trace_data = retrieve_data( + session, + "BNCMT 2007-1 A5@TRAC Mtge", + start_date=datetime.datetime(2018, 5, 18, 9), + end_date=datetime.datetime(2018, 9, 18, 9), + ) today = datetime.datetime.today() yesterday = today - datetime.timedelta(days=1) - tick_data = retrieve_data(session, "GOOG US Equity", - start_date=yesterday, - end_date=today) + tick_data = retrieve_data( + session, "GOOG US Equity", start_date=yesterday, end_date=today + ) diff --git a/python/bbg_index_quotes.py b/python/bbg_index_quotes.py index b11d0395..b8f016f5 100644 --- a/python/bbg_index_quotes.py +++ b/python/bbg_index_quotes.py @@ -6,32 +6,44 @@ securities = {} for series in range(9, 32): for index_type in ["IG", "HY"]: for t in [3, 5, 7, 10]: - securities[f"CDX {index_type} CDSI S{series} {t}Y Corp"] = \ - (series, index_type, f"{t}yr") + securities[f"CDX {index_type} CDSI S{series} {t}Y Corp"] = ( + series, + index_type, + f"{t}yr", + ) for series in range(10, 31): for index_type in ["EUR", "XOVER"]: for t in [3, 5, 7, 10]: - securities[f"ITRX {index_type} CDSI S{series} {t}Y Corp"] = \ - (series, index_type, f"{t}yr") + securities[f"ITRX {index_type} CDSI S{series} {t}Y Corp"] = ( + series, + index_type, + f"{t}yr", + ) -place_holders = ",".join(['%s'] * 7) -sql_str_price = ("INSERT INTO index_quotes_pre" - "(date, index, series, version, tenor, close_price, source)" - f" VALUES({place_holders}) ON CONFLICT DO NOTHING") -sql_str_spread = ("INSERT INTO index_quotes_pre" - "(date, index, series, version, tenor, close_spread, source)" - f" VALUES({place_holders}) ON CONFLICT DO NOTHING") +place_holders = ",".join(["%s"] * 7) +sql_str_price = ( + "INSERT INTO index_quotes_pre" + "(date, index, series, version, tenor, close_price, source)" + f" VALUES({place_holders}) ON CONFLICT DO NOTHING" +) +sql_str_spread = ( + "INSERT INTO index_quotes_pre" + "(date, index, series, version, tenor, close_spread, source)" + f" VALUES({place_holders}) ON CONFLICT DO NOTHING" +) start_date = datetime.date.today() - datetime.timedelta(days=7) # one weeek of overlap -conn = dbconn('serenitasdb') +conn = dbconn("serenitasdb") with init_bbg_session(BBG_IP) as session: d = retrieve_data(session, securities.keys(), fields=["TICKER", "VERSION"]) - ticker_mapping = {v['TICKER']: securities[k] + (v['VERSION'],) - for k, v in d.items() if v} + ticker_mapping = { + v["TICKER"]: securities[k] + (v["VERSION"],) for k, v in d.items() if v + } for pcs in ["MSG1", "CMAN", "CBGN"]: securities = [f"{e['TICKER']} {pcs} Curncy" for e in d.values() if e] - d2 = retrieve_data(session, securities, fields=["PX_LAST"], - start_date=start_date) + d2 = retrieve_data( + session, securities, fields=["PX_LAST"], start_date=start_date + ) with conn.cursor() as c: for k, v in d2.items(): ticker = k.split()[0] @@ -45,7 +57,11 @@ with init_bbg_session(BBG_IP) as session: else: sql_str = sql_str_spread if not v.empty: - c.executemany(sql_str, - [(t[0], index, series, version, tenor, t[1], pcs) - for t in v.itertuples()]) + c.executemany( + sql_str, + [ + (t[0], index, series, version, tenor, t[1], pcs) + for t in v.itertuples() + ], + ) conn.commit() diff --git a/python/bbg_prices.py b/python/bbg_prices.py index e9b549ea..dbd95016 100644 --- a/python/bbg_prices.py +++ b/python/bbg_prices.py @@ -4,54 +4,95 @@ import numpy as np import pandas as pd from psycopg2.extensions import register_adapter, AsIs + register_adapter(type(pd.NaT), lambda nat: AsIs(None)) -engine = create_engine('postgresql://et_user@debian/ET') +engine = create_engine("postgresql://et_user@debian/ET") -fields_update = ["LN_ISSUE_STATUS", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT", - "LN_CURRENT_MARGIN", "DEFAULTED", "DEFAULT_DATE", - "CALLED", "CALLED_DT", "PRICING_SOURCE"] +fields_update = [ + "LN_ISSUE_STATUS", + "AMT_OUTSTANDING", + "PX_LAST", + "LAST_UPDATE_DT", + "LN_CURRENT_MARGIN", + "DEFAULTED", + "DEFAULT_DATE", + "CALLED", + "CALLED_DT", + "PRICING_SOURCE", +] # append securities to request -cusips = pd.read_sql_query("SELECT id_bb_unique, substring(id_bb_unique from 3) AS cusip " \ - "FROM bloomberg_corp_ref " \ - "WHERE (status is Null or status NOT IN ('REFINANCED','RETIRED', 'REPLACED')) "\ - "AND not called", engine, index_col='cusip') +cusips = pd.read_sql_query( + "SELECT id_bb_unique, substring(id_bb_unique from 3) AS cusip " + "FROM bloomberg_corp_ref " + "WHERE (status is Null or status NOT IN ('REFINANCED','RETIRED', 'REPLACED')) " + "AND not called", + engine, + index_col="cusip", +) securities = ["{0} Corp".format(cusip) for cusip in cusips.index] with init_bbg_session(BBG_IP) as session: data = retrieve_data(session, securities, fields_update) -df = pd.DataFrame.from_dict(data, orient='index') -df['security'] = df.index.str.slice(0,9) -df.set_index(['security'], inplace=True) -df['ID_BB_UNIQUE'] = cusips['id_bb_unique'] +df = pd.DataFrame.from_dict(data, orient="index") +df["security"] = df.index.str.slice(0, 9) +df.set_index(["security"], inplace=True) +df["ID_BB_UNIQUE"] = cusips["id_bb_unique"] df.reset_index(inplace=True) -to_insert = df[['DEFAULTED', 'DEFAULT_DATE', 'CALLED', 'CALLED_DT', 'LN_ISSUE_STATUS', 'ID_BB_UNIQUE']] +to_insert = df[ + [ + "DEFAULTED", + "DEFAULT_DATE", + "CALLED", + "CALLED_DT", + "LN_ISSUE_STATUS", + "ID_BB_UNIQUE", + ] +] to_insert = to_insert.where(to_insert.notnull(), None) conn = engine.raw_connection() with conn.cursor() as c: - c.executemany("UPDATE bloomberg_corp_ref SET defaulted = %(DEFAULTED)s, " \ - "default_date = %(DEFAULT_DATE)s, called= %(CALLED)s, called_date = %(CALLED_DT)s, " \ - "status = %(LN_ISSUE_STATUS)s WHERE id_bb_unique=%(ID_BB_UNIQUE)s", - to_insert.to_dict('records')) + c.executemany( + "UPDATE bloomberg_corp_ref SET defaulted = %(DEFAULTED)s, " + "default_date = %(DEFAULT_DATE)s, called= %(CALLED)s, called_date = %(CALLED_DT)s, " + "status = %(LN_ISSUE_STATUS)s WHERE id_bb_unique=%(ID_BB_UNIQUE)s", + to_insert.to_dict("records"), + ) conn.commit() -currentdata = pd.read_sql_query("SELECT id_bb_unique, pricingdate from bloomberg_corp", - engine, - parse_dates=["pricingdate"], - index_col=['id_bb_unique', 'pricingdate']) -#no need to insert empty prices -df.dropna(subset=['PX_LAST', 'LAST_UPDATE_DT'], inplace=True) -df.set_index(['ID_BB_UNIQUE', 'LAST_UPDATE_DT'], inplace=True) +currentdata = pd.read_sql_query( + "SELECT id_bb_unique, pricingdate from bloomberg_corp", + engine, + parse_dates=["pricingdate"], + index_col=["id_bb_unique", "pricingdate"], +) +# no need to insert empty prices +df.dropna(subset=["PX_LAST", "LAST_UPDATE_DT"], inplace=True) +df.set_index(["ID_BB_UNIQUE", "LAST_UPDATE_DT"], inplace=True) df = df.ix[df.index.difference(currentdata.index)] -df.index.names = ['ID_BB_UNIQUE', 'LAST_UPDATE_DT'] +df.index.names = ["ID_BB_UNIQUE", "LAST_UPDATE_DT"] df.reset_index(inplace=True) -to_insert = df[['ID_BB_UNIQUE','LAST_UPDATE_DT','PX_LAST','LN_CURRENT_MARGIN', - 'AMT_OUTSTANDING', 'PRICING_SOURCE']] -to_insert.columns = ['id_bb_unique', 'pricingdate', 'price', 'loan_margin', - 'amount_outstanding', 'source'] -to_insert.to_sql('bloomberg_corp', engine, if_exists='append', index=False) +to_insert = df[ + [ + "ID_BB_UNIQUE", + "LAST_UPDATE_DT", + "PX_LAST", + "LN_CURRENT_MARGIN", + "AMT_OUTSTANDING", + "PRICING_SOURCE", + ] +] +to_insert.columns = [ + "id_bb_unique", + "pricingdate", + "price", + "loan_margin", + "amount_outstanding", + "source", +] +to_insert.to_sql("bloomberg_corp", engine, if_exists="append", index=False) diff --git a/python/calibrate_tranches_BC.py b/python/calibrate_tranches_BC.py index b6d7a37f..8fde82e9 100644 --- a/python/calibrate_tranches_BC.py +++ b/python/calibrate_tranches_BC.py @@ -11,9 +11,11 @@ import argparse def get_lastdate(conn, index, series, tenor): - sql_str = ("SELECT (max(date) AT TIME ZONE 'America/New_York')::date + 1 " - "AS date FROM risk_numbers " - "WHERE index=%s and series = %s and tenor = %s") + sql_str = ( + "SELECT (max(date) AT TIME ZONE 'America/New_York')::date + 1 " + "AS date FROM risk_numbers " + "WHERE index=%s and series = %s and tenor = %s" + ) with conn.cursor() as c: c.execute(sql_str, (index, series, tenor)) date, = c.fetchone() @@ -22,31 +24,47 @@ def get_lastdate(conn, index, series, tenor): def build_sql_str(df): - cols = ','.join(df.columns) - cols_ex_tranche_id = ','.join([c for c in df.columns if c != 'tranche_id']) - cols_excluded = ','.join([f"excluded.{c}" for c in df.columns if c != 'tranche_id']) + cols = ",".join(df.columns) + cols_ex_tranche_id = ",".join([c for c in df.columns if c != "tranche_id"]) + cols_excluded = ",".join([f"excluded.{c}" for c in df.columns if c != "tranche_id"]) place_holders = ",".join([f"%({c})s" for c in df.columns]) - sql_str = (f"INSERT INTO tranche_risk({cols}) " - f"VALUES({place_holders}) ON CONFLICT (tranche_id) DO " - f"UPDATE SET ({cols_ex_tranche_id}) = ({cols_excluded})") + sql_str = ( + f"INSERT INTO tranche_risk({cols}) " + f"VALUES({place_holders}) ON CONFLICT (tranche_id) DO " + f"UPDATE SET ({cols_ex_tranche_id}) = ({cols_excluded})" + ) return sql_str if __name__ == "__main__": from utils import SerenitasFileHandler - logger = logging.getLogger('tranche_calib') + + logger = logging.getLogger("tranche_calib") parser = argparse.ArgumentParser() - parser.add_argument("-u", "--update", action="store_true", default=False, - help="Update from the last run date [default %default]") - parser.add_argument("-c", "--config", metavar="config_file", - help="Runs the list of indices provided in CONFIG_FILE") + parser.add_argument( + "-u", + "--update", + action="store_true", + default=False, + help="Update from the last run date [default %default]", + ) + parser.add_argument( + "-c", + "--config", + metavar="config_file", + help="Runs the list of indices provided in CONFIG_FILE", + ) parser.add_argument("-i", "--index", help="Index name we want to run") - parser.add_argument("--tenor", default="5yr", help="Tenor we want to run [default '5yr']") - parser.add_argument("--until", default=pd.Timestamp.now()-BDay(), - type=lambda s: pd.Timestamp(s)) - parser.add_argument("--start_from", default=None, - type=lambda s: pd.Timestamp(s)) - parser.add_argument("-d", "--debug", action="store_true", help="more verbose logging") + parser.add_argument( + "--tenor", default="5yr", help="Tenor we want to run [default '5yr']" + ) + parser.add_argument( + "--until", default=pd.Timestamp.now() - BDay(), type=lambda s: pd.Timestamp(s) + ) + parser.add_argument("--start_from", default=None, type=lambda s: pd.Timestamp(s)) + parser.add_argument( + "-d", "--debug", action="store_true", help="more verbose logging" + ) args = parser.parse_args() logger.setLevel(logging.DEBUG if args.debug else logging.INFO) CODE_DIR = Path(os.environ["CODE_DIR"]) @@ -57,48 +75,49 @@ if __name__ == "__main__": handler.setFormatter(SerenitasFileHandler._formatter) logger.addHandler(handler) - start_dates = {# 'hy10': datetime.date(2014, 8, 11), - # 'hy15': datetime.date(2014, 6, 10), - # 'hy17': datetime.date(2013, 1, 1), - 'hy19': datetime.date(2013, 2, 1), - 'hy21': datetime.date(2013, 10, 4), - 'hy23': datetime.date(2014, 10, 16), - 'hy25': datetime.date(2015, 10, 1), - 'hy27': datetime.date(2016, 10, 4), - 'hy29': datetime.date(2017, 10, 3), - 'hy31': datetime.date(2018, 10, 2), - 'ig9': datetime.date(2013, 1, 1), - 'ig19': datetime.date(2013, 5, 1), - 'ig21': datetime.date(2013, 9, 26), - 'ig23': datetime.date(2014, 10, 14), - 'ig25': datetime.date(2015, 9, 22), - 'ig27': datetime.date(2016, 9, 27), - 'ig29': datetime.date(2017, 9, 26), - 'ig31': datetime.date(2018, 9, 25), - 'xo22': datetime.date(2014, 10, 20), - 'xo24': datetime.date(2015, 9, 28), - 'xo26': datetime.date(2016, 9, 27), - 'xo28': datetime.date(2017, 9, 28), - 'eu9': datetime.date(2014, 9, 15), - 'eu19': datetime.date(2013, 4, 3), - 'eu21': datetime.date(2014, 3, 27), - 'eu22': datetime.date(2014, 10, 22), - 'eu24': datetime.date(2015, 9, 23), - 'eu26': datetime.date(2016, 9, 27), - 'eu28': datetime.date(2017, 9, 28), - 'eu30': datetime.date(2018, 9, 25), - 'xo30': datetime.date(2018, 9, 25)} + start_dates = { # 'hy10': datetime.date(2014, 8, 11), + # 'hy15': datetime.date(2014, 6, 10), + # 'hy17': datetime.date(2013, 1, 1), + "hy19": datetime.date(2013, 2, 1), + "hy21": datetime.date(2013, 10, 4), + "hy23": datetime.date(2014, 10, 16), + "hy25": datetime.date(2015, 10, 1), + "hy27": datetime.date(2016, 10, 4), + "hy29": datetime.date(2017, 10, 3), + "hy31": datetime.date(2018, 10, 2), + "ig9": datetime.date(2013, 1, 1), + "ig19": datetime.date(2013, 5, 1), + "ig21": datetime.date(2013, 9, 26), + "ig23": datetime.date(2014, 10, 14), + "ig25": datetime.date(2015, 9, 22), + "ig27": datetime.date(2016, 9, 27), + "ig29": datetime.date(2017, 9, 26), + "ig31": datetime.date(2018, 9, 25), + "xo22": datetime.date(2014, 10, 20), + "xo24": datetime.date(2015, 9, 28), + "xo26": datetime.date(2016, 9, 27), + "xo28": datetime.date(2017, 9, 28), + "eu9": datetime.date(2014, 9, 15), + "eu19": datetime.date(2013, 4, 3), + "eu21": datetime.date(2014, 3, 27), + "eu22": datetime.date(2014, 10, 22), + "eu24": datetime.date(2015, 9, 23), + "eu26": datetime.date(2016, 9, 27), + "eu28": datetime.date(2017, 9, 28), + "eu30": datetime.date(2018, 9, 25), + "xo30": datetime.date(2018, 9, 25), + } - serenitas_conn = dbconn('serenitasdb') + serenitas_conn = dbconn("serenitasdb") if args.config is None: if args.index is None: raise ValueError("Please provide an index to run") - config = {'runs': [(args.index, args.tenor)]} + config = {"runs": [(args.index, args.tenor)]} else: with (CODE_DIR / "etc" / args.config).open("r") as fh: config = load(fh) - for index, tenor in config['runs']: + for index, tenor in config["runs"]: begin_date = None index, series = index[:2].upper(), int(index[2:]) if args.start_from is not None: @@ -123,7 +142,9 @@ if __name__ == "__main__": logger.debug(f"calibrating for {d.date()}") try: if tranche_index is None: - tranche_index = TrancheBasket(index, series, tenor, value_date=d.date()) + tranche_index = TrancheBasket( + index, series, tenor, value_date=d.date() + ) else: tranche_index.value_date = d.date() except (RuntimeError, ValueError) as e: @@ -141,31 +162,37 @@ if __name__ == "__main__": logger.error(e) continue - df = pd.concat([tranche_index.tranche_deltas(), - tranche_index.tranche_fwd_deltas(), - tranche_index.tranche_durations(), - tranche_index.tranche_EL(), - tranche_index.tranche_spreads()], axis=1) + df = pd.concat( + [ + tranche_index.tranche_deltas(), + tranche_index.tranche_fwd_deltas(), + tranche_index.tranche_durations(), + tranche_index.tranche_EL(), + tranche_index.tranche_spreads(), + ], + axis=1, + ) try: - df['theta'] = tranche_index.tranche_thetas(method="TLP") + df["theta"] = tranche_index.tranche_thetas(method="TLP") except ValueError: - df['theta'] = None + df["theta"] = None - df['index_duration'], df['index_expected_loss'], df['index_price'] = \ - tranche_index.index_pv() - df['index_expected_loss'] *= -1 - df['index_duration'] -= tranche_index.accrued() - df['index_basis'] = tranche_index.tweaks[0] - df['index_theta'] = tranche_index.theta()[tenor] - df['tranche_id'] = tranche_index.tranche_quotes.id.values - df['corr_at_detach'] = tranche_index.rho[1:] - df['corr01'] = tranche_index.tranche_corr01() - del df['fwd_gamma'] + df["index_duration"], df["index_expected_loss"], df[ + "index_price" + ] = tranche_index.index_pv() + df["index_expected_loss"] *= -1 + df["index_duration"] -= tranche_index.accrued() + df["index_basis"] = tranche_index.tweaks[0] + df["index_theta"] = tranche_index.theta()[tenor] + df["tranche_id"] = tranche_index.tranche_quotes.id.values + df["corr_at_detach"] = tranche_index.rho[1:] + df["corr01"] = tranche_index.tranche_corr01() + del df["fwd_gamma"] data[d] = df if data: data = pd.concat(data) sql_str = build_sql_str(data) with serenitas_conn.cursor() as c: - c.executemany(sql_str, data.to_dict(orient='record')) + c.executemany(sql_str, data.to_dict(orient="record")) serenitas_conn.commit() diff --git a/python/cds_curve.py b/python/cds_curve.py index 2e0cf73f..5f8ff1cf 100644 --- a/python/cds_curve.py +++ b/python/cds_curve.py @@ -7,41 +7,52 @@ import pandas as pd from utils.db import dbconn -def all_curves_pv(curves, today_date, jp_yc, start_date, step_in_date, value_date, maturities): +def all_curves_pv( + curves, today_date, jp_yc, start_date, step_in_date, value_date, maturities +): r = {} for d in maturities: tenor = {} - coupon_leg = FeeLeg(start_date, d, True, 1., 1.) + coupon_leg = FeeLeg(start_date, d, True, 1.0, 1.0) default_leg = ContingentLeg(start_date, d, True) accrued = coupon_leg.accrued(step_in_date) tickers = [] data = [] for sc in curves: - coupon_leg_pv = coupon_leg.pv(today_date, step_in_date, value_date, jp_yc, sc, False) - default_leg_pv = default_leg.pv(today_date, step_in_date, value_date, - jp_yc, sc, 0.4) + coupon_leg_pv = coupon_leg.pv( + today_date, step_in_date, value_date, jp_yc, sc, False + ) + default_leg_pv = default_leg.pv( + today_date, step_in_date, value_date, jp_yc, sc, 0.4 + ) tickers.append(sc.ticker) - data.append((coupon_leg_pv-accrued, default_leg_pv)) - r[pd.Timestamp(d)] = pd.DataFrame.from_records(data, - index=tickers, - columns=['duration', 'protection_pv']) + data.append((coupon_leg_pv - accrued, default_leg_pv)) + r[pd.Timestamp(d)] = pd.DataFrame.from_records( + data, index=tickers, columns=["duration", "protection_pv"] + ) return pd.concat(r, axis=1).swaplevel(axis=1).sort_index(axis=1, level=0) -def calibrate_portfolio(index_type, series, tenors=['3yr', '5yr', '7yr', '10yr'], - start_date=None): +def calibrate_portfolio( + index_type, series, tenors=["3yr", "5yr", "7yr", "10yr"], start_date=None +): try: index = MarkitBasketIndex(index_type, series, tenors) except ValueError: return if start_date: index.index_quotes = index.index_quotes[start_date:] - for value_date, v in index.index_quotes.groupby('date')['id']: + for value_date, v in index.index_quotes.groupby("date")["id"]: index.value_date = value_date index.tweak() - df = pd.concat([index.theta(), - index.duration(), - pd.Series(index.tweaks, index=tenors, name='tweak')], axis=1) + df = pd.concat( + [ + index.theta(), + index.duration(), + pd.Series(index.tweaks, index=tenors, name="tweak"), + ], + axis=1, + ) for (_, t), id in v.items(): yield (id, df.loc[t]) @@ -51,26 +62,33 @@ if __name__ == "__main__": import argparse import logging import os + parser = argparse.ArgumentParser() - parser.add_argument('index', help="index type (IG, HY, EU or XO)") - parser.add_argument('series', help="series", type=int) - parser.add_argument('--latest', required=False, action="store_true") + parser.add_argument("index", help="index type (IG, HY, EU or XO)") + parser.add_argument("series", help="series", type=int) + parser.add_argument("--latest", required=False, action="store_true") args = parser.parse_args() index, series = args.index, args.series - conn = dbconn('serenitasdb') + conn = dbconn("serenitasdb") if args.latest: with conn.cursor() as c: - c.execute("SELECT max(date) FROM index_quotes_pre " - "RIGHT JOIN index_risk2 USING (id) " - "WHERE index=%s AND series=%s " - "AND tenor in ('3yr', '5yr', '7yr', '10yr')", - (index, series)) + c.execute( + "SELECT max(date) FROM index_quotes_pre " + "RIGHT JOIN index_risk2 USING (id) " + "WHERE index=%s AND series=%s " + "AND tenor in ('3yr', '5yr', '7yr', '10yr')", + (index, series), + ) start_date, = c.fetchone() else: start_date = None - fh = logging.FileHandler(filename=os.path.join(os.getenv("LOG_DIR"), "index_curves.log")) - formatter = logging.Formatter("%(asctime)s - %(name)s - %(levelname)s - %(message)s") + fh = logging.FileHandler( + filename=os.path.join(os.getenv("LOG_DIR"), "index_curves.log") + ) + formatter = logging.Formatter( + "%(asctime)s - %(name)s - %(levelname)s - %(message)s" + ) fh.setFormatter(formatter) loggers = [logging.getLogger("analytics"), logging.getLogger("index_curves")] for logger in loggers: @@ -78,12 +96,13 @@ if __name__ == "__main__": logger.addHandler(fh) loggers[1].info(f"filling {index} {series}") - g = calibrate_portfolio(index, series, ['3yr', '5yr', '7yr', '10yr'], - start_date) + g = calibrate_portfolio(index, series, ["3yr", "5yr", "7yr", "10yr"], start_date) with conn.cursor() as c: for id, t in g: - c.execute("INSERT INTO index_risk2 VALUES(%s, %s, %s, %s) ON CONFLICT (id) " - "DO UPDATE SET theta=%s, duration=%s, tweak=%s", - (id,) + tuple(t) + tuple(t)) + c.execute( + "INSERT INTO index_risk2 VALUES(%s, %s, %s, %s) ON CONFLICT (id) " + "DO UPDATE SET theta=%s, duration=%s, tweak=%s", + (id,) + tuple(t) + tuple(t), + ) conn.commit() conn.close() diff --git a/python/cds_rebook.py b/python/cds_rebook.py index d4c4eb56..fa826913 100644 --- a/python/cds_rebook.py +++ b/python/cds_rebook.py @@ -5,6 +5,7 @@ from pyisda.date import default_accrual, previous_twentieth from analytics.index import CreditIndex from copy import copy + def get_outstanding_positions(trade_date): r = dawn_engine.execute( "SELECT security_id, notional, folder, nextredindexcode, currency, " @@ -13,65 +14,83 @@ def get_outstanding_positions(trade_date): "JOIN index_version_markit " "ON a.security_id=index_version_markit.redindexcode " "WHERE nextredindexcode IS NOT NULL", - (trade_date,)) + (trade_date,), + ) return r + def default_adjustment(company_id, end_date): - r = serenitas_engine.execute("SELECT recovery, event_date, auction_date FROM defaulted " - "WHERE id=%s", (company_id,)) + r = serenitas_engine.execute( + "SELECT recovery, event_date, auction_date FROM defaulted " "WHERE id=%s", + (company_id,), + ) recovery, event_date, auction_date = next(r) fee = 1 - recovery start_date = previous_twentieth(event_date) - accrual_days, _ = default_accrual(auction_date, event_date, - start_date, end_date, 1., 1.) + accrual_days, _ = default_accrual( + auction_date, event_date, start_date, end_date, 1.0, 1.0 + ) return accrual_days, fee -PORTFOLIO = {"HYOPTDEL": "OPTIONS", - "HEDGE_MBS": "MORTGAGES"} + +PORTFOLIO = {"HYOPTDEL": "OPTIONS", "HEDGE_MBS": "MORTGAGES"} + def rebook(trade_date, company_id): upfront_settle_date = trade_date + 3 * BDay() effective_date = trade_date + datetime.timedelta(days=1) for r in get_outstanding_positions(trade_date): - accrual_days, fee = default_adjustment(company_id, r['maturity']) - index_new = CreditIndex(redcode=r['nextredindexcode'], - maturity=r['maturity'], - value_date=trade_date, - notional=r['notional']) + accrual_days, fee = default_adjustment(company_id, r["maturity"]) + index_new = CreditIndex( + redcode=r["nextredindexcode"], + maturity=r["maturity"], + value_date=trade_date, + notional=r["notional"], + ) - adj = (fee - accrual_days * index_new.fixed_rate * 1e-4 /360) * \ - r['notional'] * (r['indexfactor'] - index_new.factor) + adj = ( + (fee - accrual_days * index_new.fixed_rate * 1e-4 / 360) + * r["notional"] + * (r["indexfactor"] - index_new.factor) + ) index_new.mark() - trade_new = {'action': 'NEW', - 'portfolio': PORTFOLIO[r['folder']], - 'folder': r['folder'], - 'cp_code': 'INTERCO', - 'custodian': 'NONE', - 'trade_date': trade_date, - 'effective_date': effective_date, - 'maturity': r['maturity'], - 'currency': r['currency'], - 'payment_rolldate': 'Following', - 'notional': abs(r['notional']), - 'fixed_rate': index_new.fixed_rate / 100, - 'day_count': 'ACT/360', - 'frequency': 4, - 'protection': index_new.direction, - 'security_id': r['nextredindexcode'], - 'security_desc': f"CDX {index_new.index_type} CDSI S{index_new.series} 5Y", - 'upfront': index_new.pv, - 'upfront_settle_date': upfront_settle_date, - 'swap_type': 'CD_INDEX'} + trade_new = { + "action": "NEW", + "portfolio": PORTFOLIO[r["folder"]], + "folder": r["folder"], + "cp_code": "INTERCO", + "custodian": "NONE", + "trade_date": trade_date, + "effective_date": effective_date, + "maturity": r["maturity"], + "currency": r["currency"], + "payment_rolldate": "Following", + "notional": abs(r["notional"]), + "fixed_rate": index_new.fixed_rate / 100, + "day_count": "ACT/360", + "frequency": 4, + "protection": index_new.direction, + "security_id": r["nextredindexcode"], + "security_desc": f"CDX {index_new.index_type} CDSI S{index_new.series} 5Y", + "upfront": index_new.pv, + "upfront_settle_date": upfront_settle_date, + "swap_type": "CD_INDEX", + } trade_prev = copy(trade_new) - trade_prev['protection'] = "Seller" if trade_new['protection'] == "Buyer" else "Buyer" - trade_prev['upfront'] = adj - index_new.pv - trade_prev['security_id'] = r['security_id'] - sql_str = (f"INSERT INTO cds({','.join(trade_new.keys())}) " - f"VALUES({','.join(['%s'] * len(trade_new))})") + trade_prev["protection"] = ( + "Seller" if trade_new["protection"] == "Buyer" else "Buyer" + ) + trade_prev["upfront"] = adj - index_new.pv + trade_prev["security_id"] = r["security_id"] + sql_str = ( + f"INSERT INTO cds({','.join(trade_new.keys())}) " + f"VALUES({','.join(['%s'] * len(trade_new))})" + ) dawn_engine.execute(sql_str, [trade_prev.values(), trade_new.values()]) + if __name__ == "__main__": # PKD - #rebook(datetime.date(2019, 1, 24), 101148) + # rebook(datetime.date(2019, 1, 24), 101148) # WINDSSE rebook(datetime.date(2019, 4, 8), 36806879) diff --git a/python/exchange.py b/python/exchange.py index bb1b660c..fce17e2e 100644 --- a/python/exchange.py +++ b/python/exchange.py @@ -4,35 +4,40 @@ import json def get_account(email_address): - with open(Path('.credentials') / (email_address + '.json')) as fh: + with open(Path(".credentials") / (email_address + ".json")) as fh: creds = json.load(fh) credentials = Credentials(**creds) - config = Configuration(server='autodiscover.lmcg.com', credentials=credentials) - return Account(primary_smtp_address=email_address, config=config, - autodiscover=False, access_type=DELEGATE) + config = Configuration(server="autodiscover.lmcg.com", credentials=credentials) + return Account( + primary_smtp_address=email_address, + config=config, + autodiscover=False, + access_type=DELEGATE, + ) + class ExchangeMessage: _account = get_account("ghorel@lmcg.com") - def get_msgs(self, count=None, - path=['GS', 'Swaptions'], **filters): + def get_msgs(self, count=None, path=["GS", "Swaptions"], **filters): folder = self._account.inbox for p in path: folder /= p folder = folder.filter(**filters) if count: - for msg in folder.all().order_by('-datetime_sent')[:count]: + for msg in folder.all().order_by("-datetime_sent")[:count]: yield msg else: - for msg in folder.all().order_by('-datetime_sent'): + for msg in folder.all().order_by("-datetime_sent"): yield msg - def send_email(self, subject, body, to_recipients, - cc_recipients): - m = Message(account=self._account, - folder=self._account.sent, - subject=subject, - body=body, - to_recipients=to_recipients, - cc_recipients=cc_recipients) + def send_email(self, subject, body, to_recipients, cc_recipients): + m = Message( + account=self._account, + folder=self._account.sent, + subject=subject, + body=body, + to_recipients=to_recipients, + cc_recipients=cc_recipients, + ) m.send_and_save() diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py index 08d3418f..12cf9b41 100644 --- a/python/external_deriv_marks.py +++ b/python/external_deriv_marks.py @@ -7,17 +7,17 @@ from pathlib import Path DAILY_DIR = Path(os.environ["DAILY_DIR"]) + def gs_navs(): d = {} for fname in (DAILY_DIR / "GS_reports").glob("Trade_Detail*.xls"): try: - df = pd.read_excel(fname, skiprows=9, skipfooter=77, - index_col='Trade Id') + df = pd.read_excel(fname, skiprows=9, skipfooter=77, index_col="Trade Id") except ValueError: continue - df['Trade Date'] = pd.to_datetime(df['Trade Date']) - df = df[['Trade Date', 'Buy/Sell', 'Notional (USD)', 'NPV (USD)']] - df.columns = ['trade_date', 'buy/sell', 'notional', 'nav'] + df["Trade Date"] = pd.to_datetime(df["Trade Date"]) + df = df[["Trade Date", "Buy/Sell", "Notional (USD)", "NPV (USD)"]] + df.columns = ["trade_date", "buy/sell", "notional", "nav"] name = fname.name.replace("9972734", "") m = re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", name) if m: @@ -30,10 +30,12 @@ def gs_navs(): def ms_navs(): d = {} for fname in (DAILY_DIR / "MS_reports").glob("Trade_Detail*.xls"): - df = pd.read_excel(fname, index_col='trade_id') + df = pd.read_excel(fname, index_col="trade_id") df.trade_date = pd.to_datetime(df.trade_date) - df = df[['trade_date', 'pay_rec', 'notional_in_trade_ccy', 'exposure_in_rpt_ccy']] - df.columns = ['trade_date', 'buy/sell', 'notional', 'nav'] + df = df[ + ["trade_date", "pay_rec", "notional_in_trade_ccy", "exposure_in_rpt_ccy"] + ] + df.columns = ["trade_date", "buy/sell", "notional", "nav"] m = re.match(r"[^\d]*(\d{8})", fname.name) if m: date_string, = m.groups() @@ -41,11 +43,15 @@ def ms_navs(): d[date] = df return pd.concat(d) + if __name__ == "__main__": for cp in ["MS", "GS"]: df = globals()[f"{cp.lower()}_navs"]() - with dbconn('dawndb') as conn: + with dbconn("dawndb") as conn: with conn.cursor() as c: - for k, v in df[['nav']].iterrows(): - c.execute("INSERT INTO external_marks_deriv " - "VALUES(%s, %s, %s, %s) ON CONFLICT DO NOTHING", (*k, float(v), cp)) + for k, v in df[["nav"]].iterrows(): + c.execute( + "INSERT INTO external_marks_deriv " + "VALUES(%s, %s, %s, %s) ON CONFLICT DO NOTHING", + (*k, float(v), cp), + ) diff --git a/python/globeop_reports.py b/python/globeop_reports.py index ffbd6a4a..b36d728f 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -10,122 +10,152 @@ import numpy as np import datetime -def get_monthly_pnl(group_by=['identifier']): +def get_monthly_pnl(group_by=["identifier"]): sql_string = "SELECT * FROM pnl_reports" - df_pnl = pd.read_sql_query(sql_string, dawn_engine, - parse_dates=['date'], - index_col=['date']) - df_pnl['identifier'] = df_pnl.invid.str.replace("_A$", "") - pnl_cols = ['bookunrealmtm', 'bookrealmtm', 'bookrealincome', 'bookunrealincome', 'totalbookpl'] - monthend_pnl = df_pnl.groupby(pd.Grouper(freq='M'), group_keys=False).apply(lambda df: df.loc[df.index[-1]]) - return monthend_pnl.groupby(['date'] + group_by)[['mtd' + col for col in pnl_cols]].sum() + df_pnl = pd.read_sql_query( + sql_string, dawn_engine, parse_dates=["date"], index_col=["date"] + ) + df_pnl["identifier"] = df_pnl.invid.str.replace("_A$", "") + pnl_cols = [ + "bookunrealmtm", + "bookrealmtm", + "bookrealincome", + "bookunrealincome", + "totalbookpl", + ] + monthend_pnl = df_pnl.groupby(pd.Grouper(freq="M"), group_keys=False).apply( + lambda df: df.loc[df.index[-1]] + ) + return monthend_pnl.groupby(["date"] + group_by)[ + ["mtd" + col for col in pnl_cols] + ].sum() def get_portfolio(report_date=None): if report_date is not None: sql_string = "SELECT * FROM valuation_reports where periodenddate = %s" - df = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['periodenddate'], - index_col=['periodenddate'], params=[report_date,]) + df = pd.read_sql_query( + sql_string, + dawn_engine, + parse_dates=["periodenddate"], + index_col=["periodenddate"], + params=[report_date], + ) else: sql_string = "SELECT * FROM valuation_reports" - df = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['periodenddate'], - index_col=['periodenddate']) - df['identifier'] = df.invid.str.replace("_A$", "") + df = pd.read_sql_query( + sql_string, + dawn_engine, + parse_dates=["periodenddate"], + index_col=["periodenddate"], + ) + df["identifier"] = df.invid.str.replace("_A$", "") return df def trade_performance(): sql_string = "SELECT * FROM bonds" - df_trades = pd.read_sql_query(sql_string, dawn_engine, - parse_dates={'lastupdate': {'utc': True}, - 'trade_date': {}, - 'settle_date': {}}) - df_trades = df_trades[df_trades['asset_class'] == 'Subprime'] + df_trades = pd.read_sql_query( + sql_string, + dawn_engine, + parse_dates={"lastupdate": {"utc": True}, "trade_date": {}, "settle_date": {}}, + ) + df_trades = df_trades[df_trades["asset_class"] == "Subprime"] df_pnl = get_monthly_pnl() - df_sell = df_trades[~df_trades.buysell].groupby('identifier').last().reset_index() + df_sell = df_trades[~df_trades.buysell].groupby("identifier").last().reset_index() df_sell.identifier = df_sell.identifier.str[:9] - df_sell['trade_pnl_date'] = df_sell.trade_date + MonthEnd(0) - df_buy = df_trades[df_trades.buysell].groupby('identifier').last().reset_index() + df_sell["trade_pnl_date"] = df_sell.trade_date + MonthEnd(0) + df_buy = df_trades[df_trades.buysell].groupby("identifier").last().reset_index() - df_all = df_sell.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier']) - df_all = df_all.merge(df_pnl.reset_index()[['date', 'identifier', 'mtdtotalbookpl']], - left_on=['trade_pnl_date', 'identifier'], - right_on=['date', 'identifier'], - suffixes=('', '_at_trade_month')) - df_all = df_all.drop(['date', 'trade_pnl_date'], axis=1) + df_all = df_sell.merge( + df_pnl.groupby("identifier").sum().reset_index(), on=["identifier"] + ) + df_all = df_all.merge( + df_pnl.reset_index()[["date", "identifier", "mtdtotalbookpl"]], + left_on=["trade_pnl_date", "identifier"], + right_on=["date", "identifier"], + suffixes=("", "_at_trade_month"), + ) + df_all = df_all.drop(["date", "trade_pnl_date"], axis=1) - #now build up the table - g = df_buy.groupby('identifier').sum() + # now build up the table + g = df_buy.groupby("identifier").sum() init_inv = g.principal_payment + g.accrued_payment - init_inv.name = 'initialinvestment' + init_inv.name = "initialinvestment" - first_buy_date = df_buy.groupby('identifier').first().trade_date - first_buy_date.name = 'firstbuydate' + first_buy_date = df_buy.groupby("identifier").first().trade_date + first_buy_date.name = "firstbuydate" - df_all = df_all.join(init_inv, on='identifier') - df_all = df_all.join(first_buy_date, on='identifier') - df_all['percent_gain'] = df_all.mtdtotalbookpl / df_all.initialinvestment - df_all['days_held'] = df_all.trade_date - df_all.firstbuydate + df_all = df_all.join(init_inv, on="identifier") + df_all = df_all.join(first_buy_date, on="identifier") + df_all["percent_gain"] = df_all.mtdtotalbookpl / df_all.initialinvestment + df_all["days_held"] = df_all.trade_date - df_all.firstbuydate - df_all = df_all.sort_values('trade_date', ascending=False) + df_all = df_all.sort_values("trade_date", ascending=False) return df_all def get_net_navs(): sql_string = "SELECT * FROM valuation_reports" - df_val = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['periodenddate']) - nav = df_val[df_val.fund == 'SERCGMAST'].groupby('periodenddate')['endbooknav'].sum() - nav = nav.resample('M').last() - df = pd.read_csv('/home/serenitas/edwin/Python/subscription_fee_data.csv', - parse_dates=['date'], - index_col =['date']) - df.index = df.index.to_period('M').to_timestamp('M') + df_val = pd.read_sql_query(sql_string, dawn_engine, parse_dates=["periodenddate"]) + nav = ( + df_val[df_val.fund == "SERCGMAST"].groupby("periodenddate")["endbooknav"].sum() + ) + nav = nav.resample("M").last() + df = pd.read_csv( + "/home/serenitas/edwin/Python/subscription_fee_data.csv", + parse_dates=["date"], + index_col=["date"], + ) + df.index = df.index.to_period("M").to_timestamp("M") df = df.join(nav) - df['begbooknav'] = (df.endbooknav + df.net_flow).shift(1) - df.at[('2013-01-31', 'begbooknav')] = 12500000 + df["begbooknav"] = (df.endbooknav + df.net_flow).shift(1) + df.at[("2013-01-31", "begbooknav")] = 12500000 return df def shift_cash(date, amount, df, strat): nav = get_net_navs() - df.loc[date, strat] = df.loc[date, strat] - amount/nav.loc[date].endbooknav - df.loc[date,'Cash'] = df.loc[date, 'Cash'] + amount/nav.loc[date].endbooknav + df.loc[date, strat] = df.loc[date, strat] - amount / nav.loc[date].endbooknav + df.loc[date, "Cash"] = df.loc[date, "Cash"] + amount / nav.loc[date].endbooknav return df def calc_trade_performance_stats(): - df = trade_performance().set_index('trade_date') + df = trade_performance().set_index("trade_date") df.days_held = df.days_held.dt.days - df['winners'] = df.percent_gain > 0 - df['curr_face'] = df.principal_payment/(df.price/100) + df["winners"] = df.percent_gain > 0 + df["curr_face"] = df.principal_payment / (df.price / 100) - index = ['All', '2017', '2016', '2015', '2014', '2013'] + index = ["All", "2017", "2016", "2015", "2014", "2013"] results = pd.DataFrame(index=index) win_per = df.winners.mean() loss_per = 1 - win_per - for x, df1 in df.groupby('winners'): - for y, df2 in df1.groupby(pd.Grouper(freq='A')): + for x, df1 in df.groupby("winners"): + for y, df2 in df1.groupby(pd.Grouper(freq="A")): y = y.date().year - results.loc[y] = df2[df2.days_held.notnull()].mean()[['curr_face', 'initialinvestment', 'days_held']] + results.loc[y] = df2[df2.days_held.notnull()].mean()[ + ["curr_face", "initialinvestment", "days_held"] + ] - df[df.days_held.notnull()]['days_held'].groupby(pd.Grouper(freq='A')).mean() + df[df.days_held.notnull()]["days_held"].groupby(pd.Grouper(freq="A")).mean() -def hist_pos(asset_class = 'rmbs', dm=False): +def hist_pos(asset_class="rmbs", dm=False): end_date = pd.datetime.today() - MonthEnd(1) - dates = pd.date_range(datetime.date(2013,1,31), end_date, freq='M') + dates = pd.date_range(datetime.date(2013, 1, 31), end_date, freq="M") calc_df = pd.DataFrame() for d in dates: - if asset_class == 'rmbs': - if d.date() == datetime.date(2018,11,30): - d_1 = datetime.date(2018,12,3) - elif d.date() == datetime.date(2016,2,29): - d_1 = datetime.date(2016,2,29) + if asset_class == "rmbs": + if d.date() == datetime.date(2018, 11, 30): + d_1 = datetime.date(2018, 12, 3) + elif d.date() == datetime.date(2016, 2, 29): + d_1 = datetime.date(2016, 2, 29) else: d_1 = None calc_df = calc_df.append(rmbs_pos(d, d_1, dm)) @@ -133,92 +163,118 @@ def hist_pos(asset_class = 'rmbs', dm=False): calc_df = calc_df.append(clo_pos(d), sort=True) return calc_df + def rmbs_pos(date, model_date=None, dm=False): date = date.date() if isinstance(date, pd.Timestamp) else date - #just non-zero factor bonds for now, need to incorporate that + # just non-zero factor bonds for now, need to incorporate that pos = get_portfolio(date) - pos = pos[(pos.port == 'MORTGAGES') & - (pos.endbookmv > 0) & - (pos.custacctname == 'V0NSCLMAMB') & - (pos['invid'].str.len() == 9)] - pos = pos[['endbookmv', 'endlocalmarketprice', 'identifier']] + pos = pos[ + (pos.port == "MORTGAGES") + & (pos.endbookmv > 0) + & (pos.custacctname == "V0NSCLMAMB") + & (pos["invid"].str.len() == 9) + ] + pos = pos[["endbookmv", "endlocalmarketprice", "identifier"]] - sql_string = ("SELECT distinct timestamp FROM priced where " - "normalization = 'current_notional' and " - "model_version = 1 and " - "date(timestamp) < %s and date(timestamp) > %s " - "order by timestamp desc") - timestamps = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['timestamp'], - params=[date, date - DateOffset(15, 'D')]) + sql_string = ( + "SELECT distinct timestamp FROM priced where " + "normalization = 'current_notional' and " + "model_version = 1 and " + "date(timestamp) < %s and date(timestamp) > %s " + "order by timestamp desc" + ) + timestamps = pd.read_sql_query( + sql_string, + dawn_engine, + parse_dates=["timestamp"], + params=[date, date - DateOffset(15, "D")], + ) if model_date is None: model_date = (timestamps.loc[0][0]).date() yc = YieldTermStructure() yc.link_to(YC(evaluation_date=model_date)) - sql_string = ("SELECT date(timestamp) as timestamp, cusip, model_version, " - "pv, moddur, delta_yield, delta_ir " - "FROM priced where date(timestamp) = %s " - "and model_version <> 2") + sql_string = ( + "SELECT date(timestamp) as timestamp, cusip, model_version, " + "pv, moddur, delta_yield, delta_ir " + "FROM priced where date(timestamp) = %s " + "and model_version <> 2" + ) params_list = (model_date,) if date > datetime.date(2017, 9, 30): - r = dawn_engine.execute("SELECT latest_sim FROM latest_sim(%s)", - model_date) + r = dawn_engine.execute("SELECT latest_sim FROM latest_sim(%s)", model_date) model_id, = next(r) sql_string += " AND model_id_sub = %s" params_list += (model_id,) - model = pd.read_sql_query(sql_string, dawn_engine, parse_dates=['timestamp'], - params=params_list) - model = model[model['pv'] != 0] - pos = pos.assign(curr_ntl = pos.endbookmv/pos.endlocalmarketprice *100) - comb_g = pos.groupby('identifier').agg({'endbookmv': 'sum', - 'curr_ntl': 'sum'}) - comb_g['date'] = pd.to_datetime(date) - model = pd.merge(comb_g, model, left_on='identifier', right_on='cusip') - positions = model.set_index(['cusip', 'model_version']).unstack(1).dropna() - v1 = positions.xs(1, level='model_version', axis=1) - v3 = positions.xs(3, level='model_version', axis=1) + model = pd.read_sql_query( + sql_string, dawn_engine, parse_dates=["timestamp"], params=params_list + ) + model = model[model["pv"] != 0] + pos = pos.assign(curr_ntl=pos.endbookmv / pos.endlocalmarketprice * 100) + comb_g = pos.groupby("identifier").agg({"endbookmv": "sum", "curr_ntl": "sum"}) + comb_g["date"] = pd.to_datetime(date) + model = pd.merge(comb_g, model, left_on="identifier", right_on="cusip") + positions = model.set_index(["cusip", "model_version"]).unstack(1).dropna() + v1 = positions.xs(1, level="model_version", axis=1) + v3 = positions.xs(3, level="model_version", axis=1) if dm is True: libor1m = USDLibor(Period(1, Months), yc) - libor = libor1m.fixing(libor1m.fixing_calendar.adjust(Date.from_datetime(model_date))) - v3 = v3.assign(b_yield = v3.moddur.apply(lambda x: float(yc.zero_rate(x))) - - libor) + libor = libor1m.fixing( + libor1m.fixing_calendar.adjust(Date.from_datetime(model_date)) + ) + v3 = v3.assign( + b_yield=v3.moddur.apply(lambda x: float(yc.zero_rate(x))) - libor + ) else: - v3 = v3.assign(b_yield = v3.moddur.apply(lambda x: float(yc.zero_rate(x)))) - v3.b_yield += np.minimum((v1.pv * v1.curr_ntl/ v1.endbookmv) - ** (1/v1.moddur) - 1, 1).dropna() + v3 = v3.assign(b_yield=v3.moddur.apply(lambda x: float(yc.zero_rate(x)))) + v3.b_yield += np.minimum( + (v1.pv * v1.curr_ntl / v1.endbookmv) ** (1 / v1.moddur) - 1, 1 + ).dropna() v3.delta_yield *= v3.endbookmv / v3.pv - v3.delta_ir *= np.minimum(1, 1/v3.moddur) * \ - (v3.endbookmv/v3.curr_ntl)/ v3.pv * v3.curr_ntl - return v3.reset_index().set_index('date') + v3.delta_ir *= ( + np.minimum(1, 1 / v3.moddur) + * (v3.endbookmv / v3.curr_ntl) + / v3.pv + * v3.curr_ntl + ) + return v3.reset_index().set_index("date") + def clo_pos(date): date = date.date() if isinstance(date, pd.Timestamp) else date df = get_portfolio(date) - df = df[(df.port == 'CLO') & - (df.endbookmv > 0) & - (df.custacctname == 'V0NSCLMAMB') & - (df['invid'].str.len() >= 9)] - df = df[['endbookmv', 'endlocalmarketprice', 'identifier']] + df = df[ + (df.port == "CLO") + & (df.endbookmv > 0) + & (df.custacctname == "V0NSCLMAMB") + & (df["invid"].str.len() >= 9) + ] + df = df[["endbookmv", "endlocalmarketprice", "identifier"]] if df.empty is True: return df else: - sql_string = "select distinct cusip, identifier from bonds where asset_class = 'CLO'" - cusip_map = {identifier: cusip for cusip, identifier in dawn_engine.execute(sql_string)} - df['cusip'] = df['identifier'].replace(cusip_map) - cusips = df.loc[[df.index[-1]], 'cusip'] + sql_string = ( + "select distinct cusip, identifier from bonds where asset_class = 'CLO'" + ) + cusip_map = { + identifier: cusip for cusip, identifier in dawn_engine.execute(sql_string) + } + df["cusip"] = df["identifier"].replace(cusip_map) + cusips = df.loc[[df.index[-1]], "cusip"] placeholders = ",".join(["%s"] * (1 + len(cusips))) sql_string = f"SELECT * FROM historical_cusip_risk({placeholders})" - etengine = dbengine('etdb') - model = pd.read_sql_query(sql_string, etengine, parse_dates=['pricingdate'], - params=(date, *cusips)) + etengine = dbengine("etdb") + model = pd.read_sql_query( + sql_string, etengine, parse_dates=["pricingdate"], params=(date, *cusips) + ) model.index = cusips - calc_df = df.loc[[df.index[-1]]].set_index('cusip').join(model) - calc_df['hy_equiv'] = calc_df.delta * calc_df.endbookmv - calc_df['date'] = date - return calc_df.set_index('date') + calc_df = df.loc[[df.index[-1]]].set_index("cusip").join(model) + calc_df["hy_equiv"] = calc_df.delta * calc_df.endbookmv + calc_df["date"] = date + return calc_df.set_index("date") diff --git a/python/gmail_helpers.py b/python/gmail_helpers.py index 37cf9cbe..7b47ce10 100644 --- a/python/gmail_helpers.py +++ b/python/gmail_helpers.py @@ -11,9 +11,10 @@ import json import oauth2client import os -SCOPES = ['https://www.googleapis.com/auth/gmail.modify'] -CLIENT_SECRET_FILE = 'secret.json' -APPLICATION_NAME = 'Swaptions' +SCOPES = ["https://www.googleapis.com/auth/gmail.modify"] +CLIENT_SECRET_FILE = "secret.json" +APPLICATION_NAME = "Swaptions" + def get_gmail_service(): """Gets valid user credentials from storage. @@ -24,25 +25,35 @@ def get_gmail_service(): Returns: Credentials, the obtained credential. """ - credential_dir = '.credentials' + credential_dir = ".credentials" if not os.path.exists(credential_dir): os.makedirs(credential_dir) - credential_path = os.path.join(credential_dir, - 'guillaume.horel@serenitascapital.com.json') + credential_path = os.path.join( + credential_dir, "guillaume.horel@serenitascapital.com.json" + ) try: credentials = Credentials.from_authorized_user_file(credential_path) except: flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES) credentials = flow.run_console() - to_save= {} - for attr in ["token", "refresh_token", "id_token", "token_uri", "client_id", "client_secret", "scopes"]: + to_save = {} + for attr in [ + "token", + "refresh_token", + "id_token", + "token_uri", + "client_id", + "client_secret", + "scopes", + ]: to_save[attr] = getattr(credentials, attr) with open(credential_path, "w") as fh: json.dump(to_save, fh) - service = build('gmail', 'v1', credentials=credentials) + service = build("gmail", "v1", credentials=credentials) return service + def ListMessagesWithLabels(service, user_id, label_ids=[]): """List all Messages of the user's mailbox with label_ids applied. @@ -58,19 +69,26 @@ def ListMessagesWithLabels(service, user_id, label_ids=[]): appropriate id to get the details of a Message. """ try: - response = service.users().messages().list(userId=user_id, - labelIds=label_ids).execute() - if 'messages' in response: - yield from response['messages'] - while 'nextPageToken' in response: - page_token = response['nextPageToken'] - response = service.users().messages().list(userId=user_id, - labelIds=label_ids, - pageToken=page_token).execute() - yield from response['messages'] + response = ( + service.users() + .messages() + .list(userId=user_id, labelIds=label_ids) + .execute() + ) + if "messages" in response: + yield from response["messages"] + while "nextPageToken" in response: + page_token = response["nextPageToken"] + response = ( + service.users() + .messages() + .list(userId=user_id, labelIds=label_ids, pageToken=page_token) + .execute() + ) + yield from response["messages"] except errors.HttpError as error: - print(json.loads(error.content.decode('utf-8'))['error']['message']) + print(json.loads(error.content.decode("utf-8"))["error"]["message"]) def ListHistory(service, user_id, label_id=None, start_history_id=10000): @@ -85,26 +103,36 @@ def ListHistory(service, user_id, label_id=None, start_history_id=10000): Returns: A list of mailbox changes that occurred after the start_history_id. """ - history = (service.users().history().list(userId=user_id, - startHistoryId=start_history_id, - historyTypes="messageAdded", - labelId=label_id) - .execute()) - changes = history['history'] if 'history' in history else [] + history = ( + service.users() + .history() + .list( + userId=user_id, + startHistoryId=start_history_id, + historyTypes="messageAdded", + labelId=label_id, + ) + .execute() + ) + changes = history["history"] if "history" in history else [] for change in changes: - if 'messagesAdded' in change: - for c in change['messagesAdded']: - yield c['message'] + if "messagesAdded" in change: + for c in change["messagesAdded"]: + yield c["message"] + + while "nextPageToken" in history: + page_token = history["nextPageToken"] + history = ( + service.users() + .history() + .list(userId=user_id, startHistoryId=start_history_id, pageToken=page_token) + .execute() + ) + for change in history["history"]: + if "messagesAdded" in change: + for c in change["messagesAdded"]: + yield c["message"] - while 'nextPageToken' in history: - page_token = history['nextPageToken'] - history = (service.users().history().list(userId=user_id, - startHistoryId=start_history_id, - pageToken=page_token).execute()) - for change in history['history']: - if 'messagesAdded' in change: - for c in change['messagesAdded']: - yield c['message'] def labels_dict(service, user_id): """Returns a dictionary mapping labels to labelids. @@ -118,10 +146,11 @@ def labels_dict(service, user_id): """ try: response = service.users().labels().list(userId=user_id).execute() - labels = response['labels'] - return {label['name']: label['id'] for label in labels} + labels = response["labels"] + return {label["name"]: label["id"] for label in labels} except errors.HttpError as error: - print(json.loads(error.content.decode('utf-8'))['error']['message']) + print(json.loads(error.content.decode("utf-8"))["error"]["message"]) + class GmailMessage(EmailMessage): _service = None @@ -132,44 +161,55 @@ class GmailMessage(EmailMessage): if GmailMessage._service is None: GmailMessage._service = get_gmail_service() if GmailMessage._labels is None: - GmailMessage._labels = labels_dict(self._service, 'me') + GmailMessage._labels = labels_dict(self._service, "me") def msgdict(self): - return {'raw': base64.urlsafe_b64encode(self.as_bytes()).decode()} + return {"raw": base64.urlsafe_b64encode(self.as_bytes()).decode()} def send(self): try: - message = (self._service.users().messages(). - send(userId='me',body=self.msgdict()) - .execute()) - print('Message Id: %s' % message['id']) + message = ( + self._service.users() + .messages() + .send(userId="me", body=self.msgdict()) + .execute() + ) + print("Message Id: %s" % message["id"]) except errors.HttpError as error: - print('An error occurred: %s' % error) + print("An error occurred: %s" % error) @classmethod def list_msg_ids(cls, label, start_history_id=None): if start_history_id is not None: - return ListHistory(cls._service, - 'me', - label_id=cls._labels[label], - start_history_id=start_history_id) + return ListHistory( + cls._service, + "me", + label_id=cls._labels[label], + start_history_id=start_history_id, + ) else: - return ListMessagesWithLabels(cls._service, - 'me', - label_ids=[cls._labels[label]]) + return ListMessagesWithLabels( + cls._service, "me", label_ids=[cls._labels[label]] + ) @classmethod - def from_id(cls, msg_id, user_id='me'): + def from_id(cls, msg_id, user_id="me"): try: - message = (cls._service.users().messages(). - get(userId=user_id, id=msg_id, format='raw').execute()) + message = ( + cls._service.users() + .messages() + .get(userId=user_id, id=msg_id, format="raw") + .execute() + ) instance = email.message_from_bytes( - base64.urlsafe_b64decode(message['raw']), - policy=email.policy.EmailPolicy()) - instance.history_id = message['historyId'] + base64.urlsafe_b64decode(message["raw"]), + policy=email.policy.EmailPolicy(), + ) + instance.history_id = message["historyId"] return instance except errors.HttpError as error: - print(json.loads(error.content.decode('utf-8'))['error']['message']) + print(json.loads(error.content.decode("utf-8"))["error"]["message"]) + def main(): """Shows basic usage of the Gmail API. @@ -179,9 +219,10 @@ def main(): """ message = GmailMessage() message.set_content("Hello everyone!") - message['To'] = 'guillaume.horel@gmail.com' - message['Subject'] = 'pomme' + message["To"] = "guillaume.horel@gmail.com" + message["Subject"] = "pomme" message.send() -if __name__ == '__main__': + +if __name__ == "__main__": message = main() diff --git a/python/handle_default.py b/python/handle_default.py index 426df961..85eb852b 100644 --- a/python/handle_default.py +++ b/python/handle_default.py @@ -2,26 +2,33 @@ from utils.db import serenitas_pool import datetime from sys import argv + def get_recovery(company_id: int, seniority: str, conn): with conn.cursor() as c: - c.execute("SELECT recovery * 100, auction_date FROM defaulted " - "WHERE id=%s AND seniority=%s", - (company_id, seniority)) + c.execute( + "SELECT recovery * 100, auction_date FROM defaulted " + "WHERE id=%s AND seniority=%s", + (company_id, seniority), + ) return c.fetchone() + def affected_indices(company_id: int, seniority: str, conn): """returns the list of indices containing company_id""" - sqlstr = ("SELECT b.*, a.curr_weight*b.indexfactor AS orig_weight " - "FROM basket_constituents_current a " - "JOIN (SELECT * FROM index_version WHERE lastdate='infinity') b " - "USING (basketid) " - "WHERE company_id=%s AND seniority=%s") + sqlstr = ( + "SELECT b.*, a.curr_weight*b.indexfactor AS orig_weight " + "FROM basket_constituents_current a " + "JOIN (SELECT * FROM index_version WHERE lastdate='infinity') b " + "USING (basketid) " + "WHERE company_id=%s AND seniority=%s" + ) print(sqlstr) with conn.cursor() as c: c.execute(sqlstr, (company_id, seniority)) recordslist = c.fetchall() return recordslist + def create_newindices(recordslist, recovery, lastdate, conn): """create the new indices versions and update the old""" insertstr = """INSERT INTO index_version(Index, Series, Version, IndexFactor, @@ -33,36 +40,40 @@ def create_newindices(recordslist, recovery, lastdate, conn): with conn.cursor() as c: newids = {} for r in recordslist: - r['indexfactor'] -= r['orig_weight'] - r['version'] += 1 - r['cumulativeloss'] += (100-recovery) * r['orig_weight'] /100 - r['lastdate'] = 'infinity' + r["indexfactor"] -= r["orig_weight"] + r["version"] += 1 + r["cumulativeloss"] += (100 - recovery) * r["orig_weight"] / 100 + r["lastdate"] = "infinity" c.execute(insertstr, r) - newids[r['basketid']] = c.fetchone()[0] + newids[r["basketid"]] = c.fetchone()[0] for oldid in newids.keys(): c.execute(updatestr, (lastdate, oldid)) conn.commit() return newids + def update_indexmembers(newids, company_id, seniority, conn): with conn.cursor() as c: for oldid, newid in newids.items(): - c.execute("INSERT INTO basket_constituents " - "(SELECT company_id, seniority, %s, weight " - "FROM basket_constituents " - "WHERE basketid=%s AND NOT (company_id=%s AND seniority=%s))", - (newid, oldid, company_id, seniority)) + c.execute( + "INSERT INTO basket_constituents " + "(SELECT company_id, seniority, %s, weight " + "FROM basket_constituents " + "WHERE basketid=%s AND NOT (company_id=%s AND seniority=%s))", + (newid, oldid, company_id, seniority), + ) conn.commit() + def update_redcodes(index_type, conn): - if index_type == 'HY': - index_subfamily = 'CDX.NA.HY' - elif index_type == 'IG': - index_subfamily = 'CDX.NA.IG' - elif index_type == 'EU': - index_subfamily = 'iTraxx Europe' - elif index_type == 'XO': - index_subfamily = 'iTraxx Europe Crossover' + if index_type == "HY": + index_subfamily = "CDX.NA.HY" + elif index_type == "IG": + index_subfamily = "CDX.NA.IG" + elif index_type == "EU": + index_subfamily = "iTraxx Europe" + elif index_type == "XO": + index_subfamily = "iTraxx Europe Crossover" with conn.cursor() as c: c.execute( @@ -72,16 +83,19 @@ def update_redcodes(index_type, conn): "AND index_version.index=%s " "AND index_version_markit.indexsubfamily=%s" "AND index_version.redindexcode IS NULL", - (index_type, index_subfamily)) + (index_type, index_subfamily), + ) conn.commit() -if __name__=="__main__": +if __name__ == "__main__": if len(argv) == 1: - print("""Usage: + print( + """Usage: python handle_default.py <company_id> <seniority> For instance: - python handle_default.py 210065 Senior""") + python handle_default.py 210065 Senior""" + ) else: conn = serenitas_pool.getconn() company_id = int(argv[1]) @@ -90,4 +104,4 @@ For instance: recordslist = affected_indices(company_id, seniority, conn) newids = create_newindices(recordslist, recovery, lastdate, conn) update_indexmembers(newids, company_id, seniority, conn) - serenitas_pool.putconn(conn,) + serenitas_pool.putconn(conn) diff --git a/python/load_globeop_report.py b/python/load_globeop_report.py index ec305a90..93d8e889 100644 --- a/python/load_globeop_report.py +++ b/python/load_globeop_report.py @@ -6,32 +6,38 @@ from dates import bus_day from utils.db import dbengine import datetime -def get_globs(fname, years=['2013', '2014', '2015', '2016', '2017']): - basedir = '/home/serenitas/Daily' - globs = [iglob(os.path.join(basedir, - year, - "{0}_*/{0}*/Reports/{1}.csv".format(year, fname))) - for year in years] + +def get_globs(fname, years=["2013", "2014", "2015", "2016", "2017"]): + basedir = "/home/serenitas/Daily" + globs = [ + iglob( + os.path.join( + basedir, year, "{0}_*/{0}*/Reports/{1}.csv".format(year, fname) + ) + ) + for year in years + ] for year in years[-2:]: - globs.append(iglob(os.path.join(basedir, - '{0}-*/Reports/{1}.csv'.format(year, - fname)))) + globs.append( + iglob(os.path.join(basedir, "{0}-*/Reports/{1}.csv".format(year, fname))) + ) return globs + def read_valuation_report(f): date = pd.Timestamp(f.parts[4]) - if date >= pd.Timestamp('2013-02-06'): - df = pd.read_csv(f, parse_dates=['KnowledgeDate', 'PeriodEndDate']) + if date >= pd.Timestamp("2013-02-06"): + df = pd.read_csv(f, parse_dates=["KnowledgeDate", "PeriodEndDate"]) else: df = pd.read_csv(f) - df['KnowledgeDate'] = date - df['PeriodEndDate'] = date - bus_day - df['row'] = df.index - if 'AccountingPeriod' in df: - del df['AccountingPeriod'] - if 'CounterParty' in df: - del df['CounterParty'] - df = df.rename(columns={'CounterPartyCode': 'counterparty'}) + df["KnowledgeDate"] = date + df["PeriodEndDate"] = date - bus_day + df["row"] = df.index + if "AccountingPeriod" in df: + del df["AccountingPeriod"] + if "CounterParty" in df: + del df["CounterParty"] + df = df.rename(columns={"CounterPartyCode": "counterparty"}) if "Strat" in df: df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1) df = df.replace({"Strat": {"TCDSCSH": "TCSH", "MTG_CRT_LD": "CRT_LD"}}) @@ -40,129 +46,181 @@ def read_valuation_report(f): df.columns = df.columns.str.lower() return df + def valuation_reports(): - df = pd.concat(read_valuation_report(f) for f in - chain.from_iterable(get_globs('Valuation_Report'))) + df = pd.concat( + read_valuation_report(f) + for f in chain.from_iterable(get_globs("Valuation_Report")) + ) # There can be duplicates in case of holidays - df = df.sort_values(['periodenddate', 'row', 'knowledgedate']) - df = df.drop_duplicates(['periodenddate', 'row'], 'last') - df.to_sql('valuation_reports', dbengine('dawndb'), if_exists='append', index=False) + df = df.sort_values(["periodenddate", "row", "knowledgedate"]) + df = df.drop_duplicates(["periodenddate", "row"], "last") + df.to_sql("valuation_reports", dbengine("dawndb"), if_exists="append", index=False) + def read_pnl_report(f): df = pd.read_csv(f) df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1) df = df.replace({"Strat": {"TCDSCSH": "TCSH", "MTG_CRT_LD": "CRT_LD"}}) df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1) - df['LongShortIndicator'] = df['LongShortIndicator'].str.strip() + df["LongShortIndicator"] = df["LongShortIndicator"].str.strip() df.columns = df.columns.str.lower().str.replace(" ", "") return df + def pnl_reports(): df = {} - for f in chain.from_iterable(get_globs('Pnl*')): + for f in chain.from_iterable(get_globs("Pnl*")): if not (f.endswith("Pnl.csv") and f.endswith("Pnl_Report.csv")): continue - date = pd.Timestamp(f.rsplit('/', 3)[1]) + date = pd.Timestamp(f.rsplit("/", 3)[1]) date = date - bus_day df[date] = read_pnl_report(f) - df = pd.concat(df, names=['date', 'row']).reset_index() - df.to_sql('pnl_reports', dbengine('dawndb'), if_exists='append', index=False) + df = pd.concat(df, names=["date", "row"]).reset_index() + df.to_sql("pnl_reports", dbengine("dawndb"), if_exists="append", index=False) + def read_cds_report(f): df = pd.read_csv(f) df2 = pd.read_csv(f.parent / "All_Report.csv") + def drop_zero_count(df): for col in df: vc = len(df[col].value_counts()) if vc == 0: del df[col] continue + drop_zero_count(df) drop_zero_count(df2) - contract = df['Contractual Definition'] - contract = contract.where(contract.isin(['ISDA2014', 'ISDA2003Cred']), 'ISDA2014') - df['Contractual Definition'] = contract - to_drop = ['Bloomberg Yellow key', 'Created User', 'Last Modified User', - 'Last Modified Date', 'Fund Long Name', 'Instrument Sub Type', - 'Netting Id', 'Client', 'Trade Status', 'Position Status', - 'Clearing Broker', 'Settle Mode', 'Off Price', 'On Price', - 'Price Ccy', 'VAT', 'SEC Fee', 'Clearing Fee', - 'Trading Notional', 'BBGID'] - df = df.drop(to_drop, - axis=1, errors='ignore') - df2 = df2.drop(to_drop, - axis=1, errors='ignore') + contract = df["Contractual Definition"] + contract = contract.where(contract.isin(["ISDA2014", "ISDA2003Cred"]), "ISDA2014") + df["Contractual Definition"] = contract + to_drop = [ + "Bloomberg Yellow key", + "Created User", + "Last Modified User", + "Last Modified Date", + "Fund Long Name", + "Instrument Sub Type", + "Netting Id", + "Client", + "Trade Status", + "Position Status", + "Clearing Broker", + "Settle Mode", + "Off Price", + "On Price", + "Price Ccy", + "VAT", + "SEC Fee", + "Clearing Fee", + "Trading Notional", + "BBGID", + ] + df = df.drop(to_drop, axis=1, errors="ignore") + df2 = df2.drop(to_drop, axis=1, errors="ignore") df.columns = df.columns.str.lower().str.replace(" ", "_") df2.columns = df2.columns.str.lower().str.replace(" ", "_") df.calendar = df.calendar.str.replace(" ", "") - df = df.rename(columns={'direction': 'buy/sell'}) + df = df.rename(columns={"direction": "buy/sell"}) df.roll_convention = df.roll_convention.str.title() - df = df[df.strategy != 'SER_TEST'] - df.loc[df.strategy == 'SERCGMAST__MBSCDS', 'strategy'] = 'MBSCDS' + df = df[df.strategy != "SER_TEST"] + df.loc[df.strategy == "SERCGMAST__MBSCDS", "strategy"] = "MBSCDS" df.strategy = df.strategy.str.replace("SER_", "") - df['buy/sell'] = df['buy/sell'].astype('category') - df['buy/sell'].cat.categories = ['Buyer', 'Seller'] - del df['independent_%'] - df2 = df2.rename(columns={'independent_%': 'independent_perc'}) - df.prime_broker = df.prime_broker.where(df.prime_broker != 'NONE') - return df.set_index('gtid').join(df2.set_index('gtid')[ - df2.columns.difference(df.columns)]).reset_index() + df["buy/sell"] = df["buy/sell"].astype("category") + df["buy/sell"].cat.categories = ["Buyer", "Seller"] + del df["independent_%"] + df2 = df2.rename(columns={"independent_%": "independent_perc"}) + df.prime_broker = df.prime_broker.where(df.prime_broker != "NONE") + return ( + df.set_index("gtid") + .join(df2.set_index("gtid")[df2.columns.difference(df.columns)]) + .reset_index() + ) + def read_swaption_report(f): df = pd.read_csv(f) df2 = pd.read_csv(f.parent / "All_Report.csv") + def drop_zero_count(df): for k, v in df.iteritems(): if len(v.value_counts()) == 0: del df[k] + drop_zero_count(df) drop_zero_count(df2) # df2 = df2[df2["Product Sub Type"] == "CD_INDEX_OPTION"] # df = df[df["Product Sub Type"] == "CD_INDEX_OPTION"] df = df.set_index("GTID").join(df2.set_index("GTID")[["Geneva ID"]]) - for key in ['Created User', 'Last Modified User', - 'Last Modified Date', 'Trade Status', 'Position Status', - 'Client', 'External Trade ID', 'Fund', 'Fund Long Name', - 'Prime Broker', 'Transaction Status', 'Created Date', 'Comments', - 'Trade Type']: + for key in [ + "Created User", + "Last Modified User", + "Last Modified Date", + "Trade Status", + "Position Status", + "Client", + "External Trade ID", + "Fund", + "Fund Long Name", + "Prime Broker", + "Transaction Status", + "Created Date", + "Comments", + "Trade Type", + ]: del df[key] for k, v in df.iteritems(): if "Date" in k and "End Date" not in k: df[k] = pd.to_datetime(v) return df + def cds_reports(): df = {} - for f in chain.from_iterable(get_globs('CDS_Report')): - date = pd.Timestamp(f.rsplit('/', 3)[1]) - old_report = date <= pd.Timestamp('2017-02-28') or date == pd.Timestamp('2017-03-02') + for f in chain.from_iterable(get_globs("CDS_Report")): + date = pd.Timestamp(f.rsplit("/", 3)[1]) + old_report = date <= pd.Timestamp("2017-02-28") or date == pd.Timestamp( + "2017-03-02" + ) date = date - bus_day df[date] = read_cds_report(f, old_report) - df = pd.concat(df, names=['date', 'row']).reset_index() + df = pd.concat(df, names=["date", "row"]).reset_index() return df + def monthly_pnl_bycusip(df, strats): - df = df[(df.strat.isin(strats)) & (df.custacctname=='V0NSCLMAMB')] - pnl_cols = ['bookunrealmtm', 'bookrealmtm', 'bookrealincome', 'bookunrealincome', 'totalbookpl'] - return df.groupby('invid').resample('M').last()[['mtd'+col for col in pnl_cols]] + df = df[(df.strat.isin(strats)) & (df.custacctname == "V0NSCLMAMB")] + pnl_cols = [ + "bookunrealmtm", + "bookrealmtm", + "bookrealincome", + "bookunrealincome", + "totalbookpl", + ] + return df.groupby("invid").resample("M").last()[["mtd" + col for col in pnl_cols]] + -if __name__=='__main__': +if __name__ == "__main__": valuation_reports() pnl_reports() - df_val = pd.read_hdf('globeop.hdf', 'valuation_report') - df_pnl = pd.read_hdf('globeop.hdf', 'pnl') - nav = df_val[df_val.Fund == 'SERCGMAST'].groupby('PeriodEndDate')['EndBookNAV'].sum() - subprime_strats = ['MTG_GOOD', 'MTG_RW', 'MTG_IO','MTG_THRU', 'MTG_B4PR'] - clo_strats = ['CLO_BBB', 'CLO_AAA', 'CLO_BB20'] + df_val = pd.read_hdf("globeop.hdf", "valuation_report") + df_pnl = pd.read_hdf("globeop.hdf", "pnl") + nav = ( + df_val[df_val.Fund == "SERCGMAST"].groupby("PeriodEndDate")["EndBookNAV"].sum() + ) + subprime_strats = ["MTG_GOOD", "MTG_RW", "MTG_IO", "MTG_THRU", "MTG_B4PR"] + clo_strats = ["CLO_BBB", "CLO_AAA", "CLO_BB20"] ## daily pnl by cusip - #subprime_daily_pnl = daily_pnl_bycusip(df_pnl, subprime_strats) + # subprime_daily_pnl = daily_pnl_bycusip(df_pnl, subprime_strats) df_monthly = monthly_pnl_bycusip(df_pnl, subprime_strats) - #df_monthly.loc[idx[ts('2015-01-01'):ts('2015-01-31'),:],:] + # df_monthly.loc[idx[ts('2015-01-01'):ts('2015-01-31'),:],:] # clo = df_pnl[df_pnl.Strat.isin(clo_strats)] # clo_monthly_pnl = clo.groupby(level=0).sum()['MTD TotalBookPL'].resample('M').last() # clo.groupby(level=0).sum()['2015-12-01':'2015-12-31'] - df_val.set_index(['custacctname', 'periodenddate', 'invid', 'strat']) + df_val.set_index(["custacctname", "periodenddate", "invid", "strat"]) diff --git a/python/load_refentity.py b/python/load_refentity.py index 7e6a9f6b..2d07bf62 100644 --- a/python/load_refentity.py +++ b/python/load_refentity.py @@ -3,9 +3,11 @@ from pathlib import Path import datetime import re import lxml.etree as etree + parser = etree.XMLParser(remove_blank_text=True) from psycopg2.extras import Json + def todict(xml, uselist=set()): if len(xml): if xml.tag in uselist: @@ -16,7 +18,7 @@ def todict(xml, uselist=set()): if c.tag in d: d[c.tag].append(todict(c, uselist)) else: - if c.tag in ['cdssuccession', 'creditevent', 'auctionccyrate']: + if c.tag in ["cdssuccession", "creditevent", "auctionccyrate"]: d[c.tag] = [todict(c, uselist)] else: d[c.tag] = todict(c, uselist) @@ -24,9 +26,10 @@ def todict(xml, uselist=set()): else: return xml.text.strip() + def dispatch_parsing(col, uselist): if len(col): - if col.tag == 'ratings': + if col.tag == "ratings": return [el.text for el in col.iterfind(".//tier")] else: return Json(todict(col, uselist)) @@ -36,23 +39,51 @@ def dispatch_parsing(col, uselist): else: return col.text + def insert_refentity(fname): - tree = etree.parse(fname, - parser=parser) - conn = dbconn('serenitasdb') - names = ['referenceentity', 'shortname', 'ticker', 'redentitycode', - 'entitycusip', 'lei', 'entitytype', 'jurisdiction', 'depthlevel', - 'markitsector', 'isdatradingdefinition', 'recorddate', 'ratings', - 'entityform', 'companynumber', 'alternativenames', - 'isdatransactiontypes', 'validto', 'validfrom', 'events', - 'holdco', 'country',] + tree = etree.parse(fname, parser=parser) + conn = dbconn("serenitasdb") + names = [ + "referenceentity", + "shortname", + "ticker", + "redentitycode", + "entitycusip", + "lei", + "entitytype", + "jurisdiction", + "depthlevel", + "markitsector", + "isdatradingdefinition", + "recorddate", + "ratings", + "entityform", + "companynumber", + "alternativenames", + "isdatransactiontypes", + "validto", + "validfrom", + "events", + "holdco", + "country", + ] # these are tags which enclose a list - uselist = set(['events', 'isdatransactiontypes', 'nextredentitycodes', 'prevredentitycodes', - 'isdatransactiontypes', 'tiers', 'auctions']) - sql_str = ("""INSERT INTO RefEntity VALUES({}) - ON CONFLICT(redentitycode) DO UPDATE SET {}""". - format(",".join(["%s"] * len(names)), - ",".join(f"{name}=EXCLUDED.{name}" for name in names[1:]))) + uselist = set( + [ + "events", + "isdatransactiontypes", + "nextredentitycodes", + "prevredentitycodes", + "isdatransactiontypes", + "tiers", + "auctions", + ] + ) + sql_str = """INSERT INTO RefEntity VALUES({}) + ON CONFLICT(redentitycode) DO UPDATE SET {}""".format( + ",".join(["%s"] * len(names)), + ",".join(f"{name}=EXCLUDED.{name}" for name in names[1:]), + ) skipfirst = True with conn.cursor() as c: for child in tree.getroot(): @@ -63,36 +94,68 @@ def insert_refentity(fname): c.execute(sql_str, [d.get(name) for name in names]) conn.commit() + def parse_prospectus(xml): return Json({c.tag: [e.text for e in c] for c in xml}) + def insert_refobligation(fname): - tree = etree.parse(fname, - parser=parser) - conn = dbconn('serenitasdb') - names_redpair = ['redpaircode', 'role', 'referenceentity', 'redentitycode', - 'tier', 'pairiscurrent', 'pairvalidfrom', 'pairvalidto', - 'ticker', 'ispreferred', 'preferreddate', 'indexconstituents', - 'recorddate', 'publiccomments', 'myticker', - 'subordinationtype', 'holdco', 'preferredremovaldate'] - names_refobligation = ['obligationname', 'prospectusinfo', 'refentities', - 'type', 'isconvert', 'isperp', 'coupontype', 'ccy', - 'maturity', 'issuedate', 'coupon', 'isin', 'cusip', 'event'] + tree = etree.parse(fname, parser=parser) + conn = dbconn("serenitasdb") + names_redpair = [ + "redpaircode", + "role", + "referenceentity", + "redentitycode", + "tier", + "pairiscurrent", + "pairvalidfrom", + "pairvalidto", + "ticker", + "ispreferred", + "preferreddate", + "indexconstituents", + "recorddate", + "publiccomments", + "myticker", + "subordinationtype", + "holdco", + "preferredremovaldate", + ] + names_refobligation = [ + "obligationname", + "prospectusinfo", + "refentities", + "type", + "isconvert", + "isperp", + "coupontype", + "ccy", + "maturity", + "issuedate", + "coupon", + "isin", + "cusip", + "event", + ] - redpair_insert = ("""INSERT INTO RedPairMapping VALUES({}) - ON CONFLICT(redpaircode) DO UPDATE SET {}""". - format(",".join(["%s"] * len(names_redpair)), - ",".join(f"{name}=EXCLUDED.{name}" for name in names_redpair[1:]))) - refobligation_insert = ("""INSERT INTO RefObligation({}) VALUES({}) - ON CONFLICT(obligationname) DO UPDATE SET {}""". - format(",".join(names_refobligation), - ",".join(["%s"] * len(names_refobligation)), - ",".join(f"{name}=EXCLUDED.{name}" for name in names_refobligation[1:]))) + redpair_insert = """INSERT INTO RedPairMapping VALUES({}) + ON CONFLICT(redpaircode) DO UPDATE SET {}""".format( + ",".join(["%s"] * len(names_redpair)), + ",".join(f"{name}=EXCLUDED.{name}" for name in names_redpair[1:]), + ) + refobligation_insert = """INSERT INTO RefObligation({}) VALUES({}) + ON CONFLICT(obligationname) DO UPDATE SET {}""".format( + ",".join(names_refobligation), + ",".join(["%s"] * len(names_refobligation)), + ",".join(f"{name}=EXCLUDED.{name}" for name in names_refobligation[1:]), + ) skipfirst = True + def simple_parse(e): - if e.tag == 'indexconstituents': + if e.tag == "indexconstituents": return [ic.text for ic in e] - elif e.tag == 'holdco': + elif e.tag == "holdco": return e.text == "TRUE" else: return e.text @@ -104,26 +167,35 @@ def insert_refobligation(fname): continue d = {col.tag: col if len(col) else col.text for col in child} with conn.cursor() as c2: - for el in d['refentities']: + for el in d["refentities"]: redpair = {e.tag: simple_parse(e) for e in el} - c2.execute(redpair_insert, [redpair.get(name) for name in names_redpair]) + c2.execute( + redpair_insert, [redpair.get(name) for name in names_redpair] + ) conn.commit() - d['refentities'] = [el.text for el in d['refentities'].iterfind('.//redpaircode')] - if 'prospectusinfo' in d: - d['prospectusinfo'] = parse_prospectus(d['prospectusinfo']) - c.execute(refobligation_insert, [d.get(name) for name in names_refobligation]) + d["refentities"] = [ + el.text for el in d["refentities"].iterfind(".//redpaircode") + ] + if "prospectusinfo" in d: + d["prospectusinfo"] = parse_prospectus(d["prospectusinfo"]) + c.execute( + refobligation_insert, [d.get(name) for name in names_refobligation] + ) conn.commit() + def get_date(f): m = re.search("(\d*)\.", f.name) if m: timestamp = int(m.groups(0)[0]) - return datetime.datetime.fromtimestamp(timestamp/1000) + return datetime.datetime.fromtimestamp(timestamp / 1000) + if __name__ == "__main__": from markit_red import download_report - base_dir = Path('/home/serenitas/CorpCDOs/Tranche_data/RED_reports/Deltas') - for report in ['REDEntityDelta', 'REDObligationDelta']: + + base_dir = Path("/home/serenitas/CorpCDOs/Tranche_data/RED_reports/Deltas") + for report in ["REDEntityDelta", "REDObligationDelta"]: g = download_report(report) f = base_dir / next(g)[0] if "Entity" in report: diff --git a/python/markit_red.py b/python/markit_red.py index 35a10f05..d2bd98bb 100644 --- a/python/markit_red.py +++ b/python/markit_red.py @@ -9,11 +9,12 @@ from lxml import etree from utils.db import serenitas_engine import pandas as pd + def request_payload(payload): - r = requests.post('https://products.markit.com/red/export.jsp', params=payload) + r = requests.post("https://products.markit.com/red/export.jsp", params=payload) res = [] - path = os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports") - if 'Delta' in payload['report']: + path = os.path.join(os.environ["BASE_DIR"], "Tranche_data", "RED_reports") + if "Delta" in payload["report"]: path = os.path.join(path, "Deltas") try: @@ -26,74 +27,110 @@ def request_payload(payload): print(r.content) return res + def download_report(report): - version_mapping = {"REDEntity": 11, - "REDEntityDelta": 11, - "REDEntityMapped": 11, - "REDObligation": 10, - "REDObligationDelta": 10, - "REDObligationMapped": 10, - "REDSROObligation": 10, - "REDobligationpreferred": 10, - "CredIndexAnnex": 10, - "CredIndexAnnexSplit": 10, - "REDIndexCodes": 9, - "redindexclassification": 9, - "redindexclassificationdelta": 9} + version_mapping = { + "REDEntity": 11, + "REDEntityDelta": 11, + "REDEntityMapped": 11, + "REDObligation": 10, + "REDObligationDelta": 10, + "REDObligationMapped": 10, + "REDSROObligation": 10, + "REDobligationpreferred": 10, + "CredIndexAnnex": 10, + "CredIndexAnnexSplit": 10, + "REDIndexCodes": 9, + "redindexclassification": 9, + "redindexclassificationdelta": 9, + } - payload = {'user': 'GuillaumeHorel', - 'password': 'password', - 'version': version_mapping[report], - 'report': report} + payload = { + "user": "GuillaumeHorel", + "password": "password", + "version": version_mapping[report], + "report": report, + } - if report in ['CredIndexAnnex', 'CredIndexAnnexSplit']: - for family in ['CDX', 'ITRAXX-EUROPE']: - payload.update({'family': family}) + if report in ["CredIndexAnnex", "CredIndexAnnexSplit"]: + for family in ["CDX", "ITRAXX-EUROPE"]: + payload.update({"family": family}) yield request_payload(payload) else: yield request_payload(payload) + def update_redcodes(fname): - with open(os.path.join(os.environ['BASE_DIR'], "Tranche_data", "RED_reports", fname)) as fh: + with open( + os.path.join(os.environ["BASE_DIR"], "Tranche_data", "RED_reports", fname) + ) as fh: et = etree.parse(fh) data_version = [] data_maturity = [] - for index in et.iter('index'): - temp = {c.tag: c.text for c in index if c.tag not in ['terms', 'paymentfrequency']} + for index in et.iter("index"): + temp = { + c.tag: c.text for c in index if c.tag not in ["terms", "paymentfrequency"] + } data_version.append(temp) - for term in index.iter('term'): - d = {'redindexcode': temp['redindexcode']} + for term in index.iter("term"): + d = {"redindexcode": temp["redindexcode"]} d.update({c.tag: c.text for c in term}) data_maturity.append(d) df_maturity = pd.DataFrame(data_maturity) df_version = pd.DataFrame(data_version) - df_version['activeversion'] = df_version['activeversion'].map({'Y': True, None: False}) - df_maturity.tenor = df_maturity['tenor'].map(lambda s: s.lower() + 'r') + df_version["activeversion"] = df_version["activeversion"].map( + {"Y": True, None: False} + ) + df_maturity.tenor = df_maturity["tenor"].map(lambda s: s.lower() + "r") df_maturity.coupon = (pd.to_numeric(df_maturity.coupon) * 10000).astype(int) - df_version.to_sql("index_version_markit", serenitas_engine, index=False, if_exists='append') - df_maturity.to_sql("index_maturity_markit", serenitas_engine, index=False, if_exists='append') + df_version.to_sql( + "index_version_markit", serenitas_engine, index=False, if_exists="append" + ) + df_maturity.to_sql( + "index_maturity_markit", serenitas_engine, index=False, if_exists="append" + ) + def update_redindices(fname): - basedir = Path(os.environ['BASE_DIR']) / "Tranche_data" + basedir = Path(os.environ["BASE_DIR"]) / "Tranche_data" with open(basedir / "RED_reports" / fname) as fh: e = etree.parse(fh) root = e.getroot() - headers = ['referenceentity', 'redentitycode', 'role', 'redpaircode', 'jurisdiction', - 'tier', 'pairiscurrent', 'pairvalidto', 'pairvalidfrom', 'ticker', - 'ispreferred', 'isdatransactiontype', 'docclause', 'recorddate', - 'publiccomments', 'weight', 'holdco', 'subordinationtype'] - for c in root.findall('index'): - names = [c.find(tag).text for tag in ['indexsubfamily', 'series', 'version']] - with open( basedir / "RED_indices" / "{0}.{1}.V{2}.csv".format(*names), "w") as fh2: + headers = [ + "referenceentity", + "redentitycode", + "role", + "redpaircode", + "jurisdiction", + "tier", + "pairiscurrent", + "pairvalidto", + "pairvalidfrom", + "ticker", + "ispreferred", + "isdatransactiontype", + "docclause", + "recorddate", + "publiccomments", + "weight", + "holdco", + "subordinationtype", + ] + for c in root.findall("index"): + names = [c.find(tag).text for tag in ["indexsubfamily", "series", "version"]] + with open( + basedir / "RED_indices" / "{0}.{1}.V{2}.csv".format(*names), "w" + ) as fh2: csvwriter = csv.DictWriter(fh2, fieldnames=headers) csvwriter.writeheader() data = [] - for constituent in c.findall('.//originalconstituent'): + for constituent in c.findall(".//originalconstituent"): data.append({l.tag: l.text for l in constituent}) - data = sorted(data, key=lambda x: x['referenceentity']) + data = sorted(data, key=lambda x: x["referenceentity"]) csvwriter.writerows(data) + if __name__ == "__main__": fname = next(download_report("REDIndexCodes")) update_redcodes(fname[0]) diff --git a/python/parse_citi_pdf.py b/python/parse_citi_pdf.py index 71bdc4c8..39a33f5a 100644 --- a/python/parse_citi_pdf.py +++ b/python/parse_citi_pdf.py @@ -3,19 +3,28 @@ import subprocess from bs4 import BeautifulSoup from env import DAILY_DIR + def load_pdf(file_path): - proc = subprocess.run(["pdftohtml", "-xml", "-stdout", "-i", - file_path.as_posix()], - capture_output=True) + proc = subprocess.run( + ["pdftohtml", "-xml", "-stdout", "-i", file_path.as_posix()], + capture_output=True, + ) soup = BeautifulSoup(proc.stdout, features="lxml") l = soup.findAll("text") l = sorted(l, key=lambda x: (int(x["top"]), int(x["left"]))) return l + def get_col(l, top, bottom, left, right): - return [c.text for c in l if int(c["left"]) >= left and \ - int(c["left"]) < right and \ - int(c["top"]) >= top and int(c["top"]) < bottom ] + return [ + c.text + for c in l + if int(c["left"]) >= left + and int(c["left"]) < right + and int(c["top"]) >= top + and int(c["top"]) < bottom + ] + def parse_num(s): s = s.replace(",", "") @@ -24,18 +33,24 @@ def parse_num(s): else: return float(s) + def get_df(l, col1, col2, col3): - df = pd.DataFrame({"amount": get_col(l, *col2), - "currency": get_col(l, *col3)}, - index=get_col(l, *col1)) + df = pd.DataFrame( + {"amount": get_col(l, *col2), "currency": get_col(l, *col3)}, + index=get_col(l, *col1), + ) df.amount = df.amount.apply(parse_num) df.index = df.index.str.lstrip() return df + def get_citi_collateral(d): try: - fname = next((DAILY_DIR / "CITI_reports"). - glob(f"262966_MarginNotice_{d.strftime('%Y%m%d')}_*.pdf")) + fname = next( + (DAILY_DIR / "CITI_reports").glob( + f"262966_MarginNotice_{d.strftime('%Y%m%d')}_*.pdf" + ) + ) except StopIteration: raise FileNotFoundError(f"CITI file not found for date {d.date()}") l = load_pdf(fname) @@ -51,5 +66,7 @@ def get_citi_collateral(d): col2 = (top, bottom, 100, 500) col3 = (top, bottom, 500, 600) initial_margin = get_df(l, col1, col2, col3) - return variation_margin.loc["VM Total Collateral", "amount"] + \ - initial_margin.loc["Non Reg IM Total Collateral", "amount"] + return ( + variation_margin.loc["VM Total Collateral", "amount"] + + initial_margin.loc["Non Reg IM Total Collateral", "amount"] + ) diff --git a/python/parse_gs.py b/python/parse_gs.py index 14137a06..ffbd038f 100644 --- a/python/parse_gs.py +++ b/python/parse_gs.py @@ -13,15 +13,20 @@ for index in ["IG", "HY"]: flag = False masterdf = {} for line in fh: - line = line.decode('utf-8', 'ignore') + line = line.decode("utf-8", "ignore") line = line.rstrip() - m = re.search("(IG|HY)(\d{2}) 5y (?:.*)SWAPTION (?:UPDATE|CLOSES|CLOSE) - Ref\D+(.+)$", line) + m = re.search( + "(IG|HY)(\d{2}) 5y (?:.*)SWAPTION (?:UPDATE|CLOSES|CLOSE) - Ref\D+(.+)$", + line, + ) if m: indextype = m.groups()[0] series = int(m.groups()[1]) - if indextype == 'HY': - refprice, refspread = map(float, - re.match("([\S]+)\s+\(([^)]+)\)", m.groups()[2]).groups()) + if indextype == "HY": + refprice, refspread = map( + float, + re.match("([\S]+)\s+\(([^)]+)\)", m.groups()[2]).groups(), + ) else: refspread = float(m.groups()[2]) continue @@ -29,10 +34,12 @@ for index in ["IG", "HY"]: quotedate = pd.to_datetime(line[4:]) continue if line.startswith("Expiry"): - m = re.match("Expiry (\d{2}\w{3}\d{2}) \((?:([\S]+) )?([\S]+)\)", line) + m = re.match( + "Expiry (\d{2}\w{3}\d{2}) \((?:([\S]+) )?([\S]+)\)", line + ) if m: date, fwprice, fwspread = m.groups() - date = pd.to_datetime(date, format='%d%b%y') + date = pd.to_datetime(date, format="%d%b%y") continue if line.startswith("Stk"): flag = True @@ -41,7 +48,7 @@ for index in ["IG", "HY"]: if flag: if line: vals = re.sub(" +", " ", line).split(" ") - if indextype=='HY': + if indextype == "HY": vals.pop(2) vals.pop(9) else: @@ -50,62 +57,85 @@ for index in ["IG", "HY"]: r.append(vals) continue else: - if indextype=='HY': - cols = ['Strike', 'Sprd', 'Pay', 'DeltaPay', 'Rec', 'Vol', - 'VolChg', 'VolBpd', 'Tail'] + if indextype == "HY": + cols = [ + "Strike", + "Sprd", + "Pay", + "DeltaPay", + "Rec", + "Vol", + "VolChg", + "VolBpd", + "Tail", + ] else: - cols = ['Strike', 'Pay', 'DeltaPay', 'Rec', 'Vol', - 'VolChg', 'VolBpd', 'Tail'] - df = pd.DataFrame.from_records(r, columns = cols) + cols = [ + "Strike", + "Pay", + "DeltaPay", + "Rec", + "Vol", + "VolChg", + "VolBpd", + "Tail", + ] + df = pd.DataFrame.from_records(r, columns=cols) - df[['PayBid', 'PayOffer']] = df.Pay.str.split('/', expand=True) - df[['RecBid', 'RecOffer']] = df.Rec.str.split('/', expand=True) - df.drop(['Pay', 'Rec'], axis=1, inplace=True) + df[["PayBid", "PayOffer"]] = df.Pay.str.split("/", expand=True) + df[["RecBid", "RecOffer"]] = df.Rec.str.split("/", expand=True) + df.drop(["Pay", "Rec"], axis=1, inplace=True) for col in df: - df[col] = pd.to_numeric(df[col], errors = 'coerce') - df.set_index('Strike', inplace=True) - d = {'quotedate': quotedate, - 'expiry': date, - 'index': indextype, - 'series': series, - 'ref': refspread if indextype =="IG" else refprice} + df[col] = pd.to_numeric(df[col], errors="coerce") + df.set_index("Strike", inplace=True) + d = { + "quotedate": quotedate, + "expiry": date, + "index": indextype, + "series": series, + "ref": refspread if indextype == "IG" else refprice, + } if indextype == "IG": - d['fwdspread'] = float(fwspread) + d["fwdspread"] = float(fwspread) else: - d['fwdprice'] = float(fwprice) + d["fwdprice"] = float(fwprice) fwd_index.append(d) masterdf[date] = df flag = False r = [] continue - all_df[(quotedate, indextype, series)] = pd.concat(masterdf, names=['expiry']) -all_df = pd.concat(all_df, names = ['quotedate', 'index', 'series']) -all_df['DeltaPay'] = - all_df['DeltaPay']/100 -all_df['Vol'] /= 100 + all_df[(quotedate, indextype, series)] = pd.concat(masterdf, names=["expiry"]) +all_df = pd.concat(all_df, names=["quotedate", "index", "series"]) +all_df["DeltaPay"] = -all_df["DeltaPay"] / 100 +all_df["Vol"] /= 100 index_df = pd.DataFrame(fwd_index) all_df.reset_index(inplace=True) -all_df = all_df.rename(columns={'Strike':'strike', - 'Vol': 'vol', - 'PayOffer': 'pay_offer', - 'PayBid': 'pay_bid', - 'RecOffer': 'rec_offer', - 'RecBid': 'rec_bid', - 'Tail': 'tail', - 'DeltaPay': 'delta_pay'}) -del all_df['VolBpd'], all_df['VolChg'] -if 'Sprd' in all_df: - del all_df['Sprd'] -all_df['quote_source'] = 'GS' -index_df = index_df.drop_duplicates(['quotedate', 'index', 'series', 'expiry']) +all_df = all_df.rename( + columns={ + "Strike": "strike", + "Vol": "vol", + "PayOffer": "pay_offer", + "PayBid": "pay_bid", + "RecOffer": "rec_offer", + "RecBid": "rec_bid", + "Tail": "tail", + "DeltaPay": "delta_pay", + } +) +del all_df["VolBpd"], all_df["VolChg"] +if "Sprd" in all_df: + del all_df["Sprd"] +all_df["quote_source"] = "GS" +index_df = index_df.drop_duplicates(["quotedate", "index", "series", "expiry"]) ##insert quotes from utils.db import dbengine -serenitasdb = dbengine('serenitasdb') + +serenitasdb = dbengine("serenitasdb") conn = serenitasdb.raw_connection() -format_str = "INSERT INTO swaption_ref_quotes({}) VALUES({}) " \ - "ON CONFLICT DO NOTHING" +format_str = "INSERT INTO swaption_ref_quotes({}) VALUES({}) " "ON CONFLICT DO NOTHING" cols = index_df.columns sqlstr = format_str.format(",".join(cols), ",".join(["%s"] * len(cols))) with conn.cursor() as c: diff --git a/python/position.py b/python/position.py index 589b22c6..91286770 100644 --- a/python/position.py +++ b/python/position.py @@ -10,160 +10,208 @@ import os import logging import sys -def get_list(engine, workdate: datetime.datetime=None, asset_class=None, - include_unsettled: bool=True, - fund="SERCGMAST"): + +def get_list( + engine, + workdate: datetime.datetime = None, + asset_class=None, + include_unsettled: bool = True, + fund="SERCGMAST", +): if workdate: - positions = pd.read_sql_query("SELECT identifier, bbg_type FROM " - "list_positions(%s, %s, %s, %s)", - engine, - params=(workdate.date(), - asset_class, - include_unsettled, - fund)) - positions.loc[positions.identifier.str.len() <= 11, 'cusip'] = \ - positions.identifier.str.slice(stop=9) - positions.loc[positions.identifier.str.len() == 12, 'isin'] = \ - positions.identifier + positions = pd.read_sql_query( + "SELECT identifier, bbg_type FROM " "list_positions(%s, %s, %s, %s)", + engine, + params=(workdate.date(), asset_class, include_unsettled, fund), + ) + positions.loc[ + positions.identifier.str.len() <= 11, "cusip" + ] = positions.identifier.str.slice(stop=9) + positions.loc[ + positions.identifier.str.len() == 12, "isin" + ] = positions.identifier else: positions = pd.read_sql_table("securities", engine) - positions['bbg_id'] = positions.cusip.where(positions.cusip.notnull(), positions['isin']) + \ - ' ' + positions.bbg_type - positions.set_index('bbg_id', inplace=True) + positions["bbg_id"] = ( + positions.cusip.where(positions.cusip.notnull(), positions["isin"]) + + " " + + positions.bbg_type + ) + positions.set_index("bbg_id", inplace=True) return positions + def get_list_range(engine, begin, end, asset_class=None): begin = pd.Timestamp(begin).date() end = pd.Timestamp(end).date() - positions = pd.read_sql_query("select identifier, bbg_type, strategy from list_positions_range(%s, %s, %s)", - engine, - params=(begin, end, asset_class)) - positions.loc[positions.identifier.str.len() <= 11, 'cusip'] = positions.identifier.str.slice(stop=9) - positions.loc[positions.identifier.str.len() == 12, 'isin'] = positions.identifier - positions['bbg_id'] = positions.cusip.where(positions.cusip.notnull(), positions['isin']) + \ - ' ' + positions.bbg_type - positions.set_index('bbg_id', inplace=True) + positions = pd.read_sql_query( + "select identifier, bbg_type, strategy from list_positions_range(%s, %s, %s)", + engine, + params=(begin, end, asset_class), + ) + positions.loc[ + positions.identifier.str.len() <= 11, "cusip" + ] = positions.identifier.str.slice(stop=9) + positions.loc[positions.identifier.str.len() == 12, "isin"] = positions.identifier + positions["bbg_id"] = ( + positions.cusip.where(positions.cusip.notnull(), positions["isin"]) + + " " + + positions.bbg_type + ) + positions.set_index("bbg_id", inplace=True) return positions -def backpopulate_marks(begin_str='2015-01-15', end_str='2015-07-15'): + +def backpopulate_marks(begin_str="2015-01-15", end_str="2015-07-15"): pattern = re.compile("\d{4}-\d{2}-\d{2}") - list_of_daily_folder = (fullpath for (fullpath, _, _) in os.walk('/home/serenitas/Daily') - if pattern.match(os.path.basename(fullpath))) + list_of_daily_folder = ( + fullpath + for (fullpath, _, _) in os.walk("/home/serenitas/Daily") + if pattern.match(os.path.basename(fullpath)) + ) list_of_bdays = bdate_range(start=begin_str, end=end_str) for path in list_of_daily_folder: date = pd.to_datetime(os.path.basename(path)) if date in list_of_bdays: marks_file = [f for f in os.listdir(path) if f.startswith("securitiesNpv")] if marks_file: - marks_file.sort(key=lambda x:x[13:], reverse=True) #sort by lexicographic order which is what we want since we use ISO dates + marks_file.sort( + key=lambda x: x[13:], reverse=True + ) # sort by lexicographic order which is what we want since we use ISO dates marks = pd.read_csv(os.path.join(path, marks_file[0])) positions = get_list(pd.to_datetime(date)) - positions = positions.merge(marks, left_on='identifier', right_on='IDENTIFIER') - positions.drop(['IDENTIFIER', 'last_settle_date'], axis=1, inplace=True) - positions['date'] = date - positions.rename(columns={'Price': 'price'}, inplace=True) + positions = positions.merge( + marks, left_on="identifier", right_on="IDENTIFIER" + ) + positions.drop(["IDENTIFIER", "last_settle_date"], axis=1, inplace=True) + positions["date"] = date + positions.rename(columns={"Price": "price"}, inplace=True) positions = positions.drop_duplicates() - positions.to_sql('position', engine, if_exists='append', index=False) + positions.to_sql("position", engine, if_exists="append", index=False) + def update_securities(engine, session, workdate): - field = {'Corp': 'PREV_CPN_DT', 'Mtge': 'START_ACC_DT'} + field = {"Corp": "PREV_CPN_DT", "Mtge": "START_ACC_DT"} securities = get_list(engine) securities = securities[securities.paid_down.isnull()] - data = retrieve_data(session, securities.index.tolist(), - ['PREV_CPN_DT', 'START_ACC_DT', 'CUR_CPN', 'CPN_ASOF_DT']) - data = pd.DataFrame.from_dict(data, orient='index') - data = data[data.CPN_ASOF_DT.isnull() |(data.CPN_ASOF_DT <= workdate)] + data = retrieve_data( + session, + securities.index.tolist(), + ["PREV_CPN_DT", "START_ACC_DT", "CUR_CPN", "CPN_ASOF_DT"], + ) + data = pd.DataFrame.from_dict(data, orient="index") + data = data[data.CPN_ASOF_DT.isnull() | (data.CPN_ASOF_DT <= workdate)] m = securities.merge(data, left_index=True, right_index=True) conn = engine.raw_connection() with conn.cursor() as c: - for r in m.to_dict('records'): - accrued_field = field[r['bbg_type']] + for r in m.to_dict("records"): + accrued_field = field[r["bbg_type"]] if r[accrued_field] < workdate: - c.execute(f"UPDATE securities SET start_accrued_date=%({accrued_field})s " - ",coupon=%(CUR_CPN)s WHERE identifier=%(identifier)s", - r) + c.execute( + f"UPDATE securities SET start_accrued_date=%({accrued_field})s " + ",coupon=%(CUR_CPN)s WHERE identifier=%(identifier)s", + r, + ) conn.commit() conn.close() + def init_fx(session, engine, startdate): - currencies = ['EURUSD', 'CADUSD'] - securities = [c + ' Curncy' for c in currencies] - data = retrieve_data(session, securities, ['PX_LAST'], start_date=startdate) - data = data['EURUSD Curncy'].merge(data['CADUSD Curncy'], left_on='date', right_on='date') - data.rename(columns={'PX_LAST_x': 'eurusd', - 'PX_LAST_y': 'cadusd'}, inplace=True) - data.to_sql('fx', engine, if_exists='append') + currencies = ["EURUSD", "CADUSD"] + securities = [c + " Curncy" for c in currencies] + data = retrieve_data(session, securities, ["PX_LAST"], start_date=startdate) + data = data["EURUSD Curncy"].merge( + data["CADUSD Curncy"], left_on="date", right_on="date" + ) + data.rename(columns={"PX_LAST_x": "eurusd", "PX_LAST_y": "cadusd"}, inplace=True) + data.to_sql("fx", engine, if_exists="append") + def update_fx(conn, session, currencies): - securities = [c + ' Curncy' for c in currencies] - data = retrieve_data(session, securities, ['FIXED_CLOSING_PRICE_NY', 'PX_CLOSE_DT']) - colnames = ['date'] + securities = [c + " Curncy" for c in currencies] + data = retrieve_data(session, securities, ["FIXED_CLOSING_PRICE_NY", "PX_CLOSE_DT"]) + colnames = ["date"] values = [] for k, v in data.items(): - currency_pair = k.split(' ')[0].lower() + currency_pair = k.split(" ")[0].lower() colnames.append(currency_pair) - values.append(v['FIXED_CLOSING_PRICE_NY']) - values = [v['PX_CLOSE_DT']] + values - sqlstr = 'INSERT INTO fx({0}) VALUES({1}) ON CONFLICT DO NOTHING'.format( - ",".join(colnames), - ",".join(["%s"]*len(values))) + values.append(v["FIXED_CLOSING_PRICE_NY"]) + values = [v["PX_CLOSE_DT"]] + values + sqlstr = "INSERT INTO fx({0}) VALUES({1}) ON CONFLICT DO NOTHING".format( + ",".join(colnames), ",".join(["%s"] * len(values)) + ) with conn.cursor() as c: c.execute(sqlstr, values) conn.commit() -def init_swap_rates(conn, session, tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20, 30], - start_date=datetime.date(1998, 10, 7)): + +def init_swap_rates( + conn, + session, + tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20, 30], + start_date=datetime.date(1998, 10, 7), +): securities = [f"USISDA{t:02} Index" for t in tenors] - data = retrieve_data(session, securities, ['PX_LAST'], - start_date=datetime.date(1998, 10, 7)) + data = retrieve_data( + session, securities, ["PX_LAST"], start_date=datetime.date(1998, 10, 7) + ) for t in tenors: ticker = f"USISDA{t:02} Index" - sql_str = f'INSERT INTO USD_swap_fixings(fixing_date, "{t}y") ' + \ - 'VALUES(%s, %s) ON CONFLICT (fixing_date)' + \ - f' DO UPDATE SET "{t}y" = excluded."{t}y"' + sql_str = ( + f'INSERT INTO USD_swap_fixings(fixing_date, "{t}y") ' + + "VALUES(%s, %s) ON CONFLICT (fixing_date)" + + f' DO UPDATE SET "{t}y" = excluded."{t}y"' + ) with conn.cursor() as c: - c.executemany(sql_str, - [(d, r) for d, r in data[ticker]['PX_LAST'].items()]) + c.executemany(sql_str, [(d, r) for d, r in data[ticker]["PX_LAST"].items()]) conn.commit() -def init_swaption_vol(session, - tenors=['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30], - source='BBIR', - vol_type='N', - start_date=datetime.date(1990, 1, 1)): + +def init_swaption_vol( + session, + tenors=["A", "C", "F", "I"] + list(range(1, 11)) + [15, 20, 25, 30], + source="BBIR", + vol_type="N", + start_date=datetime.date(1990, 1, 1), +): tickers = [] for t1 in tenors: for t2 in tenors[4:]: tickers.append(f"USS{vol_type}{t1:0>2}{t2} {source} Curncy") - data = retrieve_data(session, tickers, ['PX_LAST'], - start_date=start_date) + data = retrieve_data(session, tickers, ["PX_LAST"], start_date=start_date) return data -def split_tenor_expiry(ticker, vol_type='N'): + +def split_tenor_expiry(ticker, vol_type="N"): m = re.match(f"USS{vol_type}(.{{2}})([^\s]*) ([^\s]*) Curncy", ticker) expiry, tenor, _ = m.groups() - if expiry[0] == '0': + if expiry[0] == "0": expiry = expiry[1:] if not expiry.isalpha(): expiry = int(expiry) tenor = int(tenor) return expiry, tenor + def insert_swaption_vol(data, conn, source, vol_type="N"): - tenors = ['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30] + tenors = ["A", "C", "F", "I"] + list(range(1, 11)) + [15, 20, 25, 30] df = pd.concat(data, axis=1) df.columns = df.columns.get_level_values(0) - df.columns = pd.MultiIndex.from_tuples([split_tenor_expiry(c, vol_type) for c in df.columns]) + df.columns = pd.MultiIndex.from_tuples( + [split_tenor_expiry(c, vol_type) for c in df.columns] + ) table_name = "swaption_normal_vol" if vol_type == "N" else "swaption_lognormal_vol" for t in tenors[-14:]: - sql_str = f'INSERT INTO {table_name}(date, "{t}y", source) ' + \ - 'VALUES(%s, %s, %s) ON CONFLICT (date, source)' + \ - f' DO UPDATE SET "{t}y" = excluded."{t}y", source = excluded.source' + sql_str = ( + f'INSERT INTO {table_name}(date, "{t}y", source) ' + + "VALUES(%s, %s, %s) ON CONFLICT (date, source)" + + f' DO UPDATE SET "{t}y" = excluded."{t}y", source = excluded.source' + ) with conn.cursor() as c: df_temp = df.xs(t, axis=1, level=1).reindex(tenors, axis=1) for k, v in df_temp.iterrows(): @@ -172,68 +220,83 @@ def insert_swaption_vol(data, conn, source, vol_type="N"): c.execute(sql_str, (k, v.tolist(), source)) conn.commit() -def update_swaption_vol(conn, session, - tenors=['A', 'C', 'F', 'I'] + list(range(1, 11)) + [15, 20, 25, 30], - *, - sources=['BBIR', 'CMPN', 'ICPL'], - vol_type="N"): + +def update_swaption_vol( + conn, + session, + tenors=["A", "C", "F", "I"] + list(range(1, 11)) + [15, 20, 25, 30], + *, + sources=["BBIR", "CMPN", "ICPL"], + vol_type="N", +): """ Parameters ---------- vol_type : one of 'N' or 'V' (normal or log-normal) """ table_name = "swaption_normal_vol" if vol_type == "N" else "swaption_lognormal_vol" - for source in ['BBIR', 'CMPN', 'ICPL']: + for source in ["BBIR", "CMPN", "ICPL"]: tickers = [] for expiry in tenors: for tenor in tenors: tickers.append(f"USS{vol_type}{expiry:0>2}{tenor} {source} Curncy") - data = retrieve_data(session, tickers, ['PX_YEST_CLOSE', 'PX_CLOSE_DT']) + data = retrieve_data(session, tickers, ["PX_YEST_CLOSE", "PX_CLOSE_DT"]) for t in tenors[4:]: - sql_str = f'INSERT INTO {table_name}(date, "{t}y", source) ' + \ - 'VALUES(%s, %s, %s) ON CONFLICT (date, source)' + \ - f' DO UPDATE SET "{t}y" = excluded."{t}y", source = excluded.source' + sql_str = ( + f'INSERT INTO {table_name}(date, "{t}y", source) ' + + "VALUES(%s, %s, %s) ON CONFLICT (date, source)" + + f' DO UPDATE SET "{t}y" = excluded."{t}y", source = excluded.source' + ) r = [] dates = [] for expiry in tenors: ticker = f"USS{vol_type}{expiry:0>2}{t} {source} Curncy" if data[ticker]: - r.append(data[ticker]['PX_YEST_CLOSE']) - dates.append(data[ticker]['PX_CLOSE_DT']) + r.append(data[ticker]["PX_YEST_CLOSE"]) + dates.append(data[ticker]["PX_CLOSE_DT"]) else: r.append(None) dates.append(dates[-1]) if dates.count(dates[0]) < len(dates): - raise ValueError('Not all quotes are from the same date') + raise ValueError("Not all quotes are from the same date") with conn.cursor() as c: c.execute(sql_str, (dates[0], r, source)) conn.commit() -def update_swap_rates(conn, session, - tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20, 30]): + +def update_swap_rates( + conn, session, tenors=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20, 30] +): securities = [f"USISDA{t:02} Index" for t in tenors] - data = retrieve_data(session, securities, ['PX_LAST', 'LAST_UPDATE_DT']) + data = retrieve_data(session, securities, ["PX_LAST", "LAST_UPDATE_DT"]) for t in tenors: ticker = f"USISDA{t:02} Index" - sql_str = f'INSERT INTO USD_swap_fixings(fixing_date, "{t}y") ' + \ - 'VALUES(%(LAST_UPDATE_DT)s, %(PX_LAST)s) ON CONFLICT (fixing_date)' + \ - f' DO UPDATE SET "{t}y" = %(PX_LAST)s' + sql_str = ( + f'INSERT INTO USD_swap_fixings(fixing_date, "{t}y") ' + + "VALUES(%(LAST_UPDATE_DT)s, %(PX_LAST)s) ON CONFLICT (fixing_date)" + + f' DO UPDATE SET "{t}y" = %(PX_LAST)s' + ) with conn.cursor() as c: c.execute(sql_str, data[ticker]) conn.commit() + def update_cash_rates(conn, session, start_date=None): - securities = {"FEDL01 Index": "FED_FUND", - "US0001M Index": "1M_LIBOR", - "US0003M Index": "3M_LIBOR"} + securities = { + "FEDL01 Index": "FED_FUND", + "US0001M Index": "1M_LIBOR", + "US0003M Index": "3M_LIBOR", + } if start_date is None: - data = retrieve_data(session, list(securities.keys()), - ["PX_LAST", "LAST_UPDATE_DT"]) + data = retrieve_data( + session, list(securities.keys()), ["PX_LAST", "LAST_UPDATE_DT"] + ) else: - data = retrieve_data(session, list(securities.keys()), - ["PX_LAST"], start_date=start_date.date()) + data = retrieve_data( + session, list(securities.keys()), ["PX_LAST"], start_date=start_date.date() + ) sql_str = "INSERT INTO rates VALUES(%s, %s, %s) ON CONFLICT DO NOTHING" with conn.cursor() as c: if start_date is None: @@ -245,53 +308,69 @@ def update_cash_rates(conn, session, start_date=None): c.execute(sql_str, (d, securities[k], r)) conn.commit() + def populate_cashflow_history(engine, session, workdate=None, fund="SERCGMAST"): securities = get_list(engine, workdate, fund=fund) - data = retrieve_data(session, securities.index.tolist(), - ['HIST_CASH_FLOW', 'MTG_HIST_CPN', - 'FLT_CPN_HIST', 'HIST_INTEREST_DISTRIBUTED']) - fixed_coupons = {'XS0306416982 Mtge': 7.62, - '91927RAD1 Mtge': 6.77} + data = retrieve_data( + session, + securities.index.tolist(), + ["HIST_CASH_FLOW", "MTG_HIST_CPN", "FLT_CPN_HIST", "HIST_INTEREST_DISTRIBUTED"], + ) + fixed_coupons = {"XS0306416982 Mtge": 7.62, "91927RAD1 Mtge": 6.77} conn = engine.raw_connection() for k, v in data.items(): - if 'HIST_CASH_FLOW' in v: - to_insert = v['HIST_CASH_FLOW'].merge(v['MTG_HIST_CPN'], how='left', - left_on='Payment Date', - right_on='Payment Date') - to_insert.rename(columns={'Coupon_y': 'coupon', - 'Interest': 'interest', - 'Payment Date': 'date', - 'Principal Balance': 'principal_bal', - 'Principal Paid': 'principal'}, inplace=True) - to_insert.drop(['Period Number', 'Coupon_x'], axis=1, inplace=True) - elif 'FLT_CPN_HIST' in v: - to_insert = v['FLT_CPN_HIST'] - to_insert.rename(columns={'Coupon Rate': 'coupon', - 'Accrual Start Date': 'date'}, inplace=True) + if "HIST_CASH_FLOW" in v: + to_insert = v["HIST_CASH_FLOW"].merge( + v["MTG_HIST_CPN"], + how="left", + left_on="Payment Date", + right_on="Payment Date", + ) + to_insert.rename( + columns={ + "Coupon_y": "coupon", + "Interest": "interest", + "Payment Date": "date", + "Principal Balance": "principal_bal", + "Principal Paid": "principal", + }, + inplace=True, + ) + to_insert.drop(["Period Number", "Coupon_x"], axis=1, inplace=True) + elif "FLT_CPN_HIST" in v: + to_insert = v["FLT_CPN_HIST"] + to_insert.rename( + columns={"Coupon Rate": "coupon", "Accrual Start Date": "date"}, + inplace=True, + ) to_insert.coupon = to_insert.coupon.shift(1) - elif 'HIST_INTEREST_DISTRIBUTED' in v: - to_insert = v['HIST_INTEREST_DISTRIBUTED'] - to_insert.rename(columns={'Interest': 'interest', - 'Historical Date': 'date'}, inplace=True) + elif "HIST_INTEREST_DISTRIBUTED" in v: + to_insert = v["HIST_INTEREST_DISTRIBUTED"] + to_insert.rename( + columns={"Interest": "interest", "Historical Date": "date"}, + inplace=True, + ) if k in fixed_coupons: - to_insert['coupon'] = fixed_coupons[k] - else: #damn you XS0299146992 ! + to_insert["coupon"] = fixed_coupons[k] + else: # damn you XS0299146992 ! continue else: logging.error("No cashflows for the given security") - identifier = securities.loc[k, 'identifier'] - to_insert['identifier'] = identifier + identifier = securities.loc[k, "identifier"] + to_insert["identifier"] = identifier with conn.cursor() as c: c.execute("DELETE FROM cashflow_history WHERE identifier=%s", (identifier,)) conn.commit() - to_insert.to_sql('cashflow_history', engine, if_exists='append', index=False) + to_insert.to_sql("cashflow_history", engine, if_exists="append", index=False) with conn.cursor() as c: c.execute("REFRESH MATERIALIZED VIEW factors_history") conn.commit() conn.close() + if __name__ == "__main__": from utils.db import serenitas_pool, dawn_engine + dawn_conn = dawn_engine.raw_connection() serenitas_conn = serenitas_pool.getconn() if len(sys.argv) > 1: @@ -302,7 +381,7 @@ if __name__ == "__main__": update_securities(dawn_engine, session, workdate) populate_cashflow_history(dawn_engine, session, workdate, "SERCGMAST") populate_cashflow_history(dawn_engine, session, workdate, "BRINKER") - update_fx(dawn_conn, session, ['EURUSD', 'CADUSD']) + update_fx(dawn_conn, session, ["EURUSD", "CADUSD"]) update_swap_rates(serenitas_conn, session) update_cash_rates(serenitas_conn, session) for vol_type in ["N", "V"]: diff --git a/python/process_queue.py b/python/process_queue.py index 8a56a751..ca50d3ce 100644 --- a/python/process_queue.py +++ b/python/process_queue.py @@ -22,83 +22,270 @@ from gmail_helpers import GmailMessage from tabulate import tabulate HEADERS_PRE = [ - 'Deal Type', 'Deal Id', 'Action', 'Client', 'Fund', 'Portfolio', - 'Folder', 'Custodian', 'Cash Account', 'Counterparty', 'Comments', - 'State', 'Trade Date'] - -HEADERS = {'bond': HEADERS_PRE + [ - 'Settlement Date', 'Reserved', 'GlopeOp Security Identifier', - 'CUSIP', 'ISIN', 'Reserved', 'Reserved', - 'Reserved', 'Security Description', 'Transaction Indicator', - 'SubTransaction Indicator', 'Accrued', 'Price', 'BlockId', 'BlockAmount', - 'Reserved', 'Resesrved', 'Reserved', 'Reserved', 'ClientReference', 'ClearingMode', - 'FaceAmount', 'Pool Factor', 'FactorAsOfDate', 'Delivery'], - 'cds': HEADERS_PRE + [ - 'Reserved', 'Reserved', 'EffectiveDate', 'MaturityDate', - 'Currency', 'Notional', 'FixedRate', 'PaymentRollDateConvention', 'DayCount', - 'PaymentFrequency', 'FirstCouponRate', 'FirstCouponDate', 'ResetLag', 'Liquidation', - 'LiquidationDate', 'Protection', 'UnderlyingSecurityId', - 'UnderlyingSecurityDescription', 'CreditSpreadCurve', - 'CreditEvents', 'RecoveryRate', 'Settlement', 'InitialMargin', - 'InitialMarginPercentage','InitialMarginCurrency', 'DiscountCurve', - 'ClientReference', 'UpfrontFee', 'UpfrontFeePayDate', 'RegenerateCashFlow', - 'UpfrontFeeComment', 'Executing Broker','SwapType', 'OnPrice', - 'OffPrice', 'AttachmentPoint', 'ExhaustionPoint', 'Fees', 'Fee Payment Dates', - 'Fee Comments', 'Credit Event Occurred', 'Calendar', - 'Clearing Facility', 'Adjusted', 'CcpTradeRef', 'BlockId', - 'BlockAmount', 'NettingId', 'AnnouncementDate', 'ExecTS', - 'DefaultProbability', 'ClientMargin', 'Factor', 'ISDADefinition'], - 'swaption': HEADERS_PRE + [ - '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'], - 'future': HEADERS_PRE + [ - "Settlement Date", "Reserved", - "GlopeOp Security Identifier", "Reserved", "Reserved", "Reserved", - "Bloomberg Ticker", "RIC", "Security Description", - "Transaction Indicator", "SubTransaction Indicator", - "Quantity", "Price", "Commission", "Tax", "VAT", - "Trade Currency", "Reserved", "Reserved", "Broker Short Name", - "MaturityDate", "Exchange", "Client Reference", "Swap Type", - "Initial Margin", "Initial Margin Currency", "Future Event", - "Commission Entries", "BlockId", "Block Amount"], - 'wire': HEADERS_PRE + [ - "Settlement Date", "Reserved", "Reserved", - "Currency", "Amount", "Associated Deal Type", "Associated Deal Id", - "Transaction Type", "Instrument Type", "Yield", "Client Reference", - "ClearingFacility", "Deal Function", "Reset Price", "Reset Date", - "Ccp Trade Ref", "Margin Type", "Block Id", "Block Amount"], - 'spot': HEADERS_PRE + ["Settlement Date", "Dealt Currency", - "Spot Rate", "Reserved", "Buy Currency", "Buy Amount", - "Sell Currency", "Sell Amount", "ClearingFees", "BlockId", - "BlockAmount", "Commission Currency", "Commission", "Reserved", - "AssociatedDealType", "AssociatedDealId", "BrokerShortName", - "ClientReference"] + "Deal Type", + "Deal Id", + "Action", + "Client", + "Fund", + "Portfolio", + "Folder", + "Custodian", + "Cash Account", + "Counterparty", + "Comments", + "State", + "Trade Date", +] +HEADERS = { + "bond": HEADERS_PRE + + [ + "Settlement Date", + "Reserved", + "GlopeOp Security Identifier", + "CUSIP", + "ISIN", + "Reserved", + "Reserved", + "Reserved", + "Security Description", + "Transaction Indicator", + "SubTransaction Indicator", + "Accrued", + "Price", + "BlockId", + "BlockAmount", + "Reserved", + "Resesrved", + "Reserved", + "Reserved", + "ClientReference", + "ClearingMode", + "FaceAmount", + "Pool Factor", + "FactorAsOfDate", + "Delivery", + ], + "cds": HEADERS_PRE + + [ + "Reserved", + "Reserved", + "EffectiveDate", + "MaturityDate", + "Currency", + "Notional", + "FixedRate", + "PaymentRollDateConvention", + "DayCount", + "PaymentFrequency", + "FirstCouponRate", + "FirstCouponDate", + "ResetLag", + "Liquidation", + "LiquidationDate", + "Protection", + "UnderlyingSecurityId", + "UnderlyingSecurityDescription", + "CreditSpreadCurve", + "CreditEvents", + "RecoveryRate", + "Settlement", + "InitialMargin", + "InitialMarginPercentage", + "InitialMarginCurrency", + "DiscountCurve", + "ClientReference", + "UpfrontFee", + "UpfrontFeePayDate", + "RegenerateCashFlow", + "UpfrontFeeComment", + "Executing Broker", + "SwapType", + "OnPrice", + "OffPrice", + "AttachmentPoint", + "ExhaustionPoint", + "Fees", + "Fee Payment Dates", + "Fee Comments", + "Credit Event Occurred", + "Calendar", + "Clearing Facility", + "Adjusted", + "CcpTradeRef", + "BlockId", + "BlockAmount", + "NettingId", + "AnnouncementDate", + "ExecTS", + "DefaultProbability", + "ClientMargin", + "Factor", + "ISDADefinition", + ], + "swaption": HEADERS_PRE + + [ + "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", + ], + "future": HEADERS_PRE + + [ + "Settlement Date", + "Reserved", + "GlopeOp Security Identifier", + "Reserved", + "Reserved", + "Reserved", + "Bloomberg Ticker", + "RIC", + "Security Description", + "Transaction Indicator", + "SubTransaction Indicator", + "Quantity", + "Price", + "Commission", + "Tax", + "VAT", + "Trade Currency", + "Reserved", + "Reserved", + "Broker Short Name", + "MaturityDate", + "Exchange", + "Client Reference", + "Swap Type", + "Initial Margin", + "Initial Margin Currency", + "Future Event", + "Commission Entries", + "BlockId", + "Block Amount", + ], + "wire": HEADERS_PRE + + [ + "Settlement Date", + "Reserved", + "Reserved", + "Currency", + "Amount", + "Associated Deal Type", + "Associated Deal Id", + "Transaction Type", + "Instrument Type", + "Yield", + "Client Reference", + "ClearingFacility", + "Deal Function", + "Reset Price", + "Reset Date", + "Ccp Trade Ref", + "Margin Type", + "Block Id", + "Block Amount", + ], + "spot": HEADERS_PRE + + [ + "Settlement Date", + "Dealt Currency", + "Spot Rate", + "Reserved", + "Buy Currency", + "Buy Amount", + "Sell Currency", + "Sell Amount", + "ClearingFees", + "BlockId", + "BlockAmount", + "Commission Currency", + "Commission", + "Reserved", + "AssociatedDealType", + "AssociatedDealId", + "BrokerShortName", + "ClientReference", + ], } + def get_effective_date(d, swaption_type): if swaption_type == "CD_INDEX_OPTION": return previous_twentieth(d + datetime.timedelta(days=1)) @@ -107,22 +294,22 @@ def get_effective_date(d, swaption_type): return pydate_from_qldate(cal.advance(Date.from_datetime(d), 2, Days)) -def get_trades(q, trade_type='bond', fund="SERCGMAST"): +def get_trades(q, trade_type="bond", fund="SERCGMAST"): queue_name = f"{trade_type}_{fund}" r = q.lrange(queue_name, 0, -1) df = [loads(e) for e in r] list_trades = [] if df: - for tradeid, v in groupby(df, lambda x: x['id']): + for tradeid, v in groupby(df, lambda x: x["id"]): trades = list(v) - trades = sorted(trades, key=lambda x: x['lastupdate']) + trades = sorted(trades, key=lambda x: x["lastupdate"]) if len(trades) == 1: list_trades.append(trades[0]) else: - if trades[-1]['action'] == 'CANCEL': + if trades[-1]["action"] == "CANCEL": continue - if trades[0]['action'] == 'NEW': - trades[-1]['action'] = 'NEW' + if trades[0]["action"] == "NEW": + trades[-1]["action"] = "NEW" list_trades.append(trades[-1]) return list_trades @@ -135,279 +322,384 @@ def rename_keys(d, mapping): def build_termination(obj): - headers = ['DealType', 'DealId', 'Action', 'Client', 'SubAction', 'PartialTermination', - 'TerminationAmount', 'TerminationDate', 'FeesPaid', 'FeesReceived', - 'DealFunction', 'Reserved', 'ClientReference'] + ['Reserved'] * 4 + \ - ['SpecialInstructions', 'AssignedCounterparty'] + ['Reserved'] * 7 + \ - ['GoTradeId'] + ['Reserved'] * 8 + ['InMoney', 'FeeCurrency'] - return ['SwaptionDeal', obj['dealid'], 'Update', 'Serenitas', 'Termination'] + headers = ( + [ + "DealType", + "DealId", + "Action", + "Client", + "SubAction", + "PartialTermination", + "TerminationAmount", + "TerminationDate", + "FeesPaid", + "FeesReceived", + "DealFunction", + "Reserved", + "ClientReference", + ] + + ["Reserved"] * 4 + + ["SpecialInstructions", "AssignedCounterparty"] + + ["Reserved"] * 7 + + ["GoTradeId"] + + ["Reserved"] * 8 + + ["InMoney", "FeeCurrency"] + ) + return ["SwaptionDeal", obj["dealid"], "Update", "Serenitas", "Termination"] def build_line(obj, trade_type="bond"): - obj['Client'] = 'Serenitas' - obj['State'] = 'Valid' - rename_cols = {'fund': 'Fund', - 'action': 'Action', - 'dealid': 'Deal Id', - 'folder': 'Folder', - 'custodian': 'Custodian', - 'cashaccount': 'Cash Account', - 'cp_code': 'Counterparty', - 'identifier': 'GlopeOp Security Identifier', - 'cusip': 'CUSIP', - 'isin': 'ISIN', - 'description': 'Security Description', - 'accrued': 'Accrued', - 'price': 'Price', - 'faceamount': 'FaceAmount', - 'trade_date': 'Trade Date', - 'settle_date': 'Settlement Date', - 'effective_date': 'EffectiveDate', - 'maturity': 'MaturityDate', - 'currency': 'Currency', - 'curr_notional': 'Notional', - 'fixed_rate': 'FixedRate', - 'payment_rolldate': 'PaymentRollDateConvention', - 'day_count': 'DayCount', - 'protection': 'Protection', - 'security_id': 'UnderlyingSecurityId', - 'security_desc': 'UnderlyingSecurityDescription', - 'upfront': 'UpfrontFee', - 'upfront_settle_date': 'UpfrontFeePayDate', - 'swap_type': 'SwapType', - 'orig_attach': 'AttachmentPoint', - 'orig_detach': 'ExhaustionPoint', - 'clearing_facility': 'Clearing Facility', - 'isda_definition': 'ISDADefinition', - 'expiration_date': 'ExpirationDate', - 'portfolio': 'Portfolio', - 'settlement_type': 'SettlementMode'} + obj["Client"] = "Serenitas" + obj["State"] = "Valid" + rename_cols = { + "fund": "Fund", + "action": "Action", + "dealid": "Deal Id", + "folder": "Folder", + "custodian": "Custodian", + "cashaccount": "Cash Account", + "cp_code": "Counterparty", + "identifier": "GlopeOp Security Identifier", + "cusip": "CUSIP", + "isin": "ISIN", + "description": "Security Description", + "accrued": "Accrued", + "price": "Price", + "faceamount": "FaceAmount", + "trade_date": "Trade Date", + "settle_date": "Settlement Date", + "effective_date": "EffectiveDate", + "maturity": "MaturityDate", + "currency": "Currency", + "curr_notional": "Notional", + "fixed_rate": "FixedRate", + "payment_rolldate": "PaymentRollDateConvention", + "day_count": "DayCount", + "protection": "Protection", + "security_id": "UnderlyingSecurityId", + "security_desc": "UnderlyingSecurityDescription", + "upfront": "UpfrontFee", + "upfront_settle_date": "UpfrontFeePayDate", + "swap_type": "SwapType", + "orig_attach": "AttachmentPoint", + "orig_detach": "ExhaustionPoint", + "clearing_facility": "Clearing Facility", + "isda_definition": "ISDADefinition", + "expiration_date": "ExpirationDate", + "portfolio": "Portfolio", + "settlement_type": "SettlementMode", + } rename_keys(obj, rename_cols) - if trade_type in ['bond', 'swaption', 'future']: - obj['Transaction Indicator'] = "Buy" if obj['buysell'] else "Sell" - if trade_type == 'bond': - obj['Deal Type'] = 'MortgageDeal' - obj['Portfolio'] = 'MORTGAGES' - obj['Delivery'] = 'S' + if trade_type in ["bond", "swaption", "future"]: + obj["Transaction Indicator"] = "Buy" if obj["buysell"] else "Sell" + if trade_type == "bond": + obj["Deal Type"] = "MortgageDeal" + obj["Portfolio"] = "MORTGAGES" + obj["Delivery"] = "S" # zero coupon bond - if obj['CUSIP'] != obj['GlopeOp Security Identifier']: - obj['CUSIP'] = None - elif trade_type == 'swaption': - obj['Deal Type'] = 'SwaptionDeal' - obj['ExerciseType'] = 'European' - rename_keys(obj, {'Settlement Date': 'PremiumSettlementDate', - 'Price': 'PercentageOfPremium', - 'notional': 'Notional', - 'initial_margin_percentage': 'InitialMarginPercentage'}) + if obj["CUSIP"] != obj["GlopeOp Security Identifier"]: + obj["CUSIP"] = None + elif trade_type == "swaption": + obj["Deal Type"] = "SwaptionDeal" + obj["ExerciseType"] = "European" + rename_keys( + obj, + { + "Settlement Date": "PremiumSettlementDate", + "Price": "PercentageOfPremium", + "notional": "Notional", + "initial_margin_percentage": "InitialMarginPercentage", + }, + ) - obj['RegenerateCashFlow'] = 'N' - for direction in ['Pay', 'Receive']: - obj[direction + 'MaturityDate'] = obj['MaturityDate'] - obj[direction + 'Currency'] = obj['Currency'] - obj[direction + 'Notional'] = obj['Notional'] - obj[direction + 'EffectiveDate'] = get_effective_date(obj['Trade Date'], - obj['SwapType']) - if obj['SwapType'] == 'CD_INDEX_OPTION': - for direction in ['Pay', 'Receive']: - obj[direction + 'Daycount'] = 'ACT/360' - obj[direction + 'Frequency'] = 'Quarterly' - obj[direction + 'PaymentRollConvention'] = 'Following' + obj["RegenerateCashFlow"] = "N" + for direction in ["Pay", "Receive"]: + obj[direction + "MaturityDate"] = obj["MaturityDate"] + obj[direction + "Currency"] = obj["Currency"] + obj[direction + "Notional"] = obj["Notional"] + obj[direction + "EffectiveDate"] = get_effective_date( + obj["Trade Date"], obj["SwapType"] + ) + if obj["SwapType"] == "CD_INDEX_OPTION": + for direction in ["Pay", "Receive"]: + obj[direction + "Daycount"] = "ACT/360" + obj[direction + "Frequency"] = "Quarterly" + obj[direction + "PaymentRollConvention"] = "Following" - if obj['option_type'] == 'PAYER': - obj['ReceiveLegRateType'] = 'Float' - obj['ReceiveFloatRate'] = 'US0003M' - obj['PayLegRateType'] = 'Fixed' - obj['PayFixedRate'] = obj['FixedRate'] - elif obj['option_type'] == 'RECEIVER': - obj['PayLegRateType'] = 'Float' - obj['PayFloatRate'] = 'US0003M' - obj['ReceiveLegRateType'] = 'Fixed' - obj['ReceiveFixedRate'] = obj['FixedRate'] - elif obj['SwapType'] == 'SWAPTION': - for direction in ['Pay', 'Receive']: - obj[direction + 'PaymentRollConvention'] = 'ModifiedFollowing' - if (obj['buysell'] and obj['option_type'] == 'RECEIVER') or\ - (not obj['buysell'] and obj['option_type'] == 'PAYER'): - obj['ReceiveFrequency'] = 'Half-Yearly' - obj['ReceiveDaycount'] = '30/360' - obj['PayFrequency'] = 'Quarterly' - obj['PayDaycount'] = 'ACT/360' - obj['ReceiveFixedRate'] = obj['strike'] - obj['ReceiveLegRateType'] = 'Fixed' - obj['PayLegRateType'] = 'Float' - obj['PayFloatRate'] = 'US0003M' + if obj["option_type"] == "PAYER": + obj["ReceiveLegRateType"] = "Float" + obj["ReceiveFloatRate"] = "US0003M" + obj["PayLegRateType"] = "Fixed" + obj["PayFixedRate"] = obj["FixedRate"] + elif obj["option_type"] == "RECEIVER": + obj["PayLegRateType"] = "Float" + obj["PayFloatRate"] = "US0003M" + obj["ReceiveLegRateType"] = "Fixed" + obj["ReceiveFixedRate"] = obj["FixedRate"] + elif obj["SwapType"] == "SWAPTION": + for direction in ["Pay", "Receive"]: + obj[direction + "PaymentRollConvention"] = "ModifiedFollowing" + if (obj["buysell"] and obj["option_type"] == "RECEIVER") or ( + not obj["buysell"] and obj["option_type"] == "PAYER" + ): + obj["ReceiveFrequency"] = "Half-Yearly" + obj["ReceiveDaycount"] = "30/360" + obj["PayFrequency"] = "Quarterly" + obj["PayDaycount"] = "ACT/360" + obj["ReceiveFixedRate"] = obj["strike"] + obj["ReceiveLegRateType"] = "Fixed" + obj["PayLegRateType"] = "Float" + obj["PayFloatRate"] = "US0003M" else: - obj['ReceiveFrequency'] = 'Quarterly' - obj['ReceiveDaycount'] = 'ACT/360' - obj['PayFrequency'] = 'Half-Yearly' - obj['PayDaycount'] = '30/360' - obj['ReceiveFloatRate'] = 'US0003M' - obj['ReceiveLegRateType'] = 'Float' - obj['PayLegRateType'] = 'Fixed' - obj['PayFixedRate'] = obj['strike'] + obj["ReceiveFrequency"] = "Quarterly" + obj["ReceiveDaycount"] = "ACT/360" + obj["PayFrequency"] = "Half-Yearly" + obj["PayDaycount"] = "30/360" + obj["ReceiveFloatRate"] = "US0003M" + obj["ReceiveLegRateType"] = "Float" + obj["PayLegRateType"] = "Fixed" + obj["PayFixedRate"] = obj["strike"] else: - raise ValueError("'SwapType' needs to be one of 'CD_INDEX_OPTION' or 'SWAPTION'") + raise ValueError( + "'SwapType' needs to be one of 'CD_INDEX_OPTION' or 'SWAPTION'" + ) - obj['PremiumCurrency'] = obj['Currency'] - if obj['InitialMarginPercentage']: - obj['InitialMarginCurrency'] = obj['Currency'] - obj['UnderlyingInstrument'] = obj.pop('UnderlyingSecurityId') - if obj['SwapType'] == 'CD_INDEX_OPTION': - obj['Strike'] = obj.pop('strike') + obj["PremiumCurrency"] = obj["Currency"] + if obj["InitialMarginPercentage"]: + obj["InitialMarginCurrency"] = obj["Currency"] + obj["UnderlyingInstrument"] = obj.pop("UnderlyingSecurityId") + if obj["SwapType"] == "CD_INDEX_OPTION": + obj["Strike"] = obj.pop("strike") - elif trade_type == 'cds': - freq = {4: 'Quarterly', 12: 'Monthly'} - obj['Deal Type'] = 'CreditDefaultSwapDeal' - obj['PaymentFrequency'] = freq[obj['frequency']] - obj['InitialMarginPercentage'] = obj.pop('initial_margin_percentage') - if obj['InitialMarginPercentage']: - obj['InitialMarginCurrency'] = obj['Currency'] - if obj.get('AttachmentPoint') is None: - obj['Executing Broker'] = obj['Counterparty'] - if obj['account_code'] == 'BAML': - obj['Counterparty'] = 'BAMSNY' - obj['Custodian'] = 'BOMLCM' - elif obj['account_code'] == 'WF': - obj['Counterparty'] = 'WELFEI' - obj['Custodian'] = 'WELLSFCM' - if obj['Clearing Facility'] is None: - obj['Clearing Facility'] = 'NOT CLEARED' + elif trade_type == "cds": + freq = {4: "Quarterly", 12: "Monthly"} + obj["Deal Type"] = "CreditDefaultSwapDeal" + obj["PaymentFrequency"] = freq[obj["frequency"]] + obj["InitialMarginPercentage"] = obj.pop("initial_margin_percentage") + if obj["InitialMarginPercentage"]: + obj["InitialMarginCurrency"] = obj["Currency"] + if obj.get("AttachmentPoint") is None: + obj["Executing Broker"] = obj["Counterparty"] + if obj["account_code"] == "BAML": + obj["Counterparty"] = "BAMSNY" + obj["Custodian"] = "BOMLCM" + elif obj["account_code"] == "WF": + obj["Counterparty"] = "WELFEI" + obj["Custodian"] = "WELLSFCM" + if obj["Clearing Facility"] is None: + obj["Clearing Facility"] = "NOT CLEARED" - elif trade_type == 'future': - obj['Deal Type'] = 'FutureDeal' - rename_keys(obj, {'currency': 'Trade Currency', - 'commission': 'Commission', - 'quantity': 'Quantity', - 'swap_type': 'Swap Type', - 'bbg_ticker': 'Bloomberg Ticker', - 'Currency': 'Trade Currency', - 'exchange': 'Exchange'}) - elif trade_type == 'wire': - obj['Deal Type'] = 'CashFlowDeal' - obj['Transaction Type'] = 'Transfer' - obj['Instrument Type'] = 'Cashflow' - obj['Settlement Date'] = obj['Trade Date'] - rename_keys(obj, {'amount': 'Amount'}) - elif trade_type == 'spot': - obj['Deal Type'] = 'SpotDeal' - rename_keys(obj, {'commission': 'Commission', - 'commission_currency': 'Commission Currency', - 'sell_currency': 'Sell Currency', - 'sell_amount': 'Sell Amount', - 'buy_currency': 'Buy Currency', - 'buy_amount': 'Buy Amount', - 'spot_rate': 'Spot Rate'}) + elif trade_type == "future": + obj["Deal Type"] = "FutureDeal" + rename_keys( + obj, + { + "currency": "Trade Currency", + "commission": "Commission", + "quantity": "Quantity", + "swap_type": "Swap Type", + "bbg_ticker": "Bloomberg Ticker", + "Currency": "Trade Currency", + "exchange": "Exchange", + }, + ) + elif trade_type == "wire": + obj["Deal Type"] = "CashFlowDeal" + obj["Transaction Type"] = "Transfer" + obj["Instrument Type"] = "Cashflow" + obj["Settlement Date"] = obj["Trade Date"] + rename_keys(obj, {"amount": "Amount"}) + elif trade_type == "spot": + obj["Deal Type"] = "SpotDeal" + rename_keys( + obj, + { + "commission": "Commission", + "commission_currency": "Commission Currency", + "sell_currency": "Sell Currency", + "sell_amount": "Sell Amount", + "buy_currency": "Buy Currency", + "buy_amount": "Buy Amount", + "spot_rate": "Spot Rate", + }, + ) return [obj.get(h, None) for h in HEADERS[trade_type]] -def get_bbg_data(conn, session, identifier, cusip=None, isin=None, - settle_date=None, asset_class=None, **kwargs): +def get_bbg_data( + conn, + session, + identifier, + cusip=None, + isin=None, + settle_date=None, + asset_class=None, + **kwargs, +): fields = ["MTG_FACTOR_SET_DT", "INT_ACC"] - fields_dict = {'Mtge': ["MTG_FACE_AMT", "START_ACC_DT"], - 'Corp': ["AMT_ISSUED", "PREV_CPN_DT"]} + fields_dict = { + "Mtge": ["MTG_FACE_AMT", "START_ACC_DT"], + "Corp": ["AMT_ISSUED", "PREV_CPN_DT"], + } with conn.cursor() as c: - c.execute("SELECT identifier FROM securities WHERE identifier=%s", - (identifier,)) + c.execute( + "SELECT identifier FROM securities WHERE identifier=%s", (identifier,) + ) if not c.fetchone(): - fields += ["MATURITY", "CRNCY", "NAME", "FLOATER", "FLT_SPREAD", "CPN", - "CPN_FREQ", "FIRST_CPN_DT", "MTG_PAY_DELAY", "DAY_CNT_DES"] + fields += [ + "MATURITY", + "CRNCY", + "NAME", + "FLOATER", + "FLT_SPREAD", + "CPN", + "CPN_FREQ", + "FIRST_CPN_DT", + "MTG_PAY_DELAY", + "DAY_CNT_DES", + ] cusip_or_isin = cusip or isin - for bbg_type in ['Mtge', 'Corp']: - bbg_id = cusip_or_isin + ' ' + bbg_type - data = retrieve_data(session, [bbg_id], fields + fields_dict[bbg_type], - overrides={'SETTLE_DT': settle_date} if settle_date else None) + for bbg_type in ["Mtge", "Corp"]: + bbg_id = cusip_or_isin + " " + bbg_type + data = retrieve_data( + session, + [bbg_id], + fields + fields_dict[bbg_type], + overrides={"SETTLE_DT": settle_date} if settle_date else None, + ) if data[bbg_id]: break else: - logging.error(f'{cusip_or_isin} not in bloomberg') + logging.error(f"{cusip_or_isin} not in bloomberg") return bbg_data = data[bbg_id] - if bbg_data.get('MTG_FACTOR_SET_DT', 0) == 0: - bbg_data['MTG_FACTOR_SET_DT'] = 1 - bbg_data['INT_ACC'] = 0 - if len(fields) > 2: # we don't have the data in the securities table - sql_fields = ['identifier', 'cusip', 'isin', 'description', 'face_amount', - 'maturity', 'floater', 'spread', 'coupon', 'frequency', - 'day_count', 'first_coupon_date', 'pay_delay', 'currency', - 'bbg_type', 'asset_class', 'start_accrued_date'] + if bbg_data.get("MTG_FACTOR_SET_DT", 0) == 0: + bbg_data["MTG_FACTOR_SET_DT"] = 1 + bbg_data["INT_ACC"] = 0 + if len(fields) > 2: # we don't have the data in the securities table + sql_fields = [ + "identifier", + "cusip", + "isin", + "description", + "face_amount", + "maturity", + "floater", + "spread", + "coupon", + "frequency", + "day_count", + "first_coupon_date", + "pay_delay", + "currency", + "bbg_type", + "asset_class", + "start_accrued_date", + ] placeholders = ",".join(["%s"] * len(sql_fields)) columns = ",".join(sql_fields) sqlstr = f"INSERT INTO securities({columns}) VALUES({placeholders})" - isfloater = bbg_data['FLOATER'] == 'Y' - pay_delay = bbg_data.get('MTG_PAY_DELAY', 0) - day_count = bbg_data.get('DAY_CNT_DES') + isfloater = bbg_data["FLOATER"] == "Y" + pay_delay = bbg_data.get("MTG_PAY_DELAY", 0) + day_count = bbg_data.get("DAY_CNT_DES") m = re.match("[^(\s]+", day_count) if m: day_count = m.group(0) if isinstance(pay_delay, str): - pay_delay = int(pay_delay.split(' ')[0]) + pay_delay = int(pay_delay.split(" ")[0]) with conn.cursor() as c: - c.execute(sqlstr, (identifier, cusip, isin, bbg_data['NAME'], - bbg_data.get('MTG_FACE_AMT') or bbg_data.get('AMT_ISSUED'), - bbg_data.get('MATURITY'), isfloater, - bbg_data.get('FLT_SPREAD') if isfloater else None, - bbg_data.get('CPN') if not isfloater else None, - bbg_data.get('CPN_FREQ'), day_count, - bbg_data.get('FIRST_CPN_DT'), pay_delay, - bbg_data.get('CRNCY'), bbg_type, asset_class, - bbg_data.get('START_ACC_DT') or bbg_data.get('PREV_CPN_DT'))) + c.execute( + sqlstr, + ( + identifier, + cusip, + isin, + bbg_data["NAME"], + bbg_data.get("MTG_FACE_AMT") or bbg_data.get("AMT_ISSUED"), + bbg_data.get("MATURITY"), + isfloater, + bbg_data.get("FLT_SPREAD") if isfloater else None, + bbg_data.get("CPN") if not isfloater else None, + bbg_data.get("CPN_FREQ"), + day_count, + bbg_data.get("FIRST_CPN_DT"), + pay_delay, + bbg_data.get("CRNCY"), + bbg_type, + asset_class, + bbg_data.get("START_ACC_DT") or bbg_data.get("PREV_CPN_DT"), + ), + ) conn.commit() return bbg_data + def bond_trade_process(conn, session, trade): bbg_data = get_bbg_data(conn, session, **trade) - currentface = trade['faceamount'] * bbg_data['MTG_FACTOR_SET_DT'] - accrued_payment = bbg_data['INT_ACC'] * currentface /100. - principal_payment = currentface * trade['price'] / 100. + currentface = trade["faceamount"] * bbg_data["MTG_FACTOR_SET_DT"] + accrued_payment = bbg_data["INT_ACC"] * currentface / 100.0 + principal_payment = currentface * trade["price"] / 100.0 with conn: with conn.cursor() as c: - c.execute("UPDATE bonds SET principal_payment = %s, accrued_payment = %s " - "WHERE id = %s", (principal_payment, accrued_payment, int(trade['id']))) + c.execute( + "UPDATE bonds SET principal_payment = %s, accrued_payment = %s " + "WHERE id = %s", + (principal_payment, accrued_payment, int(trade["id"])), + ) # mark it at buy price - if trade['buysell']: + if trade["buysell"]: sqlstr = "INSERT INTO marks VALUES(%s, %s, %s) ON CONFLICT DO NOTHING" with conn: with conn.cursor() as c: - c.execute(sqlstr, (trade['trade_date'], trade['identifier'], trade['price'])) + c.execute( + sqlstr, (trade["trade_date"], trade["identifier"], trade["price"]) + ) def send_email(trade): # send out email with trade content email = GmailMessage() email.set_content(print_trade(trade)) - email['To'] = 'nyops@lmcg.com' - email['Subject'] = email_subject(trade) + email["To"] = "nyops@lmcg.com" + email["Subject"] = email_subject(trade) email.send() def is_tranche_trade(trade): - return trade['swap_type'] in ('CD_INDEX_TRANCHE', 'BESPOKE') + return trade["swap_type"] in ("CD_INDEX_TRANCHE", "BESPOKE") def cds_trade_process(conn, session, trade): - sqlstr = ("SELECT indexfactor/100 FROM index_version " - "WHERE redindexcode=%(security_id)s") + sqlstr = ( + "SELECT indexfactor/100 FROM index_version " + "WHERE redindexcode=%(security_id)s" + ) try: with conn.cursor() as c: c.execute(sqlstr, trade) factor, = c.fetchone() except ValueError: - bbg_data = get_bbg_data(conn, session, trade['security_id'], - isin=trade['security_id'], - asset_class='Subprime') + bbg_data = get_bbg_data( + conn, + session, + trade["security_id"], + isin=trade["security_id"], + asset_class="Subprime", + ) - factor = bbg_data['MTG_FACTOR_SET_DT'] + factor = bbg_data["MTG_FACTOR_SET_DT"] if is_tranche_trade(trade): - tranche_factor = ((trade['attach'] - trade['detach']) / - (trade['orig_attach'] - trade['orig_detach'])) - trade['curr_notional'] = trade['notional'] * tranche_factor + tranche_factor = (trade["attach"] - trade["detach"]) / ( + trade["orig_attach"] - trade["orig_detach"] + ) + trade["curr_notional"] = trade["notional"] * tranche_factor else: - trade['curr_notional'] = trade['notional'] * factor + trade["curr_notional"] = trade["notional"] * factor return trade @@ -425,59 +717,73 @@ def generate_csv(l, trade_type="bond", fund="SERCGMAST"): return output.getvalue().encode() -def get_filepath(base_dir: pathlib.Path, trade_type: str, - fund: str) -> pathlib.Path: - d = {'bond': 'Mortgages', - 'cds': 'CreditDefaultSwapDeal', - 'swaption': 'SwaptionDeal', - 'future': 'Future', - 'wire': 'CashFlowDeal', - 'spot': 'SpotDeal', - 'capfloor': 'TODO'} +def get_filepath(base_dir: pathlib.Path, trade_type: str, fund: str) -> pathlib.Path: + d = { + "bond": "Mortgages", + "cds": "CreditDefaultSwapDeal", + "swaption": "SwaptionDeal", + "future": "Future", + "wire": "CashFlowDeal", + "spot": "SpotDeal", + "capfloor": "TODO", + } timestamp = datetime.datetime.now() if fund == "BRINKER": - return (base_dir / str(timestamp.date()) / - f"LMCG_BBH_SWAP_TRADES_P.{timestamp:%Y%m%d%H%M%S}.csv") + return ( + base_dir + / str(timestamp.date()) + / f"LMCG_BBH_SWAP_TRADES_P.{timestamp:%Y%m%d%H%M%S}.csv" + ) else: - return (base_dir / str(timestamp.date()) / - f'Serenitas.ALL.{timestamp:%Y%m%d.%H%M%S}.{d[trade_type]}.csv') + return ( + base_dir + / str(timestamp.date()) + / f"Serenitas.ALL.{timestamp:%Y%m%d.%H%M%S}.{d[trade_type]}.csv" + ) def upload_file(file_path: pathlib.Path) -> None: if "BBH" in file_path.name: return - ftp = FTP('ftp.globeop.com') - ftp.login('srntsftp', config.ftp_password) - ftp.cwd('incoming') + ftp = FTP("ftp.globeop.com") + ftp.login("srntsftp", config.ftp_password) + ftp.cwd("incoming") cmd = f"STOR {file_path.name}" with file_path.open("rb") as fh: - ftp.storbinary(cmd, fh) + ftp.storbinary(cmd, fh) -def write_buffer(buf: bytes, base_dir: pathlib.Path, - trade_type: str = "bond", - fund: str = "SERCGMAST"): +def write_buffer( + buf: bytes, + base_dir: pathlib.Path, + trade_type: str = "bond", + fund: str = "SERCGMAST", +): file_path = get_filepath(base_dir, trade_type, fund) file_path.write_bytes(buf) return file_path def email_subject(trade): - return "[{0}] {1} {2} {3}".format(trade['asset_class'], trade['action'], - "Buy" if trade['buysell'] else "Sell", - trade['description']) + return "[{0}] {1} {2} {3}".format( + trade["asset_class"], + trade["action"], + "Buy" if trade["buysell"] else "Sell", + trade["description"], + ) def print_trade(trade): d = trade.copy() - d['buysell'] = "Buy" if d["buysell"] else "Sell" + d["buysell"] = "Buy" if d["buysell"] else "Sell" return tabulate((k, v) for k, v in d.items()) if __name__ == "__main__": parser = argparse.ArgumentParser() - parser.add_argument("-n", "--no-upload", action="store_true", - help="do not upload to Globeop") + parser.add_argument( + "-n", "--no-upload", action="store_true", help="do not upload to Globeop" + ) parser.add_argument("fund", nargs="?", default="SERCGMAST") args = parser.parse_args() q = get_redis_queue() @@ -486,11 +792,11 @@ if __name__ == "__main__": except KeyError: sys.exit("Please set path of daily directory in 'DAILY_DIR'") - dawndb = dbconn('dawndb') - for trade_type in ['bond', 'cds', 'swaption', 'future', 'wire', 'spot', 'capfloor']: + dawndb = dbconn("dawndb") + for trade_type in ["bond", "cds", "swaption", "future", "wire", "spot", "capfloor"]: list_trades = get_trades(q, trade_type, args.fund) if list_trades: - if trade_type in ['bond', 'cds']: + if trade_type in ["bond", "cds"]: process_fun = globals()[f"{trade_type}_trade_process"] with init_bbg_session(BBG_IP) as session: for trade in list_trades: @@ -503,9 +809,10 @@ if __name__ == "__main__": try: buf = generate_csv( filter(lambda t: t.get("upload", True), list_trades), - trade_type, args.fund) - file_path = write_buffer(buf, DAILY_DIR, - trade_type, args.fund) + trade_type, + args.fund, + ) + file_path = write_buffer(buf, DAILY_DIR, trade_type, args.fund) if not args.no_upload: upload_file(file_path) except IOError: diff --git a/python/sftp.py b/python/sftp.py deleted file mode 100644 index 14d2ee68..00000000 --- a/python/sftp.py +++ /dev/null @@ -1,19 +0,0 @@ -import paramiko -import os - -hostname="alias.im" -port=22 -username="guillaume" -paramiko.util.log_to_file('sftp.log') - -t = paramiko.Transport((hostname, port)) -keys = paramiko.Agent().get_keys() -rsakeys = [key for key in keys if key.name=="ssh-rsa"] -gkey = rsakeys[0] -host_keys = paramiko.util.load_host_keys(os.path.expanduser('~/.ssh/known_hosts')) -hostkey = host_keys[hostname]['ssh-rsa'] -t.connect(username="guillaume", hostkey=hostkey, pkey=gkey) -sftp = paramiko.SFTPClient.from_transport(t) -dirlist = sftp.listdir('.') -print("Dirlist:", dirlist) -t.close() diff --git a/python/swaption_quotes.py b/python/swaption_quotes.py index 27a80b7a..83ecb427 100644 --- a/python/swaption_quotes.py +++ b/python/swaption_quotes.py @@ -5,14 +5,22 @@ from db import serenitas_pool from statistics import median -def get_refids(conn, index, series, expiry, value_date=datetime.date.today(), - sources=["GS", "MS", "CITI"]): - sql_str = ("SELECT ref_id, ref, quotedate FROM swaption_ref_quotes " - "WHERE quotedate::date=%s " - " AND quote_source=%s " - " AND index=%s AND series=%s" - " AND expiry=%s " - "ORDER BY quotedate DESC LIMIT 1") +def get_refids( + conn, + index, + series, + expiry, + value_date=datetime.date.today(), + sources=["GS", "MS", "CITI"], +): + sql_str = ( + "SELECT ref_id, ref, quotedate FROM swaption_ref_quotes " + "WHERE quotedate::date=%s " + " AND quote_source=%s " + " AND index=%s AND series=%s" + " AND expiry=%s " + "ORDER BY quotedate DESC LIMIT 1" + ) d = {} with conn.cursor() as c: for s in sources: @@ -20,57 +28,70 @@ def get_refids(conn, index, series, expiry, value_date=datetime.date.today(), d[s] = c.fetchone() return d -def adjust_stacks(index_type, series, expiry, - value_date=datetime.date.today(), - sources=["GS", "MS", "CITI"], common_ref=None): + +def adjust_stacks( + index_type, + series, + expiry, + value_date=datetime.date.today(), + sources=["GS", "MS", "CITI"], + common_ref=None, +): conn = serenitas_pool.getconn() d = get_refids(conn, index_type, series, expiry, value_date, sources) if all(v is None for v in d.values()): raise ValueError("no quotes") if common_ref is None: common_ref = median(v[1] for v in d.values()) - index = CreditIndex(index_type, series, "5yr", value_date=value_date, - notional=10000.) + index = CreditIndex( + index_type, series, "5yr", value_date=value_date, notional=10000.0 + ) index.ref = common_ref old_pv = index.pv quotes = {} for s, (ref_id, ref, _) in d.items(): index.ref = ref dindex_pv = index.pv - old_pv - df = pd.read_sql_query("SELECT strike, pay_bid, pay_offer, delta_pay, " - "rec_bid, rec_offer, delta_rec FROM swaption_quotes " - "WHERE ref_id=%s ORDER BY strike", - conn, - params=(ref_id,), - index_col=['strike']) + df = pd.read_sql_query( + "SELECT strike, pay_bid, pay_offer, delta_pay, " + "rec_bid, rec_offer, delta_rec FROM swaption_quotes " + "WHERE ref_id=%s ORDER BY strike", + conn, + params=(ref_id,), + index_col=["strike"], + ) if s == "GS": - df['delta_rec'] = 1 - df['delta_pay'] + df["delta_rec"] = 1 - df["delta_pay"] if index_type == "HY": - df[['pay_bid', 'pay_offer', 'rec_bid', 'rec_offer']] *=100 + df[["pay_bid", "pay_offer", "rec_bid", "rec_offer"]] *= 100 if s == "CITI": - df['delta_rec'] *= -1 - if dindex_pv != 0.: - df[['pay_bid', 'pay_offer']] = df[['pay_bid', 'pay_offer']].sub( - df.delta_pay * dindex_pv, axis=0) - df[['rec_bid', 'rec_offer']] = df[['rec_bid', 'rec_offer']].add( - df.delta_rec * dindex_pv, axis=0) + df["delta_rec"] *= -1 + if dindex_pv != 0.0: + df[["pay_bid", "pay_offer"]] = df[["pay_bid", "pay_offer"]].sub( + df.delta_pay * dindex_pv, axis=0 + ) + df[["rec_bid", "rec_offer"]] = df[["rec_bid", "rec_offer"]].add( + df.delta_rec * dindex_pv, axis=0 + ) quotes[s] = df - quotes = pd.concat(quotes, names=['source']) - quotes = quotes.swaplevel('source', 'strike').sort_index() - inside_quotes = pd.concat([ - quotes[['pay_bid', 'rec_bid']].groupby(level='strike').max(), - quotes[['pay_offer', 'rec_offer']].groupby(level='strike').min()], - axis=1 + quotes = pd.concat(quotes, names=["source"]) + quotes = quotes.swaplevel("source", "strike").sort_index() + inside_quotes = pd.concat( + [ + quotes[["pay_bid", "rec_bid"]].groupby(level="strike").max(), + quotes[["pay_offer", "rec_offer"]].groupby(level="strike").min(), + ], + axis=1, ).sort_index(axis=1) - quotes = quotes.unstack('source') + quotes = quotes.unstack("source") d = {} - for k in ['pay_bid', 'rec_bid']: - #quotes[k].style.apply(highlight_max, axis=1) + for k in ["pay_bid", "rec_bid"]: + # quotes[k].style.apply(highlight_max, axis=1) df = pd.concat([quotes[k], inside_quotes[k]], axis=1) - d[k] = df.rename(columns={k: 'Best'}) - for k in ['pay_offer', 'rec_offer']: - #quotes[k].style.apply(highlight_min, axis=1) + d[k] = df.rename(columns={k: "Best"}) + for k in ["pay_offer", "rec_offer"]: + # quotes[k].style.apply(highlight_min, axis=1) df = pd.concat([inside_quotes[k], quotes[k]], axis=1) - d[k] = df.rename(columns={k: 'Best'}) + d[k] = df.rename(columns={k: "Best"}) serenitas_pool.putconn(conn) return common_ref, pd.concat(d, axis=1) diff --git a/python/task_runner.py b/python/task_runner.py index 52bc1e98..ad2fb817 100644 --- a/python/task_runner.py +++ b/python/task_runner.py @@ -6,15 +6,16 @@ from common import get_redis_queue from subprocess import CalledProcessError from tasks import Rpc + def run(): - ET = dbconn('etdb') + ET = dbconn("etdb") q = get_redis_queue() notify("READY=1") while True: - rpc = Rpc.from_json(q.blpop("tasks")[1].decode('utf-8')) + rpc = Rpc.from_json(q.blpop("tasks")[1].decode("utf-8")) print("Running '{}' with {}".format(rpc.fun, rpc.args)) - if rpc.fun == 'generate_scenarios': + if rpc.fun == "generate_scenarios": rpc.args += [ET] try: rpc() @@ -23,13 +24,14 @@ def run(): else: print("'{}' completed".format(rpc.fun)) if rpc.fun == "build_portfolios": - q.rpush("tasks", str(Rpc('build_scenarios', rpc.args))) + q.rpush("tasks", str(Rpc("build_scenarios", rpc.args))) if rpc.fun == "build_scenarios": - q.rpush("tasks", str(Rpc('generate_scenarios', rpc.args[:-1]))) + q.rpush("tasks", str(Rpc("generate_scenarios", rpc.args[:-1]))) ET.close() -if __name__=="__main__": - logger = logging.getLogger('intex') + +if __name__ == "__main__": + logger = logging.getLogger("intex") logger.setLevel(logging.INFO) logger.addHandler(logging.StreamHandler()) run() diff --git a/python/test_hyoption.py b/python/test_hyoption.py deleted file mode 100644 index b6d01ab0..00000000 --- a/python/test_hyoption.py +++ /dev/null @@ -1,16 +0,0 @@ -from analytics import Index, Swaption -import datetime -index = Index.from_name("hy", 27, "5yr", - trade_date = datetime.date(2016, 11, 16)) -index.price = 103.75 -exercise_date = datetime.date(2017, 3, 15) -strike = 102.5 -payer = Swaption(index, exercise_date, strike, strike_is_price = True) -payer.sigma = .4 -payer.notional= 1e7 -#payer.pv = 1.948 * 1e-2 - -receiver = Swaption(index, exercise_date, strike, "receiver", strike_is_price = True) -receiver.sigma = .4 -receiver.notional= 1e7 - |