Combine two charts in one

[popoff_1]

Hello, I have an r-code which is calculating the S&P500 Total return index. So now I want to caluclate, in the same chart, the maximum drawdown with chart.Drawdown, see the code below. But i did not get it done to solve the problem. The following problem occurs:

*Error in checkData(R) : *

• The data cannot be converted into a time series. If you are trying to pass in names from a data object with one column, you should use the form 'data[rows, columns, drop = FALSE]'. Rownames should have standard date formats, such as '1985-03-15'.*

Maybe anyone can help me to solve the problem. At least the picture should look like the picture attached, here I have calculated the drawdown in excel.

---
output:
  pdf_document: default
  html_document: default
---
library(tidyverse) # for overall grammar
library(lubridate) # to parse dates
library(tidyquant) # to download data from yahoo finance
library(glue)      # to automatically construct figure captions
library(scales)    # for nicer axis labels 
library(readxl)    # to read Shiller's data 
library(rio)

tbl.SP500Recent <-  tq_get("^SP500TR", get = "stock.prices",
                           from = "1988-01-04", to = "2022-05-31") %>%
  transmute(Date = date, TotalReturnIndex = close) %>%
  na.omit() %>%
  group_by(Month = ceiling_date(Date, "month")-1) %>%
  arrange(Date) %>%
  filter(Date == max(Date)) %>%
  ungroup() %>%
  select(Month, TotalReturnIndex)
  
  
  temp <- tempfile(fileext = ".xls")

download.file(url = "http://www.econ.yale.edu/~shiller/data/ie_data.xls",
              destfile = temp, mode='wb')

tbl.ShillerHistorical <- read_excel(temp, sheet = "Data", skip = 7) %>%
  transmute(Month = ceiling_date(ymd(str_replace(str_c(Date, ".01"), "\\.1\\.", "\\.10\\.")), "month")-1,
            Price = as.numeric(P),
            Dividend = as.numeric(D)) 
            
tbl.ShillerHistorical <- tbl.ShillerHistorical %>%
  arrange(Month) %>%
  mutate(Ret = (Price + Dividend / 12) / lag(Price) - 1)
  
  tbl.Check <- tbl.ShillerHistorical %>%
  full_join(tbl.SP500Recent, by = "Month") %>%
  filter(!is.na(TotalReturnIndex)) %>%
  arrange(Month) %>%
  mutate(Ret = if_else(row_number() == 1, 0, Ret), # ignore first month return
         TotalReturnCheck = TotalReturnIndex[1] * cumprod(1 + Ret)) %>%
  na.omit()
  
  fig.Check <- tbl.Check %>%
  select(Month, Actual = TotalReturnIndex, Simulated = TotalReturnCheck) %>%
  pivot_longer(cols = -Month, names_to = "Type", values_to = "Value") %>%
  ggplot(aes(x = Month, y = Value, color = Type)) +
  geom_line() +
  theme_bw() +
  scale_y_continuous(labels = comma)+ 
  labs(x = NULL, y = NULL,
       title = "Actual and Simulated S&P 500 Total Return Index",
       subtitle = glue("Both Indexes start at {min(tbl.Check$Month)}"))
fig.Check

tbl.SP500Historical <- tbl.SP500Recent %>% 
  filter(Month == min(Month)) %>%
  full_join(tbl.ShillerHistorical %>%
              filter(Month <= min(tbl.SP500Recent$Month)), by = "Month") %>%
  arrange(desc(Month)) %>%
  mutate(Ret = if_else(row_number() == 1, 0, Ret), # ignore first month return
         TotalReturnIndex = TotalReturnIndex[1] / cumprod(1 + Ret))
         
         
  tbl.SP500Index <- tq_get("^GSPC", get = "stock.prices",
                         from = "1871-02-28", to = "2021-12-31") %>%
  transmute(Date = date, Index = close) %>%
  na.omit() %>%
  group_by(Month = ceiling_date(Date, "month") - 1) %>%
  arrange(Date) %>%
  filter(Date == max(Date)) %>%
  ungroup() %>%
  select(Month, Index)
  
  
  tbl.SP500Monthly <- tbl.SP500Recent%>%
  bind_rows(tbl.SP500Historical %>%
              filter(Month < min(tbl.SP500Recent$Month))  %>%
              select(Month, TotalReturnIndex)) %>%
  full_join(tbl.SP500Index %>% 
              select(Month, Index), by = "Month") %>%
  filter(Month >= "1871-02-28")  %>%
  arrange(Month)
tbl.SP500Monthly

fig.Historical <- tbl.SP500Monthly %>%
  select(Month, Index, `Total Return` = TotalReturnIndex) %>%
  pivot_longer(cols = -Month, names_to = "Type", values_to = "Value") %>%
  group_by(Type) %>%
  arrange(Month) %>%
  mutate(Value = Value / Value[1] * 100) %>%
  ggplot(aes(x = Month, y = Value, color = Type)) +
  geom_line() +
  theme_bw() +
  scale_y_log10(labels = comma) +
  scale_x_date(expand = c(0, 0), date_breaks = "10 years", date_labels = "%Y") + 
  labs(x = NULL, y = NULL,
       title = "S&P 500 Index and Total Return Index Since 1871",
       subtitle = glue("Both Indexes are Normalized to 100 at {min(tbl.SP500Monthly$Month)}"))
fig.Historical




chart.Drawdown(tbl.SP500Monthly, plot.engine = "ggplot2") +
  geom_hline(yintercept = -0.55, size = 1) +
  theme_minimal() +
  theme(legend.position = "none", 
        axis.text.x = element_text(angle = 45, hjust = 1),
        plot.caption = element_text(hjust = 0, lineheight = 0.5)) +
            scale_y_continuous(labels = scales::percent, limits=c(-1,0))+
ggtitle(paste0("Drawdowns of ",tbl.SP500Monthly,", monthly data"),
          subtitle = "Black line indicates bear markets")
       

export(tbl.SP500Monthly, "mxfile.xlsx")