Calculating the return, standard deviation, and SR for the SPX

SR_calc.R

library(tidyverse)
library(tidyquant)
library(lubridate)

# get data from Yahoo ----
spx <- tq_get("^GSPC")

# calculate returns ----
ret_daily <- spx %>% 
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted))) %>% 
  filter(date <= as.Date("2020-08-31"))

ret_weekly_end <- spx %>% 
  group_by(d = ceiling_date(date, "weeks")) %>% 
  filter(date == max(date)) %>% 
  ungroup() %>% 
  select(-d) %>% 
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted)))

ret_monthly_end <- spx %>% 
  group_by(d = ceiling_date(date, "months")) %>% 
  filter(date == max(date)) %>% 
  ungroup() %>% 
  select(-d) %>%
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted)))

ret_quarterly_end <- spx %>% 
  group_by(d = ceiling_date(date, "quarter")) %>% 
  filter(date == max(date)) %>% 
  ungroup() %>% 
  select(-d) %>%
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted)))

ret_yearly_end <- spx %>% 
  group_by(d = ceiling_date(date, "years")) %>% 
  filter(date == max(date)) %>% 
  ungroup() %>% 
  select(-d) %>%
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted)))

ret_weekly_beg <- spx %>% 
  group_by(d = ceiling_date(date, "weeks")) %>% 
  filter(date == min(date)) %>% 
  ungroup() %>% 
  select(-d) %>% 
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted)))

ret_monthly_beg <- spx %>% 
  group_by(d = ceiling_date(date, "months")) %>% 
  filter(date == min(date)) %>% 
  ungroup() %>% 
  select(-d) %>%
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted)))

ret_quarterly_beg <- spx %>% 
  group_by(d = ceiling_date(date, "quarter")) %>% 
  filter(date == min(date)) %>% 
  ungroup() %>% 
  select(-d) %>%
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted)))

ret_yearly_beg <- spx %>% 
  group_by(d = ceiling_date(date, "years")) %>% 
  filter(date == min(date)) %>% 
  ungroup() %>% 
  select(-d) %>%
  mutate(ret = adjusted/lag(adjusted)-1,
         log_ret = log(adjusted/lag(adjusted)))

