get.hist.quote and mysql

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