diff options
Diffstat (limited to 'python/collateral_calc.py')
| -rw-r--r-- | python/collateral_calc.py | 86 |
1 files changed, 66 insertions, 20 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index 4d0589a0..7ad97049 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -251,13 +251,29 @@ def baml_collateral(d): 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"]) + file_name = (DAILY_DIR / "Wells_reports" / + f"OTC_CDS_Position_Activity_{account}_{d:%m%d%Y}.csv") + try: + df = pd.read_csv(file_name, + usecols=["TENOR", "MARKET_VALUE_NPV", + "PAIR_CLIP", "BUY_SELL", "NOTIONAL", + "MATURITY_DATE"], + parse_dates=["MATURITY_DATE"], + index_col=["PAIR_CLIP", "MATURITY_DATE"]) + except ValueError: + # backpopulated files have a different format... + df = pd.read_csv(file_name, + usecols=["Tenor", "NPV", "Reference_Entity_ID", + "Fixed_Rate_Notional_Buy", "Amount", + "Scheduled_Termination_Date"], + parse_dates=["Scheduled_Termination_Date"], + index_col=["Reference_Entity_ID", "Scheduled_Termination_Date"]) + df = df.rename(columns={"Tenor": "TENOR", + "NPV": "MARKET_VALUE_NPV", + "Amount": "NOTIONAL"}) + df["BUY_SELL"] = 1 + df.loc[df.Fixed_Rate_Notional_Buy.isnull(), "BUY_SELL"] = 2 + del df["Fixed_Rate_Notional_Buy"] positions = pd.read_sql_query("SELECT security_id, security_desc, maturity, " "folder, notional, currency " "FROM list_cds_positions_by_strat_fcm(%s, 'WF')", @@ -405,13 +421,20 @@ def load_citi_file(d): return pd.read_excel(fname, skiprows=6, skipfooter=2) -def get_dawn_trades(): +def get_dawn_trades(d): df_cds = pd.read_sql_query("SELECT cpty_id, folder FROM cds " - "WHERE cpty_id IS NOT NULL", dawn_engine) + "WHERE cpty_id IS NOT NULL AND trade_date <= %s", + dawn_engine, + params=(d,)) df_swaptions = pd.read_sql_query("SELECT cpty_id, folder FROM swaptions " - "WHERE cpty_id IS NOT NULL", dawn_engine) + "WHERE cpty_id IS NOT NULL " + "AND trade_date <= %s", + dawn_engine, params=(d,)) df_caps = pd.read_sql_query("SELECT cpty_id, folder FROM capfloors " - "WHERE cpty_id IS NOT NULL", dawn_engine) + "WHERE cpty_id IS NOT NULL " + "AND trade_date <= %s", + dawn_engine, + params=(d,)) df = pd.concat([df_cds, df_swaptions, df_caps]) df = df.replace({"folder": {'IGREC': 'COCSH', 'IGPAYER': 'COCSH', @@ -496,14 +519,19 @@ if __name__ == "__main__": parser.add_argument('workdate', nargs='?', type=lambda s: pd.datetime.strptime(s, "%Y-%m-%d").date(), default=pd.Timestamp.today().normalize()) + parser.add_argument("-d", "--download", action="store_true", + help="download counterparty reports") + parser.add_argument("-s", "--send-email", action="store_true", + help="send email to Globeop") args = parser.parse_args() - download_ms_emails() - download_gs_emails() - download_citi_emails() - #download_sftp_files(d) - download_baml_files() - download_wells_files() - dawn_trades = get_dawn_trades() + if args.download: + download_ms_emails() + download_gs_emails() + download_citi_emails() + download_baml_files() + download_wells_files() + + dawn_trades = get_dawn_trades(args.workdate) df_citi = citi_collateral(args.workdate, dawn_trades) args.workdate = args.workdate - bus_day try: @@ -519,5 +547,23 @@ if __name__ == "__main__": logging.info(e) df_gs = gs_collateral(args.workdate-busday, dawn_trades) df_wells = wells_collateral(args.workdate) - d = pd.Timestamp.today().normalize() - send_email(args.workdate, [df_ms, df_baml, df_gs, df_citi, df_wells]) + df = pd.concat([df_gs.set_index("Strategy"), + df_ms.set_index("Strategy"), + df_citi.set_index("Strategy"), + df_wells.set_index("Strategy"), + df_baml.set_index("Strategy")], + keys=["GS", "MS", "CITI", "WF", "BAML"], + names=["broker", "strategy"]).reset_index() + df.strategy = df.strategy.str.replace("^(M_|SER_)?", "", 1) + from db import dbconn + conn = dbconn('dawndb') + sql_str = ("INSERT INTO strategy_im VALUES(%s, %s, %s, %s, %s) " + "ON CONFLICT (date, strategy, broker) DO UPDATE " + "SET currency=EXCLUDED.currency, amount=EXCLUDED.amount") + with conn.cursor() as c: + for t in df.itertuples(index=False): + c.execute(sql_str, (args.workdate, *t)) + conn.commit() + conn.close() + if args.send_email: + send_email(args.workdate, [df_ms, df_baml, df_gs, df_citi, df_wells]) |