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
|
from serenitas.utils.env import DAILY_DIR
from serenitas.utils.remote import SftpClient
from zoneinfo import ZoneInfo
import datetime
import csv
from trade_dataclasses import CDSDeal, BondDeal
from decimal import Decimal
from serenitas.utils.db import dbconn
from lru import LRU
_funds = {"SERENITAS_CGMF": "SERCGMAST", "BOWDOINST": "BOWDST"}
_fcms = {"Bank of America, N.A.": "BAML", "Goldman Sachs": "GS"}
_cdx_cp = {
"MSDU": "MSCSNY",
"GSMX": "GOLDNY",
"JPGP": "JPCBNY",
"JFF": "JEFF",
"BMLE": "BAMSNY",
"BARX": "BARCNY",
"CSDA": "CSFBBO",
"EBNP": "BNPBNY",
"WFCD": "WELFEI",
"BSEF": "BSEONY",
"JPOS": "JPCBNY",
"CGCI": "CITINY",
}
_bond_cp = {
"CG": "CITINY",
"WFBS": "WELFEI",
"MZZ": "MIZUNY",
"BABS": "BAML",
"PTRU": "PERFCH",
"BARC": "BARCNY",
"MS": "MORGNY",
"BA": "BAML",
"FB": "CSUINY",
"INTC": "STONEX",
"SOCG": "SGSANY",
"NOM": "NOMINY",
"JP": "JPCBNY",
"BTIG": "BTIG",
}
def get_indic_data(conn, redcode, tenor):
sql_str = (
"SELECT maturity, coupon "
"FROM index_desc "
"WHERE tenor=%s AND redindexcode=%s "
)
with conn.cursor() as c:
c.execute(sql_str, (redcode, tenor))
return c.fetchone()
def cdx_booking_process(path, conn):
with open(path) as fh:
reader = csv.DictReader(fh)
for line in reader:
tenor = line["Security"].rsplit(" ", 1)[-1].lower() + "r"
maturity, coupon = get_indic_data(conn, tenor, line["Red Code"])
trade = CDSDeal(
fund=_funds[line["Account"]],
folder="*",
portfolio="UNALLOCATED",
security_id=line["Red Code"],
security_desc=line["Security"].removesuffix(" PRC"),
traded_level=Decimal(line["Price (Dec)"]),
notional=line["Quantity"],
fixed_rate=coupon * 0.01,
trade_date=datetime.datetime.strptime(
line["Trade Dt"], "%m/%d/%Y"
).date(),
maturity=maturity,
currency=line["Curncy"],
protection="Buyer" if line["Side"] == "B" else "Seller",
upfront=line["Principal"],
cp_code=_cdx_cp[line["Brkr"]],
account_code=_fcms[line["Client FCM"]],
)
trade.stage()
CDSDeal.commit()
def bond_booking_process(path, conn):
df = pd.read_csv(path)
for row in df.itertuples():
trade = BondDeal(
faceamount=Decimal(line["Quantity"]),
price=Decimal(line["Price (Dec)"]),
cp_code=_bond_cp[line["Brkr"]],
cusip=line["Cusip"],
identifier=line["Cusip"],
trade_date=datetime.datetime.strptime(line["Trade Dt"], "%m/%d/%Y"),
settle_date=datetime.datetime.strptime(line["SetDt"], "%m/%d/%Y"),
portfolio="UNALLOCATED",
asset_class=None,
description=line["Security"].removesuffix(" Mtge"),
buysell=line["Side"] == "B",
)
trade.stage()
BondDeal.commit()
df.columns.index = "bbg_trade_id"
df.index = [get_bbg_id(path.name)]
def get_bbg_id(name):
return name.split("_", 1)[1]
if __name__ == "__main__":
from serenitas.utils.db import serenitas_pool, dawn_engine
conn = serenitas_pool.getconn()
# While True here
_cache = LRU(128)
d = datetime.date.today()
sftp = SftpClient.from_creds("bbg")
filters = [lambda f: S_ISREG(f.st_mode)]
for f in filter(lambda f: all(filt(f) for filt in filters), sftp.listdir_iter(src)):
if ("CDX" in f.name) or ("BOND" in f.name):
if get_bbg_id(f.name) in _cache.keys():
continue
else:
if "CDX" in f.name:
cds_booking_process(sftp.get(f), d, conn, dawn_engine)
elif "BOND" in f.name:
bond_booking_process(sftp.get(f), d, conn, dawn_engine)
_cache[get_bbg_id(f.name)] = None
|
