diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/collateral/__main__.py | 8 | ||||
| -rw-r--r-- | python/collateral/gs_fcm.py | 87 |
2 files changed, 91 insertions, 4 deletions
diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py index ade60c21..69c4ee12 100644 --- a/python/collateral/__main__.py +++ b/python/collateral/__main__.py @@ -47,7 +47,7 @@ cp_dict = { "isda_cps": ("citi", "baml_isda", "ms", "gs", "bnp", "cs"), }, "Brinker": {"fcms": (), "isda_cps": ("ms", "gs")}, - "BowdSt": {"fcms": (), "isda_cps": ("ms", "bnp", "gs", "baml_isda")}, + "BowdSt": {"fcms": ("gs_fcm",), "isda_cps": ("ms", "bnp", "gs", "baml_isda")}, } @@ -70,7 +70,7 @@ def run_collateral(cp, fund, positions, workdate, engine): df = {} -fcm_mapping = {"baml_fcm": "BAML", "wells": "WF"} +fcm_mapping = {"baml_fcm": "BAML", "wells": "WF", "gs_fcm": "GS"} fund_mapping = {"Serenitas": "SERCGMAST", "Brinker": "BRINKER", "BowdSt": "BOWDST"} for fund in ("Serenitas", "Brinker", "BowdSt"): @@ -81,9 +81,9 @@ for fund in ("Serenitas", "Brinker", "BowdSt"): positions = pd.read_sql_query( "SELECT security_id, security_desc, maturity, " "folder, notional, currency " - "FROM list_cds_positions_by_strat_fcm(%s, %s)", + "FROM list_cds_positions_by_strat_fcm(%s, %s, %s)", dawn_engine, - params=(args.workdate.date(), fcm_mapping[fcm]), + params=(args.workdate.date(), fcm_mapping[fcm], fund_mapping[fund]), index_col=["security_id", "maturity"], ) df[(fund, fcm.upper())] = run_collateral( diff --git a/python/collateral/gs_fcm.py b/python/collateral/gs_fcm.py new file mode 100644 index 00000000..87ab09a2 --- /dev/null +++ b/python/collateral/gs_fcm.py @@ -0,0 +1,87 @@ +import pandas as pd + +from . import DAILY_DIR, SftpClient +from .common import compare_notionals, STRATEGY_CASH_MAPPING + + +def collateral(d, positions, *, engine, **kwargs): + df = pd.read_csv( + DAILY_DIR + / "BowdSt" + / "GS_fcm_reports" + / f"Open _Trades_Report_LMCG_51341_{d:%Y%m%d}.csv", + usecols=[ + "Notional", + "Direction", + "NPV (local)", + "Maturity Date", + "COB Date", + "Red Code", + ], + parse_dates=["COB Date", "Maturity Date"], + index_col=["Red Code", "Maturity Date"], + thousands=",", + ) + df.Notional = df.Notional.where(df.Direction == "Buy", -df.Notional) + df.index.names = ["security_id", "maturity"] + df = df.groupby(level=["security_id", "maturity"])[ + ["Notional", "NPV (local)"] + ].sum() + df = df.rename(columns={"Notional": "NOTIONAL"}) + compare_notionals(df, positions, "GS") + positions["Amount"] = df.reindex(positions.index)["NPV (local)"] + positions.folder = positions.folder.replace(STRATEGY_CASH_MAPPING) + + def aux(row): + if row.folder == "XCURVE": + return "SER_IGCVECSH" if row.currency == "USD" else "SER_ITRXCVCSH" + else: + return row.folder + + positions.folder = positions.apply(aux, axis=1) + df = ( + positions.groupby(["folder", "currency"]) + .agg({"Amount": "sum"}) + .reset_index(["folder", "currency"]) + ) + df = df.rename(columns={"folder": "Strategy", "currency": "Currency"}) + df_margin = pd.read_csv( + DAILY_DIR + / "BowdSt" + / "GS_fcm_reports" + / f"Account_Balances_and_Margin_Report_LMCG_51338_{d:%Y%m%d}.csv", + parse_dates=["COB Date"], + thousands=",", + ) + + df_margin = df_margin.rename( + columns={ + "Opening Balance (local)": "beginning_balance", + "Ending Balance (local)": "ending_balance", + "PAI (local)": "pai", + "Account Value (local)": "account_value_market", + "Initial Margin Requirement (local)": "current_im", + "Excess/Deficit (local)": "current_excess_deficit", + "Currency": "currency", + "GS Account Number": "account", + "COB Date": "date", + } + ) + cols = [ + "date", + "account", + "beginning_balance", + "ending_balance", + "pai", + "account_value_market", + "current_im", + "current_excess_deficit", + "currency", + ] + place_holders = ",".join(["%s"] * len(cols)) + engine.execute( + f"INSERT INTO fcm_moneyline({','.join(cols)}) VALUES({place_holders})", + list(df_margin[cols].itertuples(index=False)), + ) + df["date"] = d + return df.set_index("Strategy") |