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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
import pandas as pd
from . import DAILY_DIR, SftpClient2
from .common import compare_notionals, STRATEGY_CASH_MAPPING
def download_files(*args, **kwargs):
sftp = SftpClient2.from_creds("wells")
sftp.download_files("/RECEIVE/339425_DATO2", DAILY_DIR / "Wells_reports")
def collateral(d, positions, *, engine, **kwargs):
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.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"})
df_margin = pd.read_csv(
DAILY_DIR
/ "Wells_reports"
/ f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv",
)
table_cols = [
"VALUE_DATE",
"CURRENCY_NAME",
"BEGINNING_BALANCE",
"CDS_INITIAL_COUPON",
"CDS_RESET_TO_PAR",
"PAI",
"CLEARING_FEES",
"TRANSACTION_FEES",
"NET_DEP_WITHDRAW",
"ENDING_BALANCE",
"ACCOUNT_VALUE_MARKET",
"REALIZED_PNL",
"CURRENT_IM",
"CURRENT_EXCESS_DEFICIT",
]
update_cols = [c.lower() for c in table_cols[2:]]
if "VALUE_DATE" in df_margin:
df_margin.VALUE_DATE = pd.to_datetime(df_margin.VALUE_DATE)
df_margin["CURRENT_EXCESS_DEFICIT"] += df_margin["PENDING_AMOUNT"]
else:
df_margin["Value Date"] = pd.to_datetime(df_margin["Value Date"])
table_cols = [c.replace("_", " ").title() for c in table_cols]
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})"
f"ON CONFLICT (date, account, currency) DO UPDATE SET "
f"({','.join(update_cols)}) = ({','.join(['EXCLUDED.'+c for c in update_cols])})",
list(df_margin[table_cols].itertuples(index=False)),
)
df["date"] = d
return df.set_index("Strategy")
|
