Problem Set 7: CS50 Finance

Building an app is a process that needs structure

I made an initial attempt that went very poorly. I read the CS50 assignment points and went through building the app page for page. Make all the code to register, then make all the code to get a quote, then buy, then sell. I started getting very picky about how the user got feedback when something was posted. I wanted nice banners to show things were successful or unsuccessful. I wanted javascript on the front end to update values. Everything became a big mess.

So i deleted it all. And pulled out some post-it notes, and a pencil.

I made 3 stacks:

  • Frontend
  • Database
  • Logic

Then I started writing down all the tasks as I read them on the CS50 assignment page. Each post was very simple and specific. On the top left corner of each post-it I put the first letter of what stack it would go on. On the top right I put the first letter for the webpage (register, buy, sell, quote etc).

A post-it might be “create view template register.html,” which would be a F on the top left.

another might be “create model for users,” which would be a D.

“create get_balance method for users,” another D.

What I found was the Frontend stack could be done very quickly because I noticed there were similar tasks. They weren’t directly connected the way a user thinks when reading about the app. They were literally the same task just for a different page of the app.

So my advice would be to write out as many tasks as possible, sort them in this way, and try to develop frontend -> database -> logic.

For the database I highly recommend creating a new file called models.py and creating a class for each part of your app. I created users, transactions and portfolio. Here is what that looks like:

During the database phase I recommend using the python terminal REPL. If you go into the directory that holds application.py for the flask app, you can actually test out any function, class or method right in the terminal. Here is an example of me using the terminal to test some of the user database methods:

type python -i application.py. This is interactive mode and makes testing models easier so you don’t have to submit forms.

When you finish the database make sure to add some fake data to save time during the logic portion.

Logic is something like “if the user does not have enough cash to by stocks return apology.”

API changes

In the helpers file on line 42 I changed the url because the one given doesn’t work. Here is the actual api now https://iexcloud.io/docs/api/#quote

change to this on line 42:

        response = requests.get(f"https://cloud.iexapis.com/stable/stock/{urllib.parse.quote_plus(symbol)}/quote?token=YOUR_FREE_APITOKEN")

change your_free_api_token with your own token when you sign up.

Other quick notes

datetime: https://www.techonthenet.com/sqlite/functions/datetime.php

foreign keys: https://sqlite.org/foreignkeys.html

inner joins: https://www.sqlitetutorial.net/sqlite-inner-join/

Using the with keyword for opening and closing sqlite3

In python there is a way to open a sql connection and automatically close it using with.

Here would be the standard way of connecting to a database and closing it at the end.

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# close the connection if we are done with it.
conn.close()

Now using with:

import sqlite3
  with sqlite3.connect('example.db') as conn:
    c = conn.cursor()

    c.execute('''CREATE TABLE stocks
                 (date text, trans text, symbol text, qty real, price real)''')
    c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

    # Save (commit) the changes
    conn.commit()

    # connection automatically closed once we leave this indented section of code

Questions?

This was a much bigger project, so if you have any questions please let me know and i’ll do my best to answer. My code will be up on github:

5 1 vote
Article Rating
Subscribe
Notify of
guest
11 Comments
oldest
newest most voted
Inline Feedbacks
View all comments
Salomon Dushimirimana
Salomon Dushimirimana
3 months ago

Hi, thank you for the information. I also recently started working on this process. However, as I started to run my application, I got an error that I can’t solve: AttributeError: ‘users_db’ object has no attribute ‘register’

I don’t know if you will ever get this, but if you do, I really need your help

Thank you!

Michael Ofengenden
Michael Ofengenden
1 month ago

Hey man, what kind of tables and colomns did you create in your finance.db, other than the table users?

Matt
Matt
24 days ago

Hi, thanks for this, but I have a question. I used your models.py, but I keep getting this error whenever on the homepage.
 
File “/home/ubuntu/web/finance/models.py”, line 62, in get_holdings
  self.c.execute(‘SELECT symbol, shares FROM portfolio WHERE user_id=?’, (self.id,))
 
sqlite3.OperationalError: no such table: portfolio
 
Any help would be appreciated on how to fix this! Thanks.

Matt
Matt
24 days ago
Reply to  Johnny

Got it, I added all the correct tables and fixed this. But I’ve come up with another problem. I keep on getting this error when going to buy stocks:

self.c.execute(‘INSERT INTO transactions VALUES (?,?,?,?,?,?)’, (self.id,) + row)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 6, and there are 5 supplied.

After pressing buy I get the error, but on the homepage i’ve got the correct info with shares, costs, etc. This has puzzled me for some time. Again, sorry to bother you!

Matt
Matt
24 days ago
Reply to  Johnny

Thanks, turns out self.id and row had 5 values, and the table transactions had 6, just got rid of a column on the table and it works.

jacob
jacob
12 days ago

This is amazing, great job. I have a question though… What all goes into the portfolio table and what is each variables types? This is the only thing which is confusing me.
Thank You!!!