diff options
Diffstat (limited to 'python/insert_fx_id.py')
| -rw-r--r-- | python/insert_fx_id.py | 79 |
1 files changed, 74 insertions, 5 deletions
diff --git a/python/insert_fx_id.py b/python/insert_fx_id.py index de9cda36..e4e417ae 100644 --- a/python/insert_fx_id.py +++ b/python/insert_fx_id.py @@ -4,6 +4,7 @@ import pandas as pd from serenitas.utils.env import DAILY_DIR from serenitas.analytics.dates import prev_business_day from serenitas.utils.db import dawn_engine +from serenitas.utils.db import dbconn from collateral.baml_isda import load_excel @@ -17,6 +18,8 @@ FX_REPORT_COLUMNS = [ "sell_amount", ] +_fund_enums = {"Serenitas": "SERCGMAST", "BowdSt": "BOWDST", "Selene": "ISOSEL"} + def get_tag(fund): if fund == "Serenitas": @@ -48,7 +51,15 @@ def read_BAMSNY(fund, cob): ] ] df.columns = FX_REPORT_COLUMNS - return df + for date_column in ( + "trade_date", + "settle_date", + ): + df[date_column] = pd.to_datetime(df[date_column], infer_datetime_format=True) + df[["buy_amount", "sell_amount"]] = df[["buy_amount", "sell_amount"]].apply( + pd.to_numeric, errors="coerce" + ) + yield from df.itertuples() def read_MSCSNY(fund, cob): @@ -67,18 +78,75 @@ def read_MSCSNY(fund, cob): ] ] df.columns = FX_REPORT_COLUMNS - return df + for date_column in ( + "trade_date", + "settle_date", + ): + df[date_column] = pd.to_datetime(df[date_column], infer_datetime_format=True) + df[["buy_amount", "sell_amount"]] = df[["buy_amount", "sell_amount"]].apply( + pd.to_numeric, errors="coerce" + ) + yield from df.itertuples() def get_forwards(cob): - return pd.read_sql_query("SELECT * FROM forwards", con=dawn_engine) + df = pd.read_sql_query("SELECT * FROM forward_trades", con=dawn_engine) + df[["buy_amount", "sell_amount"]] = df[["buy_amount", "sell_amount"]].apply( + pd.to_numeric, errors="coerce" + ) + return df def main(cob): - for fund in ("Serenitas", "BowdSt", "Selene"): + conn = dbconn("dawndb") + dawn_trades = get_forwards(cob) + for fund, fund_code in _fund_enums.items(): for counterparty in ("MSCSNY", "BAMSNY"): read_fun = globals()[f"read_{counterparty}"] - print(read_fun(fund, cob)) + for row in read_fun(fund, cob): + if row.cpty_id not in dawn_trades.cpty_id.values: + matching_candidates = dawn_trades.loc[ + (dawn_trades["cp_code"] == counterparty) + & (dawn_trades["fund"] == fund_code) + ] + for filter_param in ( + "trade_date", + "settle_date", + "buy_currency", + "sell_currency", + "buy_amount", + "sell_amount", + ): + matching_candidates = matching_candidates.loc[ + matching_candidates[filter_param] + == getattr(row, filter_param) + ] + if matching_candidates.empty: + raise ValueError( + f"This is an unknown trade from us. {row.cpty_id}" + ) + else: + matched_candidate = matching_candidates.iloc[0, :] + with conn.cursor() as c: + if matched_candidate.fx_type == "SPOT": + table = "spots" + column = "cpty_id" + elif matched_candidate.fx_type in ( + "NEAR", + "FAR", + ): + table = "fx_swaps" + column = f"{matched_candidate.fx_type.lower()}_cpty_id" + c.execute( + f"UPDATE {table} SET {column}=%s WHERE id=%s", + ( + row.cpty_id, + matched_candidate.id, + ), + ) + conn.commit() + else: + pass # Will later start verifying that our trades are matched right if __name__ == "__main__": @@ -86,6 +154,7 @@ if __name__ == "__main__": parser.add_argument( "cob", type=datetime.date, + nargs="?", default=prev_business_day(datetime.date.today()), help="Date to process (YYYY-MM-DD)", ) |