def printing(quarter_output):
'''function that compiles the summary sheet'''
master_list = list_of_masters_all[0:4]
milestone_master = all_milestone_data_bulk(list_of_masters_all[0].projects, list_of_masters_all[0])
for project_name in list_of_masters_all[0].projects:
doc = Document()
print(project_name)
heading = str(project_name)
name = str(project_name)
# TODO: change heading font size
# todo be able to change text size and font
intro = doc.add_heading(str(heading), 0)
intro.alignment = 1
intro.bold = True
y = doc.add_paragraph()
a = list_of_masters_all[0].data[project_name]['Senior Responsible Owner (SRO)']
if a == None:
a = 'TBC'
else:
a = a
b = list_of_masters_all[0].data[project_name]['SRO Phone No.']
if b == None:
b = 'TBC'
else:
b = b
y.add_run('SRO name: ' + str(a) + ', Tele: ' + str(b))
y = doc.add_paragraph()
a = list_of_masters_all[0].data[project_name]['Project Director (PD)']
if a == None:
a = 'TBC'
else:
a = a
b = list_of_masters_all[0].data[project_name]['PD Phone No.']
if b == None:
b = 'TBC'
else:
b = b
y.add_run('PD name: ' + str(a) + ', Tele: ' + str(b))
'''Start of table with DCA confidence ratings'''
table1 = doc.add_table(rows=1, cols=5)
table1.cell(0, 0).width = Cm(7)
'''quarter information in top row of table is here'''
for i, quarter in enumerate(quarter_list):
table1.cell(0, i+1).text = quarter
# '''setting row height - partially working'''
# # todo understand row height better
# row = table1.rows[0]
# tr = row._tr
# trPr = tr.get_or_add_trPr()
# trHeight = OxmlElement('w:trHeight')
# trHeight.set(qn('w:val'), str(200))
# trHeight.set(qn('w:hRule'), 'atLeast')
# trPr.append(trHeight)
SRO_conf_table_list = ['SRO DCA', 'Finance DCA', 'Benefits DCA', 'Resourcing DCA', 'Schedule DCA']
SRO_conf_key_list = ['Departmental DCA', 'SRO Finance confidence', 'SRO Benefits RAG', 'Overall Resource DCA - Now',
'SRO Schedule Confidence']
'''All SRO RAG rating placed in table'''
for i in range(0, len(master_list)+1):
table = doc.add_table(rows=1, cols=5)
table.cell(0, 0).width = Cm(7)
table.cell(0, 0).text = SRO_conf_table_list[i]
for x, master in enumerate(master_list):
try:
rating = convert_rag_text(master.data[project_name][SRO_conf_key_list[i]])
table.cell(0, x + 1).text = rating
cell_colouring(table.cell(0, x + 1), rating)
except (KeyError, TypeError):
table.cell(0, x + 1).text = 'N/A'
'''DCA Narrative text'''
doc.add_paragraph()
y = doc.add_paragraph()
heading = 'SRO Overall DCA Narrative'
y.add_run(str(heading)).bold = True
dca_a = list_of_masters_all[0].data[project_name]['Departmental DCA Narrative']
try:
dca_b = list_of_masters_all[1].data[project_name]['Departmental DCA Narrative']
except KeyError:
dca_b = dca_a
'''comparing text options'''
# compare_text_showall(dca_a, dca_b, doc)
compare_text_newandold(dca_a, dca_b, doc)
'''Finance section'''
y = doc.add_paragraph()
heading = 'Financial information'
y.add_run(str(heading)).bold = True
'''Financial Meta data'''
table1 = doc.add_table(rows=2, cols=5)
table1.cell(0, 0).text = 'Forecast Whole Life Cost (£m):'
table1.cell(0, 1).text = 'Percentage Spent:'
table1.cell(0, 2).text = 'Source of Funding:'
table1.cell(0, 3).text = 'Nominal or Real figures:'
table1.cell(0, 4).text = 'Full profile reported:'
wlc = round(list_of_masters_all[0].data[project_name]['Total Forecast'], 1)
table1.cell(1, 0).text = str(wlc)
# str(list_of_masters_all[0].data[project_name]['Total Forecast'])
#a = list_of_masters_all[0].data[project_name]['Total Forecast']
b = list_of_masters_all[0].data[project_name]['Pre 19-20 RDEL Forecast Total']
if b == None:
b = 0
c = list_of_masters_all[0].data[project_name]['Pre 19-20 CDEL Forecast Total']
if c == None:
c = 0
d = list_of_masters_all[0].data[project_name]['Pre 19-20 Forecast Non-Gov']
if d == None:
d = 0
e = b + c + d
try:
c = round(e / wlc * 100, 1)
except (ZeroDivisionError, TypeError):
c = 0
table1.cell(1, 1).text = str(c) + '%'
a = str(list_of_masters_all[0].data[project_name]['Source of Finance'])
b = list_of_masters_all[0].data[project_name]['Other Finance type Description']
if b == None:
table1.cell(1, 2).text = a
else:
table1.cell(1, 2).text = a + ' ' + str(b)
table1.cell(1, 3).text = str(list_of_masters_all[0].data[project_name]['Real or Nominal - Actual/Forecast'])
table1.cell(1, 4).text = ''
'''Finance DCA Narrative text'''
doc.add_paragraph()
y = doc.add_paragraph()
heading = 'SRO Finance Narrative'
y.add_run(str(heading)).bold = True
#TODO further testing on code down to 308. current hard code solution not ideal, plus not sure working properly yet
gmpp_narrative_keys = ['Project Costs Narrative', 'Cost comparison with last quarters cost narrative',
'Cost comparison within this quarters cost narrative']
fin_text_1 = combine_narrtives(project_name, list_of_masters_all[0], gmpp_narrative_keys)
try:
fin_text_2 = combine_narrtives(project_name, list_of_masters_all[1], gmpp_narrative_keys)
except KeyError:
fin_text_2 = fin_text_1
# if narrative == 'NoneNoneNone':
# fin_text = combine_narrtives(name, dictionary_1, bicc_narrative_keys)
# else:
# fin_text = narrative
compare_text_newandold(fin_text_1, fin_text_2, doc)
#compare_text_showall()
'''financial chart heading'''
y = doc.add_paragraph()
heading = 'Financial Analysis - Cost Profile'
y.add_run(str(heading)).bold = True
y = doc.add_paragraph()
y.add_run('{insert chart}')
'''milestone section'''
y = doc.add_paragraph()
heading = 'Planning information'
y.add_run(str(heading)).bold = True
'''Milestone Meta data'''
table1 = doc.add_table(rows=2, cols=4)
table1.cell(0, 0).text = 'Project Start Date:'
table1.cell(0, 1).text = 'Latest Approved Business Case:'
table1.cell(0, 2).text = 'Start of Operations:'
table1.cell(0, 3).text = 'Project End Date:'
key_dates = milestone_master[project_name]
#c = key_dates['Start of Project']
try:
c = tuple(key_dates['Start of Project'])[0]
c = datetime.datetime.strptime(c.isoformat(), '%Y-%M-%d').strftime('%d/%M/%Y')
except (KeyError, AttributeError):
c = 'Not reported'
table1.cell(1, 0).text = str(c)
table1.cell(1, 1).text = str(list_of_masters_all[0].data[project_name]['IPDC approval point'])
try:
a = tuple(key_dates['Start of Operation'])[0]
a = datetime.datetime.strptime(a.isoformat(), '%Y-%M-%d').strftime('%d/%M/%Y')
table1.cell(1, 2).text = str(a)
except (KeyError, AttributeError):
table1.cell(1, 2).text = 'Not reported'
#b = key_dates['Project End Date']
try:
b = tuple(key_dates['Project End Date'])[0]
b = datetime.datetime.strptime(b.isoformat(), '%Y-%M-%d').strftime('%d/%M/%Y')
except (KeyError, AttributeError):
b = 'Not reported'
table1.cell(1, 3).text = str(b)
# TODO: workout generally styling options for doc, paragraphs and tables
'''milestone narrative text'''
doc.add_paragraph()
y = doc.add_paragraph()
heading = 'SRO Milestone Narrative'
y.add_run(str(heading)).bold = True
mile_dca_a = list_of_masters_all[0].data[project_name]['Milestone Commentary']
if mile_dca_a == None:
mile_dca_a = 'None'
try:
mile_dca_b = list_of_masters_all[1].data[project_name]['Milestone Commentary']
if mile_dca_b == None:
mile_dca_b = 'None'
except KeyError:
mile_dca_b = mile_dca_a
# compare_text_showall()
compare_text_newandold(mile_dca_a, mile_dca_b, doc)
'''milestone chart heading'''
y = doc.add_paragraph()
heading = 'Project reported high-level milestones and schedule changes'
y.add_run(str(heading)).bold = True
y = doc.add_paragraph()
some_text = 'The below table presents all project reported remaining high-level milestones, with six months grace ' \
'from close of the current quarter. Milestones are sorted in chronological order. Changes in milestones' \
' dates in comparison to last quarter and baseline have been calculated and are provided.'
y.add_run(str(some_text)).italic = True
y = doc.add_paragraph()
y.add_run('{insert chart}')
doc.save(root_path/'output/{}_summary.docx'.format(project_name + quarter_output))
def overall_info(wb):
ws = wb.worksheets[3]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=2).value
if project_name in latest_quarter_project_names:
'''BC Stage'''
bc_stage = financial_analysis_masters_list[0].data[project_name][
'BICC approval point']
ws.cell(row=row_num,
column=3).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = financial_analysis_masters_list[1].data[
project_name]['BICC approval point']
if bc_stage != bc_stage_lst_qrt:
ws.cell(row=row_num,
column=3).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''planning stage'''
plan_stage = financial_analysis_masters_list[0].data[project_name][
'Project stage']
ws.cell(row=row_num, column=4).value = plan_stage
try:
plan_stage_lst_qrt = financial_analysis_masters_list[1].data[
project_name]['Project stage']
if plan_stage != plan_stage_lst_qrt:
ws.cell(row=row_num,
column=4).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''Total WLC'''
wlc_now = financial_analysis_masters_list[0].data[project_name][
'Total Forecast']
ws.cell(row=row_num, column=5).value = wlc_now
'''WLC variance against lst quarter'''
try:
wlc_lst_quarter = financial_analysis_masters_list[1].data[
project_name]['Total Forecast']
diff_lst_qrt = wlc_now - wlc_lst_quarter
if float(diff_lst_qrt) > 0.49 or float(diff_lst_qrt) < -0.49:
ws.cell(row=row_num, column=6).value = diff_lst_qrt
else:
ws.cell(row=row_num, column=6).value = '-'
percentage_change = (
(wlc_now - wlc_lst_quarter) / wlc_now) * 100
if percentage_change > 5 or percentage_change < -5:
ws.cell(row=row_num,
column=6).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
ws.cell(row=row_num, column=6).value = '-'
'''WLC variance against baseline quarter'''
wlc_baseline = financial_analysis_masters_list[fin_bc_index[
project_name][2]].data[project_name]['Total Forecast']
diff_bl = wlc_now - wlc_baseline
if float(diff_bl) > 0.49 or float(diff_bl) < -0.49:
ws.cell(row=row_num, column=7).value = diff_bl
else:
ws.cell(row=row_num, column=7).value = '-'
print(project_name)
percentage_change = ((wlc_now - wlc_baseline) / wlc_now) * 100
if percentage_change > 5 or percentage_change < -5:
ws.cell(row=row_num, column=7).font = Font(name='Arial',
size=10,
color='00fc2525')
'''vfm category now'''
if list_of_masters_all[0].data[project_name][
'VfM Category lower range'] is None:
vfm_cat = list_of_masters_all[0].data[project_name][
'VfM Category single entry']
ws.cell(row=row_num, column=8).value = vfm_cat
else:
vfm_cat = str(list_of_masters_all[0].data[project_name]['VfM Category lower range']) + ' - ' + \
str(list_of_masters_all[0].data[project_name]['VfM Category upper range'])
ws.cell(row=row_num, column=8).value = vfm_cat
'''vfm category baseline'''
try:
if list_of_masters_all[bc_index[project_name][2]].data[
project_name]['VfM Category lower range'] is None:
vfm_cat_baseline = list_of_masters_all[1].data[
project_name]['VfM Category single entry']
else:
vfm_cat_baseline = str(list_of_masters_all[bc_index[project_name][2]].data[project_name][
'VfM Category lower range']) + ' - ' + \
str(list_of_masters_all[bc_index[project_name][2]].data[project_name][
'VfM Category upper range'])
except KeyError:
try:
vfm_cat_baseline = list_of_masters_all[
bc_index[project_name]
[2]].data[project_name]['VfM Category single entry']
except KeyError:
try:
vfm_cat_baseline = list_of_masters_all[
bc_index[project_name]
[2]].data[project_name]['VfM Category']
except:
vfm_cat_baseline = None
if vfm_cat != vfm_cat_baseline:
if vfm_cat_baseline is None:
pass
else:
ws.cell(row=row_num,
column=8).font = Font(name='Arial',
size=10,
color='00fc2525')
'''full operation current date'''
try:
foc = tuple(current_milestones_all[project_name]
['Project End Date'])[0]
ws.cell(row=row_num, column=9).value = foc
if foc < bicc_date:
ws.cell(row=row_num, column=9).value = 'Completed'
else:
ws.cell(row=row_num, column=9).value = foc
except (KeyError, TypeError):
ws.cell(row=row_num, column=9).value = ''
'''fop against lst quarter'''
try:
foc_lst_qrt_diff = first_diff_data[project_name][
'Project End Date']
ws.cell(row=row_num, column=10).value = foc_lst_qrt_diff
if foc_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=10).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=10).value = ''
'''fop against baseline'''
try:
foc_bl_diff = second_diff_data[project_name][
'Project End Date']
ws.cell(row=row_num, column=11).value = foc_bl_diff
if foc_bl_diff > 86:
ws.cell(row=row_num,
column=11).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=11).value = ''
try:
ws.cell(row=row_num, column=12).value = highlight_close_dates_ipdc\
(concatenate_dates(list_of_masters_all[0].data[project_name]['Last time at BICC']))
ws.cell(row=row_num, column=13).value = highlight_close_dates_ipdc\
(concatenate_dates(list_of_masters_all[0].data[project_name]['Next at BICC']))
except TypeError:
print(project_name +
' caused error in last at / next at ipdc calculation')
'''IPA DCA rating'''
ipa_dca = convert_rag_text(
list_of_masters_all[0].data[project_name]['GMPP - IPA DCA'])
ws.cell(row=row_num, column=15).value = ipa_dca
if ipa_dca == 'None':
ws.cell(row=row_num, column=15).value = ''
'''DCA rating - this quarter'''
ws.cell(row=row_num, column=17).value = convert_rag_text(
list_of_masters_all[0].data[project_name]['Departmental DCA'])
'''DCA rating - last qrt'''
try:
ws.cell(row=row_num, column=19).value = convert_rag_text(
list_of_masters_all[1].data[project_name]
['Departmental DCA'])
except KeyError:
ws.cell(row=row_num, column=19).value = ''
'''DCA rating - 2 qrts ago'''
try:
ws.cell(row=row_num, column=20).value = convert_rag_text(
list_of_masters_all[2].data[project_name]
['Departmental DCA'])
except KeyError:
ws.cell(row=row_num, column=20).value = ''
'''DCA rating - 3 qrts ago'''
try:
ws.cell(row=row_num, column=21).value = convert_rag_text(
list_of_masters_all[3].data[project_name]
['Departmental DCA'])
except KeyError:
ws.cell(row=row_num, column=21).value = ''
'''DCA rating - baseline'''
try:
ws.cell(row=row_num, column=23).value = \
convert_rag_text(list_of_masters_all[bc_index[project_name][2]].data[project_name]
['Departmental DCA'])
except:
ws.cell(row=row_num, column=23).value = ''
'''list of columns with conditional formatting'''
list_columns = ['o', 'q', 's', 't', 'u', 'w']
'''same loop but the text is black. In addition these two loops go through the list_columns list above'''
for column in list_columns:
for i, dca in enumerate(rag_txt_list):
text = black_text
fill = fill_colour_list[i]
dxf = DifferentialStyle(font=text, fill=fill)
rule = Rule(type="containsText",
operator="containsText",
text=dca,
dxf=dxf)
for_rule_formula = 'NOT(ISERROR(SEARCH("' + dca + '",' + column + '5)))'
rule.formula = [for_rule_formula]
ws.conditional_formatting.add(column + '5:' + column + '60',
rule)
for row_num in range(2, ws.max_row + 1):
for col_num in range(5, ws.max_column + 1):
if ws.cell(row=row_num, column=col_num).value == 0:
ws.cell(row=row_num, column=col_num).value = '-'
return wb
def benefits_info(wb):
ws = wb.worksheets[2]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in latest_quarter_project_names:
'''BICC approval point'''
bc_stage = list_of_masters_all[0].data[project_name][
'BICC approval point']
ws.cell(row=row_num,
column=4).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = list_of_masters_all[1].data[project_name][
'BICC approval point']
if bc_stage != bc_stage_lst_qrt:
ws.cell(row=row_num,
column=4).font = Font(name='Arial',
size=10,
color='00fc2525')
except:
pass
'''Next stage'''
proj_stage = list_of_masters_all[0].data[project_name][
'Project stage']
ws.cell(row=row_num, column=5).value = proj_stage
try:
proj_stage_lst_qrt = list_of_masters_all[1].data[project_name][
'Project stage']
if proj_stage != proj_stage_lst_qrt:
ws.cell(row=row_num,
column=5).font = Font(name='Arial',
size=10,
color='00fc2525')
except:
pass
'''initial bcr'''
initial_bcr = list_of_masters_all[0].data[project_name][
'Initial Benefits Cost Ratio (BCR)']
ws.cell(row=row_num, column=6).value = initial_bcr
'''initial bcr baseline'''
try:
baseline_initial_bcr = \
list_of_masters_all[bc_index[project_name][2]].data[project_name]['Initial Benefits Cost Ratio (BCR)']
ws.cell(row=row_num, column=7).value = baseline_initial_bcr
if initial_bcr != baseline_initial_bcr:
if baseline_initial_bcr is None:
pass
else:
ws.cell(row=row_num,
column=6).font = Font(name='Arial',
size=10,
color='00fc2525')
ws.cell(row=row_num,
column=7).font = Font(name='Arial',
size=10,
color='00fc2525')
except TypeError:
ws.cell(row=row_num, column=7).value = ''
'''adjusted bcr'''
adjusted_bcr = list_of_masters_all[0].data[project_name][
'Adjusted Benefits Cost Ratio (BCR)']
ws.cell(row=row_num, column=8).value = adjusted_bcr
'''adjusted bcr baseline'''
try:
baseline_adjusted_bcr = \
list_of_masters_all[bc_index[project_name][2]].data[project_name]['Adjusted Benefits Cost Ratio (BCR)']
ws.cell(row=row_num, column=9).value = baseline_adjusted_bcr
if adjusted_bcr != baseline_adjusted_bcr:
if baseline_adjusted_bcr is None:
pass
else:
ws.cell(row=row_num,
column=8).font = Font(name='Arial',
size=10,
color='00fc2525')
ws.cell(row=row_num,
column=9).font = Font(name='Arial',
size=10,
color='00fc2525')
except TypeError:
ws.cell(row=row_num, column=9).value = ''
'''vfm category now'''
if list_of_masters_all[0].data[project_name][
'VfM Category lower range'] is None:
vfm_cat = list_of_masters_all[0].data[project_name][
'VfM Category single entry']
ws.cell(row=row_num, column=10).value = vfm_cat
else:
vfm_cat = str(list_of_masters_all[0].data[project_name]['VfM Category lower range']) + ' - ' + \
str(list_of_masters_all[0].data[project_name]['VfM Category upper range'])
ws.cell(row=row_num, column=10).value = vfm_cat
'''vfm category baseline'''
try:
if list_of_masters_all[bc_index[project_name][2]].data[
project_name]['VfM Category lower range'] is None:
vfm_cat_baseline = list_of_masters_all[bc_index[
project_name[2]]].data[project_name][
'VfM Category single entry']
ws.cell(row=row_num, column=11).value = vfm_cat_baseline
else:
vfm_cat_baseline = str(list_of_masters_all[bc_index[project_name][2]].data[project_name]['VfM Category lower range']) + ' - ' + \
str(list_of_masters_all[bc_index[project_name][2]].data[project_name]['VfM Category upper range'])
ws.cell(row=row_num, column=11).value = vfm_cat_baseline
except KeyError:
try:
vfm_cat_baseline = list_of_masters_all[
bc_index[project_name]
[2]].data[project_name]['VfM Category single entry']
ws.cell(row=row_num, column=11).value = vfm_cat_baseline
except KeyError:
vfm_cat_baseline = list_of_masters_all[bc_index[
project_name][2]].data[project_name]['VfM Category']
ws.cell(row=row_num, column=11).value = vfm_cat_baseline
if vfm_cat != vfm_cat_baseline:
if vfm_cat_baseline is None:
pass
else:
ws.cell(row=row_num,
column=10).font = Font(name='Arial',
size=10,
color='00fc2525')
ws.cell(row=row_num,
column=11).font = Font(name='Arial',
size=10,
color='00fc2525')
'''total monetised benefits'''
tmb = list_of_masters_all[0].data[project_name][
'Total BEN Forecast - Total Monetised Benefits']
ws.cell(row=row_num, column=12).value = tmb
'''tmb variance'''
baseline_tmb = list_of_masters_all[bc_index[project_name][2]].data[
project_name]['Total BEN Forecast - Total Monetised Benefits']
ws.cell(row=row_num, column=13).value = tmb - baseline_tmb
try:
percentage_change = ((tmb - baseline_tmb) / tmb) * 100
if percentage_change > 5 or percentage_change < -5:
ws.cell(row=row_num,
column=13).font = Font(name='Arial',
size=10,
color='00fc2525')
except ZeroDivisionError:
pass
'''in year benefits'''
'''benefits DCA rating - this quarter'''
ws.cell(row=row_num, column=16).value = convert_rag_text(
list_of_masters_all[0].data[project_name]['SRO Benefits RAG'])
'''benefits DCA rating - last qrt'''
try:
ws.cell(row=row_num, column=17).value = convert_rag_text(
list_of_masters_all[1].data[project_name]
['SRO Benefits RAG'])
except KeyError:
ws.cell(row=row_num, column=17).value = ''
'''benefits DCA rating - 2 qrts ago'''
try:
ws.cell(row=row_num, column=18).value = convert_rag_text(
list_of_masters_all[2].data[project_name]
['SRO Benefits RAG'])
except KeyError:
ws.cell(row=row_num, column=18).value = ''
'''benefits DCA rating - 3 qrts ago'''
try:
ws.cell(row=row_num, column=19).value = convert_rag_text(
list_of_masters_all[3].data[project_name]
['SRO Benefits RAG'])
except KeyError:
ws.cell(row=row_num, column=19).value = ''
'''benefits DCA rating - baseline'''
try:
ws.cell(row=row_num, column=20).value = \
convert_rag_text(list_of_masters_all[bc_index[project_name][2]].data[project_name]
['SRO Benefits RAG'])
except:
ws.cell(row=row_num, column=20).value = ''
'''list of columns with conditional formatting'''
list_columns = ['p', 'q', 'r', 's', 't']
'''loops below place conditional formatting (cf) rules into the wb. There are two as the dashboard currently has
two distinct sections/headings, which do not require cf. Therefore, cf starts and ends at the stated rows. this
is hard code that will need to be changed should the position of information in the dashboard change. It is an
easy change however'''
'''same loop but the text is black. In addition these two loops go through the list_columns list above'''
for column in list_columns:
for i, dca in enumerate(rag_txt_list):
text = black_text
fill = fill_colour_list[i]
dxf = DifferentialStyle(font=text, fill=fill)
rule = Rule(type="containsText",
operator="containsText",
text=dca,
dxf=dxf)
for_rule_formula = 'NOT(ISERROR(SEARCH("' + dca + '",' + column + '5)))'
rule.formula = [for_rule_formula]
ws.conditional_formatting.add(
'' + column + '5:' + column + '60', rule)
for row_num in range(2, ws.max_row + 1):
for col_num in range(5, ws.max_column + 1):
if ws.cell(row=row_num, column=col_num).value == 0:
ws.cell(row=row_num, column=col_num).value = '-'
return wb
def financial_info(wb):
ws = wb.worksheets[0]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in latest_quarter_project_names:
'''BC Stage'''
bc_stage = financial_analysis_masters_list[0].data[project_name][
'BICC approval point']
ws.cell(row=row_num,
column=4).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = financial_analysis_masters_list[1].data[
project_name]['BICC approval point']
if bc_stage != bc_stage_lst_qrt:
ws.cell(row=row_num,
column=4).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''planning stage'''
plan_stage = financial_analysis_masters_list[0].data[project_name][
'Project stage']
ws.cell(row=row_num, column=5).value = plan_stage
try:
plan_stage_lst_qrt = financial_analysis_masters_list[1].data[
project_name]['Project stage']
if plan_stage != plan_stage_lst_qrt:
ws.cell(row=row_num,
column=5).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''Total WLC'''
wlc_now = financial_analysis_masters_list[0].data[project_name][
'Total Forecast']
ws.cell(row=row_num, column=6).value = wlc_now
'''WLC variance against lst quarter'''
try:
wlc_lst_quarter = financial_analysis_masters_list[1].data[
project_name]['Total Forecast']
diff_lst_qrt = wlc_now - wlc_lst_quarter
if float(diff_lst_qrt) > 0.49 or float(diff_lst_qrt) < -0.49:
ws.cell(row=row_num, column=7).value = diff_lst_qrt
else:
ws.cell(row=row_num, column=7).value = '-'
percentage_change = (
(wlc_now - wlc_lst_quarter) / wlc_now) * 100
if percentage_change > 5 or percentage_change < -5:
ws.cell(row=row_num,
column=7).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
ws.cell(row=row_num, column=7).value = '-'
'''WLC variance against baseline quarter'''
wlc_baseline = financial_analysis_masters_list[fin_bc_index[
project_name][2]].data[project_name]['Total Forecast']
diff_bl = wlc_now - wlc_baseline
if float(diff_bl) > 0.49 or float(diff_bl) < -0.49:
ws.cell(row=row_num, column=8).value = diff_bl
else:
ws.cell(row=row_num, column=8).value = '-'
percentage_change = ((wlc_now - wlc_baseline) / wlc_now) * 100
if percentage_change > 5 or percentage_change < -5:
ws.cell(row=row_num, column=8).font = Font(name='Arial',
size=10,
color='00fc2525')
'''Aggregate Spent'''
'''Committed spend'''
'''remaining'''
'''P-Value'''
'''Contigency'''
ws.cell(row=row_num, column=13).value = \
financial_analysis_masters_list[0].data[project_name]['Overall contingency (£m)']
'''OB'''
ws.cell(row=row_num, column=14).value = \
financial_analysis_masters_list[0].data[project_name]['Overall figure for Optimism Bias (£m)']
'''financial DCA rating - this quarter'''
ws.cell(row=row_num, column=15).value = convert_rag_text(
financial_analysis_masters_list[0].data[project_name]
['SRO Finance confidence'])
'''financial DCA rating - last qrt'''
try:
ws.cell(row=row_num, column=16).value = convert_rag_text(
financial_analysis_masters_list[1].data[project_name]
['SRO Finance confidence'])
except KeyError:
ws.cell(row=row_num, column=16).value = ''
'''financial DCA rating - 2 qrts ago'''
try:
ws.cell(row=row_num, column=17).value = convert_rag_text(
financial_analysis_masters_list[2].data[project_name]
['SRO Finance confidence'])
except KeyError:
ws.cell(row=row_num, column=17).value = ''
'''financial DCA rating - 3 qrts ago'''
try:
ws.cell(row=row_num, column=18).value = convert_rag_text(
financial_analysis_masters_list[3].data[project_name]
['SRO Finance confidence'])
except KeyError:
ws.cell(row=row_num, column=18).value = ''
'''financial DCA rating - baseline'''
ws.cell(row=row_num, column=19).value = \
convert_rag_text(financial_analysis_masters_list[fin_bc_index[project_name][2]].data[project_name]
['SRO Finance confidence'])
'''list of columns with conditional formatting'''
list_columns = ['o', 'p', 'q', 'r', 's']
'''same loop but the text is black. In addition these two loops go through the list_columns list above'''
for column in list_columns:
for i, dca in enumerate(rag_txt_list):
text = black_text
fill = fill_colour_list[i]
dxf = DifferentialStyle(font=text, fill=fill)
rule = Rule(type="containsText",
operator="containsText",
text=dca,
dxf=dxf)
for_rule_formula = 'NOT(ISERROR(SEARCH("' + dca + '",' + column + '5)))'
rule.formula = [for_rule_formula]
ws.conditional_formatting.add('' + column + '5:' + column + '60',
rule)
for row_num in range(2, ws.max_row + 1):
for col_num in range(5, ws.max_column + 1):
if ws.cell(row=row_num, column=col_num).value == 0:
ws.cell(row=row_num, column=col_num).value = '-'
return wb
def schedule_info(wb):
ws = wb.worksheets[1]
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=3).value
if project_name in latest_quarter_project_names:
'''BICC approval point'''
bc_stage = list_of_masters_all[0].data[project_name][
'BICC approval point']
ws.cell(row=row_num,
column=4).value = convert_bc_stage_text(bc_stage)
try:
bc_stage_lst_qrt = list_of_masters_all[1].data[project_name][
'BICC approval point']
if bc_stage != bc_stage_lst_qrt:
ws.cell(row=row_num,
column=4).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''Next stage'''
plan_stage = list_of_masters_all[0].data[project_name][
'Project stage']
ws.cell(row=row_num, column=5).value = plan_stage
try:
plan_stage_lst_qrt = list_of_masters_all[1].data[project_name][
'Project stage']
if plan_stage != plan_stage_lst_qrt:
ws.cell(row=row_num,
column=5).font = Font(name='Arial',
size=10,
color='00fc2525')
except KeyError:
pass
'''Next milestone name and variance'''
local_milestone_dates = []
for x, key in enumerate(
current_milestones_ap_p[project_name].keys()):
date = tuple(current_milestones_ap_p[project_name][key])[0]
if date is None:
pass
elif date > bicc_date:
local_milestone_dates.append((date, x))
try:
next_milestone_name = list(
current_milestones_ap_p[project_name].keys())[
local_milestone_dates[0][1]]
ws.cell(row=row_num, column=6).value = next_milestone_name
next_milestone_date = local_milestone_dates[0][0]
ws.cell(row=row_num, column=7).value = next_milestone_date
except (TypeError, KeyError):
ws.cell(row=row_num, column=6).value = 'None Scheduled'
try:
lst_qrt_diff = first_diff_data[project_name][
next_milestone_name]
ws.cell(row=row_num, column=8).value = lst_qrt_diff
if lst_qrt_diff > 25:
ws.cell(row=row_num,
column=8).font = Font(name='Arial',
size=10,
color='00fc2525')
except (TypeError, KeyError):
ws.cell(row=row_num, column=8).value = ''
try:
bl_qrt_diff = second_diff_data[project_name][
next_milestone_name]
ws.cell(row=row_num, column=9).value = bl_qrt_diff
if bl_qrt_diff > 46:
ws.cell(row=row_num,
column=9).font = Font(name='Arial',
size=10,
color='00fc2525')
except (TypeError, KeyError):
ws.cell(row=row_num, column=9).value = ''
'''start of construction (soc) current date'''
try:
current_soc = tuple(current_milestones_all[project_name]
['Start of Construction/build'])[0]
ws.cell(row=row_num, column=10).value = current_soc
if current_soc < bicc_date:
ws.cell(row=row_num, column=10).value = 'Completed'
except (KeyError, TypeError):
ws.cell(row=row_num, column=10).value = ''
'''soc variance against lst quarter'''
try:
soc_lst_qrt_diff = first_diff_data[project_name][
'Start of Construction/build']
ws.cell(row=row_num, column=11).value = soc_lst_qrt_diff
if soc_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=11).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=11).value = ''
'''soc variance against baseline'''
try:
soc_bl_diff = second_diff_data[project_name][
'Start of Construction/build']
ws.cell(row=row_num, column=12).value = soc_bl_diff
if soc_bl_diff > 85:
ws.cell(row=row_num,
column=12).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=12).value = ''
'''start of operation (sop) current date'''
try:
current_sop = tuple(current_milestones_all[project_name]
['Start of Operation'])[0]
ws.cell(row=row_num, column=13).value = current_sop
if current_sop < bicc_date:
ws.cell(row=row_num, column=13).value = 'Completed'
except (KeyError, TypeError):
ws.cell(row=row_num, column=13).value = ''
'''sop variance against lst quarter'''
try:
sop_lst_qrt_diff = first_diff_data[project_name][
'Start of Operation']
ws.cell(row=row_num, column=14).value = sop_lst_qrt_diff
if sop_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=14).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=14).value = ''
'''sop variance against baseline'''
try:
sop_bl_diff = second_diff_data[project_name][
'Start of Operation']
ws.cell(row=row_num, column=15).value = sop_bl_diff
if sop_bl_diff > 86:
ws.cell(row=row_num,
column=15).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=15).value = ''
'''full operation current date'''
try:
foc = tuple(
current_milestones_all[project_name]['Full Operations'])[0]
ws.cell(row=row_num, column=16).value = foc
if foc < bicc_date:
ws.cell(row=row_num, column=16).value = 'Completed'
else:
ws.cell(row=row_num, column=16).value = foc
except (KeyError, TypeError):
ws.cell(row=row_num, column=16).value = ''
'''fop against lst quarter'''
try:
foc_lst_qrt_diff = first_diff_data[project_name][
'Full Operations']
ws.cell(row=row_num, column=17).value = foc_lst_qrt_diff
if foc_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=17).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=17).value = ''
'''fop against baseline'''
try:
foc_bl_diff = second_diff_data[project_name]['Full Operations']
ws.cell(row=row_num, column=18).value = foc_bl_diff
if foc_bl_diff > 86:
ws.cell(row=row_num,
column=18).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=18).value = ''
'''project end date'''
try:
ped = tuple(current_milestones_all[project_name]
['Project End Date'])[0]
ws.cell(row=row_num, column=19).value = ped
except (KeyError, TypeError):
ws.cell(row=row_num, column=19).value = ''
'''ped variance against lst quarter'''
try:
ped_lst_qrt_diff = first_diff_data[project_name][
'Project End Date']
ws.cell(row=row_num, column=20).value = ped_lst_qrt_diff
if ped_lst_qrt_diff > 46:
ws.cell(row=row_num,
column=20).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=20).value = ''
'''ped variance against baseline'''
try:
ped_bl_diff = second_diff_data[project_name][
'Project End Date']
ws.cell(row=row_num, column=21).value = ped_bl_diff
if ped_bl_diff > 86:
ws.cell(row=row_num,
column=21).font = Font(name='Arial',
size=10,
color='00fc2525')
except (KeyError, TypeError):
ws.cell(row=row_num, column=21).value = ''
'''schedule DCA rating - this quarter'''
ws.cell(row=row_num, column=22).value = convert_rag_text(
list_of_masters_all[0].data[project_name]
['SRO Schedule Confidence'])
'''schedule DCA rating - last qrt'''
try:
ws.cell(row=row_num, column=23).value = convert_rag_text(
list_of_masters_all[1].data[project_name]
['SRO Schedule Confidence'])
except KeyError:
ws.cell(row=row_num, column=23).value = ''
'''schedule DCA rating - 2 qrts ago'''
try:
ws.cell(row=row_num, column=24).value = convert_rag_text(
list_of_masters_all[2].data[project_name]
['SRO Schedule Confidence'])
except KeyError:
ws.cell(row=row_num, column=24).value = ''
'''schedule DCA rating - 3 qrts ago'''
try:
ws.cell(row=row_num, column=25).value = convert_rag_text(
list_of_masters_all[3].data[project_name]
['SRO Schedule Confidence'])
except KeyError:
ws.cell(row=row_num, column=25).value = ''
'''schedule DCA rating - baseline'''
try:
ws.cell(row=row_num, column=26).value = \
convert_rag_text(list_of_masters_all[bc_index[project_name][2]].data[project_name]
['SRO Schedule Confidence'])
except:
ws.cell(row=row_num, column=26).value = ''
'''list of columns with conditional formatting'''
list_columns = ['v', 'w', 'x', 'y', 'z']
'''same loop but the text is black. In addition these two loops go through the list_columns list above'''
for column in list_columns:
for i, dca in enumerate(rag_txt_list):
text = black_text
fill = fill_colour_list[i]
dxf = DifferentialStyle(font=text, fill=fill)
rule = Rule(type="containsText",
operator="containsText",
text=dca,
dxf=dxf)
for_rule_formula = 'NOT(ISERROR(SEARCH("' + dca + '",' + column + '5)))'
rule.formula = [for_rule_formula]
ws.conditional_formatting.add('' + column + '5:' + column + '60',
rule)
for row_num in range(2, ws.max_row + 1):
for col_num in range(5, ws.max_column + 1):
if ws.cell(row=row_num, column=col_num).value == 0:
ws.cell(row=row_num, column=col_num).value = '-'
return wb