def add_all_to_db():
"""Get a list of all symbols in the database, and fetch new data."""
# Connect to the database
db = DB(FILE)
# Get symbols from the ticker table
query = db.sql_query('SELECT Distinct symbol from %s;' % db.TABLE_SYM)
symbols = []
for row in query:
symbols.append(row[0])
# store all errors encountered to pass back up the chain
errors = []
for sym in symbols:
try:
# today's date:
dt = datetime.date.today()
date = str(dt.year) + '-' + str(dt.month) + '-' + str(dt.day)
# Check to see if the data is already there
query = db.sql_query('SELECT date from %s WHERE date=? AND symbol=?;' % db.TABLE, (date,sym,))
if len(query.fetchall()) == 0:
#print 'does not exist!'
add_data_db(sym, db)
db.db.commit()
except Exception as e:
errors.append(e)
return errors
def load_exchange(fname, delim='\t', exchange='', clear=False, header=True):
"""Load exchange data (tickers and info about them) from a CSV file."""
with open(fname, 'rU') as csvfile:
# Open a connection to the DB:
db = DB(FILE)
header_text = ''
data = []
# Read in everything from the CSV file:
reader = csv.reader(csvfile, delimiter=delim, quotechar='|')
for row in reader:
if header and header_text=='':
header_text = row
else:
data.append(row)
# If requested, delete everything from the current database
if clear:
db.sql_query('''DELETE from %s WHERE exchange=?;''' % db.TABLE_SYM, (exchange,))
# Iterate over data and insert into the database
for row in data:
try:
# fetch data
sym = row[0]
name = row[1]
ipo = row[5]
if ipo == 'n/a':
ipo = ''
sector = row[6]
industry = row[7]
# insert into db
db.sql_query('INSERT INTO %s VALUES (?,?,?,?,?,?)' % db.TABLE_SYM,
(sym,name,exchange,ipo,sector,industry,))
except:
print(('trouble with row: ', row))
def load_old_csv(fname, sym=None):
"""Import data from an old CSV file, and add it to the database."""
# If no symbol is supplied, get it from the filename:
if sym is None:
temp = os.path.split(fname)[-1]
temp = temp.split('.')[0]
sym = temp
# Connect to the database
db = DB(FILE)
with open(fname, 'rU') as csvfile:
# Read in the data and parse so that each element in raw
# corresponds to one dataset
raw = []
temp = []
reader = csv.reader(csvfile, delimiter=',', quotechar='|')
for row in reader:
if row[1] == 'Date':
if len(temp) > 0:
raw.append(temp)
temp = [row]
else:
temp.append(row)
# Now we need to convert to data that can be inserted into the database
all_data = []
for row in raw:
# Convert into a dict, which is convenient for the next step
date = row[0][3].replace('[','').replace(']','').replace(', ','-')
raw2 = {'Date': date}
for x in row[1:]:
# ugly but needed to fix a previous mistake:
if x[1] == 'Divident Pay Date':
raw2['Dividend Pay Date'] = x[3]
else:
raw2[x[1]] = x[3]
all_data.append(raw2)
# Now put it into the database:
for data in all_data:
# Check to see if the data is already there
query = db.sql_query('SELECT date from %s WHERE date=? AND symbol=?;' % db.TABLE, (date,sym,))
if len(query.fetchall()) == 0:
# Build data:
tbd = (data['Date'],
sym,
data['Open'],
data['Low'],
data['High'],
data['Target'],
data['52 week high'],
data['52 week low'],
data['Volume'],
data['EPS'],
data['EPS Estimate (current year)'],
data['EPS Estimate (next year)'],
data['Book Value'],
data['EBITDA'],
data['Price/Sales'],
data['Price/Book'],
data['P/E'],
data['PEG'],
data['P/EPS Estimate (current year)'],
data['P/EPS Estimate (next year)'],
data['Short Ratio'],
data['Dividend Yield'],
data['Dividend'],
data['Dividend Pay Date'],
data['Ex-Dividend Date'],
data['50-day moving average'],
data['200-day moving average'],
data['Market Cap'],)
db.sql_query('INSERT INTO %s VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' % db.TABLE, tbd)
db.db.commit()
