Plotting candlesticks with intraday data and adding moving averages, chartSeries or geom_candlestick

Question

I am trying to plot candlesticks in R overlaid with moving averages, from my downloaded data for 30' SPY. I eventually want to plot one candlestick chart per day, with 14 day moving average overlaid, using a for-loop. Currently, I am not able to even plot candlesticks for the whole data. (My version of RStudio is 2022.12.0 Build 353, Mac Darwin20, version of R is 4.2.2).

The last 10 lines of the data frame (spy30_reordered) look like this.:

structure(list(timestamp = structure(c(1643725800, 1643727600, 
1643729400, 1643731200, 1643733000, 1643734800, 1643736600, 1643738400, 
1643740200, 1643742000), tzone = "UTC", class = c("POSIXct", 
"POSIXt")), open = c(450.69, 449.75, 448.785, 449.95, 449.89, 
450.76, 450.09, 450.45, 450.34, 450.47), high = c(451, 450.03, 
450.05, 450.91, 451.08, 450.97, 450.54, 450.55, 450.725, 450.88
), low = c(448.585, 446.885, 447.86, 449.4, 448.95, 449.52, 448.975, 
449.505, 449.575, 449.485), close = c(449.76, 448.88, 449.99, 
449.975, 450.635, 450.03, 450.41, 450.335, 450.395, 450.215), 
    ticker = c("SPY", "SPY", "SPY", "SPY", "SPY", "SPY", "SPY", 
    "SPY", "SPY", "SPY"), date = structure(c(19024, 19024, 19024, 
    19024, 19024, 19024, 19024, 19024, 19024, 19024), class = "Date"), 
    time = structure(c(52200, 54000, 55800, 57600, 59400, 61200, 
    63000, 64800, 66600, 68400), class = c("hms", "difftime"), units = "secs"), 
    dma14 = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_)), row.names = c(NA, 
-10L), class = c("tbl_df", "tbl", "data.frame"))

I first tried chartSeries from quantmod package:

chartSeries(spy30_reordered, type = "candlesticks", theme = 'white')

#This gave the error, "Error in try.xts(x, error = "chartSeries requires an xtsible object") : chartSeries requires an xtsible object". My understanding was that the first column needs to be a POSIXct object, which my data has. If I try to change my data frame to its object as follows:

spy30_reordered_xts <- xts(spy30_reordered, order.by=spy30_reordered[,1])

#I get the error, "Error in xts(spy30_reordered, order.by = spy30_reordered[, 1]) : order.by requires an appropriate time-based object". Is my first column, a POSIXct object, not a time based object?

I also tried ggplot as follows:

ggplot(spy30_reordered, aes(x = timestamp, open = open, high = high, low = low, close = close)) + geom_candlestick()

#This gives a plot, but the plot shows only vertical lines and not candlesticks.

I am not able to tell what I am doing wrong. Thanks for any help.

Answer 1

My understanding was that the first column needs to be a POSIXct object, which my data has.

xts/zoo objects are a matrix with an index attribute. The index is not a column, because a matrix can only hold one type of data.

In the current version of xts the as.xts() function will look for a datetime column in the input and use it as the index. You should only use the first 5 columns, otherwise your xts object will be character (because xts is a matrix, not a data.frame).

spy30_reordered_xts <- as.xts(spy30_reordered[, 1:5])

The code below errors because spy30_reordered[,1] is a tibble, not a POSIXct object. And all the columns of you your spy30_reordered object will be converted to character because xts/zoo objects can only contain one type of data.

spy30_reordered_xts <- xts(spy30_reordered, spy30_reordered[,1])

You need to do this instead:

spy30_reordered_xts <- xts(spy30_reordered[, 2:5], spy30_reordered[[1]])
chartSeries(spy30_reordered_xts)
# or
chart_Series(spy30_reordered_xts)

Then you can add series to the plots using addTA() or add_TA(), respectively.

Answer 2

Have a look at the tidyquant library.

Note that the library has excellent documentation, for example here.

For example use this:

library(tidyquant)
library(ggplot2)
library(dplyr)

spy <- tq_get("SPY", from = "2020-01-01", to = "2022-12-31")

spy
#> # A tibble: 756 × 8
#>    symbol date        open  high   low close   volume adjusted
#>    <chr>  <date>     <dbl> <dbl> <dbl> <dbl>    <dbl>    <dbl>
#>  1 SPY    2020-01-02  324.  325.  323.  325. 59151200     310.
#>  2 SPY    2020-01-03  321.  324.  321.  322. 77709700     307.
#>  3 SPY    2020-01-06  320.  324.  320.  324. 55653900     309.
#>  4 SPY    2020-01-07  323.  324.  322.  323. 40496400     308.
#>  5 SPY    2020-01-08  323.  326.  323.  324. 68296000     309.
#>  6 SPY    2020-01-09  326.  327.  326.  327. 48473300     311.
#>  7 SPY    2020-01-10  327.  327.  325.  326. 53029300     310.
#>  8 SPY    2020-01-13  326.  328.  326.  328. 47086800     313.
#>  9 SPY    2020-01-14  327.  329.  327.  327. 62832800     312.
#> 10 SPY    2020-01-15  327.  329.  327.  328. 72056600     313.
#> # … with 746 more rows

