aboutsummaryrefslogtreecommitdiffstats
path: root/python
diff options
context:
space:
mode:
Diffstat (limited to 'python')
-rw-r--r--python/collateral/baml_isda.py39
-rw-r--r--python/external_deriv_marks.py82
2 files changed, 79 insertions, 42 deletions
diff --git a/python/collateral/baml_isda.py b/python/collateral/baml_isda.py
index c5918a88..4df80c68 100644
--- a/python/collateral/baml_isda.py
+++ b/python/collateral/baml_isda.py
@@ -87,7 +87,7 @@ def download_files(d=None, count=20):
p.write_bytes(attach.content)
-def baml_load_excel(fname):
+def load_excel_old(fname):
wb = open_workbook(fname)
s = wb.sheet_by_index(0)
headers = s.row_values(6, 0)
@@ -108,6 +108,36 @@ def baml_load_excel(fname):
return df
+def load_excel(fname):
+ wb = open_workbook(fname)
+ s = wb.sheet_by_index(0)
+ headers = s.row_values(13, 0)
+ i = 19
+ rows = []
+ while s.cell(i, 0).ctype != xlrd.XL_CELL_DATE:
+ if s.cell(i, 0).value == "" or s.cell(i, 0).value.startswith("Credit"):
+ i += 1
+ continue
+ r = []
+ for e in s.row_slice(i, 0):
+ if e.ctype == xlrd.XL_CELL_DATE:
+ r.append(pd.Timestamp(*xldate_as_tuple(e.value, wb.datemode)))
+ else:
+ r.append(e.value)
+ rows.append(r)
+ i += 1
+ df = pd.DataFrame.from_records(rows, columns=headers)
+ df = df.rename(
+ columns={
+ "Contract ID ": "Trade ID",
+ "Market Value Amount": "MTM(USD)",
+ "Trade Date ": "Trade Date",
+ }
+ )
+ df["MTM(USD)"] *= -1.0
+ return df
+
+
def collateral(d, dawn_trades, *args):
REPORTS_DIR = DAILY_DIR / "BAML_ISDA_reports"
try:
@@ -120,12 +150,9 @@ def collateral(d, dawn_trades, *args):
collateral = float(df.Notional)
except TypeError:
collateral = df.Notional.sum()
- d -= BDay()
- fname = REPORTS_DIR / f"Interest Rates Trade Summary_{d:%d-%b-%Y}.xls"
- # TODO: make more robust
- df = baml_load_excel(fname)
+ fname = next(REPORTS_DIR.glob(f"301__LMCG_INVESTMENTSLP_CSA_{d:%m%d%Y}_*"))
+ df = load_excel(fname)
df = df[["Trade ID", "MTM(USD)"]]
- df["Trade ID"] = df["Trade ID"].astype("str")
df = df.merge(dawn_trades, how="left", left_on="Trade ID", right_on="cpty_id")
missing_ids = df.loc[df.cpty_id.isnull(), "Trade ID"]
if not missing_ids.empty:
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py
index 619d2700..129a3961 100644
--- a/python/external_deriv_marks.py
+++ b/python/external_deriv_marks.py
@@ -2,7 +2,7 @@ import datetime
import pandas as pd
import re
from env import DAILY_DIR
-from collateral.baml_isda import baml_load_excel
+from collateral.baml_isda import load_excel
def gs_navs(date: datetime.date = None):
@@ -15,11 +15,19 @@ def gs_navs(date: datetime.date = None):
continue
df = df.dropna(subset=["GS Entity"])
df["Trade Date"] = pd.to_datetime(df["Trade Date"])
- df = df[["Trade Date", "Buy/Sell", "Notional (USD)", "NPV (USD)"]]
- df.columns = ["trade_date", "buy/sell", "notional", "nav"]
+ df = df[
+ [
+ "Trade Date",
+ "Buy/Sell",
+ "Notional (USD)",
+ "NPV (USD)",
+ "Initial Margin Required",
+ ]
+ ]
+ df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"]
name = fname.name.replace("9972734", "")
if m := re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", name):
- date_string, = m.groups()
+ (date_string,) = m.groups()
date = datetime.datetime.strptime(date_string, "%d_%b_%Y")
d[date] = df
df = pd.concat(d)
@@ -35,11 +43,18 @@ def ms_navs(date: datetime.date = None):
df = pd.read_excel(fname, index_col="trade_id")
df.trade_date = pd.to_datetime(df.trade_date)
df = df[
- ["trade_date", "pay_rec", "notional_in_trade_ccy", "exposure_in_rpt_ccy"]
+ [
+ "trade_date",
+ "pay_rec",
+ "notional_in_trade_ccy",
+ "exposure_in_rpt_ccy",
+ "collat_req_in_rpt_ccy",
+ ]
]
- df.columns = ["trade_date", "buy/sell", "notional", "nav"]
+ df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"]
+ df.ia = df.nav - df.ia
if m := re.match(r"[^\d]*(\d{8})", fname.name):
- date_string, = m.groups()
+ (date_string,) = m.groups()
date = datetime.datetime.strptime(date_string, "%Y%m%d")
d[date] = df
return pd.concat(d)
@@ -58,8 +73,10 @@ def citi_navs(date: datetime.date = None):
df = df.dropna(subset=["Operations File"]).set_index(
["Value Date", "Operations File"]
)
- df = df[["Trade Date", "Party Position", "Notional", "Market Value"]]
- df.columns = ["trade_date", "buy/sell", "notional", "nav"]
+ df = df[
+ ["Trade Date", "Party Position", "Notional", "Market Value", "BasicAmt"]
+ ]
+ df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"]
d[date_parsed] = df
# there can be multiple files per day, we take the latest one
df = (
@@ -75,37 +92,29 @@ def citi_navs(date: datetime.date = None):
def baml_navs(date: datetime.date = None):
d = {}
- glob_str = date.strftime("%d-%b-%Y") if date else "*"
+ glob_str = date.strftime("%m%d%Y") if date else "*"
for fname in (DAILY_DIR / "BAML_ISDA_reports").glob(
- f"Interest Rates Trade Summary_{glob_str}.xls"
+ f"301__LMCG_INVESTMENTSLP_CSA_{glob_str}_*.xls"
):
- date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y")
- df = baml_load_excel(fname)
+ date = datetime.datetime.strptime(fname.stem.split("_")[5], "%m%d%Y")
+ df = load_excel(fname)
df = df.set_index("Trade ID")
- df = df[["Trade Date", "Flow Direction", "Notional", "MTM(USD)"]]
- df.columns = ["trade_date", "buy/sell", "notional", "nav"]
+ df = df[
+ [
+ "Trade Date",
+ "Buy/Sell",
+ "Notional 1",
+ "MTM(USD)",
+ "Cpty Independent Amount",
+ ]
+ ]
+ df.columns = ["trade_date", "buy/sell", "notional", "nav", "ia"]
d[date] = df
return pd.concat(d)
-def bnp_navs(date: datetime.date = None):
- d = {}
- date_str = date.strftime("%Y%m%d") if date else ""
- for fname in (DAILY_DIR / "BNP_reports").glob(f"Exposure*{date_str}.XLS"):
- try:
- df = pd.read_excel(fname, skiprows=7)
- except ValueError:
- continue
- df["Trade Ref"] = df["Trade Ref"].str.replace("MBO-", "")
- df = df.set_index("Trade Ref")
- df["Trade Date"] = pd.to_datetime(df["Trade Date"], dayfirst=True)
- df = df[["Trade Date", "Buy/Sell", "Notional 1", "Exposure Amount (Agmt Ccy)"]]
- df.columns = ["trade_date", "buy/sell", "notional", "nav"]
- d[datetime.datetime.strptime(fname.stem[-8:], "%Y%m%d").date()] = df
- df = pd.concat(d)
- # nav is from BNP's point of view
- df.nav *= -1.0
- return df
+def cs_navs(date: datetime.date = None):
+ pass
# def bnp_navs_old(date: datetime.date = None):
@@ -124,6 +133,7 @@ def bnp_navs(date: datetime.date = None):
# df = pd.concat(d)
# return df
+
if __name__ == "__main__":
import argparse
import logging
@@ -161,9 +171,9 @@ if __name__ == "__main__":
logger.debug(df)
with dbconn("dawndb") as conn:
with conn.cursor() as c:
- for k, v in df[["nav"]].iterrows():
+ for k, v in df[["nav", "ia"]].iterrows():
c.execute(
"INSERT INTO external_marks_deriv "
- "VALUES(%s, %s, %s, %s) ON CONFLICT DO NOTHING",
- (*k, float(v), cp),
+ "VALUES(%s, %s, %s, %s, %s) ON CONFLICT DO NOTHING",
+ (*k, float(v.nav), cp, float(v.ia)),
)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-23 19:49:25 +0000