def index():
"""Show portfolio of stocks"""
user = Users.query.get(session.get("user_id"))
query = db.session.query(
Records.symbol, Records.company_name,
db.func.sum(Records.shares).label("shares")).group_by(
Records.symbol,
Records.company_name).filter(Records.user_id == user.id)
info = [{}] * query.count()
grand_total = user.cash
for i, row in enumerate(query):
try:
info[i]['price'] = lookup(row.symbol)['price']
except:
info[i]['price'] = 1
info[i]['symbol'] = row.symbol
info[i]['company_name'] = row.company_name
info[i]['shares'] = row.shares
info[i]['value'] = round(row.shares * info[i]['price'], 2)
grand_total += info[i]['value']
return render_template("index.html",
cash=user.cash,
rows=info,
total=round(grand_total, 2))
def buy():
"""Buy share of stock"""
if request.method == "GET":
return render_template("buy.html")
# User reached route via POST (as by submitting a form via POST)
shares = int(request.form.get("shares"))
symbol = request.form.get("symbol")
quote = lookup(symbol)
if not quote:
return apology("invalid symbol", 404)
price = quote['price']
value = round(shares * price, 2)
user = Users.query.get(session.get("user_id"))
if value > user.cash:
return apology("You don't have enough cash", 406)
record = Records(symbol=quote['symbol'],
company_name=quote['name'],
transact_type="buy",
shares=shares,
price=price,
user_id=user.id)
user.cash -= value
db.session.add(record)
db.session.commit()
flash("Bought")
return redirect(url_for('index'))
def getStockQuote():
print("1) in route getStockQuote")
# Query for currency exchange rate
symbol = request.form.get("symbol")
print(f"2) in route getStockQuote, symbol = {symbol}")
stockDict = lookup(symbol)
if not stockDict:
#message = "You have requested an invalid stock symbol " + symbol + ".<br>Please try again."
return jsonify({
"success": False,
"stockSymbol": symbol,
"stockCompanyName": "",
"stockLatestPrice": "",
"title": ""
})
stockSymbol = stockDict["symbol"]
stockCompanyName = stockDict["companyName"]
stockLatestPrice = stockDict["latestPrice"]
title = "Quote: Stock Symbol " + stockSymbol
print(
f"3) getStockQuote: done returning stock price {stockLatestPrice}, for company {stockCompanyName}"
)
return jsonify({
"success": True,
"stockSymbol": stockSymbol,
"stockCompanyName": stockCompanyName,
"stockLatestPrice": stockLatestPrice,
"title": title
})
def quote(args=""):
"""Get stock quote."""
if request.method == "GET":
return render_template("getStockQuote.html", symbol=args)
symbol = request.form.get("symbol")
if not symbol:
return messageAlert(Markup.escape("Stock symbol is required"), 403,
"error.png", "quote")
stockDict = lookup(symbol)
if not stockDict:
message = "You have requested an invalid stock symbol " + symbol + ".<br>Please try again."
return messageAlert(Markup.escape(message), 403, "error.png", "quote")
stockSymbol = stockDict["symbol"]
stockCompanyName = stockDict["companyName"]
stockLatestPrice = stockDict["latestPrice"]
title = "Quote: Stock Symbol " + stockSymbol
return render_template('displayStockQuote.html',
symbol=stockSymbol,
stockSymbol=stockSymbol,
stockCompanyName=stockCompanyName,
stockLatestPrice=stockLatestPrice,
title=title)
def quote():
"""Get stock quote."""
if request.method == "GET":
return render_template("quote.html")
symbol = request.form.get("symbol")
quote = lookup(symbol)
if not quote:
return apology("invalid symbol", 404)
name = quote['name']
price = quote['price']
symbol = quote['symbol']
return render_template("quoted.html",
name=name,
price=price,
symbol=symbol)
def sell():
"""Sell shares of stock"""
if request.method == "GET":
symbols = Records.query.with_entities(Records.symbol).\
distinct().filter_by(user_id=session.get("user_id")).all()
return render_template("sell.html", symbols=symbols)
symbol = request.form.get("symbol")
shares = int(request.form.get("shares"))
record = db.session.query(db.func.sum(Records.shares).label("shares")).\
group_by(Records.user_id).filter_by(symbol=symbol, user_id=session.get('user_id')).one()
if shares > record.shares:
return apology(
f"You can only sell { record.shares } shares or less than", 400)
quote = lookup(symbol)
price = quote['price']
value = round(shares * price, 2)
user = Users.query.get(session.get('user_id'))
user.cash += value
record = Records(symbol=quote['symbol'],
company_name=quote['name'],
transact_type="sell",
shares=int('-' + str(shares)),
price=price,
user_id=user.id)
db.session.add(record)
db.session.commit()
flash('Sold')
return redirect(url_for('index'))
def sell(args=""):
"""Sell shares of stock"""
if request.method == "GET":
return render_template("buyShares.html",
title="Sell Shares",
symbol=args,
action="sell")
symbol = request.form.get("symbol")
shares = request.form.get("shares")
if not symbol:
return messageAlert(Markup.escape("Stock symbol is required"), 403,
"error.png", "sell")
if not shares:
return messageAlert(Markup.escape("Number of shares is required"), 403,
"error.png", "sell", symbol)
try:
int(shares)
shares = int(shares)
isValidShares = True
except ValueError:
isValidShares = False
if not isValidShares:
return messageAlert(Markup.escape("Number of shares is an integer"),
403, "error.png", "sell", symbol)
stockDict = lookup(symbol)
if not stockDict:
message = "You have requested an invalid stock symbol " + symbol + ".<br>Please try again."
return messageAlert(Markup.escape(message), 403, "error.png", "sell")
stockSymbol = stockDict["symbol"]
stockCompanyName = stockDict["companyName"]
stockLatestPrice = stockDict["latestPrice"]
title = "Buy: Stock Symbol " + stockSymbol
# create a database connection
#conn = create_connection(db)
#conn.row_factory = sqlite3.Row
conn = psycopg2.connect(host=os.environ.get('POSTGRESQL_HOST'),
database=os.environ.get('POSTGRESQL_DATABASE'),
user=os.environ.get('POSTGRESQL_USERNAME'),
password=os.environ.get('POSTGRESQL_PASSWORD'),
port=os.environ.get('POSTGRESQL_PORT'))
with conn:
isError = 0
try:
cur = conn.cursor()
cur.execute("Select COALESCE(sum(shares),0) as accountShares from orders \
Where user_id='%s' \
And symbol = upper(%s)" , \
(current_user.id,stockSymbol))
rows = cur.fetchall()
# Ensure record exists
if len(rows) != 1:
message = "You do not own any shares of " + stockCompanyName + " (" + stockSymbol + ").<br>Please try again."
return messageAlert(Markup.escape(message), 403, "error.png",
"sell", symbol)
accountShares = rows[0][0]
if shares > accountShares:
message = "You own " + \
str(accountShares) + " shares of " + stockCompanyName + " (" + stockSymbol + ").<br>" + \
"You may not sell more shares (" + str(shares) + ") than you currently own."
return messageAlert(Markup.escape(message), 403, "error.png",
"sell", symbol)
shares = shares * (-1)
extendedPrice = stockLatestPrice * shares
values = (current_user.id, "Sell", stockSymbol, stockCompanyName,
shares, stockLatestPrice, extendedPrice,
datetime.utcnow())
sql = ''' INSERT INTO orders(user_id, transaction_type, symbol, company_name, shares, price, extended_price,order_date) VALUES(%s,%s,%s,%s,%s,%s,%s,%s) '''
cur = conn.cursor()
cur.execute(sql, values)
id = cur.lastrowid
cur = conn.cursor()
cur.execute("Update users Set cash = cash - %s Where id=%s;",
(extendedPrice, current_user.id))
conn.commit()
# Redirect user to home page
return redirect("/")
except (Exception, psycopg2.Error) as error:
isError = 1
print("Error in route sell", error)
if isError == 1:
return messageAlert(
Markup.escape(
"Sell Error: Error while connecting to PostgreSQL to sell an equity"
), 500, "error.png", "index")
def index():
"""Show portfolio of stocks"""
# create a database connection
conn = psycopg2.connect(host=os.environ.get('POSTGRESQL_HOST'),
database=os.environ.get('POSTGRESQL_DATABASE'),
user=os.environ.get('POSTGRESQL_USERNAME'),
password=os.environ.get('POSTGRESQL_PASSWORD'),
port=os.environ.get('POSTGRESQL_PORT'))
#conn = create_connection(db)
#conn.row_factory = sqlite3.Row
with conn:
isError = 0
try:
cur = conn.cursor()
print(f"executing query to get cash")
# Query database for username
cur.execute(
"SELECT id, username, hash, cash, mobile,comments FROM users WHERE id = '%s';",
(current_user.id, ))
rows = cur.fetchall()
# Ensure record exists
if len(rows) != 1:
return messageAlert(
Markup.escape("Portfolio Error: User does not exist"), 500,
"error.png", "login")
#cash = rows[0]["cash"]
cash = rows[0][3]
accountTotal = cash
print(f"Account total is {accountTotal}")
except (Exception, psycopg2.Error) as error:
isError = 1
print("Error while connecting to PostgreSQL for query to get cash",
error)
if isError == 1:
# if(conn):
# cur.close()
# conn.close()
# cur = None
# conn = None
# print("1) index: PostgreSQL connection is closed")
return messageAlert(
Markup.escape(
"Portfolio Error: Error while connecting to PostgreSQL for query to get cash"
), 500, "error.png", "index")
isError = 0
try:
cur = conn.cursor()
cur.execute("SELECT symbol, sum(shares) as shares from orders \
WHERE user_id = '%s' \
group by symbol \
having sum(shares) > 0 \
order by symbol" , \
(current_user.id,))
rows = cur.fetchall()
idx = -1
allStocks = []
for row in rows:
idx += 1
# shares = rows[idx][1]
stockDict = lookup(rows[idx][0])
if not stockDict:
return messageAlert(
Markup.escape(
"Portfolio Error: Invalid stock symbol " +
str(idx) + " " + rows[idx]["symbol"]), 500,
"error.png", "quote")
# The tuple is constructed as (Symbol, company_name, Shares, Price, TOTAL)
stockTuple = ()
stockSymbol = stockDict["symbol"]
stockCompanyName = stockDict["companyName"]
shares = rows[idx][1]
stockLatestPrice = stockDict["latestPrice"]
formattedLatestPrice = "${:,.2f}".format(stockLatestPrice)
stockTotalPrice = stockLatestPrice * shares
formattedTotalPrice = "${:,.2f}".format(stockTotalPrice)
stockTuple = (stockSymbol, stockCompanyName, shares,
formattedLatestPrice, formattedTotalPrice)
allStocks.append(stockTuple)
accountTotal = accountTotal + stockTotalPrice
# if(conn):
# cur.close()
# conn.close()
# cur = None
# conn = None
# print("2) index: PostgreSQL connection is closed")
return render_template("portfolio.html",
title="Portfolio",
cash=cash,
allStocks=allStocks,
accountTotal=accountTotal)
except (Exception, psycopg2.Error) as error:
isError = 1
print(
"Error while connecting to PostgreSQL for query to get stock transactions",
error)
# if(conn):
# cur.close()
# conn.close()
# cur = None
# conn = None
# print("3) index: PostgreSQL connection is closed")
if isError == 1:
return messageAlert(
Markup.escape(
"Portfolio Error: Error while connecting to PostgreSQL for query to get stock transactions"
), 500, "error.png", "index")
def buy(args=""):
"""Buy shares of stock"""
if request.method == "GET":
return render_template("buyShares.html",
title="Buy Shares",
symbol=args,
action="buy")
symbol = request.form.get("symbol")
shares = request.form.get("shares")
if not symbol:
return messageAlert(Markup.escape("Stock symbol is required"), 403,
"error.png", "buy")
if not shares:
return messageAlert(Markup.escape("Number of shares is required"), 403,
"error.png", "buy", symbol)
try:
int(shares)
shares = int(shares)
isValidShares = True
except ValueError:
isValidShares = False
if not isValidShares:
return messageAlert(Markup.escape("Number of shares is an integer"),
403, "error.png", "buy", symbol)
stockDict = lookup(symbol)
if not stockDict:
message = "You have requested an invalid stock symbol " + symbol + ".<br>Please try again."
return messageAlert(Markup.escape(message), 403, "error.png", "buy",
symbol)
stockSymbol = stockDict["symbol"]
stockCompanyName = stockDict["companyName"]
stockLatestPrice = stockDict["latestPrice"]
title = "Buy: Stock Symbol " + stockSymbol
extendedPrice = stockLatestPrice * shares
# create a database connection
# conn = create_connection(db)
# conn.row_factory = sqlite3.Row
conn = psycopg2.connect(host=os.environ.get('POSTGRESQL_HOST'),
database=os.environ.get('POSTGRESQL_DATABASE'),
user=os.environ.get('POSTGRESQL_USERNAME'),
password=os.environ.get('POSTGRESQL_PASSWORD'),
port=os.environ.get('POSTGRESQL_PORT'))
with conn:
isError = 0
try:
cur = conn.cursor()
# Query database for username
cur.execute(
"SELECT id, username, hash, cash, mobile,comments FROM users WHERE id = '%s';",
(current_user.id, ))
rows = cur.fetchall()
# Ensure record exists
if len(rows) != 1:
return messageAlert(
Markup.escape("Portfolio Error: User does not exist"), 500,
"error.png", "login")
cash = rows[0][3]
if cash < extendedPrice:
message = "You do not have sufficient funds (" + "${:,.2f}".format(cash) + ")<br>to buy " + \
str(shares) + " shares of " + stockCompanyName + "(" + stockSymbol + ") at " + \
"${:,.2f}".format(stockLatestPrice) + " a share<br>for a net price of " + "${:,.2f}".format(extendedPrice) + "."
return messageAlert(Markup.escape(message), 403, "error.png",
"buy", symbol)
values = (current_user.id, "Buy", stockSymbol, stockCompanyName,
shares, stockLatestPrice, extendedPrice,
datetime.utcnow())
sql = ''' INSERT INTO orders(user_id, transaction_type, symbol, company_name, shares, price, extended_price, order_date) VALUES(%s,%s,%s,%s,%s,%s,%s,%s) '''
cur = conn.cursor()
cur.execute(sql, values)
id = cur.lastrowid
cur = conn.cursor()
cur.execute("Update users Set cash = cash - %s Where id=%s;",
(extendedPrice, current_user.id))
conn.commit()
# if(conn):
# cur.close()
# conn.close()
# cur = None
# conn = None
# print("1) buy: PostgreSQL connection is closed")
# Redirect user to home page
return redirect("/")
except (Exception, psycopg2.Error) as error:
isError = 1
print("Error in route Buy", error)
# if(conn):
# cur.close()
# conn.close()
# cur = None
# conn = None
# print("2) buy: PostgreSQL connection is closed")
if isError == 1:
return messageAlert(
Markup.escape(
"Buy Error: Error while connecting to PostgreSQL to buy a stock"
), 500, "error.png", "index")