Problem Set 9: Finance

Evidence of a 5-point Submission

  • index function builds the stock portfolio from a single transactions table, likely by summing shares while grouping by symbol. Uses the AS keyword after a keyword such as "SUM(shares)" to format the column name.
  • buy and sell functions robustly check user input using guards. Use try-except to convert the number of shares to an int. Use only one SELECT to get present cash (for buy) or shares (for sell), one UPDATE to update cash, and one INSERT to add the transaction to a table.
  • history function consists of a single SELECT query to the single transactions table, does not loop through the returned object to reformat the data.
  • quote function distinguishes between “missing symbol” error and “invalid symbol” error.
  • register uses an INSERT inside of a try-except to check for a unique username without using a SELECT.

Evidence of a 4-point Submission

  • index function builds the stock portfolio from a single transactions table. Instead of positive/negative shares values, may use a separate column to differentiate between bought/sold shares. May neglect AS to clean up column names.
  • buy and sell functions robustly check user input using guards. Use only one SELECT to get present cash (for buy) or shares (for sell), one UPDATE to update cash, and one INSERT to add the transaction to a table.
  • history function consists of a single SELECT query to the single transactions table, does not loop through the returned object to reformat the data.
  • quote function distinguishes between “missing symbol” error and “invalid symbol” error.
  • register checks the number of rows returned by a SELECT to check for a unique username.

Evidence of a 3-point Submission

  • index function may use an additional table besides a transactions table.
  • buy and sell functions use only one SELECT to get present cash (for buy) or shares (for sell), one UPDATE to update cash, and one INSERT to add the transaction to a table.
  • history function consists of a single SELECT query to the single transactions table. May loop through data to add additional information.
  • quote function does not distinguish between “missing symbol” error and “invalid symbol” error.
  • register checks the number of rows returned by a SELECT to check for a unique username.

Evidence of a 2-point Submission

  • index function may use an additional table besides a transactions table. May unnecessarily overwrite data returned by the SELECT query, or may copy the data into a separate data structure.
  • buy and sell functions may use more than one SELECT to get present cash (for buy) or shares (for sell), more than one UPDATE to update cash, or more than one INSERT to add the transaction to a table. May pass a symbol value to lookup without sufficiently validating it.
  • history function may unnecessarily overwrite data returned by the SELECT query, may copy the data into a separate data structure.
  • quote function may use extraneous variables, conditionals, or computation.
  • register function may loop through a list of current usernames to check if username already exists.

Evidence of a 1-point Submission

  • index function does not call the lookup function
  • buy function does not handle errors, may allow for an illegal purchase (may not properly check for the legality of a purchase, may not use the lookup function), may incorrectly calculate transaction values
  • register function stores unhashed passwords into the database
  • sell function may incorrectly calculate transaction values

Example Implementations (Worse vs. Better)

index Function

Worse Implementation

The example below creates an unnecessary object, stocklist, which requires the copying of large amounts of information

stocklist = {}
total_stocks = 0
rows = db.execute("""SELECT symbol, SUM(shares) AS totalshares FROM trades
                  WHERE userid = ? GROUP BY symbol
                  HAVING SUM (shares > 0)""", session["user_id"])

for row in rows:
    quote = lookup(row["symbol"])
    stocklist[quote["symbol"]] = {"name": quote["name"],
                                  "shares": row["totalshares"],
                                  "price": quote["price"],
                                  "total": row["totalshares"] * quote["price"]}
    total_stocks += row["totalshares"] * quote["price"]

Better Implementation

The example below uses the AS keyword to avoid copying unnecessary information

stocks = db.execute("""SELECT symbol, SUM(shares) AS shares FROM transactions
                  WHERE user_id = :user_id GROUP BY symbol
                  HAVING SUM (shares > 0)""", user_id = session["user_id"])

for stock in stocks:
    quote = lookup(stock["symbol"])
    stock["name"] = quote["name"]
    stock["price"] = quote["price"]
    total += stock["shares"] * quote["price"]

buy Function

Worse Implementation

The example below calls the lookup function three separate times and includes many instances of unnecessary variable declaration

if not request.form.get("shares") or not (request.form.get("shares")).isnumeric():
    return apology("Please insert a positive integer for the number of shares.")
if not lookup(request.form.get("symbol")):
    return apology("Please insert a valid stock symbol")
newShares = int(request.form.get("shares"))
buySymbol = lookup(request.form.get("symbol"))["symbol"]
currentCash = db.execute("SELECT cash FROM users WHERE id = ?", session["user_id"])
currentPrice = lookup(request.form.get("symbol"))["price"]
symbolName = lookup(request.form.get("symbol"))["name"]
if currentCash[0]["cash"] < newShares * currentPrice:
    return apology("You cannot afford this transaction")
