diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/insert_fx_id.py | 44 |
1 files changed, 41 insertions, 3 deletions
diff --git a/python/insert_fx_id.py b/python/insert_fx_id.py index 40053ff7..ef23770c 100644 --- a/python/insert_fx_id.py +++ b/python/insert_fx_id.py @@ -6,7 +6,9 @@ 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__) @@ -39,7 +41,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 +98,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( @@ -154,7 +188,11 @@ def main(workdate): conn = dbconn("dawndb") dawn_trades = get_forwards(workdate) for fund in FundEnum: - for counterparty in ("MSCSNY", "BAMSNY"): + for counterparty in ( + "MSCSNY", + "BAMSNY", + "GOLDNY", + ): process_rows(fund, counterparty, workdate, dawn_trades, conn) |