How to plot custom hourly data into R with quantmod?

Question

I'm trying to get into R because for some personal project, I need R and quantmod to create OHCL charts for me. I'm stuck at the candleChart creation step, and I'm not sure I understand why. Using a 'daily' input file works fine, but trying to create a hourly chart from hourly data is just a big failure.

Here is a sample of the data I'm using:

> zz <- read.csv(dataFile, sep=",", header=TRUE, stringsAsFactors=T)
> head(zz)
                 DATE OPEN HIGH LOW CLOSE VOLUME
1 2012-10-23 02:00:00   22   22  22    22    171
2 2012-10-23 03:00:00   22   22  22    22    171
3 2012-10-23 04:00:00   22   22  22    22    171
4 2012-10-23 05:00:00   22   22  22    22    171
5 2012-10-23 06:00:00   22   22  22    22    171
6 2012-10-23 07:00:00   22   22  22    22    171

As you can see, the first data is all the same but there is one year of data in my input file, and prices goes up to 96 at the end.

What I do next is create my xts object from this zz data frame like that:

> xx <- xts(zz[,2:6], order.by=as.POSIXct(zz[, 1], tz="", format="%Y-%m-%d %H:%M:%S"))
> head(xx)
                    OPEN HIGH LOW CLOSE VOLUME
2012-10-23 02:00:00   22   22  22    22    171
2012-10-23 03:00:00   22   22  22    22    171
2012-10-23 04:00:00   22   22  22    22    171
2012-10-23 05:00:00   22   22  22    22    171
2012-10-23 06:00:00   22   22  22    22    171
2012-10-23 07:00:00   22   22  22    22    171
> tail(xx)
                    OPEN HIGH LOW CLOSE VOLUME
2013-10-22 06:00:00   96   96  96    96    115
2013-10-22 07:00:00   96   96  96    96    115
2013-10-22 08:00:00   96   96  96    96    115
2013-10-22 09:00:00   96   96  96    96    115
2013-10-22 10:00:00   96   96  96    96    115
2013-10-22 11:00:00   96   96  96    96    118

Now, it looks like (to me) the xts is correct. There is the date+time in unnamed columns 1 and remaining columns named correctly to be understood by chartCandle() from quantmod.

Here is what happens when I try to plot it :

> candleChart(xx, name=tickerName, subset="last 3 weeks", bar.type = "ohlc")
Error in periodicity(x) : can not calculate periodicity of 1 observation
> candleChart(xx)
Error in periodicity(x) : can not calculate periodicity of 1 observation

I'm not sure why the first argument would be any subset of my XTS object ? Also, I found that :

> periodicity(xx)
Error in periodicity(xx) : can not calculate periodicity of 1 observation
> periodicity(xx)
Error in periodicity(xx) : can not calculate periodicity of 1 observation

The second call looks correct but I'm not sure why periodicity(xx) would think there is only 1 observation in my xts ?

Answer 1

periodicity calls:

p <- median(diff(.index(x)))
if (is.na(p)) 
  stop("can not calculate periodicity of 1 observation")

p can be NA if x has 1 observation, or if you have missing values in your index (because there's no na.rm=TRUE in the median call.

> xx <- xts(1:10, as.POSIXct(c(1:5,NA,7:10),origin='1970-01-01'))
> periodicity(xx)
Error in periodicity(xx) : can not calculate periodicity of 1 observation
> candleChart(xx)
Error in periodicity(x) : can not calculate periodicity of 1 observation

The NA in your index likely has to do with daylight saving time when you convert zz[, 1] to POSIXct.

Answer 2

I got stuck in a similar issue. My work around was to set the timezone of my dataframe as "UTC". It doesn't has a daylight time, the source of the problem. So try

xx <- xts(zz[,2:6], order.by=as.POSIXct(zz[, 1], tz="UTC", format="%Y-%m-%d %H:%M:%S"))