def set_tick(name, disable=False):
tick = self.get_element_by_title(name+'-tick')
value_e = self.get_element_by_title(name)
# If both icons are either above 15% or below -10% on the same metric,
# only display actual amount for advisory icon
if disable:
remove(value_e)
# tick positioning
v = self.get_cell(name)
tick_w = self.get_element_sizes(tick)[0]
if v < -.10: # If amount is lower than -10%: Display at -10.5% on scale, display actual amount (with no decimal point) above the icon
pos_v = -.105
elif v > .15: # If amount is higher than 15%: Display at 15.5% on scale, display actual amount (with no decimal point) above the icon
pos_v = .155
else:
pos_v = v # If both icons are either above 15% or below-10% on the same metric, only display actual amount for advisory icon
x = x0 + (min(max(pos_v, -0.105), .155)+.1)*4*w # tick coord
self.set_element_pos(tick, x-tick_w/2, None) # set tick on the chart
value_e_w = self.get_element_sizes(value_e)[0] # tick label
# if higher than 99%, just display 99%; if Lower than -99%, just display -99%
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)+'%'
self.set_element_text(value_e, text)
self.set_element_pos(value_e, x-value_e_w/2, None)
def fill_headline_metrics(self):
#If growth rate is negative: have chevron pointing down, else up.
for c in C('C4')+C('F4')+C('H4')+C('K4'):
val = self.get_cell(c)
remove(self.get_element_by_title('%s-arrow-%s' % (c, 'up' if val < 0 else 'down')))
#In 'Advisory' write 'Analytics' instead of 'Risk Analytics'.
C2 = self.get_cell('C2')
if C2 == 'Risk Analytics':
C2 = 'Analytics'
self.set_text('C2', C2)
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_blue_rect(self):
separate_chart_group = self.E('separate-revenue-and-earnings')
def rotate(e):
# rotate elements(90 degrees)
self.set_element_rotation(e, -5400000)
w, h = self.get_element_sizes(e)
shift = (h-w)/2
coords = self.get_element_coords(e)
x, y = coords[0], coords[1]
x += shift
y -= shift
self.set_element_pos(e, x, y)
self.set_element_text_alignment(e, 'r')
coords = self.get_element_coords(self.shapes['big-blue-rect']) # base rectangle coords
w, h = coords[2]-coords[0], coords[3]-coords[1]
x0, y0 = coords[0], coords[3]
# minor rectangles sizes
sizes = zip(
'BCDE',
[self.get_cell(c) for c in C('B12')],
[self.get_cell(c) for c in C('B16')],
)
# If below 3.0% width: Do not show section at all.
sizes = [(l, wn, hn) if wn > 0.03 else (l, 0., 0.) for l, wn, hn in sizes]
norm = sum(wn for l, wn, hn in sizes)
x = x0
for l, wpn, hpn in sizes:
wn = wpn*w/norm
if wpn > .11:
# If section is above 11% width (row 12):
# Max height of Earnings box (light blue) is 80%
# Min Height of Earnings box (light blue) is 13.33%
hpn_min, hpn_max = .1333, .8
def format_money(v):
if v > 9999:
return '$%sB' % self.format_float(v/10**3, prec=1)
return '$%s' % self.format_float(v, prec=0)
elif wpn > .065:
# If section is below 10% width, and above 6.5% width:
# Min Height of Earnings box (light blue) is 20.5%
# Max height of earnings box (light blue) is 72%
hpn_min, hpn_max = .205, .72
def format_money(v):
return '$%s' % self.format_float(min(v, 999), prec=0)
elif wpn > .03:
# If below 6.5% width, above 3.0% width:
# Max height of earnings box is 77%
# Minimum height of earnings box is 0%
hpn_min, hpn_max = 0, .77
else:
# Do not show section at all.
hpn = 0
hn = min(max(hpn, hpn_min), hpn_max)*h if wpn else 0
# set rectangle
r = self.shapes['%s-rect' % l]
if wpn == 0:
# if width == 0, remove
remove(r)
else:
# set rectangle size and position
self.set_element_pos(r, x, y0-hn)
self.set_element_size(r, wn, hn)
# set section title(A&F, ANLYT, Bussiness Risk, Tech Risk)
section_title = self.get_element_by_title('%s9' % l)
if wpn == 0:
# if width == 0, remove rectangle
remove(section_title)
else:
self.set_element_pos(section_title, x, coords[1])
if wpn <= .125:
# If width is below 12.5%, rotate name vertically
if wn < self.get_element_sizes(section_title)[1]:
self.set_element_text_inset(section_title, 't', 0)
self.set_element_text_inset(section_title, 'b', 0)
self.set_element_text_vert_alignment(section_title, 'ctr')
self.set_element_size(section_title, None, wn)
rotate(section_title)
# set earnings
earnings = self.get_element_by_title('%s13' % l)
if wpn <= .065:
remove(earnings)
else:
earnings_value = self.get_cell('%s13' % l)/10.**6
# if width > 11%:
# if height of earnings box is above 30%: display Earnings value($) in white, at 29.15%
# if 6.5% < width < 11% :
# If earnings box is above 38%: display earnings value ($)in white, at 37.5%
# If earnings box is below 38%: display earnings value($) in light blue, at 53.33%
t, low, high = (.3, .2915, .3677) if wpn > .11 else (.38, .375, .5333)
earnings_hp = low if hpn > t else high
color = 'FFFFFF' if hpn > t else'00B0F0' # set color of ERN label
# set earnings label position, text and color
self.set_element_pos(earnings, x, y0-earnings_hp*h)
self.set_element_text(earnings, format_money(earnings_value))
self.set_element_text_color(earnings, color)
# if section width below 11% rotate it
if wpn < .11 and round(earnings_value, 0) >= 100:
rotate(earnings)
revenue_rotated = False
margin_rotated = False
for c in [10, 14]:
title = '%s%s' % (l, c) #rectangle values
val = self.get_cell(title)
if c == 10:
val /= 10.**6 # format REV value
s = self.get_element_by_title(title)
if wpn <= .065:
remove(s) # remove values if width < 6.5%
else:
# set REV label on template
self.set_element_pos(s, x, None)
self.set_element_text(s, format_money(val) if c == 10 else self.format_percent(val, prec=0)+'%') # format values
if c == 10 and wpn < .125:
# If width <12.5%, rotate name vertically, and move Revenue ($) at 70%
self.set_element_pos(s, None, y0-.7*h)
if wpn < .11 and (c == 10 and round(val, 0) >= 100 or val == 1):
if c == 14:
margin_rotated = True
else:
revenue_rotated = True
rotate(s)
# set REV arrow
rev_arrow = self.get_element_by_title('%s11-arrow' % l)
if wpn <= .065:
# If below 6.5% width, remove arrow
remove(rev_arrow)
else:
self.set_element_text(rev_arrow, self.format_percent(self.get_cell('%s11' % l), prec=0)+'%')
rev_w, rev_h = self.get_element_sizes(self.get_element_by_title('%s10' % l))
shift = rev_w-self.get_element_sizes(rev_arrow)[0]
self.set_element_pos(rev_arrow, x+shift/2, None)
# If width < 12.5%, move Revenue Growth(%) at 55%
if wpn < .125:
self.set_element_pos(rev_arrow, None, y0-.55*h)
if revenue_rotated:
rot_shift = (rev_h - rev_w)/2
self.mod_element_pos(rev_arrow, rot_shift, -rot_shift)
# set MARG arrow
if wpn > .065:
marg_arrow_val = self.get_cell('%s15' % l) # MARG value
marg_arrow = self.clone_template('marg-arrow-up' if marg_arrow_val >= 0 else 'marg-arrow-down') # MARG arrow up/down
self.set_element_text(marg_arrow, self.format_float(min(abs(marg_arrow_val), 999), prec=0)+'BPS') # set MARG value(BPS)
marg_w, marg_h = self.get_element_sizes(self.get_element_by_title('%s14' % l)) # MARG value(%)
shift = marg_w-self.get_element_sizes(marg_arrow)[0]
self.set_element_pos(marg_arrow, x+shift/2, None)
separate_chart_group.append(marg_arrow)
if margin_rotated:
rot_shift = (marg_h - marg_w)/2
self.mod_element_pos(marg_arrow, rot_shift, -rot_shift)
x += wn
def fill_blue_rect(self):
separate_chart_group = self.E("separate-revenue-and-earnings")
coords = self.get_element_coords(self.shapes["big-blue-rect"]) # base rectangle
w, h = coords[2] - coords[0], coords[3] - coords[1] # rectangle width, height
x0, y0 = coords[0], coords[3]
# section sizes and values
sizes = zip(
"BCDE",
[self.get_cell(c) for c in C("B12", "E12")], # Width (%)
[self.get_cell(c) for c in C("B18", "E18")], # CE Height (%, if width != 0)
[self.get_cell(c) for c in C("B14", "E14")], # EBA Height (Dotted Line), if width != 0
)
# If width of section is below 4% Do not display
sizes = [(l, wn, hn, lpn) if wn > 0.04 else (l, 0.0, 0.0) for l, wn, hn, lpn in sizes]
norm = sum(wn for l, wn, hn, lpn in sizes) # normalize section width
x = x0
for l, wpn, hpn, lpn in sizes:
wn = wpn * w / norm # normalized section width
gap_size = 0.08 # minimum difference between CE Margin height and EBA Margin Height is 8%
hpn_min, hpn_max = 0.2, 0.69 # Min CE Height; Max CE Height
lpn_min, lpn_max = 0.28, 0.77 # Min EBA Height; Max EBA Height
if wpn > 0.10:
# If Width of a section is above 10%
def format_money(v): # Max value of all $ figures is $9999
if v > 9999:
return "$%sB" % self.format_float(v / 10 ** 3, prec=1)
return "$%s" % self.format_float(v, prec=0)
font_size = 15 # Font size of all values is 15 pt
elif wpn > 0.07:
# If Width of a Section is between 10%-7%
def format_money(v): # Max value of all $ figures is $9999
if v > 999:
return "$%sB" % self.format_float(v / 10 ** 3, prec=1)
return "$%s" % self.format_float(v, prec=0)
font_size = 12 # Change font size of all values to 12pt
elif wpn > 0.04:
# If width of a section is between 7%-4%
hpn_min, hpn_max = 0, 0.9 # No MIN value; MAX of Blue square 90%
lpn_max = 0.91 # MAX of Dotted line is 91%
else:
# if width == 0, remove section
hpn = 0
gape_needed_adjustment = max(gap_size - (lpn - hpn), 0) # diff between CE Margin h. and EBA Margin h.
if gape_needed_adjustment:
# To fit text, minimum difference between CE Margin height and EBA Margin Height is 8%
# If difference is less than 8%:
if lpn_max - lpn < gape_needed_adjustment / 2:
hpn -= gape_needed_adjustment - (lpn_max - lpn)
lpn = lpn_max
elif hpn - hpn_min < gape_needed_adjustment / 2:
lpn += gape_needed_adjustment - (hpn - hpn_min)
hpn = hpn_min
else:
hpn -= gape_needed_adjustment / 2
lpn += gape_needed_adjustment / 2
hn = min(max(hpn, hpn_min), hpn_max) * h if wpn else 0 # CE Height(%), if width != 0
ln = min(max(lpn, lpn_min), lpn_max) * h if wpn else 0 # EBA Height, if width != 0
rect = self.shapes["%s-rect" % l] # section
line = self.get_element_by_title("%s-line" % l) # dotted line(EBA)
revenue = self.get_element_by_title("%s10" % l) # REV.
revenue_arrow = self.get_element_by_title("%s11-arrow" % l) # REV. arrow
earnings = self.get_element_by_title("%s13" % l) # ABA
ce_marg = self.get_element_by_title("%s15" % l) # C.E
ce_marg_p = self.get_element_by_title("%s16" % l) # C.E Marg
# if section exists, set icon under it(Advisory, Audit etc)
if wpn:
icon = self.clone_template("icon-%s" % self.get_cell("%s9" % l).lower())
self.set_element_pos(icon, x + self.get_element_coords(icon)[1] - coords[1], None)
separate_chart_group.append(icon)
# If width of a section is between 7%-4%
if wpn <= 0.07:
# Remove all values, just display correct heights of margin boxes/lines
for e in [revenue, revenue_arrow, earnings, ce_marg, ce_marg_p]:
remove(e)
else:
# If Width of a Section is above 7% of total width
# Set all values
for e in [revenue, earnings, ce_marg, ce_marg_p]:
self.set_element_text_size(e, font_size)
# display all values
self.set_element_pos(revenue, x, None) # set REV.
self.set_element_text(revenue, format_money(self.get_cell("%s10" % l) / 10.0 ** 6))
revenue_arrow_shift = (
self.get_element_sizes(revenue)[0] - self.get_element_sizes(revenue_arrow)[0]
) # set REV. arrow
self.set_element_pos(revenue_arrow, x + revenue_arrow_shift / 2, None)
self.set_element_text(revenue_arrow, self.format_percent(self.get_cell("%s11" % l), prec=0) + "%")
earnings_value = self.get_cell("%s13" % l) / 10.0 ** 6 # set EBA
self.set_element_pos(earnings, x, y0 - ln)
self.set_element_text(earnings, format_money(earnings_value))
ce_marg_val = self.get_cell("%s15" % l) / 10.0 ** 6 # set C.E
self.set_element_pos(ce_marg, x, None)
self.set_element_text(ce_marg, format_money(ce_marg_val))
ce_marg_p_val = self.get_cell("%s16" % l) # set C.E Marg
self.set_element_pos(ce_marg_p, x, None)
self.set_element_text(ce_marg_p, self.format_percent(ce_marg_p_val, prec=1) + "%")
marg_arrow_val = self.get_cell("%s17" % l) # set C.E Marg arrow
marg_arrow = self.clone_template("marg-arrow-up" if marg_arrow_val >= 0 else "marg-arrow-down")
shift = self.get_element_sizes(ce_marg_p)[0] - self.get_element_sizes(marg_arrow)[0]
self.set_element_text(marg_arrow, self.format_float(min(abs(marg_arrow_val), 999), prec=0) + "BPS")
self.set_element_pos(marg_arrow, x + shift / 2, None)
separate_chart_group.append(marg_arrow)
if wpn == 0:
# if section width == 0, remove section
remove(rect)
remove(line)
else:
# add section and EBA line
self.set_element_pos(rect, x, y0 - hn)
self.set_element_size(rect, wn, hn)
self.set_element_pos(line, x, y0 - ln)
self.set_element_size(line, wn, None)
x += wn
if l != "E":
s = self.get_elements_by_title("%s-sep" % l)[0]
if wn == 0:
remove(s)
else:
self.set_element_pos(s, x, None)
def fill_chart(chart_name, values, non_data_row):
# if chart values are empty (no No-Period Driven expenses etc) than remove chart and return
if len(values) == 1:
remove(self.get_element_by_title(chart_name))
return
chart = self.get_chart_by_title(chart_name) # get pie chart
label_angle = math.radians(angle)
# delete all numbers on pie chart
numCache = chart.xpath('//c:numCache')[0]
ptCount = chart.xpath('.//c:ptCount', numCache)[0]
ptCount.set('val', str(len(values)))
for e in chart.xpath('.//c:pt', numCache):
remove(e)
# delete all strings on pie chart
strCache = chart.xpath('//c:cat/c:strRef/c:strCache')[0]
ptCount = chart.xpath('.//c:ptCount', strCache)[0]
ptCount.set('val', str(len(values)))
for e in chart.xpath('.//c:pt', strCache):
remove(e)
ser = chart.xpath('//c:ser')[0]
for e in chart.xpath('.//c:dPt', ser):
remove(e)
dLbls = chart.xpath('.//c:dLbls', ser)[0]
for e in chart.xpath('c:dLbl', dLbls):
remove(e)
chart.xpath('//c:dLbls/c:showVal')[0].set('val', '1')
chart.xpath('//c:dLbls/c:showLeaderLines')[0].set('val', '0')
chart.xpath('//c:firstSliceAng')[0].set('val', self.format_float(angle, prec=0))
txPr = chart.xpath('//c:dLbls/c:txPr')[0]
coords = self.get_plot_area_coords_from_chart(chart_name)
r = max((coords[2]-coords[0]), (coords[3]-coords[1]))*.5
c = ((coords[2]+coords[0])*.5, (coords[3]+coords[1])*.5)
for i, row in enumerate(values):
pt = etree.SubElement(strCache, '{%s}pt' % chart.NS['c'])
pt.set('idx', str(i))
v = etree.SubElement(pt, '{%s}v' % chart.NS['c'])
v.text = row[0]
pt = etree.SubElement(numCache, '{%s}pt' % chart.NS['c'])
pt.set('idx', str(i))
v = etree.SubElement(pt, '{%s}v' % chart.NS['c'])
v.text = self.format_float(row[2]/10.**6, prec=0)
dPt = etree.SubElement(ser, '{%s}dPt' % chart.NS['c'])
etree.SubElement(dPt, '{%s}idx' % chart.NS['c']).set('val', str(i))
spPr = etree.SubElement(dPt, '{%s}spPr' % chart.NS['c'])
if row is non_data_row:
etree.SubElement(spPr, '{%s}noFill' % chart.NS['a'])
else:
solidFill = etree.SubElement(spPr, '{%s}solidFill' % chart.NS['a'])
etree.SubElement(solidFill, '{%s}srgbClr' % chart.NS['a']).set('val', colors.pop())
dLbl = etree.Element('{%s}dLbl' % chart.NS['c'])
etree.SubElement(dLbl, '{%s}idx' % chart.NS['c']).set('val', str(i))
etree.SubElement(dLbl, '{%s}showVal' % chart.NS['c']).set('val', '0' if row is non_data_row else '1')
etree.SubElement(dLbl, '{%s}dLblPos' % chart.NS['c']).set('val', 'inEnd')
if float(row[2])/total <= .05:
txPr_clone = copy.deepcopy(txPr)
self.xpath('.//a:defRPr', txPr_clone)[0].set('sz', '1200')
dLbl.append(txPr_clone)
elif row[2]/10.**6 > 9999:
etree.SubElement(dLbl, '{%s}layout' % chart.NS['c'])
rich_e = etree.SubElement(
etree.SubElement(
dLbl,
'{%s}tx' % chart.NS['c']
),
'{%s}rich' % chart.NS['c']
)
etree.SubElement(
rich_e,
'{%s}bodyPr' % chart.NS['a'],
)
etree.SubElement(
rich_e,
'{%s}lstStyle' % chart.NS['a'],
)
etree.SubElement(
etree.SubElement(
etree.SubElement(
rich_e,
'{%s}p' % chart.NS['a'],
),
'{%s}r' % chart.NS['a'],
),
'{%s}t' % chart.NS['a']
).text = '$9,999'
dLbls.insert(i, dLbl)
element_names = [
'showLegendKey',
'showCatName',
'showSerName',
'showPercent',
'showBubbleSize',
'showLeaderLines',
]
for e_name in element_names:
etree.SubElement(dLbl, '{%s}%s' % (chart.NS['c'], e_name)).set('val', '0')
def get_shift(e, angle):
angle -= int(angle)/360*360
w, h = self.get_element_sizes(e)
pPr = self.xpath('.//a:pPr', e)[0]
bodyPr = self.xpath('.//a:bodyPr', e)[0]
if 45 <= angle < 135:
x = 0
pPr.set('algn', 'l')
elif 225 <= angle < 315:
x = w
pPr.set('algn', 'r')
else:
x = w/2
if 45 <= angle < 135 or 225 <= angle < 315:
y = h/2
elif 135 <= angle < 225:
y = 0
bodyPr.set('anchor', 'b')
else:
y = h
bodyPr.set('anchor', 't')
return (x, y+h/2) # should be (x, y)
angle_n = row[2]/float(total)*2*math.pi
if row is not non_data_row:
point_angle = label_angle+angle_n*.5
p = (c[0]+r*math.sin(point_angle), c[1]-r*math.cos(point_angle))
label = self.clone_template('estimated-uses-chart-label')
shift_x, shift_y = get_shift(label, math.degrees(point_angle))
self.set_element_text(label, row[0])
self.set_element_pos(label, p[0]-shift_x, p[1]-shift_y)
chart_group.append(label)
label_angle += angle_n
chart.write()
def fill_cost_breakdown(self):
def rotate(e):
# rotate on 90 deg
self.xpath('.//a:xfrm', e)[0].set('rot', '-5400000')
w, h = self.get_element_sizes(e)
shift = (h-w)/2
coords = self.get_element_coords(e)
x, y = coords[0], coords[1]
x += shift
y -= shift
self.set_element_pos(e, x, y)
try:
pPr = self.xpath('.//a:pPr', e)[0]
except ValueError:
pPr = etree.Element('{%s}pPr' % self.NS['a'])
self.xpath('.//a:p', e)[0].insert(0, pPr)
pPr.set('algn', 'r')
cols = 'BCDEF'
values = [] # (rec. width, amount($))
s = 0
for l in cols:
amount = round(self.get_cell('%s22' % l)/10.**6, 0) # Cerrent Amount
width = self.get_cell('%s24' % l) # % of Max
# If lower than 3% of total width do not display
# If Value is $10,000+ and width below 7.5% do not display
if width < .03 or amount >= 10**4 and width < .075:
width = 0
values.append((width, amount))
s += width # real width
values = [(w/s, a) for w, a in values] # change values for new width
marg = 12700*2
box = self.get_element_by_title('cost-breakdown-box') # chart
coords = self.get_element_coords(box)
x0, y0 = coords[0], coords[1]
w, h = self.get_element_sizes(box) # chart width and height
x = x0
for col, (wn, an) in zip(cols, values):
title = self.get_element_by_title('%s21' % col) # rectangle title(CS Sal. P&A etc)
amount = self.get_element_by_title('%s22' % col) # amount
yoy = self.get_element_by_title('%s23-arrow' % col) # YOY growth (arrow)
rect = self.get_element_by_title('%s24-rect' % col) # rectangle width
color = self.get_cell('%s25' % col) # rectangle color
color = '93C83D' if color.strip().lower() == 'green' else 'B1B3B5' # rectangle hex color
# if width == 0, remove section
if wn == 0:
for e in [title, amount, yoy, rect]:
remove(e)
continue
xn = wn*w
def format_money(val):
val = self.format_float(val, prec=0)
if len(val) > 3:
val = val[:-3] + ',' + val[-3:]
return '$' + val
font_size = 14 # Font size for all figures is 14pt
rotated = False
# If value is less than $999
if an < 999:
# If between 7% and 3% width
if wn < .07:
font_size = 12 # Change to 12pt font
rotated = True # Turn 90 degrees
# If value is between $1,000-$9,999
elif an < 9999:
# If width is between 9% and 7.5%
if .075 <= wn < .09:
font_size = 12 # display horizontally at 12 pt font
# If between 7.5% and 3%
elif wn < .075:
font_size = 10 # Change to 10pt font
rotated = False # Turn 90 degrees
else:
# If Value is $10,000+ (Max is $99,999)
if .075 <= wn < .1:
# If between 10% and 7.5%
format_money = lambda val: '$' + self.format_float(val/1000, prec=1) + 'B' # isplay in billions with 1 decimal point
font_size = 12 # display at 12 pt font
# set title position
self.set_element_pos(title, x, y0-2*h)
self.set_element_size(title, xn, 2*h)
self.set_element_text_direction(title, 'vert270' if rotated else None) # set rotation
if rotated:
self.set_element_text(title, self.get_cell('%s21' % col)[:7])
bodyPr = self.xpath('.//a:bodyPr', title)[0]
bodyPr.set('lIns', '0')
bodyPr.set('rIns', '0')
else:
lines = [''.join(c) for c in chunks(self.get_cell('%s21' % col), 7)][:3] # underline text
self.set_element_text_lines(title, lines)
# set amount
self.set_element_pos(amount, x, y0)
self.set_element_size(amount, xn, h)
self.set_element_text(amount, format_money(an))
self.set_element_text_size(amount, font_size)
self.set_element_text_direction(amount, 'vert270' if rotated else None)
# set YOY growth
self.set_element_pos(yoy, x+(xn-self.get_element_sizes(yoy)[0])/2, None)
self.set_element_text(yoy, self.format_percent(self.get_cell('%s23' % col), prec=1)+'%')
# set rectangle
self.set_element_pos(rect, x+marg, None)
self.set_element_size(rect, xn-2*marg, None)
srgbClr = self.xpath('.//a:srgbClr', rect)[0]
srgbClr.set('val', color)
x += xn
def fill_blue_rect(self):
separate_chart_group = self.E('separate-revenue-and-earnings')
coords = self.get_element_coords(self.shapes['big-blue-rect'])
w, h = coords[2]-coords[0], coords[3]-coords[1] # chart widht and height
x0, y0 = coords[0], coords[3]
# rectangle sizes and values
sizes = zip(
'BCDE',
[self.get_cell(c) for c in C('B12', 'E12')], # rectangle width(%)
[self.get_cell(c) for c in C('B18', 'E18')], # CE Height(%)
[self.get_cell(c) for c in C('B14', 'E14')], # EBA Height (Dotted Line)(%)
)
# f width of section is below 4% Do not display. Add width to next smallest section to maintain size.
sizes = [(l, wn, hn, lpn) if wn > 0.04 else (l, 0., 0.) for l, wn, hn, lpn in sizes]
norm = sum(wn for l, wn, hn, lpn in sizes)
x = x0
for l, wpn, hpn, lpn in sizes:
wn = wpn*w/norm # normilize width
hpn_min, hpn_max = .2, .69 # Min height of CE Height is 20%; Max height of CE Height is 69%
lpn_max = .77 # The Maximum height of the dotted line (EBA Height) is 77%
if wpn > .10:
# If Width of a section is above 10% of total width
def format_money(v):
# Max value of all $ figures is $9999
if v > 9999:
# If higher put as $1X.XB (only 1 decimal point)
return '$%sB' % self.format_float(v/10**3, prec=1)
return '$%s' % self.format_float(v, prec=0)
font_size = 15 # Font size of all values is 15 pt
elif wpn > .07:
# If Width of a Section is between 10%-7% of total width
def format_money(v):
# If a $ value reaches 4 digits, display in Billions with 1 decimal place($7894 = $7.9B)
if v > 999:
return '$%sB' % self.format_float(v/10**3, prec=1)
return '$%s' % self.format_float(v, prec=0)
font_size = 12 # Change font size of all values to 12pt
elif wpn > .04:
# If width of a section is between 7%-4%
hpn_min, hpn_max = 0, .9 # No MIN CE Height value; Max CE Height is 90%
lpn_max = .91 # MAX of Dotted line is 91%
else:
# If width of section is below 4%
# Do not display.
hpn = 0
hn = min(max(hpn, hpn_min), hpn_max)*h if wpn else 0 # CE Height
ln = min(lpn, lpn_max)*h if wpn else 0 # EBA Height
rect = self.shapes['%s-rect' % l] # section rectangle
line = self.get_element_by_title('%s-line' % l) # dotted line
revenue = self.get_element_by_title('%s10' % l) # REV value
revenue_arrow = self.get_element_by_title('%s11-arrow' % l) # Revenue Growth From Prior
earnings = self.get_element_by_title('%s13' % l) # EBA
ce_marg = self.get_element_by_title('%s15' % l) # Controllable Earnings
ce_marg_p = self.get_element_by_title('%s16' % l) # CE Margin
if wpn <= .07:
# If width of a section is between 7%-4%
# Remove all values, just display correct heights of margin boxes/lines
for e in [revenue, revenue_arrow, earnings, ce_marg, ce_marg_p]:
remove(e)
else:
# set section value labels
for e in [revenue, earnings, ce_marg, ce_marg_p]:
self.set_element_text_size(e, font_size)
# section icon(Ad, A, C and T)
icon = self.clone_template('icon-%s' % self.get_cell('%s9' % l).lower())
self.set_element_pos(icon, x, None)
separate_chart_group.append(icon)
# set Rev value
self.set_element_pos(revenue, x, None)
self.set_element_text(revenue, format_money(self.get_cell('%s10' % l)/10.**6))
# set revenue arrow
revenue_arrow_shift = self.get_element_sizes(revenue)[0]-self.get_element_sizes(revenue_arrow)[0]
self.set_element_pos(revenue_arrow, x+revenue_arrow_shift/2, None)
self.set_element_text(revenue_arrow, self.format_percent(self.get_cell('%s11' % l), prec=0)+'%')
# set EBA
earnings_value = self.get_cell('%s13' % l)/10.**6
self.set_element_pos(earnings, x, y0-ln)
self.set_element_text(earnings, format_money(earnings_value))
# Controllable Earnings (C.E)
ce_marg_val = self.get_cell('%s15' % l)/10.**6
self.set_element_pos(ce_marg, x, None)
self.set_element_text(ce_marg, format_money(ce_marg_val))
# CE Margin (C.E Marg)
ce_marg_p_val = self.get_cell('%s16' % l)
self.set_element_pos(ce_marg_p, x, None)
self.set_element_text(ce_marg_p, self.format_percent(ce_marg_p_val, prec=0)+'%')
# set CE Margin arrow
marg_arrow_val = self.get_cell('%s17' % l)
marg_arrow = self.clone_template('marg-arrow-up' if marg_arrow_val >= 0 else 'marg-arrow-down')
shift = self.get_element_sizes(ce_marg_p)[0]-self.get_element_sizes(marg_arrow)[0]
self.set_element_text(marg_arrow, self.format_float(min(abs(marg_arrow_val), 999), prec=0)+'BPS')
self.set_element_pos(marg_arrow, x+shift/2, None)
separate_chart_group.append(marg_arrow)
if wpn == 0:
# If width is 0, remove section
remove(rect)
remove(line)
else:
# set rectangle
self.set_element_pos(rect, x, y0-hn)
self.set_element_size(rect, wn, hn)
# set dotted line
self.set_element_pos(line, x, y0-ln)
self.set_element_size(line, wn, None)
x += wn
if l != 'E':
s = self.get_elements_by_title('%s-sep' % l)[0]
if wn == 0:
remove(s)
else:
self.set_element_pos(s, x, 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,
)
