Re: [R] zoo.read intraday data
On Tue, Dec 21, 2010 at 11:36 PM, szimine szim...@gmail.com wrote: Hi Gabor et al. the f3 - function(...) as.POSIXct(paste(...), format = %Y%m%d %H:%M:%S ) helped me to read intraday data from file ## TICKER,NAME,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOL,OPENINT ICE.BRN,ice.brn_m5,5,20100802,10:40:00,79.21000,79.26000,79.16000,79.2,238,0 ICE.BRN,ice.brn_m5,5,20100802,10:45:00,79.19000,79.26000,79.19000,79.21000,413,0 ## ##intraday data 5m file fnameId= ./finam_brn_m5.csv pDateTimeColumns - list(4,5) b - read.zoo(fnameId, index=pDateTimeColumns , sep=,, header=TRUE, FUN=f3 ) xb - as.xts(b) head(b,2) ## X.TICKER. X.NAME. X.PER. X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL. X.OPENINT. 2010-08-02 10:40:00 ICE.BRN ice.brn_m5 5 79.21 79.26 79.16 79.20 238 0 2010-08-02 10:45:00 ICE.BRN ice.brn_m5 5 79.19 79.26 79.19 79.21 413 0 problem is that after the conversion to xts numeric values got converted to chars head(xb,2) X.TICKER. X.NAME. X.PER. X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL. X.OPENINT. 2010-08-02 10:40:00 ICE.BRN ice.brn_m5 5 79.21 79.26 79.16 79.20 238 0 2010-08-02 10:45:00 ICE.BRN ice.brn_m5 5 79.19 79.26 79.19 79.21 413 0 Read it all in using read.csv and the reread it using read.zoo (note that read.zoo can read data.frames) excluding the bad columns or else use the colClasses argument to suppress the unwanted column(s). Here are 4 methods. Lines - TICKER,NAME,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOL,OPENINT ICE.BRN,ice.brn_m5,5,20100802,10:40:00,79.21000,79.26000,79.16000,79.2,238,0 ICE.BRN,ice.brn_m5,5,20100802,10:45:00,79.19000,79.26000,79.19000,79.21000,413,0 library(zoo) # method 1. read.csv/read.zoo with split= and removing col 2 DF - read.csv(textConnection(Lines)) z1 - read.zoo(DF[-2], split = 1, index = list(3, 4), FUN = f3) # method 2. read.csv/read.zoo removing col 1 and 2 # this one only works if there is one ticker DF - read.csv(textConnection(Lines)) z2 - read.zoo(DF[-(1:2)], index = list(2, 3), FUN = f3) # method 3. read.zoo with colClasses as in #1 colClasses - c(character, NULL, numeric, character, character, rep(numeric, 6)) z3 - read.zoo(textConnection(Lines), header = TRUE, sep = ,, split = 1, index = list(3, 4), FUN = f3, colClasses = colClasses) #4. A method similar to #2 could also be used based on colClasses. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] zoo.read intraday data
Dear Gabor, many thanks for the quick reply. for the sake of a working example in list archive, code below reads a csv with 5 min intraday bars and converts it to a quantmod::barChart_able xts object ##csv file 5min bars with the format below #TICKER,NAME,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOL,OPENINT #ICE.BRN,ice.brn_m5,5,20100802,10:40:00,79.21000,79.26000,79.16000,79.2,238,0 # ICE.BRN,ice.brn_m5,5,20100802,10:45:00,79.19000,79.26000,79.19000,79.21000,413,0 ## ##intraday data 5m file fnameId= ~/rlab/csvdat/finam_brn_m5.csv df -read.csv(fnameId,sep=',' , header=TRUE) f3 - function(...) as.POSIXct(paste(...), format = %Y%m%d %H:%M:%S) b - read.zoo(df[-(1:3)], index=list(1,2),FUN=f3 ) xb - as.xts(b) colnames(xb)=c(Open,High,Low,Close,Volume,OpenInt) barChart(xb) On Wed, Dec 22, 2010 at 1:39 PM, Gabor Grothendieck ggrothendi...@gmail.com wrote: On Tue, Dec 21, 2010 at 11:36 PM, szimine szim...@gmail.com wrote: Hi Gabor et al. the f3 - function(...) as.POSIXct(paste(...), format = %Y%m%d %H:%M:%S ) helped me to read intraday data from file ## TICKER,NAME,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOL,OPENINT ICE.BRN,ice.brn_m5,5,20100802,10:40:00,79.21000,79.26000,79.16000,79.2,238,0 ICE.BRN,ice.brn_m5,5,20100802,10:45:00,79.19000,79.26000,79.19000,79.21000,413,0 ## ##intraday data 5m file fnameId= ./finam_brn_m5.csv pDateTimeColumns - list(4,5) b - read.zoo(fnameId, index=pDateTimeColumns , sep=,, header=TRUE, FUN=f3 ) xb - as.xts(b) head(b,2) ## X.TICKER. X.NAME. X.PER. X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL. X.OPENINT. 2010-08-02 10:40:00 ICE.BRN ice.brn_m5 5 79.21 79.26 79.16 79.20 238 0 2010-08-02 10:45:00 ICE.BRN ice.brn_m5 5 79.19 79.26 79.19 79.21 413 0 problem is that after the conversion to xts numeric values got converted to chars head(xb,2) X.TICKER. X.NAME. X.PER. X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL. X.OPENINT. 2010-08-02 10:40:00 ICE.BRN ice.brn_m5 5 79.21 79.26 79.16 79.20 238 0 2010-08-02 10:45:00 ICE.BRN ice.brn_m5 5 79.19 79.26 79.19 79.21 413 0 Read it all in using read.csv and the reread it using read.zoo (note that read.zoo can read data.frames) excluding the bad columns or else use the colClasses argument to suppress the unwanted column(s). Here are 4 methods. Lines - TICKER,NAME,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOL,OPENINT ICE.BRN,ice.brn_m5,5,20100802,10:40:00,79.21000,79.26000,79.16000,79.2,238,0 ICE.BRN,ice.brn_m5,5,20100802,10:45:00,79.19000,79.26000,79.19000,79.21000,413,0 library(zoo) # method 1. read.csv/read.zoo with split= and removing col 2 DF - read.csv(textConnection(Lines)) z1 - read.zoo(DF[-2], split = 1, index = list(3, 4), FUN = f3) # method 2. read.csv/read.zoo removing col 1 and 2 # this one only works if there is one ticker DF - read.csv(textConnection(Lines)) z2 - read.zoo(DF[-(1:2)], index = list(2, 3), FUN = f3) # method 3. read.zoo with colClasses as in #1 colClasses - c(character, NULL, numeric, character, character, rep(numeric, 6)) z3 - read.zoo(textConnection(Lines), header = TRUE, sep = ,, split = 1, index = list(3, 4), FUN = f3, colClasses = colClasses) #4. A method similar to #2 could also be used based on colClasses. -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] zoo.read intraday data
Hi Gabor et al. the f3 - function(...) as.POSIXct(paste(...), format = %Y%m%d %H:%M:%S ) helped me to read intraday data from file ## TICKER,NAME,PER,DATE,TIME,OPEN,HIGH,LOW,CLOSE,VOL,OPENINT ICE.BRN,ice.brn_m5,5,20100802,10:40:00,79.21000,79.26000,79.16000,79.2,238,0 ICE.BRN,ice.brn_m5,5,20100802,10:45:00,79.19000,79.26000,79.19000,79.21000,413,0 ## ##intraday data 5m file fnameId= ./finam_brn_m5.csv pDateTimeColumns - list(4,5) b - read.zoo(fnameId, index=pDateTimeColumns , sep=,, header=TRUE, FUN=f3 ) xb - as.xts(b) head(b,2) ## X.TICKER. X.NAME.X.PER. X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL. X.OPENINT. 2010-08-02 10:40:00 ICE.BRN ice.brn_m5 5 79.21 79.26 79.16 79.20 238 0 2010-08-02 10:45:00 ICE.BRN ice.brn_m5 5 79.19 79.26 79.19 79.21 413 0 problem is that after the conversion to xts numeric values got converted to chars head(xb,2) X.TICKER. X.NAME. X.PER. X.OPEN. X.HIGH. X.LOW. X.CLOSE. X.VOL. X.OPENINT. 2010-08-02 10:40:00 ICE.BRN ice.brn_m5 579.21 79.26 79.16 79.20 238 0 2010-08-02 10:45:00 ICE.BRN ice.brn_m5 579.19 79.26 79.19 79.21 413 0 and quantmod charting does not work. Q. how to prevent converting to char with xts ? I suspect the problem is that index is constructed from two columns date and time. sessionInfo() R version 2.12.1 (2010-12-16) Platform: x86_64-apple-darwin9.8.0/x86_64 (64-bit) locale: [1] C attached base packages: [1] stats graphics grDevices utils datasets methods base other attached packages: [1] quantmod_0.3-15 TTR_0.20-2 Defaults_1.1-1 xts_0.7-6.11 zoo_1.7-0 Slava -- View this message in context: http://r.789695.n4.nabble.com/zoo-read-intraday-data-tp3010256p3160102.html Sent from the R help mailing list archive at Nabble.com. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
[R] zoo.read intraday data
Hello all, I'm trying to use zoo.read but can't figure out how to deal with the time format. (example below) would be nice if someone could help. best regards, Immanuel --- L - Date,Time,Open,High,Low,Close,Up,Down 05.02.2001,00:30,421.20,421.20,421.20,421.20,11,0 05.02.2001,01:30,421.20,421.40,421.20,421.40,7,0 05.02.2001,02:00,421.30,421.30,421.30,421.30,0,5 05.02.2001,02:30,421.60,421.60,421.50,421.50,26,1 library(zoo) library(chron) f - function(x) chron(paste(x[,1]),paste(x[,2]), format = c(dates = D.M.Y, times = hh:mm)) z - read.zoo(textConnection(L), index = 1:2, sep=,, header = TRUE, FUN = f) print(z) __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] zoo.read intraday data
On Mon, 25 Oct 2010, Immanuel wrote: Hello all, I'm trying to use zoo.read Just for the record: read.zoo(). but can't figure out how to deal with the time format. (example below) Yes, the problem is only the chron conversion (and not read.zoo). would be nice if someone could help. best regards, Immanuel --- L - Date,Time,Open,High,Low,Close,Up,Down 05.02.2001,00:30,421.20,421.20,421.20,421.20,11,0 05.02.2001,01:30,421.20,421.40,421.20,421.40,7,0 05.02.2001,02:00,421.30,421.30,421.30,421.30,0,5 05.02.2001,02:30,421.60,421.60,421.50,421.50,26,1 library(zoo) library(chron) f - function(x) chron(paste(x[,1]),paste(x[,2]), format = c(dates = D.M.Y, times = hh:mm)) I think that chron might require seconds. Hence you can add 00 seconds for all times and change the format accordingly, e.g., f - function(x) chron(paste(x[,1]), paste(x[,2], 00, sep = :), format = c(dates = d.m.y, times = h:m:s)) Then, the call below should work ok. hth, Z z - read.zoo(textConnection(L), index = 1:2, sep=,, header = TRUE, FUN = f) print(z) __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] zoo.read intraday data
Hey, work's like a charm. thanks __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] zoo.read intraday data
On Mon, Oct 25, 2010 at 9:02 AM, Immanuel mane.d...@googlemail.com wrote: Hello all, I'm trying to use zoo.read but can't figure out how to deal with the time format. (example below) would be nice if someone could help. best regards, Immanuel --- L - Date,Time,Open,High,Low,Close,Up,Down 05.02.2001,00:30,421.20,421.20,421.20,421.20,11,0 05.02.2001,01:30,421.20,421.40,421.20,421.40,7,0 05.02.2001,02:00,421.30,421.30,421.30,421.30,0,5 05.02.2001,02:30,421.60,421.60,421.50,421.50,26,1 library(zoo) library(chron) f - function(x) chron(paste(x[,1]),paste(x[,2]), format = c(dates = D.M.Y, times = hh:mm)) z - read.zoo(textConnection(L), index = 1:2, sep=,, header = TRUE, FUN = f) print(z) Here are a few more possibilities: # use chron appending seconds and index = list(1, 2) f - function(d, t, format = c(m.d.y, h:m:s)) { chron(d, paste(t, 00, sep = :), format = format) } z - read.zoo(textConnection(L), index = list(1, 2), sep=,, header = TRUE, FUN = f) # use as.chron and index = list(1, 2) f2 - function(d, t, format = %d.%m.%Y %H:%M) { as.chron(paste(d, t), format = format) } z2 - read.zoo(textConnection(L), index = list(1, 2), sep=,, header = TRUE, FUN = f) -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] zoo.read intraday data
Thanks, by now I ran already into the next problem, while trying to convert the zoo to an xts object. Somehow the timestamps get lost, no idea why. I did read through the vignettes, but got confused by all this, chron, as.POSIXct, zoo and xts stuff. with your example I got: Error in .subset(x, j) : invalid subscript type 'list' best regards - # TODO: Add comment # # Author: flyingimmi ### L - Date,Time,Open,High,Low,Close,Up,Down 05.02.2001,00:30,421.20,421.20,421.20,421.20,11,0 05.02.2001,01:30,421.20,421.40,421.20,421.40,7,0 06.02.2001,02:00,421.30,421.30,421.30,421.30,0,5 05.02.2001,02:30,421.60,421.60,421.50,421.50,26,1 library(zoo) library(chron) f - function(x) chron(paste(x[,1]), paste(x[,2], 00, sep = :), format = c(dates = d.m.y, times = h:m:s)) # z - read.zoo(myfile.csv, index = 1:2, sep=,, header = TRUE, FUN = f) z - read.zoo(textConnection(L), index = 1:2, sep=,, header = TRUE, FUN = f) intradayData - xts(z, time(z)) print(z) print(intradayData) --- output: Open High Low Close Up Down (05.02.01 00:30:00) 421.2 421.2 421.2 421.2 110 (05.02.01 01:30:00) 421.2 421.4 421.2 421.4 70 (05.02.01 02:30:00) 421.6 421.6 421.5 421.5 261 (06.02.01 02:00:00) 421.3 421.3 421.3 421.3 05 Open High Low Close Up Down NA 421.2 421.2 421.2 421.2 110 NA 421.2 421.4 421.2 421.4 70 NA 421.6 421.6 421.5 421.5 261 NA 421.3 421.3 421.3 421.3 05 On 10/25/2010 03:47 PM, Gabor Grothendieck wrote: On Mon, Oct 25, 2010 at 9:02 AM, Immanuel mane.d...@googlemail.com wrote: Hello all, I'm trying to use zoo.read but can't figure out how to deal with the time format. (example below) would be nice if someone could help. best regards, Immanuel --- L - Date,Time,Open,High,Low,Close,Up,Down 05.02.2001,00:30,421.20,421.20,421.20,421.20,11,0 05.02.2001,01:30,421.20,421.40,421.20,421.40,7,0 05.02.2001,02:00,421.30,421.30,421.30,421.30,0,5 05.02.2001,02:30,421.60,421.60,421.50,421.50,26,1 library(zoo) library(chron) f - function(x) chron(paste(x[,1]),paste(x[,2]), format = c(dates = D.M.Y, times = hh:mm)) z - read.zoo(textConnection(L), index = 1:2, sep=,, header = TRUE, FUN = f) print(z) Here are a few more possibilities: # use chron appending seconds and index = list(1, 2) f - function(d, t, format = c(m.d.y, h:m:s)) { chron(d, paste(t, 00, sep = :), format = format) } z - read.zoo(textConnection(L), index = list(1, 2), sep=,, header = TRUE, FUN = f) # use as.chron and index = list(1, 2) f2 - function(d, t, format = %d.%m.%Y %H:%M) { as.chron(paste(d, t), format = format) } z2 - read.zoo(textConnection(L), index = list(1, 2), sep=,, header = TRUE, FUN = f) __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] zoo.read intraday data
On Mon, Oct 25, 2010 at 11:44 AM, Immanuel mane.d...@googlemail.com wrote: Thanks, by now I ran already into the next problem, while trying to convert the zoo to an xts object. Somehow the timestamps get lost, no idea why. I did read through the vignettes, but got confused by all this, chron, as.POSIXct, zoo and xts stuff. with your example I got: Error in .subset(x, j) : invalid subscript type 'list' best regards - # TODO: Add comment # # Author: flyingimmi ### L - Date,Time,Open,High,Low,Close,Up,Down 05.02.2001,00:30,421.20,421.20,421.20,421.20,11,0 05.02.2001,01:30,421.20,421.40,421.20,421.40,7,0 06.02.2001,02:00,421.30,421.30,421.30,421.30,0,5 05.02.2001,02:30,421.60,421.60,421.50,421.50,26,1 library(zoo) library(chron) f - function(x) chron(paste(x[,1]), paste(x[,2], 00, sep = :), format = c(dates = d.m.y, times = h:m:s)) # z - read.zoo(myfile.csv, index = 1:2, sep=,, header = TRUE, FUN = f) z - read.zoo(textConnection(L), index = 1:2, sep=,, header = TRUE, FUN = f) intradayData - xts(z, time(z)) print(z) print(intradayData) --- output: Open High Low Close Up Down (05.02.01 00:30:00) 421.2 421.2 421.2 421.2 11 0 (05.02.01 01:30:00) 421.2 421.4 421.2 421.4 7 0 (05.02.01 02:30:00) 421.6 421.6 421.5 421.5 26 1 (06.02.01 02:00:00) 421.3 421.3 421.3 421.3 0 5 Open High Low Close Up Down NA 421.2 421.2 421.2 421.2 11 0 NA 421.2 421.4 421.2 421.4 7 0 NA 421.6 421.6 421.5 421.5 26 1 NA 421.3 421.3 421.3 421.3 0 5 I don't think xts supports chron. Try using POSIXct instead: f3 - function(...) as.POSIXct(paste(...), format = %d.%m.%Y %H:%M) z3 - read.zoo(textConnection(L), index = list(1, 2), sep=,, header = TRUE, FUN = f3) as.xts(z3) -- Statistics Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.