I am running a modified version of pset7/finance for my final project, and after running an algorithm to load data into a db, I am interacting with that db with "Flask Run".
I am getting a 500 Internal Server Error with the following:
INFO:werkzeug:192.168.171.40 - - [15/Jul/2019 20:09:55] "GET / HTTP/1.0" 500 -
ERROR:werkzeug:Error on request:
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/werkzeug/serving.py", line 303, in run_wsgi
execute(self.server.app)
File "/usr/local/lib/python3.7/site-packages/werkzeug/serving.py", line 291, in execute
application_iter = app(environ, start_response)
File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
raise value
File "/home/ubuntu/environment/project.new/application.py", line 35, in <module>
Session(app)
File "/usr/local/lib/python3.7/site-packages/flask_session/__init__.py", line 54, in __init__
self.init_app(app)
File "/usr/local/lib/python3.7/site-packages/flask_session/__init__.py", line 61, in init_app
app.session_interface = self._get_interface(app)
File "/usr/local/lib/python3.7/site-packages/flask_session/__init__.py", line 93, in _get_interface
config['SESSION_USE_SIGNER'], config['SESSION_PERMANENT'])
File "/usr/local/lib/python3.7/site-packages/flask_session/sessions.py", line 313, in __init__
from werkzeug.contrib.cache import FileSystemCache
File "/usr/local/lib/python3.7/site-packages/werkzeug/contrib/cache.py", line 85, in <module>
stacklevel=2,
DeprecationWarning: 'werkzeug.contrib.cache' is deprecated as of version 0.15 and will be removed in version 1.0. It has moved to https://github.com/pallets/cachelib.
Any idea on how to address this issue?
Below my "Application" code:
""" Code to create functions that can be called from the website """
import os
from datetime import date
from cs50 import SQL
from flask import Flask, flash, redirect, render_template, request, session
from flask_session import Session
from tempfile import mkdtemp
from werkzeug.exceptions import default_exceptions
from helpers import lookup, lookupsec
# Configure application
app = Flask(__name__)
# Configure CS50 Library to use SQLite database
db = SQL("sqlite:///formula.db")
# Ensure templates are auto-reloaded
app.config["TEMPLATES_AUTO_RELOAD"] = True
# Ensure responses aren't cached
@app.after_request
def after_request(response):
response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate"
response.headers["Expires"] = 0
response.headers["Pragma"] = "no-cache"
return response
# Configure session to use filesystem (instead of signed cookies)
app.config["SESSION_FILE_DIR"] = mkdtemp()
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
Session(app)
""" LANDING PAGE: PROVIDES VIEW OF PERFORMANCE OF THE PORTFOLIO """
@app.route("/")
def index():
#query db to build history BUT EXCLUDES 'VOO' AS IS THE BENCHMARK FOR COMPARISON
historys=db.execute("SELECT symbol, SUM(shares) as sum_shares FROM transactions WHERE symbol!='VOO' GROUP BY symbol HAVING SUM(shares) != 0")
rows=db.execute("SELECT COUNT(id) FROM transactions")
for row in rows:
number=row["COUNT(id)"]
count=1
while count < number+1:
coverages=db.execute("SELECT symbol, shares FROM transactions WHERE id=:count", count=count)
for coverage in coverages:
symbol=str(coverage["symbol"])
shares=float(coverage["shares"])
quote=lookupsec(symbol)
if not quote:
db.execute("UPDATE transactions SET last=:last, mktval=:mktval WHERE id=:count", last='N/A', count=count, mktval='N/A')
else:
last=quote["price"]
mktval=last*shares
db.execute("UPDATE transactions SET last=:last, mktval=:mktval WHERE id=:count", last=last, count=count, mktval=mktval)
count=count+1
totals=db.execute("SELECT SUM(total) as cost_total, SUM(mktval) as mktval_total, SUM(shares) as sum_shares FROM transactions WHERE symbol!='VOO' GROUP BY symbol HAVING SUM(shares) != 0")
lasts=db.execute("SELECT symbol, last FROM transactions WHERE symbol!='VOO' GROUP BY symbol")
prices=db.execute("SELECT symbol, price, timestamp FROM transactions WHERE transtype='buy' AND symbol!='VOO'")
dividends=db.execute("SELECT symbol, SUM(shares) as sum_div FROM transactions WHERE transtype='div' AND symbol!='VOO' GROUP BY symbol")
benchmarks=db.execute("SELECT SUM(total) as cost_total, SUM(mktval) as mktval_total, SUM(shares) as sum_shares FROM transactions WHERE symbol='VOO' GROUP BY symbol HAVING SUM(shares) != 0")
return render_template("performance.html", historys=historys, lasts=lasts, prices=prices, dividends=dividends, totals=totals, benchmarks=benchmarks)
""" CODE TO SELECT TOP LONG EQUITY IDEAS """
@app.route("/selectionlong", methods=["GET", "POST"])
def selectionlong():
"""input for query"""
if request.method == "POST":
size = request.form.get("size")
earningyield = request.form.get("earningyield")
roic = request.form.get("roic")
weight = request.form.get("weight")
earningyield = float(earningyield)
roic = float(roic)
weight = float(weight)
""" deletes content of long db and populate with top results"""
""" NEED TO UPDATE CODE WHEN ALL FINANCIALS HAVE BEEN SUBMITTED FOR GIVEN YEAR i.e. FROM 2018 to 2019 """
db.execute("DELETE FROM long")
db.execute("INSERT INTO long(ticker, companyname, sector, marketcap, ebitev, capreturn, fcfyield, today, calendardate, de, ebitdausd, debtusd, cr, qr, ltdtoeq) SELECT ticker, companyname, sector, marketcap, ebitev, capreturn, fcfyield, today, calendardate, de, ebitdausd, debtusd, cr, qr, ltdtoeq FROM equity WHERE calendardate LIKE '%2019%' AND ebitev>:earningyield AND capreturn>:roic AND marketcap>:size AND companyname!='N/A' AND companyname NOT LIKE '%Depositary%' AND companyname NOT LIKE '%ADR%' AND capreturn != 'NULL'", earningyield=earningyield, roic=roic, size=size)
rows=db.execute("SELECT COUNT(id) FROM long")
for row in rows:
number=row["COUNT(id)"]
""" Iterates through top results from above and ranks based on proprietary formula"""
count=1
while count < number+1:
selections=db.execute("SELECT ticker, ebitev, capreturn FROM long WHERE id=:count", count=count)
for selection in selections:
symbol=str(selection["ticker"])
ebitev=float(selection["ebitev"])
capreturn=float(selection["capreturn"])
""" Prop ranking -- to be modified as one suits """
ranking = weight*ebitev + 0.25*(1-weight)*capreturn
if not ranking:
db.execute("UPDATE long SET ranking=:ranking WHERE id=:count", count=count, ranking='N/A')
else:
db.execute("UPDATE long SET ranking=:ranking WHERE id=:count", count=count, ranking=ranking)
count=count+1
results=db.execute("SELECT ticker, companyname, sector, marketcap, ebitev, capreturn, fcfyield, ranking, de, today, calendardate, ebitdausd, debtusd, cr, qr, ltdtoeq FROM long ORDER BY ranking DESC")
for result in results:
ticker=result["ticker"]
historical=db.execute("SELECT ticker, AVG(capreturn) as ave_roic FROM equity GROUP BY ticker HAVING ave_roic>0")
return render_template ("long.html", results=results, historical=historical)
else:
return render_template ("selectionlong.html")
""" SAME AS LONG BUT SELECT BEST SHORT IDEAS"""
@app.route("/selectionshort", methods=["GET", "POST"])
def selectionshort():
"""input for query"""
if request.method == "POST":
size = request.form.get("size")
earningyield = request.form.get("earningyield")
roic = request.form.get("roic")
weight = request.form.get("weight")
earningyield = float(earningyield)
roic = float(roic)
weight = float(weight)
db.execute("DELETE FROM short")
db.execute("INSERT INTO short(ticker, companyname, sector, marketcap, ebitev, capreturn, fcfyield, today, calendardate, de, ebitdausd, debtusd) SELECT ticker, companyname, sector, marketcap, ebitev, capreturn, fcfyield, today, calendardate, de, ebitdausd, debtusd FROM equity WHERE calendardate LIKE '%2019%' AND ebitev<:earningyield AND capreturn<:roic AND marketcap>:size AND companyname!='N/A' AND companyname NOT LIKE '%Depositary%' AND companyname NOT LIKE '%ADR%' AND capreturn != 'NULL'", earningyield=earningyield, roic=roic, size=size)
rows=db.execute("SELECT COUNT(id) FROM short")
for row in rows:
number=row["COUNT(id)"]
count=1
while count < number+1:
selections=db.execute("SELECT ticker, ebitev, capreturn FROM short WHERE id=:count", count=count)
for selection in selections:
symbol=str(selection["ticker"])
ebitev=float(selection["ebitev"])
capreturn=float(selection["capreturn"])
ranking = weight*ebitev + 0.5*(1-weight)*capreturn
if not ranking:
db.execute("UPDATE short SET ranking=:ranking WHERE id=:count", count=count, ranking='N/A')
else:
db.execute("UPDATE short SET ranking=:ranking WHERE id=:count", count=count, ranking=ranking)
count=count+1
results=db.execute("SELECT ticker, companyname, sector, marketcap, ebitev, capreturn, fcfyield, ranking, de, today, calendardate, ebitdausd, debtusd FROM short ORDER BY ranking ASC")
for result in results:
ticker=result["ticker"]
historical=db.execute("SELECT ticker, AVG(capreturn) as ave_roic FROM equity GROUP BY ticker HAVING ave_roic>0")
return render_template ("short.html", results=results, historical=historical)
else:
return render_template ("selectionshort.html")
""" Creates platform to enter executed buy trades to then track performance """
""" KNOWN BUG: USER CURRENTLY NEED TO ENTER A ZERO DIVIDEND WHEN A BUY POSITION IS OPENED, OTHERWISE PERFORMANCE IS NOT QUERIED CORRECTLY """
@app.route("/buy", methods=["GET", "POST"])
def buy():
"""Buy shares of stock"""
if request.method == "POST":
#symbol blank
if not request.form.get("symbol"):
return ("symbol is not provided")
#symbol does not exist
stock = lookupsec(request.form.get("symbol"))
if stock == None:
return ("symbol is not valid")
#shares not positive integer
shares = float(request.form.get("shares"))
"""
if shares.isdigit() is False:
return ("shares not valid")"""
"""Removed <= because of the need to enter 0 div and make the cost calc work in performance """
"""
if float(shares) < 0:
return ("shares not valid")"""
"""Removed b/c need to enter short positions, expressed as a negative share"""
"""transaction cost"""
transcost = float(request.form.get("transcost"))
shares=float(shares)
symbol = stock["symbol"]
name = stock["name"]
purprice = float(request.form.get("price"))
total = shares*purprice
cost = shares*purprice + transcost
""" CASH REMOVED: STILL NEED TO BE IMPLEMENTED """
"""
cash = db.execute("SELECT cash FROM users")
cash = cash[0]["cash"]
if total > cash:
return ("Not enough cash")
else:
#time = datetime.datetime.now()
transtype = "buy"
db.execute("INSERT INTO transactions(transtype, symbol, shares, price, total, name, timestamp, cost) VALUES(:transtype, :symbol, :shares, :purprice, :total, :name, datetime('now'), :cost)", transtype=transtype, symbol=symbol, shares=shares, purprice=purprice, total=-total, name=name, cost=cost)
db.execute("UPDATE users SET cash = cash-:total", total=total)
"""
if request.form.get("type")=='Buy':
transtype = "buy"
db.execute("INSERT INTO transactions(transtype, symbol, shares, price, transcost, total, name, timestamp, cost) VALUES(:transtype, :symbol, :shares, :purprice, :transcost, :total, :name, datetime('now'), :cost)", transtype=transtype, symbol=symbol, shares=shares, purprice=purprice, transcost=transcost, total=-total, name=name, cost=-cost)
elif request.form.get("type")=='Div':
transtype = "div"
db.execute("INSERT INTO transactions(transtype, symbol, shares, price, transcost, total, name, timestamp, cost) VALUES(:transtype, :symbol, :shares, :purprice, :transcost, :total, :name, datetime('now'), :cost)", transtype=transtype, symbol=symbol, shares=shares, purprice=purprice, transcost=transcost, total=-total, name=name, cost=-cost)
return redirect("/")
else:
return render_template("buy.html")
""" SAME AS ABOVE, ALLOWS USER TO SELL STOCKS, EITHER OWNED OR SHORT SOLD"""
@app.route("/sell", methods=["GET", "POST"])
def sell():
"""Sell shares of stock"""
if request.method == "POST":
if not request.form.get("symbol"):
return ("symbol is not provided")
shares = request.form.get("shares")
"""
if shares.isdigit() is False:
return ("shares not valid")
Removed <= because of the need to enter 0 div and make the cost calc work in performance"""
"""
if float(shares) <= 0:
return ("shares must be positive")
"""
"""Removed b/c need to enter short positions, expressed as a negative share"""
shares = float(shares)
stock = lookup(request.form.get("symbol"))
holdings=db.execute("SELECT symbol, SUM(shares) as sum_shares FROM transactions GROUP BY symbol HAVING sum_shares > 0")
liste=[]
for holding in holdings:
liste.append(holding["symbol"])
if stock["symbol"] not in liste:
return ("stock not owned")
for holding in holdings:
if str(holding["symbol"]) == stock["symbol"]:
if shares > float(holding["sum_shares"]):
return ("you don't own enough shares")
else:
symbol = stock["symbol"]
name = stock["name"]
sellprice = float(request.form.get("price"))
transcost = float(request.form.get("transcost"))
total = shares*sellprice
proceed = shares*sellprice - transcost
transtype = "sell"
db.execute("INSERT INTO transactions(transtype, symbol, shares, transcost, price, total, name, timestamp, proceed) VALUES(:transtype, :symbol, :shares, :transcost, :sellprice, :total, :name, datetime('now'), :proceed)", transtype=transtype, symbol=str(holding["symbol"]), shares=-shares, transcost=transcost, sellprice=sellprice, total=total, name=name, proceed=proceed)
""" BELOW REMOVED AS CASH NOT YET IMPLEMENTED"""
"""db.execute("UPDATE users SET cash = cash + :total WHERE id=:id", total=total, id=session["user_id"])"""
return redirect("/")
else:
holdings=db.execute("SELECT symbol, SUM(shares) as sum_shares FROM transactions GROUP BY symbol HAVING sum_shares > 0")
return render_template("sell.html", holdings=holdings)
""" ALLOWS USER TO KEEP TRACK OF HISTORY OF BUY, SELL and DIV TO AS TO TRACK PERFORMANCE """
@app.route("/history")
def history():
"""Show history of transactions"""
#query db to build history
historys=db.execute("SELECT * FROM transactions")
return render_template("history.html", historys=historys)
""" SAME AS LANDING PAGE """
@app.route("/performance")
def performance():
#query db to build history
historys=db.execute("SELECT symbol, SUM(shares) as sum_shares FROM transactions WHERE symbol!='VOO' GROUP BY symbol")
rows=db.execute("SELECT COUNT(id) FROM transactions")
for row in rows:
number=row["COUNT(id)"]
count=1
while count < number+1:
coverages=db.execute("SELECT symbol, shares FROM transactions WHERE id=:count", count=count)
for coverage in coverages:
symbol=str(coverage["symbol"])
shares=float(coverage["shares"])
quote=lookupsec(symbol)
if not quote:
db.execute("UPDATE transactions SET last=:last, mktval=:mktval WHERE id=:count", last='N/A', count=count, mktval='N/A')
else:
last=quote["price"]
mktval=last*shares
db.execute("UPDATE transactions SET last=:last, mktval=:mktval WHERE id=:count", last=last, count=count, mktval=mktval)
count=count+1
totals=db.execute("SELECT SUM(total) as cost_total, SUM(mktval) as mktval_total FROM transactions WHERE symbol!='VOO'")
lasts=db.execute("SELECT symbol, last FROM transactions WHERE symbol!='VOO' GROUP BY symbol")
prices=db.execute("SELECT symbol, price, timestamp FROM transactions WHERE transtype='buy' AND symbol!='VOO'")
dividends=db.execute("SELECT symbol, SUM(shares) as sum_div FROM transactions WHERE transtype='div' AND symbol!='VOO' GROUP BY symbol")
benchmarks=db.execute("SELECT SUM(total) as cost_total, SUM(mktval) as mktval_total FROM transactions WHERE symbol='VOO'")
return render_template("performance.html", historys=historys, lasts=lasts, prices=prices, dividends=dividends, totals=totals, benchmarks=benchmarks)
Helpers.py code as follows, which features lookup function:
""" Code to call IEXTRADING API VIA FUNCTIONS to GET 1) COMPANY'S STATIC INFO and 2) COMPANY'S LATEST PRICE """
import requests
import urllib.parse
from flask import redirect, render_template, request, session
def lookup(symbol):
# Contact API
try:
"""response = requests.get(f"https://api.iextrading.com/1.0/stock/{urllib.parse.quote_plus(symbol)}/company")"""
response = requests.get(f"https://cloud.iexapis.com/stable/stock/{urllib.parse.quote_plus(symbol)}/company?token=pk_8e51fea56ee5457ca3d9cd766712e421")
response.raise_for_status()
except requests.RequestException:
return None
# Parse response
try:
company = response.json()
return {
"symbol": company["symbol"],
"name": company["companyName"],
"industry": company["industry"],
"sector": company["sector"]
}
except (KeyError, TypeError, ValueError):
return None
def lookupsec(symbol):
"""Look up quote for symbol."""
# Contact API
try:
"""response = requests.get(f"https://api.iextrading.com/1.0/stock/{urllib.parse.quote_plus(symbol)}/quote")"""
response = requests.get(f"https://cloud.iexapis.com/stable/stock/{urllib.parse.quote_plus(symbol)}/quote?token=pk_8e51fea56ee5457ca3d9cd766712e421")
response.raise_for_status()
except requests.RequestException:
return None
# Parse response
try:
quote = response.json()
return {
"symbol": quote["symbol"],
"name": quote["companyName"],
"marketcap": quote["marketCap"],
"price": quote["latestPrice"]
}
except (KeyError, TypeError, ValueError):
return None
lookup.py
that sets the database?