else:
    newCash = currentCash[0]["cash"] - (newShares * currentPrice)
    db.execute("UPDATE users SET cash = ? WHERE id = ?", newCash, session["user_id"])
    db.execute("INSERT INTO transactions (user_id, symbol, shares, price, log) VALUES (?, ?, ?, ?, ?)",
                session["user_id"], buySymbol, newShares,
                currentPrice, datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
return redirect("/")

Better Implementation

The example below uses only one lookup call and does not declare unnecessary variables

if not request.form.get("symbol"):
    return apology("Missing symbol")
elif not request.form.get("shares"):
    return apology("Missing shares")
elif not request.form.get("shares").isdigit():
    return apology("Invalid shares")
shares = int(request.form.get("shares"))
if not shares:
    return apology("Too few shares")

quote = lookup(request.form.get("symbol"))
if not quote:
    return apology("Invalid symbol")

cost = shares * quote["price"]

rows = db.execute("SELECT cash FROM users WHERE id = :id",
                  id = session["user_id"])
if not rows:
    return apology("Missing user")
cash = rows[0]["cash"]

if cash < cost:
    return apology("Can't afford")

db.execute("""INSERT INTO transactions (user_id, symbol, shares, price) 
           VALUES (:user_id, :symbol, :shares, :price)""",
           user_id = session["user_id"], symbol = quote["symbol"],
           shares = shares, price = quote["price"])

db.execute("UPDATE users SET cash = cash - :cost WHERE id = :id", cost = cost, id = session["user_id"])

flash("Bought!")
return redirect("/")

register Function

Worse Implementation

The example below stores the user’s unhashed password - posing a security risk for the user

if not request.form.get("username"):
    return apology("Must provide username", 400)
elif not request.form.get("password"):
    return apology("Must provide password", 400)
elif not request.form.get("password" == request.form.get("confirmation")):
    return apology("Must match passwords", 400)
elif len(db.execute("SELECT * FROM users WHERE username LIKE ?", request.form.get("username"))) != 0:
    return apology("Must provide unique username", 400)

username = request.form.get("username")
password = generate_password_hash(request.form.get("password"))
confirmation = request.form.get("confirmation")
p_k = db.execute("INSERT INTO users (username, hash) VALUES (?, ?)",
                 username, password)
return render_template("login.html")

Better Implementation

The example below does not store the user’s unhashed password at any point, and uses error handling on the INSERT to avoid the need for an extra SELECT statement

if not request.form.get("username"):
    return apology("Must provide username")
elif not request.form.get("password"):
    return apology("Must provide password")
elif not request.form.get("password" == request.form.get("confirmation")):
    return apology("Must match passwords")

try:
    id = db.execute("INSERT INTO users (username, hash) VALUES (?, ?)",
                    request.form.get("username"),
                    generate_password_hash(request.form.get("password")))

except ValueError:
    return apology("Username taken")

session["user_id"] = id

flash("Registered1")
return redirect("/")

sell Function

Worse Implementation

The example below includes many unnecessary calculations and variables

symbol = request.form.get("symbol")
shares = int(request.form.get("shares"))

if lookup(symbol) == None:
    return apology("Invalid symbol")
else:
    stock = lookup(symbol)
    name = stock["name"]
    price = stock["price"]
    symbol2 = stock["symbol"]

if len(name) < 1:
    name = "Name placeholder"

total = shares * price * -1
today = date.today()

if not shares or shares < 0:
    return apology("Must enter a valid number of shares")

currentShares = db.execute("SELECT shares FROM active WHERE name = ? AND user_id = ?",
                           name, session["user_id"])
if currentShares[0]["shares"] >= shares:
    db.execute("""UPDATE active
                  SET totalValue = totalValue + ?, shares = shares - ?
                  WHERE name = ? AND user_id = ?""",
                  total, shares, name, session["user_id"])
else:
    return apology("You don't have enough shares")

totalValue = db.execute("SELECT totalValue FROM active WHERE name = ? AND user_id = ?",
                        name, session["user_id"])[0]["totalValue"]
totalValue = totalValue + total

db.execute("""INSERT INTO records
              (symbol, name, sharesType, price, totalValue, date, user_id)
              VALUES (?, ?, ?, ?, ?, ?, ?)""",
              symbol2, name, "sold", price, total, today, session["user_id"])

db.execute("UPDATE users SET cash = cash - ? WHERE id = ?",
           total, session["user_id"])
           
return index()

Better Implementation

The example below uses no extraneous variables and does no unnecessary computation

if not request.form.get("symbol"):
    return apology("Missing symbol")
symbol = request.form.get("symbol").upper()
if not request.foorm.get("shares"):
    return apology("Missing shares")
elif not request.form.get("shares").isdigit():
    return apology("Invalid shares")
shares = int(request.form.get("shares"))
if shares < 1:
    return apology("Shares must be positive")

rows = db.execute("""SELECT SUM(shares) AS shares FROM transactions
                     WHERE user_id = :user_id AND symbol = :symbol GROUP BY symbol""",
                     user_id = session["user_id"], symbol = symbol)
if len(rows) != 1:
    return apology("Symbol not owned")
if shares > rows[0]["shares"]:
    return apology("Too many shares")

quote = lookup(request.form.get("symbol"))

db.execute("""INSERT INTO transactions (user_id, symbol, shares, price)
              VALUES (:user_id, :symbol, :shares, :price)""",
              user_id = session["user_id"], symbol = quote["symbol"],
              shares = -shares, price = quote["price"])

db.execute("UPDATE users SET cash = cash + :value WHERE id = :id),
           value = shares * quote["price"], id = session["user_id"])

flash("Sold!")
return redirect("/")