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 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()