So I got this data that I want to convert into xts so I can plot it using quantmod. (It is 15m chart)
> sample
Date Open High Low Close Vol.at.Price Volume
515 2016-06-15 pm 1:15:00 1.7381 1.7600 1.710 1.7399 1.7399 176000
516 2016-06-15 pm 1:30:00 1.7389 1.7400 1.725 1.7350 1.7350 152900
517 2016-06-15 pm 1:45:00 1.7350 1.7650 1.720 1.7550 1.7550 179900
518 2016-06-15 pm 2:00:00 1.7550 1.7600 1.740 1.7500 1.7500 130800
519 2016-06-15 pm 2:15:00 1.7550 1.7800 1.745 1.7800 1.7800 188400
520 2016-06-15 pm 2:30:00 1.7800 1.7899 1.730 1.7300 1.7300 256700
521 2016-06-15 pm 2:45:00 1.7300 1.7800 1.730 1.7664 1.7664 151900
522 2016-06-15 pm 3:00:00 1.7600 1.7700 1.740 1.7600 1.7600 74100
523 2016-06-15 pm 3:15:00 1.7501 1.8000 1.750 1.7850 1.7850 232300
524 2016-06-15 pm 3:30:00 1.7900 1.8200 1.760 1.7600 1.7600 183300
525 2016-06-15 pm 3:45:00 1.7600 1.7700 1.730 1.7400 1.7400 151600
526 2016-06-15 pm 4:00:00 1.7400 1.7599 1.650 1.7150 1.7150 443500
527 2016-06-15 pm 4:15:00 1.7100 1.7100 1.710 1.7100 1.7100 4628
528 2016-06-15 pm 6:30:00 1.7100 1.7100 1.710 1.7100 1.7100 2000
529 2016-06-15 pm 7:00:00 1.6900 1.6900 1.680 1.6900 1.6900 1050
530 2016-06-15 pm 8:00:00 1.6900 1.6900 1.690 1.6900 1.6900 100
> class(sample)
[1] "data.frame"
At first, I thought using xts function would do the job but then I realized it just cuts off minutes part.
> sampled.after<-xts(sample[2:ncol(sample)],as.Date(sample$Date))
> sampled.after
Open High Low Close Vol.at.Price Volume
2016-06-15 1.7381 1.7600 1.710 1.7399 1.7399 176000
2016-06-15 1.7389 1.7400 1.725 1.7350 1.7350 152900
2016-06-15 1.7350 1.7650 1.720 1.7550 1.7550 179900
2016-06-15 1.7550 1.7600 1.740 1.7500 1.7500 130800
2016-06-15 1.7550 1.7800 1.745 1.7800 1.7800 188400
2016-06-15 1.7800 1.7899 1.730 1.7300 1.7300 256700
2016-06-15 1.7300 1.7800 1.730 1.7664 1.7664 151900
2016-06-15 1.7600 1.7700 1.740 1.7600 1.7600 74100
2016-06-15 1.7501 1.8000 1.750 1.7850 1.7850 232300
2016-06-15 1.7900 1.8200 1.760 1.7600 1.7600 183300
2016-06-15 1.7600 1.7700 1.730 1.7400 1.7400 151600
2016-06-15 1.7400 1.7599 1.650 1.7150 1.7150 443500
2016-06-15 1.7100 1.7100 1.710 1.7100 1.7100 4628
2016-06-15 1.7100 1.7100 1.710 1.7100 1.7100 2000
2016-06-15 1.6900 1.6900 1.680 1.6900 1.6900 1050
2016-06-15 1.6900 1.6900 1.690 1.6900 1.6900 100
How do I convert it into timeseries so I can plot candle chart?
as.Date returns just the date portion of the timestamp. Use as.POSIXct to keep the full timestamp. In addition, you need to provide a format argument so that as.POSIXct understands your date format. Without the format argument, it just truncates the timestamp to the date portion. Here are a few examples:
as.Date returns date only
as.Date("2016-06-15 pm 1:15:00")
[1] "2016-06-15"
as.POSIXct without format argument returns date plus default timezone
as.POSIXct("2016-06-15 pm 1:15:00")
[1] "2016-06-15 PDT"
Add correct format to as.POSIXct in order to get full timestamp
as.POSIXct("2016-06-15 pm 1:15:00", format="%Y-%m-%d %p %I:%M:%S")
[1] "2016-06-15 13:15:00 PDT"
See the help for strptime for details on how to interpret the format argument above.
Add correct format and tz to as.POSIXct in order to get full timestamp and correct timezone
Note that as.POSIXct also added my local timezone to the result (PDT). You should therefore probably also add a timezone argument to make sure that the correct timezone is applied (especially if you have quotes from exchanges in different timezones). For example:
as.POSIXct("2016-06-15 pm 1:15:00", format="%Y-%m-%d %p %I:%M:%S", tz="America/New_York")
[1] "2016-06-15 13:15:00 EDT"