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
|
import datetime
import pandas as pd
from . import DAILY_DIR, SftpClient
from .common import compare_notionals, STRATEGY_CASH_MAPPING, next_business_day
def download_files(*args, **kwargs):
sftp = SftpClient.from_creds("gs")
sftp.download_files("outgoing", DAILY_DIR / "BowdSt" / "GS_fcm_reports")
def load_file(d: datetime.date):
try:
fname = next(
(DAILY_DIR / "BowdSt" / "GS_fcm_reports").glob(
f"GS-OTCSDIReporting-Open_Trades_Redcode-*-{d:%Y%m%d}-*.csv"
)
)
except StopIteration:
raise FileNotFoundError("GS fcm file not found for date {d}")
return pd.read_csv(
fname,
usecols=[
"Notional",
"Direction",
"NPV (local)",
"Maturity Date",
"COB Date",
"Red Code",
],
parse_dates=["COB Date", "Maturity Date"],
index_col=["Red Code", "Maturity Date"],
thousands=",",
)
def collateral(d: datetime.date, positions, *, engine, **kwargs):
df = load_file(next_business_day(d))
df.Notional = df.Notional.where(df.Direction == "Buy", -df.Notional)
df.index.names = ["security_id", "maturity"]
cob_date = df["COB Date"][0]
df = df.groupby(level=["security_id", "maturity"])[
["Notional", "NPV (local)"]
].sum()
df = df.rename(columns={"Notional": "NOTIONAL"})
compare_notionals(df, positions, "GS")
positions["Amount"] = df.reindex(positions.index)["NPV (local)"]
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
/ "BowdSt"
/ "GS_fcm_reports"
/ f"Account_Balances_and_Margin_Report_LMCG_51338_{d:%Y%m%d}.csv",
parse_dates=["COB Date"],
thousands=",",
)
df["date"] = cob_date
df_margin = df_margin.rename(
columns={
"Opening Balance (local)": "beginning_balance",
"Ending Balance (local)": "ending_balance",
"PAI (local)": "pai",
"Account Value (local)": "account_value_market",
"Initial Margin Requirement (local)": "current_im",
"Excess/Deficit (local)": "current_excess_deficit",
"Currency": "currency",
"GS Account Number": "account",
"COB Date": "date",
}
)
cols = [
"date",
"account",
"beginning_balance",
"ending_balance",
"pai",
"account_value_market",
"current_im",
"current_excess_deficit",
"currency",
]
place_holders = ",".join(["%s"] * len(cols))
engine.execute(
f"INSERT INTO fcm_moneyline({','.join(cols)}) VALUES({place_holders})",
list(df_margin[cols].itertuples(index=False)),
)
return df.set_index("Strategy")
|
