def setUp(self):
test_stock = ["sym", "name"]
self.test_stock_list_table = "test_table"
self.fe_stock_add_to_list = FE_Stock_List()
self.fe_stock_add_to_list.init(self.test_stock_list_table)
self.fe_stock_add_to_list.add_stock(test_stock[0], test_stock[1])
self.fe_stock_add_to_list.close()
self.fe_stock = FE_Stock(test_stock[0], self.test_stock_list_table)
self.stock_db = "sym.db"
self.db_name = os.path.join(database_folder, self.stock_db)
self.db = sqlite3.connect(self.db_name)
self.stock_table_name = "data"
self.cursor = self.db.cursor()
def run(self):
list_of_company = self.get_company_list()
fe_stock_list = FE_Stock_List()
print("Initiated %s" % self.stock_list_db)
fe_stock_list.init(table_name)
for sym, name in zip(list_of_company.Symbol, list_of_company.Name):
print("Adding %s to list" % sym)
try:
fe_stock_list.add_stock(sym, name)
except:
print("%s Exists" % sym)
list_of_stock = fe_stock_list.list_of_stocks()
fe_stock_list.close()
fe_quandl = FE_Quandl(filename=quandl_api_key_file)
for k in list_of_stock:
print(k[0], table_name)
fe_stock = FE_Stock(k[0], table_name)
fe_stock.init()
data = fe_quandl.get(k[0])
for ind in data.index:
stock_row = {"date": str(ind).split(" ")[0]}
stock_row["open"] = data.loc[ind]['Open']
stock_row["high"] = data.loc[ind]['High']
stock_row["low"] = data.loc[ind]['Low']
stock_row["close"] = data.loc[ind]['Close']
stock_row["volume"] = data.loc[ind]['Volume']
try:
fe_stock.add_stock_row(stock_row)
except:
print("Date %s exists" % stock_row["date"])
fe_stock.close()
print("Added price data for %s to database" % k[0])
def run(self):
list_of_company = self.get_company_list()
fe_stock_list = FE_Stock_List()
print("Initiated %s" % self.stock_list_db)
fe_stock_list.init(table_name)
for sym, name in zip(list_of_company.Symbol, list_of_company.Name):
print("Adding %s to list" % sym)
try:
fe_stock_list.add_stock(sym, name)
except:
print("%s Exists" % sym)
list_of_stock = fe_stock_list.list_of_stocks()
fe_stock_list.close()
fe_quandl = FE_Quandl(quandl_api_key_file)
for stock in list_of_stock:
print("Updating %s", stock[0])
stock_sym = stock[0]
fe_stock = FE_Stock(stock_sym, table_name)
fe_stock.init()
last_date = fe_stock.get_last_date()
if is_update_required(last_date):
if not len(last_date):
print(last_date)
print("No data availabel for %s", stock_sym)
try:
data = fe_quandl.get(stock_sym)
except:
print("%s Not found; deleteing from list", stock_sym)
fe_stock_list = FE_Stock_List()
fe_stock_list.init(table_name)
fe_stock_list.delete_stock(stock_sym)
fe_stock_list.close()
else:
try:
data = fe_quandl.filter(stock_sym, last_date)
except:
print("%s Not found; deleteing from list", stock_sym)
fe_stock_list = FE_Stock_List()
fe_stock_list.init(table_name)
fe_stock_list.delete_stock(stock_sym)
fe_stock_list.close()
for ind in data.index:
stock_row = {"date": str(ind).split(" ")[0]}
stock_row["open"] = data.loc[ind]['Open']
stock_row["high"] = data.loc[ind]['High']
stock_row["low"] = data.loc[ind]['Low']
stock_row["close"] = data.loc[ind]['Close']
stock_row["volume"] = data.loc[ind]['Volume']
try:
fe_stock.add_stock_row(stock_row)
print("Added %s for %s" %
(stock_row["date"], stock_sym))
except:
print("Date %s exists for %s" %
(stock_row["date"], stock_sym))
fe_stock.close()
else:
print("%s is already Up to date, last date: %s" %
(stock_sym, last_date))
if self.c:
print("re_update flag set to True")
data = fe_quandl.filter(stock_sym, last_date)
for ind in data.index:
stock_row = {"date": str(ind).split(" ")[0]}
stock_row["open"] = data.loc[ind]['Open']
stock_row["high"] = data.loc[ind]['High']
stock_row["low"] = data.loc[ind]['Low']
stock_row["close"] = data.loc[ind]['Close']
stock_row["volume"] = data.loc[ind]['Volume']
try:
print("Deleting row %s" % stock_row["date"])
fe_stock.delete_stock_row(stock_row["date"])
fe_stock.add_stock_row(stock_row)
print("Added %s for %s" %
(stock_row["date"], stock_sym))
except:
print("Date %s exists for %s" %
(stock_row["date"], stock_sym))
def get_weekly_values_company(self, company_sym, columns="high"):
feStock = FE_Stock(company_sym, table_name)
all_items = feStock.fetch_all(columns)
dates = [k[0] for k in all_items]
values = [k[1] for k in all_items]
date_sorted_by_week, values_sorted_by_week = [], []
temp_d, temp_v = [], []
week_limits = []
start_date_db = datetime.datetime.strptime(dates[0], '%Y-%m-%d')
last_date_db = datetime.datetime.strptime(dates[-1], '%Y-%m-%d')
temp_date = start_date_db
week_limits_list = []
date_sorted_by_week, values_sorted_by_week = [], []
k = 0
delta = 0
flag = 0
while temp_date < last_date_db:
temp_date = start_date_db + timedelta(days=delta)
k_date = datetime.datetime.strptime(dates[k], '%Y-%m-%d')
if temp_date.weekday() == 0:
week_limits = [temp_date, temp_date + timedelta(days=5)]
flag = 1
if temp_d:
date_sorted_by_week += [temp_d]
values_sorted_by_week += [temp_v]
temp_d = [k_date]
temp_v = [values[k]]
week_limits = []
else:
if not week_limits:
if flag:
i = 0
while (k_date - timedelta(days=i)).weekday() != 0:
i += 1
week_limits = [
k_date - timedelta(i),
k_date - timedelta(i) + timedelta(days=5)
]
continue
if not temp_date == k_date:
delta += 1
continue
else:
if week_limits:
if k_date >= week_limits[0] and k_date <= week_limits[1]:
# print(k_date, week_limits)
temp_d += [k_date]
temp_v += [values[k]]
k += 1
delta += 1
else:
if temp_d and temp_v:
date_sorted_by_week += [temp_d]
values_sorted_by_week += [temp_v]
return values_sorted_by_week, date_sorted_by_week
def get_values_company_by_date(self, company_sym, date, columns="close"):
feStock = FE_Stock(company_sym, table_name)
all_items = feStock.fetch_by_date(date, columns)
return [float(k[1]) for k in all_items], [k[0] for k in all_items]
class TestFEStocks(unittest.TestCase):
def setUp(self):
test_stock = ["sym", "name"]
self.test_stock_list_table = "test_table"
self.fe_stock_add_to_list = FE_Stock_List()
self.fe_stock_add_to_list.init(self.test_stock_list_table)
self.fe_stock_add_to_list.add_stock(test_stock[0], test_stock[1])
self.fe_stock_add_to_list.close()
self.fe_stock = FE_Stock(test_stock[0], self.test_stock_list_table)
self.stock_db = "sym.db"
self.db_name = os.path.join(database_folder, self.stock_db)
self.db = sqlite3.connect(self.db_name)
self.stock_table_name = "data"
self.cursor = self.db.cursor()
def test_stock_return_correct_table_name(self):
self.assertTrue(self.stock_db == self.fe_stock.get_db())
def test_stock_init_creates_db_if_doesnot_exists(self):
self.cursor.execute(
"SELECT name FROM sqlite_master WHERE type='table';")
self.assertFalse(self.cursor.fetchone())
self.fe_stock.init()
self.fe_stock.close()
self.cursor.execute(
"SELECT name FROM sqlite_master WHERE type='table';")
res = self.cursor.fetchall()
self.assertTrue(res)
table_exists = False
for k in res:
if self.stock_table_name in k:
table_exists = True
self.assertTrue(table_exists)
def test_stock_init_creates_db_with_columns(self):
rows_in_db = ["date", "open", "high", "low", "close", "volume"]
self.fe_stock.init()
self.fe_stock.close()
self.cursor.execute("PRAGMA table_info('%s')" % self.stock_table_name)
res = self.cursor.fetchall()
columns = [k[1] for k in res]
for k in rows_in_db:
self.assertTrue(k in columns)
def test_stock_add_row(self):
stock_row = {
"date": "2019-3-30",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
self.fe_stock.init()
self.fe_stock.add_stock_row(stock_row)
self.fe_stock.close()
self.cursor.execute("SELECT * from %s Where date=\'%s\'" %
(self.stock_table_name, stock_row["date"]))
result = self.cursor.fetchone()
self.assertTrue(
list(result) == list([
stock_row["date"], stock_row["open"], stock_row["high"],
stock_row["low"], stock_row["close"], stock_row["volume"]
]))
def test_stock_add_row_fail_same_name(self):
stock_row = {
"date": "2019-3-30",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
self.fe_stock.init()
self.fe_stock.add_stock_row(stock_row)
thrown = False
try:
self.fe_stock.add_stock_row(stock_row)
except:
thrown = True
self.assertTrue(thrown)
self.fe_stock.close()
def test_stock_fetch_row_gets_correct_row_count(self):
stock_row = {
"date": "2019-3-30",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
self.fe_stock.init()
self.fe_stock.add_stock_row(stock_row)
result = self.fe_stock.fetch_latest(1)
self.assertTrue(len(result) == 1)
self.fe_stock.close()
def test_stock_fetch_row_gets_correct_row_count_of_2(self):
stock_row1 = {
"date": "2019-3-30",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
stock_row2 = {
"date": "2019-4-01",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
self.fe_stock.init()
self.fe_stock.add_stock_row(stock_row1)
self.fe_stock.add_stock_row(stock_row2)
result = self.fe_stock.fetch_latest(2)
self.assertTrue(len(result) == 2)
self.fe_stock.close()
def test_stock_fetch_last_day_gets_correct_date(self):
stock_row1 = {
"date": "2019-3-30",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
stock_row2 = {
"date": "2019-4-01",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
self.fe_stock.init()
self.fe_stock.add_stock_row(stock_row1)
self.fe_stock.add_stock_row(stock_row2)
result = self.fe_stock.get_last_date()
self.assertTrue(result == stock_row2["date"])
self.fe_stock.close()
def test_delete_row_deletes_row(self):
stock_row1 = {
"date": "2019-3-30",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
stock_row2 = {
"date": "2019-4-01",
"open": 30.5,
"high": 60.5,
"low": -10.5,
"close": 30.5,
"volume": 30000
}
self.fe_stock.init()
self.fe_stock.add_stock_row(stock_row1)
self.fe_stock.add_stock_row(stock_row2)
self.fe_stock.delete_stock_row(stock_row1["date"])
self.fe_stock.close()
self.cursor.execute("SELECT * from %s Where date=\'%s\'" %
(self.stock_table_name, stock_row1["date"]))
result = self.cursor.fetchone()
self.assertTrue(result is None)
def tearDown(self):
# self.cursor.execute("DROP TABLE IF EXISTS %s" % (self.test_table_name))
self.db.commit()
self.db.close()
os.remove(self.db_name)
os.remove(os.path.join(database_folder, db_stock_list))