diff options
| -rw-r--r-- | python/mark_backtest_backfill.py | 503 | ||||
| -rw-r--r-- | python/mark_backtest_underpar.py | 240 | ||||
| -rw-r--r-- | python/notebooks/Valuation Backtest.ipynb | 14 |
3 files changed, 561 insertions, 196 deletions
diff --git a/python/mark_backtest_backfill.py b/python/mark_backtest_backfill.py index 1d054424..d44e2155 100644 --- a/python/mark_backtest_backfill.py +++ b/python/mark_backtest_backfill.py @@ -4,14 +4,14 @@ import re import psycopg2 import datetime import bbg_helpers -from db import dbconn + +from utils.db import dbconn, serenitas_engine, dawn_engine, DataError from pickle import dumps from sqlalchemy import create_engine from itertools import chain from glob import glob, iglob from pandas.tseries.offsets import MonthEnd, BDay -from db import serenitas_engine, dawn_engine, DataError def runAllFill(): for f in get_globs(): @@ -19,29 +19,34 @@ def runAllFill(): runSingleFill(f) print("done " + f) + def runSingleFill(f): range_name, sheet_name, done = settings[os.path.basename(f)] - markdate = pd.Timestamp(os.path.dirname(f).rsplit(os.path.sep, 1)[-1])-5*BDay() + MonthEnd() - #change /usr/lib/python3.6/site-packages/xlrd/xlsx.py line 609 to check for ":" in ref as well. Otherwise single merged cells bombs + markdate = ( + pd.Timestamp(os.path.dirname(f).rsplit(os.path.sep, 1)[-1]) + - 5 * BDay() + + MonthEnd() + ) + # change /usr/lib/python3.6/site-packages/xlrd/xlsx.py line 609 to check for ":" in ref as well. Otherwise single merged cells bombs marks = pd.read_excel(f, sheet_name, skiprows=11, usecols=range_name) - marks = marks.rename(columns=lambda x: x.replace('.1','')) + marks = marks.rename(columns=lambda x: x.replace(".1", "")) df = pd.DataFrame() - for s in ['1st', '2nd', '3rd'] +[str(i)+'th' for i in range(4, 12)]: - if s+' Source Price' not in marks: + for s in ["1st", "2nd", "3rd"] + [str(i) + "th" for i in range(4, 12)]: + if s + " Source Price" not in marks: break - temp = marks[['CUSIP', s+' Source Price', s+' Source']] - temp.columns = ['identifier', 'mark', 'source'] + temp = marks[["CUSIP", s + " Source Price", s + " Source"]] + temp.columns = ["identifier", "mark", "source"] df = df.append(temp) df = df.dropna() df = df[df.mark != 0] - df['date'] = markdate - df['source'] = df['source'].str.upper() + df["date"] = markdate + df["source"] = df["source"].str.upper() - conn = dbconn('dawndb') + conn = dbconn("dawndb") sql_str = "INSERT INTO external_marks VALUES(%(identifier)s, %(date)s, %(mark)s, %(source)s) ON CONFLICT DO NOTHING" with conn.cursor() as c: - for r in df.to_dict(orient='record'): + for r in df.to_dict(orient="record"): try: c.execute(sql_str, r) except (psycopg2.DataError, psycopg2.IntegrityError) as detail: @@ -50,34 +55,41 @@ def runSingleFill(f): finally: conn.commit() + def get_CUSIPs(): - conn = dbconn('dawndb') + conn = dbconn("dawndb") sql_str = "select distinct cusip from bonds where asset_class = 'Subprime'" df = pd.read_sql_query(sql_str, dawn_engine) return df + def get_BVAL(): - BBG_IP = ['192.168.9.65'] + BBG_IP = ["192.168.9.65"] bbgstartdate = pd.datetime(2013, 1, 1) - hist_securities = get_CUSIPs()['cusip']+ ' Mtge' - hist_fields = ['PX_LAST'] + hist_securities = get_CUSIPs()["cusip"] + " Mtge" + hist_fields = ["PX_LAST"] with bbg_helpers.init_bbg_session(BBG_IP) as session: - hist_data = bbg_helpers.retrieve_data(session, hist_securities, hist_fields, start_date=bbgstartdate) + hist_data = bbg_helpers.retrieve_data( + session, hist_securities, hist_fields, start_date=bbgstartdate + ) - hist_data1 = pd.concat(hist_data, axis = 1) - hist_data1 = hist_data1.resample('1m', closed= 'right').last() - hist_data1 = hist_data1.drop(pd.Timestamp('2017-02-28')).stack(level = -2) - hist_data1['source'] = 'BVAL' - hist_data1 = hist_data1.reset_index().rename(columns = {'level_1':'identifier', 'PX_LAST': 'mark'}) + hist_data1 = pd.concat(hist_data, axis=1) + hist_data1 = hist_data1.resample("1m", closed="right").last() + hist_data1 = hist_data1.drop(pd.Timestamp("2017-02-28")).stack(level=-2) + hist_data1["source"] = "BVAL" + hist_data1 = hist_data1.reset_index().rename( + columns={"level_1": "identifier", "PX_LAST": "mark"} + ) hist_data1.identifier = hist_data1.identifier.str[:9] return hist_data1 + def pop_BVAL_to_database(df): - conn = dbconn('dawndb') + conn = dbconn("dawndb") sql_str = "INSERT INTO external_marks VALUES(%(identifier)s, %(date)s, %(mark)s, %(source)s) ON CONFLICT DO NOTHING" with conn.cursor() as c: - for r in df.to_dict(orient='record'): + for r in df.to_dict(orient="record"): try: c.execute(sql_str, r) except (psycopg2.DataError, psycopg2.IntegrityError) as detail: @@ -86,90 +98,367 @@ def pop_BVAL_to_database(df): finally: conn.commit() + def get_globs(): - basedir = '/home/serenitas/Daily' - globs = [iglob(os.path.join(basedir, - year, - "{}_*/{}*/ReviewedPack*.xlsx".format(year, year))) - for year in ['2015', '2016', '2017', '2018', '2019']] - for year in ['2013', '2014']: - globs.append(iglob(os.path.join(basedir, - year, - "{}_*/{}*/Serenitas_Month-end_book*.xlsx".format(year, year)))) + basedir = "/home/serenitas/Daily" + globs = [ + iglob( + os.path.join( + basedir, year, "{}_*/{}*/ReviewedPack*.xlsx".format(year, year) + ) + ) + for year in ["2015", "2016", "2017", "2018", "2019"] + ] + for year in ["2013", "2014"]: + globs.append( + iglob( + os.path.join( + basedir, + year, + "{}_*/{}*/Serenitas_Month-end_book*.xlsx".format(year, year), + ) + ) + ) return chain.from_iterable(globs) + settings = { - 'ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190401.20190430.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190301.20190331.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190201.20190228.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20190101.20190131.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20181201.20181231.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20181101.20181130.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20181001.20181031.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20180901.20180930.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180801.20180831.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180701.20180731.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180601.20180630.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.jipark.SERENITAS.SERCGMAST.20180501.20180531.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180401.20180430.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180301.20180331.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180201.20180228.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20180101.20180131.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171201.20171231.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171101.20171130.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171001.20171031.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170901.20170930.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170801.20170831.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170701.20170731.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170601.20170630.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170501.20170531.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170401.20170430.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170301.20170331.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170201.20170228.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170101.20170131.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161201.20161231.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161101.20161130.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161001.20161031.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160901.20160930.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160801.20160831.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160701.20160731.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160601.20160630.Draft.xlsx': ("JA:JX", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160501.20160531.Draft.xlsx': ("IA:IV", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160401.20160430.Final.xlsx': ("IA:IV", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160301.20160331.Final.xlsx': ("IA:IV", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160201.20160229.Draft.xlsx': ("IA:IV", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160101.20160131.Draft.xlsx': ("IA:IV", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151201.20151231.Draft.xlsx': ("IA:IV", "Securities Valuation Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151101.20151130.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151001.20151031.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150901.20150930.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150801.20150831.Draft.savable.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150701.20150731.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150601.20150630.Draft.xlsx': ("GA:GR", "Securities Valuation - Details", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150501.20150531.Draft.xlsx': ("GA:GR", "VC Report 0531", "Y"), - 'ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150401.20150430.Draft.xlsx': ("GA:GR", "All Securities Valuation Report", "Y"), - '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", "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"), - 'Serenitas_Month-end_book--11_30_2013.xlsx': ("II:IZ", "ALL Securities", "Y"), - 'Serenitas_Month-end_book--10_31_2013.xlsx': ("II:IZ", "All Securities", "Y"), - 'Serenitas_Month-end_book--09_30_2013.xlsx': ("II:IZ", "All Securities", "Y"), - 'Serenitas_Month-end_book--08_31_2013.xlsx': ("II:IZ", "All Securities", "Y"), - 'Serenitas_Month-end_book--07_31_2013.xlsx': ("II:IZ", "All Securities", "Y"), - 'Serenitas_Month-end_book--06_30_2013.xlsx': ("II:IZ", "All Securities", "Y"), - 'Serenitas_Month-end_book--05 31 2013.xlsx': ("II:IZ", "All Securities", "Y"), - 'Serenitas_Month-end_book--04 30 2013.xlsx': ("II:IZ", "All Securities", "Y") + "ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190901.20190930.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190801.20190831.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.lkadriu.SERENITAS.SERCGMAST.20190701.20190731.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190601.20190630.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190501.20190531.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190401.20190430.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190301.20190331.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20190201.20190228.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20190101.20190131.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20181201.20181231.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20181101.20181130.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20181001.20181031.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.jmircovi.SERENITAS.SERCGMAST.20180901.20180930.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180801.20180831.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180701.20180731.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180601.20180630.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.jipark.SERENITAS.SERCGMAST.20180501.20180531.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180401.20180430.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180301.20180331.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20180201.20180228.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20180101.20180131.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171201.20171231.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171101.20171130.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20171001.20171031.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170901.20170930.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170801.20170831.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170701.20170731.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170601.20170630.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170501.20170531.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170401.20170430.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170301.20170331.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170201.20170228.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20170101.20170131.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161201.20161231.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161101.20161130.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20161001.20161031.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160901.20160930.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160801.20160831.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.bseeman.SERENITAS.SERCGMAST.20160701.20160731.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160601.20160630.Draft.xlsx": ( + "JA:JX", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160501.20160531.Draft.xlsx": ( + "IA:IV", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160401.20160430.Final.xlsx": ( + "IA:IV", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160301.20160331.Final.xlsx": ( + "IA:IV", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160201.20160229.Draft.xlsx": ( + "IA:IV", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20160101.20160131.Draft.xlsx": ( + "IA:IV", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151201.20151231.Draft.xlsx": ( + "IA:IV", + "Securities Valuation Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151101.20151130.Draft.xlsx": ( + "GA:GR", + "Securities Valuation - Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20151001.20151031.Draft.xlsx": ( + "GA:GR", + "Securities Valuation - Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150901.20150930.Draft.xlsx": ( + "GA:GR", + "Securities Valuation - Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150801.20150831.Draft.savable.xlsx": ( + "GA:GR", + "Securities Valuation - Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150701.20150731.Draft.xlsx": ( + "GA:GR", + "Securities Valuation - Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150601.20150630.Draft.xlsx": ( + "GA:GR", + "Securities Valuation - Details", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150501.20150531.Draft.xlsx": ( + "GA:GR", + "VC Report 0531", + "Y", + ), + "ReviewedPack.pcharubh.SERENITAS.SERCGMAST.20150401.20150430.Draft.xlsx": ( + "GA:GR", + "All Securities Valuation Report", + "Y", + ), + "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", "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"), + "Serenitas_Month-end_book--11_30_2013.xlsx": ("II:IZ", "ALL Securities", "Y"), + "Serenitas_Month-end_book--10_31_2013.xlsx": ("II:IZ", "All Securities", "Y"), + "Serenitas_Month-end_book--09_30_2013.xlsx": ("II:IZ", "All Securities", "Y"), + "Serenitas_Month-end_book--08_31_2013.xlsx": ("II:IZ", "All Securities", "Y"), + "Serenitas_Month-end_book--07_31_2013.xlsx": ("II:IZ", "All Securities", "Y"), + "Serenitas_Month-end_book--06_30_2013.xlsx": ("II:IZ", "All Securities", "Y"), + "Serenitas_Month-end_book--05 31 2013.xlsx": ("II:IZ", "All Securities", "Y"), + "Serenitas_Month-end_book--04 30 2013.xlsx": ("II:IZ", "All Securities", "Y"), } diff --git a/python/mark_backtest_underpar.py b/python/mark_backtest_underpar.py index a7d72c2b..401ef81c 100644 --- a/python/mark_backtest_underpar.py +++ b/python/mark_backtest_underpar.py @@ -3,162 +3,236 @@ import matplotlib import numpy as np import matplotlib.pyplot as plt import statsmodels.api as sm -from statsmodels.formula.api import gls import seaborn as sb +import datetime + +from statsmodels.formula.api import gls -from db import serenitas_engine, dawn_engine, DataError +from utils.db import serenitas_engine, dawn_engine, DataError import globeop_reports as ops -def get_mark_df(asset_class = 'Subprime'): - #It used to be >1000 but as of 12/17/2018 changed it to 3000 - #Filter marks >3000 where the marks are weird... - df_external_marks = pd.read_sql_query("select * from external_marks_mapped where mark < 3000" - , dawn_engine) + +def get_mark_df(asset_class="Subprime"): + # It used to be >1000 but as of 12/17/2018 changed it to 3000 + # Filter marks >3000 where the marks are weird... + df_external_marks = pd.read_sql_query( + "select * from external_marks_mapped where mark < 3000", dawn_engine + ) df_trades = pd.DataFrame() for date in df_external_marks.date.unique(): - df_temp= pd.read_sql_query("select description, identifier, usd_market_value/price*100 as curr_ntl from risk_positions(%s, %s) where price >0 and length(identifier) = 9 " - , dawn_engine, params = [date, asset_class]) - df_temp['date'] = date + df_temp = pd.read_sql_query( + "select description, identifier, usd_market_value/price*100 as curr_ntl from risk_positions(%s, %s) where price >0 and length(identifier) = 9 ", + dawn_engine, + params=[date, asset_class], + ) + df_temp["date"] = date df_trades = df_trades.append(df_temp) df = df_trades.merge(df_external_marks).dropna() - return df.set_index(['date','identifier']) + return df.set_index(["date", "identifier"]) + -def calc_mark_diff(df, sources= ['PRICESERVE', 'PRICINGDIRECT','BVAL','MARKIT','BROKER', 'REUTERS', 'S&P', 'IDC']): +def calc_mark_diff( + df, + sources=[ + "PRICESERVE", + "PRICINGDIRECT", + "BVAL", + "MARKIT", + "BROKER", + "REUTERS", + "S&P", + "IDC", + ], +): - #All Sources (including manager...?!) - average, manager mark only, median, closest - g = df.groupby(level = ['date','identifier']) + # All Sources (including manager...?!) - average, manager mark only, median, closest + g = df.groupby(level=["date", "identifier"]) diff = g.mean() - diff = diff.join(df[df.source == 'MANAGER']['mark'], rsuffix = '_manager') - diff = diff.join(g.median()['mark'], rsuffix = '_median_all') + diff = diff.join(df[df.source == "MANAGER"]["mark"], rsuffix="_manager") + diff = diff.join(g.median()["mark"], rsuffix="_median_all") temp = g.apply(closest) - temp = temp.rename('mark_closest_all') + temp = temp.rename("mark_closest_all") diff = diff.join(temp) - #Filtered Sources - mean, median, remove max min + # Filtered Sources - mean, median, remove max min df_filtered = df[df.source.isin(sources)] - g1 = df_filtered.groupby(level = ['date','identifier']) - diff = diff.join(g1.mean()['mark'], rsuffix = '_filtered_mean') - diff = diff.join(g1.median()['mark'], rsuffix = '_filtered_median') - diff = diff.join(g1.mark.apply(remove_max_min), rsuffix = '_filtered_no_max_min') + g1 = df_filtered.groupby(level=["date", "identifier"]) + diff = diff.join(g1.mean()["mark"], rsuffix="_filtered_mean") + diff = diff.join(g1.median()["mark"], rsuffix="_filtered_median") + diff = diff.join(g1.mark.apply(remove_max_min), rsuffix="_filtered_no_max_min") - #calculate difference: negative means Serenitas marks higher - diff = diff.multiply(diff.curr_ntl/100, axis = 'index') - del diff['curr_ntl'] - diff = diff.rename(columns = {'mark':'mark_mean_all'}) - diff = diff.apply(lambda x: (x-x.mark_manager), axis = 1) + # calculate difference: negative means Serenitas marks higher + diff = diff.multiply(diff.curr_ntl / 100, axis="index") + del diff["curr_ntl"] + diff = diff.rename(columns={"mark": "mark_mean_all"}) + diff = diff.apply(lambda x: (x - x.mark_manager), axis=1) + + return diff.groupby(level="date").sum() - return diff.groupby(level = 'date').sum() def closest(x): if x.mark.count() > 1: - x['dist'] = abs(x.mark - x.mark[x.source == 'MANAGER']) - return x.mark[x.dist == x.dist[x.dist>0].min()].iloc[0] + x["dist"] = abs(x.mark - x.mark[x.source == "MANAGER"]) + return x.mark[x.dist == x.dist[x.dist > 0].min()].iloc[0] else: return x.mark[0] + def remove_max_min(x): if x.count() >= 4: - return (x.sum() - x.max() - x.min())/(x.count() -2) + return (x.sum() - x.max() - x.min()) / (x.count() - 2) else: return x.mean() + def diff_by_source(df): - #diff_by_source: input get_mark_df(), calculate the pricing by source - df = df.drop('description', 1) - df = df.set_index(['source'], append=True).apply(lambda x: x.curr_ntl * x.mark/100, axis = 1) - df = df.groupby(level =['date','identifier','source']).mean() - df = df.unstack(-1).apply(lambda x: (x-x.MANAGER), axis = 1) - return df.groupby(level = 'date').sum() + # diff_by_source: input get_mark_df(), calculate the pricing by source + df = df.drop("description", 1) + df = df.set_index(["source"], append=True).apply( + lambda x: x.curr_ntl * x.mark / 100, axis=1 + ) + df = df.groupby(level=["date", "identifier", "source"]).mean() + df = df.unstack(-1).apply(lambda x: (x - x.MANAGER), axis=1) + return df.groupby(level="date").sum() def diff_by_source_percentage(df): df = diff_by_source(df) - df = df.join(ops.get_net_navs()['endbooknav']) - df = df.apply(lambda x: (x/x.endbooknav), axis = 1) - del df['endbooknav'] + df = df.join(ops.get_net_navs()["endbooknav"]) + df = df.apply(lambda x: (x / x.endbooknav), axis=1) + del df["endbooknav"] return df + def count_sources(df): - #input get_mark_df(), plot count of each source - g2 = df.set_index('source', append=True).groupby(level = ['date','source']) + # input get_mark_df(), plot count of each source + g2 = df.set_index("source", append=True).groupby(level=["date", "source"]) # there are a good amount of Bloomberg duplicates, not a big deal but should clean them up - g2['mark'].count().unstack(-1).plot() + g2["mark"].count().unstack(-1).plot() + def alt_navs(): navs = ops.get_net_navs() df = calc_mark_diff(get_mark_df()) - end_nav, beg_nav, returns, nav_100 = pd.DataFrame(), pd.DataFrame(), pd.DataFrame(), pd.DataFrame(index = df.index, columns=df.columns) + end_nav, beg_nav, returns, nav_100 = ( + pd.DataFrame(), + pd.DataFrame(), + pd.DataFrame(), + pd.DataFrame(index=df.index, columns=df.columns), + ) for col in df.columns: end_nav[col] = df[col] + navs.endbooknav beg_nav[col] = end_nav[col].shift(1) + navs.net_flow.shift(1) beg_nav[col].iloc[0] = 12500000 - returns[col] = (end_nav[col] - navs.incentive)/beg_nav[col] -1 + returns[col] = (end_nav[col] - navs.incentive) / beg_nav[col] - 1 for i, row in returns.dropna().reset_index().iterrows(): - nav_100.iloc[i] = 100 if i == 0 else nav_100.iloc[i-1] + nav_100.iloc[i] = 100 if i == 0 else nav_100.iloc[i - 1] nav_100.iloc[i] = nav_100.iloc[i] * (1 + returns.iloc[i]) return returns, nav_100 + def annual_performance(nav_100): - perf = nav_100.groupby(pd.Grouper(freq = 'A')).last() - perf_ann = perf/perf.shift(1) - 1 - perf_ann['2013'] = perf['2013']/100-1 + perf = nav_100.groupby(pd.Grouper(freq="A")).last() + perf_ann = perf / perf.shift(1) - 1 + perf_ann["2013"] = perf["2013"] / 100 - 1 return perf_ann + def alt_nav_impact(): navs = ops.get_net_navs() df = calc_mark_diff(get_mark_df()) df = df.join(navs.endbooknav) return df - #return df.iloc[-1]/df.iloc[-1]['endbooknav'] + # return df.iloc[-1]/df.iloc[-1]['endbooknav'] -def back_test(begindate = '2013-01-01', enddate = '2018-01-01', sell_price_threshold = 200): - df = pd.read_sql_query("SELECT * FROM external_marks_mapped WHERE source IS NOT NULL", dawn_engine, - parse_dates=['date']) - df_wide = (pd.pivot_table(df, 'mark', ['identifier', 'date'], 'source').reset_index().sort_values('date')) - df_trades = pd.read_sql_query("select trade_date, identifier, price, buysell from bonds", - dawn_engine, parse_dates=['trade_date']) - df_trades.sort_values('trade_date', inplace = True) - df_sell_wide = pd.merge_asof(df_trades[df_trades.buysell == False], df_wide, left_on='trade_date', right_on='date', by='identifier').drop('date', 1) - df_long = df_sell_wide.set_index(['trade_date','identifier','price','buysell']).stack() - df_long = df_long.reset_index().rename(columns={'level_4': 'source', 0:'mark'}) - df_long['difference'] = (df_long['price'] - df_long['mark'])/df_long['mark'] +def back_test( + begindate="2013-01-01", enddate=datetime.date.today(), sell_price_threshold=200 +): + sql_string = "SELECT * FROM external_marks_mapped WHERE source IS NOT NULL" + df = pd.read_sql_query( + sql_string, + dawn_engine, + parse_dates=["date"], + index_col=["date", "identifier", "source"], + ) + df = pd.pivot_table( + df, values="mark", index=["identifier", "date"], columns="source" + ) - #filtering - df_long = df_long[df_long.identifier.str.len() == 9] - df_long = df_long[df_long.price < sell_price_threshold] - df_long = df_long[(df_long['trade_date'] > begindate) & (df_long['trade_date'] < enddate)] - df_long.loc[df_long.source == 'MANAGER','source'] = 'LMCG' + sql_string = "select trade_date, identifier, price from bonds where buysell=%s and asset_class = %s" + df_trades = pd.read_sql_query( + sql_string, dawn_engine, parse_dates=["trade_date"], params=[False, "Subprime"] + ) - return df_long + df = pd.merge_asof( + df_trades.sort_values("trade_date"), + df.reset_index().sort_values("date"), + left_on="trade_date", + right_on="date", + by="identifier", + ).drop("date", 1) -def stats(df_long, diff_threshold = 5): + df = df.set_index(["trade_date", "identifier", "price"]).stack() + df = df.reset_index().rename(columns={"level_3": "source", 0: "mark"}) + df["difference"] = (df["price"] - df["mark"]) / df["mark"] - g = df_long[df_long.difference < diff_threshold].groupby('source') + # filtering + df = df[df.identifier.str.len() == 9] + df = df[df.price < sell_price_threshold] + df = df[(df["trade_date"] > begindate) & (df["trade_date"] < enddate)] + df.loc[df.source == "MANAGER", "source"] = "LMCG" + + return df - #fit all the models at once - params = g.apply(lambda df: gls('price~mark', df).fit().params) - error = pd.DataFrame([g.difference.mean(),g.difference.std()]) - error.index = ['average', 'standard deviation'] + +def stats(df_long, diff_threshold=5): + + g = df_long[df_long.difference < diff_threshold].groupby("source") + + # fit all the models at once + params = g.apply(lambda df: gls("price~mark", df).fit().params) + error = pd.DataFrame([g.difference.mean(), g.difference.std()]) + error.index = ["average", "standard deviation"] return params, error + def pretty_plot(df_long): - #plt.switch_backend('Agg') + # plt.switch_backend('Agg') sb.set_style("whitegrid") sb.set_context("notebook") - order = ['LMCG','BROKER','BVAL','IDC','MARKIT','PRICESERVE', - 'PRICINGDIRECT','REUTERS','S&P'] - sb.set_palette(sb.hls_palette(10, l=.4, s=.8)) + order = [ + "LMCG", + "BROKER", + "BVAL", + "IDC", + "MARKIT", + "PRICESERVE", + "PRICINGDIRECT", + "REUTERS", + "S&P", + ] + sb.set_palette(sb.hls_palette(10, l=0.4, s=0.8)) - grid = sb.FacetGrid(df_long, hue='source', hue_kws={'s':[50] + [20]*9, - 'marker': ["o"]+["s"]*9, 'alpha': [1]+[.4]*9}, legend_out=True, - aspect=2.1, height= 4, hue_order=order) + grid = sb.FacetGrid( + df_long, + hue="source", + hue_kws={ + "s": [50] + [20] * 9, + "marker": ["o"] + ["s"] * 9, + "alpha": [1] + [0.4] * 9, + }, + legend_out=True, + aspect=2.1, + height=4, + hue_order=order, + ) grid.set(ylim=(0, 105), xlim=(0, 105)) - ax = grid.map(plt.scatter, 'mark', 'price').add_legend() - ax.set_axis_labels('Mark', 'sale price') - plt.plot([100, 0], [100, 0], color="black", lw=2, linestyle='solid') + ax = grid.map(plt.scatter, "mark", "price").add_legend() + ax.set_axis_labels("Mark", "sale price") + plt.plot([100, 0], [100, 0], color="black", lw=2, linestyle="solid") ax.fig.savefig("/home/serenitas/edwin/PythonGraphs/backtest.png") diff --git a/python/notebooks/Valuation Backtest.ipynb b/python/notebooks/Valuation Backtest.ipynb index ab912bab..92549418 100644 --- a/python/notebooks/Valuation Backtest.ipynb +++ b/python/notebooks/Valuation Backtest.ipynb @@ -6,7 +6,7 @@ "metadata": {}, "outputs": [], "source": [ - "from db import dbengine\n", + "from utils.db import dbengine\n", "\n", "import datetime\n", "import mark_backtest_underpar as mark\n", @@ -33,7 +33,7 @@ "outputs": [], "source": [ "#exclude sell price that are over 200\n", - "df_long = mark.back_test('2013-01-01', '2018-12-01', sell_price_threshold = 200)\n", + "df_long = mark.back_test('2013-01-01', date, sell_price_threshold = 200)\n", "df_long = df_long[df_long.source != 'PB']" ] }, @@ -44,7 +44,7 @@ "outputs": [], "source": [ "#%matplotlib nbagg\n", - "#%matplotlib inline\n", + "%matplotlib inline\n", "mark.pretty_plot(df_long)\n", "#file saved in serenitas shared drive/edwin/" ] @@ -224,7 +224,9 @@ "to_plot = ['mark_closest_all', 'mark_filtered_mean']\n", "nav_impact = nav_impact[to_plot].rename(columns={'mark_closest_all': 'mark to closest', \n", " 'mark_filtered_mean': 'mark to mean'})\n", - "nav_impact.plot()" + "ax = nav_impact.plot()\n", + "ax.figure.set_figheight(7)\n", + "ax.figure.savefig(\"/home/serenitas/edwin/PythonGraphs/Valuation_3.png\", bbox_extra_artists=(lgd,), bbox_inches='tight')" ] }, { @@ -296,9 +298,9 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.7.3" + "version": "3.7.4" } }, "nbformat": 4, - "nbformat_minor": 2 + "nbformat_minor": 4 } |