Purpose

In this bonus practical, I want to show you how to create a table of statistics for financial ratios.

It requires you to go from tidy to xts and back to tidy.

Let’s do so on a Monthly frequency, with variable look back periods specified as a parameter.

This function should thus be generic ito look-back period, allowing us to combine several periods.

Also, as we will be considering tracking error - the Benchmark index should be stated explicitly.

Safe TE

In order to be sure that the tracking error you use is safe (same as with the portfolio.returns function from PerformanceAnalytics):

Run this example quick and check what is wrong here:

# Problem with PerformanceAnalytics - if the dates aren't
# exactly aligned, the calculation is wrong and it won't
# alert you to it...  An example of this is:

library(tidyverse)
library(fmxdat)
library(lubridate)
library(xts)
library(tbl2xts)
# Load capped swix as bm
alsi <- fmxdat::Jalshtr %>%
    mutate(YM = format(date, "%Y%B")) %>%
    group_by(YM) %>%
    filter(date == last(date)) %>%
    ungroup() %>%
    mutate(Returns = TRI/lag(TRI) - 1) %>%
    filter(!is.na(Returns)) %>%
    filter(date > ymd(20100131))
funds <- fmxdat::asisa %>%
    filter(Funds %in% c("Fund_1", "Fund_10")) %>%
    mutate(YM = format(date, "%Y%B")) %>%
    filter(date > ymd(20100131))
Endate <- ymd(20210430)

# For the past three years, the exact dates don't align:
# E.g. in Ra we have 2018-06-30, but Rb has 2018-06-29.

# This can happen surprisingly easily - where e.g. your
# benchmark uses end of month, and your data series uses
# weekday end of months...  index(Ra)[!index(Ra) %in%
# index(Rb)] index(Rb)[!index(Rb) %in% index(Ra)]

Ra <- funds %>%
    filter(date <= Endate) %>%
    filter(date >= fmxdat::safe_month_min(last(date), N = 36)) %>%
    tbl2xts::tbl_xts(cols_to_xts = Returns, spread_by = Funds)
Rb <- alsi %>%
    filter(date <= Endate) %>%
    filter(date >= RA::safe_month_min(last(date), N = 36)) %>%
    tbl2xts::tbl_xts(cols_to_xts = Returns)
# Now PerformanceAnalytics::TrackingError simply drops the
# dates that aren't exactly aligned, causing a misspecified
# TE calc based on 25, not 36 values. Note the enormous
# difference below:

PerformanceAnalytics::TrackingError(Ra, Rb, scale = 12)

##                            Fund_1   Fund_10
## Tracking Error: Returns 0.1900745 0.2069661

fmxdat::Safe_TE(Ra, Rb, scale = 12)

##                        Fund_1    Fund_10
## Tracking Error: BM 0.06757044 0.03957616

Lesson learnt - please be careful with PerformanceAnalytics’s portfolio returns and TE functions. Instead use rmsfuns::Safe_Return.portfolio & fmxdat::Safe_TE.

Application

Add a logical check that if more than X% is NA - you exclude that index.

pacman::p_load(tidyverse)
pacman::p_load(tbl2xts, gt, lubridate)

funds <- fmxdat::SA_Indexes %>%
    select(date, Tickers, Price) %>%
    arrange(date) %>%
    group_by(Tickers) %>%
    mutate(Ret = Price/lag(Price) - 1) %>%
    ungroup()

# Envisaged parameters:

BM <- "JSHRALTR Index"
Yrs_LookBack <- 5
NA_Check <- 0.8  # atleast 80% of obs not-NA.

Let’s create this general purpose stats table:

