diff options
Diffstat (limited to 'python/mark_backtest_backfill.py')
| -rw-r--r-- | python/mark_backtest_backfill.py | 14 |
1 files changed, 10 insertions, 4 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"), |