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