out <- tibble()
for (yr in c(3,5,10)) {
  # calculate annualized mean return, volatility, SR ----
  daily <- ret_daily  %>% 
    filter(date >= Sys.Date()- years(yr)) %>% 
    summarise(mu = (1+(mean.geometric(1+ret)-1))^252-1,
              sigma = sd(ret, na.rm = T)*sqrt(252),
              log_mu = mean(log_ret, na.rm = T)*252,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(252),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  weekly_end <- ret_weekly_end %>% 
    filter(date >= Sys.Date()- years(yr)) %>% 
    summarise(mu = (1+(mean.geometric(1+ret)-1))^52-1,
              sigma = sd(ret, na.rm = T)*sqrt(52),
              log_mu = mean(log_ret, na.rm = T)*52,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(52),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  monthly_end <- ret_monthly_end %>% 
    filter(date >= Sys.Date()- years(yr)) %>% 
    summarise(mu = (1+(mean.geometric(1+ret)-1))^12-1,
              sigma = sd(ret, na.rm = T)*sqrt(12),
              log_mu = mean(log_ret, na.rm = T)*12,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(12),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  monthly <- ret_monthly %>% 
    filter(date >= Sys.Date()- years(yr)) %>%
    summarise(mu = (1+(mean.geometric(1+ret)-1))^12-1,
              sigma = sd(ret, na.rm = T)*sqrt(12),
              log_mu = mean(log_ret, na.rm = T)*12,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(12),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  quarterly_end <- ret_quarterly_end %>% 
    filter(date >= Sys.Date()- years(yr)) %>%
    summarise(mu = (1+(mean.geometric(1+ret)-1))^4-1,
              sigma = sd(ret, na.rm = T)*sqrt(4),
              log_mu = mean(log_ret, na.rm = T)*4,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(4),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  yearly_end <- ret_yearly_end %>% 
    filter(date >= Sys.Date()- years(yr)) %>%
    summarise(mu = mean.geometric(1+ret)-1,
              sigma = sd(ret, na.rm = T),
              log_mu = mean(log_ret, na.rm = T),
              log_sigma = sd(log_ret, na.rm = T),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  weekly_beg <- ret_weekly_beg %>% 
    filter(date >= Sys.Date()- years(yr)) %>%
    summarise(mu = (1+(mean.geometric(1+ret)-1))^52-1,
              sigma = sd(ret, na.rm = T)*sqrt(52),
              log_mu = mean(log_ret, na.rm = T)*52,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(52),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  monthly_beg <- ret_monthly_beg %>% 
    filter(date >= Sys.Date()- years(yr)) %>%
    summarise(mu = (1+(mean.geometric(1+ret)-1))^12-1,
              sigma = sd(ret, na.rm = T)*sqrt(12),
              log_mu = mean(log_ret, na.rm = T)*12,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(12),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  monthly <- ret_monthly %>% 
    filter(date >= Sys.Date()- years(yr)) %>%
    summarise(mu = (1+(mean.geometric(1+ret)-1))^12-1,
              sigma = sd(ret, na.rm = T)*sqrt(12),
              log_mu = mean(log_ret, na.rm = T)*12,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(12),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  quarterly_beg <- ret_quarterly_beg %>% 
    filter(date >= Sys.Date()- years(yr)) %>%
    summarise(mu = (1+(mean.geometric(1+ret)-1))^4-1,
              sigma = sd(ret, na.rm = T)*sqrt(4),
              log_mu = mean(log_ret, na.rm = T)*4,
              log_sigma = sd(log_ret, na.rm = T)*sqrt(4),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  yearly_beg <- ret_yearly_beg %>% 
    filter(date >= Sys.Date()- years(yr)) %>%
    summarise(mu = mean.geometric(1+ret)-1,
              sigma = sd(ret, na.rm = T),
              log_mu = mean(log_ret, na.rm = T),
              log_sigma = sd(log_ret, na.rm = T),
              SR = mu/sigma,
              log_SR = log_mu/log_sigma)
  
  # create overview table ----
  o <- bind_rows(daily %>% mutate(freq = "daily"), 
                 weekly_end %>% mutate(freq = "weekly end"),
                 monthly_end %>% mutate(freq = "monthy end"), 
                 quarterly_end %>% mutate(freq = "quarterly end"),
                 yearly_end %>% mutate(freq = "yearly end"),
                 weekly_beg %>% mutate(freq = "weekly beg"),
                 monthly_beg %>% mutate(freq = "monthy beg"), 
                 quarterly_beg %>% mutate(freq = "quarterly beg"),
                 yearly_beg %>% mutate(freq = "yearly beg"))
  
  out <- bind_rows(out, o %>% mutate(risk = yr))
}



simple <- bind_rows(out) %>% 
  select(mu, sigma, SR, freq, risk) %>% mutate(calc = "simple")

logari <- bind_rows(out) %>% 
  select(mu = log_mu, sigma = log_sigma, SR = log_SR, freq, risk) %>% mutate(calc = "log")

bind_rows(simple, logari) %>% 
  # figures we get from https://www.morningstar.com/indexes/spi/spx/risk
  mutate(mu_ms = case_when(risk == 10 ~ 0.1115,
                           risk == 5 ~ 0.108,
                           risk == 3 ~ 0.0896),
         sigma_ms = case_when(risk == 10 ~ 0.1338,
                              risk == 5 ~ 0.148,
                              risk == 3 ~ 0.1751),
         SR_ms = case_when(risk == 10 ~ 0.92,
                           risk == 5 ~ 0.77,
                           risk == 3 ~ 0.66)) %>% 
  # differences
  mutate(delta_mu = mu_ms - mu,
         delta_sigma = sigma_ms - sigma,
         delta_SR = SR_ms - SR)