def prerun_partition(debug=False):
'''
Prerun function for step 1, partition.
This function cannot take in any arguments due to limitations
of the RunPython VBA code in xlwings.
Read in/check the input parameters and return a
dict for input parameters and a df for data.
'''
if debug:
print('=' * 30)
print('start of prerun_partition')
print('=' * 30)
# Keys for input parameter dict
keys = [
'freq', 'sdate', 'edate', 'method', 'pcutoff', 'method_growth',
'retropolate', 'sheet_partitions', 'sheet_loadings'
]
# --------------------------
# Read in parameters
# --------------------------
dict_input_partition = read_parameters_partition()
# --------------------------
# Check parameter values
# --------------------------
check_parameters_partition(dict_input_partition, keys)
# --------------------------
# Read in global parameters
# --------------------------
# (this also checks if values have changed since being initially set)
dict_global_params = read_parameters_global()
# Add each key, val from dict_global_params to dict_input_partition
for key, val in dict_global_params.items():
# Check that the keys do not clash
if key in dict_input_partition:
message = 'dict_input_partition should not have key ' + key + ' that is common with dict_global_params'
show_message(message, halt=True)
dict_input_partition[key] = val
# --------------------------
# Create df for data
# --------------------------
df_partition = read_data_partition()
# --------------------------
# Create a dict for groups
# --------------------------
if dict_input_partition['method'] == "PLS":
dict_groups, dict_PLS = read_partition_groupsPLS()
dict_input_partition['PLS_target'] = dict_PLS
else:
dict_groups = read_partition_groups()
dict_input_partition['PLS_target'] = None
# --------------------------
# Check df for partition
# --------------------------
check_data_partition(df_partition, dict_input_partition)
# --------------------------
# Set start and end dates and
# fill missing values for data df
# --------------------------
df_partition = format_data_partition(df_partition,
dict_input_partition['sdate'],
dict_input_partition['edate'])
#---------------------------
# Check PLS target coverage
#---------------------------
if dict_input_partition['method'] == "PLS":
PLSvar = set()
for g in dict_PLS.values():
for e in g:
PLSvar.add(e)
PLSvar = list(PLSvar)
if df_partition.loc[:, PLSvar].isnull().sum().sum() > 0:
print(df_partition.loc[:, PLSvar].head())
message = 'PLS target should cover all dates'
show_message(message, halt=True)
# --------------------------
# Check partition groups
# --------------------------
check_partition_groups(dict_groups, df_partition)
# --------------------------
# Return a dict for input parameters,
# a dict for groups,
# and a df for the data
# --------------------------
return dict_input_partition, dict_groups, df_partition
def read_parameters_tsfit():
'''
Read in parameters for tsfit.
The cell positions for inputs are hardcoded in.
Parameter value and range checking is done in the check_parameters_tsfit function.
'''
# Create dict for parameters
dict_parameters_tsfit = dict()
# ---------------------------#
# Read in necessary values
# ---------------------------#
cellpos = 'F31'
# Get a list of all values starting at cellpos going down
dict_parameters_tsfit['quantlist'] = wb.sheets['Input_parameters'].range(
cellpos).expand('down').value
# Read in info on regressors.
dict_parameters_tsfit['regressors'] = dict()
# Start with the first cell that contains the regressors.
startrow = 31
cellpos = 'A' + str(startrow)
# Read down and get a list of all regressors
regressors = wb.sheets['Input_parameters'].range(cellpos).expand(
'down').value
if not isinstance(regressors, (list, tuple)):
regressors = [regressors]
for iregressor, regressor in enumerate(regressors):
# Get the cells for transformation and optional parameter
# from columns B and C for this row
colnum = startrow + iregressor
transform = wb.sheets['Input_parameters'].range('B' +
str(colnum)).value
option = wb.sheets['Input_parameters'].range('D' + str(colnum)).value
dict_parameters_tsfit['regressors'][regressor + '_trans_' +
str(iregressor) + '_' +
transform] = dict()
# Set as values of dict
dict_parameters_tsfit['regressors'][regressor + '_trans_' +
str(iregressor) + '_' +
transform]['transform'] = transform
dict_parameters_tsfit['regressors'][regressor + '_trans_' +
str(iregressor) + '_' +
transform]['option'] = option
# Read in latest_date
cellpos = 'B61'
dict_parameters_tsfit['latest_date'] = wb.sheets['Input_parameters'].range(
cellpos).value
# Read in t-skew fit parameters
dict_parameters_tsfit['fit_params'] = dict()
cellpos = 'B59'
dict_parameters_tsfit['fit_params']['fittype'] = wb.sheets[
'Input_parameters'].range(cellpos).value
dict_parameters_tsfit['fit_params']['mode'] = dict()
cellpos = 'B64'
dict_parameters_tsfit['fit_params']['mode']['constraint'] = wb.sheets[
'Input_parameters'].range(cellpos).value
cellpos = 'D64'
dict_parameters_tsfit['fit_params']['mode']['value'] = wb.sheets[
'Input_parameters'].range(cellpos).value
dict_parameters_tsfit['fit_params']['qsmooth'] = dict()
cellpos = 'B67'
dict_parameters_tsfit['fit_params']['qsmooth']['option'] = wb.sheets[
'Input_parameters'].range(cellpos).value
cellpos = 'D67'
dict_parameters_tsfit['fit_params']['qsmooth']['period'] = wb.sheets[
'Input_parameters'].range(cellpos).value
#Read in plot parameters
obj1 = wb.sheets['Input_parameters'].api
for ind, c in enumerate(obj1.CheckBoxes()):
e = c.value
print(e)
if ind == 0:
dict_parameters_tsfit['fit_params']['plot_mode'] = (e > 0)
elif ind == 1:
dict_parameters_tsfit['fit_params']['plot_median'] = (e > 0)
elif ind == 2:
dict_parameters_tsfit['fit_params']['plot_mean'] = (e > 0)
# Read in output sheet
cellpos = 'B69'
dict_parameters_tsfit['sheet_tsfit'] = wb.sheets['Input_parameters'].range(
cellpos).value
# Advanced t-skew fit parameters
# Process each parameter in order
pos = 74
for param in ['dof', 'var_low', 'var_high', 'skew_low', 'skew_high']:
dict_parameters_tsfit['fit_params'][param] = dict()
cellpos = 'B' + str(pos)
dict_parameters_tsfit['fit_params'][param]['constraint'] = wb.sheets[
'Input_parameters'].range(cellpos).value
cellpos = 'D' + str(pos)
dict_parameters_tsfit['fit_params'][param]['value'] = wb.sheets[
'Input_parameters'].range(cellpos).value
pos += 1
# The sheetname for the input is read in from what is in cell B52.
# This is read in for do_quantfit() but since there is no way to connect
# the output, we have to assume that the user has not changed this cell
# since running the partitions.
startrow = 51
for isheetname, sheetname in enumerate(
['sheet_quantreg', 'sheet_cond_quant']):
colnum = startrow + isheetname
cellpos = 'B' + str(colnum)
dict_parameters_tsfit[sheetname] = wb.sheets['Input_parameters'].range(
cellpos).value
# checking of values will be done in check_parameters_quantfit
# Read in partitions sheet
cellpos = 'B24'
dict_parameters_tsfit['sheet_partition'] = wb.sheets[
'Input_parameters'].range(cellpos).value
dict_parameters_tsfit['partition_groups'] = read_partition_groups()
return dict_parameters_tsfit
def read_parameters_scenario():
'''
Read in parameters for quantfit.
The cell positions for inputs are hardcoded in.
Parameter value and range checking is done in the check_parameters_quantfit function.
'''
# Create dict for parameters
dict_parameters_scenario = dict()
# ---------------------------#
# Read in necessary values
# ---------------------------#
# Read in quantlist
cellpos = 'F31'
# Get a list of all values starting at cellpos going down
dict_parameters_scenario['quantlist'] = wb.sheets[
'Input_parameters'].range(cellpos).expand('down').value
# Read in info on regressors.
dict_parameters_scenario['regressors'] = dict()
# Start with the first cell that contains the regressors.
startrow = 31
cellpos = 'A' + str(startrow)
# Read down and get a list of all regressors
regressors = wb.sheets['Input_parameters'].range(cellpos).expand(
'down').value
if not isinstance(regressors, (list, tuple)):
regressors = [regressors]
for iregressor, regressor in enumerate(regressors):
# Get the cells for transformation and optional parameter
# from columns B and C for this row
colnum = startrow + iregressor
transform = wb.sheets['Input_parameters'].range('B' +
str(colnum)).value
option = wb.sheets['Input_parameters'].range('D' + str(colnum)).value
dict_parameters_scenario['regressors'][regressor + '_trans_' +
str(iregressor) + '_' +
transform] = dict()
# Set as values of dict
dict_parameters_scenario['regressors'][
regressor + '_trans_' + str(iregressor) + '_' +
transform]['transform'] = transform
dict_parameters_scenario['regressors'][regressor + '_trans_' +
str(iregressor) + '_' +
transform]['option'] = option
# Read in latest_date
cellpos = 'B61'
dict_parameters_scenario['latest_date'] = wb.sheets[
'Input_parameters'].range(cellpos).value
# Read in t-skew fit parameters
dict_parameters_scenario['fit_params'] = dict()
cellpos = 'B59'
dict_parameters_scenario['fit_params']['fittype'] = wb.sheets[
'Input_parameters'].range(cellpos).value
dict_parameters_scenario['fit_params']['mode'] = dict()
cellpos = 'B64'
dict_parameters_scenario['fit_params']['mode']['constraint'] = wb.sheets[
'Input_parameters'].range(cellpos).value
cellpos = 'D64'
dict_parameters_scenario['fit_params']['mode']['value'] = wb.sheets[
'Input_parameters'].range(cellpos).value
dict_parameters_scenario['fit_params']['qsmooth'] = dict()
cellpos = 'B67'
dict_parameters_scenario['fit_params']['qsmooth']['option'] = wb.sheets[
'Input_parameters'].range(cellpos).value
cellpos = 'D67'
dict_parameters_scenario['fit_params']['qsmooth']['period'] = wb.sheets[
'Input_parameters'].range(cellpos).value
# Advanced t-skew fit parameters
# Process each parameter in order
pos = 74
for param in ['dof', 'var_low', 'var_high', 'skew_low', 'skew_high']:
dict_parameters_scenario['fit_params'][param] = dict()
cellpos = 'B' + str(pos)
dict_parameters_scenario['fit_params'][param][
'constraint'] = wb.sheets['Input_parameters'].range(cellpos).value
cellpos = 'D' + str(pos)
dict_parameters_scenario['fit_params'][param]['value'] = wb.sheets[
'Input_parameters'].range(cellpos).value
pos += 1
# Read in shocked t-skew fit parameters
dict_parameters_scenario['fit_params_shocked'] = copy.deepcopy(
dict_parameters_scenario['fit_params'])
cellpos = 'B110'
dict_parameters_scenario['fit_params_shocked']['mode'][
'constraint'] = wb.sheets['Input_parameters'].range(cellpos).value
cellpos = 'D110'
dict_parameters_scenario['fit_params_shocked']['mode'][
'value'] = wb.sheets['Input_parameters'].range(cellpos).value
# Read in scenario parameters
dict_parameters_scenario['shockvars'] = dict()
startrow = 99
cellpos = 'A' + str(startrow)
shockvars = wb.sheets['Input_parameters'].range(cellpos).expand(
'down').value
if not isinstance(shockvars, (list, tuple)):
shockvars = [shockvars]
for ind, shockvar in enumerate(shockvars):
col = startrow + ind
shocktype = wb.sheets['Input_parameters'].range('B' + str(col)).value
if shocktype == 'By +/- STD':
shockvalue = wb.sheets['Input_parameters'].range('F' +
str(col)).value
else:
shockvalue = wb.sheets['Input_parameters'].range('D' +
str(col)).value
dict_parameters_scenario['shockvars'][shockvar] = dict()
dict_parameters_scenario['shockvars'][shockvar][
'shocktype'] = shocktype
dict_parameters_scenario['shockvars'][shockvar][
'shockvalue'] = shockvalue
# Read in output sheet
cellpos = 'B112'
dict_parameters_scenario['sheet_scenario'] = wb.sheets[
'Input_parameters'].range(cellpos).value
startrow = 51
for isheetname, sheetname in enumerate(
['sheet_quantreg', 'sheet_cond_quant']):
colnum = startrow + isheetname
cellpos = 'B' + str(colnum)
dict_parameters_scenario[sheetname] = wb.sheets[
'Input_parameters'].range(cellpos).value
# checking of values will be done in check_parameters_quantfit
# Read in partitions sheet
cellpos = 'B24'
dict_parameters_scenario['sheet_partition'] = wb.sheets[
'Input_parameters'].range(cellpos).value
dict_parameters_scenario['partition_groups'] = read_partition_groups()
return dict_parameters_scenario
def read_parameters_segment():
'''
Read in parameters for quantfit.
The cell positions for inputs are hardcoded in.
Parameter value and range checking is done in the check_parameters_quantfit function.
'''
# Create dict for parameters
dict_parameters_segment = dict()
# ---------------------------#
# Read in necessary values
# ---------------------------#
# Read in partition parament
pos = 17
# Process each parameter in order
for param in [
'freq', 'sdate', 'edate', 'method', 'pcutoff', 'method_growth',
'retropolate'
]:
cellpos = 'B' + str(pos)
dict_parameters_segment[param] = wb.sheets['Input_parameters'].range(
cellpos).value
pos += 1
# Read in quantlist
cellpos = 'F31'
# Get a list of all values starting at cellpos going down
dict_parameters_segment['quantlist'] = wb.sheets['Input_parameters'].range(
cellpos).expand('down').value
# Read in info on regressors.
dict_parameters_segment['regressors'] = dict()
# Start with the first cell that contains the regressors.
startrow = 31
cellpos = 'A' + str(startrow)
# Read down and get a list of all regressors
regressors = wb.sheets['Input_parameters'].range(cellpos).expand(
'down').value
if not isinstance(regressors, (list, tuple)):
regressors = [regressors]
for iregressor, regressor in enumerate(regressors):
# Get the cells for transformation and optional parameter
# from columns B and C for this row
colnum = startrow + iregressor
transform = wb.sheets['Input_parameters'].range('B' +
str(colnum)).value
option = wb.sheets['Input_parameters'].range('D' + str(colnum)).value
dict_parameters_segment['regressors'][regressor + '_trans_' +
str(iregressor) + '_' +
transform] = dict()
# Set as values of dict
dict_parameters_segment['regressors'][
regressor + '_trans_' + str(iregressor) + '_' +
transform]['transform'] = transform
dict_parameters_segment['regressors'][regressor + '_trans_' +
str(iregressor) + '_' +
transform]['option'] = option
# Read in t-skew fit parameters
dict_parameters_segment['fit_params'] = dict()
cellpos = 'B59'
dict_parameters_segment['fit_params']['fittype'] = wb.sheets[
'Input_parameters'].range(cellpos).value
dict_parameters_segment['fit_params']['qsmooth'] = dict()
cellpos = 'B67'
dict_parameters_segment['fit_params']['qsmooth']['option'] = wb.sheets[
'Input_parameters'].range(cellpos).value
cellpos = 'D67'
dict_parameters_segment['fit_params']['qsmooth']['period'] = wb.sheets[
'Input_parameters'].range(cellpos).value
# Advanced t-skew fit parameters
# Process each parameter in order
pos = 74
for param in ['dof', 'var_low', 'var_high', 'skew_low', 'skew_high']:
dict_parameters_segment['fit_params'][param] = dict()
cellpos = 'B' + str(pos)
dict_parameters_segment['fit_params'][param]['constraint'] = wb.sheets[
'Input_parameters'].range(cellpos).value
cellpos = 'D' + str(pos)
dict_parameters_segment['fit_params'][param]['value'] = wb.sheets[
'Input_parameters'].range(cellpos).value
pos += 1
# Read in segment parameters
# Start with the first cell that contains the horizonlist.
startrow = 120
cellpos = 'A' + str(startrow)
# Read down and get a list of all horizons
dict_parameters_segment['horizonlist'] = wb.sheets[
'Input_parameters'].range(cellpos).expand('down').value
# Start with the first cell that contains the fitdatelist.
cellpos = 'B' + str(startrow)
# Read down and get a list of all horizons
dict_parameters_segment['fitdatelist'] = wb.sheets[
'Input_parameters'].range(cellpos).expand('down').value
n_hzs = len(dict_parameters_segment['fitdatelist'])
dict_parameters_segment['fitconstrainlist'] = []
dict_parameters_segment['fitconstrainvalues'] = []
# Start with the first cell that contains the mode.
for i in range(n_hzs):
cellpos = 'D' + str(startrow + i)
# Read down and get a list of all horizons
dict_parameters_segment['fitconstrainlist'].append(
wb.sheets['Input_parameters'].range(cellpos).value)
# Start with the first cell of values of the mode constrains.
cellpos = 'F' + str(startrow + i)
# Read down and get a list of all horizons
dict_parameters_segment['fitconstrainvalues'].append(
wb.sheets['Input_parameters'].range(cellpos).value)
# Read in output sheet
cellpos = 'B130'
dict_parameters_segment['sheet_segment'] = wb.sheets[
'Input_parameters'].range(cellpos).value
cellpos = 'B132'
dict_parameters_segment['sheet_term'] = wb.sheets[
'Input_parameters'].range(cellpos).value
dict_parameters_segment['partition_groups'] = read_partition_groups()
if dict_parameters_segment['method'] == "PLS":
dict_groups, dict_PLS = read_partition_groupsPLS()
dict_parameters_segment['PLS_target'] = dict_PLS
dict_parameters_segment['partition_groups'] = dict_groups
else:
dict_groups = read_partition_groups()
dict_parameters_segment['partition_groups'] = dict_groups
dict_parameters_segment['PLS_target'] = None
return dict_parameters_segment
def run_quantfit(dict_input_quantfit, df_quantfit, debug=False):
'''
Main run function for step 2, quantfit.
Takes in as arguments a dict for input parameters
and a df for data. Outputs a dict for output parameters.
Does quantile fits and returns a dict of output parameters.
** This function should be independent of any Excel input/output
and be executable as a regular Python function independent of Excel. **
'''
warnings.filterwarnings("ignore")
if debug:
print('=' * 30)
print('start of run_quantfit')
print('=' * 30)
# ------------------------
# Create DataFrame for log
# ------------------------
log_frame = pd.DataFrame(columns=['Time', 'Action'])
# ------------------------
# Create output dict
# ------------------------
dict_output_quantfit = dict()
# ------------------------
# Copy the output sheet names
# from dict_input_quantfit
# ------------------------
for key in dict_input_quantfit:
if key.find('sheet_') != -1:
dict_output_quantfit[key] = dict_input_quantfit[key]
# ------------------------
# Get parameters from
# dict_input_quantfit
# ------------------------
horizon = dict_input_quantfit['horizon']
depvar = dict_input_quantfit['target'] + '_cpd_' + str(horizon)
# Get the list of regressors from the sheet Partition_groups
dict_groups = read_partition_groups()
regressors = list(dict_groups.keys())
# TODO: Don't do this here, drop the column so it is not redundant
df_quantfit = df_quantfit.set_index(df_quantfit['date'], drop=False)
#df_quantfit = df_quantfit[:-horizon]
# ------------------------
# Run the quantfit
# ------------------------
qcoeff_all, dcond_quantiles_all, loco_all, exitcode = condquant(
df_quantfit, depvar, regressors, horizon,
dict_input_quantfit['quantlist'])
if exitcode < 1:
action = 'Failed to do quantile regression, exit code: ' + str(
exitcode)
else:
action = 'Quantile regression finished succesfully.'
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame.append(log, ignore_index=True)
# Add return values
figs = {}
figs = coeff_plot(qcoeff_all, dict_groups,
dict_input_quantfit['quantlist'])
dict_output_quantfit['qcoef'] = qcoeff_all
dict_output_quantfit['cond_quant'] = dcond_quantiles_all
dict_output_quantfit['localprj'] = loco_all
dict_output_quantfit['figs'] = figs
return dict_output_quantfit
