def insert_stock(dbfile, ticker, datetime, do_srv):
conn = sqlite3.connect(dbfile, 5)
cursor = conn.cursor()
cursor.execute("SELECT * FROM stocks_static_data WHERE ticker=\""+ticker+"\"")
if cursor.fetchone() == None:
cursor.execute("INSERT INTO stocks_static_data VALUES(\""+ticker+"\",NULL)")
print "inserted ticker "+ticker
conn.commit()
cursor.close()
conn.close()
if do_srv == True:
c = core.connection()
c.add_ticker(ticker)
def find_dates(dbfile, tickers, fromdate, todate, wait):
if fromdate != "":
fromtime = time.mktime(time.strptime(fromdate, "%Y-%m-%d %H:%M:%S"))
if todate != "":
totime = time.mktime(time.strptime(todate, "%Y-%m-%d %H:%M:%S"))
connection = core.connection()
sql_tickers = string.join(tickers, "\",\"")
conn = sqlite3.connect(dbfile)
c = conn.cursor()
d = conn.cursor()
c.execute(
"select ticker_id, ticker from stocks_static_data where ticker in (\""
+ sql_tickers + "\")")
prevtime = 0
for ticker_id in c:
#should check if it already exists using get_ticker
srv_id = connection.add_ticker(str(ticker_id[1]))
srv_id_opt = connection.add_ticker(str(ticker_id[1]) + "_options")
if (fromdate == "") or (todate == ""):
d.execute("select date, data from stocks_data where ticker_id=" +
str(ticker_id[0]) + " ORDER BY date")
else:
d.execute("select date, data from stocks_data where ticker_id=" +
str(ticker_id[0]) + " and (date > " + str(fromtime) +
" AND date < " + str(totime) + ")")
for r in d:
rowdate = str(r[0])
rowdata = str(r[1])
rowtime = float(r[0])
if prevtime == 0:
prevtime = rowtime
connection.push_ticker(srv_id, rowdata)
vcursor = conn.cursor()
vcursor.execute("select data from options_data where ticker_id=" +
str(ticker_id[0]) + " and date=" + rowdate)
for row in vcursor:
connection.push_ticker(srv_id_opt, str(row[0]))
#TODO make this better: take exec time into consideration
time.sleep((rowtime - prevtime) / wait)
prevtime = rowtime
c.close()
def insert_options(dbfile, ticker, datetime, do_srv):
conn = sqlite3.connect(dbfile, 5)
cursor = conn.cursor()
cursor.execute("SELECT ticker_id FROM stocks_static_data WHERE ticker=\""+ticker+"\"")
tickerid = cursor.fetchone()[0]
f = urllib.urlopen("http://finance.yahoo.com/q/op?s="+ticker+"+Options")
s = f.read()
optionparser = OptionTickerParser(ticker)
optionparser.feed(s)
tickers = optionparser.tickers
tenors = optionparser.tenors
optionparser.close()
for t in tenors:
f = urllib.urlopen("http://finance.yahoo.com/q/op?s="+ticker+"&m="+t)
s = f.read()
optionparser.feed(s)
tickers.extend(optionparser.tickers)
optionparser.close()
n = 0
for t in tickers:
if len(t) == 15:
date = t[:6]
datet = time.mktime(time.strptime(date,"%y%m%d"))
putcall = t[6]
strike = float(t[7:])/1000
cursor.execute("INSERT INTO options_static_data VALUES("+str(tickerid)+","+str(datet)+","+str(strike)+",\""+putcall+"\")")
n = n + 1
print "inserted "+str(n)+" options over "+str(len(tenors))+" tenors for ticker "+ticker
conn.commit()
cursor.close()
conn.close()
if do_srv == True:
c = core.connection()
c.add_ticker(ticker+"_options")
def find_dates(dbfile, tickers, fromdate, todate, wait):
if fromdate != "":
fromtime = time.mktime(time.strptime(fromdate, "%Y-%m-%d %H:%M:%S"))
if todate != "":
totime = time.mktime(time.strptime(todate, "%Y-%m-%d %H:%M:%S"))
connection = core.connection()
sql_tickers = string.join(tickers, "\",\"")
conn = sqlite3.connect(dbfile)
c = conn.cursor()
d = conn.cursor()
c.execute("select ticker_id, ticker from stocks_static_data where ticker in (\""+sql_tickers+"\")")
prevtime = 0
for ticker_id in c:
#should check if it already exists using get_ticker
srv_id = connection.add_ticker(str(ticker_id[1]))
srv_id_opt = connection.add_ticker(str(ticker_id[1])+"_options")
if (fromdate == "") or (todate == ""):
d.execute("select date, data from stocks_data where ticker_id="+str(ticker_id[0])+" ORDER BY date")
else:
d.execute("select date, data from stocks_data where ticker_id="+str(ticker_id[0])+" and (date > "+str(fromtime)+" AND date < "+str(totime)+")")
for r in d:
rowdate = str(r[0])
rowdata = str(r[1])
rowtime = float(r[0])
if prevtime == 0:
prevtime = rowtime
connection.push_ticker(srv_id, rowdata)
vcursor = conn.cursor()
vcursor.execute("select data from options_data where ticker_id="+str(ticker_id[0])+" and date="+rowdate)
for row in vcursor:
connection.push_ticker(srv_id_opt, str(row[0]))
#TODO make this better: take exec time into consideration
time.sleep((rowtime-prevtime)/wait)
prevtime = rowtime
c.close()
def main():
tickers = ""
tags = ""
keys = list()
c = -1
c_id = -1
c_oid = -1
(tickers, tags, keys, dbfile, do_srv) = parse_command_args()
data = get_data(tickers, tags)
if do_srv == True:
c = core.connection()
for k, i in data.iteritems():
try:
values = data_to_dict(i, keys)
date = time.time()
id = get_ticker_id(dbfile, k)
save_price_to_db(values, dbfile, k, date, id)
if do_srv == True:
c_id = c.get_ticker(k)
push_data_to_data_srv(values, c, c_id)
print "fetched ticker "+k
option_tickers = get_options(dbfile, k)
oi = 0
option_data = get_data_html2(k)
save_option_to_db(option_data, dbfile, id, date)
if do_srv == True:
co_id = c.get_ticker(k+"_options")
print str(co_id)
push_data_to_data_srv(option_data, c, co_id)
print "fetched "+str(len(option_tickers))+" options for ticker "+k
except sqlite3.OperationalError as exp:
print "### SQL Exception caught: "+str(exp)
sys.stderr.write("SQL Exception caught for ticker "+k+"\n")
raise