Moments_Comp <- function(funds, BM, Yrs_LookBack, NA_Check){
  
  funds_considered <- 
    funds %>% filter(date >= fmxdat::safe_year_min(datesel = last(date), N = Yrs_LookBack))
  
  Funds_Cons <- 
    funds_considered %>% 
    group_by(Tickers) %>% 
    summarise(N_noNA = sum(!is.na(Ret)) / length(unique(funds_considered$date)) ) %>% 
    filter(N_noNA > NA_Check) %>% pull(Tickers)
  
    Fundxts <- 
      funds_considered %>% filter(Tickers %in% Funds_Cons) %>% 
      tbl_xts(cols_to_xts = Ret, spread_by = Tickers, Colnames_Exact = T)
  
    BMxts <- 
      funds_considered %>% filter(Tickers %in% BM) %>% 
      tbl_xts(cols_to_xts = Ret, Colnames_Exact = T) 

  library(PerformanceAnalytics)
    
  Moms <- 
      bind_rows(
        data.frame(Return.cumulative(Fundxts) ) %>% round(., 3),
        data.frame(Return.annualized(Fundxts, scale = 12, geometric = T)) %>% round(., 3),
        data.frame(PerformanceAnalytics::Return.annualized.excess(Fundxts, BMxts) ) %>% round(., 3),
        data.frame(sd.annualized(Fundxts, scale = 12, geometric = T)) %>% round(., 3),
        
        data.frame(PerformanceAnalytics::AdjustedSharpeRatio( Fundxts ) ) %>% round(., 3),
        data.frame(PainIndex(Fundxts, scale = 12, geometric = T)) %>% round(., 3),
        data.frame(AverageDrawdown(Fundxts, scale = 12)) %>% round(., 3),
        
        data.frame(fmxdat::Safe_TE(Ra = Fundxts, Rb = BMxts, scale = 12)) %>% round(., 3),
        data.frame(PerformanceAnalytics::InformationRatio(Ra = Fundxts, Rb = BMxts)) %>% round(., 3),
        data.frame(PerformanceAnalytics::CAPM.beta(Ra = Fundxts, Rb = BMxts, Rf = 0)) %>% round(., 3),
        data.frame(PerformanceAnalytics::CAPM.beta.bull(Ra = Fundxts, Rb = BMxts, Rf = 0)) %>% round(., 3),
        data.frame(PerformanceAnalytics::CAPM.beta.bear(Ra = Fundxts, Rb = BMxts, Rf = 0)) %>% round(., 3),
        data.frame(PerformanceAnalytics::UpDownRatios(Ra = Fundxts, Rb = BMxts, method = "Percent", side = "Up")) %>% round(., 3),
        data.frame(PerformanceAnalytics::CVaR(R = Fundxts, p = 0.05, method = "modified")) %>% round(., 3)
        ) %>% 
        
        tibble::rownames_to_column("Info") %>%
        mutate(Period = glue::glue("Last {Yrs_LookBack} Years"), Info = c("Cum Returns", "Returns (Ann.)", "Returns Excess (Ann.)", "SD (Ann.)", "Adj. Sharpe Ratio", "Pain Index", 
                                                 "Avg DD", "Tracking Error", "Information Ratio", "Beta", "Beta Bull", "Beta Bear", "Up-Down Ratio", "Modified CVaR")) %>% 
        relocate(Period, .before = Info) %>% as_tibble() 
  
  # This line replaces the `.` with a space.
  # Note the forward slashes, as `.` there means everything, `\\.` means a full-stop
  colnames(Moms) <- gsub("\\.", " ", colnames(Moms))
  
  Moms
}

Fundselected <- c("JSHRALTR Index", "JALSHTR Index",  "TOP40TR Index",  "JSAPYTR Index")

Tab_stats <- 

  bind_rows(
  
    Moments_Comp(funds %>% filter(Tickers %in% Fundselected), BM, Yrs_LookBack = 3, NA_Check),
  
    Moments_Comp(funds %>% filter(Tickers %in% Fundselected), BM, Yrs_LookBack = 5, NA_Check)

    )
  

Make_perc <- 
  
  c( "Cum Returns", "Returns (Ann.)", "Returns Excess (Ann.)", "SD (Ann.)",
     "Avg DD", "Tracking Error")

Rows_to_Perc <- 
  
  Tab_stats %>% mutate(RN=row_number()) %>% filter(Info %in% Make_perc) %>% pull(RN)

colnams <- colnames(Tab_stats)[-1:-2]

Cols_length <- ncol(Tab_stats)

library(gt)

