[R-SIG-Finance] multivariate zoo and looping

[Gabor Grothendieck]

Please try to provide reproducible code. That means that
one can simply copy it from the post and paste it into their
browser to rerun it and it has no dependencies. See last
line to every message to r-help.

If the question is how to get a zoo object from a data frame
then try this:

# create DF, a test data frame

Lines <- "ticker,time,open,bid
LQD,2009-09-10 10:39:00,104.99,104.96
LQD,2009-09-10 10:39:00,104.99,104.96
LQD,2009-09-10 10:40:00,104.99,104.96
LQD,2009-09-10 10:41:00,104.99,104.96
LQD,2009-09-10 10:42:00,104.99,104.98
LQD,2009-09-10 10:43:00,104.99,104.96"

DF <- read.csv(textConnection(Lines),  header = TRUE, as.is = TRUE)
DF$time <- as.POSIXct(DF$time)

# aggregate duplicate times
DFag <- aggregate(DF[3:4], DF["time"], mean)

#  convert to zoo
library(zoo)
z <- zoo(DFag[-1], DFag$time)


On Sun, Sep 13, 2009 at 10:09 AM, zubin <binabina at bellsouth.net> wrote:
> Hello, I am working on some data input routines and learning zoo.
>
> I have a SQL database with stock price data by ticker, data looks like
> below. Tickers stacked in the db.
>
> LQD
>    ticker                time   open    bid    ask volume
> currentsystemtime
> 1      LQD 2009-09-10 10:39:00 104.99 104.96 104.99 199253 2009-09-10
> 07:48:28
> 2      LQD 2009-09-10 10:39:00 104.99 104.96 104.99 199446 2009-09-10
> 07:49:04
> 3      LQD 2009-09-10 10:40:00 104.99 104.96    105 200184 2009-09-10
> 07:50:04
> 4      LQD 2009-09-10 10:41:00 104.99 104.96 104.99 200684 2009-09-10
> 07:51:04
> 5      LQD 2009-09-10 10:42:00 104.99 104.98 104.99 201124 2009-09-10
> 07:52:04
> 6      LQD 2009-09-10 10:43:00 104.99 104.96 104.97 201874 2009-09-10
> 07:53:04
>
> My objective is to create a multivariate ZOO object so i can perform
> modeling tasks.   I am successful at doing this manually, now automating
> with loops.
> I can do this manually, create a POSIXct from currentsystemtime and
> generate a zoo object.
>
>
> Question 1)  From this data set above i create a zoo object:
>
> d$time_system <- as.character(LQD$currentsystemtime)
> d$time_system <- as.POSIXct(d$time_system, "%Y-%m-%d %H:%M:%S", tz="")
> dzoo <- zoo(d$ask, d$time_system)
>
> If i wanted to create a zoo object with not only the ask prices but
> multiple columns, I cant figure out how this is done?  Do i need to
> create a set of zoo objects and merge?
>
>
> Okay moving on now to the looping:
>
>
> Step1) Fetch tickernames only from dataset
>
> tickernames<-dbGetQuery(conn,"SELECT distinct ticker from zstockdata");
>
> works fine get:  > tickernames
> sample tickers, there are 23
> 1      EEM
> 2      FAS
> 3      GLD
> 4      HYG
> 5      IWM
> 6      LQD
> 7     QQQQ
>
>  > tickernames
> its a factor
>
> 'data.frame':    23 obs. of  1 variable:
> $ ticker: Factor w/ 23 levels "^IRX","^TNX",..: 5 6 7 8 9 10 11 12 13 14
> ...
>
> 2) Now i loop thru each ticker, create a data frame object in R.
>
> for(i in 1:nrow(tickernames))
> {
>        sqlstring <- paste("select * from zstockdata where TICKER
> =","'",tickernames[i,1],"'",sep="")
>        tname <- as.character(tickernames[i,1])
> ??A     assign(tname, dbGetQuery(conn,sqlstring))
>
> #perform some conversions and create a zoo object
>
> ??B    tname$ticker <- as.character(tname$ticker)  ??????
>
> }
>
> a) Questions from here:   ??A, Works well, generates a data frame object
> for each query by ticker.  But this may be inefficient, i was thinking
> of using an array to store each dataframe.   I tried this:
>
> mvtime[[i]] <-dbGetQuery(conn,sqlstring)
>
> double brackets, when i run this i get object "mvtime" not found
>
> How do i initialize an array to store multiple data frames.  A data
> frame array object?  is there such a thing.  That would be optimal i
> think vs an object for each ticker.
>
>
> b) ??B,  Now i need to reference each data frame object, do some data
> cleanups, conversion, etc..
>
> (example: one of many conversions:  convert ticker from factor to a
> character)
>
> tname$ticker <- as.character(tname$ticker)
> Error in tname$ticker : $ operator is invalid for atomic vectors
>  > tname
> [1] "EEM"
>
> However, this works:
> EEM$ticker <- as.character(EEM$ticker)
>
> So i don't understand R well enough, the tname is not being rendered
> into "EEM"?
>
> The end game is a cleansed multivariate zoo object with all the tickers
> and all the columns of data in one object.
>
> -zubin
>
>
>        [[alternative HTML version deleted]]
>
> _______________________________________________
> R-SIG-Finance at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-sig-finance
> -- Subscriber-posting only.
> -- If you want to post, subscribe first.
>