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
|
import datetime
import pandas as pd
from . import DAILY_DIR, SftpClient
from .common import compare_notionals, STRATEGY_CASH_MAPPING
def download_files(*args, **kwargs):
sftp = SftpClient.from_creds("gs")
sftp.download_files("outgoing", DAILY_DIR / "BowdSt" / "GS_fcm_reports")
def get_filename(d: datetime.date, name: str):
try:
fname = next(
(DAILY_DIR / "BowdSt" / "GS_fcm_reports").glob(
f"GS-*-{name}-*-{d:%Y%m%d}-*.csv"
)
)
except StopIteration:
raise FileNotFoundError(f"GS fcm file {name} not found for date {d}")
else:
return fname
def collateral(d: datetime.date, positions, *, engine, **kwargs):
df = pd.read_csv(
get_filename(d + datetime.timedelta(days=1), "Open_Trades_Redcode"),
usecols=[
"Notional",
"Direction",
"NPV (local)",
"Maturity Date",
"COB Date",
"Red Code",
],
parse_dates=["COB Date", "Maturity Date"],
index_col=["Red Code", "Maturity Date"],
thousands=",",
)
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["date"] = cob_date
df_margin = pd.read_csv(
get_filename(
d + datetime.timedelta(days=1), "Account_Balances_and_Margin_extended"
),
parse_dates=["COB Date"],
thousands=",",
)
col_mapping = {
"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",
"CCP Fees (local)": "clearing_fees",
"Commissions (local)": "transaction_fees",
}
df_margin = df_margin.rename(columns=col_mapping)
df_margin["current_im"] *= -1.0
df_margin["account"] = df_margin["account"].str.replace(" ", "")
cols = col_mapping.values()
df_margin_futures = pd.read_csv(
get_filename(d + datetime.timedelta(days=1), "Account_Balances_By_Currency"),
parse_dates=["COB Date"],
thousands=",",
)
col_mapping = {
"Beginning Account Balance (Local)": "beginning_balance",
"Ending Account Balance (Local)": "ending_balance",
"Initial Margin Requirement (Local)": "current_im",
"GS Account Number": "account",
"COB Date": "date",
"Excess/Deficit (Local)": "current_excess_deficit",
"Total Equity (Local)": "account_value_market",
"Currency": "currency",
"Gross Realized P&L (Local)": "realized_pnl",
"Commission & Fees (Local)": "transaction_fees",
}
df_margin_futures = df_margin_futures.rename(columns=col_mapping)
df_margin_futures["pai"] = 0.0
df_margin = pd.concat([df_margin, df_margin_futures])
place_holders = ",".join(["%s"] * len(cols))
engine.execute(
f"INSERT INTO fcm_moneyline({','.join(cols)}) VALUES({place_holders})"
"ON CONFLICT (date, account, currency) "
"DO NOTHING",
list(df_margin[cols].itertuples(index=False)),
)
return df.set_index("Strategy")
|