tab <- 
  
  Tab_stats %>% 
  
  gt::gt(groupname_col = 'Period', caption = 'Fund Moments Comparison') %>% 
      tab_header(title = glue::glue("Index Statistics: Relative to {BM}")) %>% 
      fmt_percent(
      columns = 3:Cols_length,
      rows = Rows_to_Perc,
      decimals = 1
    ) %>%   
  
  sub_missing(
    columns = all_of(colnams),
    missing_text = '-'
  ) %>%
  
   tab_footnote( footnote = 'JSAPY is the local property index, consisting of REITS companies.',
                locations = cells_column_labels(columns = contains('JSAPYTR'))) %>%
  
   tab_footnote(footnote = 'Beta of fund in bull-market periods',
                locations = cells_column_labels(columns = contains('JSAPYTR'))) %>%
  
      tab_style(
        style = list(
            cell_fill(color = 'gray27', alpha = 0.15),
            cell_text(size = 'large', weight = 'bold',align = 'left')

        ),
        locations = cells_row_groups()) 

tab %>% 
  
  tab_options(data_row.padding = px(4),table.width = pct(100),
            column_labels.font.size = pct(50),
            column_labels.vlines.width = 1, table.font.size = pct(80)) %>%
                tab_options(data_row.padding = px(6),
                column_labels.font.size = pct(100)) %>% 
  tab_style(style = cell_text(weight = 1200, align = 'left'),locations = cells_title(groups = 'title')) %>%
    tab_style(style = cell_text(color = 'darkgrey', transform = 'uppercase', align = 'center'),
              locations = cells_column_labels(everything()))

Fund Moments Comparison
Info	JALSHTR Index	JSAPYTR Index^1,2	JSHRALTR Index	TOP40TR Index
Index Statistics: Relative to JSHRALTR Index
Last 3 Years
Cum Returns	11.3%	−49.1%	2.2%	15.5%
Returns (Ann.)	0.2%	−1.1%	0.0%	0.2%
Returns Excess (Ann.)	2.9%	−20.9%	0.0%	4.2%
SD (Ann.)	4.5%	6.3%	4.6%	4.7%
Adj. Sharpe Ratio	0.171	-0.426	0.034	0.221
Pain Index	0.058	0.256	0.082	0.060
Avg DD	6.7%	8.7%	6.6%	5.2%
Tracking Error	0.8%	4.7%	0.0%	1.0%
Information Ratio	0.776	-0.979	-	0.959
Beta	0.952	0.917	1.000	0.990
Beta Bull	0.971	1.057	1.000	1.008
Beta Bear	0.965	1.181	1.000	0.986
Up-Down Ratio	0.406	0.301	0.000	0.512
Modified CVaR	-0.050	-0.037	-0.052	-0.049
Last 5 Years
Cum Returns	25.1%	−42.7%	13.6%	28.4%
Returns (Ann.)	0.2%	−0.5%	0.1%	0.2%
Returns Excess (Ann.)	2.0%	−12.9%	0.0%	2.5%
SD (Ann.)	4.1%	5.4%	4.3%	4.3%
Adj. Sharpe Ratio	0.232	-0.370	0.131	0.248
Pain Index	0.051	0.169	0.066	0.056
Avg DD	5.5%	5.4%	4.9%	5.3%
Tracking Error	0.9%	4.2%	0.0%	1.0%
Information Ratio	0.516	-0.696	-	0.533
Beta	0.945	0.834	1.000	0.987
Beta Bull	0.963	0.911	1.000	1.009
Beta Bear	0.958	1.108	1.000	0.984
Up-Down Ratio	0.409	0.313	0.000	0.486
Modified CVaR	-0.043	-0.022	-0.045	-0.042
¹ JSAPY is the local property index, consisting of REITS companies.
² Beta of fund in bull-market periods

# To save this, use:
# gt::gtsave(tab, file = glue::glue('Results/TempTab.png'))

# In your rmd, you can now reference it using:
# ![]('Results/TempTab.png'))

And there you go…

Some nice statistics on the indicated indexes.

Financial Econometrics Practical

Practical 1: Bonus - Creating a Financial Statistics Table

NF Katzke

Purpose

Safe TE

Application

Let’s create this general purpose stats table: