''' 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