diff options
Diffstat (limited to 'python/collateral_calc.py')
| -rw-r--r-- | python/collateral_calc.py | 68 |
1 files changed, 62 insertions, 6 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index 36695d5b..6f9bb047 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -29,6 +29,13 @@ def get_baml_sftp_client(): transport.connect(username='lmcginvs', pkey=pkey) return SFTPClient.from_transport(transport) +def download_baml_files(d=None): + DATA_DIR = DAILY_DIR / "BAML_reports" + sftp = get_baml_sftp_client() + for f in sftp.listdir('outgoing'): + sftp.get(f"outgoing/{f}", localpath= DATA_DIR / f) + + def download_sftp_files(d=None, report_types=["OTC_CASH_ACTIVITY", "OTC_POSITIONS", "OTC_MARGIN", "OTC_MARGIN_EX_DEF", @@ -124,6 +131,53 @@ def download_gs_emails(count=20): if not p.exists(): p.write_bytes(attach.content) +def baml_collateral(d): + dawn_engine = dbengine("dawndb") + df = pd.read_csv(DAILY_DIR / "BAML_reports" / + f"OTC_Open_Positions_-_Credit_-_LMCG_{d:%Y%m%d}.CSV", + usecols=['MTM', 'ACCRUEDCPN', 'VARMARGIN', 'REDCODE', + 'NOTIONAL', 'EODSETTLEMENTPRICE', 'PERIOD', 'BUYSELL'], + index_col=['REDCODE', 'PERIOD']) + df = df[df.EODSETTLEMENTPRICE.notnull()] + positions = pd.read_sql_query("SELECT security_id, security_desc, folder, notional, currency " + "FROM list_cds_positions_by_strat(%s)", + dawn_engine, params=(d.date(),), + index_col=['security_id']) + df_helper = pd.read_sql_query("SELECT redindexcode, upper(tenor::text) AS tenor, " + "to_char(maturity, 'YYYYMM')::integer AS PERIOD FROM index_desc", + dawn_engine, + index_col=['redindexcode', 'tenor']) + positions['tenor'] = (positions.security_desc + "R").str.split(" ", expand=True)[4] + positions = positions.set_index('tenor', append=True) + positions['PERIOD'] = df_helper.loc[positions.index] + positions = positions.reset_index(['tenor']).set_index(['PERIOD'], append=True) + df['DIRTYUPFRONT'] = df.VARMARGIN / df.NOTIONAL + df['DIRTYUPFRONT'] = df.DIRTYUPFRONT.where(df.BUYSELL == 'Sell', -df.DIRTYUPFRONT) + positions['dirtyupfront'] = df.loc[positions.index, 'DIRTYUPFRONT'] + positions['amount'] = positions['notional'] * positions['dirtyupfront'] + df = (positions. + groupby('folder'). + agg({'amount': 'sum', 'currency': 'first'}). + reset_index('folder')) + df.columns = ['Strategy', 'Amount', 'Currency'] + df.Strategy = df.Strategy.map({'HEDGE_MBS': 'MBSCDSCSH', + 'SER_ITRXCURVE': 'SER_ITRXCVCSH', + 'SER_IGCURVE': 'SER_IGCVECSH', + 'HYOPTDEL': 'HYCDSCSH', + 'IGOPTDEL': 'IGCDSCSH'}) + df_margin = pd.read_csv(DAILY_DIR / "BAML_reports" / + f"OTC_Moneyline_{d:%Y%m%d}.CSV", + usecols=['Statement Date', 'AT CCY', 'Initial Margin Requirement'], + parse_dates=['Statement Date']) + df_margin.columns = ['date', 'currency', 'amount'] + df_margin['account'] = 'V0NSCLMFCM' + try: + dawn_engine.execute("INSERT INTO fcm_im " + "VALUES(%(date)s, %(account)s, %(currency)s, %(amount)s)", + df_margin.iloc[-1].to_dict()) + except IntegrityError: + pass + return df def sg_collateral(d): df_activity = pd.read_csv(DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_CASH_ACTIVITY.csv", @@ -251,17 +305,17 @@ def gs_collateral(d): return df -def send_email(account, df_ms, df_sg, df_gs): +def send_email(account, df_ms, df_baml, df_gs): pd.set_option('display.float_format', '{:.2f}'.format) content = HTMLBody('<html><body>' '<h3>At Morgan Stanley:</h3>' '{}' - '<h3>At Societe Generale:</h3>' + '<h3>At Bank of America Merrill Lynch:</h3>' '{}' '<h3>At Goldman Sachs:</h3>' '{}' '</body><html>'.format(df_ms.to_html(index=False), - df_sg.to_html(index=False), + df_baml.to_html(index=False), df_gs.to_html(index=False))) m = Message( account=account, @@ -278,17 +332,19 @@ if __name__ == "__main__": download_ms_emails() download_gs_emails() d = (pd.Timestamp.today() - BDay()).normalize() - download_sftp_files(d) + #download_sftp_files(d) + download_baml_files() try: df_ms = ms_collateral(d) except FileNotFoundError as e: logging.info(e) df_ms = ms_collateral(d - BDay()) - df_sg = sg_collateral(d) + #df_sg = sg_collateral(d) + df_baml = baml_collateral(d) try: df_gs = gs_collateral(d) except FileNotFoundError as e: logging.info(e) df_gs = gs_collateral(d - BDay()) account = get_account('ghorel@lmcg.com') - send_email(account, df_ms, df_sg, df_gs) + send_email(account, df_ms, df_baml, df_gs) |