I personally like using Postgres with R and time series. I don't know about MySQL, but there is a fantastic contributed package for Postgres called PL/R. It lets your write stored procedures in R. This mean that I can keep 100's of millions of observations in Postgres, use SQL to select what I want and use R from directly inside the database. 
 
There are a number of time series operations that can be a pain to do in standard SQL, such as calculating a weekly return from a time series table (as you would typically have to self-join the table). These operations can be greatly improved using PL/R to write custom aggregates. Using these type of techniques it is possible to write queries like:
 
SELECT weekly_return(date, price) 
FROM data 
WHERE ticker = 'GOOG' 
 
It shouldn't take you long to import Yahoo finance data into Postgres. There are probably plenty of packages on the web that will do it for you. Once you have the data installed, you can connect to Postgres using ODBC and write your code. Once you are happy with your code, you can put it in PL/R and then access it from inside SQL statements.
 
I generally prefer to use SQL for working with large data sets as R is bound by memory size and is not as efficient at executing 'select' type statements as there is no native concept of an index. Depending on how involved your models are, you may end up writing a small handful of generic utility functions (like weekly_return) and coordinating most of your model construction and backtesting within SQL.
 
Josh

________________________________

From: r-sig-finance-bounces@stat.math.ethz.ch on behalf of Dry Eraser
Sent: Thu 9/20/2007 2:53 PM
To: r-sig-finance@stat.math.ethz.ch
Subject: [R-SIG-Finance] get.hist.quotes and MySQL



I want to screen the universe of stock price histories available from Yahoo for certain patterns.  That means iterating a screen over several thousand time series.  get.hist.quotes saves time series to CSV.  Putting everything into a database like MySQL seems to be a better idea than working with thousands of CSV files, especially when I might want to add new data points in the future.

I scoured the R and R packages manuals, the TOC of the Times Series Analysis Using R book, and googled r lists for information on get.hist.quotes and MySQL and came up only almost nothing on the practical use of MySQL.  (I did find a tantalizing pointer by Bollinger to a bunch of python, r and gnuplot packages for his own projects.  I think I might need to leverage python or perl to load up the database and then R packages manipulate the data.)

Any suggestions on where to learn more -- and find examples -- on using MySQL for ts and any comments on my type of project using R in general?

Thanks,
Ming


      
____________________________________________________________________________________

eChase.

        [[alternative HTML version deleted]]

_______________________________________________
R-SIG-Finance@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.



	[[alternative HTML version deleted]]

