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
|
from serenitas.utils.env import DAILY_DIR
from serenitas.utils.remote import SftpClient
import datetime
import pytz
from stat import S_ISREG
import csv
from process_queue import rename_keys
from serenitas.utils.db import dbconn
fund_dictionary = {"SERENITAS_CGMF": "SERCGMAST", "BOWDOINST": "BOWDST"}
fcm_dictionary = {"Bank of America, N.A.": "BAML", "Goldman Sachs": "GS"}
counterparty_dictionary = {"BNP PARIB.": "BNPBNY"}
def download_files(date):
downloaded_files = []
sftp = SftpClient.from_creds("bbg")
dst = DAILY_DIR / f"{date:%Y-%m-%d}" / "bbg_tickets"
if not dst.exists():
dst.mkdir()
est = pytz.timezone("US/Eastern")
src = ""
for f in sftp.client.listdir_iter():
if S_ISREG(f.st_mode):
local_file = dst / f.filename
modification_time = datetime.datetime.fromtimestamp(
f.st_mtime, tz=datetime.timezone.utc
).astimezone(est)
if notlocal_file.exists() and (modification_time.date() == date):
sftp.client.get(f"{src}/{f.filename}", localpath=local_file)
downloaded_files.append(local_file)
return downloaded_files
def get_bbg_data(bbg_id, trade_date, conn):
try:
_, indextype, _, series, tenor = bbg_id.split()
except ValueError:
return "not a valid bloomberg description", 400
indextype = indextype[:2]
tenor = tenor[:-1] + "yr"
series = int(series[1:])
sql_str = (
"SELECT redindexcode, maturity, coupon "
"FROM index_desc "
"WHERE index=%s and series=%s and tenor=%s "
" and lastdate >=%s ORDER BY version"
)
with conn.cursor() as c:
c.execute(sql_str, (indextype, series, tenor, trade_date))
redcode, maturity, coupon = c.fetchone()
return str(maturity), redcode, coupon
def cdx_booking_process(path):
conn = dbconn("dawndb")
reader = csv.DictReader(open(path))
trades = []
for csv_dict in reader:
rename_keys(
csv_dict,
{
"Curncy": "currency",
# 'Net': 'upfront'
},
)
csv_dict["upfront"] = float(csv_dict["Net"])
csv_dict["security_desc"] = csv_dict["Security"].replace(" PRC", "")
csv_dict["traded_level"] = float(csv_dict["Price (Dec)"])
csv_dict["notional"] = float(csv_dict["Quantity"])
csv_dict["trade_date"] = datetime.datetime.strptime(
csv_dict["Trade Dt"], "%m/%d/%Y"
)
csv_dict["upfront_settle_date"] = datetime.datetime.strptime(
csv_dict["SetDt"], "%m/%d/%Y"
)
csv_dict["protection"] = "Buyer" if csv_dict["Side"] == "B" else "Seller"
csv_dict["account_code"] = fcm_dictionary[csv_dict["Client FCM"]]
csv_dict["fund"] = fund_dictionary[csv_dict["Account"]]
csv_dict["action"] = "NEW"
csv_dict["folder"] = "*"
csv_dict["cp_code"] = counterparty_dictionary[csv_dict["BrkrName"]]
csv_dict["payment_rolldate"] = "Following"
csv_dict["day_count"] = "ACT/360"
csv_dict["frequency"] = 4
csv_dict["swap_type"] = "CD_INDEX"
csv_dict["portfolio"] = "UNALLOCATED"
csv_dict["clearing_facility"] = "ICE-CREDIT"
(
csv_dict["maturity"],
csv_dict["security_id"],
csv_dict["fixed_rate"],
) = get_bbg_data(csv_dict["security_desc"], csv_dict["trade_date"], conn)
csv_dict["effective_date"] = datetime.date(2021, 12, 20)
trades.append(csv_dict)
with conn.cursor() as c:
c.executemany(
"INSERT INTO cds (action, folder, cp_code, trade_date, effective_date, maturity, currency, payment_rolldate, notional, fixed_rate, day_count, frequency, protection, security_id, security_desc, upfront, upfront_settle_date, swap_type, clearing_facility, portfolio, fund) "
"VALUES (%(action)s, %(folder)s, %(cp_code)s, %(trade_date)s, %(effective_date)s, %(maturity)s, %(currency)s, %(payment_rolldate)s, %(notional)s, %(fixed_rate)s, %(day_count)s, %(frequency)s, %(protection)s, %(security_id)s, %(security_desc)s, %(upfront)s, %(upfront_settle_date)s, %(swap_type)s, %(clearing_facility)s, %(portfolio)s, %(fund)s);",
trades,
)
conn.commit()
def book_trades(date):
downloaded_files = download_files(date)
if downloaded_files:
for f in downloaded_files:
if "CDX" in f.name:
cdx_booking_process(f)
if __name__ == "__main__":
book_trades(datetime.date(2022, 2, 7))
|
