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