diff options
| -rw-r--r-- | python/insert_fx_id.py | 79 | ||||
| -rw-r--r-- | sql/dawn.sql | 20 |
2 files changed, 85 insertions, 14 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)", ) diff --git a/sql/dawn.sql b/sql/dawn.sql index fc3721a2..23f7e05c 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4046,8 +4046,8 @@ EXECUTE PROCEDURE update_account('ISDA'); CREATE TYPE author AS ENUM ('auto', 'manual'); -CREATE OR REPLACE VIEW forward_trades -AS SELECT a.*, c.name from(SELECT unnest(ARRAY[fx_swaps.dealid::text, fx_swaps.dealid::text]) AS dealid, + CREATE OR REPLACE VIEW forward_trades +AS SELECT a.*, c.name from(SELECT unnest(ARRAY[fx_swaps.dealid::text, fx_swaps.dealid::text]) AS dealid, fx_swaps.trade_date, unnest(ARRAY[fx_swaps.near_settle_date, fx_swaps.far_settle_date]) AS settle_date, fx_swaps.fund, @@ -4056,14 +4056,15 @@ AS SELECT a.*, c.name from(SELECT unnest(ARRAY[fx_swaps.dealid::text, fx_swaps.d fx_swaps.cp_code, unnest(ARRAY[fx_swaps.near_buy_currency, fx_swaps.far_buy_currency]) AS buy_currency, unnest(ARRAY[fx_swaps.near_sell_currency, fx_swaps.far_sell_currency]) AS sell_currency, - unnest(ARRAY[fx_swaps.near_buy_amount, fx_swaps.far_buy_amount]) AS buy_amount, - unnest(ARRAY[fx_swaps.near_sell_amount, fx_swaps.far_sell_amount]) AS sell_amount, + unnest(ARRAY[fx_swaps.near_buy_amount::numeric(11,2), fx_swaps.far_buy_amount::numeric(11,2)]) AS buy_amount, + unnest(ARRAY[fx_swaps.near_sell_amount::numeric(11,2), fx_swaps.far_sell_amount::numeric(11,2)]) AS sell_amount, unnest(ARRAY[fx_swaps.near_cpty_id, fx_swaps.far_cpty_id]) AS cpty_id, unnest(ARRAY[fx_swaps.near_rate, fx_swaps.far_rate]) AS spot_rate, - fx_swaps.id as id + fx_swaps.id as id, + unnest(ARRAY['NEAR', 'FAR']) as fx_type FROM fx_swaps UNION - SELECT spots.dealid, + SELECT spots.dealid, spots.trade_date, spots.settle_date, spots.fund, @@ -4072,11 +4073,12 @@ UNION spots.cp_code, spots.buy_currency, spots.sell_currency, - spots.buy_amount, - spots.sell_amount, + spots.buy_amount::numeric(11,2), + spots.sell_amount::numeric(11,2), spots.cpty_id, spots.spot_rate, - spots.id as id + spots.id as id, + 'SPOT' as fx_type FROM spots) a LEFT JOIN counterparties c ON cp_code=code ; |