diff options
| -rw-r--r-- | python/externalmarksbackfill.py | 20 | ||||
| -rw-r--r-- | sql/dawn.sql | 9 |
2 files changed, 19 insertions, 10 deletions
diff --git a/python/externalmarksbackfill.py b/python/externalmarksbackfill.py index 89ecf773..c0939e2f 100644 --- a/python/externalmarksbackfill.py +++ b/python/externalmarksbackfill.py @@ -92,16 +92,16 @@ settings = { 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150301.20150331.Draft.xlsx': ("GA:GP", "Actual Valuation Detail", "Y"), 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150201.20150228.Draft.xlsx': ("GA:GL", "Securities Valuation - Details", "Y"), 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150101.20150131.Draft.xlsx': ("GA:GL", "Securities Valuation - Details", "Y"), - 'Serenitas_Month-end_book_12.31.14.xlsx': ("NA:NR", "Valuation report", "N"), - 'Serenitas_Month-end_book_113014.xlsx': ("NA:NR", "Valuation report", "N"), - 'Serenitas_Month-end_book-10_31_2014_updated.xlsx': ("NA:NR", "Valuation report", "N"), - 'Serenitas_Month-end_book-09_30_2014.xlsx': ("II:IZ", "Valuation report", "N"), - 'Serenitas_Month-end_book--08_31_2014_Final.xlsx': ("II:IZ", "Valuation report", "N"), - 'Serenitas_Month-end_book--07_31_2014_final.xlsx': ("II:IZ", "Valuation report", "N"), - 'Serenitas_Month-end_book--06_30_2014_-_Final_with_VC_Report (with CAD payments).xlsx': ("II:IZ", "Valuation report", "N"), - 'Serenitas_Month-end_book--05_31_2014_Final.xlsx': ("II:IZ", "Valuation report", "N"), - 'Serenitas_Month-end_book--04_30_2014 Final VC Report.xlsx': ("II:IZ", "All Securities Report", "N"), - 'Serenitas_Month-end_book--03_31_2014 - Final.xlsx': ("II:IZ", "All Securities Report", "N"), + 'Serenitas_Month-end_book_12.31.14.xlsx': ("NA:NR", "Valuation report", "Y"), + 'Serenitas_Month-end_book_113014.xlsx': ("NA:NR", "Valuation report", "Y"), + 'Serenitas_Month-end_book-10_31_2014_updated.xlsx': ("NA:NR", "Valuation report", "Y"), + 'Serenitas_Month-end_book-09_30_2014.xlsx': ("II:IZ", "Valuation report", "Y"), + 'Serenitas_Month-end_book--08_31_2014_Final.xlsx': ("II:IZ", "Valuation report", "Y"), + 'Serenitas_Month-end_book--07_31_2014_final.xlsx': ("II:IZ", "Valuation report", "Y"), + 'Serenitas_Month-end_book--06_30_2014_-_Final_with_VC_Report (with CAD payments).xlsx': ("II:IZ", "Valuation report", "Y"), + 'Serenitas_Month-end_book--05_31_2014_Final.xlsx': ("II:IZ", "Valuation report", "Y"), + 'Serenitas_Month-end_book--04_30_2014 Final VC Report.xlsx': ("II:IZ", "All Securities Report", "Y"), + 'Serenitas_Month-end_book--03_31_2014 - Final.xlsx': ("II:IZ", "All Securities Report", "Y"), 'Serenitas_Month-end_book--02_28_2014 Final.xlsx': ("II:IZ", "All Securities Report", "Y"), 'Serenitas_Month-end_book--01_31_2014.xlsx': ("II:IZ", "ALL Securities", "Y"), 'Serenitas_Month-end_book--12_31_2013.xlsx': ("II:IZ", "ALL Securities", "Y"), diff --git a/sql/dawn.sql b/sql/dawn.sql index 3f38a4a2..35ebd395 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -270,6 +270,11 @@ CREATE TABLE external_marks( source text, PRIMARY KEY (identifier, date, source)); +CREATE TABLE mark_source_mapping( + globeop text, + final text, + PRIMARY KEY (globeop)); + CREATE OR REPLACE function list_marks(p_date date, include_unsettled boolean DEFAULT False) RETURNS TABLE(identifier varchar(12), price float) AS $$ BEGIN @@ -393,6 +398,10 @@ protection, attach, detach, trade_date, upfront_settle_date FROM cds JOIN index_desc ON index_desc.redindexcode=cds.security_id AND index_desc.maturity=cds.maturity ORDER BY dealid, trade_date; +CREATE OR REPLACE VIEW external_marks_mapped AS +select date, identifier, mark, b.final as source from external_marks a +left join mark_source_mapping b on a.source = b.globeop order by a.date asc; + CREATE OR REPLACE function query_positions(p_type text DEFAULT NULL) RETURNS text AS $$ DECLARE |