spy %>%
  ggplot(aes(x = date, y = close)) +
  geom_candlestick(aes(open = open, high = high, low = low, close = close)) +
  theme_tq()

# repeat with the first 20 days
spy %>%
  head(20) |> 
  ggplot(aes(x = date, y = close)) +
  geom_candlestick(aes(open = open, high = high, low = low, close = close)) +
  theme_tq()

# add moving averages
spy %>%
  ggplot(aes(x = date, y = close)) +
  geom_candlestick(aes(open = open, high = high, low = low, close = close)) +
  geom_ma(ma_fun = SMA, n = 50) +
  theme_tq()

^{Created on 2023-02-01 by the reprex package (v2.0.1)}

Answer 3

I seem to have the same problem with geom_candlestick() whenever I use POSIXct values as x in aes(). Here's a way to get candlestick charts without {tidyquant}:

# bring in libraries and functions
library(dplyr)
library(ggplot2)

# create the price history data
dayData <-
  structure(list(ticker = c("BRK.B", "BRK.B", "BRK.B", "BRK.B", "BRK.B", 
                            "BRK.B", "BRK.B", "BRK.B", "BRK.B", "BRK.B", 
                            "BRK.B", "BRK.B", "BRK.B", "BRK.B", "BRK.B", 
                            "BRK.B", "BRK.B", "BRK.B", "BRK.B", "BRK.B", 
                            "BRK.B"), 
                 date = structure(c(19394, 19394, 19394, 19394, 19394, 19394, 
                                    19394, 19394, 19394, 19394, 19394, 19394, 
                                    19394, 19394, 19394, 19394, 19394, 19394, 
                                    19394, 19394, 19394), class = "Date"), 
                 date_time = structure(c(1675684800, 1675686600, 1675688400, 
                                         1675690200, 1675692000, 1675693800, 
                                         1675695600, 1675697400, 1675699200, 
                                         1675701000, 1675702800, 1675704600, 
                                         1675706400, 1675708200, 1675710000, 
                                         1675711800, 1675713600, 1675715400, 
                                         1675717200, 1675719000, 1675720800), 
                                       tzone = "America/New_York", 
                                       class = c("POSIXct", "POSIXt")), 
                 open = c(306.87, 307.34, 307, 307.11, 307.32, 308.25, 307.36, 
                          306.33, 307.28, 307.81, 306.89, 307.08, 307.22, 
                          307.475, 307.26, 308.04, 308.23, 307.46, 308.44, 
                          308.23, 308.7), 
                 high = c(307.4, 307.4, 308.51, 307.32, 308, 308.8, 307.42, 
                          307.3, 307.985, 308.11, 307.26, 307.57, 308.03, 
                          307.68, 308.14, 308.2, 308.2497, 308.48, 308.71, 
                          308.7, 308.7), 
                 low = c(306.65, 307.18, 306.95, 307, 307, 306.86, 306.22, 
                         305.6, 307.14, 306.64, 306.54, 307.04, 306.99, 
                         306.815, 307.26, 307.66, 307.47, 307.3, 306.7, 308.23, 
                         308.25), 
                 close = c(307.18, 307.36, 307.2, 307.14, 308, 307.38, 306.38, 
                           307.3, 307.78, 306.87, 307.06, 307.22, 307.545, 
                           307.22, 307.98, 308.2, 307.48, 308.47, 308.7, 308.7, 
                           308.43), 
                 volume = c(1629L, 835L, 1125L, 2593L, 2241L, 191730L, 100723L, 
                            101705L, 79902L, 61784L, 54728L, 54499L, 61446L, 
                            60656L, 49542L, 66834L, 75818L, 287762L, 1996734L, 
                            215L, 208L)), 
            class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -21L))

# set the times for market open and close
opTime <- as.POSIXct(paste(strptime(dayData$date_time[nrow(dayData)], 
                                    "%Y-%m-%d"), "09:30"))
clTime <- as.POSIXct(paste(strptime(dayData$date_time[nrow(dayData)], 
                                    "%Y-%m-%d"), "16:00"))                       
plt <- dayData %>% 
  mutate(boxCol = ifelse(open < close, "Up", "Down")) %>% 
  ggplot(aes(x = date_time)) + 
  geom_boxplot(stat = "identity", 
               aes(x = date_time, 
                   lower = ifelse(open < close, open, close), 
                   middle = close, 
                   upper = ifelse(open > close, open, close),
                   ymin = low, ymax = high, fill = boxCol,
                   group = date_time)) +
  scale_fill_manual(values = c("DarkRed", "DarkGreen")) +
  geom_vline(aes(xintercept = opTime), linetype = "dotted") +
  geom_vline(aes(xintercept = clTime), linetype = "dotted") +
  labs(title = paste("Today's Chart for:", 
                     dayData$ticker[1], 
                     "... on:", 
                     dayData$date[1]), 
       y = "Price", x = "Time")

print(plt)