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
87
88
89
90
91
92
93
94
95
96
97
98
99
|
from . import DAILY_DIR, SftpClient
from .common import compare_notionals, STRATEGY_CASH_MAPPING
import pandas as pd
from sqlalchemy.exc import IntegrityError
def download_files(*args, **kwargs):
sftp = SftpClient.from_creds("baml_fcm")
sftp.download_files("outgoing", DAILY_DIR / "BAML_reports")
def collateral(d, positions, *, engine, **kwargs):
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"]
# dropped old versions (trades should be offsetting
df = df.groupby(level=["security_id", "maturity"]).agg(
{"NOTIONAL": "sum", "DIRTYUPFRONT": "first"}
)
df = df[df.NOTIONAL != 0.0]
compare_notionals(df, positions, "BAML")
positions["dirtyupfront"] = df.reindex(positions.index)["DIRTYUPFRONT"]
positions["Amount"] = positions["notional"] * positions["dirtyupfront"]
positions.folder = positions.folder.replace(STRATEGY_CASH_MAPPING)
def aux(row):
if row.folder == "XCURVE":
return "SER_IGCVECSH" if row.currency == "USD" else "SER_ITRXCVCSH"
else:
return row.folder
positions.folder = positions.apply(aux, axis=1)
df = (
positions.groupby(["folder", "currency"])
.agg({"Amount": "sum"})
.reset_index(["folder", "currency"])
)
df = df.rename(columns={"folder": "Strategy", "currency": "Currency"})
col_names = [
"Statement Date",
"AT",
"AT CCY",
"Beginning Balance",
"CDS Initial Coupon",
"CDS Trade Variation",
"Price Aligned Interest",
"Clearing Fee",
"Commission",
"Cash Amounts",
"Ending Balance",
"Account Value at Market",
"Net P&L",
"Initial Margin Requirement",
"Margin Excess/Deficit",
]
df_margin = pd.read_csv(
DAILY_DIR / "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",}
)
df_margin = df_margin.set_index("AT", append=True)
df_margin = df_margin.groupby(level=(0, 1)).sum()
df_margin = df_margin.reset_index()
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)),
)
except IntegrityError:
pass
df["date"] = d
return df.set_index("Strategy")
|
