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")