def get_lowest_buy_price_quantity(symbol, conn=None):
'''
get quantity of the lowest buy price transaction
'''
if conn is None:
conn = get_default_db_connection()
session = conn.get_sessionmake()()
sell_count = session.query(StockTransaction).\
filter(StockTransaction.symbol == symbol).\
filter(StockTransaction.buy_or_sell ==
StockTransaction.SELL_FLAG).\
count()
if sell_count > 0:
session.close()
raise Exception("sell count > 0")
stock_transaction = session.query(StockTransaction).\
filter(StockTransaction.symbol == symbol).\
filter(StockTransaction.buy_or_sell ==
StockTransaction.BUY_FLAG).\
order_by(asc(StockTransaction.price)).\
first()
if stock_transaction is None:
result = 0
else:
result = stock_transaction.quantity
session.close()
return result
def split_transaction(conn=None):
'''
split_transaction
'''
if conn is None:
db_connection = get_default_db_connection()
else:
db_connection = conn
session = db_connection.create_session()
query = session.query(StockTransaction).\
filter(StockTransaction.quantity > 100)
transactions = query.all()
for transaction in transactions:
quantity = transaction.quantity
i = 100
while i <= quantity:
trans = StockTransaction()
trans.buy_or_sell = transaction.buy_or_sell
trans.date = transaction.date
trans.price = transaction.price
trans.symbol = transaction.symbol
trans.quantity = 100
session.add(trans)
print("ADD")
i = i + 100
session.delete(transaction)
session.commit()
break
session.close()
return
def complete_buy_transaction(symbol, price, quantity, conn=None):
'''
update DB after buy transaction
'''
if conn is None:
conn = get_default_db_connection()
session = conn.get_sessionmake()()
stock_cash = session.query(StockCash).\
filter(StockCash.symbol == symbol).\
one_or_none()
if stock_cash is None:
session.close()
raise Exception("Save buy transaction failed")
LOGGER.debug("Amount before: {0}".format(stock_cash.amount))
stock_cash.amount = stock_cash.amount - \
price * quantity - \
buy_fee(symbol, price, quantity)
LOGGER.debug("Amount after: {0}".format(stock_cash.amount))
stock_transaction = StockTransaction()
stock_transaction.symbol = symbol
stock_transaction.buy_or_sell = "Buy"
stock_transaction.quantity = quantity
stock_transaction.price = price
stock_transaction.date = datetime.utcnow()
session.add(stock_transaction)
session.commit()
session.close()
return
def __init__(self, conn = None):
if (conn is None):
conn = get_default_db_connection()
self.logger = logging.getLogger(__name__ +
".StockClosedTransactionTable")
self.conn = conn
return
def main():
'''
main module
'''
conn = get_default_db_connection()
session = conn.create_session()
stock_infos = session.query(StockInfo).all()
total = 0.0
for stock_info in stock_infos:
symbol = stock_info.symbol
total_amount = update_total_history(symbol, session)
if "美元" in symbol:
continue
else:
total += total_amount
all_investments_history = AllInvestmentsHistory(date=datetime.utcnow(),
total_value=total)
session.add(all_investments_history)
session.commit()
session.close()
return
def test_lowest_buy_price_quantity3(self):
'''
test lowest buy price quantity with sell transaction
'''
stock_db_connection = get_default_db_connection()
reset_table(stock_db_connection)
stock_transaction_table = StockTransactionTable(stock_db_connection)
# init transaction 1
stock_transaction_1 = StockTransaction()
stock_transaction_1.symbol = "601398"
stock_transaction_1.buy_or_sell = StockTransaction.BUY_FLAG
stock_transaction_1.date = date(2016, 5, 15)
stock_transaction_1.quantity = 200
stock_transaction_1.price = 4.51
stock_transaction_table.add_stock_transaction(stock_transaction_1)
stock_transaction_1.trans_id
# init transaction 2
stock_transaction_2 = StockTransaction()
stock_transaction_2.symbol = "601398"
stock_transaction_2.buy_or_sell = StockTransaction.SELL_FLAG
stock_transaction_2.date = date(2016, 5, 16)
stock_transaction_2.quantity = 100
stock_transaction_2.price = 4.81
stock_transaction_table.add_stock_transaction(stock_transaction_2)
stock_transaction_2.trans_id
with self.assertRaises(Exception):
StockTransaction.get_lowest_buy_price_quantity("601398")
return
def complete_buy_transaction(symbol, price, quantity, conn=None):
'''
update DB after buy transaction
'''
if conn is None:
conn = get_default_db_connection()
session = conn.get_sessionmake()()
total_amount = price * quantity
stock_cash = session.query(StockCash).\
filter(StockCash.symbol == symbol).\
one_or_none()
if stock_cash is None:
session.close()
raise Exception("Save buy transaction failed")
stock_cash.amount = stock_cash.amount - total_amount
stock_transaction = StockTransaction()
stock_transaction.symbol = symbol
stock_transaction.buy_or_sell = "Buy"
stock_transaction.quantity = quantity
stock_transaction.price = price
stock_transaction.date = datetime.utcnow()
session.add(stock_transaction)
session.commit()
session.close()
return
def test_lowest_buy_price_quantity(self):
'''
test lowest buy price quantity
'''
stock_db_connection = get_default_db_connection()
reset_table(stock_db_connection)
stock_transaction_table = StockTransactionTable(stock_db_connection)
# init transaction 1
stock_transaction_1 = StockTransaction()
stock_transaction_1.symbol = "601398"
stock_transaction_1.buy_or_sell = StockTransaction.BUY_FLAG
stock_transaction_1.date = date(2016, 5, 15)
stock_transaction_1.quantity = 200
stock_transaction_1.price = 4.9
stock_transaction_table.add_stock_transaction(stock_transaction_1)
stock_transaction_1.trans_id
# init transaction 2
stock_transaction_2 = StockTransaction()
stock_transaction_2.symbol = "601398"
stock_transaction_2.buy_or_sell = StockTransaction.BUY_FLAG
stock_transaction_2.date = date(2016, 5, 16)
stock_transaction_2.quantity = 100
stock_transaction_2.price = 4.81
stock_transaction_table.add_stock_transaction(stock_transaction_2)
stock_transaction_2.trans_id
quantity = StockTransaction.get_lowest_buy_price_quantity("601398")
self.assertEqual(quantity, 100)
return
def test_stock_transaction_sanity(self):
stock_db_connection = get_default_db_connection()
reset_table(stock_db_connection)
stock_transaction_table = StockTransactionTable(stock_db_connection)
stock_transaction = StockTransaction()
stock_transaction.set_symbol("601398")
stock_transaction.set_buy_or_sell("buy")
stock_transaction.set_quantity(100)
stock_transaction.set_price(4.51)
stock_transaction.set_date(date(2015, 11, 10))
stock_transaction_table.add_stock_transaction(stock_transaction)
stock_transaction = StockTransaction()
stock_transaction.set_symbol("601857")
stock_transaction.set_buy_or_sell("buy")
stock_transaction.set_quantity(100)
stock_transaction.set_price(4.51)
stock_transaction.set_date(date(2015, 11, 10))
stock_transaction_table.add_stock_transaction(stock_transaction)
stock_transaction = \
stock_transaction_table.get_stock_transaction_by_trans_id(1)
stock_transaction.set_quantity("500")
stock_transaction_table.update_stock_transaction(stock_transaction)
stock_transaction = \
stock_transaction_table.get_stock_transaction_by_trans_id(1)
stock_transaction_table.delete_stock_transaction(stock_transaction)
return
def update_keep_alive(app_name=None, conn=None):
if app_name is None:
return
if conn is None:
conn = get_default_db_connection()
session = conn.create_session()
trade_keep_alive = session.query(TradeKeepAlive).\
filter(TradeKeepAlive.app_name == app_name).\
one_or_none()
if trade_keep_alive is None:
LOGGER.debug("No entry in TradeKeepAlive table, create a new one.")
trade_keep_alive = TradeKeepAlive(app_name=app_name,
refresh_time=datetime.utcnow())
session.add(trade_keep_alive)
else:
LOGGER.debug("Existing entry found, refresh time")
trade_keep_alive.refresh_time = datetime.utcnow()
session.commit()
session.close()
return
def __init__(self):
self.link = \
"http://www.icbc.com.cn/ICBCDynamicSite/" + \
"Charts/GoldTendencyPicture.aspx"
self.driver = webdriver.Firefox()
self.table_id = "TABLE1"
self.conn = get_default_db_connection()
return
def complete_sell_transaction(symbol, price, quantity, conn=None):
'''
update DB after sell transaction
'''
if conn is None:
conn = get_default_db_connection()
session = conn.get_sessionmake()()
stock_cash = session.query(StockCash).\
filter(StockCash.symbol == symbol).\
one_or_none()
if stock_cash is None:
session.close()
raise Exception("Save sell transaction failed")
LOGGER.debug("Amount before: {0}".format(stock_cash.amount))
stock_cash.amount = stock_cash.amount + \
price * quantity - \
sell_fee(symbol, price, quantity)
LOGGER.debug("Amount after: {0}".format(stock_cash.amount))
# get lowest buy transaction
stock_transaction = session.query(StockTransaction).\
filter(StockTransaction.buy_or_sell ==
StockTransaction.BUY_FLAG).\
filter(StockTransaction.symbol == symbol).\
order_by(asc(StockTransaction.price)).\
first()
if stock_transaction is None:
session.close()
raise Exception("Cannot find lowest buy price transaction")
if stock_transaction.quantity < quantity:
session.close()
raise Exception("stock_transaction.quantity < quantity")
if stock_transaction.price > price:
session.close()
raise Exception("stock_tranaction price > sell_price")
stock_closed_transaction = StockClosedTransaction(
symbol=symbol,
quantity=quantity,
buy_price=stock_transaction.price,
buy_date=stock_transaction.date,
sell_price=price,
sell_date=datetime.utcnow())
session.add(stock_closed_transaction)
if stock_transaction.quantity == quantity:
session.delete(stock_transaction)
else:
stock_transaction.quantity = stock_transaction.quantity - quantity
session.commit()
session.close()
return
def complete_sell_transaction(symbol, price, quantity, conn=None):
'''
update DB after sell transaction
'''
if conn is None:
conn = get_default_db_connection()
session = conn.get_sessionmake()()
stock_cash = session.query(StockCash).\
filter(StockCash.symbol == symbol).\
one_or_none()
if stock_cash is None:
session.close()
raise Exception("Save sell transaction failed")
LOGGER.debug("Amount before: {0}".format(stock_cash.amount))
stock_cash.amount = stock_cash.amount + \
price * quantity - \
sell_fee(symbol, price, quantity)
LOGGER.debug("Amount after: {0}".format(stock_cash.amount))
# get lowest buy transaction
stock_transaction = session.query(StockTransaction).\
filter(StockTransaction.buy_or_sell ==
StockTransaction.BUY_FLAG).\
filter(StockTransaction.symbol == symbol).\
order_by(asc(StockTransaction.price)).\
first()
if stock_transaction is None:
session.close()
raise Exception("Cannot find lowest buy price transaction")
if stock_transaction.quantity < quantity:
session.close()
raise Exception("stock_transaction.quantity < quantity")
if stock_transaction.price > price:
session.close()
raise Exception("stock_tranaction price > sell_price")
stock_closed_transaction = StockClosedTransaction(
symbol=symbol,
quantity=quantity,
buy_price=stock_transaction.price,
buy_date=stock_transaction.date,
sell_price=price,
sell_date=datetime.utcnow())
session.add(stock_closed_transaction)
if stock_transaction.quantity == quantity:
session.delete(stock_transaction)
else:
stock_transaction.quantity = stock_transaction.quantity - quantity
session.commit()
session.close()
return
def stock_info_view(request):
conn = get_default_db_connection()
session = conn.create_session()
results = []
stock_infos = session.query(StockInfo).all()
for stock_info in stock_infos:
stock_info_dict = {"symbol": stock_info.symbol,
"name": stock_info.name}
results.append(stock_info_dict)
session.close()
return JsonResponse({"results": results})
def test_reset_table(self):
# test
logging.info("new StockDbConnection")
stock_db_connection = get_default_db_connection()
logging.info("reset table")
reset_table(stock_db_connection)
stock_info_table = StockInfoTable(stock_db_connection)
stock_info_list = stock_info_table.get_all_stock_info()
self.assertEqual(len(stock_info_list), 7)
return
def test_lowest_buy_price2(self):
'''
test lowest buy price
'''
stock_db_connection = get_default_db_connection()
reset_table(stock_db_connection)
StockTransactionTable(stock_db_connection)
lowest_price = StockTransaction.get_lowest_buy_price("601398")
self.assertEqual(lowest_price, 9999.00)
return
def test_close_stock_transaction(self):
'''
test_close_stock_transaction
'''
stock_db_connection = get_default_db_connection()
reset_table(stock_db_connection)
stock_transaction_table = StockTransactionTable(stock_db_connection)
# init transaction 1
stock_transaction_1 = StockTransaction()
stock_transaction_1.symbol = "601398"
stock_transaction_1.buy_or_sell = StockTransaction.BUY_FLAG
stock_transaction_1.date = datetime(2016, 5, 15, 0, 0, 0)
stock_transaction_1.quantity = 200
stock_transaction_1.price = 4.51
stock_transaction_table.add_stock_transaction(stock_transaction_1)
trans_id_1 = stock_transaction_1.trans_id
# init transaction 2
stock_transaction_2 = StockTransaction()
stock_transaction_2.symbol = "601398"
stock_transaction_2.buy_or_sell = StockTransaction.SELL_FLAG
stock_transaction_2.date = datetime(2016, 5, 16, 0, 0, 0)
stock_transaction_2.quantity = 200
stock_transaction_2.price = 4.81
stock_transaction_table.add_stock_transaction(stock_transaction_2)
trans_id_2 = stock_transaction_2.trans_id
stock_closed_transaction = \
StockClosedTransactionTable.close_transaction(stock_transaction_1,
stock_transaction_2)
self.assertEqual(stock_closed_transaction.symbol, "601398")
self.assertEqual(stock_closed_transaction.buy_price, 4.51)
self.assertEqual(stock_closed_transaction.sell_price, 4.81)
self.assertEqual(stock_closed_transaction.buy_date,
datetime(2016, 5, 15, 0, 0, 0))
self.assertEqual(stock_closed_transaction.sell_date,
datetime(2016, 5, 16, 0, 0, 0))
self.assertEqual(stock_closed_transaction.quantity, 200)
stock_transaction = \
stock_transaction_table.get_stock_transaction_by_trans_id(
trans_id_1)
self.assertIsNone(stock_transaction,
"stock_transaction_1 is not deleted")
stock_transaction = \
stock_transaction_table.get_stock_transaction_by_trans_id(
trans_id_2)
self.assertIsNone(stock_transaction,
"stock_transaction_2 is not deleted")
return
def test_lowest_buy_price_quantity2(self):
'''
test lowest buy price quantity
'''
stock_db_connection = get_default_db_connection()
reset_table(stock_db_connection)
StockTransactionTable(stock_db_connection)
quantity = StockTransaction.get_lowest_buy_price_quantity("601398")
self.assertEqual(quantity, 0)
return
def clean_transaction_by_symbol(symbol, conn=None):
'''
clean_transaction_table_by_symbol
'''
if conn is None:
db_connection = get_default_db_connection()
else:
db_connection = conn
session = db_connection.create_session()
query = session.query(StockTransaction).\
filter(StockTransaction.symbol == symbol).\
filter(StockTransaction.buy_or_sell ==
StockTransaction.SELL_FLAG).\
order_by(desc(StockTransaction.date))
sell_transaction = query.first()
if (sell_transaction is None):
session.close()
raise Exception("no need to clean up transaction table")
query = session.query(StockTransaction).\
filter(StockTransaction.symbol == symbol).\
filter(StockTransaction.buy_or_sell ==
StockTransaction.BUY_FLAG).\
order_by(desc(StockTransaction.price))
found = False
buy_transaction = None
for trans in query:
if (trans.quantity == sell_transaction.quantity) and \
(trans.price < sell_transaction.price):
buy_transaction = trans
found = True
break
if not found:
session.close()
raise Exception("no matched buy transaction found")
print(buy_transaction)
print(sell_transaction)
make_transient(buy_transaction)
make_transient(sell_transaction)
session.close()
StockClosedTransactionTable.close_transaction(buy_transaction,
sell_transaction)
return
def recreate_db(conn=None):
'''
recreate_db
'''
if conn is None:
db_connection = get_default_db_connection()
else:
db_connection = conn
base = stock_db.db_stock.Base
engine = db_connection.get_engine()
base.metadata.create_all(engine)
return
def close_transaction(stock_transaction_1, stock_transaction_2):
'''
close_transaction
'''
if stock_transaction_1.symbol != \
stock_transaction_2.symbol:
raise ValueError("not same symbol")
if stock_transaction_1.quantity != \
stock_transaction_2.quantity:
raise ValueError("not same quantity")
if stock_transaction_1.buy_or_sell == \
stock_transaction_2.buy_or_sell:
raise ValueError("same buy or sell flag")
conn = get_default_db_connection()
Session = conn.get_sessionmake()
session = Session()
stock_closed_transaction = StockClosedTransaction()
stock_closed_transaction.symbol = stock_transaction_1.symbol
stock_closed_transaction.quantity = stock_transaction_1.quantity
if stock_transaction_1.buy_or_sell == \
StockTransaction.BUY_FLAG:
stock_closed_transaction.buy_price = stock_transaction_1.price
stock_closed_transaction.buy_date = stock_transaction_1.date
stock_closed_transaction.sell_price = stock_transaction_2.price
stock_closed_transaction.sell_date = stock_transaction_2.date
else:
stock_closed_transaction.buy_price = stock_transaction_2.price
stock_closed_transaction.buy_date = stock_transaction_2.date
stock_closed_transaction.sell_price = stock_transaction_1.price
stock_closed_transaction.sell_date = stock_transaction_1.date
session.add(stock_closed_transaction)
new_stock_transaction = session.merge(stock_transaction_1)
session.delete(new_stock_transaction)
new_stock_transaction = session.merge(stock_transaction_2)
session.delete(new_stock_transaction)
session.commit()
session.refresh(stock_closed_transaction)
make_transient(stock_closed_transaction)
session.close()
return stock_closed_transaction
def __init__(self,
symbol=None,
start_price=None,
stop_price=None,
current_price=None,
conn=None):
if conn == None:
self.conn = get_default_db_connection()
else:
self.conn = conn
self.symbol = symbol
self.start_price = start_price
self.stop_price = stop_price
self.current_price = current_price
self.stock_quantity = -1
self.suggested_buy_or_sell = None
self.suggested_amount = 0
def test_stock_closed_transaction_sanity(self):
'''
test_stock_closed_transaction_sanity()
'''
stock_db_connection = get_default_db_connection()
reset_table(stock_db_connection)
transaction_table = StockClosedTransactionTable(stock_db_connection)
# new a stock closed transaction
stock_closed_transaction = StockClosedTransaction()
stock_closed_transaction.symbol = "601398"
stock_closed_transaction.buy_price = 4.51
stock_closed_transaction.sell_price = 4.61
stock_closed_transaction.buy_date = datetime(2015, 11, 10, 0, 0, 0)
stock_closed_transaction.sell_date = datetime(2015, 12, 30, 0, 0, 0)
stock_closed_transaction.quantity = 200
transaction_table.add_stock_closed_transaction(
stock_closed_transaction)
# query and compare
stock_closed_transaction_list = \
transaction_table.get_all_stock_closed_transaction()
self.assertEqual(len(stock_closed_transaction_list), 1,
"There should be only 1 item")
stock_closed_transaction = stock_closed_transaction_list[0]
self.assertEqual(stock_closed_transaction.symbol, "601398")
self.assertEqual(stock_closed_transaction.buy_price, 4.51)
self.assertEqual(stock_closed_transaction.sell_price, 4.61)
self.assertEqual(stock_closed_transaction.buy_date,
datetime(2015, 11, 10, 0, 0, 0))
self.assertEqual(stock_closed_transaction.sell_date,
datetime(2015, 12, 30, 0, 0, 0))
self.assertEqual(stock_closed_transaction.quantity, 200)
# delete the newly created item
transaction_table.delete_stock_closed_transaction(
stock_closed_transaction)
stock_closed_transaction_list = \
transaction_table.get_all_stock_closed_transaction()
self.assertEqual(len(stock_closed_transaction_list), 0,
"The list should be an empty list")
return
def reset_table(conn=None):
'''
reset_table
'''
if conn is None:
db_connection = get_default_db_connection()
else:
db_connection = conn
base = stock_db.db_stock.Base
engine = db_connection.get_engine()
base.metadata.drop_all(engine)
base.metadata.create_all(engine)
# import stock info from csv file
import_stock_info()
return
def test_stock_cash_sanity(self):
stock_db_connection = get_default_db_connection()
reset_table(stock_db_connection)
stock_cash_table = StockCashTable(stock_db_connection)
stock_cash = StockCash("601398", 1000)
stock_cash_table.add_stock_cash(stock_cash)
# test the new created line
stock_cash = stock_cash_table.get_stock_cash_by_symbol("601398")
self.assertEqual(stock_cash.get_symbol(), "601398")
self.assertEqual(stock_cash.get_amount(), 1000)
# test update stock cash
stock_cash.set_amount(23.456)
stock_cash_table.update_stock_cash(stock_cash)
stock_cash = stock_cash_table.get_stock_cash_by_symbol("601398")
self.assertEqual(stock_cash.get_amount(), 23.456)
# test an unavailable line
stock_cash = stock_cash_table.get_stock_cash_by_symbol("XXXXXX")
self.assertEqual(stock_cash, None)
# insert a new line, and test get_all function
stock_cash = StockCash("601857", 5000)
stock_cash_table.add_stock_cash(stock_cash)
stock_cash_list = stock_cash_table.get_all_stock_cash()
self.assertEqual(len(stock_cash_list), 2)
stock_cash = stock_cash_list[0]
self.assertEqual(stock_cash.get_symbol(), "601398")
self.assertEqual(stock_cash.get_amount(), 23.456)
stock_cash = stock_cash_list[1]
self.assertEqual(stock_cash.get_symbol(), "601857")
self.assertEqual(stock_cash.get_amount(), 5000)
# delete a line
stock_cash = StockCash("601398", 0)
stock_cash_table.delete_stock_cash(stock_cash)
stock_cash = stock_cash_table.get_stock_cash_by_symbol("601398")
self.assertEqual(stock_cash, None)
def get_lowest_gain(symbol, conn=None):
"""
Get lowest gain by symbol
"""
if conn is None:
db_connection = get_default_db_connection()
else:
db_connection = conn
session = db_connection.create_session()
stock_lowest_gain = session.query(StockLowestGain).\
filter(StockLowestGain.symbol == symbol).\
one_or_none()
if stock_lowest_gain is None:
lowest_gain = None
else:
lowest_gain = stock_lowest_gain.lowest_gain
session.close()
return lowest_gain
def __init__(self):
self.conn = get_default_db_connection()
return
