1 files changed, 138 insertions, 0 deletions
diff --git a/python/collateral/wells.py b/python/collateral/wells.py
new file mode 100644
index 00000000..95fbfe50
--- /dev/null
+++ b/python/collateral/wells.py
@@ -0,0 +1,138 @@
+import pandas as pd
+import socket
+from . import DAILY_DIR
+from .common import compare_notionals
+from paramiko import Transport, SFTPClient
+from sqlalchemy.exc import IntegrityError
+from ssh2.session import Session
+from ssh2.sftp import LIBSSH2_FXF_READ, LIBSSH2_SFTP_S_IRUSR, LIBSSH2_SFTP_S_IFREG
+
+
+def get_wells_sftp_client():
+    transport = Transport(("axst.wellsfargo.com", 10022))
+    transport.connect(username="LMCHsWC6EP", password="HI2s2h19+")
+    return SFTPClient.from_transport(transport)
+
+
+def get_wells_sftp_client2():
+    sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
+    sock.connect(("axst.wellsfargo.com", 10022))
+    session = Session()
+    session.handshake(sock)
+    session.userauth_password("LMCHsWC6EP", "HI2s2h19+")
+    sftp = session.sftp_init()
+    return sftp
+
+
+def download_files2(d=None):
+    DATA_DIR = DAILY_DIR / "Wells_reports"
+    sftp = get_wells_sftp_client()
+    base_dir = "/RECEIVE/339425_DATO2"
+    for f in sftp.listdir(base_dir):
+        if not (DATA_DIR / f).exists():
+            sftp.get(f"{base_dir}/{f}", localpath=DATA_DIR / f)
+
+
+def download_files(d=None):
+    DATA_DIR = DAILY_DIR / "Wells_reports"
+    sftp = get_wells_sftp_client2()
+    files = []
+    with sftp.opendir("/RECEIVE/339425_DATO2") as fh:
+        for size, buf, attrs in fh.readdir():
+            if attrs.permissions & LIBSSH2_SFTP_S_IFREG:
+                files.append(buf.decode())
+    for f in files:
+        local_file = DATA_DIR / f
+        if not local_file.exists():
+            with sftp.open(
+                f"/RECEIVE/339425_DATO2/{f}", LIBSSH2_FXF_READ, LIBSSH2_SFTP_S_IRUSR
+            ) as remote_handle, local_file.open("wb") as local_handle:
+                for size, data in remote_handle:
+                    local_handle.write(data)
+
+
+def collateral(d, positions, engine):
+    account = "A5882186"
+    file_name = (
+        DAILY_DIR
+        / "Wells_reports"
+        / f"OTC_CDS_Position_Activity_{account}_{d:%m%d%Y}.csv"
+    )
+    try:
+        df = pd.read_csv(
+            file_name,
+            usecols=[
+                "TENOR",
+                "MARKET_VALUE_NPV",
+                "PAIR_CLIP",
+                "BUY_SELL",
+                "NOTIONAL",
+                "MATURITY_DATE",
+                "TRADE_PRICE",
+            ],
+            parse_dates=["MATURITY_DATE"],
+            index_col=["PAIR_CLIP", "MATURITY_DATE"],
+        )
+    except ValueError:
+        # backpopulated files have a different format...
+        df = pd.read_csv(
+            file_name,
+            usecols=[
+                "Tenor",
+                "NPV",
+                "Reference_Entity_ID",
+                "Fixed_Rate_Notional_Buy",
+                "Amount",
+                "Scheduled_Termination_Date",
+            ],
+            parse_dates=["Scheduled_Termination_Date"],
+            index_col=["Reference_Entity_ID", "Scheduled_Termination_Date"],
+        )
+        df = df.rename(
+            columns={"Tenor": "TENOR", "NPV": "MARKET_VALUE_NPV", "Amount": "NOTIONAL"}
+        )
+        df["BUY_SELL"] = 1
+        df.loc[df.Fixed_Rate_Notional_Buy.isnull(), "BUY_SELL"] = 2
+        del df["Fixed_Rate_Notional_Buy"]
+    df = df[df.TRADE_PRICE != 0.0]
+    del df["TRADE_PRICE"]
+    df["NOTIONAL"] = df.NOTIONAL.where(df.BUY_SELL == 1, -df.NOTIONAL).astype("float")
+    df["DIRTYUPFRONT"] = df.MARKET_VALUE_NPV / df.NOTIONAL
+    df.index.names = ["security_id", "maturity"]
+    compare_notionals(df, positions, "Wells")
+    positions = positions.join(df, how="left")
+    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
+        / "Wells_reports"
+        / f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv",
+        usecols=["CURRENCY_NAME", "CURRENT_IM", "VALUE_DATE"],
+        parse_dates=["VALUE_DATE"],
+        index_col=["CURRENCY_NAME"],
+    )
+    try:
+        engine.execute(
+            "INSERT INTO fcm_im " "VALUES(%s, 'WFNSCLMFCM', 'USD', %s)",
+            df_margin.loc["ZZZZZ", ["VALUE_DATE", "CURRENT_IM"]].tolist(),
+        )
+    except IntegrityError:
+        pass
+    df["date"] = d
+    return df.set_index("Strategy")