1 files changed, 55 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index 5de27fdf..a33fc96a 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -277,6 +277,60 @@ RETURN QUERY EXECUTE query_positions('abs') USING p_date;
 END;
 $$ LANGUAGE plpgsql;
 
+CREATE OR REPLACE function list_cds_marks(p_date date)
+RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
+              notional float, factor float, coupon integer, duration float, clean_nav float, accrued float) AS $$
+DECLARE
+  days integer;
+BEGIN
+days:=days_accrued(p_date);
+RETURN QUERY
+WITH temp AS (SELECT a.*, c.index, c.series, c.version, d,tenor, c.indexfactor/100. AS fact, d.coupon
+              FROM list_cds_positions(p_date) a
+              LEFT JOIN index_version c ON a.security_id=c.redindexcode
+              LEFT JOIN index_desc d USING (redindexcode, maturity)),
+index_price AS (SELECT index, series, version, tenor, closeprice, index_quotes.duration
+                FROM index_quotes WHERE date=p_date)
+SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, temp.coupon,
+index_price.duration, (index_price.closeprice/100.-1)*temp.notional*temp.fact,
+temp.notional*temp.coupon/10000.*temp.fact*days/360
+FROM temp
+LEFT JOIN index_price ON temp.index::text=index_price.index
+		AND temp.series=index_price.series
+		AND temp.version=index_price.version
+		AND temp.tenor=index_price.tenor;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE function list_tranche_marks(p_date date)
+RETURNS TABLE(security_id varchar(12), security_desc varchar(32), maturity date,
+              notional float, factor float, coupon integer, clean_nav float, accrued float) AS $$
+DECLARE
+  days integer;
+BEGIN
+days:=days_accrued(p_date);
+RETURN QUERY
+WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.basketid, d.tenor,
+             tranche_factor(a.attach, a.detach, c.indexfactor, c.cumulativeloss) AS fact
+              FROM list_tranche_positions(p_date) a
+              LEFT JOIN index_version c ON a.security_id=c.redindexcode
+              LEFT JOIN index_desc d USING (redindexcode, maturity)),
+tranche_price AS (SELECT DISTINCT ON (basketid, tenor, attach, detach) basketid, tenor, attach, detach, upfront_mid, tranche_spread FROM
+              markit_tranche_quotes WHERE quotedate<=p_date ORDER by basketid, tenor, attach, detach, quotedate desc)
+SELECT temp.security_id, temp.security_desc, temp.maturity, temp.notional, temp.fact, tranche_spread::integer,
+-upfront_mid*temp.notional*temp.fact,
+temp.notional*tranche_spread/10000.*temp.fact*days/360
+FROM temp
+LEFT JOIN tranche_price USING (basketid, attach, detach, tenor);
+END
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE function days_accrued(p_date date)
+RETURNS integer AS $$
+from dates import days_accrued
+return days_accrued(p_date)
+$$ LANGUAGE plpython2u;
+
 CREATE MATERIALIZED VIEW factors_history AS
  WITH temp AS (
          SELECT c.date,
@@ -415,6 +469,7 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
+
 -- Not sure how to map enums so use text for now
 CREATE FOREIGN TABLE priced(
      cusip varchar(9),