diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/insert_fx_id.py | 148 |
1 files changed, 95 insertions, 53 deletions
diff --git a/python/insert_fx_id.py b/python/insert_fx_id.py index 93015a62..827cb319 100644 --- a/python/insert_fx_id.py +++ b/python/insert_fx_id.py @@ -1,16 +1,19 @@ import argparse import datetime import logging +import sys 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 +from collateral.baml_isda import load_excel as load_excel_baml +from collateral.gs import load_file as load_excel_gs + from report_ops.enums import FundEnum logger = logging.getLogger(__name__) -logger.setLevel(logging.INFO) +logging.basicConfig(stream=sys.stdout, level=logging.DEBUG) FX_REPORT_COLUMNS = [ "cpty_id", @@ -39,7 +42,7 @@ def read_BAMSNY(fund, workdate): fname = next( REPORTS_DIR.glob(f"301__LMCG_INVESTMEN{get_tag(fund)}_CSA_{workdate:%m%d%Y}_*") ) - df = load_excel(fname) + df = load_excel_baml(fname) df = df[df["ProductID"] == "FX_Fwd"] df = df[ [ @@ -96,6 +99,38 @@ def read_MSCSNY(fund, workdate): yield from df.itertuples() +def extract_amounts_and_currencies(row): + cpty_id = row["Trade Id"] + trade_date = datetime.datetime.strptime(row["Trade Date"], "%d-%b-%Y").date() + settle_date = datetime.datetime.strptime(row["Maturity Date"], "%d-%b-%Y").date() + if row["Notional(1)"] < 0: # From perspective of GSIL + buy_amount = abs(row["Notional(1)"]) + buy_currency = row["Not1Ccy"] + sell_amount = abs(row["Notional(2)"]) + sell_currency = row["Not2Ccy"] + else: + buy_amount = abs(row["Notional(2)"]) + buy_currency = row["Not2Ccy"] + sell_amount = abs(row["Notional(1)"]) + sell_currency = row["Not1Ccy"] + return { + "cpty_id": cpty_id, + "trade_date": trade_date, + "settle_date": settle_date, + "buy_amount": buy_amount, + "buy_currency": buy_currency, + "sell_amount": sell_amount, + "sell_currency": sell_currency, + } + + +def read_GOLDNY(fund, workdate): + df = load_excel_gs(prev_business_day(workdate), fund, "Trade_Detail") + df = df[df["Transaction Type"] == "FX"] + result = df.apply(extract_amounts_and_currencies, axis=1) + yield from pd.DataFrame.from_records(result).itertuples() + + def get_forwards(cob): df = pd.read_sql_query("SELECT * FROM forward_trades", con=dawn_engine) df[["buy_amount", "sell_amount"]] = df[["buy_amount", "sell_amount"]].apply( @@ -104,66 +139,73 @@ def get_forwards(cob): return df +def process_rows(fund, counterparty, workdate, dawn_trades, conn): + read_fun = globals()[f"read_{counterparty}"] + for row in read_fun(fund.name, workdate): + 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.value) + ] + filters = { + "trade_date": row.trade_date, + "settle_date": row.settle_date, + "buy_currency": row.buy_currency, + "sell_currency": row.sell_currency, + "buy_amount": row.buy_amount, + "sell_amount": row.sell_amount, + } + matching_candidates = matching_candidates.loc[ + (matching_candidates[list(filters.keys())] == pd.Series(filters)).all( + axis=1 + ) + ] + if not matching_candidates.empty: + 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, + ), + ) + logger.info( + f"MATCHED {matched_candidate.id}: {table} to {row.cpty_id}" + ) + conn.commit() + else: + raise ValueError(f"This is an unknown trade from us. {row.cpty_id}") + else: + pass # Will later start verifying that our trades are matched right + + def main(workdate): conn = dbconn("dawndb") dawn_trades = get_forwards(workdate) for fund in FundEnum: - for counterparty in ("MSCSNY", "BAMSNY"): - read_fun = globals()[f"read_{counterparty}"] - for row in read_fun(fund.name, workdate): - 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.value) - ] - filters = { - "trade_date": row.trade_date, - "settle_date": row.settle_date, - "buy_currency": row.buy_currency, - "sell_currency": row.sell_currency, - "buy_amount": row.buy_amount, - "sell_amount": row.sell_amount, - } - matching_candidates = matching_candidates.loc[ - ( - matching_candidates[list(filters.keys())] - == pd.Series(filters) - ).all(axis=1) - ] - if not matching_candidates.empty: - 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, - ), - ) - logger.info("UPDATED %", matched_candidate.id) - conn.commit() - else: - raise ValueError( - f"This is an unknown trade from us. {row.cpty_id}" - ) - else: - pass # Will later start verifying that our trades are matched right + for counterparty in ( + "MSCSNY", + "BAMSNY", + "GOLDNY", + ): + try: + process_rows(fund, counterparty, workdate, dawn_trades, conn) + except FileNotFoundError as e: + logger.warning(f"FileNotFound for {fund}: {counterparty} on {workdate}") if __name__ == "__main__": parser = argparse.ArgumentParser() parser.add_argument( "workdate", - type=datetime.date, + type=datetime.date.fromisoformat, nargs="?", default=datetime.date.today(), help="Workdate (YYYY-MM-DD)", |