diff options
| -rw-r--r-- | python/mark_backtest_backfill.py | 14 | ||||
| -rw-r--r-- | python/mark_backtest_underpar.py | 11 | ||||
| -rw-r--r-- | python/notebooks/Valuation Backtest.ipynb | 8 |
3 files changed, 21 insertions, 12 deletions
diff --git a/python/mark_backtest_backfill.py b/python/mark_backtest_backfill.py index 60e1ec30..1d054424 100644 --- a/python/mark_backtest_backfill.py +++ b/python/mark_backtest_backfill.py @@ -5,13 +5,14 @@ import psycopg2 import datetime import bbg_helpers from db import dbconn -from db import dbengine 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(): if settings[os.path.basename(f)][2] == "N": @@ -22,7 +23,8 @@ 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 - marks = pd.read_excel(f, sheet_name, skiprows=13, parse_cols=range_name) + marks = pd.read_excel(f, sheet_name, skiprows=11, usecols=range_name) + 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)]: @@ -51,7 +53,7 @@ def runSingleFill(f): def get_CUSIPs(): conn = dbconn('dawndb') sql_str = "select distinct cusip from bonds where asset_class = 'Subprime'" - df = pd.read_sql_query(sql_str, dbengine('dawndb')) + df = pd.read_sql_query(sql_str, dawn_engine) return df def get_BVAL(): @@ -89,7 +91,7 @@ def get_globs(): globs = [iglob(os.path.join(basedir, year, "{}_*/{}*/ReviewedPack*.xlsx".format(year, year))) - for year in ['2015', '2016', '2017', '2018']] + for year in ['2015', '2016', '2017', '2018', '2019']] for year in ['2013', '2014']: globs.append(iglob(os.path.join(basedir, year, @@ -97,6 +99,10 @@ def get_globs(): 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"), diff --git a/python/mark_backtest_underpar.py b/python/mark_backtest_underpar.py index 982a8886..a7d72c2b 100644 --- a/python/mark_backtest_underpar.py +++ b/python/mark_backtest_underpar.py @@ -1,5 +1,4 @@ import pandas as pd -from db import dbengine import matplotlib import numpy as np import matplotlib.pyplot as plt @@ -7,17 +6,19 @@ import statsmodels.api as sm from statsmodels.formula.api import gls import seaborn as sb +from 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" - , dbengine('dawndb')) + , 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 " - , dbengine('dawndb'), params = [date, asset_class]) + , 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() @@ -112,11 +113,11 @@ def alt_nav_impact(): #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", dbengine('dawndb'), + 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", - dbengine('dawndb'), parse_dates=['trade_date']) + 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) diff --git a/python/notebooks/Valuation Backtest.ipynb b/python/notebooks/Valuation Backtest.ipynb index 26580d3c..ab912bab 100644 --- a/python/notebooks/Valuation Backtest.ipynb +++ b/python/notebooks/Valuation Backtest.ipynb @@ -222,7 +222,9 @@ "#% impact historical: positive % means the alternative methodology results in a higher NAV\n", "nav_impact = df.divide(df.endbooknav, axis=0)\n", "to_plot = ['mark_closest_all', 'mark_filtered_mean']\n", - "nav_impact[to_plot].plot()" + "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()" ] }, { @@ -253,7 +255,7 @@ "metadata": {}, "outputs": [], "source": [ - "summary.iloc[-2]" + "summary.iloc[-1]" ] }, { @@ -294,7 +296,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.7.0" + "version": "3.7.3" } }, "nbformat": 4, |