aboutsummaryrefslogtreecommitdiffstats
path: root/python
diff options
context:
space:
mode:
Diffstat (limited to 'python')
-rw-r--r--python/insert_index.py127
-rw-r--r--python/insert_tranche_quotes.py29
2 files changed, 87 insertions, 69 deletions
diff --git a/python/insert_index.py b/python/insert_index.py
index 5fe52547..1223b0f0 100644
--- a/python/insert_index.py
+++ b/python/insert_index.py
@@ -1,79 +1,68 @@
-import psycopg2
import datetime
-import pdb
+from sqlalchemy import MetaData, create_engine, Table
-conn = psycopg2.connect(database="mlpdb")
-cursor = conn.cursor()
+engine = create_engine('postgresql://mlpdb_user:Serenitas1@debian/mlpdb')
+metadata = MetaData(bind = engine)
+index_desc = Table('index_desc', metadata, autoload=True)
+index_ins = index_desc.insert()
-# offset=182950
-# index = [[offset, 'EU', 19, psycopg2.Date(2016, 06, 20), 'Y3', 100, 0],
-# [offset, 'EU', 19, psycopg2.Date(2018, 06, 20), 'Y5', 100, 0],
-# [offset, 'EU', 19, psycopg2.Date(2020, 06, 20), 'Y7', 100, 0],
-# [offset, 'EU', 19, psycopg2.Date(2023, 06, 20), 'Y10', 100, 0]]
+offset=182970
+# index = [[offset, 'EU', 19, datetime.date(2016, 06, 20), 'Y3', 100, 0],
+# [offset, 'EU', 19, datetime.date(2018, 06, 20), 'Y5', 100, 0],
+# [offset, 'EU', 19, datetime.date(2020, 06, 20), 'Y7', 100, 0],
+# [offset, 'EU', 19, datetime.date(2023, 06, 20), 'Y10', 100, 0]]
-# index = [[offset, 'HY', 17, psycopg2.Date(2014, 12, 20), 'Y3', 96, 0.027075],
-# [offset, 'HY', 17, psycopg2.Date(2016, 12, 20), 'Y5', 96, 0.027075],
-# [offset, 'HY', 17, psycopg2.Date(2018, 12, 20), 'Y7', 96, 0.027075],
-# [offset+10, 'HY', 19, psycopg2.Date(2015, 12, 20), 'Y3', 100, 0],
-# [offset+10, 'HY', 19, psycopg2.Date(2017, 12, 20), 'Y5', 100, 0],
-# [offset+10, 'HY', 19, psycopg2.Date(2019, 12, 20), 'Y7', 100, 0],
-# [offset+20, 'IG', 19, psycopg2.Date(2015, 12, 20), 'Y3', 100, 0],
-# [offset+20, 'IG', 19, psycopg2.Date(2017, 12, 20), 'Y5', 100, 0],
-# [offset+20, 'IG', 19, psycopg2.Date(2019, 12, 20), 'Y7', 100, 0],
-# [offset+20, 'IG', 19, psycopg2.Date(2022, 12, 20), 'Y10', 100, 0],
-# [offset+30, 'HY', 15, psycopg2.Date(2015, 12, 20), 'Y3', 96, 0.026375],
-# [offset+30, 'HY', 15, psycopg2.Date(2013, 12, 20), 'Y5', 96, 0.026375],
-# [offset+30, 'HY', 15, psycopg2.Date(2013, 12, 20), 'Y7', 96, 0.026375],
+# index = [[offset, 'HY', 21, datetime.date(2016, 12, 20), 'Y3', 100, 0],
+# [offset, 'HY', 21, datetime.date(2018, 12, 20), 'Y5', 100, 0],
+# [offset, 'HY', 21, datetime.date(2020, 12, 20), 'Y7', 100, 0],
+# [offset+10, 'EU', 20, datetime.date(2016, 12, 20), 'Y3', 100, 0],
+# [offset+10, 'EU', 20, datetime.date(2018, 12, 20), 'Y5', 100, 0],
+# [offset+10, 'EU', 20, datetime.date(2020, 12, 20), 'Y7', 100, 0],
+# [offset+10, 'EU', 20, datetime.date(2023, 12, 20), 'Y10', 100, 0],
+# [offset+20, 'IG', 21, datetime.date(2016, 12, 20), 'Y3', 100, 0],
+# [offset+20, 'IG', 21, datetime.date(2018, 12, 20), 'Y5', 100, 0],
+# [offset+20, 'IG', 21, datetime.date(2020, 12, 20), 'Y7', 100, 0],
+# [offset+20, 'IG', 21, datetime.date(2023, 12, 20), 'Y10', 100, 0]
# ];
+for line in index:
+ with engine.begin() as conn:
+ conn.execute(index_ins, basketid = line[0],
+ index = line[1],
+ series = line[2],
+ maturity = line[3],
+ tenor = line[4],
+ indexfactor = line[5],
+ cumulativeloss = line[6])
-# for line in index:
-# cursor.execute("INSERT INTO index_desc VALUES({0})".format(",".join(["%s"] * 7)), line)
+tranche_desc = Table('tranche_desc', metadata, autoload = True)
+tranche_ins = tranche_desc.insert()
-# conn.commit()
-# cursor.close()
-# conn.close()
+igbasketid = 182990
+igattach = [0, 3, 7, 15, 100]
+igtenors = ['Y3', 'Y5', 'Y7', 'Y10']
+for tenor in igtenors:
+ for i, (attach, detach) in enumerate(zip(igattach[:-1], igattach[1:])):
+ with engine.begin() as conn:
+ conn.execute(tranche_ins,
+ trancheid = igbasketid+i,
+ basketid = igbasketid,
+ tenor = tenor,
+ attach=attach,
+ detach = detach)
-# index = [[offset, 'HY', 17, psycopg2.Date(2014, 12, 20), 'Y3', 96, 0.027075],
-# [offset, 'HY', 17, psycopg2.Date(2016, 12, 20), 'Y5', 96, 0.027075],
-# [offset, 'HY', 17, psycopg2.Date(2016, 12, 20), 'Y7', 96, 0.027075],
-# [offset+10, 'HY', 19, psycopg2.Date(2015, 12, 20), 'Y3', 100, 0],
-# [offset+10, 'HY', 19, psycopg2.Date(2017, 12, 20), 'Y5', 100, 0],
-# [offset+10, 'HY', 19, psycopg2.Date(2017, 12, 20), 'Y7', 100, 0],
-# [offset+20, 'IG', 19, psycopg2.Date(2017, 12, 20), 'Y3', 100, 0],
-# [offset+20, 'IG', 19, psycopg2.Date(2017, 12, 20), 'Y5', 100, 0],
-# [offset+20, 'IG', 19, psycopg2.Date(2019, 12, 20), 'Y7', 100, 0],
-# [offset+20, 'IG', 19, psycopg2.Date(2022, 12, 20), 'Y10', 100, 0],
-# [offset+30, 'HY', 15, psycopg2.Date(2015, 12, 20), 'Y3', 96, 0.026375],
-# [offset+30, 'HY', 15, psycopg2.Date(2013, 12, 20), 'Y5', 96, 0.026375],
-# [offset+30, 'HY', 15, psycopg2.Date(2013, 12, 20), 'Y7', 96, 0.026375],
-# ];
-
-
-# igbasketid = 182920
-# igattach = [0, 3, 7, 15, 100]
-# igtenors = ['Y3', 'Y5', 'Y7', 'Y10']
-# for tenor in igtenors:
-# for i, (attach, detach) in enumerate(zip(igattach[:-1], igattach[1:])):
-# tranche = [igbasketid+i, igbasketid, tenor, attach, detach]
-# cursor.execute("INSERT INTO tranche_desc VALUES(%s, %s, %s, %s, %s)", tranche)
-
-# hyattach = [0, 15, 25, 35, 100]
-# hytenors = ['Y3', 'Y5', 'Y7']
-# for basketid in [182900, 182930, 182910]:
-# for tenor in hytenors:
-# for i, (attach, detach) in enumerate(zip(hyattach[:-1], hyattach[1:])):
-# tranche = [basketid+i, basketid, tenor, attach, detach]
-# cursor.execute("INSERT INTO tranche_desc VALUES(%s, %s, %s, %s, %s)", tranche)
-
-euattach = [0, 3, 6, 9, 12, 22, 100]
-eutenors = ['Y3', 'Y5', 'Y7']
-eubasketid = 182950
-for tenor in eutenors:
- for i, (attach, detach) in enumerate(zip(euattach[:-1], euattach[1:])):
- tranche = [eubasketid+i, eubasketid, tenor, attach, detach]
- cursor.execute("INSERT INTO tranche_desc VALUES(%s, %s, %s, %s, %s)", tranche)
+hybasketid = 182970
+hyattach = [0, 15, 25, 35, 100]
+hytenors = ['Y3', 'Y5', 'Y7']
+for tenor in hytenors:
+ for i, (attach, detach) in enumerate(zip(hyattach[:-1], hyattach[1:])):
+ with engine.begin() as conn:
+ conn.execute(tranche_ins,
+ trancheid = hybasketid+i,
+ basketid = hybasketid,
+ tenor = tenor,
+ attach = attach,
+ detach = detach)
-conn.commit()
-cursor.close()
-conn.close()
+# euattach = [0, 3, 6, 9, 12, 22, 100]
+# eutenors = ['Y3', 'Y5', 'Y7']
diff --git a/python/insert_tranche_quotes.py b/python/insert_tranche_quotes.py
new file mode 100644
index 00000000..f1b8cfb2
--- /dev/null
+++ b/python/insert_tranche_quotes.py
@@ -0,0 +1,29 @@
+from sqlalchemy import Table, create_engine, MetaData
+import os
+import csv
+
+engine = create_engine('postgresql://mlpdb_user:Serenitas1@debian/mlpdb')
+metadata = MetaData(bind = engine)
+quotes = Table('quotes', metadata, autoload = True)
+ins = quotes.insert()
+
+root_dir = '/home/share/CorpCDOs'
+quotefiles = [f for f in os.listdir(os.path.join(root_dir, 'Scenarios', 'Calibration')) if 'tranches' in f]
+for quotefile in quotefiles:
+ with open(os.path.join(root_dir, 'Scenarios', 'Calibration', quotefile)) as fh:
+ quotedate = os.path.splitext(quotefile)[0].split("_")[-1]
+ reader = csv.DictReader(fh)
+ data = []
+ for i, csvdict in enumerate(reader):
+ d = {'quotedate' : quotedate,
+ 'refbasketprice': csvdict['bidRefPrice'],
+ 'tranchedelta': csvdict['bidDelta'],
+ 'quotesource' : csvdict['AskContributorCode'],
+ 'upfront' : csvdict['Mid'],
+ 'running' : float(csvdict['Coupon']) * 10000 if 'Coupon' in csvdict else 500,
+ 'tenor' : 'Y5',
+ 'basketid' : 182910,
+ 'trancheid' : 182910+i}
+ data.append(d)
+ with engine.begin() as conn:
+ conn.execute(ins, data)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 01:44:02 +0000