1 files changed, 86 insertions, 0 deletions
diff --git a/python/collateral/baml_fcm.py b/python/collateral/baml_fcm.py
new file mode 100644
index 00000000..a7c960ed
--- /dev/null
+++ b/python/collateral/baml_fcm.py
@@ -0,0 +1,86 @@
+from . import DAILY_DIR
+from .common import compare_notionals
+from paramiko import Transport, SFTPClient, RSAKey
+import os.path
+import pandas as pd
+from sqlalchemy.exc import IntegrityError
+
+
+def get_sftp_client():
+    transport = Transport(("ftps.b2b.ml.com", 22))
+    pkey = RSAKey.from_private_key_file(os.path.expanduser("~/.ssh/id_rsa_lmcg"))
+    transport.connect(username="lmcginvs", pkey=pkey)
+    return SFTPClient.from_transport(transport)
+
+
+def download_files(d=None):
+    DATA_DIR = DAILY_DIR / "BAML_reports"
+    sftp = get_sftp_client()
+    for f in sftp.listdir("outgoing"):
+        local_file = DATA_DIR / f
+        if not local_file.exists():
+            sftp.get(f"outgoing/{f}", localpath=DATA_DIR / f)
+
+
+def collateral(d, positions, engine):
+    df = pd.read_csv(
+        DAILY_DIR
+        / "BAML_reports"
+        / f"OTC_Open_Positions_-_Credit_-_LMCG_{d:%Y%m%d}.CSV",
+        usecols=[
+            "MTM",
+            "ACCRUEDCPN",
+            "VARMARGIN",
+            "REDCODE",
+            "NOTIONAL",
+            "EODSETTLEMENTPRICE",
+            "PERIOD",
+            "BUYSELL",
+        ],
+        index_col=["REDCODE"],
+    )
+    df.PERIOD = pd.to_datetime(df.PERIOD.astype("str") + "20")
+    df = df.set_index("PERIOD", append=True)
+    df = df[df.EODSETTLEMENTPRICE.notnull()]
+    df["NOTIONAL"] = df.NOTIONAL.where(df.BUYSELL == "Buy", -df.NOTIONAL).astype(
+        "float"
+    )
+    df["DIRTYUPFRONT"] = (df.MTM + df.ACCRUEDCPN) / df.NOTIONAL
+    df.index.names = ["security_id", "maturity"]
+    compare_notionals(df, positions, "BAML")
+    positions["dirtyupfront"] = df.reindex(positions.index)["DIRTYUPFRONT"]
+    positions["amount"] = positions["notional"] * positions["dirtyupfront"]
+    positions.folder = positions.folder.map(
+        {
+            "HEDGE_MBS": "MBSCDSCSH",
+            "SER_ITRXCURVE": "SER_ITRXCVCSH",
+            "SER_IGCURVE": "SER_IGCVECSH",
+            "HYOPTDEL": "COCSH",
+            "IGOPTDEL": "COCSH",
+            "IGINX": "TCSH",
+            "HYINX": "TCSH",
+        }
+    )
+    df = (
+        positions.groupby("folder")
+        .agg({"amount": "sum", "currency": "first"})
+        .reset_index("folder")
+    )
+    df.columns = ["Strategy", "Amount", "Currency"]
+    df_margin = pd.read_csv(
+        DAILY_DIR / "BAML_reports" / f"OTC_Moneyline_{d:%Y%m%d}.CSV",
+        usecols=["Statement Date", "AT CCY", "Initial Margin Requirement"],
+        parse_dates=["Statement Date"],
+    )
+    df_margin.columns = ["date", "currency", "amount"]
+    df_margin["account"] = "V0NSCLMFCM"
+    try:
+        engine.execute(
+            "INSERT INTO fcm_im "
+            "VALUES(%(date)s, %(account)s, %(currency)s, %(amount)s)",
+            df_margin.iloc[-1].to_dict(),
+        )
+    except IntegrityError:
+        pass
+    df["date"] = d
+    return df.set_index("Strategy")