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.013605 | -0.008504 | -0.034107 | -0.056216 |
2014-02 | -0.023256 | 0.025989 | 0.013025 | 0.015758 |
2014-03 | -0.038626 | 0.038483 | 0.061109 | 0.060967 |
2014-04 | 0.022628 | 0.043690 | 0.030722 | 0.097040 |
2014-05 | -0.010851 | 0.009275 | 0.013411 | 0.011835 |
2014-06 | 0.003168 | 0.005415 | 0.014891 | 0.023475 |
2014-07 | 0.010063 | 0.000654 | -0.016603 | -0.005885 |
2014-08 | 0.005035 | 0.017843 | -0.019654 | 0.003224 |
2014-09 | 0.012234 | 0.000997 | 0.002639 | 0.015869 |
2014-10 | 0.041136 | 0.020191 | 0.008405 | 0.069732 |
2014-11 | 0.033892 | 0.022902 | -0.001689 | 0.055105 |
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.013605 | -0.023256 | -0.038626 | 0.022628 | -0.010851 | 0.003168 | 0.010063 | 0.005035 | 0.012234 | 0.041136 | 0.033892 | NaN | 0.039610 |
Portfolio_2 | -0.008504 | 0.025989 | 0.038483 | 0.043690 | 0.009275 | 0.005415 | 0.000654 | 0.017843 | 0.000997 | 0.020191 | 0.022902 | NaN | 0.190345 |
Portfolio_3 | -0.034107 | 0.013025 | 0.061109 | 0.030722 | 0.013411 | 0.014891 | -0.016603 | -0.019654 | 0.002639 | 0.008405 | -0.001689 | NaN | 0.071051 |
Fund | -0.056216 | 0.015758 | 0.060967 | 0.097040 | 0.011835 | 0.023475 | -0.005885 | 0.003224 | 0.015869 | 0.069732 | 0.055105 | NaN | 0.321348 |
# 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
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,
})
# Outline regions
add_box(tf.all) # Box around the table
add_box(tf.cells) # Box around the cells
add_box(tf.header) # Box around the header
add_box(tf.index) # Box around the portfolio names
add_box(tf.all.iloc[-1:, :]) # Box above last line (separate total)
add_box(tf.header.iloc[:1, :]) # box around the 2014 returns
#add_box(tf.all.iloc[:, -1:])
# cells Style
tf.cells.apply_style("ROWBACKGROUNDS", ROW_COLORS)
# make the last column bold
tf.all.iloc[-1:, :].apply_style('FONTNAME', FONT_BOLD)
# make the last row bold
tf.all.iloc[:, -1:].apply_style('FONTNAME', FONT_BOLD)
# format as percent and use red for negatives
tf.cells.apply_number_format(rlab.PercentFormatter)
# Header
tf.header.apply_style('FONTNAME', FONT_BOLD)
tf.header.apply_style('ALIGN', 'CENTER')
tf.header.detect_colspans()
# Index
tf.index.apply_style('FONTNAME', FONT_BOLD)
tf.index.apply_style("ROWBACKGROUNDS", ROW_COLORS)
# Index-Header
tf.index_header.apply_style('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()