2

How can I convert tick data into OHLC data using R? I have seen a couple of examples on here but the issue I am having is converting the actual times for the individual time stamps. For example the very first time stamp is 2013-07-29 15:30:00.

x <- read.delim(header=TRUE, stringsAsFactor=FALSE,"http://hopey.netfonds.no/tradedump.php?date=20130729&paper=AAPL.O&csv_format=txt")

xx <- xts(x[,c(2:3)], as.POSIXct(x[,1], "UTC", "%Y%m%dT%H%M%S"))

to.period(xx,"seconds",5)

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Jason
  • 311
  • 1
  • 4
  • 14
  • 1
    What have you tried? I don't understand how the first time stamp could possibly be on 2013-07-26. And your problem isn't how to convert tick to OHLC... it's how to convert the timestamp into an actual date-time object (e.g. `POSIXct`). – Joshua Ulrich Jul 30 '13 at 16:22
  • you are right i missed copied the tick data... the first time stamp should be 2013-06-14 15:30:00... i am new to R, but from what i have seen here, i am suppose to use xts ? & to.minute – Jason Jul 30 '13 at 16:39
  • @JoshuaUlrich also here is a link, that you have also helped on i believe? http://stackoverflow.com/questions/8830472/create-an-ohlc-series-from-ticker-data-using-r – Jason Jul 30 '13 at 16:45
  • I would strongly suggest using `read.delim()` instead of `read.csv()` -- it appears that the data you are pulling down are tab- rather than comma-separated. – Ben Bolker Jul 30 '13 at 20:25

1 Answers1

5

Just use to.period (or one of the wrappers) once you've created an xts object. To properly convert time to POSIXct, you have to specify the correct format (including the "T").

xx <- xts(x[,-1], as.POSIXct(x[,1], "UTC", "%Y%m%dT%H%M%S"))
to.period(xx, "seconds")

Also note that you should specify the timezone the time column was recorded in. I specified it as "UTC", since I don't know what timezone to use.

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
  • works well in converting the time, but i get an error message when i add: to.period (xx, "seconds") as such... – Jason Jul 30 '13 at 18:31
  • Warning message: timezone of object (UTC) is different than current timezone (). > to.period(xx,"seconds") Error in na.omit.xts(x) : unsupported type – Jason Jul 30 '13 at 18:32
  • My code works fine on the data in this Q/A. You didn't provide a reproducible example of your data, so I suspect there's an issue with your actual data. My guess is that's it's being coerced to character because xts/zoo objects can only contain a single data type. If you want me to do more than guess, provide a [reproducible example](http://stackoverflow.com/q/5963269/271616). – Joshua Ulrich Jul 30 '13 at 18:36
  • I'm not going to do your work for you. Simply providing the data is not a reproducible example. You should edit your question to read the data from the URL in your previous comment, then show what you tried and how it differed from what you expected. – Joshua Ulrich Jul 30 '13 at 19:31
  • i tried pulling the data straight from the source as shown on the original question.... to show what i have done. "x" contains no data... i dont know if that helps – Jason Jul 30 '13 at 20:15
  • 2
    @user2634864 try `read.delim` instead of `read.csv`, then `xx <- xts(x[,c(2:3)], as.POSIXct(x[,1], "UTC", "%Y%m%dT%H%M%S"))` – GSee Jul 30 '13 at 20:23
  • @JoshuaUlrich That did the trick, thank you so much. if i have data stored in Excel but save it as .csv & import it into Rstudio would i have to write `x <- read.csv(file,header=TRUE, stringsAsFactor=FALSE)` – Jason Jul 30 '13 at 20:34
  • If @JoshuaUlrich's answer solved your problem, you are encouraged (not required) to click the check-mark next to the answer to accept it ... – Ben Bolker Jul 30 '13 at 20:40
  • while you're at it, you might as well edit your original question to use `read.delim()`, so future readers don't get confused/have to dig into the comments to find out how to get Josh's answer to work. – Ben Bolker Jul 30 '13 at 20:46