def level_1_income( ws_cur, r_next, row_sales, category_name, level_1_month, level_1_ytd, category_lookup, ): c1 = ws_cur.cell(row=r_next, column=6, value=category_name) c1 = ws_cur.cell(row=r_next, column=1, value=-level_1_month.get(category_lookup, 0)) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' formula1 = "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})".format( col1=dcc.get(1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=2, value=formula1) c1.number_format = '0.0%' c1 = ws_cur.cell(row=r_next, column=3) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' formula1 = "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})".format( col1=dcc.get(3), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=4, value=formula1) c1.number_format = '0.0%' formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(col_a=dcc.get(1), col_b=dcc.get(3), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=5, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' c1 = ws_cur.cell(row=r_next, column=7, value=-level_1_ytd.get(category_lookup, 0)) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' formula1 = "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})".format( col1=dcc.get(7), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=8, value=formula1) c1.number_format = '0.0%' c1 = ws_cur.cell(row=r_next, column=9) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' formula1 = "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})".format( col1=dcc.get(9), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=10, value=formula1) c1.number_format = '0.0%' formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format(col_a=dcc.get(7), col_b=dcc.get(9), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=11, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' return ws_cur
def get_cover_page(wb, template_directory, date_end): wb_cur = wb ws_cur = wb_cur.create_sheet('Cover') ht_logo = Image(template_directory + 'ht_logo.png') ht_logo.anchor(ws_cur.cell(row=7, column=3), 'absolute') ws_cur.add_image(ht_logo) companies_logo = Image(template_directory + 'companies_logo.png') companies_logo.anchor(ws_cur.cell(row=31, column=1), 'absolute') ws_cur.add_image(companies_logo) for c in range(1, 13): ws_cur.column_dimensions[(dcc.get(c))].width = 8.5 c1 = ws_cur.cell(row=17, column=1, value='Consolidated Financial Results') c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true', size=22) ws_cur.merge_cells(start_row=17, end_row=17, start_column=1, end_column=12) c1 = ws_cur.cell(row=22, column=1, value=datetime.strftime(date_end, '%B %Y')) c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true', size=22) ws_cur.merge_cells(start_row=22, end_row=22, start_column=1, end_column=12) ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID ws_cur.page_setup.fitToPage = True ws_cur.page_setup.fitToHeight = 1 ws_cur.page_setup.fitToWidth = 1 ws_cur.print_options.horizontalCentered = True ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5, footer=.5) return wb_cur
def build_consolidated_p_and_l(wb, date_start, date_ytd_start, dict_db, date_end): wb_cur = wb level_1 = retrieve_level_1(date_start, date_ytd_start, dict_db) level_2 = retrieve_level_2(date_start, date_ytd_start, dict_db) level_1_month = {} level_1_ytd = {} for x in range(0, len(level_1)): level_1_month[level_1[x][0]] = level_1[x][1] level_1_ytd[level_1[x][0]] = level_1[x][2] level_2_month = {} level_2_ytd = {} for x in range(0, len(level_2)): level_2_month[level_2[x][0]] = level_2[x][1] level_2_ytd[level_2[x][0]] = level_2[x][2] ws_cur = wb_cur.create_sheet('P&L Consolidated') c1 = ws_cur.cell(row=1, column=1, value='Consolidated P&L - (HT/MYOP/RAC)') c1 = ws_cur.cell(row=2, column=1, value='Income Statement') report_date = '{d1:%B} {d1.day}, {d1.year}'.format(d1=date_end) c1 = ws_cur.cell(row=3, column=1, value=report_date) row_header_1 = 5 border_tb = Border(top=Side(style='thin'), bottom=Side(style='thin')) border_tb2 = Border(top=Side(style='thin'), bottom=Side(style='double', )) border_all = Border(top=Side(style='thin'), bottom=Side(style='thin'), left=Side(style='thin'), right=Side(style='thin')) for r in range(1, 4): ws_cur.merge_cells(start_row=r, end_row=r, start_column=1, end_column=11) c1 = ws_cur.cell(row=r, column=1) c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') c1 = ws_cur.cell(row=row_header_1, column=1, value='Month') ws_cur.merge_cells(start_row=row_header_1, end_row=row_header_1, start_column=1, end_column=5) c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') c1 = ws_cur.cell(row=row_header_1, column=7, value='Year to Date') ws_cur.merge_cells(start_row=row_header_1, end_row=row_header_1, start_column=7, end_column=11) c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') row_header_2 = 6 c1 = ws_cur.cell(row=row_header_2, column=1, value='Actual') c1 = ws_cur.cell(row=row_header_2, column=2, value='% Sales') c1 = ws_cur.cell(row=row_header_2, column=3, value='Budget') c1 = ws_cur.cell(row=row_header_2, column=4, value='% Sales') c1 = ws_cur.cell(row=row_header_2, column=5, value='Variance') for c in range(1, 6): c1 = ws_cur.cell(row=row_header_2, column=c) c1.fill = PatternFill(start_color='1e90ff', end_color='1e90ff', fill_type='solid') c1.font = Font(bold='true', color='f8f8ff') c1.alignment = Alignment(horizontal='center') c1 = ws_cur.cell(row=row_header_2, column=7, value='Actual') c1 = ws_cur.cell(row=row_header_2, column=8, value='% Sales') c1 = ws_cur.cell(row=row_header_2, column=9, value='Budget') c1 = ws_cur.cell(row=row_header_2, column=10, value='% Sales') c1 = ws_cur.cell(row=row_header_2, column=11, value='Variance') for c in range(7, 12): c1 = ws_cur.cell(row=row_header_2, column=c) c1.fill = PatternFill(start_color='1e90ff', end_color='1e90ff', fill_type='solid') c1.font = Font(bold='true', color='f8f8ff') c1.alignment = Alignment(horizontal='center') for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=row_header_2, column=c) c1.border = border_all #------Revenue------ r_next = row_header_2 + 1 row_sales = r_next ws_cur = level_1_income(ws_cur, r_next, row_sales, 'Total Revenue', level_1_month, level_1_ytd, 'Sales') for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Cost------ r_next += 1 ws_cur = level_1_expense(ws_cur, r_next, row_sales, 'Cost of Sales', level_1_month, level_1_ytd, 'Cost') for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Gross Margin------ r_next += 1 row_gm = r_next c1 = ws_cur.cell(row=r_next, column=6, value='Gross Margin') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "={col_letter}{r1}-{col_letter}{r2}".format( col_letter=dcc.get(c), r1=r_next - 2, r2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Adjustments to Margin------ r_next += 2 c1 = ws_cur.cell(row=r_next, column=6, value='Adjustments to Margin:') c1.font = Font(bold='true') #------Rebates------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Rebates', level_2_month, level_2_ytd, 'Rebates') #------Cash Discounts------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Cash Discounts', level_2_month, level_2_ytd, 'Cash Discounts') #------Customer Discounts------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Customer Discounts', level_2_month, level_2_ytd, 'Customer Discounts') #------Cost of Goods Adjustments------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Cost of Goods Adjustments', level_2_month, level_2_ytd, 'Cost of Goods Adjustments') #------Other------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Other', level_2_month, level_2_ytd, 'Other Adjustments to Margin') #------Total Adjustments to Margin------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Total Adjustments to Margin') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format( col_letter=dcc.get(c), r1=r_next - 5, r2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Adjusted Gross Margin------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Adjusted Gross Margin') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "={col_letter}{r1}+{col_letter}{r2}".format( col_letter=dcc.get(c), r1=r_next - 9, r2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Commissions------ r_next += 2 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Commissions', level_2_month, level_2_ytd, 'Commissions') #------Total Commissions------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Total Commissions') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format( col_letter=dcc.get(c), r1=r_next - 1, r2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_b}{r_cur}-{col_a}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #% To Margin r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='% To Margin') for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = ("" "=if({col1}{r_gm}=0,0,{col1}{r_cur}/{col1}{r_gm})").format( col1=dcc.get(c), r_gm=row_gm, r_cur=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(italic='true') #------Other Personnel Expenses------ r_next += 2 c1 = ws_cur.cell(row=r_next, column=6, value='Other Personnel Expenses:') c1.font = Font(bold='true') #------Salaries------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Salaries', level_2_month, level_2_ytd, 'Salaries') #------Bonuses------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Bonuses', level_2_month, level_2_ytd, 'Bonuses') #------Benefits------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Benefits', level_2_month, level_2_ytd, 'Benefits') #------Payroll Taxes------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Payroll Taxes', level_2_month, level_2_ytd, 'Payroll Taxes') #------Total Other Personnel Expenses------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Total Other Personnel Expenses') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format( col_letter=dcc.get(c), r1=r_next - 4, r2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_b}{r_cur}-{col_a}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Other Operating Expenses------ r_next += 2 c1 = ws_cur.cell(row=r_next, column=6, value='Other Operating Expenses:') c1.font = Font(bold='true') #------Travel------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Travel', level_2_month, level_2_ytd, 'Travel') #------Meals & Entertainment------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Meals & Entertainment', level_2_month, level_2_ytd, 'Meals & Entertainment') #------Facility Rent------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Facility Rent', level_2_month, level_2_ytd, 'Facility Rent') #------Utilities------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Utilities', level_2_month, level_2_ytd, 'Utilities') #------Facility Maintenance------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Facility Maintenance', level_2_month, level_2_ytd, 'Facility Maintenance') #------Fleet Fuel------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Fleet Fuel', level_2_month, level_2_ytd, 'Fleet Fuel') #------Fleet Repair & Maintenance------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Fleet Repair & Maintenance', level_2_month, level_2_ytd, 'Fleet Repair & Maintenance') #------Vehicle Rent Expense------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Vehicle Rent Expense', level_2_month, level_2_ytd, 'Vehicle Rent Expense') #------Outsourced Delivery------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Outsourced Delivery', level_2_month, level_2_ytd, 'Outsourced Delivery') #------Outbound Freight------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Outbound Freight', level_2_month, level_2_ytd, 'Outbound Freight') #------Outbound Freight Rebates------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Outbound Freight Rebates', level_2_month, level_2_ytd, 'Outbound Freight Rebates') #------Equipment Rental------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Equipment Rental', level_2_month, level_2_ytd, 'Equipment Rental') #------MRO Supplies------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'MRO Supplies', level_2_month, level_2_ytd, 'MRO Supplies') #------Office Freight & Postage------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Office Freight & Postage', level_2_month, level_2_ytd, 'Office Freight & Postage') #------Outside Services------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Outside Services', level_2_month, level_2_ytd, 'Outside Services') #------IT Services & Maintenance------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'IT Services & Maintenance', level_2_month, level_2_ytd, 'IT Services & Maintenance') #------Telecom------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Telecom Expense', level_2_month, level_2_ytd, 'Telecom Expense') #------Business Insurance------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Business Insurance', level_2_month, level_2_ytd, 'Business Insurance') #------Training, Dues & Subscriptions------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Training, Dues & Subscriptions', level_2_month, level_2_ytd, 'Training, Dues & Subscriptions') #------Advertising & Marketing------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Advertising & Marketing', level_2_month, level_2_ytd, 'Advertising & Marketing') #------Diversity Partner Fees------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Diversity Partner Fees', level_2_month, level_2_ytd, 'Diversity Partner Fees') #------e-Commerce Fees------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'E-Commerce Fees', level_2_month, level_2_ytd, 'e-Commerce Fees') #------Professional Fees------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Professional Fees', level_2_month, level_2_ytd, 'Professional Fees') #------Bank Charges------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Bank Charges', level_2_month, level_2_ytd, 'Bank Charges') #------Bad Debt Expense------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Bad Debt Expense', level_2_month, level_2_ytd, 'Bad Debt Expense') #------Other Taxes------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Other Taxes', level_2_month, level_2_ytd, 'Other Taxes') #------Other Expenses------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Other Expenses', level_2_month, level_2_ytd, 'Other Expenses') #------Total Other Operating Expenses------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Total Other Operating Expenses') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format( col_letter=dcc.get(c), r1=r_next - 27, r2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_b}{r_cur}-{col_a}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Total Operating Expenses------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Total Operating Expenses') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "={col_letter}{r1}+{col_letter}{r2}+{col_letter}{r3}".format( col_letter=dcc.get(c), r1=r_next - 39, r2=r_next - 31, r3=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_b}{r_cur}-{col_a}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Other Income / (Expense)------ r_next += 2 c1 = ws_cur.cell(row=r_next, column=6, value='Other Income / (Expense):') c1.font = Font(bold='true') #------Corporate Allocation------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Corporate Allocation', level_2_month, level_2_ytd, 'Corporate Allocation') #------Deferred Partner Revenue------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Deferred Partner Revenue', level_2_month, level_2_ytd, 'Deferred Partner Revenue') #------Excluded Expenses------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Excluded Expenses', level_2_month, level_2_ytd, 'Excluded Expenses') #------Misc. Income------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Misc. Income', level_2_month, level_2_ytd, 'Misc. Income') #------Gain/(Loss) on Fixed Asset Disposal------ r_next += 1 ws_cur = level_2_income(ws_cur, r_next, row_sales, 'Gain / (Loss) Fixed Asset Disposal', level_2_month, level_2_ytd, 'Gain / (Loss) Fixed Asset Disposal') #------Total Other Income / (Expense)------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Total Other Income / (Expense)') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "=sum({col_letter}{r1}:{col_letter}{r2})".format( col_letter=dcc.get(c), r1=r_next - 5, r2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #-----EBITDA------ r_next += 2 c1 = ws_cur.cell(row=r_next, column=6, value='EBITDA') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "={col_letter}{r1}-{col_letter}{r2}+{col_letter}{r3}".format( col_letter=dcc.get(c), r1=r_next - 52, r2=r_next - 10, r3=r_next - 2) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') c1.fill = PatternFill(start_color='ffd700', end_color='ffd700', fill_type='solid') #------Interest Expense------ r_next += 2 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Interest Expense', level_2_month, level_2_ytd, 'Interest Expense') #------Depreciation------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Depreciation', level_2_month, level_2_ytd, 'Depreciation') #------Amortization------ r_next += 1 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'Amortization', level_2_month, level_2_ytd, 'Amortization') #------Net Income Before Taxes------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Net Income Before Taxes') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = ("={col_letter}{r3}-" "sum({col_letter}{r1}:{col_letter}{r2})").format( col_letter=dcc.get(c), r1=r_next - 3, r2=r_next - 1, r3=r_next - 5) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------State Income Taxes------ r_next += 2 ws_cur = level_2_expense(ws_cur, r_next, row_sales, 'State Income Taxes', level_2_month, level_2_ytd, 'State Income Taxes') #------Net Income------ r_next += 1 c1 = ws_cur.cell(row=r_next, column=6, value='Net Income') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = ("={col_letter}{r1}-{col_letter}{r2}").format( col_letter=dcc.get(c), r1=r_next - 3, r2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb2 for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') #------Excluded Expenses------ r_next += 2 c1 = ws_cur.cell(row=r_next, column=6, value='EXCLUDED EXPENSES') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "=-{col_letter}{r1}".format(col_letter=dcc.get(c), r1=r_next - 15) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') c1.fill = PatternFill(start_color='ffd700', end_color='ffd700', fill_type='solid') #------Adjusted EBITDA------ r_next += 2 c1 = ws_cur.cell(row=r_next, column=6, value='ADJUSTED EBITDA') #calculation for c in [x for x in range(1, 12) if x in [1, 3, 7, 9]]: formula1 = "={col_letter}{r1}+{col_letter}{r2}".format( col_letter=dcc.get(c), r1=r_next - 12, r2=r_next - 2) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #% sales for c in [x for x in range(2, 11, 2) if x != 6]: formula1 = ( "" "=if({col1}{r_sales}=0,0,{col1}{r_cur}/{col1}{r_sales})").format( col1=dcc.get(c - 1), r_sales=row_sales, r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #variance for c in range(5, 12, 6): formula1 = "={col_a}{r_cur}-{col_b}{r_cur}".format( col_a=dcc.get(c - 4), col_b=dcc.get(c - 2), r_cur=r_next) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #border for c in [x for x in range(1, 12) if x != 6]: c1 = ws_cur.cell(row=r_next, column=c) c1.border = border_tb for c in range(1, 12): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') c1.fill = PatternFill(start_color='ffd700', end_color='ffd700', fill_type='solid') #Change font size and name for r in range(1, ws_cur.max_row + 1): for c in range(1, ws_cur.max_column + 1): c1 = ws_cur.cell(row=r, column=c) c1.font = Font(bold=c1.font.bold, italic=c1.font.italic, name='Arial', size=8, color=c1.font.color) #Row height for r in range(1, ws_cur.max_row + 1): ws_cur.row_dimensions[r].height = 11.5 #Column width for x in [x for x in range(1, 12) if x in [1, 3, 5, 7, 9, 11]]: ws_cur.column_dimensions[dcc.get(x)].width = 11 for x in [x for x in range(1, 12) if x in [2, 4, 8, 10]]: ws_cur.column_dimensions[dcc.get(x)].width = 6.5 ws_cur.column_dimensions[dcc.get(6)].width = 27 #Page Setup ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID ws_cur.page_setup.fitToPage = True ws_cur.page_setup.fitToHeight = 1 ws_cur.page_setup.fitToWidth = 1 ws_cur.print_options.horizontalCentered = True ws_cur.add_print_title(6) ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5, footer=.5) date_cur = datetime.strftime( datetime.today(), "%a {dt.month}/{dt.day}/%Y %I:%M %p".format(dt=datetime.today())) ws_cur.oddFooter.right.text = date_cur ws_cur.oddFooter.right.size = 8 ws_cur.oddFooter.right.font = 'Arial' #Freeze Panes c1 = ws_cur.cell(row=7, column=1) ws_cur.freeze_panes = c1 return wb_cur
def sum_col_1(row1, row2, col1): formula1 = "=sum({col1}{row1}:{col1}{row2})".format( row1=row1, row2=row2, col1=dcc.get(col1)) return formula1
def sum_row_1(row, col1, col2): formula1 = "=sum({col_letter1}{row1}:{col_letter2}{row1})".format( col_letter1=dcc.get(col1), row1=row, col_letter2=dcc.get(col2)) return formula1
def pct_of_total1(col1, r_total, r_row): formula1 = "=if({col1}{r_total}=0,0,{col1}{r_row}/{col1}{r_total})".format( col1=dcc.get(col1-1), r_total=r_total, r_row=r_row) return formula1
def build_report(dict_db, date_end, wb): sales_and_cost = gross_margin_data(dict_db, date_end) pl_trend_data = profit_loss_data(dict_db, date_end) wb_cur = wb ws_cur = wb_cur.create_sheet('P&L_Trend_HT_MYOP') ws_cur.cell(row=1, column=1, value='Consolidated P&L Trend- (HT/MYOP)') ws_cur.cell(row=2, column=1, value='Income Statement') ws_cur.cell(row=3, column=1, value='For the 12 Months Ending {d1}'.format( d1=datetime.strftime(date_end, '%B %d, %Y'))) for r in range(1, 4): c1 = ws_cur.cell(row=r, column=1) wb_cur = ExcelWidget(wb_cur).arial_8_bold_center(c1) ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=14) ws_cur.merge_cells(start_row=2, end_row=2, start_column=1, end_column=14) ws_cur.merge_cells(start_row=3, end_row=3, start_column=1, end_column=14) date_next = date_end + relativedelta(days=-date_end.day + 1) trend_period = [] for i in range(0, 12): trend_period.append(date_next) date_next = date_next + relativedelta(months=-1) trend_period.sort(key=None, reverse=False) row_next = 5 c = 2 #Header for d in range(0, len(trend_period)): c1 = ws_cur.cell(row=row_next, column=c, value=datetime.strftime(trend_period[d], '%b %Y')) wb_cur = ExcelWidget(wb_cur).header_blue_arial_8(c1) c += 1 c1 = ws_cur.cell(row=row_next, column=14, value='Total') wb_cur = ExcelWidget(wb_cur).header_blue_arial_8(c1) c_last = ws_cur.max_column #Gross Margin row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Total Revenue') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) c = 2 for d in range(0, len(trend_period)): c1 = ws_cur.cell(row=row_next, column=c, value=sum([ x[2] for x in sales_and_cost if x[0] == trend_period[d] if x[1] == 'Sales' ])) wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) c += 1 c1 = ws_cur.cell(row=row_next, column=c) formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Cost of Sales') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) c = 2 for d in range(0, len(trend_period)): c1 = ws_cur.cell(row=row_next, column=c, value=sum([ x[2] for x in sales_and_cost if x[0] == trend_period[d] if x[1] == 'Cost' ])) wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) c += 1 c1 = ws_cur.cell(row=row_next, column=c) formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Gross Margin') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_gm = row_next for c in range(2, 15): c1 = ws_cur.cell(row=row_next, column=c, value='={c1}{r1}-{c1}{r2}'.format(c1=dcc.get(c), r1=row_next - 2, r2=row_next - 1)) wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #Adj to Margin row_next += 2 c1 = ws_cur.cell(row=row_next, column=1, value='Adjustments to Margin:') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_next += 1 adjustment_to_margin = [ 'Rebates', 'Cash Discounts', 'Customer Discounts', 'Cost of Goods Adjustments', 'Other Adjustments to Margin' ] for x in adjustment_to_margin: c1 = ws_cur.cell(row=row_next, column=1, value=x) wb_cur = ExcelWidget(wb_cur).arial_8(c1) c = 2 for d in trend_period: amount = sum([ p[3] for p in pl_trend_data if p[0] == d if p[1] == 'Adjustment to Margin' if p[2] == x ]) c1 = ws_cur.cell(row=row_next, column=c, value=amount) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) c += 1 formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1 = ws_cur.cell(row=row_next, column=c, value=formula1) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Total Adjustments to Margin') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) formula1 = ExcelWidget(wb_cur).col_total( row_next - len(adjustment_to_margin), row_next - 1, c) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #Adjusted Gross Margin row_next += 1 row_agm = row_next c1 = ws_cur.cell(row=row_next, column=1, value='Adjusted Gross Margin') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) cs1 = ws_cur.cell(row=row_gm, column=c) cs2 = ws_cur.cell(row=row_next - 1, column=c) formula1 = ExcelWidget(wb_cur).sum_2(cs1, cs2) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #Commission row_next += 2 c1 = ws_cur.cell(row=row_next, column=1, value='Commissions') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) commission = ['Commissions'] for x in commission: c1 = ws_cur.cell(row=row_next, column=1, value=x) wb_cur = ExcelWidget(wb_cur).arial_8(c1) c = 2 for d in trend_period: amount = sum([ p[3] for p in pl_trend_data if p[0] == d if p[1] == 'Commissions' if p[2] == x ]) c1 = ws_cur.cell(row=row_next, column=c, value=amount) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) c += 1 formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1 = ws_cur.cell(row=row_next, column=c, value=formula1) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Total Commissions') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_commission = row_next for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) formula1 = ExcelWidget(wb_cur).col_total(row_next - 1, row_next - 1, c) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='% To Margin') wb_cur = ExcelWidget(wb_cur).arial_8_italic(c1) for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) cdivisor = ws_cur.cell(row=row_next - 1, column=c) cdividend = ws_cur.cell(row=row_gm, column=c) formula1 = ExcelWidget(wb_cur).divide_2(cdivisor, cdividend) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_italic_percent1(c1) #Other Personnel Expense row_next += 2 c1 = ws_cur.cell(row=row_next, column=1, value='Other Personnel Expenses:') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_next += 1 personnel_exp = ['Salaries', 'Bonuses', 'Benefits', 'Payroll Taxes'] for x in personnel_exp: c1 = ws_cur.cell(row=row_next, column=1, value=x) wb_cur = ExcelWidget(wb_cur).arial_8(c1) c = 2 for d in trend_period: amount = sum([ p[3] for p in pl_trend_data if p[0] == d if p[1] == 'Other Personnel Expense' if p[2] == x ]) c1 = ws_cur.cell(row=row_next, column=c, value=amount) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) c += 1 formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1 = ws_cur.cell(row=row_next, column=c, value=formula1) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Total Other Personnel Expenses') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_oth_pers_exp = row_next for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) formula1 = ExcelWidget(wb_cur).col_total(row_next - len(personnel_exp), row_next - 1, c) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #Other Operating Expense row_next += 2 c1 = ws_cur.cell(row=row_next, column=1, value='Other Operating Expenses:') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_next += 1 opex = [ 'Travel', 'Meals & Entertainment', 'Facility Rent', 'Utilities', 'Facility Maintenance', 'Fleet Fuel', 'Fleet Repair & Maintenance', 'Vehicle Rent Expense', 'Outsourced Delivery', 'Outbound Freight', 'Outbound Freight Rebates', 'Equipment Rental', 'MRO Supplies', 'Office Freight & Postage', 'Outside Services', 'IT Services & Maintenance', 'Telecom Expense', 'Business Insurance', 'Training, Dues & Subscriptions', 'Advertising & Marketing', 'Diversity Partner Fees', 'e-Commerce Fees', 'Professional Fees', 'Bank Charges', 'Bad Debt Expense', 'Other Taxes', 'Other Expenses' ] for x in opex: c1 = ws_cur.cell(row=row_next, column=1, value=x) wb_cur = ExcelWidget(wb_cur).arial_8(c1) c = 2 for d in trend_period: amount = sum([ p[3] for p in pl_trend_data if p[0] == d if p[1] == 'Other Operating Expense' if p[2] == x ]) c1 = ws_cur.cell(row=row_next, column=c, value=amount) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) c += 1 formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1 = ws_cur.cell(row=row_next, column=c, value=formula1) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Total Other Operating Expenses') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) formula1 = ExcelWidget(wb_cur).col_total(row_next - len(opex), row_next - 1, c) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #Total Opex row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Total Operating Expenses') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_total_opex = row_next for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) cs1 = ws_cur.cell(row=row_commission, column=c) cs2 = ws_cur.cell(row=row_oth_pers_exp, column=c) cs3 = ws_cur.cell(row=row_next - 1, column=c) formula1 = ExcelWidget(wb_cur).sum_3(cs1, cs2, cs3) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #Other Income/(Expense) row_next += 2 c1 = ws_cur.cell(row=row_next, column=1, value='Other Income / (Expense):') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_next += 1 oth_inc_exp = [ 'Corporate Allocation', 'Deferred Partner Revenue', 'Excluded Expenses', 'Misc. Income', 'Gain / (Loss) Fixed Asset Disposal' ] for x in oth_inc_exp: c1 = ws_cur.cell(row=row_next, column=1, value=x) wb_cur = ExcelWidget(wb_cur).arial_8(c1) c = 2 for d in trend_period: amount = sum([ p[3] for p in pl_trend_data if p[0] == d if p[1] == 'Other Income / (Expense)' if p[2] == x ]) c1 = ws_cur.cell(row=row_next, column=c, value=amount) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) c += 1 formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1 = ws_cur.cell(row=row_next, column=c, value=formula1) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Total Other Income / (Expense)') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) formula1 = ExcelWidget(wb_cur).col_total(row_next - len(oth_inc_exp), row_next - 1, c) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #EBITDA row_next += 2 c1 = ws_cur.cell(row=row_next, column=1, value='EBITDA') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) row_ebitda = row_next for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) c_agm = ws_cur.cell(row=row_agm, column=c) c_opex = ws_cur.cell(row=row_total_opex, column=c) c_oth_income = ws_cur.cell(row=row_next - 2, column=c) formula1 = ExcelWidget(wb_cur).ebitda_calc(c_agm, c_opex, c_oth_income) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_yellow_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #IDA row_next += 2 ida = ['Interest Expense', 'Depreciation', 'Amortization'] for x in ida: c1 = ws_cur.cell(row=row_next, column=1, value=x) wb_cur = ExcelWidget(wb_cur).arial_8(c1) c = 2 for d in trend_period: amount = sum([ p[3] for p in pl_trend_data if p[0] == d if p[1] == 'ITDA' if p[2] == x ]) c1 = ws_cur.cell(row=row_next, column=c, value=amount) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) c += 1 formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1 = ws_cur.cell(row=row_next, column=c, value=formula1) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) row_next += 1 c1 = ws_cur.cell(row=row_next, column=1, value='Net Income Before Taxes') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) ebitda = ws_cur.cell(row=row_ebitda, column=c) ida_start = ws_cur.cell(row=row_next - len(ida), column=c) ida_end = ws_cur.cell(row=row_next - 1, column=c) formula1 = ExcelWidget(wb_cur).net_income_before_tax( ebitda, ida_start, ida_end) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) row_next += 2 tax = ['State Income Taxes'] for x in tax: c1 = ws_cur.cell(row=row_next, column=1, value=x) wb_cur = ExcelWidget(wb_cur).arial_8(c1) c = 2 for d in trend_period: amount = sum([ p[3] for p in pl_trend_data if p[0] == d if p[1] == 'ITDA' if p[2] == x ]) c1 = ws_cur.cell(row=row_next, column=c, value=amount) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) c += 1 formula1 = ExcelWidget(wb_cur).row_total(2, c_last - 1, row_next) c1 = ws_cur.cell(row=row_next, column=c, value=formula1) wb_cur = ExcelWidget(wb_cur).arial_8_number0(c1) row_next += 1 #Net Income c1 = ws_cur.cell(row=row_next, column=1, value='Net Income') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) c_nibt = ws_cur.cell(row=row_next - 3, column=c) c_tax = ws_cur.cell(row=row_next - 1, column=c) formula1 = ExcelWidget(wb_cur).subtract_2(c_nibt, c_tax) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb2(c1) #Excluded Expenses row_next += 2 c1 = ws_cur.cell(row=row_next, column=1, value='EXCLUDED EXPENSES') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) for r in range(row_next, row_next - 30, -1): if ws_cur.cell(row=r, column=1).value == 'Excluded Expenses': row_excluded_expense = r break for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) c_excluded_expense = ws_cur.cell(row=row_excluded_expense, column=c) formula1 = ( ExcelWidget(wb_cur).excluded_expense_inverse(c_excluded_expense)) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_yellow_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #Adjusted EBITDA row_next += 2 c1 = ws_cur.cell(row=row_next, column=1, value='ADJUSTED EBITDA') wb_cur = ExcelWidget(wb_cur).arial_8_bold(c1) for c in range(2, c_last + 1): c1 = ws_cur.cell(row=row_next, column=c) c_ebitda = ws_cur.cell(row=row_ebitda, column=c) c_excl_exp = ws_cur.cell(row=row_next - 2, column=c) formula1 = ExcelWidget(wb_cur).sum_2(c_ebitda, c_excl_exp) c1.value = formula1 wb_cur = ExcelWidget(wb_cur).arial_8_yellow_bold_number0(c1) wb_cur = ExcelWidget(wb_cur).border_tb(c1) #Row height for r in range(1, ws_cur.max_row + 1): ws_cur.row_dimensions[r].height = 11.5 #Column Width ws_cur.column_dimensions['A'].width = 27 for c in range(2, 14): ws_cur.column_dimensions[dcc.get(c)].width = 10 ws_cur.column_dimensions['N'].width = 10.5 #Page Setup wb_cur = ExcelWidget(wb_cur).page_setup_portrait_1X1(ws_cur, 5) #Freeze Panes c1 = ws_cur.cell(row=6, column=1) ws_cur.freeze_panes = c1 return wb_cur
def excelupdate(Workbook, dict_db, date_end, month_actual, month_budget): #Get data from data warehouse gl_data = retrieve1(dict_db=dict_db) wb_new = Workbook ws_cur = wb_new.create_sheet('EBITDA Cash Analysis') col_actual_start = 2 col_actual_end = col_actual_start + len(month_actual) - 1 col_budget_start = col_actual_end + 1 col_budget_end = col_budget_start + len(month_budget) - 1 c1 = ws_cur.cell(row=1, column=1, value="Consolidated (HT/MYOP/RAC)") c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true') c1 = ws_cur.cell(row=2, column=1, value="EBITDA Cash Analysis") c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true') str_period = ("For Period Ending {d1:%B} " "{d1.day}, {d1.year}").format(d1=date_end) c1 = ws_cur.cell(row=3, column=1, value=str_period) c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true') row_date = 7 ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=14) ws_cur.merge_cells(start_row=2, end_row=2, start_column=1, end_column=14) ws_cur.merge_cells(start_row=3, end_row=3, start_column=1, end_column=14) for c in range(2, 14): c1 = ws_cur.cell(row=row_date, column=c, value=calendar.month_name[c - 1]) c1.font = Font(underline='single', bold='true') c1.alignment = Alignment(horizontal='center') c1 = ws_cur.cell(row=row_date, column=14, value='Total ' + str(date_end.year)) c1.font = Font(underline='single', bold='true') c1.alignment = Alignment(horizontal='center') col_last = ws_cur.max_column #Actual Header c1 = ws_cur.cell(row=row_date - 1, column=col_actual_start, value='Actual') c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') ws_cur.merge_cells(start_row=row_date - 1, end_row=row_date - 1, start_column=col_actual_start, end_column=col_actual_end) for c in range(col_actual_start, col_actual_end + 1): c1 = ws_cur.cell(row=row_date - 1, column=c) c1.fill = PatternFill(start_color='b0e0e6', end_color='b0e0e6', fill_type='solid') #Budget Header c1 = ws_cur.cell(row=row_date - 1, column=col_budget_start, value='Budget') c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') ws_cur.merge_cells(start_row=row_date - 1, end_row=row_date - 1, start_column=col_budget_start, end_column=col_budget_end) for c in range(col_budget_start, col_budget_end + 1): c1 = ws_cur.cell(row=row_date - 1, column=c) c1.fill = PatternFill(start_color='bdb76b', end_color='bdb76b', fill_type='solid') r_next = row_date + 1 #Sales c1 = ws_cur.cell(row=r_next, column=1, value='Sales') sales = [] for x in range(1, len(month_actual) + 1): sales.append( sum(r[1] for r in gl_data if r[0].month == x if r[0].year == date_end.year)) for c in range(2, 2 + len(month_actual)): c1 = ws_cur.cell(row=r_next, column=c, value=sales[c - 2]) adj_gross_margin = [] for x in range(1, len(month_actual) + 1): adj_gross_margin.append( sum(r[2] for r in gl_data if r[0].month == x if r[0].year == date_end.year)) r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Adjusted Gross Margin') for c in range(2, 2 + len(month_actual)): c1 = ws_cur.cell(row=r_next, column=c, value=adj_gross_margin[c - 2]) for c in range(2, 15): for r in range(r_next - 1, r_next + 1): c1 = ws_cur.cell(row=r, column=c) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #Adjusted Gross Margin % r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='%') for c in range(2, 15): formula1 = ("=if({col_letter}{row1}=0,0," "{col_letter}{row2}/{col_letter}{row1})").format( col_letter=dcc.get(c), row1=r_next - 2, row2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #Adjusted EBITDA adj_ebitda = [] for x in range(1, len(month_actual) + 1): adj_ebitda.append( sum(r[7] for r in gl_data if r[0].month == x if r[0].year == date_end.year)) r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Adjusted EBITDA') for c in range(col_actual_start, col_actual_end + 1): c1 = ws_cur.cell(row=r_next, column=c, value=adj_ebitda[c - 2]) for c in range(col_actual_start, col_last + 1): c1 = ws_cur.cell(row=r_next, column=c) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #Adj EBITDA % r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='%') for c in range(2, 15): formula1 = ("=if({col_letter}{row1}=0,0," "{col_letter}{row2}/{col_letter}{row1})").format( col_letter=dcc.get(c), row1=r_next - 4, row2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) c1.number_format = '0.0%' #EBITDA Adjustment r_next += 2 c1 = ws_cur.cell(row=r_next, column=1, value='EBITDA Adjustment') r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Adjusted EBITDA') c1.alignment = Alignment(indent=2) for c in range(col_actual_start, col_actual_end + 1): c1 = ws_cur.cell(row=r_next, column=c, value=adj_ebitda[c - 2]) def_revenue = [] for x in range(1, len(month_actual) + 1): def_revenue.append( sum(r[5] for r in gl_data if r[0].month == x if r[0].year == date_end.year)) r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Deferred Revenue') c1.alignment = Alignment(indent=2) for c in range(col_actual_start, col_actual_end + 1): c1 = ws_cur.cell(row=r_next, column=c, value=def_revenue[c - 2]) excl_expense = [] for x in range(1, len(month_actual) + 1): excl_expense.append( sum(r[4] for r in gl_data if r[0].month == x if r[0].year == date_end.year)) r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Excluded Expenses') c1.alignment = Alignment(indent=2) for c in range(col_actual_start, col_actual_end + 1): c1 = ws_cur.cell(row=r_next, column=c, value=excl_expense[c - 2]) r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Cash EBITDA') for c in range(col_actual_start, col_last + 1): formula1 = "=sum({col_letter}{row1}:{col_letter}{row2})".format( col_letter=dcc.get(c), row1=r_next - 3, row2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) #Cash Needs r_next += 2 c1 = ws_cur.cell(row=r_next, column=1, value='Cash Needs') r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Cash Interest (1)') c1.alignment = Alignment(indent=2) def_finance_fees = setup1.def_finance_fees() interest_expense = [] for x in range(1, len(month_actual) + 1): interest_expense.append( sum(r[6] - def_finance_fees for r in gl_data if r[0].month == x if r[0].year == date_end.year)) for c in range(col_actual_start, col_actual_end + 1): c1 = ws_cur.cell(row=r_next, column=c, value=interest_expense[c - 2]) r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Principal Payments (2)') c1.alignment = Alignment(indent=2) for c in range(col_actual_start, col_actual_end + 1): c1 = ws_cur.cell(row=r_next, column=c, value=setup1.principal_payments()) r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='CAPEX') c1.alignment = Alignment(indent=2) capex = [] for x in range(1, len(month_actual) + 1): capex.append( sum(r[8] for r in gl_data if r[0].month == x if r[0].year == date_end.year)) for c in range(col_actual_start, col_actual_end + 1): c1 = ws_cur.cell(row=r_next, column=c, value=capex[c - 2]) r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Total Cash Needs') for c in range(col_actual_start, col_last + 1): formula1 = "=sum({col_letter}{row1}:{col_letter}{row2})".format( col_letter=dcc.get(c), row1=r_next - 3, row2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) #Free Cash Flow r_next += 2 c1 = ws_cur.cell(row=r_next, column=1, value='Free Cash Flow (EBITDA Coverage') for c in range(col_actual_start, col_last + 1): formula1 = "={col_letter}{row1}-{col_letter}{row2}".format( col_letter=dcc.get(c), row1=r_next - 8, row2=r_next - 2) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) #Budgeted FCF r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Budged FCF') #Variance r_next += 1 c1 = ws_cur.cell(row=r_next, column=1, value='Variance') for c in range(col_actual_start, col_last + 1): formula1 = "={col_letter}{row1}-{col_letter}{row2}".format( col_letter=dcc.get(c), row1=r_next - 2, row2=r_next - 1) c1 = ws_cur.cell(row=r_next, column=c, value=formula1) #Total Year sum_rows = [8, 9, 11, 15, 16, 17, 21, 22, 23, 27] for r in range(0, len(sum_rows)): ws_cur.cell(row=sum_rows[r], column=col_last, value=excel_formulas.sum_row_1(sum_rows[r], 2, col_last - 1)) #Format Cells row_start = row_date + 6 row_last = ws_cur.max_row for c in range(2, col_last + 1): for r in range(row_start, row_last + 1): c1 = ws_cur.cell(row=r, column=c) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' #Footnotes r_next = row_last + 3 str_note = ( "1) Deferred Financing Fees are deducted from interest expense " "since it is non cash.") c1 = ws_cur.cell(row=r_next, column=1, value=str_note) #Column Width ws_cur.column_dimensions[dcc.get(1)].width = 35 for c in range(2, 15): ws_cur.column_dimensions[dcc.get(c)].width = 13 #Page Setup ws_cur.page_setup.orientation = ws_cur.ORIENTATION_LANDSCAPE ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID ws_cur.page_setup.fitToPage = True ws_cur.page_setup.fitToHeight = False ws_cur.page_setup.fitToWidth = 1 ws_cur.print_options.horizontalCentered = True ws_cur.add_print_title(7) ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5) #Freeze Panes c1 = ws_cur.cell(row=8, column=1) ws_cur.freeze_panes = c1 return wb_new
def location_inventory_update(wb): wb_cur = wb ws_cur = wb.create_sheet('Location Inventory') file_path = "Z:/Accounting/Accounting/Financial Package/" dir_list = listdir(file_path) inv_file = [x for x in dir_list if 'Inventory By Location' in x] if len(inv_file) > 1: print('Multiple Inventory By Location Files, Process Cancelled!') sys.exit() msg_text = "Use Inventory File {f1}?".format(f1=inv_file) root = Tk() root.withdraw() answer_loc_file = messagebox.askquestion('Location File', msg_text) if answer_loc_file == 'no': print('Wrong Location File in Package Folder, Process Cancelled!') err_index = 1 cont_return = [wb_cur, err_index] sys.exit() wb_source = load_workbook(file_path + inv_file[0], read_only=True) #Last Worksheet ws_source = wb_source.worksheets[len(wb_source.worksheets) - 1] #Last data row r = 1 while True: if ws_source.cell(row=r, column=2).value == 'TOTAL INVENTORY': break if r > ws_source.max_row: break r += 1 if r > ws_source.max_row: print(("Location Inventory: Can't find TOTAL INVENTORY record, " "Process Cancelled")) err_index = 1 cont_return = [wb_cur, err_index] sys.exit() row_end = r - 1 #Header data row r = 1 while True: if ws_source.cell(row=r, column=2).value == 'Location': break if r > ws_source.max_row: break r += 1 if r > ws_source.max_row: print(("Location Inventory: Can't find Location column header record, " "Process Cancelled")) err_index = 1 cont_return = [wb_cur, err_index] sys.exit() row_header = r titles = [] titles.append(ws_source.cell(row=1, column=1).value) titles.append(ws_source.cell(row=2, column=1).value) titles.append(ws_source.cell(row=3, column=1).value) header1 = [ ws_source.cell(row=row_header, column=x).value for x in range(1, 7) ] #flat list of records records = [] for r in range(row_header + 1, row_end + 1): for c in range(1, 5): records.append(ws_source.cell(row=r, column=c).value) #row list of records records2 = [] for x in range(0, len(records), 4): records2.append(records[x:x + 4]) for x in range(0, len(records2)): if records2[x][0] == None: records2[x][0] = records2[x - 1][0] if records2[x][0] == 'Managed': records2[x][0] = 'Vendor Managed' #remove blanks records3 = [ x for x in records2 if float(x[2] or 0) + float(x[3] or 0) != 0 ] #Write to Financial Package c1 = ws_cur.cell(row=1, column=1, value=titles[0]) c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') c1 = ws_cur.cell(row=2, column=1, value=titles[1]) c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') c1 = ws_cur.cell(row=3, column=1, value=titles[2]) c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=6) ws_cur.merge_cells(start_row=2, end_row=2, start_column=1, end_column=6) ws_cur.merge_cells(start_row=3, end_row=3, start_column=1, end_column=6) #Header r_next = 5 for c in range(1, 7): c1 = ws_cur.cell(row=r_next, column=c, value=header1[c - 1]) c1.fill = PatternFill(start_color='cdc9c9', end_color='cdc9c9', fill_type='solid') #Records r_next += 1 c_next = 1 for r in range(0, len(records3)): for e in range(0, len(records3[r])): ws_cur.cell(row=r_next, column=c_next, value=records3[r][e]) c_next += 1 c_next = 1 r_next += 1 r_next = 6 for r in range(r_next, ws_cur.max_row + 1): formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format( row_cur=r, col_1=dcc.get(4), col_2=dcc.get(3)) ws_cur.cell(row=r, column=5, value=formula1) for r in range(r_next, ws_cur.max_row + 1): formula1 = ("=if({col_1}{row_cur}=0,0," "{col_2}{row_cur}/{col_1}{row_cur})").format( row_cur=r, col_1=dcc.get(3), col_2=dcc.get(5)) ws_cur.cell(row=r, column=6, value=formula1) #Count region records regions = sorted(set([x[0] for x in records3])) region_count = {} for r in regions: region_count[r] = [x[0] for x in records3 if x[0] == r].count(r) #Totals r_next = ws_cur.max_row + 2 ws_cur.cell(row=r_next, column=1, value='Total') inv_pm = sum([x[2] for x in records3]) ws_cur.cell(row=r_next, column=3, value=inv_pm) inv_cm = sum([x[3] for x in records3]) ws_cur.cell(row=r_next, column=4, value=inv_cm) formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format(row_cur=r_next, col_1=dcc.get(4), col_2=dcc.get(3)) ws_cur.cell(row=r_next, column=5, value=formula1) formula1 = ("=if({col_1}{row_cur}=0,0," "{col_2}{row_cur}/{col_1}{row_cur})").format(row_cur=r_next, col_1=dcc.get(3), col_2=dcc.get(5)) ws_cur.cell(row=r_next, column=6, value=formula1) for c in range(1, 7): ws_cur.cell(row=r_next, column=c).font = Font(bold=True) #Region Totals r_next = ws_cur.max_row + 2 regions_supplies = [x for x in regions if x != 'Vendor Managed'] for r in range(0, len(regions_supplies)): ws_cur.cell(row=r_next, column=1, value=regions_supplies[r]) inv_pm = sum([x[2] for x in records3 if x[0] == regions_supplies[r]]) ws_cur.cell(row=r_next, column=3, value=inv_pm) inv_cm = sum([x[3] for x in records3 if x[0] == regions_supplies[r]]) ws_cur.cell(row=r_next, column=4, value=inv_cm) formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format( row_cur=r_next, col_1=dcc.get(4), col_2=dcc.get(3)) ws_cur.cell(row=r_next, column=5, value=formula1) formula1 = ("=if({col_1}{row_cur}=0,0," "{col_2}{row_cur}/{col_1}{row_cur})").format( row_cur=r_next, col_1=dcc.get(3), col_2=dcc.get(5)) ws_cur.cell(row=r_next, column=6, value=formula1) r_next += 1 r_next += 1 ws_cur.cell(row=r_next, column=1, value='Total Supplies Inventory') inv_pm_supplies = sum([x[2] for x in records3 if x[0] != 'Vendor Managed']) ws_cur.cell(row=r_next, column=3, value=inv_pm_supplies) inv_cm_supplies = sum([x[3] for x in records3 if x[0] != 'Vendor Managed']) ws_cur.cell(row=r_next, column=4, value=inv_cm_supplies) formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format(row_cur=r_next, col_1=dcc.get(4), col_2=dcc.get(3)) ws_cur.cell(row=r_next, column=5, value=formula1) formula1 = ("=if({col_1}{row_cur}=0,0," "{col_2}{row_cur}/{col_1}{row_cur})").format(row_cur=r_next, col_1=dcc.get(3), col_2=dcc.get(5)) ws_cur.cell(row=r_next, column=6, value=formula1) for c in range(1, 7): ws_cur.cell(row=r_next, column=c).font = Font(bold=True) #Vendor Managed Total r_next += 2 ws_cur.cell(row=r_next, column=1, value='Vendor Managed') inv_pm_vendor = sum([x[2] for x in records3 if x[0] == 'Vendor Managed']) ws_cur.cell(row=r_next, column=3, value=inv_pm_vendor) inv_cm_vendor = sum([x[3] for x in records3 if x[0] == 'Vendor Managed']) ws_cur.cell(row=r_next, column=4, value=inv_cm_vendor) formula1 = "={col_1}{row_cur}-{col_2}{row_cur}".format(row_cur=r_next, col_1=dcc.get(4), col_2=dcc.get(3)) ws_cur.cell(row=r_next, column=5, value=formula1) formula1 = ("=if({col_1}{row_cur}=0,0," "{col_2}{row_cur}/{col_1}{row_cur})").format(row_cur=r_next, col_1=dcc.get(3), col_2=dcc.get(5)) ws_cur.cell(row=r_next, column=6, value=formula1) for c in range(1, 7): ws_cur.cell(row=r_next, column=c).font = Font(bold=True) #Format format_number = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' for r in range(6, ws_cur.max_row + 1): for c in range(3, 6): ws_cur.cell(row=r, column=c).number_format = format_number for r in range(6, ws_cur.max_row + 1): for c in range(6, 7): ws_cur.cell(row=r, column=c).number_format = '0.0%' for c in range(1, 7): ws_cur.cell(row=5, column=c).alignment = Alignment(horizontal='center') ws_cur.cell(row=5, column=c).font = Font(bold=True) #Page Setup ws_cur.column_dimensions[dcc.get(1)].width = 24 ws_cur.column_dimensions[dcc.get(2)].width = 33 ws_cur.column_dimensions[dcc.get(3)].width = 18 ws_cur.column_dimensions[dcc.get(4)].width = 18 ws_cur.column_dimensions[dcc.get(5)].width = 18 ws_cur.column_dimensions[dcc.get(6)].width = 13 ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID ws_cur.page_setup.fitToPage = True ws_cur.page_setup.fitToHeight = 1 ws_cur.page_setup.fitToWidth = 1 ws_cur.print_options.horizontalCentered = True ws_cur.add_print_title(5) ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5, footer=.5) #Freeze Panes c1 = ws_cur.cell(row=6, column=1) ws_cur.freeze_panes = c1 cont_return = [wb_cur] return cont_return
def excelupdate(wb, dict_db, date_start, date_end, date_ytd_start, date_trail_12_start): result = retrieve1(dict_db=dict_db) wb_cur = wb prod_class = sorted(set([r[1] for r in result])) ws_cur = wb_cur.create_sheet('Schedule B') c1 = ws_cur.cell(row=1, column=1, value='HiTouch Business Services, LLC') str_value = ("Schedule B - Office Products Business Unit Product Mix") c1 = ws_cur.cell(row=2, column=1, value=str_value) str_value = ("For the Period ending {d1:%B} {d1.day}, {d1.year}").format( d1=date_end) c1 = ws_cur.cell(row=3, column=1, value=str_value) #4 Prior Months in descending order month_prior_4 = [] month_prior_4.append(date_start) for m in range(1, 4): month_prior_4.append(date_start + relativedelta(months=-m)) #Reverse in ascending order month_prior_4.reverse() row_start = 8 #Product Class r_next = row_start for x in range(0, len(prod_class)): c1 = ws_cur.cell(row=r_next, column=1, value=prod_class[x]) r_next += 1 #Headers r_next = row_start - 1 c1 = ws_cur.cell(row=r_next, column=1, value='Product Class') for c in range(2, 13, 2): c1 = ws_cur.cell(row=r_next, column=c, value='Revenues') for c in range(3, 14, 2): c1 = ws_cur.cell(row=r_next, column=c, value='% of Rev') r_next = row_start - 2 c_next = 2 for x in range(0, len(month_prior_4)): mth_name = calendar.month_name[month_prior_4[x].month] c1 = ws_cur.cell(row=r_next, column=c_next, value=mth_name) c_next += 2 c1 = ws_cur.cell(row=r_next, column=10, value=str(date_end.year) + ' ' + 'Year-to-Date') c1 = ws_cur.cell(row=r_next, column=12, value='Trailing 12 Months') #Revenue Trailing 4 Months r_next = row_start for p in prod_class: c_next = 2 for c in month_prior_4: rev = sum([x[2] for x in result if x[0] == c if x[1] == p]) c1 = ws_cur.cell(row=r_next, column=c_next, value=rev) c_next += 2 r_next += 1 #Revenue YTD r_next = row_start c_next = 10 for p in prod_class: rev = sum([ x[2] for x in result if date_ytd_start <= x[0] <= date_end if x[1] == p ]) c1 = ws_cur.cell(row=r_next, column=c_next, value=rev) r_next += 1 #Revenue Trailing 12 Months r_next = row_start c_next = 12 for p in prod_class: rev = sum([ x[2] for x in result if date_trail_12_start <= x[0] <= date_end if x[1] == p ]) c1 = ws_cur.cell(row=r_next, column=c_next, value=rev) r_next += 1 #Totals r_last = ws_cur.max_row r_total = r_last + 2 c_next = 2 for c in range(c_next, 14): formula1 = excel_formulas.sum_col_1(row1=row_start, row2=r_last, col1=c) c1 = ws_cur.cell(row=r_total, column=c, value=formula1) #% of Rev c_next = 3 for c in range(c_next, 14, 2): for r in range(row_start, r_last + 1): formula1 = excel_formulas.pct_of_total1(c, r_total, r) c1 = ws_cur.cell(row=r, column=c, value=formula1) #Format c_last = ws_cur.max_column ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=c_last) ws_cur.merge_cells(start_row=2, end_row=2, start_column=1, end_column=c_last) ws_cur.merge_cells(start_row=3, end_row=3, start_column=1, end_column=c_last) for r in range(1, 4): c1 = ws_cur.cell(row=r, column=1) c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true') border_left = Border(left=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) border_right = Border(right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) r_next = row_start - 2 for c in range(2, 13, 2): ws_cur.merge_cells(start_row=r_next, end_row=r_next, start_column=c, end_column=c + 1) c1 = ws_cur.cell(row=r_next, column=c) c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true') c1.border = border_left c1 = ws_cur.cell(row=r_next, column=c + 1) c1.border = border_right r_next = row_start - 1 for c in range(1, c_last + 1): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true') c1.alignment = Alignment(horizontal='center') for c in range(2, 13, 2): for r in range(row_start, r_total + 1): c1 = ws_cur.cell(row=r, column=c) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' for c in range(3, 14, 2): for r in range(row_start, r_total + 1): c1 = ws_cur.cell(row=r, column=c) c1.number_format = '0.0%' #Column widths ws_cur.column_dimensions['A'].width = 27 for c in range(2, c_last + 1): ws_cur.column_dimensions[dcc.get(c)].width = 12.5 #Page Setup ws_cur.page_setup.orientation = ws_cur.ORIENTATION_LANDSCAPE ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID ws_cur.page_setup.fitToPage = True ws_cur.page_setup.fitToHeight = False ws_cur.page_setup.fitToWidth = 1 ws_cur.print_options.horizontalCentered = True ws_cur.add_print_title(7) ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5) #Freeze Panes c1 = ws_cur.cell(row=8, column=1) ws_cur.freeze_panes = c1 return wb_cur
def col_total(self, rowstart, rowend, colcur): formula1 = "=SUM({c1}{r1}:{c1}{r2})".format(c1=dcc.get(colcur), r1=rowstart, r2=rowend) return formula1
def row_total(self, colstart, colend, rcur): formula1 = "=SUM({c1}{r1}:{c2}{r1})".format(c1=dcc.get(colstart), c2=dcc.get(colend), r1=rcur) return formula1
def build_report(dict_db, date_end, wb): rebates = get_rebate_data(date_end, dict_db) sales = get_sales_data(date_end, dict_db) date_reverse = sorted(set([x[0] for x in sales]), reverse=1) wb_cur = wb ws_cur = wb_cur.create_sheet('Schedule C') ws_cur.cell(row=1, column=1, value='Schedule C - Rebate Components') ws_cur.cell(row=2, column=1, value='Consolidated (HT/MYOP)') ws_cur.cell(row=3, column=1, value='For the 12 months ending {d1}'.format( d1=datetime.strftime(date_end, '%B %d, %Y'))) for r in range(1, 4): ws_cur.cell(row=r, column=1).font = Font(bold='true') date_reverse = sorted(set([x[0] for x in sales]), reverse=1) r_next = 5 ws_cur.cell(row=r_next, column=2, value='Pricing') ws_cur.cell(row=r_next, column=3, value='Wholesalers') ws_cur.cell(row=r_next, column=4, value='Manufacturing') ws_cur.cell(row=r_next, column=5, value='Direct Buy') ws_cur.cell(row=r_next, column=6, value='Total') for c in range(2, 7): c1 = ws_cur.cell(row=r_next, column=c) c1.font = Font(bold='true', underline='single') c1.alignment = Alignment(horizontal='center') ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=6) ws_cur.merge_cells(start_row=2, end_row=2, start_column=1, end_column=6) ws_cur.merge_cells(start_row=3, end_row=3, start_column=1, end_column=6) for r in range(1, 4): c1 = ws_cur.cell(row=r, column=1) c1.alignment = Alignment(horizontal='center') format_number = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' border_tl = Border(top=Side(style='thin'), left=Side(style='thin')) border_t = Border(top=Side(style='thin')) border_tr = Border(top=Side(style='thin'), right=Side(style='thin')) border_bl = Border(bottom=Side(style='thin'), left=Side(style='thin')) border_b = Border(bottom=Side(style='thin')) border_br = Border(bottom=Side(style='thin'), right=Side(style='thin')) r_next += 2 for d in date_reverse: month_sales = sum([s[1] for s in sales if s[0] == d]) ws_cur.cell(row=r_next, column=1, value = datetime.strftime(d, '%B %Y')) pricing = sum([r[2] for r in rebates if r[0] == d if r[1] == 'Pricing']) c1 = ws_cur.cell(row=r_next, column=2, value = pricing) c1.border = border_tl wholesaler = sum([r[2] for r in rebates if r[0] == d if r[1] == 'Wholesalers']) ws_cur.cell(row=r_next, column=3, value = wholesaler) manufacturing = sum([r[2] for r in rebates if r[0] == d if r[1] == 'Manufacturing']) ws_cur.cell(row=r_next, column=4, value = manufacturing) direct_buy = sum([r[2] for r in rebates if r[0] == d if r[1] == 'Direct Buy Savings']) ws_cur.cell(row=r_next, column=5, value = direct_buy) formula_total = '=sum(B{r1}:E{r1})'.format(r1=r_next) c1 = ws_cur.cell(row=r_next, column=6, value=formula_total) c1.border = border_tr for c in range(2, 7): ws_cur.cell(row=r_next, column=c).number_format = format_number for c in range(3, 6): c1 = ws_cur.cell(row=r_next, column=c).border = border_t pricing_ratio = pricing / month_sales if month_sales else 0 wholesaler_ratio = wholesaler / month_sales if month_sales else 0 manufacturing_ratio = manufacturing / month_sales if month_sales else 0 direct_buy_ratio = direct_buy / month_sales if month_sales else 0 ws_cur.cell(row=r_next + 1, column=1, value='% of Revenue') ws_cur.cell(row=r_next + 1, column=2, value=pricing_ratio) ws_cur.cell(row=r_next + 1, column=3, value=wholesaler_ratio) ws_cur.cell(row=r_next + 1, column=4, value=manufacturing_ratio) ws_cur.cell(row=r_next + 1, column=5, value=direct_buy_ratio) formula_total = '=sum(B{r1}:E{r1})'.format(r1=r_next + 1) ws_cur.cell(row=r_next + 1, column=6, value=formula_total) for c in range(2, 7): ws_cur.cell(row=r_next + 1, column=c).number_format = '0.0%' ws_cur.cell(row=r_next + 1, column=2).border = border_bl for c in range(3, 6): ws_cur.cell(row=r_next + 1, column=c).border = border_b ws_cur.cell(row=r_next + 1, column=6).border = border_br r_next += 3 #Total ws_cur.cell(row=r_next, column=1, value='Rolling 12 Month Total') for c in range(2, 7): r = 3 cat_total = '=' for x in range(1, 13): cat_total = cat_total + '{c1}{r1}+'.format( c1=dcc.get(c), r1=r_next - r) r += 3 cat_total = cat_total[:-1] ws_cur.cell(row=r_next, column=c, value=cat_total) ws_cur.cell(row=r_next + 1, column=1, value='% of Revenue') pricing = sum([r[2] for r in rebates if r[1] == 'Pricing']) wholesaler = sum([r[2] for r in rebates if r[1] == 'Wholesalers']) manufacturing = sum([r[2] for r in rebates if r[1] == 'Manufacturing']) direct_buy = sum([r[2] for r in rebates if r[1] == 'Direct Buy Savings']) rebate_total = sum([r[2] for r in rebates]) sales_total = sum([s[1] for s in sales]) pricing_ratio = pricing / sales_total if sales_total else 0 wholesaler_ratio = wholesaler / sales_total if sales_total else 0 manufacturing_ratio = manufacturing / sales_total if sales_total else 0 direct_buy_ratio = direct_buy / sales_total if sales_total else 0 rebate_total_ratio = rebate_total / sales_total if sales_total else 0 ws_cur.cell(row=r_next + 1, column=2, value=pricing_ratio) ws_cur.cell(row=r_next + 1, column=3, value=wholesaler_ratio) ws_cur.cell(row=r_next + 1, column=4, value=manufacturing_ratio) ws_cur.cell(row=r_next + 1, column=5, value=direct_buy_ratio) ws_cur.cell(row=r_next + 1, column=6, value=rebate_total_ratio) for c in range(2, 7): ws_cur.cell(row=r_next, column=c).number_format = format_number ws_cur.cell(row=r_next + 1, column=c).number_format = '0.0%' ws_cur.cell(row=r_next, column=2).border = border_tl ws_cur.cell(row=r_next + 1, column=2).border = border_bl for c in range(3, 6): ws_cur.cell(row=r_next, column=c).border = border_t ws_cur.cell(row=r_next + 1, column=c).border = border_b ws_cur.cell(row=r_next, column=6).border = border_tr ws_cur.cell(row=r_next + 1, column=6).border = border_br #Page Setup ws_cur.column_dimensions[dcc.get(1)].width = 22 for c in range(2, 7): ws_cur.column_dimensions[dcc.get(c)].width = 15 ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID ws_cur.page_setup.fitToPage = True ws_cur.page_setup.fitToHeight = 1 ws_cur.page_setup.fitToWidth = 1 ws_cur.print_options.horizontalCentered = True ws_cur.add_print_title(5) ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5, footer=.5) #Freeze Panes c1 = ws_cur.cell(row=6, column=1) ws_cur.freeze_panes = c1 return wb_cur
end_column=col2) c1.alignment = Alignment(horizontal='center') c1.font = Font(bold='true') c1.fill = PatternFill(start_color='da70d6', end_color='da70d6', fill_type='solid') #Sum Sales and Cost row_end = ws_ht.max_row col_end = ws_ht.max_column r = row_end + 2 c1 = ws_ht.cell(row=r, column=1, value='DW Total') for col in range(2, col_end + 1): f1 = '=SUM({col_letter1}{row1}:{col_letter2}{row2})'.format( col_letter1=dcc.get(col), row1=row_start, row2=row_end, col_letter2=dcc.get(col)) c1 = ws_ht.cell(row=r, column=col, value=f1) c1.number_format = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)' ws_ht.column_dimensions['{col_letter}'.format( col_letter=dcc.get(col))].width = w_Amt #Column Headers for c in range(2, col_end + 1, 2): c1 = ws_ht.cell(row=row_start - 1, column=c, value='Sales') for c in range(3, col_end + 1, 2): c1 = ws_ht.cell(row=row_start - 1, column=c, value='Cost')