'''
programme to create a dashboard which has meta data for each projects funding position.
returns a wb. data placed into a table
'''
#TODO tidy/up consider how can be used in the future
from openpyxl import load_workbook, Workbook
from bcompiler.utils import project_data_from_master
current_Q_dict = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_3_2018.xlsx')
def filter_dictionary(dictionary, category):
f_dict = {}
for x in dictionary:
if dictionary[x][category] != None:
f_dict[x] = dictionary[x]
return f_dict
def get_project_names(data):
project_name_list = []
for x in data:
project_name_list.append(x)
return project_name_list
'Project Lifecycle Stage', 'SRO Finance confidence', 'Last time at BICC', 'Next at BICC',
'GMPP - IPA DCA last quarter']
'''key of interest for previous quarter'''
dash_keys_previous_quarter = ['Departmental DCA']
keys_to_concatenate = ['Start of Operation', 'Project End Date', 'Last time at BICC',
'Next at BICC']
'''1) Provide file to empty dashboard document'''
wb = load_workbook(
'C:\\Users\\Standalone\\general\\masters folder\\summary_dashboard_docs\\Q1_2019\\dashboard master_Q4_1819.xlsx')
ws = wb.active
'''2) Provide file path to master data sets'''
q1_1920 = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_1_2019_wip_(18_7_19).xlsx')
q4_1819 = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_4_2018.xlsx')
p_names = list(q1_1920.keys())
#p_names = ['Digital Railway'] # can be useful for checking specific projects/the programme so leaving for now
'''3) Specify data of bicc that is discussing the report. NOTE: Python date format is (YYYY,MM,DD)'''
bicc_date = datetime.datetime(2019, 9, 9)
latest_q_dict = inital_dict(p_names, q1_1920, dash_keys)
last_q_dict = inital_dict(p_names, q4_1819, dash_keys_previous_quarter)
m_data = all_milestone_data_bulk(p_names, q1_1920)
latest_q_dict_2 = add_sop_pend_data(m_data, latest_q_dict)
merged_dict = final_dict(latest_q_dict_2, last_q_dict, keys_to_concatenate, 'Departmental DCA')
bicc_narrative_keys = [
'Project Costs Narrative RDEL', 'Project Costs Narrative CDEL'
]
all_keys = dash_keys + gmpp_narrative_keys + bicc_narrative_keys
'''key of interest for previous quarter'''
dash_keys_previous_quarter = ['SRO Finance confidence']
'''1) Provide file path to empty dashboard document'''
wb = load_workbook(
'C:\\Users\\Standalone\\general\\masters folder\\Financial\\Q1_1920\\'
'finance_dashboard master_Q1_1920.xlsx')
ws = wb.active
'''2) Provide file path to master data sets'''
data_one = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_1_2019_wip_'
'(18_7_19).xlsx')
data_two = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_4_2018.xlsx'
)
'''This code runs the programme and can be ignored as variables to enter into functions do not change'''
p_names = list(data_one.keys())
#p_names = ['Digital Railway'] # can be useful for checking specific projects/the programme so leaving for now
# '''3) Specify data of bicc that is discussing the report. NOTE: Python date format is (YYYY,MM,DD)'''
# bicc_date = datetime.datetime(2019, 9, 9)
latest_q_dict = inital_dict(p_names, data_one, all_keys)
last_q_dict = inital_dict(p_names, data_two, dash_keys_previous_quarter)
merged_dict = final_dict(latest_q_dict, last_q_dict, 'SRO Finance confidence')
wb = placing_excel(merged_dict, last_q_dict)
'''4) provide file path and specific name of output file.'''
compare_text_newandold(dca_text, dca_text_old, doc)
new_para = doc.add_paragraph()
heading = 'Annex E - narratives against whole life cost (WLC)'
new_para.add_run(str(heading)).bold = True
for project_name in dictionary_1:
new_para = doc.add_paragraph()
new_para.add_run(str(project_name)).bold = True
# new_para = doc.add_paragraph()
dca_text = dictionary_1[project_name]['narrative on wlc']
try:
dca_text_old = dictionary_2[project_name]['narrative on wlc']
except KeyError:
dca_text_old = dca_text
compare_text_newandold(dca_text, dca_text_old, doc)
return doc
current_Q_dict = project_data_from_master(
'C:\\Users\\Standalone\\Will\\ipa_annual_report_2019_narratives_2.xlsx')
last_Q_dict = project_data_from_master(
'C:\\Users\\Standalone\\Will\\ipa_annual_report_2019_narratives.xlsx')
current_Q_list = list(current_Q_dict.keys())
run = printing(current_Q_dict, last_Q_dict)
run.save(
'C://Users//Standalone//Will//2019_IPA_annual_report_narratives_annex_ver_2.docx'
)
# for loop for placing data into the worksheet
for row_num in range(2, ws.max_row + 1):
key = ws.cell(row=row_num, column=1).value
# this loop places all latest raw data into the worksheet
try:
if key in gmpp_data[name].keys():
ws.cell(row=row_num,
column=4 + i).value = gmpp_data[name][key]
else:
ws.cell(row=row_num,
column=4 + i).value = dft_data[name][key]
except KeyError:
pass
return dft_dm
dft_datamap = load_workbook(
"C:\\Users\\Standalone\\Will\\masters folder\\dms\\datamap_current_internal.xlsx"
)
gmpp_master = project_data_from_master(
"C:\\Users\\Standalone\\Will\\masters folder\\core data\\gmpp_master_3"
"_2018.xlsx")
dft_master = project_data_from_master(
"C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_3_2018.xlsx"
)
output = create_internal_master(gmpp_master, dft_master, dft_datamap)
output.save("C:\\Users\\Standalone\\Will\\gmpp_data_output.xlsx")
try:
ws.cell(row=row_num,
column=5 + i).value = last_gmpp[name][key]
ws.cell(row=row_num, column=5 + i).font = red_text
except KeyError:
pass
return gmpp_wb
# list_gmpp_static_keys = ['SRO Last Name', 'SRO First Name', 'PD Last Name', 'PD First Name', 'First Name',
# 'Last Name', 'Project Costs Narrative']
latest_dm = load_workbook(
"C:\\Users\\Standalone\\general\\masters folder\\gmpp_reporting_docs\\datamaps\\"
"gmpp_datamap_2.xlsx") # 1) place file path to gmpp data map here
dft_master = project_data_from_master(
"C:\\Users\\Standalone\\general\\masters folder\\core data\\master_1_2019_wip.xlsx"
)
# 2) place file path to latest quarter master above
old_hs2_master = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\gmpp_reporting_docs\\'
'Q2_1819\\Q2_1819_DfT_gmpp_data_loader_for_macro_FINAL.xlsx')
# 3) place file path to last quarter gmpp master here
run = create_master(latest_dm, dft_master, old_hs2_master)
run.save("C:\\Users\\Standalone\\general\\q1_1920_gmpp_data.xlsx")
# Place file path for whether you want file to be saved and what you'd like it to be called here
ws.cell(row=row_num, column=col_start).value = 'None'
col_start += 2
ws.cell(row=1, column=1, value='Project')
ws.cell(row=1, column=2, value='Last @ BICC')
ws.cell(row=1, column=3, value='Manual amend: Last @ BICC')
ws.cell(row=1, column=4, value='Next @ BICC')
ws.cell(row=1, column=5, value='Manual amend: Next @ BICC')
return wb
'''1) Specify file paths to master data sources - normally latest and last quarter data'''
one = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_1_2019_wip.xlsx'
) # file path to latest quarter
two = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_4_2018.xlsx'
) # file path to last quarter
one_list = list(one.keys())
two_list = list(two.keys())
overall_list = sorted(list(set(one_list + two_list)))
data_interest = ['Last time at BICC', 'Next at BICC']
run = data_return(overall_list, data_interest, one, two)
'''2) Specify file path and name of document to be saved'''
run.save(
'C:\\Users\\Standalone\\general\\masters folder\\summary_dashboard_docs\\Q1_2019\\q1_1920_bicc_dates_check.xlsx'
from openpyxl import load_workbook, Workbook
from bcompiler.utils import project_data_from_master
def place_in_excel(list):
wb = Workbook()
ws = wb.active
ws.cell(row=1, column=1).value = 'MISSING'
for i, key in enumerate(list):
ws.cell(row=i + 2, column=1).value = key
return wb
gmpp_datamap = project_data_from_master(
"C:\\Users\\Standalone\\Will\\masters folder\\dms\\gmpp_dm_merged_excel_master.xlsx"
)
#internal_datamap = project_data_from_master("C:\\Users\\Standalone\\Will\\masters folder\\dms\\datamap_current_internal"
# ".xlsx")
gmpp_keys = gmpp_datamap['sheet'].keys()
#internal_keys = internal_datamap['template_sheet'].keys()
#missing_keys = [x for x in internal_keys if x not in gmpp_keys]
#output = place_in_excel(missing_keys)
#output.save("C:\\Users\\Standalone\\Will\\test.xlsx")
def ref_class_fore(master_dict, project_title, start_row, output_wb):
# output_wb = Workbook()
data = project_data_from_master(master_dict)
project_data = data[project_title]
cells_we_want_to_capture = [
'Reporting period (GMPP - Snapshot Date)', 'Approval MM1',
'Approval MM1 Forecast / Actual', 'Approval MM3',
'Approval MM3 Forecast / Actual', 'Approval MM10',
'Approval MM10 Forecast / Actual', 'Project MM18',
'Project MM18 Forecast - Actual', 'Project MM19',
'Project MM19 Forecast - Actual', 'Project MM20',
'Project MM20 Forecast - Actual', 'Project MM21',
'Project MM21 Forecast - Actual'
]
output_list = []
for item in project_data.items():
if item[0] in cells_we_want_to_capture:
output_list.append(item)
output_list = list(enumerate(output_list, start=1))
print(output_list)
output_list2 = [
output_list[2][1][1], output_list[4][1][1], output_list[6][1][1],
output_list[8][1][1], output_list[10][1][1], output_list[12][1][1],
output_list[14][1][1]
]
SOBC = output_list2[0]
print('SOBC', SOBC)
OBC = output_list2[1]
print('OBC', OBC)
FBC = output_list2[2]
print('FBC', FBC)
start_project = output_list2[3]
print('Start of Project', start_project)
start_construction = output_list2[4]
print('Start of construction', start_construction)
start_ops = output_list2[5]
print('Start of Ops', start_ops)
end_project = output_list2[6]
print('End of project', end_project)
try:
time_delta1 = (SOBC - start_project).days
except TypeError:
time_delta1 = None
print(time_delta1)
try:
time_delta2 = (OBC - SOBC).days
except TypeError:
time_delta2 = None
print(time_delta2)
try:
time_delta3 = (FBC - OBC).days
except TypeError:
time_delta3 = None
print(time_delta3)
try:
time_delta4 = (start_construction - FBC).days
except TypeError:
time_delta4 = None
print(time_delta4)
try:
time_delta5 = (start_ops - start_construction).days
except TypeError:
time_delta5 = None
print(time_delta5)
try:
time_delta6 = (end_project - start_ops).days
except TypeError:
time_delta6 = None
print(time_delta6)
ws = output_wb.active
for x in output_list[:3]:
ws.cell(row=2, column=x[0] + 1, value=x[1][0])
ws.cell(row=start_row + 1, column=x[0] + 1, value=x[1][1])
ws.cell(row=start_row + 1, column=x[0] + 2, value=time_delta1)
for x in output_list[3:5]:
ws.cell(row=2, column=x[0] + 2, value=x[1][0])
ws.cell(row=start_row + 1, column=x[0] + 2, value=x[1][1])
ws.cell(row=start_row + 1, column=x[0] + 3, value=time_delta2)
for x in output_list[5:7]:
ws.cell(row=2, column=x[0] + 3, value=x[1][0])
ws.cell(row=start_row + 1, column=x[0] + 3, value=x[1][1])
ws.cell(row=start_row + 1, column=x[0] + 4, value=time_delta3)
for x in output_list[7:9]:
ws.cell(row=2, column=x[0] + 4, value=x[1][0])
ws.cell(row=start_row + 1, column=x[0] + 4, value=x[1][1])
# ws.cell(row=start_row+1, column=x[0]+5, value=time_delta3)
for x in output_list[9:11]:
ws.cell(row=2, column=x[0] + 5, value=x[1][0])
ws.cell(row=start_row + 1, column=x[0] + 5, value=x[1][1])
ws.cell(row=start_row + 1, column=x[0] + 6, value=time_delta4)
for x in output_list[11:13]:
ws.cell(row=2, column=x[0] + 6, value=x[1][0])
ws.cell(row=start_row + 1, column=x[0] + 6, value=x[1][1])
ws.cell(row=start_row + 1, column=x[0] + 7, value=time_delta5)
for x in output_list[13:15]:
ws.cell(row=2, column=x[0] + 7, value=x[1][0])
ws.cell(row=start_row + 1, column=x[0] + 7, value=x[1][1])
ws.cell(row=start_row + 1, column=x[0] + 8, value=time_delta6)
for x in output_list[1:3]:
ws.cell(row=start_row + 13, column=x[0] + 1, value=x[1][1])
# ws.cell(row=start_row+10, column=x[0]+2, value=x[1][1])
ws.cell(row=start_row + 13, column=5, value=time_delta1)
ws.cell(row=start_row + 13, column=6, value=1)
for x in output_list[3:5]:
ws.cell(row=start_row + 13 + len(master_list),
column=x[0] - 1,
value=x[1][1])
ws.cell(row=start_row + 13 + len(master_list),
column=5,
value=time_delta2)
ws.cell(row=start_row + 13 + len(master_list), column=6, value=2)
for x in output_list[5:7]:
ws.cell(row=start_row + 13 + (len(master_list) * 2),
column=x[0] - 3,
value=x[1][1])
ws.cell(row=start_row + 13 + (len(master_list) * 2),
column=5,
value=time_delta3)
ws.cell(row=start_row + 13 + (len(master_list) * 2), column=6, value=3)
for x in output_list[9:11]:
ws.cell(row=start_row + 13 + (len(master_list) * 3),
column=x[0] - 7,
value=x[1][1])
ws.cell(row=start_row + 13 + (len(master_list) * 3),
column=5,
value=time_delta4)
ws.cell(row=start_row + 13 + (len(master_list) * 3), column=6, value=4)
for x in output_list[11:13]:
ws.cell(row=start_row + 13 + (len(master_list) * 4),
column=x[0] - 9,
value=x[1][1])
ws.cell(row=start_row + 13 + (len(master_list) * 4),
column=5,
value=time_delta5)
ws.cell(row=start_row + 13 + (len(master_list) * 4), column=6, value=5)
for x in output_list[13:15]:
ws.cell(row=start_row + 13 + (len(master_list) * 5),
column=x[0] - 11,
value=x[1][1])
ws.cell(row=start_row + 13 + (len(master_list) * 5),
column=5,
value=time_delta6)
ws.cell(row=start_row + 13 + (len(master_list) * 5), column=6, value=6)
return output_wb
dm_list.append(key)
# for loop for placing data into the worksheet
for row_num in range(2, ws.max_row + 1):
key = ws.cell(row=row_num, column=1).value
# this loop places all latest raw data into the worksheet
try:
if key in dft_data[name].keys():
ws.cell(row=row_num,
column=4 + i).value = dft_data[name][key]
else:
pass
#ws.cell(row=row_num, column=4 + i).value = dft_data[name][key]
except KeyError:
pass
return gmpp_dm
gmpp_datamap = load_workbook(
"C:\\Users\\Standalone\\Will\\masters folder\\dms\\dm_merged_all_excel_master.xlsx"
)
dft_master = project_data_from_master(
"C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_non_gmpp_testing.xlsx"
)
output = create_internal_master(dft_master, gmpp_datamap)
output.save(
"C:\\Users\\Standalone\\Will\\output_internal_data_gmpp_format.xlsx")
# output_list.remove('Hexagon')
return output_list
master_list = [
'C:\\Users\\Standalone\\Will\\masters folder\\master_3_2016.xlsx',
'C:\\Users\\Standalone\\Will\\masters folder\\master_4_2016.xlsx',
'C:\\Users\\Standalone\\Will\\masters folder\\master_1_2017.xlsx',
'C:\\Users\\Standalone\\Will\\masters folder\\master_2_2017.xlsx',
'C:\\Users\\Standalone\\Will\\masters folder\\master_3_2017_full_report.xlsx'
]
# 'C:\\Users\\Standalone\\Will\\masters folder\\master_4_2017.xlsx']
master_list.reverse()
m1 = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_4_2017.xlsx')
m5 = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_3_2016.xlsx')
m1_list = get_project_names(m1)
m5_list = get_project_names(m5)
joint_list = m1_list + m5_list
project_list = list(set(joint_list))
project_list = [
'M20 Lorry Area', 'M20 Op Stack Interim Solution (Project BROCK)'
]
# print(project_list)
# wb = Workbook()
for p in project_list:
wb = Workbook()
else:
print('could find ' + str(project_name))
return wb
def projects_missing(wb, master):
master_list = master.keys()
worksheet_list = []
ws = wb.active
for row_num in range(2, ws.max_row + 1):
project_name = ws.cell(row=row_num, column=6).value
worksheet_list.append(project_name)
missing = [x for x in master_list if x not in worksheet_list]
print(missing)
print(worksheet_list)
master = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_2_2018_for_ncip_comparison_to_delete.xlsx'
)
ncip_wb = load_workbook('C:\\Users\\Standalone\\Will\\Ncip_comparison.xlsx')
populated_ncip_wb = placing_excel(ncip_wb, master)
populated_ncip_wb.save(
'C:\\Users\\Standalone\\Will\\ncip_comparison_ver3.xlsx')
projects_missing(ncip_wb, master)
def get_wlc(data, key):
output_dict = {}
for name in (data):
total = data[name][key]
output_dict[name] = total
return output_dict
red_text = Font(color="FF0000")
'''INSTRUCTIONS FOR RUNNING PROGRAMME'''
'''1) specify file paths to where master data for analysis is stored.'''
latest_q_data = project_data_from_master(
"C:\\Users\\Standalone\\general\\masters folder\\core data\\master_1_2019"
"_wip_(18_7_19).xlsx")
other_q_data = project_data_from_master(
"C:\\Users\\Standalone\\general\\masters folder\\core data\\master_4_2018.xlsx"
)
'''2) decide which output you require'''
'''in year cost lists is chosen through the cost list. No not change.'''
cost_list = [
' RDEL Forecast Total', ' CDEL Forecast Total', ' Forecast Non-Gov'
]
'''OPTION ONE - for comparing in year costs'''
'''in year income list is chosen through the income list. No not change.'''
income_list = [' Forecast - Income both Revenue and Capital']
'''chose financial year of interest. change accordingly. needs to be in format of YY-YY'''
year_interest = '19-20'
'''get fy information by entering the appropriate variables'''
ws.conditional_formatting.add('D1:D100', rule)
# assign the icon set to a rule
first = FormatObject(type='num', val=-1)
second = FormatObject(type='num', val=0)
third = FormatObject(type='num', val=1)
iconset = IconSet(iconSet='3Arrows', cfvo=[first, second, third], percent=None, reverse=None)
rule = Rule(type='iconSet', iconSet=iconset)
ws.conditional_formatting.add('D1:D100', rule)
return wb
'''1) Provide file path to empty dashboard document'''
wb = load_workbook(
'C:\\Users\\Standalone\\Will\\ipa_annual_report_dashboard_master.xlsx')
ws = wb.active
'''2) Provide file path to master data sets'''
data_one = project_data_from_master(
'C:\\Users\\Standalone\\Will\\DfT AR 2019 Data.xlsx')
data_two = project_data_from_master(
'C:\\Users\\Standalone\\Will\\ipa_annual_report_2018.xlsx')
p_names = list(data_one.keys())
wb = placing_excel(data_one, data_two)
'''4) provide file path and specific name of output file.'''
wb.save(
'C:\\Users\\Standalone\\Will\\test.xlsx')
def run(project_name, milestone_data, td_data):
wb = Workbook()
ws = wb.active
print(project_name)
parser_1 = placing_date_in_excel_single(ws, project_name, milestone_data,
td_data)
approval_point = zero[project_name]['BICC approval point']
build_chart_single(parser_1, project_name, approval_point, td_data)
return wb
'''master files are loaded here'''
zero = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_2_2018.xlsx')
one = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_1_2018.xlsx')
two = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_4_2017.xlsx')
three = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_3_2017.xlsx')
four = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_2_2017.xlsx')
five = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_1_2017.xlsx')
six = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_4_2016.xlsx')
last = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_3_2016.xlsx')
'''master files put into a list'''
a = count_list[0][1] * 0
b = count_list[1][1] * 50
c = count_list[2][1] * 100
score = a + b + c
maximum = total * 100
result = score / maximum
print(result)
'''1) Specify file path to core data sets of interest'''
current_Q_dict = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\'
'master_1_2019_wip_(18_7_19).xlsx')
last_Q_dict = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_4_2018.xlsx'
)
current_Q_list = get_project_names(current_Q_dict)
#current_Q_list = ['High Speed Rail Programme (HS2)']
sro_dca = create_dict('Departmental DCA', current_Q_dict, last_Q_dict)
finance_dca = create_dict('SRO Finance confidence', current_Q_dict,
last_Q_dict)
resource_dca = create_dict('Overall Resource DCA - Now', current_Q_dict,
last_Q_dict)
benefits_dca = create_dict('SRO Benefits RAG', current_Q_dict, last_Q_dict)
ws.cell(row=row_num, column=col_start).font = red_text
except KeyError:
pass
else:
ws.cell(row=row_num, column=col_start).value = 'None'
col_start += 1
ws.cell(row=1, column=1, value='Project')
ws.cell(row=1, column=2, value='Last quarter reported BC')
ws.cell(row=1, column=3, value='This quarter reported BC')
ws.cell(row=1, column=4, value='Manual edit to BC stage')
return wb
'''1) Specify path to master data information - usually for latest and last quarter'''
one = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_4_2018_wip.xlsx')
two = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_3_2018.xlsx')
dictionary_list = [two, one]
one_list = list(one.keys())
two_list = list(two.keys())
overall_list = sorted(list(set(one_list + two_list)))
data_interest = 'BICC approval point'
'''running programme'''
run = data_return(overall_list, data_interest, dictionary_list)
'''2) Specify file path and name of document to be saved. I suggest it is {last q info}_bc_stage_from_master'''
def run(project_name, milestone_data, td_data):
wb = Workbook()
ws = wb.active
parser_1 = placing_date_in_excel_single(ws, project_name, milestone_data,
td_data)
approval_point = q4_1819[project_name]['BICC approval point']
build_chart_single(parser_1, project_name, approval_point, td_data)
return wb
'''master files are loaded here'''
q1_1920 = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\core data\\Hs2_NPR_Q1_1918_draft.xlsx'
)
q4_1819 = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_4_2018.xlsx'
)
q3_1819 = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_3_2018.xlsx'
)
q2_1819 = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_2_2018.xlsx'
)
q1_1819 = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_1_2018.xlsx'
)
q4_1718 = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_4_2017.xlsx'
quarter_stamp = quarter_data_dict[proj_name]['Reporting period (GMPP - Snapshot Date)']
output_list.append(quarter_stamp)
for rag in rag_list:
total = 0
for proj_name in quarter_data_dict.keys():
if proj_name in remove_projects:
pass
else:
proj_rag = quarter_data_dict[proj_name][rag_of_interest]
if proj_rag == rag:
proj_total = quarter_data_dict[proj_name]['Total Forecast']
total = total + proj_total
else:
pass
output_list.append((rag, total))
return output_list
remove_projects = ['West Coast Partnership Franchise', 'South Eastern Rail Franchise Competition',
'Rail Franchising Programme', 'East Midlands Franchise', 'HS2 Phase 2b',
'HS2 Phase1', 'HS2 Phase2a']
rag_list_five = ['Red', 'Amber/Red', 'Amber', 'Amber/Green', 'Green']
rag_list_three = ['Red', 'Amber', 'Green']
chosen_q_data = project_data_from_master("C:\\Users\\Standalone\\Will\\masters folder\\core data\\master_4_2018.xlsx")
run = get_totals(chosen_q_data, rag_list_three, 'SRO Finance confidence')
print(run)
'21-22 Forecast - Income both Revenue and Capital',
'22-23 Forecast - Income both Revenue and Capital',
'23-24 Forecast - Income both Revenue and Capital',
'24-25 Forecast - Income both Revenue and Capital',
'25-26 Forecast - Income both Revenue and Capital',
'26-27 Forecast - Income both Revenue and Capital',
'27-28 Forecast - Income both Revenue and Capital',
'28-29 Forecast - Income both Revenue and Capital',
'Unprofiled Forecast Income'
]
all_data_lists = capture_rdel + capture_cdel + capture_ng + capture_income
'''INSTRUCTION FOR RUNNING PROGRAMME'''
'''1) load all master quarter data files here'''
q1_1920 = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_1_2019_wip_'
'(18_7_19).xlsx')
q4_1819 = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_4_2018.xlsx'
)
q3_1819 = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_3_2018.xlsx'
)
q2_1819 = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_2_2018.xlsx'
)
q1_1819 = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_1_2018.xlsx'
)
q4_1718 = project_data_from_master(
'C:\\Users\\Standalone\\general\\masters folder\\core data\\master_4_2017.xlsx'
output_list.remove(email)
final_list = []
for email in output_list:
for address in email_addresses:
if address in email:
final_list.append(email)
return final_list
def parse_into_excle(email_list):
wb = Workbook()
ws = wb.active
for i, email in enumerate(email_list):
ws.cell(row=i + 1, column=1).value = email
return wb
master = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\master_2_2018.xlsx')
email_keys = ['Working Contact Email', 'SRO Email', 'PD Email']
addresses = ['dft', 'dvsa', 'highwaysengland']
test = get_emails(master, email_keys, addresses)
parse = parse_into_excle(test)
parse.save('C:\\Users\\Standalone\\Will\\email_list.xlsx')
def merging_dms(old_dm, new_dm):
wb = Workbook()
ws = wb.active
new_old_keys = new_dm[
'Old GMPP'] # reference to the old key values in the new datamap
old_old_keys = old_dm[
'gmpp_template_cell_reference'] # reference to the old key values in the old datamap
for i, key_1 in enumerate(new_old_keys):
for key_2 in old_old_keys:
if new_old_keys[key_1] == 'GMPP Return\'!' + old_old_keys[key_2]:
ws.cell(row=i + 2, column=4).value = key_2
print(i, key_1)
return wb
old = project_data_from_master(
"C:\\Users\\Standalone\\Will\\masters folder\\gmpp_reporting_docs\\datamaps\\gmpp_datamap_2.0.xlsx"
)
new = project_data_from_master(
"C:\\Users\\Standalone\\Will\\masters folder\\gmpp_reporting_docs\\datamaps\\new_gmpp_datamap.xlsx"
)
merged_wb = merging_dms(old, new)
merged_wb.save(
"C:\\Users\\Standalone\\Will\\masters folder\\gmpp_reporting_docs\\datamaps\\test_merge.xlsx"
)
column=11 + i).value = last_gmpp[name][key]
ws.cell(row=row_num, column=11 + i).font = red_text
return gmpp_wb
list_gmpp_static_keys = [
'SRO Last Name', 'SRO First Name', 'PD Last Name', 'PD First Name',
'First Name', 'Last Name', 'Project Costs Narrative'
]
latest_dm = load_workbook(
"C:\\Users\\Standalone\\Will\\masters folder\\gmpp_reporting_docs\\datamaps\\"
"gmpp_datamap_FINAL.xlsx") # 1) place file path to gmpp data map here
dft_master = project_data_from_master(
"C:\\Users\\Standalone\\Will\\masters folder\\gmpp_reporting_docs\\"
"gmpp_master_Q3_EWR.xlsx")
# 2) place file path to latest quarter master above
last_gmpp_master = project_data_from_master(
'C:\\Users\\Standalone\\Will\\masters folder\\gmpp_reporting_docs\\Q2_1819'
'\\Q2_1819_DfT_gmpp_data_loader_for_macro_FINAL.xlsx')
# 3) place file path to last quarter gmpp master here
run = create_master(latest_dm, dft_master, last_gmpp_master)
run.save(
"C:\\Users\\Standalone\\Will\\masters folder\\gmpp_reporting_docs\\gmpp_Q3_2018_19_EWR.xlsx"
)
# Place file path for whether you want file to be saved and what you'd like it to be called here
