def postrun_tsfit(dict_output_tsfit, debug=False):
'''
Postrun function for step 3, tsfit.
Takes as input dict from main run function.
This function cannot return any values due to limitations
of the RunPython VBA code in xlwings.
'''
if debug:
print('=' * 30)
print('start of postrun_tsfit')
print('=' * 30)
# Create DataFrame for log
log_frame = pd.DataFrame(columns=['Time', 'Action'])
# Create the output sheets
sheetname = dict_output_tsfit['sheet_tsfit']
sheetnames = [sheet.name for sheet in wb.sheets]
pd.options.display.float_format = '{:,.4f}'.format
try:
# Clear the sheet if it already exists
if sheetname in sheetnames:
wb.sheets[sheetname].clear()
action = 'Cleared sheet ' + sheetname
# Otherwise add it after the "Data" sheet
else:
wb.sheets.add(sheetname, after='Data')
# Set output sheet colors to blue
wb.sheets[sheetname].api.Tab.Colorindex = 23
action = 'Created sheet ' + sheetname
except:
action = 'Unable to access sheet ' + sheetname
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# end of loop over output sheetvars
# Write out tsfit results
cond_quant = dict_output_tsfit['data']
df_pdf = dict_output_tsfit['dfpdf']
res = dict_output_tsfit['result']
sheetname = dict_output_tsfit['sheet_tsfit']
try:
wb.sheets[sheetname].pictures[0].delete()
except:
pass
try:
wb.sheets[sheetname].pictures[1].delete()
except:
pass
try:
wb.sheets[sheetname].range('A1').value = res
wb.sheets[sheetname].range('M1').value = df_pdf
wb.sheets[sheetname].range('M:M').clear()
wb.sheets[sheetname].range('R1').value = cond_quant
action = 'T-skew fit saved succesfully.'
except:
action = 'Unable to output t-skew fit result.'
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# Write out figure
sheetname = dict_output_tsfit['sheet_tsfit']
sheet = wb.sheets[sheetname]
fig = dict_output_tsfit['fig']
fig2 = dict_output_tsfit['fig2']
# Set the path of the output file to be in the same dir as the
# calling Excel file
fullpath = os.path.abspath(os.path.dirname(wb.fullname) + '/figures')
if not os.path.isdir(fullpath):
os.makedirs(fullpath)
outfilename = fullpath + '\\tskfit_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
try:
fig.savefig(outfilename, dpi='figure')
except:
print('Fail to save t-skew fit figure.')
try:
partitionfiglist = glob(fullpath + '\\partition*.png')
pimgfile = max(partitionfiglist)
qautfiglist = glob(fullpath + '\\quantfit*.png')
qimgfile = max(qautfiglist)
print(pimgfile, qimgfile)
pimgr = plt.imread(pimgfile)
qimgr = plt.imread(qimgfile)
pfig, pax = plt.subplots(1, 1, figsize=(57, 38))
pax.imshow(pimgr)
plt.axis('off')
qfig, qax = plt.subplots(1, 1, figsize=(35, 14))
qax.imshow(qimgr)
plt.axis('off')
print(pimgfile, qimgfile)
pdfile = fullpath + '\\resultplots_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.pdf'
print(pdfile)
pp = PdfPages(pdfile)
pp.savefig(pfig)
pp.savefig(qfig)
pp.savefig(fig)
pp.close()
except:
print('Unable to save PDF plots.')
try:
sheet.pictures.add(fig,
name='MyPlot',
update=True,
left=sheet.range('B14').left,
top=sheet.range('B14').top,
height=250,
width=500)
action = 'Skewed T distribution fit figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
try:
sheet.pictures.add(fig2,
name='MyPlot_2',
update=True,
left=sheet.range('B33').left,
top=sheet.range('B33').top,
height=250,
width=500)
action = 'Skewed T distribution fit figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
sheet.autofit()
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# Write out log_frame
add_logsheet(wb, log_frame, colnum=5)
def postrun_partition(dict_output_partition, debug=False):
'''
Postrun function for step 1, partition.
Takes as input dict from main run function.
This function cannot return any values due to limitations
of the RunPython VBA code in xlwings.
'''
if debug:
print('=' * 30)
print('start of run_partition')
print('=' * 30)
# Create DataFrame for log
log_frame = pd.DataFrame(columns=['Time', 'Action'])
# Create the output sheets
for sheetvar in [
key for key in dict_output_partition if key.find('sheet') != -1
]:
# Check that sheetvar exists as a key in dict_output_partition
if sheetvar not in dict_output_partition:
message = 'sheetvar ' + sheetvar + ' is not a key for dict_output_partition'
show_message(message, halt=True)
# Get the actual sheet name
sheetname = dict_output_partition[sheetvar]
# Get existing sheetnames
sheetnames = [sheet.name for sheet in wb.sheets]
try:
# Clear the sheet if it already exists
if sheetname in sheetnames:
wb.sheets[sheetname].clear()
action = 'Cleared sheet ' + sheetname
# Otherwise add it after the "Data" sheet
else:
wb.sheets.add(sheetname, after='Data')
# Set output sheet colors to blue
wb.sheets[sheetname].api.Tab.Colorindex = 23
action = 'Created sheet ' + sheetname
except:
action = 'Unable to access sheet ' + sheetname
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# end of loop over output sheetvars
# Write out partition and loadings
try:
sheetname = dict_output_partition['sheet_partitions']
wb.sheets[sheetname].range('A1').options(
index=False).value = dict_output_partition['frame']
wb.sheets[sheetname].autofit()
sheetname = dict_output_partition['sheet_loadings']
wb.sheets[sheetname].range('A1').options(
index=False).value = dict_output_partition['loading']
wb.sheets[sheetname].autofit()
action = 'Partitions and loadings saved succesfully.'
except:
action = 'Unable to output partitions and loadings.'
sheetname = dict_output_partition['sheet_partitions']
for p in wb.sheets[sheetname].shapes:
try:
p.delete()
except Exception as e:
print(e)
sheet = wb.sheets[sheetname]
if dict_output_partition['method'] == 'PLS':
for i, fig in enumerate(dict_output_partition['figs']):
fullpath = os.path.abspath(
os.path.dirname(wb.fullname) + '/figures')
if not os.path.isdir(fullpath):
os.makedirs(fullpath)
group = dict_output_partition['groups'][i]
outfilename = fullpath + '\\partition_PLS_' + group + date.now(
).strftime('%Y_%m-%d@%H_%M-%S') + '.png'
fig.savefig(outfilename)
try:
X = str(2 + i * 38)
sheet.pictures.add(fig,
name='MyPlot_P' + str(i + 1),
update=True,
left=sheet.range('M' + X).left,
top=sheet.range('M' + X).top,
height=500,
width=750)
action = 'Partition figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
else:
fig = dict_output_partition['figs'][0]
# Set the path of the output file to be in the same dir as the
# calling Excel file
fullpath = os.path.abspath(os.path.dirname(wb.fullname) + '/figures')
if not os.path.isdir(fullpath):
os.makedirs(fullpath)
outfilename = fullpath + '\\partition_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
fig.savefig(outfilename)
cr = len(dict_output_partition['groups'])
try:
sheet.pictures.add(fig,
name='MyPlot_P1',
update=True,
left=sheet.range('M2').left,
top=sheet.range('M2').top,
height=720,
width=cr * 255)
action = 'Partition figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
fig1 = dict_output_partition['figs'][1]
if dict_output_partition['method'] == 'PLS':
ht = 700
wd = 480
else:
ht = 320
wd = 320
try:
sheet.pictures.add(fig1,
name='MyPlot_P2',
update=True,
left=sheet.range('M54').left,
top=sheet.range('M54').top,
height=ht,
width=wd)
action = 'Partition figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
# Write out log_frame
add_logsheet(wb, log_frame, colnum=1)
def postrun_segment(dict_output_segment, debug=False):
'''
Postrun function for step 2, segment.
Takes as input dict from main run function.
This function cannot return any values due to limitations
of the RunPython VBA code in xlwings.
'''
if debug:
print('=' * 30)
print('start of postrun_segment')
print('=' * 30)
# Create DataFrame for log
log_frame = pd.DataFrame(columns=['Time', 'Action'])
sheetname = dict_output_segment['sheet_segment']
# Get existing sheetnames
sheetnames = [sheet.name for sheet in wb.sheets]
try:
# Clear the sheet if it already exists
if sheetname in sheetnames:
wb.sheets[sheetname].clear()
action = 'Cleared sheet ' + sheetname
# Otherwise add it after the "Data" sheet
else:
wb.sheets.add(sheetname, after='Data')
# Set output sheet colors to blue
wb.sheets[sheetname].api.Tab.ColorIndex = 23
action = 'Created sheet ' + sheetname
except:
print('Unable to acess ' + sheetname)
for p in wb.sheets[sheetname].shapes:
try:
p.delete()
except Exception as e:
print(e)
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
sheet = wb.sheets[sheetname]
pd.options.display.float_format = '{:,.4f}'.format
df_pdf = dict_output_segment['dfpdf'].round(decimals=5)
df_cqs = dict_output_segment['cqs'].round(decimals=5)
df_term = dict_output_segment['dfterm'].round(decimals=5)
nhz = dict_output_segment['nhz']
nrg = dict_output_segment['nrg']
# Write out segment resul
res = dict_output_segment['res']
wb.sheets[sheetname].range('A1').value = res
wb.sheets[sheetname].range('O1').options(index=False).value = df_cqs
wb.sheets[sheetname].range('N' + str(len(df_cqs) + 4)).options(
index=False).value = df_pdf
fig = dict_output_segment['fig']
fullpath = os.path.abspath(os.path.dirname(wb.fullname) + '/figures')
if not os.path.isdir(fullpath):
os.makedirs(fullpath)
outfilename = fullpath + '\\segment_pdf' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
try:
fig.savefig(outfilename)
except:
print('Fail to save segment figure.')
try:
pic = sheet.pictures.add(fig,
name='MyPlot',
update=True,
left=sheet.range('B12').left,
top=sheet.range('B12').top,
height=250,
width=500)
pic.height = 250
pic.width = 500
action = 'segment figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
fig2 = dict_output_segment['fig2']
outfilename = fullpath + '\\segment_cdf' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
try:
fig2.savefig(outfilename)
except:
print('Fail to save segment figure.')
try:
pic = sheet.pictures.add(fig2,
name='MyPlot_2',
update=True,
left=sheet.range('B32').left,
top=sheet.range('B32').top,
height=250,
width=500)
pic.height = 250
pic.width = 500
action = 'segment figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
fig3 = dict_output_segment['fig3']
outfilename = fullpath + '\\segment_qcoef' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
try:
fig3.savefig(outfilename)
except:
print('Fail to save segment figure.')
try:
pic = sheet.pictures.add(fig3,
name='MyPlot_3',
update=True,
left=sheet.range('B52').left,
top=sheet.range('B52').top,
height=230 * nrg,
width=500)
pic.height = 230 * nrg
pic.width = 500
action = 'segment figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
wb.sheets[sheetname].autofit()
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
sheetname = dict_output_segment['sheet_term']
# Get existing sheetnames
sheetnames = [sheet.name for sheet in wb.sheets]
try:
# Clear the sheet if it already exists
if sheetname in sheetnames:
wb.sheets[sheetname].clear()
action = 'Cleared sheet ' + sheetname
# Otherwise add it after the "Data" sheet
else:
wb.sheets.add(sheetname, after='Data')
# Set output sheet colors to blue
wb.sheets[sheetname].api.Tab.ColorIndex = 23
action = 'Created sheet ' + sheetname
except:
print('Unable to acess ' + sheetname)
for p in wb.sheets[sheetname].shapes:
try:
p.delete()
except Exception as e:
print(e)
wb.sheets[sheetname].range('T1').options(index=True).value = df_term
sheet = wb.sheets[sheetname]
wb.sheets[sheetname].autofit()
termfigs = dict_output_segment['termfigs']
rs = (nhz - 1) // 4 + 1
cs = min(4, nhz)
for i, fig in enumerate(termfigs):
outfilename = fullpath + '\\termstruct_' + str(
i + 1) + '_' + date.now().strftime('%Y_%m-%d@%H_%M-%S') + '.png'
try:
fig.savefig(outfilename)
except:
print('Fail to save term structure figure.')
try:
pic = sheet.pictures.add(fig,
name='Termplot_' + str(i),
update=True,
left=sheet.range('B3').left,
top=sheet.range('B' +
str(3 + i * rs * 21)).top,
height=300 * rs - 40,
width=200 * cs)
pic.height = 300 * rs - 40
pic.width = 200 * cs
action = 'Term structure figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# Write out log_frame
add_logsheet(wb, log_frame, colnum=3)
def postrun_scenario(dict_output_scenario, debug=False):
'''
Postrun function for step 2, scenario.
Takes as input dict from main run function.
This function cannot return any values due to limitations
of the RunPython VBA code in xlwings.
'''
if debug:
print('=' * 30)
print('start of postrun_scenario')
print('=' * 30)
# Create DataFrame for log
log_frame = pd.DataFrame(columns=['Time', 'Action'])
sheetname = dict_output_scenario['sheet_scenario']
# Get existing sheetnames
sheetnames = [sheet.name for sheet in wb.sheets]
try:
# Clear the sheet if it already exists
if sheetname in sheetnames:
wb.sheets[sheetname].clear()
action = 'Cleared sheet ' + sheetname
# Otherwise add it after the "Data" sheet
else:
wb.sheets.add(sheetname, after='Data')
# Set output sheet colors to blue
wb.sheets[sheetname].api.Tab.ColorIndex = 23
action = 'Created sheet ' + sheetname
except:
print('Unable to acess ' + sheetname)
try:
wb.sheets[sheetname].pictures[0].delete()
except:
pass
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
sheet = wb.sheets[sheetname]
pd.options.display.float_format = '{:,.4f}'.format
df_pdf = dict_output_scenario['dfpdf']
df_data = dict_output_scenario['data']
# Write out scenario resul
res = dict_output_scenario['res']
wb.sheets[sheetname].range('A1').value = res
wb.sheets[sheetname].range('N1').options(index=False).value = df_pdf
wb.sheets[sheetname].range('T1').options(index=True).value = df_data
wb.sheets[sheetname].range('T1').value = 'Variables : '
fig = dict_output_scenario['fig']
# Set the path of the output file to be in the same dir as the
# calling Excel file
fullpath = os.path.abspath(os.path.dirname(wb.fullname) + '/figures')
if not os.path.isdir(fullpath):
os.makedirs(fullpath)
outfilename = fullpath + '\\scenario_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
try:
fig.savefig(outfilename)
except:
print('Fail to save scenario figure.')
try:
pic = sheet.pictures.add(fig,
name='MyPlot',
update=True,
left=sheet.range('B12').left,
top=sheet.range('B12').top,
height=250,
width=500)
pic.height = 250
pic.width = 500
action = 'Scenario figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
wb.sheets[sheetname].autofit()
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# Write out log_frame
add_logsheet(wb, log_frame, colnum=3)
def postrun_historical(dict_output_historical, debug=False):
'''
Postrun function for step 2, historical.
Takes as input dict from main run function.
This function cannot return any values due to limitations
of the RunPython VBA code in xlwings.
'''
if debug:
print('=' * 30)
print('start of postrun_historical')
print('=' * 30)
# Create DataFrame for log
log_frame = pd.DataFrame(columns=['Time', 'Action'])
sheetname = dict_output_historical['sheet_historical']
# Get existing sheetnames
sheetnames = [sheet.name for sheet in wb.sheets]
try:
# Clear the sheet if it already exists
if sheetname in sheetnames:
wb.sheets[sheetname].clear()
action = 'Cleared sheet ' + sheetname
# Otherwise add it after the "Data" sheet
else:
wb.sheets.add(sheetname, after='Data')
# Set output sheet colors to blue
wb.sheets[sheetname].api.Tab.ColorIndex = 23
action = 'Created sheet ' + sheetname
except:
print('Unable to acess ' + sheetname)
for p in wb.sheets[sheetname].shapes:
try:
p.delete()
except Exception as e:
print(e)
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
sheet = wb.sheets[sheetname]
# Write out historical results
figs = dict_output_historical['figs']
res = dict_output_historical['data']
charts = dict_output_historical['charts']
# Set the path of the output file to be in the same dir as the
# calling Excel file
fullpath = os.path.abspath(os.path.dirname(wb.fullname) + '/figures')
if not os.path.isdir(fullpath):
os.makedirs(fullpath)
outfilename = fullpath + '\\historical_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
try:
figs['res'].savefig(outfilename)
except:
print('Fail to save historical figure.')
try:
pic = sheet.pictures.add(figs['res'],
name='MyPlot',
update=True,
left=sheet.range('B30').left,
top=sheet.range('B30').top,
height=1700,
width=480)
pic.height = 1700
pic.width = 480
action = 'historical figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
outfilename = fullpath + '\\pittest_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
try:
figs['pit'].savefig(outfilename)
except:
print('Fail to save pit figure.')
try:
pic = sheet.pictures.add(figs['pit'],
name='MyPlot2',
update=True,
left=sheet.range('B3').left,
top=sheet.range('B3').top,
height=360,
width=350)
pic.height = 360
pic.width = 350
action = 'historical figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
outfilename = fullpath + '\\logscore_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
try:
figs['ls'].savefig(outfilename)
except:
print('Fail to save logscore figure.')
try:
pic = sheet.pictures.add(figs['ls'],
name='MyPlot3',
update=True,
left=sheet.range('J3').left,
top=sheet.range('J3').top,
height=360,
width=480)
pic.height = 360
pic.width = 480
action = 'historical figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
try:
wb.sheets[sheetname].range('U1').value = res
except:
action = 'Unable to output historical result.'
try:
pdfile = fullpath + '\\historicalcharts_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.pdf'
pp = PdfPages(pdfile)
for e in charts:
pp.savefig(e)
pp.close()
except:
print('Unable to save PDF chartpacks.')
wb.sheets[sheetname].autofit()
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# Write out log_frame
add_logsheet(wb, log_frame, colnum=3)
def postrun_quantfit(dict_output_quantfit, debug=False):
'''
Postrun function for step 2, quantfit.
Takes as input dict from main run function.
This function cannot return any values due to limitations
of the RunPython VBA code in xlwings.
'''
if debug:
print('=' * 30)
print('start of postrun_quantfit')
print('=' * 30)
# Create DataFrame for log
log_frame = pd.DataFrame(columns=['Time', 'Action'])
# Create the output sheets
sheetvars = [
key for key in dict_output_quantfit if key.find('sheet') != -1
]
for sheetvar in sheetvars:
# Don't do anything for the input sheet
if sheetvar == 'sheet_input':
continue
# Check that sheetvar exists as a key in dict_output_quantfit
if sheetvar not in dict_output_quantfit:
message = 'sheetvar ' + sheetvar + ' is not a key for dict_output_quantfit'
show_message(message, halt=True)
# Get the actual sheet name
sheetname = dict_output_quantfit[sheetvar]
# Get existing sheetnames
sheetnames = [sheet.name for sheet in wb.sheets]
try:
# Clear the sheet if it already exists
if sheetname in sheetnames:
wb.sheets[sheetname].clear()
action = 'Cleared sheet ' + sheetname
# Otherwise add it after the "Data" sheet
else:
wb.sheets.add(sheetname, after='Data')
# Set output sheet colors to blue
wb.sheets[sheetname].api.Tab.ColorIndex = 23
action = 'Created sheet ' + sheetname
except:
action = 'Unable to access sheet ' + sheetname
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# end of loop over output sheetvars
# Write out quantfit results
try:
for sheetvar in sheetvars:
sheetname = dict_output_quantfit[sheetvar]
if sheetvar == 'sheet_quantreg':
wb.sheets[sheetname].range('A1').options(
index=False).value = dict_output_quantfit['qcoef']
wb.sheets[sheetname].autofit()
elif sheetvar == 'sheet_cond_quant':
wb.sheets[sheetname].range('A1').options(
index=True).value = dict_output_quantfit['cond_quant']
wb.sheets[sheetname].autofit()
action = 'Quantfit results saved succesfully.'
except:
action = 'Unable to output quantfit results.'
print(action)
sheetname = dict_output_quantfit['sheet_quantreg']
try:
wb.sheets[sheetname].pictures[0].delete()
except:
pass
sheet = wb.sheets[sheetname]
fig = dict_output_quantfit['figs']
# Set the path of the output file to be in the same dir as the
# calling Excel file
fullpath = os.path.abspath(os.path.dirname(wb.fullname) + '/figures')
if not os.path.isdir(fullpath):
os.makedirs(fullpath)
outfilename = fullpath + '\\quantfit_' + date.now().strftime(
'%Y_%m-%d@%H_%M-%S') + '.png'
fig.savefig(outfilename)
cr = len(dict_output_quantfit['regressors'].keys())
try:
pic = sheet.pictures.add(fig,
name='MyPlot_q',
update=True,
left=sheet.range('N6').left,
top=sheet.range('N6').top,
height=340 * (cr // 4 + 1),
width=240 * (min(4, cr + 1)))
pic.height = 340 * (cr // 4 + 1)
pic.width = 240 * (min(4, cr + 1))
action = 'Quantile figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# Write out log_frame
add_logsheet(wb, log_frame, colnum=3)
def postrun_quantfit(dict_output_quantfit, path='.', debug=False):
'''
Postrun function for step 2, quantfit.
Takes as input dict from main run function.
This function cannot return any values due to limitations
of the RunPython VBA code in xlwings.
'''
if debug:
print('=' * 30)
print('start of postrun_quantfit')
print('=' * 30)
# Create DataFrame for log
log_frame = pd.DataFrame(columns=['Time', 'Action'])
# Create the output sheets
sheetvars = [
key for key in dict_output_quantfit if key.find('sheet') != -1
]
for sheetvar in sheetvars:
# Don't do anything for the input sheet
if sheetvar == 'sheet_input':
continue
# Check that sheetvar exists as a key in dict_output_quantfit
if sheetvar not in dict_output_quantfit:
message = 'sheetvar ' + sheetvar + ' is not a key for dict_output_quantfit'
show_message(message, halt=True)
# Get the actual sheet name
sheetname = dict_output_quantfit[sheetvar]
# Get existing sheetnames
sheetnames = [sheet.name for sheet in wb.sheets]
try:
# Clear the sheet if it already exists
if sheetname in sheetnames:
wb.sheets[sheetname].clear()
action = 'Cleared sheet ' + sheetname
# Otherwise add it after the "Data" sheet
else:
wb.sheets.add(sheetname, after='Data')
# Set output sheet colors to blue
wb.sheets[sheetname].api.Tab.Colorindex = 23
action = 'Created sheet ' + sheetname
except:
action = 'Unable to access sheet ' + sheetname
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# end of loop over output sheetvars
# Write out quantfit results
try:
for sheetvar in sheetvars:
sheetname = dict_output_quantfit[sheetvar]
if sheetvar == 'sheet_quantreg':
wb.sheets[sheetname].range('A1').options(
index=False).value = dict_output_quantfit['qcoef']
elif sheetvar == 'sheet_cond_quant':
wb.sheets[sheetname].range('A1').options(
index=True).value = dict_output_quantfit['cond_quant']
elif sheetvar == 'sheet_local_proj':
wb.sheets[sheetname].range('A1').options(
index=False).value = dict_output_quantfit['localprj']
action = 'Quantfit results saved succesfully.'
except:
action = 'Unable to output quantfit results.'
print(action)
sheetname = dict_output_quantfit['sheet_quantreg']
try:
wb.sheets[sheetname].pictures[0].delete()
except:
pass
sheet = wb.sheets[sheetname]
fig = dict_output_quantfit['figs']
fig.savefig(path + '\\quantfit' + date.now().strftime('%Y%m%d-%H-%M') +
'.png')
try:
sheet.pictures.add(fig,
name='MyPlot_q',
update=True,
left=sheet.range('L7').left,
top=sheet.range('L7').top,
height=260,
width=1040)
action = 'Quantile figure saved'
except:
action = 'Unable to add figure to sheet ' + sheetname
# Add to log
tn = date.now().strftime('%Y-%m-%d %H:%M:%S')
log = pd.Series({'Time': tn, 'Action': action})
log_frame = log_frame.append(log, ignore_index=True)
# Write out log_frame
add_logsheet(wb, log_frame, colnum=3)