aboutsummaryrefslogtreecommitdiffstats
path: root/python/collateral
diff options
context:
space:
mode:
Diffstat (limited to 'python/collateral')
-rw-r--r--python/collateral/__main__.py8
-rw-r--r--python/collateral/baml_fcm.py28
-rw-r--r--python/collateral/citi.py25
-rw-r--r--python/collateral/wells.py14
4 files changed, 44 insertions, 31 deletions
diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py
index 3d234381..5f7311da 100644
--- a/python/collateral/__main__.py
+++ b/python/collateral/__main__.py
@@ -113,17 +113,13 @@ df.strategy = df.strategy.str.replace("^(M_|SER_)?", "", 1, regex=True)
df["fund"] = df.fund.map(fund_mapping)
df = df[["date", "broker", "strategy", "Amount", "Currency", "fund"]]
-conn = dbconn("dawndb")
sql_str = (
"INSERT INTO strategy_im VALUES(%s, %s, %s, %s, %s, %s) "
"ON CONFLICT (date, strategy, broker, fund) DO UPDATE "
"SET currency=EXCLUDED.currency, amount=EXCLUDED.amount"
)
-with conn.cursor() as c:
- for t in df.itertuples(index=False):
- c.execute(sql_str, t)
-conn.commit()
-conn.close()
+with dawn_engine.connect() as conn:
+ conn.execute(sql_str, list(df.itertuples(index=False)))
if args.send_email:
send_email(workdate, df[df.fund == "SERCGMAST"].drop("fund", axis=1))
diff --git a/python/collateral/baml_fcm.py b/python/collateral/baml_fcm.py
index ff80f480..c7c892a4 100644
--- a/python/collateral/baml_fcm.py
+++ b/python/collateral/baml_fcm.py
@@ -4,14 +4,17 @@ import pandas as pd
from sqlalchemy.exc import IntegrityError
-def download_files(*args, **kwargs):
+def download_files(*args, fund="Serenitas", **kwargs):
+ if fund != "Serenitas":
+ return
sftp = SftpClient.from_creds("baml_fcm")
- sftp.download_files("outgoing", DAILY_DIR / "BAML_reports")
+ sftp.download_files("outgoing", DAILY_DIR / fund / "BAML_reports")
-def collateral(d, positions, *, engine, **kwargs):
+def collateral(d, positions, *, engine, fund="Serenitas", **kwargs):
df = pd.read_csv(
DAILY_DIR
+ / fund
/ "BAML_reports"
/ f"OTC_Open_Positions_-_Credit_-_LMCG_{d:%Y%m%d}.CSV",
usecols=[
@@ -75,13 +78,19 @@ def collateral(d, positions, *, engine, **kwargs):
"Margin Excess/Deficit",
]
df_margin = pd.read_csv(
- DAILY_DIR / "BAML_reports" / f"OTC_Moneyline_{d:%Y%m%d}.CSV",
+ DAILY_DIR / fund / "BAML_reports" / f"OTC_Moneyline_{d:%Y%m%d}.CSV",
usecols=col_names,
parse_dates=["Statement Date"],
index_col=["Statement Date"],
)
df_margin.AT = df_margin.AT.replace(
- {"Q4": "EUR", "F4": "EUR", "Q1": "USD", "F2": "USD", "9Z": "ZZZZZ",}
+ {
+ "Q4": "EUR",
+ "F4": "EUR",
+ "Q1": "USD",
+ "F2": "USD",
+ "9Z": "ZZZZZ",
+ }
)
df_margin = df_margin.set_index("AT", append=True)
df_margin = df_margin.groupby(level=(0, 1)).sum()
@@ -89,10 +98,11 @@ def collateral(d, positions, *, engine, **kwargs):
col_names.pop(2)
try:
place_holders = ",".join(["%s"] * (len(col_names) - 1))
- engine.execute(
- f"INSERT INTO fcm_moneyline VALUES(%s, 'V0NSCLMFCM', {place_holders})",
- list(df_margin[col_names].itertuples(index=False)),
- )
+ with engine.connect() as conn:
+ conn.execute(
+ f"INSERT INTO fcm_moneyline VALUES(%s, 'V0NSCLMFCM', {place_holders})",
+ list(df_margin[col_names].itertuples(index=False)),
+ )
except IntegrityError:
pass
df["date"] = d
diff --git a/python/collateral/citi.py b/python/collateral/citi.py
index e9acfa88..7433547a 100644
--- a/python/collateral/citi.py
+++ b/python/collateral/citi.py
@@ -1,12 +1,13 @@
+import datetime
import pandas as pd
from . import DAILY_DIR
from .common import load_pdf, get_col, next_business_day, parse_num
-def load_file(d):
+def load_file(d, fund="Serenitas"):
try:
fname = next(
- (DAILY_DIR / "CITI_reports").glob(
+ (DAILY_DIR / fund / "CITI_reports").glob(
f"262966_Portfolio_{d.strftime('%Y%m%d')}*"
)
)
@@ -15,11 +16,13 @@ def load_file(d):
return pd.read_excel(fname, skiprows=6, skipfooter=2)
-def download_files(em, count=20, **kwargs):
+def download_files(em, count=20, fund="Serenitas", **kwargs):
+ if fund != "Serenitas":
+ return
emails = em.get_msgs(
path=["NYops", "Margin Calls Citi"], count=count, subject__startswith="262966"
)
- DATA_DIR = DAILY_DIR / "CITI_reports"
+ DATA_DIR = DAILY_DIR / fund / "CITI_reports"
for msg in emails:
for attach in msg.attachments:
fname = attach.name
@@ -38,15 +41,15 @@ def get_df(l, col1, col2, col3):
return df
-def get_total_collateral(d):
+def get_total_collateral(d: datetime.date, fund="Serenitas"):
try:
fname = next(
- (DAILY_DIR / "CITI_reports").glob(
+ (DAILY_DIR / fund / "CITI_reports").glob(
f"262966_MarginNotice_{d.strftime('%Y%m%d')}_*.pdf"
)
)
except StopIteration:
- raise FileNotFoundError(f"CITI file not found for date {d.date()}")
+ raise FileNotFoundError(f"CITI file not found for date {d}")
l = load_pdf(fname)
col1 = (370, 500, 70, 250)
col2 = (370, 500, 300, 530)
@@ -68,9 +71,11 @@ def get_total_collateral(d):
)
-def collateral(d, dawn_trades, **kwargs):
- df = load_file(next_business_day(d))
- collat = sum(get_total_collateral(d)[:2])
+def collateral(d, dawn_trades, fund="Serenitas", **kwargs):
+ if fund != "Serenitas":
+ raise ValueError
+ df = load_file(next_business_day(d), fund)
+ collat = sum(get_total_collateral(d, fund)[:2])
df = df[["Operations File", "Market Value", "BasicAmt"]].dropna(
subset=["Operations File"]
) # missing Operations File means assignment usually
diff --git a/python/collateral/wells.py b/python/collateral/wells.py
index 028b5683..64917c10 100644
--- a/python/collateral/wells.py
+++ b/python/collateral/wells.py
@@ -3,15 +3,18 @@ from . import DAILY_DIR, SftpClient2
from .common import compare_notionals, STRATEGY_CASH_MAPPING
-def download_files(*args, **kwargs):
+def download_files(*args, fund="Serenitas", **kwargs):
+ if fund != "Serenitas":
+ return
sftp = SftpClient2.from_creds("wells")
- sftp.download_files("/RECEIVE/339425_DATO2", DAILY_DIR / "Wells_reports")
+ sftp.download_files("/RECEIVE/339425_DATO2", DAILY_DIR / fund / "Wells_reports")
-def collateral(d, positions, *, engine, **kwargs):
+def collateral(d, positions, *, engine, fund="Serenitas", **kwargs):
account = "A5882186"
file_name = (
DAILY_DIR
+ / fund
/ "Wells_reports"
/ f"OTC_CDS_Position_Activity_{account}_{d:%m%d%Y}.csv"
)
@@ -77,6 +80,7 @@ def collateral(d, positions, *, engine, **kwargs):
df = df.rename(columns={"folder": "Strategy", "currency": "Currency"})
df_margin = pd.read_csv(
DAILY_DIR
+ / fund
/ "Wells_reports"
/ f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv",
)
@@ -106,9 +110,7 @@ def collateral(d, positions, *, engine, **kwargs):
table_cols[-2] = "Current IM"
df_margin["Current Excess Deficit"] += df_margin["Pending Amount"]
place_holders = ",".join(["%s"] * (len(table_cols) - 1))
- # pomme = (f"INSERT INTO fcm_moneyline VALUES(%s, 'WFNSCLMFCM', {place_holders})"
- # f"ON CONFLICT (date, account, currency) DO UPDATE "
- # f"({','.join(update_cols)}) = ({','.join(['EXCLUDED.'+c for c in update_cols])}))
+
with engine.connect() as conn:
conn.execute(
f"INSERT INTO fcm_moneyline VALUES(%s, 'WFNSCLMFCM', {place_holders})"
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-24 13:28:57 +0000