[R] Quantmod, Xts, TTR and Postgresql

[Joshua Ulrich]

On Tue, May 22, 2012 at 5:54 AM, R-type Studios <rtypestudios at 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.ulrich at gmail.com>
> wrote:
>>
>> On Tue, May 22, 2012 at 2:15 AM, R-type Studios <rtypestudios at 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
>
>