def execute(self):
self.cursor = db.cursor()
self.cursor.execute(self.query, self.params)
#print "######## DEBUG ########\n"
#print self.query % self.params
#print "\n######## DEBUG ########"
return self
def execute(self): self.cursor = db.cursor() self.cursor.execute(self.query, self.params) #print "######## DEBUG ########\n" #print self.query % self.params #print "\n######## DEBUG ########" return self
def prices(symbol):
"""
Loads the prices from the start date for the given symbol
Only new quotes are downloaded.
"""
to = date.today().strftime("%Y%m%d")
c = db.cursor()
c.execute("SELECT DATE_ADD(max(date), INTERVAL 1 DAY) FROM quote where symbol = %s",
(symbol))
(_from, ) = c.fetchone()
if _from == date.today():
print "Skipping %s" % symbol
return
print "Downloading %s" % symbol
if _from is None:
_from = start_date
else:
_from = _from.strftime("%Y%m%d")
prices = ystockquote.get_historical_prices(symbol, _from, to)
headers = prices[0]
try:
close = get_idx(headers, 'Close')
date_ = get_idx(headers, 'Date')
open = get_idx(headers, 'Open')
high = get_idx(headers, 'High')
low = get_idx(headers, 'Low')
quotes = prices[1:]
for l in quotes:
#print "%s %s" % (l[date_], l[close])
insert(symbol, l[date_], l[close], l[high], l[low], l[open])
print "Inserted %s new quotes for %s" % (len(quotes), symbol)
except:
print "Could not download %s" % symbol
def prices(symbol):
"""
Loads the prices from the start date for the given symbol
Only new quotes are downloaded.
"""
to = date.today().strftime("%Y%m%d")
c = db.cursor()
c.execute("SELECT DATE_ADD(max(date), INTERVAL 1 DAY) FROM quote where symbol = %s",
(symbol))
(_from, ) = c.fetchone()
if _from == date.today():
print "Skipping %s" % symbol
return
print "Downloading %s" % symbol
if _from is None:
_from = start_date
else:
_from = _from.strftime("%Y%m%d")
prices = stockquote.get_historical_prices(symbol, _from, to)
headers = prices[0]
try:
close = get_idx(headers, 'Close')
date_ = get_idx(headers, 'Date')
open = get_idx(headers, 'Open')
high = get_idx(headers, 'High')
low = get_idx(headers, 'Low')
quotes = prices[1:]
for l in quotes:
#print "%s %s" % (l[date_], l[close])
try:
insert(symbol, l[date_], l[close], l[high], l[low], l[open])
except Exception, e:
print "Could not insert %s:%s" % (symbol, e)
print "Inserted %s new quotes for %s" % (len(quotes), symbol)
def build_averages(symbol, date):
c = db.cursor()
c.execute(
"SELECT avg(close) from (select close as close from quote where date <= %s and symbol =%s order by date desc limit %s) as tbl",
(date, symbol, 20))
(sma_20, ) = c.fetchone()
c.execute(
"SELECT avg(close) from (select close as close from quote where date <= %s and symbol =%s order by date desc limit %s) as tbl",
(date, symbol, 50))
(sma_50, ) = c.fetchone()
c.execute(
"SELECT avg(tr) from (select tr from quote where date <= %s and symbol =%s order by date desc limit %s) as tbl",
(date, symbol, 14))
(atr_14, ) = c.fetchone()
c.execute("SELECT tr FROM quote WHERE date = %s and symbol =%s",
(date, symbol))
(todays_tr, ) = c.fetchone()
# previous day atr
c.execute(
"SELECT atr_exp20 FROM indicator WHERE date < %s and symbol =%s order by date desc limit 1",
(date, symbol))
result = c.fetchone()
if result:
(prev_day_atr, ) = result
else:
# use the simple moving avg when no data is present
prev_day_atr = todays_tr
days = 14
atr_exp20 = ((days - 1) * prev_day_atr + todays_tr) / days
c.execute(
"SELECT min(low) from ("
"select low from quote where date < %s"
" and symbol = %s order by date desc limit %s) as tbl",
(date, symbol, 10))
(ll_10, ) = c.fetchone()
c.execute(
"SELECT max(high) from ("
"select high from quote where date < %s"
" and symbol = %s order by date desc limit %s) as tbl",
(date, symbol, 20))
(hh_20, ) = c.fetchone()
c.execute(
"SELECT max(high) from ("
"select high from quote where date < %s"
" and symbol = %s order by date desc limit %s) as tbl",
(date, symbol, 50))
(hh_50, ) = c.fetchone()
c.execute(
"INSERT INTO indicator "
"(date, symbol, sma_20, sma_50, atr_14, atr_exp20, ll_10, hh_20, hh_50)"
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
(date, symbol, sma_20, sma_50, atr_14, atr_exp20, ll_10, hh_20, hh_50))
def build_indicators():
c = db.cursor()
c.execute("select q.symbol, q.date from quote q left outer join indicator i on (q.date=i.date AND q.symbol=i.symbol) where i.symbol is null")
missing_indicators = c.fetchall()
print "Building tr"
for (symbol, date) in missing_indicators:
build_tr(symbol, date)
print "Building averages"
for (symbol, date) in missing_indicators:
build_averages(symbol, date)
print "Built %s missing indicators" % len(missing_indicators)
def save(object):
table = object.__class__.__name__
keys = object.__cols__
cols = ", ".join(keys)
values = []
value_places = []
for k in keys:
value_places.append("%s")
values.append(object.__dict__.get(k))
insert = "INSERT INTO %s (%s) values (%s)" % (table, cols, ", ".join(value_places))
cursor = db.cursor()
cursor.execute(insert, values)
def save(object):
table = object.__class__.__name__
keys = object.__cols__
cols = ", ".join(keys)
values = []
value_places = []
for k in keys:
value_places.append("%s")
values.append(object.__dict__.get(k))
insert = "INSERT INTO %s (%s) values (%s)" % (table, cols,
", ".join(value_places))
cursor = db.cursor()
cursor.execute(insert, values)
def build_indicators():
c = db.cursor()
c.execute(
"select q.symbol, q.date from quote q left outer join indicator i on (q.date=i.date AND q.symbol=i.symbol) where i.symbol is null"
)
missing_indicators = c.fetchall()
print "Building tr"
for (symbol, date) in missing_indicators:
build_tr(symbol, date)
print "Building averages"
for (symbol, date) in missing_indicators:
build_averages(symbol, date)
print "Built %s missing indicators" % len(missing_indicators)
def build_averages(symbol, date):
c = db.cursor()
c.execute("SELECT avg(close) from (select close as close from quote where date <= %s and symbol =%s order by date desc limit %s) as tbl", (date, symbol, 20))
(sma_20, ) = c.fetchone()
c.execute("SELECT avg(close) from (select close as close from quote where date <= %s and symbol =%s order by date desc limit %s) as tbl", (date, symbol, 50))
(sma_50, ) = c.fetchone()
c.execute("SELECT avg(tr) from (select tr from quote where date <= %s and symbol =%s order by date desc limit %s) as tbl", (date, symbol, 14))
(atr_14, ) = c.fetchone()
c.execute("SELECT tr FROM quote WHERE date = %s and symbol =%s", (date, symbol))
(todays_tr, ) = c.fetchone()
# previous day atr
c.execute("SELECT atr_exp20 FROM indicator WHERE date < %s and symbol =%s order by date desc limit 1", (date, symbol))
result = c.fetchone()
if result:
(prev_day_atr, ) = result
else:
# use the simple moving avg when no data is present
prev_day_atr = todays_tr
days = 14
atr_exp20 = ((days - 1) * prev_day_atr + todays_tr)/days
c.execute("SELECT min(low) from ("
"select low from quote where date < %s"
" and symbol = %s order by date desc limit %s) as tbl",
(date, symbol, 10))
(ll_10, ) = c.fetchone()
c.execute("SELECT max(high) from ("
"select high from quote where date < %s"
" and symbol = %s order by date desc limit %s) as tbl",
(date, symbol, 20))
(hh_20, ) = c.fetchone()
c.execute("SELECT max(high) from ("
"select high from quote where date < %s"
" and symbol = %s order by date desc limit %s) as tbl",
(date, symbol, 50))
(hh_50, ) = c.fetchone()
c.execute("INSERT INTO indicator "
"(date, symbol, sma_20, sma_50, atr_14, atr_exp20, ll_10, hh_20, hh_50)"
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
(date, symbol, sma_20, sma_50, atr_14, atr_exp20, ll_10, hh_20, hh_50))
from _mysql_exceptions import IntegrityError
import indicators
from connection import db
def prices(symbol):
"""
Downloads and stores the latest quote for the given symbol
"""
all = ystockquote.get_all(symbol)
try:
download.insert(
symbol,
datetime.strptime(all['date'], '"%m/%d/%Y"').strftime('%Y-%m-%d'),
all['price'], all['high'], all['low'], all['open'])
except IntegrityError:
print "Quote for %s already stored" % symbol
except ValueError:
print "Quote for %s could not be downloaded" % symbol
if __name__ == '__main__':
c = db.cursor()
c.execute("SELECT distinct symbol FROM position")
rows = c.fetchall()
for (symbol, ) in rows:
prices(symbol)
indicators.build_indicators()
def insert(symbol, date, close, high, low, open):
c = db.cursor()
c.execute("INSERT INTO quote (date, symbol, close, high, low, open) VALUES (%s, %s, %s, %s, %s, %s)",
(date, symbol, close, high, low, open))
from _mysql_exceptions import IntegrityError
import indicators
from connection import db
def prices(symbol):
"""
Downloads and stores the latest quote for the given symbol
"""
all = ystockquote.get_all(symbol)
try:
download.insert(symbol,
datetime.strptime(all['date'], '"%m/%d/%Y"').strftime('%Y-%m-%d'),
all['price'],
all['high'],
all['low'],
all['open'])
except IntegrityError:
print "Quote for %s already stored" % symbol
except ValueError:
print "Quote for %s could not be downloaded" % symbol
if __name__ == '__main__':
c = db.cursor()
c.execute("SELECT distinct symbol FROM position")
rows = c.fetchall()
for (symbol,) in rows:
prices(symbol)
indicators.build_indicators()
def insert(symbol, date, close, high, low, open):
c = db.cursor()
c.execute("INSERT INTO quote (date, symbol, close, high, low, open) VALUES (%s, %s, %s, %s, %s, %s)",
(date, symbol, close, high, low, open))
