Diagnosing Missing Timestamps in Hourly quantmod Charts
Summary
The document investigates why quantmod cannot create a candlestick chart from an hourly xts series even though the imported data appears to contain a year of hourly open, high, low, close, and volume observations. The reported error says periodicity cannot be calculated from one observation. The explanation is that periodicity estimates the typical interval from the median difference between index values; an NA in the time index can make that median NA, producing the same error as a series with only one observation.
The answer demonstrates the issue with an xts object whose timestamps include an NA, then suggests that daylight saving time may have caused a failed POSIXct conversion in the original data. The diagnosis points toward checking the parsed timestamps and time-zone handling before charting. The source does not confirm the precise problematic row or show a corrected import, so the daylight-saving explanation remains a likely cause rather than a demonstrated fix.
Key ideas
- quantmod periodicity estimates sampling frequency from differences between index timestamps.
- A missing timestamp can make the median interval undefined and trigger a misleading one-observation error.
- An xts series may look populated while containing an invalid time index.
- Daylight saving time is proposed as a possible cause of failed timestamp conversion.
- Inspect parsed timestamps and time-zone handling when hourly chart creation fails.
Tags
Full text
# How to plot custom hourly data into R with quantmod?
# How to plot custom hourly data into R with quantmod?
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 by Joshua Ulrich (score 2)
https://quant.stackexchange.com/a/9249
`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`.Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.