import numpy as np
import pandas as pd
import IPython.display as display
from reportlab.lib import colors
from tia.analysis.perf import returns_cumulative
import tia.rlab as rlab
# The goal is to build a table which looks like the following
display.Image('returns.png')
# create fake returns for year
index = pd.PeriodIndex(start='1/2014', end='12/2014', freq='M')
returns = pd.DataFrame(.025 * np.random.randn(12, 3), index=index, columns=['Portfolio_{0}'.format(i) for i in range(1, 4)])
# remove last month to see how table handles no data
returns.iloc[-1, :] = np.nan
returns['Fund'] = returns.sum(axis=1)
returns
Portfolio_1 | Portfolio_2 | Portfolio_3 | Fund | |
---|---|---|---|---|
2014-01 | -0.048752 | -0.004033 | 0.005801 | -0.046984 |
2014-02 | -0.013185 | -0.001041 | 0.040603 | 0.026377 |
2014-03 | 0.006299 | 0.015496 | 0.006989 | 0.028784 |
2014-04 | 0.017744 | 0.007020 | -0.001317 | 0.023446 |
2014-05 | 0.019605 | -0.056622 | -0.019955 | -0.056972 |
2014-06 | 0.043809 | -0.007819 | -0.020782 | 0.015208 |
2014-07 | 0.012296 | -0.001631 | -0.005637 | 0.005028 |
2014-08 | -0.020952 | 0.026630 | 0.014684 | 0.020362 |
2014-09 | 0.000942 | -0.038948 | 0.002801 | -0.035205 |
2014-10 | -0.022562 | -0.013278 | -0.026065 | -0.061905 |
2014-11 | -0.021469 | -0.003409 | 0.009949 | -0.014928 |
2014-12 | NaN | NaN | NaN | NaN |
# Create the View for the pdf
view = returns.copy()
view.index = [i.strftime('%b') for i in view.index]
# YTD Returns
ytd = returns_cumulative(view)
ytd.name = 'YTD'
# transpose so dates are the columns
view = view.append(ytd.to_frame().T)
view = view.T
view.index.name = 'Portfolio'
# make a multi-level header
view.columns = pd.MultiIndex.from_arrays([ ['2014 Returns']*len(view.columns), view.columns])
view
2014 Returns | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | YTD | |
Portfolio | |||||||||||||
Portfolio_1 | -0.048752 | -0.013185 | 0.006299 | 0.017744 | 0.019605 | 0.043809 | 0.012296 | -0.020952 | 0.000942 | -0.022562 | -0.021469 | NaN | -0.029194 |
Portfolio_2 | -0.004033 | -0.001041 | 0.015496 | 0.007020 | -0.056622 | -0.007819 | -0.001631 | 0.026630 | -0.038948 | -0.013278 | -0.003409 | NaN | -0.077535 |
Portfolio_3 | 0.005801 | 0.040603 | 0.006989 | -0.001317 | -0.019955 | -0.020782 | -0.005637 | 0.014684 | 0.002801 | -0.026065 | 0.009949 | NaN | 0.005297 |
Fund | -0.046984 | 0.026377 | 0.028784 | 0.023446 | -0.056972 | 0.015208 | 0.005028 | 0.020362 | -0.035205 | -0.061905 | -0.014928 | NaN | -0.098515 |
# Setup the builder and any templates
FONT_SZ = 8
FONT = 'Helvetica'
FONT_BOLD = 'Helvetica-Bold'
FONT_COLOR = colors.HexColor('#3b595f')
GRID_COLOR = colors.HexColor('#a8c2cb')
RPAD = LPAD = 3
TPAD = BPAD = 4
LEADING = FONT_SZ
ROW_COLORS = [colors.HexColor('#eff4f6'), colors.white]
pdf_path = 'returns_example.pdf'
pdf = rlab.PdfBuilder(pdf_path, showBoundary=0)
t = rlab.GridTemplate('page1', 100, 100)
t.define_frames({'tbl': t[5:95, 5:95]}) # whole page
t.register(pdf)
# helper method to place box around a region
def add_box(rng):
rng.apply_style('BOX', .7, GRID_COLOR)
tf = rlab.TableFormatter(view)
# Styles for all cells
# Use direct method
tf.all.apply_styles({
'FONTSIZE': FONT_SZ, 'FONTNAME': FONT, 'TEXTCOLOR': FONT_COLOR,
'VALIGN': 'MIDDLE', 'ALIGN': 'RIGHT',
'LEADING': LEADING,
'RIGHTPADDING': RPAD, "BOTTOMPADDING": BPAD, 'TOPPADDING': TPAD, 'LEFTPADDING': LPAD,
})
#
# Can use methods which means you do not have to remember style names and arguments
#
box_args = {'weight': .7, 'color': GRID_COLOR}
regions = [tf.all, tf.cells, tf.header, tf.index, tf.all.iloc[-1:, :], tf.header.iloc[:1, :]]
[region.set_box(**box_args) for region in regions]
# cells Style
tf.cells.set_row_backgrounds(ROW_COLORS)
# make the last column bold
tf.all.iloc[-1:, :].set_font(FONT_BOLD)
# make the last row bold
tf.all.iloc[:, -1:].set_font(FONT_BOLD)
# format as percent and use red for negatives
tf.cells.percent_format()
# Header
tf.header.set_fontname(FONT_BOLD)
tf.header.set_align_center()
tf.header.detect_colspans()
# Index
tf.index.set_font(FONT_BOLD)
tf.index.set_row_backgrounds(ROW_COLORS)
# Index-Header
tf.index_header.set_fontname(FONT_BOLD)
# don't allow table to expand or shrink
pdf.build_page('page1', {'tbl': tf.build(expand=None, shrink=None)})
# allow table to expand both width and height wise
pdf.build_page('page1', {'tbl': tf.build(expand='wh', shrink=None)})
pdf.save()