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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
import pandas as pd
import socket
from . import DAILY_DIR
from .common import compare_notionals
from paramiko import Transport, SFTPClient
from sqlalchemy.exc import IntegrityError
from ssh2.session import Session
from ssh2.sftp import LIBSSH2_FXF_READ, LIBSSH2_SFTP_S_IRUSR, LIBSSH2_SFTP_S_IFREG
def get_wells_sftp_client():
transport = Transport(("axst.wellsfargo.com", 10022))
transport.connect(username="LMCHsWC6EP", password="HI2s2h19+")
return SFTPClient.from_transport(transport)
def get_wells_sftp_client2():
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect(("axst.wellsfargo.com", 10022))
session = Session()
session.handshake(sock)
session.userauth_password("LMCHsWC6EP", "HI2s2h19+")
sftp = session.sftp_init()
return sftp
def download_files2(d=None):
DATA_DIR = DAILY_DIR / "Wells_reports"
sftp = get_wells_sftp_client()
base_dir = "/RECEIVE/339425_DATO2"
for f in sftp.listdir(base_dir):
if not (DATA_DIR / f).exists():
sftp.get(f"{base_dir}/{f}", localpath=DATA_DIR / f)
def download_files(d=None):
DATA_DIR = DAILY_DIR / "Wells_reports"
sftp = get_wells_sftp_client2()
files = []
with sftp.opendir("/RECEIVE/339425_DATO2") as fh:
for size, buf, attrs in fh.readdir():
if attrs.permissions & LIBSSH2_SFTP_S_IFREG:
files.append(buf.decode())
for f in files:
local_file = DATA_DIR / f
if not local_file.exists():
with sftp.open(
f"/RECEIVE/339425_DATO2/{f}", LIBSSH2_FXF_READ, LIBSSH2_SFTP_S_IRUSR
) as remote_handle, local_file.open("wb") as local_handle:
for size, data in remote_handle:
local_handle.write(data)
def collateral(d, positions, engine):
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(
{
"HEDGE_MBS": "MBSCDSCSH",
"SER_ITRXCURVE": "SER_ITRXCVCSH",
"SER_IGCURVE": "SER_IGCVECSH",
"HYOPTDEL": "COCSH",
"IGOPTDEL": "COCSH",
"IGINX": "TCSH",
"HYINX": "TCSH",
"XOINX": "TCSH",
}
)
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",
usecols=["CURRENCY_NAME", "CURRENT_IM", "VALUE_DATE"],
parse_dates=["VALUE_DATE"],
index_col=["CURRENCY_NAME"],
)
try:
engine.execute(
"INSERT INTO fcm_im " "VALUES(%s, 'WFNSCLMFCM', 'USD', %s)",
df_margin.loc["ZZZZZ", ["VALUE_DATE", "CURRENT_IM"]].tolist(),
)
except IntegrityError:
pass
df["date"] = d
return df.set_index("Strategy")
|
