diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/collateral_calc.py | 57 |
1 files changed, 51 insertions, 6 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index d1279ca8..69f18dfd 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -211,6 +211,51 @@ def baml_collateral(d): pass return df + +def wells_collateral(d): + account = "A5882186" + df = pd.read_csv(DAILY_DIR / "Wells_reports" / + f"OTC_CDS_Position_Activity_{account}_{d:%m%d%Y}.csv", + usecols=["TENOR", "MARKET_VALUE_NPV", "STATEMENT_CLOSING_PRICE", + "PAIR_CLIP", "BUY_SELL", "NOTIONAL", + "MATURITY_DATE"], + parse_dates=["MATURITY_DATE"], + index_col=["PAIR_CLIP", "MATURITY_DATE"]) + positions = pd.read_sql_query("SELECT security_id, security_desc, maturity, " + "folder, notional, currency " + "FROM list_cds_positions_by_strat_fcm(%s, 'WF')", + dawn_engine, params=(d.date(),), + index_col=["security_id", "maturity"]) + df["DIRTYUPFRONT"] = df.MARKET_VALUE_NPV / df.NOTIONAL + df["DIRTYUPFRONT"] = df.DIRTYUPFRONT.where(df.BUY_SELL == 1, -df.DIRTYUPFRONT) + df.index.names = ["security_id", "maturity"] + positions = positions.join(df, how="left") + positions["amount"] = positions["notional"] * positions["DIRTYUPFRONT"] + positions.folder = positions.folder.map({'HEDGE_MBS': 'MBSCDSCSH', + 'SER_ITRXCURVE': 'SER_ITRXCVCSH', + 'SER_IGCURVE': 'SER_IGCVECSH', + 'HYOPTDEL': 'COCSH', + 'IGOPTDEL': 'COCSH', + 'IGINX': 'TCSH'}) + df = (positions. + groupby('folder'). + agg({"amount": "sum", + "currency": "first"}). + reset_index("folder")) + df.columns = ["Strategy", "Amount", "Currency"] + df_margin = pd.read_csv(DAILY_DIR / "Wells_reports" / + f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv", + usecols=["CURRENCY_NAME", "CURRENT_IM", "VALUE_DATE"], + parse_dates=["VALUE_DATE"], + index_col=["CURRENCY_NAME"]) + try: + dawn_engine.execute("INSERT INTO fcm_im " + "VALUES(%s, 'WFNSCLMFCM', 'USD', %s)", + df_margin.loc["ZZZZZ", ["VALUE_DATE", "CURRENT_IM"]].tolist()) + 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", usecols=["Ticket Reference", "Record Type", "Currency", "Amount"]) @@ -377,7 +422,7 @@ def citi_collateral(d, dawn_trades): return df -def send_email(df_ms, df_baml, df_gs, df_citi): +def send_email(dfs): pd.set_option('display.float_format', '{:.2f}'.format) content = HTMLBody('<html><body>' '<h3>At Morgan Stanley:</h3>' @@ -388,10 +433,9 @@ def send_email(df_ms, df_baml, df_gs, df_citi): '{}' '<h3>At Citi:</h3>' '{}' - '</body><html>'.format(df_ms.to_html(index=False), - df_baml.to_html(index=False), - df_gs.to_html(index=False), - df_citi.to_html(index=False))) + '<h3>At Wells Fargo:</h3>' + '{}' + '</body><html>'.format([df.to_html(index=False) for df in dfs]) em = ExchangeMessage() em.send_email("IAM booking", content, ['serenitas.otc@sscinc.com'], @@ -420,6 +464,7 @@ if __name__ == "__main__": except FileNotFoundError as e: logging.info(e) df_gs = gs_collateral(d - BDay(), dawn_trades) + df_wells = wells_collateral(d) d = pd.Timestamp.today().normalize() df_citi = citi_collateral(d, dawn_trades) - send_email(df_ms, df_baml, df_gs, df_citi) + send_email([df_ms, df_baml, df_gs, df_citi, df_wells]) |