def index():
"""Show portfolio of stocks"""
DBcash = db.execute( "SELECT cash FROM users WHERE id = :Uid", Uid = session["user_id"] )
TOTAL_SUM = DBcash[0]['cash']
DBcash[0]['cash'] = usd(DBcash[0]['cash'])
M = sumStocks(session["user_id"])
for stock in M:
M[stock]['symbol'] = stock
stock_info = lookup(M[stock]['symbol'])
if not stock_info:
apology("Database does not have contact with site for fetching prices")
M[stock].update(stock_info)
M[stock]['curr_total'] = M[stock]['price'] * M[stock]['amount']
M[stock]['price'] = usd( M[stock]["price"] )
TOTAL_SUM += M[stock]['total']
M[stock]['curr_total'] = usd( M[stock]['curr_total'] )
TOTAL_SUM = usd(TOTAL_SUM)
N = {}
for stock in M:
if M[stock]['amount'] > 0:
#print(f"M[stock]['symbol'] = {M[stock]['symbol']}")
#print(f"M[stock] = {M[stock]}")
N[M[stock]['symbol']] = M[stock]
return render_template("index.html",DBcash=DBcash,DBstocks=N,TOTAL_SUM=TOTAL_SUM)
def index():
"""Show portfolio of stocks"""
# jinja values = stocks[[symbol, shares, price, value]], cash, totalvalue
portfolio = db.execute(
"SELECT * FROM portfolios WHERE userID = :sessionid", sessionid=session["user_id"])
cashrow = db.execute(
"SELECT cash FROM users WHERE id = :sessionid", sessionid=session["user_id"])
if cashrow:
cash = cashrow[0]["cash"]
else:
cash = 10000
stocks = []
totalvalue = cash
if portfolio:
for stock in portfolio:
symbol = stock["symbol"]
shares = stock["shares"]
# lookup name and price
myvalue = lookup(symbol)
if not myvalue:
return apology("API not working")
price = myvalue["price"]
value = int(shares) * float(price)
stocks.append({
"symbol": symbol,
"shares": shares,
"price": usd(price),
"value": usd(value)
})
totalvalue += price * shares
return render_template("index.html", stocks=stocks, cash=usd(cash), totalvalue=usd(totalvalue))
def history():
"""Show portfolio of stocks"""
# jinja values = transaction[{symbol, shares, price, date}]
resultsHistory = db.execute(
"SELECT * FROM history WHERE userID = :sessionid", sessionid=session["user_id"])
transactions = []
if resultsHistory:
for transaction in resultsHistory:
# db values = transactionID, userID, shares, symbol, price, datetime
symbol = transaction["symbol"]
shares = transaction["shares"]
price = transaction["price"]
date = transaction["datetime"]
transactions.append({
"symbol": symbol,
"shares": shares,
"price": usd(price),
"date": date
})
else:
transactions.append({
"name": "-",
"symbol": "-",
"shares": "-",
"price": "-",
"value": "-"
})
return render_template("history.html", transactions=transactions)
def quote():
"""Get stock quote."""
if request.method == "POST":
value = request.form.get("symbol")
if not value:
return apology("Please enter a valid ticker")
quote = lookup(value)
if not quote:
return apology("Not a valid symbol")
return render_template("display.html", ticker=quote["symbol"], price=usd(int(quote["price"])))
else:
return render_template("quote.html")
def quote():
"""Get stock quote."""
if request.method == "POST":
if not request.form.get("symbol"):
return apology("No stock name submitted.",400)
stock_info = lookup(request.form.get("symbol"))
if not stock_info:
return apology("Could not find the stock",400)
stock_info["price"] = usd( stock_info["price"] )
return render_template("showQuote.html",stock_info=stock_info)
else:
return render_template("quote.html")
def sell():
"""Sell shares of stock"""
DBcash = db.execute( "SELECT cash FROM users WHERE id = :Uid", Uid = session["user_id"] )
TOTAL_SUM = DBcash[0]['cash']
DBcash[0]['cash'] = usd(DBcash[0]['cash'])
M = sumStocks(session["user_id"])
N = {}
#print(M)
for stock in M:
if M[stock]['amount'] > 0:
N[stock] = M[stock]
#print(N)
if request.method == "POST":
#print("Sell Post")
if not request.form.get("symbol"):
return apology("No stock name submitted.",400)
if not request.form.get("shares"):
return apology("No stock amount submitted.",400)
try:
a = int(request.form.get("shares"))
except:
return apology("invalid amount of shares.",400)
#if (int(request.form.get("shares")) != int(request.form.get("shares"))//1) or int(request.form.get("shares")) < 0:
if (int(request.form.get("shares")) != int(request.form.get("shares"))//1) or int(request.form.get("shares")) < 0:
return apology("invalid amount of shares.",400)
stock_info = lookup(request.form.get("symbol"))
if not stock_info:
return apology("Could not find the stock",400)
if request.form.get("symbol") in M:
if M[request.form.get("symbol")]['amount'] >= int(request.form.get("shares")):
tot_price = stock_info["price"] * int(request.form.get("shares"))
result = db.execute("INSERT INTO transactions (symbol,price,amount,date,Uid) VALUES(:symbol,:price,:amount, :date,:Uid)",
symbol=stock_info["symbol"], price=stock_info["price"], amount=-int(request.form.get("shares")), date=datetime.now(), Uid=session["user_id"] )
if not result:
return apology("The transaction could not be completed.")
else:
db.execute("UPDATE users SET cash = cash + :tot_price where id = :Uid", tot_price = tot_price, Uid = session["user_id"])
else:
return apology("Not enuff shares to sell.",400)
# TODO: write code...
return redirect("/")
#return render_template("sell.html",DBstocks=M)
else:
#print("Sell else")
return render_template("sell.html",DBstocks=N)
def buy():
"""Buy shares of stock"""
if request.method == "POST":
# convert to int and check
try:
shares = int(request.form.get("shares"))
except ValueError:
return apology("Shares must be a positive integer", 400)
if shares <= 0:
return apology("Shares must be a positive integer", 400)
# get the price and other info
info = lookup(request.form.get("symbol"))
if info == None:
return apology("Not a valid symbol", 400)
price = info["price"]
symbol = info["symbol"]
name = info["name"]
subout = price * shares
# check current cash
cash = db.execute("SELECT cash FROM users WHERE id = :idd",
idd=session["user_id"])[0]["cash"]
# make sure if is affordable
if cash - subout < 0:
return apology("Can not be afforded", 403)
else:
cash -= subout
# update cash amount
db.execute("UPDATE users SET cash=:cash, fcash=:fcash WHERE id = :idd",
cash=cash, fcash=usd(cash), idd=session["user_id"])
# Get current stocks
rows = db.execute("SELECT * FROM current WHERE user_id = :idd",
idd=session["user_id"])
already = False
# check if this stock is already owned
for row in rows:
if row["symbol"] == symbol:
already = True
newtot = row["total"] + subout
newshares = row["shares"] + shares
# add it to the database
if already:
db.execute("UPDATE current SET shares = :shares, total = :ntotal, price = :price, format_price = :fprice, format_total = :ftotal WHERE user_id = :idd AND symbol = :symbol",
shares=newshares, ntotal=newtot, price=price, fprice=usd(price), ftotal=usd(newtot), idd=session["user_id"], symbol=symbol)
else:
db.execute("INSERT INTO current (user_id, symbol, name, shares, price, total, format_price, format_total) VALUES (:idd, :symbol, :name, :shares, :price, :total, :fprice, :ftotal)",
idd=session["user_id"], symbol=symbol, name=name, shares=shares, price=price, total=subout, fprice=usd(price), ftotal=usd(subout))
# add it to the history
db.execute("INSERT INTO history (user_id, symbol, shares, price, Action, cum_sum) VALUES (:idd, :symbol, :shares, :price, :action, :s)",
idd=session["user_id"], symbol=symbol, shares=shares, price=usd(price), action="Bought", s=usd(cash))
return redirect("/")
else:
return render_template("buy.html")
def sell():
"""Sell shares of stock"""
if request.method == "GET":
stocks = db.execute("SELECT Symbol FROM Bought WHERE ID = :id",
id=session['user_id'])
return render_template("sell.html", stocks=stocks)
# ensure symbol is submitted
stock = request.form.get("symbol")
if not stock:
return apology("Must provide stock symbol")
# ensure number of shares is submitted
if not request.form.get("shares"):
return apology("Must provide number of shares")
# Ensure valid stock
quote = lookup(stock)
if not quote:
return apology("Invalid Stock Symbol")
if not request.form.get("shares").isdigit():
return apology("Must provide positive integer")
# ensure postive integer
shares = int(request.form.get("shares"))
# select the symbol shares of that user
user_shares = db.execute(
"SELECT shares FROM Bought WHERE ID = :id AND Symbol=:symbol",
id=session["user_id"],
symbol=quote["symbol"])
# check if enough shares to sell
if user_shares[0]["Shares"] < shares:
return apology("Not enough shares")
# update user cash (increase)
db.execute("UPDATE users SET cash = cash + :sale WHERE id = :id",
id=session["user_id"],
sale=quote["price"] * shares)
# otherwise, update portfolio shares count
if shares < user_shares[0]["Shares"]:
db.execute(
"UPDATE Bought SET Shares= shares - :shares WHERE id=:id AND symbol=:symbol",
shares=shares,
id=session["user_id"],
symbol=quote["symbol"])
# if after decrement is zero, delete shares from portfolio
elif shares == user_shares[0]["Shares"]:
db.execute("DELETE FROM Bought WHERE ID=:id AND Symbol=:symbol",
id=session["user_id"],
symbol=quote["symbol"])
# update history of a sell
db.execute(
"INSERT INTO histories ('Symbol', 'Shares', 'Price', 'ID') VALUES(:symbol, :shares, :price, :id)",
symbol=quote["symbol"],
shares=-shares,
price=usd(quote["price"]),
id=session["user_id"])
flash("Sold shares!")
# redirect user to homepage
return redirect("/")
def buy():
"""Buy shares of stock"""
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
user_id = session.get("user_id")
symbol = request.form.get("symbol")
shares = request.form.get("shares")
# ensure symbol not blank
if not symbol:
return apology("missing symbol", 400)
# ensure shares not blank
if not shares:
return apology("missing shares", 400)
# ensure shares is an integer
try:
shares = int(shares)
if shares < 0:
return apology("invalid shares", 400)
except:
return apology("invalid shares", 400)
# ensure the stock is valid
if not lookup(symbol):
return apology("invalid symbol", 400)
# get stock values from lookup
lookup_symbol = lookup(symbol)["symbol"]
lookup_name = lookup(symbol)["name"]
lookup_price = float(lookup(symbol)["price"])
# Query database for cash user has
rows = db.execute("SELECT * FROM users WHERE id = :id", id=user_id)
cash = float(rows[0]["cash"])
# ensure user can afford the shares at current price
balance = round(cash - lookup_price * shares, 2)
if balance < 0:
return apology("can't afford", 400)
# proceed transaction
else:
# update user table
new_cash = str(balance)
rows_users = db.execute(
"UPDATE users SET cash = :cash WHERE id = :id", id=user_id, cash=new_cash)
# update history table
rows_history = db.execute("INSERT INTO history (user_id, symbol, name, shares, price) VALUES(:user_id, :symbol, :name, :shares, :price)",
user_id=user_id, symbol=lookup_symbol, name=lookup_name, shares=shares, price=usd(lookup_price))
# update index table
rows_index = db.execute("INSERT INTO 'index' (user_id, symbol, name, shares) VALUES(:user_id, :symbol, :name, :shares)",
user_id=user_id, symbol=lookup_symbol, name=lookup_name, shares=shares)
if not rows_index:
rows_index = db.execute(
"SELECT * FROM 'index' WHERE user_id = :user_id AND name = :name", user_id=user_id, name=lookup_name)
shares_owned = int(rows_index[0]["shares"])
rows_index_new = db.execute("UPDATE 'index' SET shares = :shares WHERE user_id = :user_id",
user_id=user_id, shares=shares_owned + shares)
# successful transaction
flash("Bought!")
return redirect("/")
# User reached route via GET (as by clicking a link or via redirect)
else:
return render_template("buy.html")
def quote():
"""Get stock quote."""
if request.method == "GET":
return render_template("quote.html")
else:
symbol = request.form.get("symbol")
if not symbol:
return apology("Missing symbol")
symbol = symbol.upper()
# lookup function return a dict
quotedict = lookup(symbol)
# Ensure symbol is valid
if quotedict == None:
return apology("Invalid symbol")
else:
return render_template("quoted.html", company=quotedict['name'], symbol=quotedict['symbol'], price=usd(quotedict['price']))
def index():
if request.method == "GET":
# get all of the user's transactions from the database
rows = db.execute(
"SELECT * FROM transactions WHERE user_id = :user_id",
user_id=session["user_id"])
# create a dictionary to hold each symbol and its information
dictionary = {}
totalOverall = 0
cash = 0
# we will add to shares if they're "buy" type, and subtract if they're "sell" type
# using data from the lookup function
for row in rows:
symbol = row["symbol"]
info = lookup(symbol)
if symbol not in dictionary:
dictionary[symbol] = {
"symbol": symbol,
"shares": 0,
"price": usd(info["price"]),
"total": 0
}
if row["type"] == "buy":
dictionary[symbol]["shares"] = int(
dictionary[symbol]["shares"]) + int(row["shares"])
elif row["type"] == "sell":
dictionary[symbol]["shares"] = int(
dictionary[symbol]["shares"]) - int(row["shares"])
dictionary[symbol]["total"] = float(info["price"]) * float(
dictionary[symbol]["shares"])
#get the user's cash and then convert it to a float
user = db.execute("SELECT cash FROM users WHERE id = :user_id",
user_id=session["user_id"])
cash = float(user[0]["cash"])
# get the total overall so it can be added to the cash
for row in dictionary:
totalOverall = float(totalOverall) + dictionary[row]["total"]
totalOverall = totalOverall + cash
# get the total overall again so it can be converted to USD
for row in dictionary:
dictionary[row]["total"] = usd(dictionary[row]["total"])
return render_template("index.html",
purchase=False,
dictionary=dictionary,
cash=usd(cash),
totalOverall=usd(totalOverall))
if request.method == "POST":
if request.form.get("cash").isnumeric() == False or int(
request.form.get("cash")) < 0:
return apology("you must input a cash value, numbers only", 403)
row = db.execute("SELECT cash FROM users WHERE id = :user_id",
user_id=session["user_id"])
cash = float(row[0]["cash"]) + float(request.form.get("cash"))
db.execute("UPDATE users SET cash = :cash WHERE id = :user_id",
cash=cash,
user_id=session["user_id"])
newCash = usd(float(request.form.get("cash")))
return render_template("index.html",
purchase=True,
newCash=newCash,
cash=usd(cash))
def buy():
"""Buy shares of stock"""
if request.method == "GET":
return render_template("/buy.html")
# for post, get form info.
else:
symbol = request.form.get('symbol')
shares = request.form.get('shares')
# First make sure shares are a positive integer
try:
shares = int(shares)
except (ValueError):
return apology("Only positive integer share numbers accepted.",
403)
if not isinstance(int(shares), int) or int(shares) <= 0:
return apology(
"Only positive integer share numbers may be entered.", 403)
# Next, find price of stock
quote = lookup(symbol)
# if api call is invalid, will return None
if quote == None:
return apology('That symbol is invalid!', 403)
price = quote['price']
# Next look up how much cash the user has
with engine.connect() as conn:
id = session['user_id']
statement = text(
"SELECT cash FROM users WHERE id = :id").bindparams(id=id)
cash = conn.execute(statement)
cash = cash.all()
total_price = price * shares
cash = cash[0]['cash']
if cash < total_price:
return apology(
f'The total price is {usd(total_price)}. You only have {usd(cash)}!',
403)
# if user can afford stock, add to purchases table. Get date of purchase as well.
t1 = datetime.now()
statement = text(
'INSERT INTO purchases (username, symbol, stock_price, shares_purchased, total_price, date)'
+
' values (:username, :symbol, :stock_price, :shares_purchased, :total_price, :date);'
).bindparams(username=session['username'],
symbol=symbol.upper(),
stock_price=usd(price),
shares_purchased=shares,
total_price=usd(total_price),
date=t1)
conn.execute(statement)
conn.commit()
# next subtract price from user's cash
statement = text(
'UPDATE users SET cash = :new_cash WHERE username = :username'
).bindparams(new_cash=cash - total_price,
username=session['username'])
conn.execute(statement)
conn.commit()
# finally, redirect to index
return redirect('/')
def history():
history = db.execute("SELECT * FROM history WHERE user_id = ?",
session["user_id"])
for row in history:
row["price"] = usd(float(row["price"]))
return render_template("history.html", history=history)
def buy():
"""Buy shares of stock"""
if request.method == "GET":
return render_template("buy.html"), 200
if request.method == "POST":
# Ensure shares was submitted
if not request.form.get("symbol"):
return apology("must provide ticker", 400)
else:
ticker = request.form.get("symbol")
if not request.form.get("shares"):
return apology("must provide number of shares")
else:
number = request.form.get("shares")
try:
val = int(number)
if val < 0:
return apology("Sorry, input must be a positive integer, try again")
except ValueError:
return apology("That's not a valid number of shares!")
# return render_template("quote.html", name = pxsy['symbol'], price = pxsy['price'])
# look up cash balance
current_id = session["user_id"]
username = db.execute("SELECT username FROM users WHERE id = :id", id=current_id)
username = username[0]['username']
cash_balance = db.execute("SELECT cash FROM users WHERE id = :id", id=current_id)
balance = cash_balance[0]
cash_balance = float(balance['cash'])
lkp = lookup(ticker)
if lkp == None:
return apology("upon lookup, ticker does not exist")
price = float(lkp['price'])
total_cost = price * val
if total_cost >= cash_balance:
return apology("insufficient funds for purchase")
# get date and time
trans_date = datetime.date.today()
trans_time = datetime.time()
new_cash_balance = cash_balance - float(total_cost)
update_cash = db.execute("UPDATE users SET cash = :cash WHERE id = :id", cash=new_cash_balance, id=current_id)
# check update
cash_balance = db.execute("SELECT cash FROM users WHERE id = :id", id=current_id)
# add transaction to that table
# idnum ticker shares price timestamp
# INSERT INTO <table> (<columns>) VALUES (<values>)
# e.g. INSERT INTO users (username, password, fullname) VALUES ('newman', 'USMAIL', 'Newman')
# id number is primary key and can be configured to autoincrement
transaction = db.execute("INSERT INTO transactions (username, buysell, ticker, shares, price, date, time) VALUES (:username, :buysell, :ticker, :shares, :price, :date, :time)",
username=username, buysell="buy", ticker=ticker, shares=val, price=price, date=trans_date, time=trans_time)
# e.g. SELECT * FROM moms WHERE username = '******'
assets = db.execute("SELECT * FROM assets WHERE username = :username AND ticker = :ticker",
username=username, ticker=ticker)
##############################################
if not assets:
assets = db.execute("INSERT INTO assets (username, ticker, shares) VALUES (:username, :ticker, :shares)",
username=username, ticker=ticker, shares=val)
else:
shares = int(number) + int(assets[0]['shares'])
add_asset = db.execute("UPDATE assets SET shares = :shares WHERE idnum = :id AND ticker = :ticker",
shares=shares, id=assets[0]['idnum'], ticker=ticker)
##################################################
# INSERT OR IGNORE INTO my_table (name,age) VALUES('Karen',34)
# UPDATE my_table SET age = 34 WHERE name='Karen'
# shares name price
return render_template("bought.html", shares=val, name=lkp["symbol"], price=usd(lkp['price']), cost=usd(total_cost), cash=usd(new_cash_balance))
def sell():
"""Sell shares of stock"""
current_id = session["user_id"]
username = db.execute("SELECT username FROM users WHERE id = :id", id=current_id)
username = username[0]['username']
if request.method == "GET":
current_holdings = db.execute("SELECT ticker FROM assets WHERE username = :username", username=username)
return render_template("sell.html", holdings=current_holdings), 200
if request.method == "POST":
if not request.form.get("symbol"):
return apology("must provide ticker", 400)
else:
ticker = request.form.get("symbol")
if not request.form.get("shares"):
return apology("must provide number of shares", 400)
else:
number = request.form.get("shares")
try:
val = int(number)
if val < 0:
return apology("Sorry, input must be a positive integer, try again", 400)
except ValueError:
return apology("That's not a valid number of shares!", 400)
cash_balance = db.execute("SELECT cash FROM users WHERE id = :id", id=current_id)
balance = cash_balance[0]
cash_balance = float(balance['cash'])
lkp = lookup(ticker)
if lkp == None:
return apology("upon lookup, ticker does not exist", 400)
price = float(lkp['price'])
total_cost = price * val
shares_owned = db.execute("SELECT shares FROM assets WHERE username = :username AND ticker = :ticker",
username=username, ticker=ticker)
if not shares_owned:
return apology("you do not appear to own any shares of this security", 400)
if float(shares_owned[0]['shares']) < val:
return apology("you do not own that many shares of this security", 400)
lkp = lookup(ticker)
if lkp == None:
return apology("upon lookup, ticker does not exist", 400)
price = float(lkp['price'])
total_cost = price * val
new_share_count = float(shares_owned[0]['shares']) - val
# get date and time
trans_date = datetime.date.today()
trans_time = datetime.datetime.now().time()
new_cash_balance = cash_balance + float(total_cost)
update_cash = db.execute("UPDATE users SET cash = :cash WHERE id = :id", cash=new_cash_balance, id=current_id)
cash_balance = db.execute("SELECT cash FROM users WHERE id = :id", id=current_id)
transaction = db.execute("INSERT INTO transactions (username, buysell, ticker, shares, price, date, time) VALUES (:username, :buysell, :ticker, :shares, :price, :date, :time)",
username=username, buysell="sell", ticker=ticker, shares=val, price=price, date=trans_date, time=trans_time)
add_asset = db.execute("UPDATE assets SET shares = :shares WHERE username = :username AND ticker = :ticker",
shares=int(new_share_count), username=username, ticker=ticker)
return render_template("sold.html", shares=val, name=ticker, price=usd(price), cost=usd(total_cost), cash=usd(new_cash_balance)), 200
def edit():
"""Edit user stats"""
if request.method == "POST":
# Import variables entered by user
savings = int(request.form.get("savings"))
salary = int(request.form.get("salary"))
savingRate = float(request.form.get("savingRate"))
age = int(request.form.get("age"))
yieldAnnual = float(request.form.get("yieldAnnual"))
payIncrease = float(request.form.get("payIncrease"))
retirementAge = int(request.form.get("retirementAge"))
expense = float(request.form.get("expense"))
# Validate input
if age >= retirementAge:
return apology("Current age has to be less than retirement age",
400)
# Set up variables for growth calculation
yearsSaving = (retirementAge - age)
contribution = (savingRate / 100) * salary
initialReturn = compound(savings, yieldAnnual, yearsSaving)
totalBalance = initialReturn
empty = 0
principle = 0
# Set up arrays for graph
ageByYear = []
moneyByYear = []
ageByYear.append(age)
moneyByYear.append(savings)
# Compounding interest while saving
for year in range(yearsSaving):
contribution = contribution * ((payIncrease / 100) + 1)
principle = principle + contribution
totalBalance = compound(totalBalance, yieldAnnual, 1) + compound(
contribution, yieldAnnual, 1)
# Insert into array for graph
myAge = age + year + 1
ageByYear.append(myAge)
moneyByYear.append(totalBalance)
peak = totalBalance
# Interest in retirement
for x in range(120):
if totalBalance > 0:
if myAge > 120:
break
else:
totalBalance = compound((totalBalance - expense),
yieldAnnual, 1)
myAge = myAge + 1
ageByYear.append(myAge)
moneyByYear.append(totalBalance)
# Years supported by retirement
else:
empty = myAge
break
interest = peak - principle
if empty > 0:
peak = usd(peak)
empty = empty
else:
empty = "never"
peak = (usd(totalBalance))
fig = Figure()
ax = fig.subplots()
ax.plot(ageByYear, moneyByYear)
ax.set_xlabel('Age')
ax.set_ylabel('Cash')
# Save it to a temporary buffer
buf = BytesIO()
fig.savefig(buf, format="png")
#Embed the result in the html output
graph = base64.b64encode(buf.getbuffer()).decode("ascii")
return render_template("stats.html",
totalBalance=usd(totalBalance),
empty=empty,
principle=usd(principle),
interest=usd(interest),
savings=savings,
salary=salary,
savingRate=savingRate,
age=age,
yieldAnnual=yieldAnnual,
retirementAge=retirementAge,
expense=expense,
payIncrease=payIncrease,
graph=graph,
peak=peak)
else:
return render_template("portfolio.html")
def buy():
"""Buy shares of stock"""
if request.method == "GET":
return render_template("buy.html")
else:
# check proper number of shares
if not request.form.get("symbol"):
return apology("must provide symbol", 400)
else:
quote = lookup(request.form.get("symbol"))
if not quote:
return apology("Symbol Invailded", 400)
# check the user input shares its a vailded int
try:
shares = int(request.form.get("shares"))
if shares < 0:
return apology("share(s) must be positive number", 400)
except:
return apology("invailed share(s)")
# select users cash from db by using cookie
userCash = db.execute("SELECT cash FROM users WHERE id = :id",
id=session["user_id"])
# check if enough money to
# or float(money[0]["cash"])?
if not userCash or float(
userCash[0]["cash"]) < quote["price"] * shares:
return apology("Not enough money")
# update history
db.execute(
"INSERT INTO 'transaction' ('symbol', 'shares', 'price', 'id') VALUES(:symbol, :shares, :price, :id)",
symbol=quote["symbol"],
shares=shares,
price=usd(quote["price"]),
id=session["user_id"])
# update user cash
db.execute("UPDATE users SET cash = cash - :purchase WHERE id = :id",
purchase=quote["price"] * float(shares),
id=session["user_id"])
# select users symbol from db
userShares = db.execute(
"SELECT shares FROM portfolio WHERE id = :id AND symbol=:symbol",
id=session["user_id"],
symbol=quote["symbol"])
# if the symbol doesnt exist then create a new stock object
if not userShares:
db.execute(
"INSERT INTO portfolio (name, shares, price, total, symbol, id) VALUES(:name, :shares, :price, :total, :symbol, :id)",
name=quote["name"],
shares=shares,
price=usd(quote["price"]),
total=usd(shares * quote["price"]),
symbol=quote["symbol"],
id=session["user_id"])
# Else if symbol exist then add the share
else:
shareTotal = userShares[0]["shares"] + shares
db.execute(
"UPDATE portfolio SET shares=:shares WHERE id=:id AND symbol=:symbol",
shares=shareTotal,
id=session["user_id"],
symbol=quote["symbol"])
# return to index
return redirect(url_for("index"))
def buy():
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
stock = lookup(request.form.get("symbol"))
# Check to make sure the ticker exists
if not stock:
return apology("Not a valid ticker", 400)
# Make sure shares are positive integer
try:
shares = int(request.form.get("shares"))
except ValueError:
return apology("Shares must be an integer", 400)
if shares <= 0:
return apology("Shares must be a positive integer", 400)
# See if user can afford the purchase
cash = db.execute("SELECT cash FROM users WHERE id = :id",
id=session["user_id"])
spend = int(request.form.get("shares")) * stock["price"]
if spend > float(cash[0]["cash"]):
return apology("You do not have enough money", 400)
# Add purchase into transaction database
db.execute(
"INSERT INTO transactions(id, type, symbol, shares, price) VALUES(:id, 'Purchase', :symbol, :shares, :price)",
id=session["user_id"],
symbol=stock["symbol"],
shares=request.form.get("shares"),
price=usd(stock["price"]))
# Adjust user cash balance
db.execute("UPDATE users SET cash = cash - :spend WHERE id = :id",
spend=spend,
id=session["user_id"])
# Retrieve current share count from portfolio
holding = db.execute(
"SELECT shares FROM portfolio WHERE id = :id AND symbol = :symbol",
id=session["user_id"],
symbol=stock["symbol"])
# If no shares, add the symbol to the portfolio table
if not holding:
db.execute(
"INSERT INTO portfolio(id, symbol, name, shares) VALUES(:id, :symbol, :name, :shares)",
id=session["user_id"],
symbol=stock["symbol"],
name=stock["name"],
shares=request.form.get("shares"))
# Otherwise, increment the share count by the purchase
else:
db.execute(
"UPDATE portfolio SET shares = shares + :shares WHERE id = :id AND symbol = :symbol",
shares=int(request.form.get("shares")),
id=session["user_id"],
symbol=stock["symbol"])
# Redirect user to index table
return redirect("/")
else:
return render_template("buy.html")
def sell():
"""Sell shares of stock"""
if request.method == "GET":
with engine.connect() as conn:
symbols = conn.execute(
text('SELECT symbol' +
' FROM purchases WHERE username = :username' +
' GROUP BY symbol'), [{
'username': session['username']
}])
symbols = symbols.all()
return render_template("/sell.html", symbols=symbols)
# for post, get form info.
else:
symbol = request.form.get('symbol')
shares = request.form.get('shares')
# First make sure shares are a positive integer
try:
shares = int(shares)
except (ValueError):
return apology("Only positive integer share numbers accepted.",
403)
if not isinstance(int(shares), int) or int(shares) <= 0:
return apology(
"Only positive integer share numbers may be entered.", 403)
# Next, find price of stock
quote = lookup(symbol)
# if api call is invalid, will return None
if quote == None:
return apology('That symbol is invalid!', 403)
price = quote['price']
# Next look up how many shares the user has
with engine.connect() as conn:
user_shares = conn.execute(
text(
'SELECT sum(shares_purchased)' +
' FROM purchases WHERE username = :username AND symbol = :symbol'
+ ' GROUP BY symbol'), [{
'username': session['username'],
'symbol': symbol
}])
user_shares = user_shares.all()
if user_shares == []:
return apology(f"You do not own shares of {symbol}!")
user_shares = user_shares[0]['sum(shares_purchased)']
# Check to see if the user has enough shares to sell
if shares > user_shares:
return apology(
f"You may not sell that much, you only own {user_shares} shares of that stock!",
403)
# if so, calculate the total selling price. insert negative numbers into table for shares sold.
total_price = price * -shares
# Get date of purchase as well.
t1 = datetime.now()
statement = text(
'INSERT INTO purchases (username, symbol, stock_price, shares_purchased, total_price, date)'
+
' values (:username, :symbol, :stock_price, :shares_purchased, :total_price, :date);'
).bindparams(username=session['username'],
symbol=symbol.upper(),
stock_price=usd(price),
shares_purchased=-shares,
total_price=usd(total_price),
date=t1)
conn.execute(statement)
conn.commit()
# next subtract price from user's cash. Since total_price is negative, this will add to cash.
cash = conn.execute(text("SELECT cash FROM users WHERE id = :id"),
[{
'id': session["user_id"]
}])
cash = cash.all()
cash = cash[0]['cash']
statement = text(
'UPDATE users SET cash = :new_cash WHERE username = :username'
).bindparams(new_cash=cash - total_price,
username=session['username'])
conn.execute(statement)
conn.commit()
# finally, redirect to index
return redirect('/')
def quote():
"""Get stock quote."""
if request.method == "POST":
ticker = lookup(request.form.get("symbol"))
if ticker == None:
flash('Symbol doesn\'t exist')
return render_template("quote.html")
else:
return render_template("quoted.html", message='A share of {0} ({1}) costs ({2})'.format(ticker['name'],ticker['symbol'],usd(ticker['price'])))
else:
user = db.execute("SELECT username FROM users WHERE id = :id", id=session["user_id"])
return render_template("quote.html",
user=user[0]['username'])
def quote():
"""Get stock quote."""
# Open the quote form page
render_template("quote.html")
# User reached route via POST
if request.method == "POST":
# Ensure symbol was submitted
if not request.form.get("symbol"):
return apology("Missing Symbol", 400)
# Lookup the symbol
quote = lookup(request.form.get("symbol"))
# Check if symbol is valid
if quote is None:
return apology("Invalid Symbol", 400)
return render_template("display_quote.html", name=quote["name"], symbol=quote["symbol"], price=usd(quote["price"]))
else:
return render_template("quote.html")
def sell():
"""Sell shares of stock"""
# store all stock symbols that the user bought in rows2 variable to iterate in html
rows2 = db.execute("SELECT symbol FROM portfolio1 WHERE id = :id",
id=session["user_id"])
if request.method == "POST":
# check if the user enters valid symbol
if not request.form.get("symbol"):
return apology("provide symbol")
quote = lookup(request.form.get("symbol"))
if not quote:
return apology("provide valid symbol")
# check if the user enters valid number of shares
if not request.form.get("shares"):
return apology("provide share")
share = int(request.form.get("shares"))
# if the share is not a positive number, return apology
if share < 1:
return apology("share must be a positive number")
afford = db.execute("SELECT cash FROM users WHERE id = :id",
id=session["user_id"])
totprice = quote['price'] * share
# check if the user owns the stock that he/she wants to sell
check_have = db.execute(
"SELECT share FROM portfolio1 WHERE id = :id AND symbol = :symbol",
id=session["user_id"],
symbol=quote["symbol"])
if check_have:
# if the user owns the stock that he/she wants to sell, record this transaction to history table
db.execute(
"INSERT INTO history (symbol, share, price, date, id) VALUES(:symbol, :share, :price, :date, :id)",
symbol=quote["symbol"],
share=share,
price=usd(quote["price"]),
id=session["user_id"],
date=now.strftime("%Y-%m-%d %H:%M:%S"))
# update the number of shares for the stock he/she just sold
totshare = check_have[0]["share"] - share
# if the share is zero, remove the entire row from the portfolio table
if totshare == 0:
db.execute(
"DELETE FROM portfolio1 WHERE id =:id AND symbol = :symbol",
id=session["user_id"],
symbol=quote["symbol"])
# update the total price and total share that the user has for the stock
else:
newtotprice = totshare * quote["price"]
db.execute(
"UPDATE portfolio1 SET share = :share, total = :total WHERE symbol = :symbol AND id = :id",
id=session["user_id"],
total=usd(newtotprice),
share=totshare,
symbol=quote["symbol"])
else:
return apology("You don't have this stock")
# update the cash that the user has
db.execute("UPDATE users SET cash = cash + :totprice WHERE id = :id",
id=session["user_id"],
totprice=totprice)
return redirect("/")
else:
return render_template("sell.html", rows2=rows2)
def history():
"""Show history of transactions"""
holdings = db.execute("SELECT stock, share, price, currentdate FROM history WHERE user_id=:user_id", user_id=session["user_id"])
for item in holdings:
item['price'] = usd(item['price'])
return render_template("history.html", holdings=holdings)
def buy():
"""Buy shares of stock"""
if request.method == "POST":
# return apology page if anything isn't provided correctly
if not request.form.get("symbol"):
return apology("provide symbol")
quote = lookup(request.form.get("symbol"))
if not quote:
return apology("provide valid symbol")
if not request.form.get("shares"):
return apology("provide share")
share = int(request.form.get("shares"))
if share < 1:
return apology("provide positive share")
afford = db.execute("SELECT cash FROM users WHERE id = :id",
id=session["user_id"])
totprice = quote['price'] * share
# purchase the stock only if the user's cash is enough to purchase the stock
if float(afford[0]["cash"]) >= totprice:
# subtract the total price of purchased stock from the user's cash
db.execute(
"UPDATE users SET cash = cash - :totprice WHERE id = :id",
id=session["user_id"],
totprice=totprice)
# display this transaction in the history tab
db.execute("INSERT INTO history (symbol, share, price, date, id) \
VALUES(:symbol, :share, :price, :date, :id)",
symbol=quote["symbol"],
share=share,
price=usd(quote["price"]),
id=session["user_id"],
date=now.strftime("%Y-%m-%d %H:%M:%S"))
# check if the user already has this stock
cur_share = db.execute(
"SELECT share FROM portfolio1 WHERE id = :id AND symbol = :symbol",
id=session["user_id"],
symbol=quote["symbol"])
# if the user already has the stock, increase the number of share and the total price instead of inserting a whole new line
if cur_share:
totshare = cur_share[0]["share"] + share
newtotprice = totshare * quote["price"]
db.execute(
"UPDATE portfolio1 SET share = :share, total = :total WHERE symbol = :symbol AND id = :id",
id=session["user_id"],
total=usd(newtotprice),
share=totshare,
symbol=quote["symbol"])
else:
db.execute(
"INSERT INTO portfolio1 (symbol, name, share, price, total, id) VALUES(:symbol, :name, :share, :price, :total, :id)",
symbol=quote["symbol"],
name=quote["name"],
share=share,
price=usd(quote["price"]),
total=usd(totprice),
id=session["user_id"])
else:
return apology("you do not have enough money")
return redirect("/")
else:
return render_template("buy.html")
def index():
"""Show portfolio of stocks"""
purchasedict = {}
holdingdict = {}
selldict = {}
# Get stock and shares from 'purchase' table for current user
stocklist = db.execute(
"SELECT stock, SUM(share) as shares FROM purchase WHERE user_id=:user_id GROUP BY stock", user_id=session['user_id'])
# Keys:symbol, Values:shares
for item in stocklist:
purchasedict[item['stock']] = item['shares']
# Get stock and shares from 'sell' table
selllist = db.execute("SELECT stock, SUM(share) as shares FROM sell WHERE user_id=:user_id GROUP BY stock",
user_id=session['user_id'])
for item in selllist:
selldict[item['stock']] = item['shares']
# Calculate currently holding shares using ('purchase.shares' + 'sell.shares(negative)')
for key in purchasedict:
if key in selldict:
purchasedict[key] = int(purchasedict[key]) + int(selldict[key])
# Get stock and shares from 'marketvalue' table
holdinglist = db.execute("SELECT stock, shares FROM marketvalue WHERE user_id=:user_id", user_id=session['user_id'])
for item in holdinglist:
holdingdict[item['stock']] = item['shares']
# Check stock information
for k in purchasedict:
pricedict = lookup(k)
price = float(pricedict['price']) # Current stock price
total = float(int(purchasedict[k]) * price) # Current stock market value
name = pricedict['name'] # Symbol company name
# if purchasedict.keys in holdingdict, update shares in 'marketvalue'
if k in holdingdict.keys():
db.execute("UPDATE marketvalue SET shares=:shares, totalvalue=:totalvalue, price=:price WHERE user_id=:user_id AND stock=:stock",
shares=purchasedict[k], totalvalue=total, price=price, user_id=session['user_id'], stock=k)
# if purchasedict.keys not in holdingdict, insert key and value as stock and shares into 'marketvalue'
else:
db.execute("INSERT or IGNORE INTO marketvalue (user_id, stock, shares, price, totalvalue, name, currentdate) VALUES (:user_id, :stock, :shares, :price, :totalvalue, :name, CURRENT_TIMESTAMP)",
user_id=session['user_id'], stock=k, shares=purchasedict[k], price=price, totalvalue=total, name=name)
# If stock shares ==0: delete stock record in 'marketvalue'
db.execute("DELETE FROM marketvalue WHERE user_id=:user_id AND shares=:shares", user_id=session['user_id'], shares=0)
# Index.html table display
balance = 0
holdings = db.execute(
"SELECT stock, name, shares, price, totalvalue FROM marketvalue WHERE user_id=:user_id GROUP BY stock", user_id=session["user_id"])
for item in holdings:
valuefloat = float(item['totalvalue'])
balance += valuefloat
item['price'] = usd(item['price'])
item['totalvalue'] = usd(item['totalvalue'])
cashdict = db.execute("SELECT cash FROM users WHERE id=:id", id=session['user_id'])
cash = float(cashdict[0]['cash'])
# balance is total of all current holding stock value
balance = round(balance, 2)
# totalbalance is balance + cash
totalbalance = round((balance+cash), 2)
return render_template("index.html", holdings=holdings, cashbalance=usd(cash), totalbalance=usd(totalbalance))
def buy():
"""Buy shares of stock"""
# if user reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# ensure a symbol and quantity were submited
if not request.form.get("symbol") or not request.form.get(
"quantity") or int(request.form.get("quantity")) < 1:
return render_template("buy.html")
symbol = request.form.get("symbol").upper()
quantity = request.form.get("quantity")
user_id = session["user_id"]
# lookup the stock
stock = lookup(symbol)
# ensure symbol exists
if not stock:
return apology("Symbol not found")
# calculate total price
total_price = float(stock["price"]) * float(quantity)
user = db.execute("SELECT * FROM users WHERE id = :id", id=user_id)
funds = float(user[0]["cash"])
# check if user has enough funds
if funds < total_price:
return apology("not enough funds available: " +
str("%.2f" % funds))
funds_left = funds - total_price
# check if symbol is already owned
stock_db = db.execute(
"SELECT * FROM stocks WHERE user_id = :user_id AND symbol = :symbol",
user_id=user_id,
symbol=symbol)
# update with new price if already owned
if len(stock_db) == 1:
new_quantity = int(stock_db[0]["quantity"]) + int(quantity)
new_total = float(stock_db[0]["total"]) + total_price
new_pps = "%.2f" % (new_total / float(new_quantity))
db.execute(
"UPDATE stocks SET quantity = :quantity, total = :total, pps = :pps WHERE user_id = :user_id AND symbol = :symbol",
quantity=new_quantity,
total=new_total,
pps=new_pps,
user_id=user_id,
symbol=symbol)
# else create a new entry in db
else:
db.execute(
"INSERT INTO stocks (user_id, symbol, quantity, total, pps) VALUES (:user_id, :symbol, :quantity, :total, :pps)",
user_id=user_id,
symbol=symbol,
quantity=quantity,
total=total_price,
pps=stock["price"])
# modify available funds
db.execute("UPDATE users SET cash = :cash WHERE id = :id",
cash=funds_left,
id=user_id)
# commit to history
db.execute(
"INSERT INTO history (user_id, action, symbol, quantity, pps) VALUES (:user_id, :action, :symbol, :quantity, :pps)",
user_id=user_id,
action=1,
symbol=symbol,
quantity=quantity,
pps=stock["price"])
# send a success message
return render_template("success.html",
action="bought",
quantity=quantity,
name=stock["name"],
total=usd(total_price),
funds=usd(funds_left))
# else if user reached route via GET (as by clicking a link or via redirect)
else:
return render_template("buy.html")
def sell():
"""Sell shares of stock"""
user_id = session.get("user_id")
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
symbol = request.form.get("symbol")
shares = request.form.get("shares")
# ensure symbol not blank
if not symbol:
return apology("missing symbol", 400)
# ensure shares not blank
if not shares:
return apology("missing shares", 400)
# ensure shares is an integer
try:
shares = int(shares)
if shares < 0:
return apology("invalid shares", 400)
except:
return apology("invalid shares", 400)
# get stock values from lookup
lookup_symbol = lookup(symbol).get("symbol")
lookup_name = lookup(symbol).get("name")
lookup_price = float(lookup(symbol).get("price"))
# get shares user owned
rows_index = db.execute(
"SELECT * FROM 'index' WHERE user_id = :user_id AND name = :name", user_id=user_id, name=lookup_name)
shares_owned = int(rows_index[0]["shares"])
# ensure selling within user's shares
if (shares_owned - shares) < 0:
return apology("too many shares", 400)
# proceed transaction
else:
# Query database for current cash user has
rows = db.execute("SELECT * FROM users WHERE id = :id", id=user_id)
cash = float(rows[0]["cash"])
# update user table
new_cash = round(cash + lookup_price * shares, 2)
rows_users = db.execute(
"UPDATE users SET cash = :cash WHERE id = :id", id=user_id, cash=new_cash)
# update history table
rows_history = db.execute("INSERT INTO history (user_id, symbol, name, shares, price) VALUES(:user_id, :symbol, :name, :shares, :price)",
user_id=user_id, symbol=lookup_symbol, name=lookup_name, shares=-shares, price=usd(lookup_price))
# update index table
if (shares_owned - shares) == 0:
rows_index = db.execute(
"DELETE FROM 'index' WHERE user_id = :user_id AND name = :name", user_id=user_id, name=lookup_name)
else:
rows_index = db.execute("UPDATE 'index' SET shares = :shares WHERE user_id = :user_id",
user_id=user_id, shares=shares_owned - shares)
# successful transaction
flash("Sold!")
return redirect("/")
# User reached route via GET (as by clicking a link or via redirect)
else:
rows = db.execute("SELECT symbol FROM 'index' WHERE user_id = :user_id", user_id=user_id)
return render_template("sell.html", symbols=rows)
def sell():
"""Sell shares of stock."""
stocks = db.execute("SELECT * FROM stocks WHERE user_id = :user_id",
user_id=session["user_id"])
# if user reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# ensure quantity was submited
if not request.form.get("quantity") or int(
request.form.get("quantity")) < 1:
return render_template("sell.html", stocks=stocks)
user_id = session["user_id"]
symbol = request.form.get("symbol").upper()
quantity = request.form.get("quantity")
# retrieve stock from db
stock_db = db.execute(
"SELECT * FROM stocks WHERE user_id = :user_id AND symbol = :symbol",
user_id=user_id,
symbol=symbol)
if stock_db:
stock_db = stock_db[0]
else:
return render_template("sell.html", stocks=stocks)
# retrieve user data from db
user = db.execute("SELECT * FROM users WHERE id = :id", id=user_id)
# ensure quantity to be sold is available
if int(quantity) > stock_db["quantity"]:
return apology("There are not enough shares")
# lookup the stock to get current price
stock = lookup(symbol)
# calculate total price
total_price = float(stock["price"]) * float(quantity)
# modify number of shares owned or delete if < 1
if int(quantity) == stock_db["quantity"]:
db.execute(
"DELETE FROM stocks WHERE user_id = :user_id AND symbol = :symbol",
user_id=user_id,
symbol=symbol)
else:
new_quantity = int(stock_db["quantity"]) - int(quantity)
new_total = float(new_quantity) * float(stock_db["pps"])
db.execute(
"UPDATE stocks SET quantity = :quantity, total = :total WHERE user_id = :user_id AND symbol = :symbol",
quantity=new_quantity,
total=new_total,
user_id=user_id,
symbol=symbol)
# modify available funds
funds_available = float(user[0]["cash"]) + total_price
db.execute("UPDATE users SET cash = :cash WHERE id = :id",
cash=funds_available,
id=user_id)
# commit to history
db.execute(
"INSERT INTO history (user_id, action, symbol, quantity, pps) VALUES (:user_id, :action, :symbol, :quantity, :pps)",
user_id=user_id,
action=0,
symbol=symbol,
quantity=quantity,
pps=stock["price"])
# send a success message
return render_template("success.html",
action="sold",
quantity=quantity,
name=stock["name"],
total=usd(total_price),
funds=usd(funds_available))
# else if user reached route via GET (as by clicking a link or via redirect)
else:
return render_template("sell.html", stocks=stocks)
def buy():
"""Buy shares of stock"""
if request.method == "POST":
# ensure symbol is submitted
symbol = request.form.get("symbol")
if not symbol:
return apology("Must provide stock symbol")
# Ensure valid stock
quote = lookup(symbol)
if not quote:
return apology("Invalid Stock Name")
# ensure number of shares is submitted
if not request.form.get("shares").isdigit():
return apology("Must provide number of shares")
# ensure postive integer
shares = int(request.form.get("shares"))
if not shares or shares < 0:
return apology("Must provide a valid integer for shares")
# check of user can afford
rows = db.execute("SELECT cash FROM users WHERE id = :id",
id=session["user_id"])
cash = rows[0]["cash"]
# look up stock price
stockprice = quote["price"]
# grand total of cash
totalcost = stockprice * shares
# return apology if cannot afford
if not cash or totalcost > cash:
return apology("Sorry, you cannot afford this stock")
# New SQL Table
else:
db.execute(
"INSERT INTO Bought ('ID', 'Symbol', 'Name', 'Price', 'Shares', 'Total') VALUES(:id, :symbol, :name, :price, :shares, :total)",
id=session["user_id"],
symbol=quote["symbol"],
name=quote["name"],
price=stockprice,
shares=shares,
total=totalcost)
# update history
db.execute(
"INSERT INTO histories ('Symbol', 'Shares', 'Price', 'ID') VALUES(:symbol, :shares, :price, :id)",
symbol=quote["symbol"],
shares=shares,
price=usd(stockprice),
id=session["user_id"])
# Update cash
db.execute(
"UPDATE users SET cash = cash - :purchase WHERE id = :id",
id=session["user_id"],
purchase=totalcost)
flash("Bought shares!")
# redirect user to homepage
return redirect("/")
# User reached route via GET
else:
return render_template("buy.html")
def buy():
"""Buy shares of stock"""
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# Define variables for user entered values
symbol = request.form.get("symbol")
shares = request.form.get("shares")
# Ensure symbol was submitted
if not symbol:
return apology("must provide symbol", 403)
# Check if symbol is upper case
if not symbol.isupper():
return apology("symbol must be uppercase", 403)
# Get quote using symbol
quote = lookup(symbol)
# Check if valid symbol
if not quote:
return apology("incorrect symbol", 403)
# Ensure shares was submitted
if not shares:
return apology("must provide number of shares", 403)
# Check if valid number of shares
shares = int(shares)
if shares <= 0:
return apology("incorrect number of shares", 403)
# Calculate remaining balance
user_id = session["user_id"]
price = float(quote.get("price"))
amount = shares * price
cash = db.execute("SELECT cash FROM users WHERE id = :user_id",
user_id=user_id)
cash = float(cash[0].get("cash"))
balance = cash - amount
if balance <= 0:
return apology("not enough cash to buy shares", 403)
# Update cash of user
db.execute("UPDATE users SET cash = :balance WHERE id = :user_id",
balance=balance,
user_id=user_id)
# Update transactions table with new buy user info
price = usd(price)
db.execute("""INSERT INTO transactions (user_id, symbol, shares, price)
VALUES (:user_id, :symbol, :shares, :price)""",
user_id=user_id,
symbol=symbol,
shares=shares,
price=price)
# Update holdings table with new buy user info
holdings = db.execute(
"SELECT shares FROM holdings WHERE user_id=:user_id AND symbol=:symbol",
user_id=user_id,
symbol=symbol)
if not holdings:
db.execute(
"INSERT INTO holdings (user_id, symbol, shares) VALUES (:user_id, :symbol, :shares)",
user_id=user_id,
symbol=symbol,
shares=shares)
else:
share_holdings = shares + holdings[0].get("shares")
db.execute(
"UPDATE holdings SET shares=:share_holdings WHERE user_id=:user_id AND symbol=:symbol",
share_holdings=share_holdings,
user_id=user_id,
symbol=symbol)
# Redirect user to home page
return redirect("/")
# User reached route via GET (as by clicking a link or via redirect)
else:
return render_template("buy.html")
curr_price = [stock_lookup[i]["price"] for i in range(len(stock_lookup))]
# print(f'Current Prices: {curr_price}')
shares = []
for share in user_shares:
for _, val in share.items():
shares.append(val)
# print(f'Shares: {shares}')
stock_total = [(curr_price[i] * shares[i]) for i in range(len(curr_price))]
stock_totals = [{'total': usd(val)} for idx, val in enumerate(stock_total)]
# print(f'Stock Totals: {stock_totals}')
user_stocks = [stocks[i] for i in range(len(stocks))]
for i in range(len(stocks)):
user_stocks[i].update(user_shares[i].items())
user_stocks[i].update(stock_lookup[i].items())
user_stocks[i].update(stock_totals[i].items())
user_stocks[i]['cost'] = usd(stocks[i]['cost'])
user_stocks[i]['price'] = usd(stock_lookup[i]['price'])
# print(f'Users Stocks: {user_stocks}')
# print(f'3rd Index of User Stocks: {user_stocks[2]}')
def sell():
"""Sell shares of stock"""
user_id = session["user_id"]
# User reached route via POST (as by submitting a form via POST)
if request.method == "POST":
# Define variables for user entered values
symbol = request.form.get("symbol")
shares = request.form.get("shares")
# Ensure symbol was submitted
if not symbol:
return apology("must provide symbol", 403)
# Check if symbol is upper case
if not symbol.isupper():
return apology("must provide symbol", 403)
# Get quote using symbol
quote = lookup(symbol)
# Check if valid symbol
if not quote:
return apology("incorrect symbol", 403)
# Ensure shares was submitted
if not shares:
return apology("must provide number of shares", 403)
# Check if valid number of shares
shares = int(shares)
if shares <= 0:
return apology("incorrect number of shares", 403)
# Check if user has enough shares
holdings = db.execute(
"SELECT shares FROM holdings WHERE user_id = :user_id AND symbol=:symbol",
user_id=user_id,
symbol=symbol)
if not holdings:
return apology("not enough share holdings", 403)
share_holdings = holdings[0].get("shares")
print("share_holdings = " + str(share_holdings))
print("shares = " + str(shares))
if share_holdings < shares:
return apology("not enough share holdings", 403)
# Calculate remaining balance
price = float(quote.get("price"))
amount = shares * price
cash = db.execute("SELECT cash FROM users WHERE id = :user_id",
user_id=user_id)
cash = float(cash[0].get("cash"))
balance = cash + amount
# Update cash of user
db.execute("UPDATE users SET cash = :balance WHERE id = :user_id",
balance=balance,
user_id=user_id)
# Update transactions table with new sell user info
price = usd(price)
shares *= -1
db.execute("""INSERT INTO transactions (user_id, symbol, shares, price)
VALUES (:user_id, :symbol, :shares, :price)""",
user_id=user_id,
symbol=symbol,
shares=shares,
price=price)
# Update holdings table with new sell user info
share_holdings += shares
if share_holdings == 0:
db.execute(
"DELETE FROM holdings WHERE user_id=:user_id AND symbol=:symbol",
user_id=user_id,
symbol=symbol)
else:
db.execute(
"UPDATE holdings SET shares=:share_holdings WHERE user_id=:user_id AND symbol=:symbol",
share_holdings=share_holdings,
user_id=user_id,
symbol=symbol)
# Redirect user to home page
return redirect("/")
# User reached route via GET (as by clicking a link or via redirect)
else:
# Get available sell options from database
symbols = db.execute(
"SELECT symbol FROM transactions WHERE user_id=:user_id",
user_id=user_id)
symbols_list = []
for symbol_dict in symbols:
symbols_list.append(symbol_dict["symbol"])
symbols = list(dict.fromkeys(symbols_list))
return render_template("sell.html", symbols=symbols)
def sell():
"""Sell shares of stock"""
if request.method == "POST":
# check proper number of shares
if not request.form.get("symbol"):
return apology("must provide symbol")
else:
quote = lookup(request.form.get("symbol"))
if not quote:
return apology("Symbol Invailded")
# check the user input shares its a vailded int
try:
sharesToSell = int(request.form.get("shares"))
if sharesToSell < 0:
return apology("share(s) must be positive number")
except:
return apology("invailed share(s)")
# check if enough shares to sell
if sharesToSell <= 0:
return apology("You have enter an invailded number.")
userShares = db.execute(
"SELECT shares FROM portfolio WHERE id=:id AND symbol=:symbol",
id=session["user_id"],
symbol=quote["symbol"])
if userShares[0]["shares"] < sharesToSell:
return apology(
"You have enter an invailded number or you don't have enough shares to sell."
)
# update history database about the sells
db.execute(
"INSERT INTO 'transaction' ('symbol', 'shares', 'price', 'id') VALUES(:symbol, :shares, :price, :id)",
symbol=quote["symbol"],
shares=-sharesToSell,
price=usd(quote["price"]),
id=session["user_id"])
# update user cash
userCash = db.execute("SELECT cash FROM users WHERE id=:id",
id=session["user_id"])
newUserCash = float(
userCash[0]["cash"]) + (quote["price"] * int(sharesToSell))
db.execute("UPDATE users SET cash=:newUserCash WHERE id=:id",
id=session["user_id"],
newUserCash=newUserCash)
# update shares count
totalShares = userShares[0]["shares"] - sharesToSell
# if its 0 then delete the share from portfolio database
if totalShares <= 0:
db.execute("DELETE FROM portfolio WHERE id=:id AND symbol=:symbol",
id=session["user_id"],
symbol=quote["symbol"])
return redirect(url_for("index"))
# otherwise update the counts
else:
db.execute(
"UPDATE portfolio SET shares=:shares WHERE id=:id AND symbol=:symbol",
shares=totalShares,
id=session["user_id"],
symbol=quote["symbol"])
return redirect(url_for("index"))
else:
# Look up for all the stock(symbol) that user have.
symbols = db.execute("SELECT symbol FROM portfolio WHERE id=:id",
id=session["user_id"])
return render_template("sell.html", symbols=symbols)
def index():
# queries the "users" SQL table for the initial budget
init_budget = (db.execute("SELECT budget FROM users WHERE id = ?", session["user_id"]))[0]["budget"]
# find first and last date of the current month
# used https://www.mytecbits.com/internet/python/first-day-of-the-month
current_date = datetime.today().date()
first_day_of_month = current_date.replace(day=1)
# used https://www.mytecbits.com/internet/python/get-last-day-of-month
last_day_of_month = current_date.replace(day=monthrange(current_date.year, current_date.month)[1])
# variable representing how much has been spent so far
monthly_expenses = 0
# for pie chart:
labels = []
values = []
# selected colors for the pie chart: mint, purple, pink, yellow, orange, sky blue, lime, cornflower blue, light green
colors = ["#aff8db", "#a79aff", "#f6a6ff", "#fff5ba", "#ffabab", "#85e3ff", "#bffcc6", "#e7ffac", "#afcbff"]
# queries the "expenses" SQL database for a list of categories the user spent money in during the past month
categories_list = db.execute("SELECT DISTINCT category FROM expenses WHERE user_id = ?;", session["user_id"])
# will contain a list of dictionaries of what categories have been used, how much has been spent in each category, and the "recent" list
category_expenses = []
# will contain the five most recent transactions in each category
recent = []
for i in categories_list:
category = i["category"]
# checks if user is logging expense or adding money
# if user is not adding money:
if category != "Raise":
# add the category to the pie chart
labels.append(category)
# query the SQL database for how much money has been spent in that category
expense = db.execute("SELECT SUM(cost) FROM expenses WHERE user_id = ? AND category = ? AND date BETWEEN ? AND ?;",
session["user_id"], category, first_day_of_month, last_day_of_month)
# query the SQL database for the 5 most recent purchases in each category (dictionary with description, cost, date)
recent = db.execute("SELECT description, cost, date FROM expenses WHERE user_id = ? AND category = ? AND date BETWEEN ? AND ? ORDER BY date DESC LIMIT 5;",
session["user_id"], category, first_day_of_month, last_day_of_month)
# calculates the total amount of money spent in each category as a percentage
# adds how much money has been spent in a category to the monthly expenses
# adds the value to the pie chart
if not expense[0]["SUM(cost)"]:
percentage = 0
total = 0
values.append(0)
else:
total = expense[0]["SUM(cost)"]
monthly_expenses += total
percentage = '{0:.2f}%'.format((total / init_budget * 100))
values.append(float(expense[0]["SUM(cost)"]))
# adds the category, total amount spent, and 5 most recent purchases (in the form of a list) to the "category_expenses" list
category_expenses.append({"category": category, "total": usd(total), "recent": recent, "percentage": percentage})
# if the category is a raise in monthly budget
else:
raises = db.execute("SELECT SUM(cost) FROM expenses WHERE user_id = ? AND category = ? AND date BETWEEN ? AND ?;",
session["user_id"], category, first_day_of_month, last_day_of_month)[0]["SUM(cost)"]
if not raises:
pass
# increase that month's budget by the amount of money inputted as a "raise"
else:
init_budget += raises
amt_left = init_budget - monthly_expenses
return render_template("index.html", set=zip(values, labels, colors), monthlybudget=usd(init_budget), left=usd(amt_left), spent=usd(monthly_expenses), category_expenses=category_expenses)
def buy():
if request.method == "POST":
stock = lookup(request.form.get("symbol"))
# Ensure valid symbol
if not stock:
return apology("invalid symbol", 400)
# Ensure valid number of shares
try:
shares = int(request.form.get("shares"))
if shares < 1:
return apology("Shares must be positive number", 400)
except:
return apology("Shares must be positive number", 400)
# Select user's money
money = db.execute("SELECT cash FROM users WHERE id = :id", id=session["user_id"])
# Check if user has enough money to buy shares
if not money or money[0]["cash"] < stock["price"] * shares:
return apology("Can't afford shares", 400)
###########################33 Update transactions
now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
db.execute("INSERT INTO transactions (symbol, shares, price, id, time) VALUES(:symbol, :shares, :price, :id, :time)",
symbol=stock["symbol"], shares=shares, price=stock["price"], id=session["user_id"], time=now)
###########################33 Update history
now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
db.execute("INSERT INTO history (symbol, shares, price, id, time) VALUES(:symbol, :shares, :price, :id, :time)",
symbol=stock["symbol"], shares=shares, price=stock["price"], id=session["user_id"], time=now)
# Update user's cash
db.execute("UPDATE users SET cash = cash - :cash WHERE id = :id", cash=stock["price"] * shares, id=session["user_id"])
#### Select user shares of specified symbol
user_shares = db.execute("SELECT shares FROM portfolio WHERE id = :id AND symbol = :symbol",
id=session["user_id"], symbol=stock["symbol"])
# If user has no shares of symbol, create new stock
if not user_shares:
user_shares = db.execute("INSERT INTO portfolio (name, symbol, shares, price, total, id) VALUES(:name, :symbol, :shares, :price, :total, :id)",
name=stock["name"], symbol=stock["symbol"], shares=shares, price=stock["price"], total=usd(stock["price"] * shares), id=session["user_id"])
# If user does, increment the shares count
else:
shares_count = user_shares[0]["shares"] + shares
db.execute("UPDATE portfolio SET shares = :shares WHERE symbol = :symbol AND id = :id",
shares=shares_count, symbol=stock["symbol"], id=session["user_id"])
# Redirect user to index page after they make a purchase
return redirect("/")
else:
return render_template("buy.html")
def quote():
"""Get stock quote."""
# User reached route via POST
if request.method == "POST":
symbol = request.form.get("symbol")
stock = lookup(symbol)
if not symbol:
return apology("A stock symbol in needed")
elif not stock:
return apology("Symbol doesn't exist")
else:
return render_template("quoted.html", name=stock["name"], symbol=stock["symbol"], price=usd(stock["price"]))
# user got to the route via GET
else:
return render_template("quote.html")
