def plot_table( returns, w, MAR=0, alpha=0.05, height=9, width=12, t_factor=252, ini_days=1, 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} ini_days : float, optional If provided, it is the number of days of compounding for first return. It is used to calculate Compound Annual Growth Rate (CAGR). This value depend on assumptions used in t_factor, for example if data is monthly you can use 21 (252 days per year) or 30 (360 days per year). The default is 1 for daily returns. 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 + ini_days 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)", "Ulcer Index (UCI)", "Average Drawdown (ADD)", "Drawdown at Risk (DaR)", "Conditional Drawdown at Risk (CDaR)", "Entropic Drawdown at Risk (EDaR)", "Max Drawdown (MDD)", "(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.UCI_Abs(X), rk.ADD_Abs(X), rk.DaR_Abs(X), rk.CDaR_Abs(X, alpha=alpha), rk.EDaR_Abs(X, alpha=alpha)[0], rk.MDD_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_drawdown(nav, w, alpha=0.05, height=8, width=10, ax=None): r""" Create a chart with the evolution of portfolio prices and drawdown. Parameters ---------- nav : DataFrame Cumulative assets returns. w : DataFrame, optional A portfolio specified by the user to compare with the efficient frontier. The default is None. alpha : float, optional Significante level of DaR and CDaR. The default is 0.05. height : float, optional Height of the image in inches. The default is 8. width : float, optional Width of the image in inches. The default is 10. ax : matplotlib axis of size (2,1), 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 ------- :: nav=port.nav ax = plf.plot_drawdown(nav=nav, w=w1, alpha=0.05, height=8, width=10, ax=None) .. image:: images/Drawdown.png """ if not isinstance(nav, pd.DataFrame): raise ValueError("nav 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 nav.shape[1] != w.shape[0]: a1 = str(nav.shape) a2 = str(w.shape) raise ValueError("shapes " + a1 + " and " + a2 + " not aligned") if ax is None: fig = plt.gcf() ax = fig.subplots(nrows=2, ncols=1) ax = ax.flatten() fig.set_figwidth(width) fig.set_figheight(height) index = nav.index.tolist() a = np.array(nav, ndmin=2) a = np.insert(a, 0, 0, axis=0) a = np.diff(a, axis=0) a = np.array(a, ndmin=2) @ np.array(w, ndmin=2) prices = 1 + np.insert(a, 0, 0, axis=0) prices = np.cumprod(prices, axis=0) prices = np.ravel(prices).tolist() prices2 = 1 + np.array(np.cumsum(a, axis=0)) prices2 = np.ravel(prices2).tolist() del prices[0] DD = [] peak = -99999 for i in range(0, len(prices)): if prices2[i] > peak: peak = prices2[i] DD.append((peak - prices2[i])) DD = -np.array(DD) titles = [ "Historical Compounded Cumulative Returns", "Historical Uncompounded Drawdown", ] data = [prices, DD] color1 = ["b", "orange"] risk = [ -rk.UCI_Abs(a), -rk.ADD_Abs(a), -rk.DaR_Abs(a, alpha), -rk.CDaR_Abs(a, alpha), -rk.EDaR_Abs(a, alpha)[0], -rk.MDD_Abs(a), ] label = [ "Ulcer Index: " + "{0:.2%}".format(risk[0]), "Average Drawdown: " + "{0:.2%}".format(risk[1]), "{0:.2%}".format((1 - alpha)) + " Confidence DaR: " + "{0:.2%}".format(risk[2]), "{0:.2%}".format((1 - alpha)) + " Confidence CDaR: " + "{0:.2%}".format(risk[3]), "{0:.2%}".format((1 - alpha)) + " Confidence EDaR: " + "{0:.2%}".format(risk[4]), "Maximum Drawdown: " + "{0:.2%}".format(risk[5]), ] color2 = ["b", "r", "fuchsia", "limegreen", "dodgerblue", "darkgrey"] j = 0 ymin = np.min(DD) * 1.5 for i in ax: i.clear() i.plot_date(index, data[j], "-", color=color1[j]) if j == 1: i.fill_between(index, 0, data[j], facecolor=color1[j], alpha=0.3) for k in range(0, len(risk)): i.axhline(y=risk[k], color=color2[k], linestyle="-", label=label[k]) i.set_ylim(ymin, 0) i.legend(loc="lower right") # , fontsize = 'x-small') i.set_title(titles[j]) i.xaxis.set_major_locator( mdates.AutoDateLocator(tz=None, minticks=5, maxticks=10) ) i.xaxis.set_major_formatter(mdates.DateFormatter("%Y-%m")) ticks_loc = i.get_yticks().tolist() i.set_yticks(i.get_yticks()) i.set_yticklabels(["{:3.2%}".format(x) for x in ticks_loc]) i.grid(linestyle=":") j = j + 1 fig = plt.gcf() fig.tight_layout() return ax
def excel_report(returns, w, rf=0, alpha=0.05, t_factor=252, ini_days=1, 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} ini_days : float, optional If provided, it is the number of days of compounding for first return. It is used to calculate Compound Annual Growth Rate (CAGR). This value depend on assumptions used in t_factor, for example if data is monthly you can use 21 (252 days per year) or 30 (360 days per year). The default is 1 for daily returns. 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 + ini_days # 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=36, 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", 36) 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)", "Ulcer Index (UCI)", "Average Drawdown (ADD)", "Drawdown at Risk (DaR)", "Conditional Drawdown at Risk (CDaR)", "Entropic Drawdown at Risk (CDaR)", "Max Drawdown (MDD)", ] 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(36, 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(37, 1 + j, 36 + 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:] + ")") EDaR = "=" + str(rk.EDaR_Abs(returns @ w, alpha=alpha)[0]) UCI = "=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, "", "", UCI, ADD, DaR, CDaR, EDaR, MDD, ] 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(2, 0, "https://github.com/dcajasn/Riskfolio-Lib") worksheet1.write(31, 0, "(1) Annualized, multiplied by " + str(t_factor)) worksheet1.write(32, 0, "(2) Annualized, multiplied by √" + str(t_factor)) worksheet1.write(33, 0, "(3) Based on uncompounded cumulated returns") worksheet1.write(0, 0, "Riskfolio-Lib Report", cell_format2) writer.save() workbook.close()