def plot_table(returns,
w,
MAR=0,
alpha=0.05,
height=9,
width=12,
t_factor=252,
ax=None):
r"""
Create a table with information about risk measures and risk adjusted
return ratios.
Parameters
----------
returns : DataFrame
Assets returns.
w : DataFrame
Portfolio weights.
MAR: float, optional
Minimum acceptable return.
alpha: float, optional
Significance level for VaR, CVaR, EVaR, DaR and CDaR.
height : float, optional
Height of the image in inches. The default is 9.
width : float, optional
Width of the image in inches. The default is 12.
t_factor : float, optional
Factor used to annualize expected return and expected risks for
risk measures based on returns (not drawdowns). The default is 252.
.. math::
\begin{align}
\text{Annualized Return} & = \text{Return} \, \times \, \text{t_factor} \\
\text{Annualized Risk} & = \text{Risk} \, \times \, \sqrt{\text{t_factor}}
\end{align}
ax : matplotlib axis, optional
If provided, plot on this axis. The default is None.
Raises
------
ValueError
When the value cannot be calculated.
Returns
-------
ax : matplotlib axis
Returns the Axes object with the plot for further tweaking.
Example
-------
::
ax = plf.plot_table(returns=Y, w=w1, MAR=0, alpha=0.05, ax=None)
.. image:: images/Port_Table.png
"""
if not isinstance(returns, pd.DataFrame):
raise ValueError("returns must be a DataFrame")
if not isinstance(w, pd.DataFrame):
raise ValueError("w must be a DataFrame")
if returns.shape[1] != w.shape[0]:
a1 = str(returns.shape)
a2 = str(w.shape)
raise ValueError("shapes " + a1 + " and " + a2 + " not aligned")
if ax is None:
ax = plt.gca()
fig = plt.gcf()
fig.set_figwidth(width)
fig.set_figheight(height)
mu = returns.mean()
cov = returns.cov()
days = (returns.index[-1] - returns.index[0]).days + 1
X = returns @ w
X = X.to_numpy().ravel()
rowLabels = [
"Profitability and Other Inputs",
"Mean Return (1)",
"Compound Annual Growth Rate (CAGR)",
"Minimum Acceptable Return (MAR) (1)",
"Significance Level",
"",
"Risk Measures based on Returns",
"Standard Deviation (2)",
"Mean Absolute Deviation (MAD) (2)",
"Semi Standard Deviation (2)",
"First Lower Partial Moment (FLPM) (2)",
"Second Lower Partial Moment (SLPM) (2)",
"Value at Risk (VaR) (2)",
"Conditional Value at Risk (CVaR) (2)",
"Entropic Value at Risk (EVaR) (2)",
"Worst Realization (2)",
"Skewness",
"Kurtosis",
"",
"Risk Measures based on Drawdowns (3)",
"Max Drawdown (MDD)",
"Average Drawdown (ADD)",
"Drawdown at Risk (DaR)",
"Conditional Drawdown at Risk (CDaR)",
"Ulcer Index",
"(1) Annualized, multiplied by " + str(t_factor),
"(2) Annualized, multiplied by √" + str(t_factor),
"(3) Based on uncompounded cumulated returns",
]
indicators = [
"",
(mu @ w).to_numpy().item() * t_factor,
np.power(np.prod(1 + X), 360 / days) - 1,
MAR,
alpha,
"",
"",
np.sqrt(w.T @ cov @ w).to_numpy().item() * t_factor**0.5,
rk.MAD(X) * t_factor**0.5,
rk.SemiDeviation(X) * t_factor**0.5,
rk.LPM(X, MAR=MAR, p=1) * t_factor**0.5,
rk.LPM(X, MAR=MAR, p=2) * t_factor**0.5,
rk.VaR_Hist(X, alpha=alpha) * t_factor**0.5,
rk.CVaR_Hist(X, alpha=alpha) * t_factor**0.5,
rk.EVaR_Hist(X, alpha=alpha)[0] * t_factor**0.5,
rk.WR(X) * t_factor**0.5,
st.skew(X, bias=False),
st.kurtosis(X, bias=False),
"",
"",
rk.MDD_Abs(X),
rk.ADD_Abs(X),
rk.DaR_Abs(X),
rk.CDaR_Abs(X, alpha=alpha),
rk.UCI_Abs(X),
"",
"",
"",
]
ratios = []
for i in range(len(indicators)):
if i < 6 or indicators[i] == "" or rowLabels[i] in [
"Skewness", "Kurtosis"
]:
ratios.append("")
else:
ratio = (indicators[1] - MAR) / indicators[i]
ratios.append(ratio)
for i in range(len(indicators)):
if indicators[i] != "":
if rowLabels[i] in ["Skewness", "Kurtosis"]:
indicators[i] = "{:.5f}".format(indicators[i])
else:
indicators[i] = "{:.4%}".format(indicators[i])
if ratios[i] != "":
ratios[i] = "{:.6f}".format(ratios[i])
data = pd.DataFrame({
"A": rowLabels,
"B": indicators,
"C": ratios
}).to_numpy()
ax.set_axis_off()
ax.axis("tight")
ax.axis("off")
colLabels = ["", "Values", "(Return - MAR)/Risk"]
colWidths = [0.45, 0.275, 0.275]
rowHeight = 0.07
table = ax.table(
cellText=data,
colLabels=colLabels,
colWidths=colWidths,
cellLoc="center",
loc="upper left",
bbox=[-0.03, 0, 1, 1],
)
table.auto_set_font_size(False)
cellDict = table.get_celld()
k = 1
rowHeight = 1 / len(rowLabels)
ncols = len(colLabels)
nrows = len(rowLabels)
for i in range(0, ncols):
cellDict[(0, i)].set_text_props(weight="bold",
color="white",
size="x-large")
cellDict[(0, i)].set_facecolor("darkblue")
cellDict[(0, i)].set_edgecolor("white")
cellDict[(0, i)].set_height(rowHeight)
for j in range(1, nrows + 1):
cellDict[(j, 0)].set_text_props(weight="bold",
color="black",
size="x-large",
ha="left")
cellDict[(j, i)].set_text_props(color="black", size="x-large")
cellDict[(j, 0)].set_edgecolor("white")
cellDict[(j, i)].set_edgecolor("white")
if k % 2 != 0:
cellDict[(j, 0)].set_facecolor("whitesmoke")
cellDict[(j, i)].set_facecolor("whitesmoke")
if j in [6, 19]:
cellDict[(j, 0)].set_facecolor("white")
cellDict[(j, i)].set_facecolor("white")
if j in [1, 7, 20]:
cellDict[(j, 0)].set_text_props(color="white")
cellDict[(j, 0)].set_facecolor("orange")
cellDict[(j, i)].set_facecolor("orange")
k = 1
k += 1
cellDict[(j, i)].set_height(rowHeight)
for i in range(0, ncols):
for j in range(nrows - 2, nrows + 1):
cellDict[(j, i)].set_text_props(weight="normal",
color="black",
size="large")
cellDict[(j, i)].set_facecolor("white")
fig = plt.gcf()
fig.tight_layout()
return ax
def plot_hist(returns, w, alpha=0.05, bins=50, height=6, width=10, ax=None):
r"""
Create a histogram of portfolio returns with the risk measures.
Parameters
----------
returns : DataFrame
Assets returns.
w : DataFrame of shape (n_assets, 1)
Portfolio weights.
alpha : float, optional
Significante level of VaR, CVaR and EVaR. The default is 0.05.
bins : float, optional
Number of bins of the histogram. The default is 50.
height : float, optional
Height of the image in inches. The default is 6.
width : float, optional
Width of the image in inches. The default is 10.
ax : matplotlib axis, optional
If provided, plot on this axis. The default is None.
Raises
------
ValueError
When the value cannot be calculated.
Returns
-------
ax : matplotlib axis.
Returns the Axes object with the plot for further tweaking.
Example
-------
::
ax = plf.plot_hist(returns=Y, w=w1, alpha=0.05, bins=50, height=6,
width=10, ax=None)
.. image:: images/Histogram.png
"""
if not isinstance(returns, pd.DataFrame):
raise ValueError("returns must be a DataFrame")
if not isinstance(w, pd.DataFrame):
raise ValueError("w must be a DataFrame")
if w.shape[1] > 1 and w.shape[0] == 0:
w = w.T
elif w.shape[1] > 1 and w.shape[0] > 0:
raise ValueError("w must be a DataFrame")
if returns.shape[1] != w.shape[0]:
a1 = str(returns.shape)
a2 = str(w.shape)
raise ValueError("shapes " + a1 + " and " + a2 + " not aligned")
if ax is None:
ax = plt.gca()
fig = plt.gcf()
fig.set_figwidth(width)
fig.set_figheight(height)
a = np.array(returns, ndmin=2) @ np.array(w, ndmin=2)
ax.set_title("Portfolio Returns Histogram")
n, bins1, patches = ax.hist(a,
bins,
density=1,
edgecolor="skyblue",
color="skyblue",
alpha=0.5)
mu = np.mean(a)
sigma = np.std(a, axis=0, ddof=1).item()
risk = [
mu,
mu - sigma,
mu - rk.MAD(a),
-rk.VaR_Hist(a, alpha),
-rk.CVaR_Hist(a, alpha),
-rk.EVaR_Hist(a, alpha)[0],
-rk.WR(a),
]
label = [
"Mean: " + "{0:.2%}".format(risk[0]),
"Mean - Std. Dev.(" + "{0:.2%}".format(-risk[1] + mu) + "): " +
"{0:.2%}".format(risk[1]),
"Mean - MAD(" + "{0:.2%}".format(-risk[2] + mu) + "): " +
"{0:.2%}".format(risk[2]),
"{0:.2%}".format(
(1 - alpha)) + " Confidence VaR: " + "{0:.2%}".format(risk[3]),
"{0:.2%}".format(
(1 - alpha)) + " Confidence CVaR: " + "{0:.2%}".format(risk[4]),
"{0:.2%}".format(
(1 - alpha)) + " Confidence EVaR: " + "{0:.2%}".format(risk[5]),
"Worst Realization: " + "{0:.2%}".format(risk[6]),
]
color = [
"b", "r", "fuchsia", "darkorange", "limegreen", "dodgerblue",
"darkgrey"
]
for i, j, k in zip(risk, label, color):
ax.axvline(x=i, color=k, linestyle="-", label=j)
# add a 'best fit' line
y = (1 / (np.sqrt(2 * np.pi) * sigma)) * np.exp(-0.5 * (1 / sigma *
(bins1 - mu))**2)
ax.plot(
bins1,
y,
"--",
color="orange",
label="Normal: $\mu=" + "{0:.2%}".format(mu) + "$%, $\sigma=" +
"{0:.2%}".format(sigma) + "$%",
)
factor = (np.max(a) - np.min(a)) / bins
ax.xaxis.set_major_locator(plt.AutoLocator())
ax.set_xticklabels(["{:3.2%}".format(x) for x in ax.get_xticks()])
ax.set_yticklabels(["{:3.2%}".format(x * factor) for x in ax.get_yticks()])
ax.legend(loc="upper right") # , fontsize = 'x-small')
ax.grid(linestyle=":")
ax.set_ylabel("Probability Density")
fig = plt.gcf()
fig.tight_layout()
return ax
def excel_report(returns, w, rf=0, alpha=0.05, t_factor=252, name="report"):
r"""
Create an Excel report (with formulas) with useful information to analyze
risk and profitability of investment portfolios.
Parameters
----------
returns : DataFrame
Assets returns.
w : DataFrame of size (n_assets, n_portfolios)
Portfolio weights.
rf : float, optional
Risk free rate or minimum aceptable return. The default is 0.
alpha : float, optional
Significante level of VaR, CVaR, EVaR, DaR and CDaR.
The default is 0.05.
t_factor : float, optional
Factor used to annualize expected return and expected risks for
risk measures based on returns (not drawdowns). The default is 252.
.. math::
\begin{align}
\text{Annualized Return} & = \text{Return} \, \times \, \text{t_factor} \\
\text{Annualized Risk} & = \text{Risk} \, \times \, \sqrt{\text{t_factor}}
\end{align}
name : str, optional
Name or name with path where the Excel report will be saved. If no
path is provided the report will be saved in the same path of
current file.
Raises
------
ValueError
When the report cannot be built.
Example
-------
::
rp.excel_report(returns, w, MAR=0, alpha=0.05, name='report', files=None)
.. image:: images/Excel.png
"""
n1 = w.shape[0]
n2 = returns.shape[0]
portfolios = w.columns.tolist()
dates = returns.index.tolist()
year = str(datetime.datetime.now().year)
days = (returns.index[-1] - returns.index[0]).days + 1
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(name + ".xlsx", engine="xlsxwriter")
# Convert the dataframe to an XlsxWriter Excel object.
w.to_excel(writer, sheet_name="Resume", startrow=35, startcol=0)
returns.to_excel(writer, sheet_name="Returns", index_label=["Date"])
# Get the xlsxwriter objects from the dataframe writer object.
workbook = writer.book
worksheet1 = writer.sheets["Resume"]
worksheet2 = writer.sheets["Returns"]
worksheet3 = workbook.add_worksheet("Portfolios")
worksheet4 = workbook.add_worksheet("Absdev")
worksheet5 = workbook.add_worksheet("CumRet")
worksheet6 = workbook.add_worksheet("Drawdown")
worksheet7 = workbook.add_worksheet("devBelowTarget")
worksheet8 = workbook.add_worksheet("devBelowMean")
worksheet1.hide_gridlines(2)
worksheet2.hide_gridlines(2)
worksheet3.hide_gridlines(2)
worksheet4.hide_gridlines(2)
worksheet5.hide_gridlines(2)
worksheet6.hide_gridlines(2)
worksheet7.hide_gridlines(2)
worksheet8.hide_gridlines(2)
# Cell Formats
cell_format1 = workbook.add_format({"bold": True, "border": True})
cell_format2 = workbook.add_format({"bold": True, "font_size": 28, "right": True})
cell_format3 = workbook.add_format({"num_format": "0.0000%"})
cell_format4 = workbook.add_format({"num_format": "0.0000%", "border": True})
cell_format5 = workbook.add_format({"num_format": "yyyy-mm-dd", "bold": True})
cell_format6 = workbook.add_format({"num_format": "0.0000", "border": True})
cell_format7 = workbook.add_format(
{"num_format": "yyyy-mm-dd", "bold": True, "border": True}
)
cell_format8 = workbook.add_format({"num_format": "0,000", "border": True})
cols = xl_col_to_name(1) + ":" + xl_col_to_name(n2)
worksheet1.set_column(cols, 11, cell_format3)
worksheet2.set_column(cols, 9, cell_format3)
worksheet2.write(0, 0, "Date", cell_format1)
worksheet3.write(0, 0, "Date", cell_format1)
worksheet4.write(0, 0, "Date", cell_format1)
worksheet5.write(0, 0, "Date", cell_format1)
worksheet6.write(0, 0, "Date", cell_format1)
worksheet7.write(0, 0, "Date", cell_format1)
worksheet8.write(0, 0, "Date", cell_format1)
worksheet1.set_column("A:A", 35)
worksheet2.set_column("A:A", 10, cell_format5)
worksheet3.set_column("A:A", 10, cell_format5)
worksheet4.set_column("A:A", 10, cell_format5)
worksheet5.set_column("A:A", 10, cell_format5)
worksheet6.set_column("A:A", 10, cell_format5)
worksheet7.set_column("A:A", 10, cell_format5)
worksheet8.set_column("A:A", 10, cell_format5)
for i in range(0, n2):
r = xl_rowcol_to_cell(i + 1, 0)
formula = "=Returns!" + r + ""
worksheet2.write(i + 1, 0, dates[i], cell_format7)
worksheet3.write_formula(i + 1, 0, formula, cell_format7)
worksheet4.write_formula(i + 1, 0, formula, cell_format7)
worksheet5.write_formula(i + 1, 0, formula, cell_format7)
worksheet6.write_formula(i + 1, 0, formula, cell_format7)
worksheet7.write_formula(i + 1, 0, formula, cell_format7)
worksheet8.write_formula(i + 1, 0, formula, cell_format7)
labels_1 = [
"",
"",
"",
"",
"Profitability and Other Inputs",
"Total Days in DataBase",
"Mean Return (1)",
"Compound Annual Growth Rate (CAGR)",
"Minimum Acceptable Return (MAR) (1)",
"Alpha",
"",
"Risk Measures based on Returns",
"Standard Deviation (2)",
"Mean Absolute Deviation (MAD) (2)",
"Semi Standard Deviation (2)",
"First Lower Partial Moment (FLPM) (2)",
"Second Lower Partial Moment (SLPM) (2)",
"Value at Risk (VaR) (2)",
"Conditional Value at Risk (CVaR) (2)",
"Entropic Value at Risk (EVaR) (2)",
"Worst Realization (2)",
"Skewness",
"Kurtosis",
"",
"Risk Measures based on Drawdowns (3)",
"Max Drawdown (MDD)",
"Average Drawdown (ADD)",
"Drawdown at Risk (DaR)",
"Conditional Drawdown at Risk (CDaR)",
"Ulcer Index (ULC)",
]
for i in range(0, len(labels_1)):
if labels_1[i] != "":
worksheet1.write(i, 0, labels_1[i], cell_format1)
for i in range(0, len(portfolios)):
a = "Portfolio " + str(i + 1)
worksheet1.write(3, 1 + i, a, cell_format1)
worksheet1.write(35, 1 + i, a, cell_format1)
worksheet3.write(0, 1 + i, a, cell_format1)
worksheet4.write(0, 1 + i, a, cell_format1)
worksheet5.write(0, 1 + i, a, cell_format1)
worksheet6.write(0, 1 + i, a, cell_format1)
worksheet7.write(0, 1 + i, a, cell_format1)
worksheet8.write(0, 1 + i, a, cell_format1)
for j in range(0, len(portfolios)):
r_0 = xl_rowcol_to_cell(8, 1 + j) # MAR cell
r_1 = xl_range_abs(36, 1 + j, 35 + n1, 1 + j)
r_2 = xl_range_abs(1, 1 + j, n2, 1 + j)
for i in range(0, n2):
r_3 = xl_range(i + 1, 1, i + 1, n1)
r_4 = xl_rowcol_to_cell(i + 1, 1 + j)
r_5 = xl_range_abs(1, 1 + j, i + 1, 1 + j)
formula1 = "{=MMULT(" + "Returns!" + r_3 + ",Resume!" + r_1 + ")}"
formula2 = "=ABS(Portfolios!" + r_4 + "-AVERAGE(Portfolios!" + r_2 + "))"
formula3 = "=SUM(Portfolios!" + r_5 + ")"
formula4 = "=MAX(CumRet!" + r_5 + ")-CumRet!" + r_4
formula5 = (
"=MAX(Resume!"
+ r_0
+ "/ "
+ str(t_factor)
+ "-Portfolios!"
+ r_4
+ ", 0)"
)
formula6 = "=MAX(AVERAGE(Portfolios!" + r_2 + ")-Portfolios!" + r_4 + ", 0)"
worksheet3.write_formula(i + 1, 1 + j, formula1, cell_format3)
worksheet4.write_formula(i + 1, 1 + j, formula2, cell_format3)
worksheet5.write_formula(i + 1, 1 + j, formula3, cell_format3)
worksheet6.write_formula(i + 1, 1 + j, formula4, cell_format3)
worksheet7.write_formula(i + 1, 1 + j, formula5, cell_format3)
worksheet8.write_formula(i + 1, 1 + j, formula6, cell_format3)
r_6 = xl_rowcol_to_cell(9, 1 + j) # Alpha cell
r_7 = xl_rowcol_to_cell(17, 1 + j) # Value at Risk cell
AVG = "=AVERAGE(Portfolios!" + r_2 + ") * " + str(t_factor) + ""
CUM = "{=PRODUCT(1 + Portfolios!" + r_2 + ")^(360/" + str(days) + ")-1}"
STDEV = "=STDEV(Portfolios!" + r_2 + ") * SQRT(" + str(t_factor) + ")"
MAD = "=AVERAGE(Absdev!" + r_2 + ") * SQRT(" + str(t_factor) + ")"
ALPHA = "=" + str(alpha)
VaR = (
"=-SMALL(Portfolios!"
+ r_2
+ ",ROUNDUP(COUNT(Portfolios!"
+ r_2
+ ")*"
+ r_6
+ ",0)) * SQRT("
+ str(t_factor)
+ ")"
)
CVaR = (
"=-((SUMIF(Portfolios!"
+ r_2
+ ',"<="&(-'
+ r_7
+ "/SQRT("
+ str(t_factor)
+ ")),Portfolios!"
+ r_2
+ ")"
)
CVaR += (
"-ROUNDUP(COUNT(Portfolios!"
+ r_2
+ ")*"
+ r_6
+ ",0)*(-"
+ r_7
+ "/SQRT("
+ str(t_factor)
+ ")))/(COUNT(Portfolios!"
+ r_2
+ ")*"
+ r_6
+ ")-"
+ r_7
+ "/SQRT("
+ str(t_factor)
+ ")) * SQRT("
+ str(t_factor)
+ ")"
)
EVaR = (
"="
+ str(rk.EVaR_Hist(returns @ w, alpha=alpha)[0])
+ " * SQRT("
+ str(t_factor)
+ ")"
)
WR = "=-MIN(Portfolios!" + r_2 + ") * SQRT(" + str(t_factor) + ")"
MDD = "=MAX(Drawdown!" + r_2 + ")"
ADD = "=AVERAGE(Drawdown!" + r_2 + ")"
DaR = (
"=+LARGE(Drawdown!"
+ r_2
+ ",ROUNDUP(COUNT(Drawdown!"
+ r_2
+ ")*"
+ r_6
+ ",0))"
)
CDaR = (
"=((SUMIF(Drawdown!" + r_2 + ',">="&' + DaR[2:] + ",Drawdown!" + r_2 + ")"
)
CDaR += (
"-ROUNDUP(COUNT(Drawdown!"
+ r_2
+ ")*"
+ r_6
+ ",0)*"
+ DaR[2:]
+ ")/(COUNT(Drawdown!"
+ r_2
+ ")*"
+ r_6
+ ")+"
+ DaR[2:]
+ ")"
)
ULC = "=SQRT(SUMSQ(Drawdown!" + r_2 + ")/COUNT(Drawdown!" + r_2 + "))"
MAR = "=" + str(rf)
FLPM = "=AVERAGE(devBelowTarget!" + r_2 + ") * SQRT(" + str(t_factor) + ")"
SLPM = (
"=SQRT(SUMSQ(devBelowTarget!"
+ r_2
+ ")/(COUNT(devBelowTarget!"
+ r_2
+ ") - 1))"
+ " * SQRT("
+ str(t_factor)
+ ")"
)
SDEV = (
"=SQRT(SUMSQ(devBelowMean!"
+ r_2
+ ")/(COUNT(devBelowMean!"
+ r_2
+ ") - 1))"
+ " * SQRT("
+ str(t_factor)
+ ")"
)
SKEW = "=SKEW(Portfolios!" + r_2 + ")"
KURT = "=KURT(Portfolios!" + r_2 + ")"
labels_2 = [
"",
"",
"",
"",
"",
str(days),
AVG,
CUM,
MAR,
ALPHA,
"",
"",
STDEV,
MAD,
SDEV,
FLPM,
SLPM,
VaR,
CVaR,
EVaR,
WR,
SKEW,
KURT,
"",
"",
MDD,
ADD,
DaR,
CDaR,
ULC,
]
for i in range(0, len(labels_2)):
if labels_1[i] in ["Skewness", "Kurtosis"]:
worksheet1.write_formula(i, 1 + j, labels_2[i], cell_format6)
elif labels_1[i] in ["Total Days in DataBase"]:
worksheet1.write_formula(i, 1 + j, labels_2[i], cell_format8)
elif labels_2[i] != "":
worksheet1.write_formula(i, 1 + j, labels_2[i], cell_format4)
merge_format = workbook.add_format({"align": "Left", "valign": "vjustify"})
merge_format.set_text_wrap()
worksheet1.set_row(1, 215)
worksheet1.merge_range("A2:K2", __LICENSE__.replace("2021", year), merge_format)
worksheet1.write(30, 0, "(1) Annualized, multiplied by " + str(t_factor))
worksheet1.write(31, 0, "(2) Annualized, multiplied by √" + str(t_factor))
worksheet1.write(32, 0, "(3) Based on uncompounded cumulated returns")
worksheet1.write(0, 0, "Riskfolio-Lib Report", cell_format2)
writer.save()
workbook.close()