def fill_enabling_areas(self):
max_h = self.get_element_sizes(self.get_element_by_title("B41"))[1] # Largest value will be Max height
y1 = self.get_element_coords(self.get_element_by_title("enabling-areas-min"))[3]
for c in alpha_range("B", "K"):
current_e = self.get_element_by_title("%s41" % c) # Height (% of Max). Current Amt
plan_e = self.get_element_by_title("%s43" % c) # Height (% of Max). Plan
prior_e = self.get_element_by_title("%s45" % c) # Height of Max. Prior
current, plan, prior = [
max(self.get_cell("%s%s" % (c, r)), 0) for r in [41, 43, 45]
] # get Current Amt, Plan and Prior values
# set Current Amt
self.set_element_size(current_e, None, max_h * current)
self.set_element_pos(current_e, None, y1 - max_h * current)
# set Plan
self.set_element_pos(plan_e, None, y1 - max_h * plan - self.get_element_sizes(plan_e)[1] / 2)
# set Prior
self.set_element_pos(prior_e, None, y1 - max_h * prior - self.get_element_sizes(prior_e)[1] / 2)
# set Current Amount label
label_e = self.get_element_by_title("%s40" % c)
self.set_element_pos(
label_e, None, y1 - max(current, prior, plan) * max_h - self.get_element_sizes(label_e)[1]
)
# labels under sections(Parent, IT, Talent etc)
self.set_element_text_lines(
self.get_element_by_title("%s39" % c), [x[:9] for x in self.get_cell("%s39" % c).split()]
)
def fill_total_headcount(self):
separate_chart_group = self.E('separate-ea-and-parent-cost')
max_size = self.get_element_sizes(self.template_shapes['total-headcount-bubble'])[0] # max chart size
font_size = self.get_element_font_size(self.template_shapes['total-headcount-bubble'])
x_axis_coords = self.get_element_coords(self.E('total-headcount-x-axis')) # x Axis
y_axis_coords = self.get_element_coords(self.E('total-headcount-y-axis')) # y Axis
x0 = x_axis_coords[0]
w = x_axis_coords[2]-x0 # chart width
y1 = y_axis_coords[3]
h = y1-y_axis_coords[1] # chart height
# x Axis scale Min -25% and Max 25%
min_xp = -.25
max_xp = .25
# y Axis scale Min -15% and Max 15%
min_yp = -.15
max_yp = .15
for col in alpha_range('B', 'H'):
size_p = self.get_cell('%s43' % col)
if size_p < .25:
continue
size = size_p*max_size # label size
bubble_e = self.clone_template('total-headcount-bubble') # label
self.set_element_size(bubble_e, size, size)
# Limit the positions of the bubbles to a max of 26% and a min of -26% on the x axis
x_val = max(min(self.get_cell('%s42' % col), .26), -.26)
# Limit the positions of the bubbles to max of 20% and a min of -20% on the y axis.
y_val = max(min(self.get_cell('%s40' % col), .2), -.2)
self.set_element_pos(
bubble_e,
x0 + (x_val-min_xp)/(max_xp-min_xp)*w-size/2,
y1 - (y_val-min_yp)/(max_yp-min_yp)*h-size/2,
)
self.set_element_text(bubble_e, self.get_cell('%s38' % col)) # label text(IT, SINT etc)
self.set_element_font_size(bubble_e, size_p*font_size) # set bubble size
separate_chart_group.append(bubble_e)
# Total Headcount and Total Cost labels
label_e = self.clone_template('total-headcount-label')
self.set_element_text_lines(
label_e,
[
'Cost: $%sM' % self.format_float(self.get_cell('%s41' % col)/10**6, prec=0),
'TOTAL HC: %s' % self.format_float(self.get_cell('%s39' % col), prec=0),
]
)
self.set_element_pos(
label_e,
self.get_element_coords(bubble_e)[0]-self.get_element_sizes(label_e)[0],
self.get_element_coords(bubble_e)[1]+size/2-self.get_element_sizes(label_e)[1]/2,
)
separate_chart_group.append(label_e)
def fill_values(self):
super(Dashboard4, self).fill_values()
def to_zero(v):
return 0 if v < 0.015 else v
self.fill_small_charts()
# fill bottom chart with values
self.fill_chart(
2,
[
[self.get_cell(name) for name in C('B%s' % c, 'Z%s' % c)]
for c in range(19, 23)
],
conv=to_zero,
)
# heights based on advisory, audit, consulting and tax
heights = [
sum(to_zero(self.get_cell('%s%s' % (c, r))) for r in range(19, 23))
for c in alpha_range('B', 'Z')
]
# numerate y-Axis
B17 = self.get_cell('B17') # y-Axis maximum
# y-Axis points
for i, c in enumerate(['B17*1/5', 'B17*2/5', 'B17*3/5', 'B17*4/5', 'B17'], 1):
self.set_text(c, '$'+self.format_float(B17*i/5/10**6, prec=1))
# chart coords for placing arrows
chart = self.get_chart_by_title('chart')
coords = self.get_element_coords(self.get_element_by_title('chart'))
chart_width, chart_height = self.get_element_sizes(self.get_element_by_title('chart'))
x0 = coords[0]+chart_width*chart.x
y0 = coords[3]-chart_height*chart.y
width = chart_width*chart.w # chart width
height = chart_height*chart.h # chart height
n = 25 # num of small arrows\rectangles
step = width/n # step between small arrows
# small arrows on a big chart
for i, (c, h) in enumerate(zip(C('B23', 'Z23'), heights)):
val = self.get_cell(c)
tmpl_name = 'small-arrow-up' if val >= 0 else 'small-arrow-down' # If Revenue Growth is negative, display chevron up, else - down
tmpl = self.clone_template(tmpl_name)
coords = self.get_element_coords(tmpl)
tmpl_w, tmpl_h = self.get_element_sizes(tmpl)
self.set_element_text(tmpl, self.format_percent(abs(val), prec=0)+'%') # set text under small arrow
# place arrowsabove rectangles
self.set_element_pos(
tmpl,
x0+step*i+(step-tmpl_w)/2+(tmpl_w/2+step/4)*(h > .95),
y=y0-height*h+(-tmpl_h*(h <= .95)),
) # set small arrow
self.E('separate-industry-revenue-mix').append(tmpl)
def fill_key_metrics(self):
separate_chart_group = self.E('separate-earnings-per-partner')
x0 = self.get_element_coords(self.get_element_by_title('key-metrics-min'))[0]
x1 = self.get_element_coords(self.get_element_by_title('key-metrics-max'))[0]
w = x1-x0 # chart width
minp = -.10 # x Axis min (-10%)
maxp = .15 # x Axis max (15%)
step = .005 # tick step
def set_tick(name):
tick = self.get_element_by_title(name)
v = self.get_cell(name)
tick_h, tick_w = self.get_element_sizes(tick) # tick height, width
show_label = False # label above tick(%)
if v < minp:
# If growth rates of metrics are lower than -10%
pos_v = minp-step # display at 15.5% level
show_label = True # display actual value above the dot in 7pt font
elif v > maxp:
# If growth rates of metrics are higher than 15%
pos_v = maxp+step # display at -10.5% level
show_label = True # display actual value above the dot in 7pt font
else:
pos_v = v
x = x0 + (pos_v-minp)/(maxp-minp)*w # tick position
self.set_element_pos(tick, x-tick_w/2, None) # set tick position
# tick label value
if -0.99 < v < 0.99:
text = self.format_percent(v, prec=1)+'%'
else:
text = self.format_percent(min(max(v, -0.99), .99), prec=0)+'%' # Max value 99%; Min -99%
# if label's value is )-10;15(
if show_label:
value_e = self.clone_template('key-metrics-label')
value_e_w, value_e_h = self.get_element_sizes(value_e) # tick label's width, heigh
label_x = x-value_e_w/2 # tick label's x
label_y = self.get_element_coords(tick)[1]-value_e_h # # tick label's y
self.set_element_text(value_e, text) # set tick label's text
self.set_element_pos(value_e, label_x, label_y) # set tick label's position
separate_chart_group.append(value_e)
for n in [30, 32, 34, 36, 37]:
for col in alpha_range('B', 'E'):
set_tick('%s%s' % (col, n))
def fill_cost_breadown(self):
y1 = self.get_element_coords(self.E('cost-breakdown-min'))[3]
max_h = self.get_element_sizes(self.E('B22'))[1] # Largest value will be Max height
for col in alpha_range('B', 'G'):
bar_e = self.E('%s22' % col) # section rectangle Bar Height (% of Max)
bar_coords = self.get_element_coords(bar_e)
bar_hs = [] # sections height
def get_h(p):
# Limit the height of any negative values to -1/4 of MAX
h = max_h*max(p, -.25)
bar_hs.append(h)
return h
bar_h = get_h(self.get_cell('%s22' % col))
if bar_h < 0: # Limit the height of any negative values to -1/4 of MAX *
self.set_element_text_color(self.E('%s20' % col), 'FFFFFF')
# set Current Amt rectangle
self.set_element_pos(bar_e, None, y1-max(bar_h, 0))
self.set_element_size(bar_e, None, abs(bar_h))
for name in ['%s24' % col, '%s26' % col]:
e = self.E(name)
self.set_element_pos(e, None, y1-get_h(self.get_cell(name)))
yoy = self.get_cell('%s21' % col) # YoY Growth(%)
yoy_e = self.clone_template('enabling-areas-yoy-down' if yoy < 0 else 'enabling-areas-yoy-up')
yoy_e_w, yoy_e_h = self.get_element_sizes(yoy_e) # YOY width and height
# set YOY label values
self.set_element_pos(
yoy_e,
(bar_coords[0]+bar_coords[2]-yoy_e_w)/2,
self.get_element_coords(bar_e)[1]-yoy_e_h
)
self.set_element_text(yoy_e, self.format_percent(abs(yoy), prec=0)+'%')
self.add_shape(yoy_e)
# section label below x Axis
label_e = self.E('%s19' % col)
self.set_element_pos(
label_e,
None,
max(self.get_element_coords(self.E('%s%s' % (col, x)))[3] for x in [20, 22, 24, 26]),
)
self.set_element_text(self.E('I20'), self.big_money(self.get_cell('B10'))) # Total
def fill_enabling_areas(self):
separate_chart_group = self.E('separate-enabling-areas')
y1 = self.get_element_coords(self.E('enabling-areas-min'))[3]
max_h = self.get_element_sizes(self.E('B11'))[1] # Largest value will be Max height
for col in alpha_range('B', 'J'):
bar_e = self.E('%s11' % col) # section rectangle Bar Height (% of Max)
bar_coords = self.get_element_coords(bar_e)
bar_hs = [] # sections height
bar_h = max_h*self.get_cell('%s11' % col) # normalized height
bar_hs.append(bar_h)
if bar_h < 0:
# If negative value, display no bar, and display value just above x axis
remove(bar_e)
else:
# set section position
self.set_element_pos(bar_e, None, y1-bar_h)
self.set_element_size(bar_e, None, bar_h)
for name in ['%s13' % col, '%s15' % col]:
# lines height
bar_h = self.get_cell(name)*max_h # section height
bar_hs.append(bar_h)
e = self.E(name)
if bar_h < 0:
# # If negative value, display no bar, and display value just above x axis
remove(e)
else:
self.set_element_pos(e, None, y1-bar_h)
yoy = self.get_cell('%s16' % col) # YoY Cost Growth
yoy_e = self.clone_template('enabling-areas-yoy-down' if yoy < 0 else 'enabling-areas-yoy-up')
yoy_e_w, yoy_e_h = self.get_element_sizes(yoy_e) # label width, height
# set YOY label
self.set_element_pos(
yoy_e,
(bar_coords[0]+bar_coords[2]-yoy_e_w)/2,
self.get_element_coords(bar_e)[1]-yoy_e_h
)
self.set_element_text(yoy_e, self.format_percent(abs(yoy), prec=0)+'%')
separate_chart_group.append(yoy_e)
def fill_earnings(self):
max_y = float(self.get_cell('B38')) # Max of Y Axis (Earnings per CS Staff)
max_x = float(self.get_cell('B39')) # Max of X Axis (Leverage W/ Partners)
# numerate Y Axis
for val, title in [(max_y*i/5., 'B38*%s/5' % i) for i in xrange(1, 5)]+[(max_y, 'B38')]:
for e in self.get_elements_by_title(title):
self.set_element_text(e, '$'+self.format_float(val/10.**3, prec=0)+'K')
# numerate X Axis
for val, title in [(max_x*i/5., 'B39*%s/5' % i) for i in xrange(1, 5)]+[(max_x, 'B39')]:
for e in self.get_elements_by_title(title):
self.set_element_text(e, self.format_float(val, prec=0))
w = self.get_element_sizes(self.E('earnings-x-axis'))[0] # char width
h = self.get_element_sizes(self.E('earnings-y-axis'))[1] # chart height
for col in alpha_range('B', 'E'):
rect_e = self.E('rect-%s' % col) # section rectangle(or section)
original_h = self.get_element_sizes(rect_e)[1] # section rectangle orig. height
wpn = self.get_cell('%s26' % col)/max_x # section rect. width
hpn = self.get_cell('%s25' % col)/max_y # section rect. height
hn = hpn*h # section chart height
# set section
self.set_element_size(
rect_e,
w*wpn,
hn,
)
self.mod_element_pos(
rect_e,
None,
original_h-hn,
)
rect_coords = self.get_element_coords(rect_e)
label_e = self.E('earnings-label-%s' % col) # section rectangle label
label_y = None
if hpn < .33:
# If lower < 33%, Move EPP Value up so that the bottom is at 33%
label_y = rect_coords[3]-.33*h-self.get_element_sizes(label_e)[1]
if wpn < .25:
# if lower < 25%, Move text so that the the left edge is at 25%
label_x = rect_coords[0]+.25*w
else:
label_x = (rect_coords[0]+rect_coords[2]-self.get_element_sizes(label_e)[0])/2
if hpn < .33 or wpn < .25:
# if width < 25%, display text at the color of the function
# if height < 33%, display text at the color of the function
color = self.get_element_fill_color(rect_e)
for e in self.xpath('.//a:rPr[a:solidFill]', label_e):
self.set_element_text_color(e, color)
self.replace_pic_color(label_e, color)
# set section label
self.set_element_pos(
label_e,
label_x,
label_y,
)
def fill_utilization_and_headcount(self):
separate_chart_group = self.E('separate-utilization-headcount-growth')
utilization_base = self.get_cell('B21') # Value at Origin (Y Axis)
minyp = utilization_base-.1
maxyp = utilization_base+.1
for title, value in zip(['B21-min', 'B21', 'B21-max'], [minyp, utilization_base, maxyp]):
self.set_text(title, self.format_percent(value, prec=1)+'%')
x0 = self.get_element_coords(self.get_element_by_title('headcount-min'))[0] # Headcount x-Axis x0
x1 = self.get_element_coords(self.get_element_by_title('headcount-max'))[0] # Headcount x-Axis x1
w = x1-x0 # chart width
h = self.get_element_sizes(self.E('utilization-axis'))[1] # chart height
y1 = self.get_element_coords(self.E('utilization-axis'))[3] # Utilization Axis(y-Axis)
minp = -.05 # min x-Axis
maxp = .2 # max x-Axis
step = .005 # tick step
max_size = self.get_element_sizes(self.E('utilization-B'))[0]
for col in alpha_range('B', 'F'):
utilization_e = self.E('utilization-%s' % col) # Utilization label(Current)
utilization_prior_e = self.E('utilization-prior-%s' % col) # Utilization label(Prior)
label_e = self.clone_template('utilization-label')
for e, utilization_cell in zip([utilization_e, utilization_prior_e], ['%s18' % col, '%s19' % col]):
size = max_size*self.get_cell('%s20' % col) # label size
self.set_element_size(e, size, size)
utilization = self.get_cell(utilization_cell)
# tick position
v = self.get_cell('%s17' % col)
if v < minp:
# If HC Growth rate is below -5%, display at -5.5%
pos_v = minp-step
elif v > maxp:
# If HC Growth rate is above 20%, display at 22.5%
pos_v = maxp+step
else:
pos_v = v
# set utilization label
x = x0 + (pos_v-minp)/(maxp-minp)*w-size/2
y = y1 - (utilization-minyp)/(maxyp-minyp)*h-size/2
self.set_element_pos(e, x, y)
# set Utilization label(Total HC, HC YoY, Utilization)
if e is utilization_e:
# set label values
text_lines = [
'Total HC: %s' % self.with_comma(self.get_cell('%s16' % col)),
'HC YoY: %s%%' % self.format_percent(v, prec=1),
'Utilization: %s%%' % self.format_percent(utilization, prec=1),
]
# set label text and position
self.set_element_text_lines(label_e, text_lines)
self.set_element_pos(
label_e,
x-self.get_element_sizes(label_e)[0],
y+(size-self.get_element_sizes(label_e)[1])/2,
)
separate_chart_group.append(label_e)
def fill_actual_variance(self):
first_col = 'B' # excel first column
last_col = 'N' # excel last column
max_val = max(self.get_cell(name) for name in C('B31', 'N31')) # max value of Earnings Variance to Prior
y0 = self.get_element_coords(self.get_element_by_title('actual-variance-min'))[1]
max_h = (y0-self.get_element_coords(self.get_element_by_title('actual-variance-max'))[3])*2/3
# set rectangles
for name in C('B31', 'N31'):
val = self.get_cell(name) # get excel value
h = abs(max_h*val/max_val) # rectangle height
rect = self.get_element_by_title(name + ' R')
text = self.get_element_by_title(name)
srgbClr = self.xpath('.//a:srgbClr', rect)[0]
if val < 0:
color = VARIANCE_NEG_COLOR # if Variance decrised, grey rectangle
y = y0
text_y = y0-self.get_element_sizes(text)[1]
else:
color = VARIANCE_POS_COLOR # if Variance encrised, blue rectangle
y = y0-h
text_y = y-self.get_element_sizes(text)[1]
# set rectangle position, color, label
self.set_element_size(rect, None, h)
self.set_element_pos(rect, None, y)
self.set_element_pos(text, None, text_y)
srgbClr.set('val', color)
# YTD Prior Var. Amount
total_hp = self.get_cell('D33') # Height of total Variance
total_label_e = self.E('B33') # Total YTD Variance
total_rect_e = self.E('B33 R') # Total Variance rectangle
total_original_h = self.get_element_sizes(total_rect_e)[1]
total_h = max_h*total_hp
self.set_element_size(total_rect_e, None, total_h) # set total variance rectangle size
self.mod_element_pos(total_rect_e, None, total_original_h-total_h) # set total variance rectangle position
# set label position
total_label_e = self.set_element_pos(
total_label_e,
None,
self.get_element_coords(total_rect_e)[1]-self.get_element_sizes(total_label_e)[1]
)
# underline current year
current_year_line = self.E('actual-variance-current-year-line')
x1 = self.get_element_coords(current_year_line)[2]
period_values = [self.get_cell(c) for c in C('%s30' % first_col, '%s30' % last_col)]
# get previous year
prev = None
for year, col in zip(period_values, alpha_range(first_col, last_col)):
if year == 1:
break # current year
prev = col # previous year
# current year line begining
first_cur_label_e = self.E('%s30' %col)
if prev:
x0 = (self.get_element_center(first_cur_label_e)[0]+self.get_element_center(self.E('%s30' % prev))[0])/2
else:
x0 = self.get_element_coords(first_cur_label_e)[0]
# underline current year rectangles
self.set_element_pos(current_year_line, x0, None)
self.set_element_size(current_year_line, x1-x0, None)
def fill_chart(self):
cols = alpha_range('B', 'M') # excel cells in range.
x0, y0, x1, y1 = self.get_element_coords(self.get_element_by_title('chart-box'))
w = x1-x0 # chart box width
h = y1-y0 # chart box height
self.set_element_pos(self.get_element_by_title('eba'), None, y0+h*.75)
x = x0
for c in cols:
wpn = self.get_cell('%s8' % c)
wn = wpn*w
hpn = min(max(self.get_cell('%s11' % c), .08), .83) # solid box height is between 8% and 83%
hn = hpn*h
rotated = wpn <= .04
rect = self.get_element_by_title('%s11' % c) # solid box
eba_label = self.get_element_by_title('%s10' % c) # Earnings
growth_e = self.get_element_by_title('%s9' % c) # Revenue Growth
revenue_label = self.get_element_by_title('%s7' % c) # Revenue
title = self.get_element_by_title('%s6' % c) # column title
arrow = self.clone_template('template-arrow') # get arrow chevron
self.set_element_size(rect, wn, hn) # set solid box size
self.set_element_pos(rect, x, y0+h-hn) # set solid box position
# Rules for displaying EBA ($) figure, solid box:
if hpn >= .32:
# If EBA Margin is between 83%-32%, display EBA ($) in white with top of text at 32% height
self.set_element_pos(eba_label, None, y0+h-.32*h)
elif hpn >= .27:
# If EBA Margin is 32%-27% Display EBA ($) in the color of the function (grey, dark blue, light blue, or green) with top of text at 34.5% height
self.set_element_text_color(eba_label, self.get_element_fill_color(rect))
self.set_element_pos(eba_label, None, y0+h-.345*h)
else:
# If EBA Margin in 27%-8%, display EBA ($) in the color of the function (grey, dark blue, light blue, or green) with top of text at 32% height
self.set_element_text_color(eba_label, self.get_element_fill_color(rect))
self.set_element_pos(eba_label, None, y0+h-.32*h)
# right-top corner of title box should be at center of section horizontally
title_w, title_h = self.get_element_sizes(title)
title_angle = math.radians(self.get_element_rotation(title)/60000)
# calculating horizonatal shift of right-top corner of box from its center
title_shift = (title_w*math.cos(title_angle)+title_h*math.sin(title_angle))/2
self.set_element_pos(title, x+(wn-title_w)/2-title_shift, None)
# Rules for displaying Revenue Growth (%) chevron:
if wpn > .015:
# When width of a section is above 1.5%, the chevron of Revenue Growth(up/down) should appear
self.set_element_pos(arrow, x+(wn-self.get_element_sizes(arrow)[0])/2, None)
self.E('separate-revenue-eba').append(arrow)
if self.get_cell('%s9' % c) < 0:
# When Revenue Growth is positive, chevron goes up.
self.set_element_flipv(arrow, True)
self.set_element_fliph(arrow, True)
# For Width of columns, except First column.
for l in [7, 9, 10, 12]:
label = self.get_element_by_title('%s%s' % (c, l))
if wpn <= .015:
# If Width is below 1.5%, Do not display any numbers on the column
remove(label)
else:
self.set_element_size(label, wn, None)
self.set_element_pos(label, x, None)
# If Larger than 3.5%, just do typical horizontal type with 13.6 size font.
#If Between 3.5% and 1.5%, turn all text horizontal but keep at same size font
self.set_element_text_direction(label, 'vert270' if .015 < wpn < .035 else None)
self.set_element_text_alignment(label, 'r' if .015 < wpn < .035 else 'ctr')
# For Width of the First column.
if c == cols[0]:
if rotated:
# If smaller than 4% wide, move labels (Revenue, EBA, Margin) outside the left edge of the chart.
for e_title in ['eba', 'revenue', 'margin']:
e = self.get_element_by_title(e_title)
self.set_element_pos(e, x0-self.get_element_sizes(e)[0], None) # to the left edge
self.set_element_text_color(e, '000000') # set them black color
else:
# If width larger than 4%:
if hpn < .25:
# If margin is below 25%, display EBA $ at the normal 25% level and change color to match the box below it.
self.set_element_text_color(
self.get_element_by_title('eba'),
self.get_element_fill_color(rect),
)
# If margin is higher than 80%, change color of the Revenue # Amount to white, move 25% above Height
elif hpn > .8:
revenue_e = self.get_element_by_title('revenue')
self.set_element_pos(revenue_e, None, y0+.25*h)
self.set_element_pos(revenue_label, None, y0+.25*h-self.get_element_sizes(revenue_label)[1])
self.set_element_text_color(revenue_label, 'FFFFFF')
self.set_element_text_color(revenue_e, 'FFFFFF')
x += wn
if c != cols[-1]:
sep = self.get_element_by_title('%s-sep' % c)
self.set_element_pos(sep, x, None)
# Set advisory, audit, consulting and tax in the middle of the column.
for label_name, (first, last) in [
('advisory', 'BE'),
('audit', 'FF'),
('consulting', 'GI'),
('tax', 'JM'),
]:
label = self.get_element_by_title(label_name)
start = self.get_element_by_title('%s11' % first)
end = self.get_element_by_title('%s11' % last)
self.set_element_pos(
label,
(self.get_element_coords(end)[2]+self.get_element_coords(start)[0]-self.get_element_sizes(label)[0])/2,
None,
)