diff options
| -rw-r--r-- | python/cusip_numbers.py | 26 | ||||
| -rw-r--r-- | sql/et_tables.sql | 46 |
2 files changed, 62 insertions, 10 deletions
diff --git a/python/cusip_numbers.py b/python/cusip_numbers.py new file mode 100644 index 00000000..72e9f6f3 --- /dev/null +++ b/python/cusip_numbers.py @@ -0,0 +1,26 @@ +import psycopg2
+import os
+import os.path
+import pdb
+
+if os.name =='nt':
+ root = "//WDsentinel/share/CorpCDOs/data/markit"
+elif os.name == 'posix':
+ root = '/home/share/CorpCDOS/data/markit'
+
+conn = psycopg2.connect(database="ET",
+ user="et_user",
+ password="Serenitas1",
+ host="192.168.1.108")
+cursor = conn.cursor()
+cursor.execute("SELECT dealname FROM latest_clo_universe")
+dealnames = cursor.fetchall()
+
+
+for dealname in dealnames:
+ cursor.execute("SELECT p_cusip, p_curr_subordination, p_curr_thickness from et_deal_subordination(%s)",
+ dealname)
+ cursor.executemany("INSERT INTO et_model_numbers(Cusip, subordination, thickness) VALUES(%s, %s, %s)",
+ cursor.fetchall())
+ conn.commit()
+conn.close()
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 93a2682d..d403252a 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -351,12 +351,12 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) BEGIN RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager,NULL, a.Spread/100, b."Deal Issue Date", b."Reinv End Date", b.Maturity, - CAST(NULL AS float), Cast(NULL AS float), CAST(NULL AS FLOAT), CAST(NULL AS float), - a.Curr_Attach/100, (a.Curr_Detach-a.Curr_Attach)/100 + CAST(NULL AS float), b."CDO Percentage", CAST(NULL AS FLOAT), CAST(NULL AS float), + d.subordination, d.thickness FROM cusip_universe a LEFT JOIN latest_clo_universe b ON a.dealname = b.dealname JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c - ON a.cusip = c.cusip ORDER BY c.id; + ON a.cusip = c.cusip LEFT JOIN et_model_numbers d ON d.cusip = c.cusip ORDER BY c.id; END; $$ LANGUAGE plpgsql; @@ -364,7 +364,8 @@ ALTER FUNCTION et_cusip_details(varchar(10)[]) OWNER TO et_user; CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) - RETURNS TABLE(p_cusip varchar(9), p_orig_subordination float, p_curr_subordination float) AS $$ + RETURNS TABLE(p_cusip varchar(9), p_orig_subordination float, p_curr_subordination float, + p_orig_thickness float, p_curr_thickness float) AS $$ DECLARE cusip_row RECORD; curr_cumsum_cusip_bal float; @@ -375,8 +376,10 @@ CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) defaulted_price float:=0.7; curr_subordination float[]; orig_subordination float[]; + curr_thickness float[]; + orig_thickness float[]; deal_info RECORD; - i int := 0; + i int := 1; previous_moody text; cusips varchar(9)[]; BEGIN @@ -396,16 +399,39 @@ CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) curr_subordination := curr_subordination || (curr_adjusted_collat_bal-curr_cumsum_cusip_bal)/deal_info."Curr Deal Bal"; orig_subordination := orig_subordination || (orig_adjusted_collat_bal - orig_cumsum_cusip_bal)/deal_info."Orig Deal Bal"; cusips := cusips || cusip_row.Cusip; - i:=i+1; - IF i>0 THEN + IF i=1 THEN + curr_thickness := curr_thickness || 1 - curr_subordination[i]; + orig_thickness := orig_thickness || 1 - orig_subordination[i]; + ELSE + curr_thickness := curr_thickness || curr_subordination[i-1] - curr_subordination[i]; + orig_thickness := orig_thickness || orig_subordination[i-1] - orig_subordination[i]; + END IF; + if i>1 THEN IF cusip_row.orig_moody = previous_moody THEN - curr_subordination[i-1]:=curr_subordination[i]; - orig_subordination[i-1]:=orig_subordination[i]; + curr_subordination[i-1] := curr_subordination[i]; + orig_subordination[i-1] := orig_subordination[i]; + IF i=2 THEN + curr_thickness[i-1] := 1 - curr_subordination[i-1]; + orig_thickness[i-1] := 1 - orig_subordination[i-1]; + ELSE + curr_thickness[i-1] := curr_subordination[i-2] - curr_subordination[i-1]; + orig_thickness[i-1] := orig_subordination[i-2] - orig_subordination[i-1]; + END IF; END IF; END IF; + i := i+1; previous_moody := cusip_row.orig_moody; END LOOP; - RETURN QUERY SELECT unnest(cusips), unnest(orig_subordination), unnest(curr_subordination); + RETURN QUERY SELECT unnest(cusips), unnest(orig_subordination), unnest(curr_subordination), unnest(orig_thickness), unnest(curr_thickness); END; $$ LANGUAGE plpgsql; +CREATE TABLE et_model_numbers( + Cusip varchar(9) references cusip_universe(Cusip), + subordination float, + thickness float, + wap_basis float, + PRIMARY KEY(Cusip) + ); + +GRANT ALL ON et_model_numbers TO et_user; |