def buy(self, ticker_symbol, trade_volume):
# Will check if user has enough funds to make purchase
last_price = wrapper.lastprice(ticker_symbol)
brokerage_fee = 6.95
buy_cost = (float(last_price) * float(trade_volume)) + brokerage_fee
user_balance = self.user_balance()
if float(user_balance) > float(buy_cost):
with Database() as db:
# Update orders
time_ = time.time()
user_id = self.id_lookup(self.username)
db.cursor.execute(
'''INSERT INTO orders (
user_id,
ticker_symbol,
last_price,
trade_volume,
unix_time)
VALUES(?,?,?,?,?);''',
(user_id, ticker_symbol, buy_cost, trade_volume, time_))
# Update holdings
db.cursor.execute(
'''SELECT trade_volume FROM holdings WHERE ticker_symbol = "{}" and user_id = {user_id};'''
.format(ticker_symbol,
user_id=self.id_lookup(self.username)))
old_volume = db.cursor.fetchone()
if old_volume is None:
db.cursor.execute(
'''INSERT INTO holdings (
user_id,
ticker_symbol,
vwap,
trade_volume)
VALUES(?,?,?,?);''',
(user_id, ticker_symbol, last_price, trade_volume))
else:
new_volume = old_volume[0] + int(trade_volume)
db.cursor.execute(
'''UPDATE holdings SET trade_volume = {} WHERE user_id = {user_id} AND ticker_symbol = "{ticker_symbol}";'''
.format(new_volume,
user_id=self.id_lookup(self.username),
ticker_symbol=ticker_symbol))
db.cursor.execute(
'''UPDATE holdings SET VWAP = {} WHERE user_id = {user_id} AND ticker_symbol = "{ticker_symbol}";'''
.format(self.vwap(ticker_symbol),
user_id=self.id_lookup(self.username),
ticker_symbol=ticker_symbol))
# Update balance
new_balance = user_balance - buy_cost
db.cursor.execute(
'''UPDATE users SET balance = {} WHERE user_id = {user_id};'''
.format(new_balance,
user_id=self.id_lookup(self.username)))
return True
else:
return False
def sell(self,ticker_symbol,trade_volume):
# Will check if user has holding of that stock,
# then will checck if user has enough volume to sell,
with Database() as db:
db.cursor.execute('''SELECT ticker_symbol FROM holdings WHERE user_id = "{user_id}";'''
.format(user_id = self.id_lookup(self.username)))
user_stock = db.cursor.fetchone()[0]
if user_stock is None:
return False
elif user_stock == ticker_symbol:
db.cursor.execute('''SELECT trade_volume FROM holdings WHERE user_id = "{user_id}";'''
.format(user_id = self.id_lookup(self.username)))
user_holdings = db.cursor.fetchone()[0]
if float(user_holdings) >= float(trade_volume):
last_price = wrapper.lastprice(ticker_symbol)
brokerage_fee = 6.95
sell_cost = (float(last_price) * float(trade_volume)) - brokerage_fee
user_balance = self.user_balance()
user_id = self.id_lookup(self.username)
# Update balance
new_balance = user_balance + sell_cost
db.cursor.execute('''UPDATE users SET balance = {} WHERE user_id = {user_id};'''
.format(new_balance, user_id = self.id_lookup(self.username)))
# Update orders
time_ = time.time()
db.cursor.execute('''INSERT INTO orders (
user_id,
order_type,
ticker_symbol,
last_price,
trade_volume,
order_cost,
unix_time)
VALUES(?,?,?,?,?,?,?);''',
(user_id, "Sell", ticker_symbol, last_price, trade_volume, sell_cost, time_))
# Update holdings
if float(user_holdings) == float(trade_volume):
db.cursor.execute('''DELETE FROM holdings WHERE user_id={user_id};'''
.format(user_id = self.id_lookup(self.username)))
return True
else:
new_volume = float(user_holdings) - float(trade_volume)
db.cursor.execute('''UPDATE holdings SET trade_volume = {} WHERE user_id = {user_id};'''
.format(new_volume, user_id = self.id_lookup(self.username)))
db.cursor.execute('''UPDATE holdings SET VWAP = {} WHERE user_id = {user_id};'''
.format(self.vwap(ticker_symbol), user_id = self.id_lookup(self.username)))
return True
else:
return False
def quote(self,ticker_symbol,trade_volume):
last_price = wrapper.lastprice(ticker_symbol)
brokerage_fee = 6.95
buy_cost = (last_price * trade_volume) + brokerage_fee
return buy_cost