Hi Joshua,
All sorted, turns out I just needed to add this "header=TRUE" and drop
those redundant statements you pointed out.
for anyone else:
# Libs
library('RPostgreSQL') # http://code.google.com/p/rpostgresql/
library('quantmod')
library('TTR')
# Connect and get data
drv <- dbDriver('PostgreSQL')
db <- dbConnect(drv, host='localhost', user='postgres', dbname='technica',
password='password')
fr <- dbGetQuery(db, 'SELECT date as "Date", open as "Open", high as
"High", low as "Low", close as "Close", volume as "Volume", 0 as "Adjusted"
FROM stocks s INNER JOIN historical_prices hp ON s.id = hp.stock_id WHERE
s.symbol = \'SDL\' ORDER BY date DESC limit 500')
# Clean up.
dbDisconnect(db)
dbUnloadDriver(drv)
fr <- xts(fr[,-1], order.by=as.Date(fr[,1]), updated=Sys.time(),
header=TRUE)
# Step 2: Create your indicator
dvi <- DVI(Cl(fr))
Thanks for your help Joshua!
Thanks!
M
On Tue, May 22, 2012 at 7:07 PM, Joshua Ulrich <josh.m.ulr...@gmail.com>wrote:
> On Tue, May 22, 2012 at 5:54 AM, R-type Studios <rtypestud...@gmail.com>
> wrote:
> > Hi Joshua,
> >
> > Oh snap, awesome to have the author of the blog your reading at that
> > moment to reply to your message.
> >
> > I updated the query to be 200 sessions, the function now prints, but
> appears
> > to get the dates all wrong. (I actually was following along with the
> post
> > here:
> > http://blog.fosstrading.com/2011/03/how-to-backtest-strategy-in-r.html
> great
> > blog by the way.)
> >
> > What I have now (simplified it a bit):
> >
> > # Libs
> > library('RPostgreSQL') # http://code.google.com/p/rpostgresql/
> > library('quantmod')
> > library('TTR')
> >
> > # Connect and get data
> >
> >
> > drv <- dbDriver('PostgreSQL')
> > db <- dbConnect(drv, host='localhost', user='postgres',
> dbname='technica',
> > password='password')
> > fr <- dbGetQuery(db, 'SELECT date as "Date", open as "Open", high as
> "High",
> > low as "Low", close as "Close", volume as "Volume", 0 as "Adjusted" FROM
> > stocks s INNER JOIN historical_prices hp ON s.id = hp.stock_id WHERE
> > s.symbol = \'SDL\' ORDER BY date DESC limit 200')
> >
> > fr <- data.frame(fr[,-1], row.names=fr[,1])
> > fr <- xts(as.matrix(fr[,-1]), order.by=as.Date(fr[,1]),
> updated=Sys.time())
> >
> Your problem is likely here and has nothing to do with DVI. Take a
> step back and look at your intermediate objects.
>
> The result of dbGetQuery is a data.frame with columns Date, OHLC,
> Volume, and Adjusted. Then you overwrite 'fr' with another data.frame
> that drops the Date column after setting them as the row names. Then
> you overwrite 'fr' again with an xts object that contains HLC, Volume,
> and Adjusted columns, ordered by the Open prices converted to Dates.
>
> > dvi <- DVI(Cl(fr))
> > print(dvi)
> >
> > # Clean up.
> > dbDisconnect(db)
> > dbUnloadDriver(drv)
> >
> > Then as you can see below the dates are all out of whack, My guess was it
> > was the date format that postgres returns (being mm/dd/yyyy) but no luck,
> > any ideas?
> >
> See above. If my assumptions aren't correct, then please provide a
> small sample of the result from dbGetQuery.
>
> > Thanks!
> > Max
> >
> > dvi.mag dvi.str dvi
> > 1970-01-01 NA NA NA
> > (Repeated a few hundread times)
> > Then this:
> > 1970-01-01 0.134920635 0.349206349 0.17777778
> <snip>
> > 1970-01-01 0.734126984 0.996031746 0.78650794
> >
> >
> > On Tue, May 22, 2012 at 6:26 PM, Joshua Ulrich <josh.m.ulr...@gmail.com>
> > wrote:
> >>
> >> On Tue, May 22, 2012 at 2:15 AM, R-type Studios <rtypestud...@gmail.com
> >
> >> wrote:
> >> > Hi Everyone,
> >> >
> >> > I'm currently using the latest build of R and R-Studio server (both
> are
> >> > amazing products)
> >> >
> >> > I'm still very new to this but I came across this issue:
> >> >
> >> > I'm trying to do a select from postgres and put the data into and xts
> >> > object like so:
> >> >
> >> > # Libs
> >> > library('RPostgreSQL') # http://code.google.com/p/rpostgresql/
> >> > library('quantmod')
> >> > library('TTR')
> >> > library('xts') #
> >> > http://cran.r-project.org/web/packages/xts/vignettes/xts.pdf
> >> >
> >> > # Connect and get data
> >> > drv <- dbDriver('PostgreSQL')
> >> > db <- dbConnect(drv, host='localhost', user='postgres',
> >> > dbname='technica',
> >> > password='password')
> >> > fr <- dbGetQuery(db, 'SELECT date as "Date", open as "Open", high as
> >> > "High", low as "Low", close as "Close", volume as "Volume", random()
> as
> >> > "Adjusted" FROM stocks s INNER JOIN historical_prices hp ON s.id =
> >> > hp.stock_id WHERE s.symbol = \'SDL\' ORDER BY date DESC limit 50')
> >> >
> >> > # copied from the mysql code in the quantmod source
> >> > fr <- data.frame(fr[,-1],row.names=fr[,1])
> >> > fr <- xts(as.matrix(fr[,-1]), order.by=as.Date(fr[,1],
> >> > origin='1970-01-01'), src='technica', updated=Sys.time())
> >> > colnames(fr) <- paste('SDL',
> >> > c('Open','High','Low','Close','Volume','Adjusted'), sep='.')
> >> >
> >> > dvi <- DVI(Cl(fr))
> >> > print(dvi)
> >> >
> >> > When the code is executed I receive the error for the line dvi <-
> >> > DVI(Cl(fr)): "Error in runSum(x, n) : Invalid 'n'"
> >> >
> >> The defaults for DVI's magnitude and stretch arguments require there
> >> be at least 100 observations. My guess is that your 'fr' object
> >> doesn't have the required 100 observations. If it doesn't, then using
> >> the default value of n = 252 may also be an issue (giving misleading
> >> results, if not an error).
> >>
> >> > But if I do this (fetch the data from yahoo):
> >> >
> >> > getSymbols("SDL.AX")
> >> > dvi <- DVI(Cl(SDL.AX))
> >> > print(dvi)
> >> >
> >> > All seems to work fine, but I cant see any difference when I print the
> >> > two
> >> > data sets out, except the data set size.
> >> >
> >> > Any ideas what I'm doing wrong? Id really like to be able to import
> from
> >> > my
> >> > postgres database.
> >> >
> >> > Also i would be happy to write a postgres routine and submit it to
> >> > quantmod
> >> > if i get this working
> >> >
> >> > Thanks!
> >> > Max
> >> >
> >>
> >> Best,
> >> --
> >> Joshua Ulrich | FOSS Trading: www.fosstrading.com
> >
> >
>
[[alternative HTML version deleted]]
______________________________________________
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.
