[R-SIG-Finance] get.hist.quote and mysql

[Jeff Ryan]

That bit of python - if you are downloading a lot of symbols, you'll
probably want to add some sort of timeout between request - lest you
get throttled by yahoo...

As I copied it it will retrieve data from 1970 (or the earliest
available) to the present, adjust the dates to to your needs....

...and in case I was unclear - your mileage may vary when run : )


I like that Postgresql idea too..


Jeff

On 9/20/07, Jeff Ryan <jeff.a.ryan at gmail.com> wrote:
> A bit of python that should facilitate the yahoo to MySQL:
>
> Should work - though you'll need a database set up, and the
> appropriate password/host/etc.
>
> At least will get you started in the right direction.
>
> Basically calls a file 'symbolsFile' in the same directory,
> which is just a text file with one yahoo symbol per line.
>
> db has columns:
> date,o,h,l,c,v,a with the appropriate MySQL types of course.
>
>
> Python requires proper indentation, so copy paste may not be perfect.
> And it requires (this script) the package MySQLdb... A word of warning
> - it has been a bit since I've used this, so double check your results
> at the very least.
>
>
> #!/usr/bin/env python2.5
>
> import urllib
> import re
> import MySQLdb
> import string
> import time
>
> sym = open("symbolsFile").readlines()
> regx = re.compile('(.+?)\\n')
>
> today = time.localtime()
> yr = time.strftime('%Y',today)
> mon = time.strftime('%m',today)
> day = time.strftime('%d',today)
>
> db = MySQLdb.connect(user='username',passwd='password',db='databasename', \
>                      host='localhost', unix_socket='/path/to/socket')
> cursor = db.cursor()
>
> for s in sym:
>     symbol = regx.findall(s)[0]
>     yahoourl = "http://ichart.finance.yahoo.com/table.csv?s="+symbol + \
>                    "&a=00&b="+day+"&c="+"1970"+"&d="+mon+"&e="+day+ \
>                    "&f="+yr+"&g=d&ignore=.csv"
>     data = urllib.urlopen(yahoourl).readlines()
>     sql = "INSERT IGNORE INTO "+string.strip(symbol,'^')+"
> (date,o,h,l,c,v,a)" + \
>           " VALUES(%s,%s,%s,%s,%s,%s,%s)"
>     mult=[]
>     for d in data[1:]:
>         dd = string.split(d,',')
>         dd[6] = string.strip(dd[6],'\n')
>         string.join(dd,',')
>         mult[len(mult):] = [dd]
>     cursor.executemany(sql,mult)
>
> And because I like to plug my R software (feedback is my goal...), check out
> www.quantmod.com or the quantmod package on CRAN.  Some nice wrappers to
> data handling and modellig stuff.  Still a work in progress, but it IS
> in progress...
>
> Jeff Ryan
>
>
> On 9/20/07, Dry Eraser <dryeraser at yahoo.com> wrote:
> > I'm doing a small project to identity particular stock movements out of the universe of stock quote histories from Yahoo.  Basically, I would be iterating a screen over several thousand time series.
> >
> > I could save and use several thousand CSV files saved by get.hist.quote, but storing the data in a database like MySQL seems to be a better idea, especially since more stock prices would be added in the future.
> >
> > I googled r lists for this subject, read r manuals, and skimmed the TOC of one or two Times Series using R books and didn't turn up much in the way of concrete application of MySQL.  The best I found was a hint by Bollinger on this list pointing to a number of packages (including python-related).
> >
> > Any suggestions on where to look and ways of approaching my project?
> >
> > Thanks,
> > Ming
> >
> >
> >       ____________________________________________________________________________________
> >
> > , and more!
> >
> >         [[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.
> >
>