def download_hist_prices(tickers):
date_to = get_end_date()
conn = pql.connect(host=DB_HOST,
database=DB_NAME,
user=DB_USER,
password='******')
cur = conn.cursor()
for t in tickers:
# find the latest date for which a price exists
cur.execute(f"SELECT max(recorded_at) FROM prices WHERE ticker='{t}'")
# fetch functions always return tuples, hence the index [0]
date_from = cur.fetchone()[0]
if date_from is None:
date_from = date(2010, 1, 1)
else:
date_from = date_from + datetime.timedelta(days=1)
if (date_from <= date_to):
try:
reader = YahooDailyReader(t, date_from, date_to)
new_prices = reader.read()
except RemoteDataError:
print(f"{t}: remote error")
except KeyError:
# There is a bug in pandas-datareader that doesn't check if the returned dataframe is empty
# This is a workaround
print(f'{t}: no new prices found')
else:
new_prices.rename(columns={
'High': 'high',
'Low': 'low',
'Open': 'open',
'Close': 'close',
'Volume': 'volume',
'Adj Close': 'adj_close'
},
inplace=True)
new_prices.index.rename('recorded_at', inplace=True)
new_prices = new_prices.astype({'volume': 'int32'})
new_prices['ticker'] = t
new_prices[[
'ticker', 'high', 'low', 'open', 'close', 'adj_close',
'volume'
]].to_csv(TEMP_FILE, header=False, float_format='%g')
with open(TEMP_FILE) as temp_csv:
cur.copy_from(temp_csv, 'prices', sep=',')
conn.commit()
os.remove(TEMP_FILE)
print(
f'{t}: new prices added for period [{date_from}, {date_to}]'
)
else:
print(f'{t}: no new prices found')