1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
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")
|
