diff options
Diffstat (limited to 'python/collateral/sg.py')
| -rw-r--r-- | python/collateral/sg.py | 137 |
1 files changed, 137 insertions, 0 deletions
diff --git a/python/collateral/sg.py b/python/collateral/sg.py new file mode 100644 index 00000000..064890b6 --- /dev/null +++ b/python/collateral/sg.py @@ -0,0 +1,137 @@ +from . import DAILY_DIR +from paramiko import Transport, SFTPClient + + +def get_sftp_client(): + transport = Transport(("prmssp.amer.sgcib.com", 22)) + transport.connect(username="SerenitasGamma@USA", password="SSqrrLL99") + return SFTPClient.from_transport(transport) + + +def download_sftp_files( + d=None, + report_types=[ + "OTC_CASH_ACTIVITY", + "OTC_POSITIONS", + "OTC_MARGIN", + "OTC_MARGIN_EX_DEF", + "OTC_STATEMENT", + ], + retry_count=0, +): + if retry_count > 20: + return + DATA_DIR = DAILY_DIR / "SG_reports" + sftp = get_sftp_client() + if d is None: + for f in sftp.listdir("OTC"): + if f.endswith("OTC_STATEMENT.xls"): + print(f) + sftp.get(f"OTC/{f}", localpath=DATA_DIR / f) + else: + for report_type in report_types[:-1]: + if f.endswith(f"{report_type}.csv"): + print(f) + sftp.get(f"OTC/{f}", localpath=DATA_DIR / f) + else: + continue + + else: + file_list = sftp.listdir("OTC") + for report_type in report_types: + if report_type == "OTC_STATEMENT": + f = f"{d:%Y%m%d}_{report_type}.xls" + else: + f = f"{d:%Y%m%d}_{report_type}.csv" + if f not in file_list: + logger.info("File not here yet, trying again in 500s...") + logger.info(f"Try count: {retry_count}") + sleep(500) + sftp.close() + download_sftp_files(d, report_types, retry_count + 1) + else: + sftp.get(f"OTC/{f}", localpath=DATA_DIR / f) + sftp.close() + + +def collateral(d): + df_activity = pd.read_csv( + DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_CASH_ACTIVITY.csv", + usecols=["Ticket Reference", "Record Type", "Currency", "Amount"], + ) + df_position = pd.read_csv( + DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_POSITIONS.csv", + usecols=["Ticket Reference", "Reference Entity", "Mtm Value"], + ) + df_activity = df_activity.loc[df_activity["Record Type"] == "VM"].set_index( + "Ticket Reference" + ) + df_margin = pd.read_csv( + DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_MARGIN_EX_DEF.csv", + usecols=["Currency", "SG IMR"], + ) + df_position = df_position.set_index("Ticket Reference") + # expired_trades + # df_position = df_position.append( + # pd.DataFrame({"Reference Entity": 'CDX-NAIGS29V1-5Y', "Mtm Value": 0.}, + # index=['T2201711010000A3K20000045561220U'])) + df = df_activity.join(df_position) + # expired trade (need to figure out how to get them from the report) + # df.loc['N201811090000A3K215946925849228U1', 'Mtm Value'] = 0. + # df.loc['N201811090000A3K215946925849228U1', 'Reference Entity'] = 'CDX-NAIGS31V1-5Y' + + df["Collateral"] = df["Mtm Value"] - df["Amount"] + ref_entity = df["Reference Entity"].str.split("-", expand=True) + del ref_entity[0] + ref_entity.columns = ["to_split", "tenor"] + ref_entity = ref_entity.join( + ref_entity["to_split"].str.extract("(IG|HY|EUROPE)S(\d+)V(\d+)$", expand=True) + ) + del ref_entity["to_split"] + ref_entity.columns = ["tenor", "index_type", "series", "version"] + ref_entity.index_type[ref_entity.index_type == "EUROPE"] = "EU" + df = df.join(ref_entity) + df = df.groupby(["index_type", "series", "tenor"])["Collateral"].sum() + positions = pd.read_sql_query( + "SELECT security_desc, folder, notional, currency " + "FROM list_cds_positions_by_strat(%s)", + dawn_engine, + params=(d.date(),), + ) + instruments = positions.security_desc.str.split(expand=True)[[1, 3, 4]] + instruments.columns = ["index_type", "series", "tenor"] + instruments.series = instruments.series.str.extract("S(\d+)") + instruments.index_type[instruments.index_type == "EUR"] = "EU" + positions = positions.join(instruments) + del positions["security_desc"] + positions = positions.set_index(["index_type", "series", "tenor"]) + df = positions.join(df) + + def f(g): + g.Collateral = g.Collateral * g.notional / g.notional.sum() + return g + + df = df.groupby(level=["index_type", "series", "tenor"]).apply(f) + df = df.groupby(["folder"]).agg({"Collateral": "sum", "currency": "first"}) + df = df.reset_index("folder") + df = df.rename( + columns={"folder": "Strategy", "currency": "Currency", "Collateral": "Amount"} + ) + df.Strategy = df.Strategy.map( + { + "HEDGE_MBS": "MBSCDSCSH", + "SER_ITRXCURVE": "SER_ITRXCVCSH", + "SER_IGCURVE": "SER_IGCVECSH", + "HYOPTDEL": "HYCDSCSH", + "IGOPTDEL": "IGCDSCSH", + } + ) + df_margin["account"] = "SGNSCLMASW" + df_margin = df_margin.rename(columns={"SG IMR": "amount", "Currency": "currency"}) + df_margin["date"] = d + try: + df_margin.to_sql("fcm_im", dawn_engine, if_exists="append", index=False) + except IntegrityError: + pass + df["date"] = d + return df |