2

I am trying to import data into R for the first time to use in the R package quantstrat. Please see the following:

fn1 <- "fgbl_formatted_vpoc_prior_week.txt"
> fn1

> dat <- read.table(file=fn1,sep=",",header=T,as.is=T)
> dat
              Timestamp   Open   High    Low   Last Volume
1   2016-09-27 02:00:00 165.50 165.58 165.46 165.47     2001
2   2016-09-27 03:00:00 165.47 165.65 165.46 165.63     1345
3   2016-09-27 04:00:00 165.64 165.92 165.59 165.91     1241
4   2016-09-27 05:00:00 165.91 166.13 165.91 165.97     880
5   2016-09-27 06:00:00 165.98 165.98 165.76 165.78     748

Can someone show how to now get this in a format suitable for quantstrat with the correct date/time format which I think is POSIXct. I am struggling to find any documentation showing how to import this kind of data.

FXQuantTrader
  • 6,821
  • 3
  • 36
  • 67
nipy
  • 5,138
  • 5
  • 31
  • 72

3 Answers3

2

You are correct in wanting time to be in POSIXct format. Quantstrat uses xts objects, which you'll need to create. You didn't provide an easily reproducible so the first bit of code here generates your data:

library(xts)
data <- "
1   2016-09-27 02:00:00 165.50 165.58 165.46 165.47     2001
2   2016-09-27 03:00:00 165.47 165.65 165.46 165.63     1345
3   2016-09-27 04:00:00 165.64 165.92 165.59 165.91     1241
4   2016-09-27 05:00:00 165.91 166.13 165.91 165.97     880
5   2016-09-27 06:00:00 165.98 165.98 165.76 165.78     748"
dat <- read.table(text = data,
                     col.names = c("num", "date", "time", "Open" ,  "High",    "Low",   "Last", "Volume"))
dat <- cbind("Timestamp" = paste(dat$date, dat$time), dat)
# Make dat look just like your example when loaded in R:
dat[, c("num", "date", "time")] <- NULL

# Now have your object, which would be a data.frame:

dat
# Timestamp   Open   High    Low   Last Volume
# 1 2016-09-27 02:00:00 165.50 165.58 165.46 165.47   2001
# 2 2016-09-27 03:00:00 165.47 165.65 165.46 165.63   1345
# 3 2016-09-27 04:00:00 165.64 165.92 165.59 165.91   1241
# 4 2016-09-27 05:00:00 165.91 166.13 165.91 165.97    880
# 5 2016-09-27 06:00:00 165.98 165.98 165.76 165.78    748

posix_times <- as.POSIXct(dat[, 1])
x_dat <- xts(x = dat[, 2:NCOL(dat)], order.by = posix_times)
> x_dat
# Open   High    Low   Last Volume
# 2016-09-27 02:00:00 165.50 165.58 165.46 165.47   2001
# 2016-09-27 03:00:00 165.47 165.65 165.46 165.63   1345
# 2016-09-27 04:00:00 165.64 165.92 165.59 165.91   1241
# 2016-09-27 05:00:00 165.91 166.13 165.91 165.97    880
# 2016-09-27 06:00:00 165.98 165.98 165.76 165.78    748

> class(x_dat)
#[1] "xts" "zoo"

x_dat is what you can use in quantstrat.

PS:

You may find your learning process with quantstrat will be a lot faster if you follow a good resource like http://www.r-programming.org/papers or the (not free =( ) quantstrat course at datacamp: https://www.datacamp.com/courses/financial-trading-in-r

FXQuantTrader
  • 6,821
  • 3
  • 36
  • 67
1

There is no need to convert to POSIXct at all. Import your file with read.zoo instead of read.table. Assuming dat is a dataframe as in your example

> class(dat)
[1] "data.frame"
> dat
         Timestamp   Open   High    Low   Last Volume
1 2016-09-27 02:00 165.50 165.58 165.46 165.47   2001
2 2016-09-27 03:00 165.47 165.65 165.46 165.63   1345
3 2016-09-27 04:00 165.64 165.92 165.59 165.91   1241
4 2016-09-27 05:00 165.91 166.13 165.91 165.97    880
5 2016-09-27 06:00 165.98 165.98 165.76 165.78    748

all that is needed is:

> dat <- as.xts(read.zoo(dat))

check class:

> class(dat)
[1] "xts" "zoo"

So to go back to your text file all you have to do to import it as `xts object' is:

dat <- as.xts(read.zoo("yourFileName.txt", arguments needed for your file)

hvollmeier
  • 2,956
  • 1
  • 12
  • 17
0

are column names case sensitive there?

cause I noticed that quantmod's chartSeries()

does not like 

colnames(my_data) <- c('Open', 'High', 'Low', 'Close', 'Volume', 'Oi')

they have to be lower case, like:

> class(my_data)
[1] "xts" "zoo"
> tail(my_data)
                       open    high     low   close volume      oi
2016-12-30 10:00:00 2233.50 2234.50 2228.00 2229.25  71515  743254
2016-12-30 10:30:00 2229.25 2234.75 2228.75 2233.75  74937  818191
2016-12-30 11:00:00 2233.75 2235.75 2229.25 2235.00 180772  998981
2016-12-30 11:30:00 2234.75 2237.50 2233.75 2234.75 245717 1244735
2016-12-30 12:00:00 2234.50 2235.25 2233.00 2233.75   6565 1251318
2016-12-30 12:30:00 2233.50 2234.00 2233.25 2233.50    686 1252004
QuanTomatic
  • 51
  • 5