Set Forth Byline with airSlate SignNow

Set forth byline

hey everyone welcome back in this video we're going to get started building our full stack trading application starting with the database design now before we get started with that i want to again mention the website hackingthemarkets.com where i'll be posting more and more content over time and it's kind of a central place to get the content that i create and so uh starting with the front page there's an email list i'm starting for anyone that wants to receive notifications on the channel or of an upcoming course that i have planned secondly there's a link to the discussion board link to the channel videos and links to a source code so all the source code that i post in github and finally there is the donate button which is to buy me a coffee page if you want to contribute something directly to the channel and briefly i want to mention uh people that recently donated including christian he said he's used the td ameritrade apis and ngrok along with tradingview based on these videos to automate some trades for a portfolio management club at its university so thanks i'm glad uh thanks for the contribution and i'm glad you've been able to use this at your university and check out the ngrok video if you haven't already if you want to create a local server it's an alternative to using aws chalice or using um heroku which we used in some other videos so ngrok is a good solution if you want to have trades or web hooks go to your local machine secondly i want to thank sudhi which uh looks like this person just uh donated just now he says i've been trying to get a way to create a trading bot for a while i did try many things like quanto and quant connect but all of them didn't work for some reason but our tutorial my tutorials are very easy to understand so many thanks so uh thanks a lot um and there's a lot more to come and we're about to get started with that now so thanks toodeep um so let's go ahead and get rolling so here is the first article that's going to be posted on the blog so you can follow along so um full stack trading app tutorial so part one is database design and why do we want a database is what i want to discuss first so uh to review in the past a couple of months ago we created a technical screen screener application which i believe i still have loaded here where i can scan for different uh technical setups so i just use candlestick patterns here so you could scan for bearish engulfing patterns for instance so there's a bunch of those right um so previously i was storing these in a csv file so we used yahoo finance and just we went through a big symbol list of all these stocks in the s p 500 and saved all of these csv files so there's hundreds hundreds of these on the desktop and anytime i hit a scan here it's actually going to open each of those load them into a data frame and see if this candlestick pattern is present and spit out uh whether there's a match or not right which uh works but also uh you we have to think about whether that's really a scalable solution do we really want our entire do you when you go to a big web application like facebook you know or twitter you know they don't just put all your information in csv files right there's millions of relationships between um what you post to your friends relationships to your friends in your network different groups you're a member of different permissions security settings all these things are related pieces of data so these relationships get very complex over time and they're not very manageable to just have a bunch of csv files with data often you'll want to use a relational database which we're going to use here but also high traffic sites also sometimes use nosql databases or key value stores which we might talk about in the future so for this a particular application we're going to use a relational database and we're going to create multiple tables to store our stocks and our stock price data so that's what we're going to do here um so just to show you what we've done so far right so let's just create a quick script to show you the type of data that we're going to be storing right so i said you can install y finance and import it and download some data so let's get some data real quick so let's make a download.pi right and as usual we can and we've done this many times on this channel uh we already have y finance installed so if i click play here um you'll see this runs but if you didn't have it you just do pip install y finance or use whatever virtual environment setting that you have so i installed y finance i already have it if i just copy this here right import y finance and i can say df which is just a data frame it's just the name of a variable creating a data frame and i'm saying why finance download uh the data for apple stock from january 1st through october 2nd right and then i can save that data frame to a csv file called apple.csv right so if i run this you'll see it actually will download the data for apple stock so you see this apple csv is here right and we have the open high low close adjusted volume and volume for apple stock here by day right so how would we go about storing this in a database and even more importantly before we design the database let's think about why you might want a relational database in the first place so i wrote this section on sql databases versus a csv file and why they would be useful the first first reason i put is ease of management so let's say i download the entire universe of thousands of stocks that would result in thousands of files on my system which are all over the place it's not very easy to find anything right so that can get very difficult to manage and also we downloaded a snapshot in a single csv file right but over time you know more and more days past we're gonna need to append new data as it comes in and you know you don't always want to open a bunch of files put another row close the file go to the next file like there's a lot of different files to manage right and often and also when we are um accessing these files so whenever i use this web application i click scan i'm going through and python's having to open each one of these files read the whole thing in and perform some analysis close the file and so forth so there's just a lot of different files and files to manage right so that's the first reason it's a little bit easier uh to manage right and sqlite which we're going to use as our database system makes it very easy to manage relationships with that data right so one of the things that's happened already um since we downloaded all of our s p 500 data is certain stock tickers have changed for instance so if you think back a stock like google became alphabet and created two symbols um certain stocks got de-listed certain stocks were removed from the s p 500 so there's a lot of changes that happen and so uh it's very easy to update these relationships in a relational database with an sql query right versus going through all the files oh let me delete this file let me delete this file let me rename this file to something else all this data is in one table we update the symbol in one place and anything else that is related to that particular stock is already up to date like that data's only stored in one place secondly i gave the reason of expandability right so let's say we want to store some additional data so we've downloaded the csv file right but that's just a flat file what if we want to store other information like fundamental data like where does that go we just have dates open high low close volume but what if i want to attach uh fundamental data or quarterly earnings uh to a particular stock there's nowhere to really put that data do we create another file uh do we and then how do we reference the stock it refers to so do we make a file called apple underscore prices apple underscore fundamental apple underscore watch lists you know there's tons of related data that we're going to want to store so if we have a multi-user system and each user has a watch list that might have apple stock we want to store that relationship some way and a relational database is going to make it very easy to handle those relationships and also if we need to add additional columns and attributes we can just run an alter table statement right and then the other reason i gave is uh indexing and aggregation and so as i mentioned when i run this filter right we're going to have to pull a bunch of files in from the file system read the entire thing into a pandas data frame perform our analysis close the file and then read hundreds of files which we don't want to do but if we have one big table that has our stock our stock symbols and one big table that stores all our prices and all those price prices reference as stock we can use sql to perform select queries to aggregate our data and slice it and dice it in different ways so sql has a where clause joins group by order by count average sum a lot of different functions are built in and so if i wanted to just select all of my data on a 15 minute time frame you know i could use this big record i could store all of my minute bars over time right and i can say oh give me it grouped by a 15 minute interval or i could say just find me all of the stocks in every in a given etf for instance only those stock symbols i could select um price history for one given week for for back testing checking seasonality so there's all these different ways i might want to just select a little piece of data and a sql select statement is very powerful for doing that and for joining a bunch of data together with uh with user preferences so users might be following stocks and so forth so um yeah so i give a lot of detailed reasons for why you might want to use a sql database and so we're going to be using a sql lite as our database and so we're going to install that real quick if you don't have it already and there are a couple of reasons we're using it there's a variety of sql databases you can use obviously you can use postgresql or you can use mysql uh you could use oracle if for some reason you had an oracle license and then there's there's also no sql databases but i like sqlite and in this case because of the simplicity it's very easy to get started with it uh with it uh because the module is already built into python and also we don't have to set up a brand new server for this a sql lite database lives in a single file so we have a whole relational database in a single file and can maintain all these complex relationships and if we wanted to back up the database it's just backing up a file you could just copy it to a usb stick put it in a dropbox wherever you want to put it and you have a backup it's not this networked database server where we have to worry about a bunch of different user accounts and network connectivity so it's very easy for us to get started and show you how this works and if for some reason we wanted to use mysql or postgres later we could port our data over or import it or use our same sql queries to create a different type of database so it's not that hard to switch if we wanted to the second reason the speed actually sqlite is very fast for selecting data so most of our operations are going to be selects or filtering our data and retrieving it right from the web so it's very fast for that and if you don't have tons of users using an application concurrently uh sql lite works really well there's actually a lot of old benchmarks saying it i think at some point it was actually faster than mysql i'm not sure if that's still true but it's plenty fast it's free easy to get started so good database to know and it's a good way for us to get started uh with this tutorial um so the first thing we're gonna do here is create a new sqlite database and to do that uh we're going to download the sqlite command line executable so i'm going to click that and open it and this has the sqlite sqlite.org download and you can download it right and depending on your operating system i'm on osx i can download this one um if you're on windows download that version obviously and you'll just get a zip file with a little binary inside of it so you'll just get an executable and so if i look um i get an s and executable and i just have this sqlite command that i can run right so if you go to whatever command line you're using right and let's just go in our projects directory and i'm working in a directory right now called a full stack trading app so i'm going to go to full stack trading app and we're just going to test this out real quick so i already have the sqlite 3 command here right so i can run that and it starts um and i do quit to quit um so this is installed this uh executable it's already in my path so i can just type sqlite wherever if if you don't have it in your path you're gonna have to work in the same directory where you have the executable so it's probably a good idea to add that to your path so in windows there's like a path variable you append the directories where to look for these executable commands um yeah so if you look on the article it just says type sqlitefdb right so i'm going to type that and then that'll actually create a new database file called appdb so if i actually insert some records or create any tables here it'll create a new database file called appdb so this create table statement we can copy that so i have this code embedded right here in the blog post and you can just copy it there should be a copy button and i'll do that and let's just copy that in and you terminate a an sql statement with a semicolon and so i click enter and now i actually have a table so if i type dot schema that's the way to show the database schema and all the tables that are available okay so this is how you create a stock table so before i dive too much into the syntax of sql let's go ahead and just draw a quick diagram of what our database is going to look like so i have i'm gonna use the ipad here pencil and let's just sketch out our database diagram for storing our stock data so i am going to create a new table and so when i design a database i do it like this i draw like a little box and at the top i write the name of the table and then we uh i draw some arrows to show the relationships between my data so i'm having a one table here called stock and then one table that i'm going to call stock price right okay and so a stock has a couple of attributes we're just going to start with a couple of attributes and the nice thing about this type of database is our tables we can add new columns over time very easily and so a stock we're going to have a unique identifier so every single table that we create in our relational database will have a unique id which is the primary key that uniquely identifies a row in that table and this is an auto incrementing value so each time i insert a new row a new number will be associated with that record and so this is the primary key right the primary key and then each stock has like a symbol right and a company name right company and those are the columns right so those are columns a symbol is of type text and company is of type text and this id is actually a number so it's an integer and so i'm going to write sql insert statements in a bit and what those are going to do is insert new rows into this table so id symbol and company are columns you can kind of think about how they are in a spreadsheet they're the headings of the column and then i insert new rows in my table and so i'll insert a record for apple for instance and that'll be apple inc and then i'm going to insert you know microsoft so simple msft and microsoft right and so when i do that sqlite our database is automatically going to assign some numbers so 1 and 2 will be the values of id so it auto increments that so the next record will be 3 4 and so forth right okay so that's the symbol and the company name so where do i put the price data so i could put price right here right and say oh yeah it's a company let's store the price and make one big flat table but since we're in a relational database um yeah we don't want to do that we want to sometimes separate out our data into another table and so since there's many prices right there's not just one price we're storing historical price data over the course you know of many years so we can't just put like price one price two and dates and all that stuff in here so we create a separate table that references each stock so i'm gonna erase that here right and so we're gonna create a table called stock price okay and what we do then is this has an id as well and then each row in this table will have a stock id and this is a foreign key so the foreign key is going to reference the primary key of the stock table right and so this will have like a number and so each record here will have like a number like one or two and then it'll point back to apple or microsoft stock or whatever right and then we can have columns for our date open hi low and close right and volume and so forth right and so that's our diagram right we we just list out all the columns the attributes of the record or of each row and then we have keys that uniquely identify those rows and if a row needs to reference another table we we put a foreign key here so this this is the foreign key and then we have numbers here to represent open high low and close right so we have two different tables here for now um that eat that reference each other so i could have many price records right so i could have many price records for each one single stock so let's say 2020 january oh one or one oh 101 right and 2020 0102 right and if we did uh minute time frames we'd store a whole time stamp so we can have tons of apple stock records here and each of those would have a stock id of one a stock id of one and then we could just have different values so 107 108.5 and so forth throughout history right and then each of these ones reference a single record here right and then if we just want to update apple stock if the symbol change for some reason we just update this one record we don't need to update it in a bunch of other files and a bunch of other tables because this is just referencing it using the number one so there's one definitive place for making updates to you know the core apple stock and then anything else that references it is just using this primary key number and so if we expanded this out further for instance and then users had watch lists for instance right we might have a user table right and each user would have an id and a username right and then their watch list right a user a watch list might have an id and a name and so we can keep we can keep expanding relationships over time with the relational database which is nice right and each watch list might have multiple stocks right so user could have a watch list so we could create joining tables here and each watch list would just point to a single stock so multiple users could have apple on different watch lists for instance and we only need to use the number one to reference apple stock in the core table and if we deleted apple stock for some reason we could have our relational database automatically delete any records that reference apple's stock to make it very easy to clean up so we could have it cascade down and delete all these records delete apple from the watch list and it's nice to be able to manage all these relationships in one place so that's kind of a rough idea of the or actually it's exactly how we're going to design the initial database we're going to start with two tables with a few columns and then we're going to expand on it when we develop our application over time so when i develop an application i i develop a very minimal a core set of functionality and then gradually add columns and complexity and relationships over time so for right now we're going to start with just these two tables and then when we talk about user authentication and other features and preferences we'll add these you know other tables like this later on okay so that's how you design uh some basic database relationships in advance to picture how your data is going to be related and how it's going to flow into your system so we typed sqlite3 fdb to create a new database and now we just need to create a new table which i did so we created a table called stock it has an id which is an integer and then it has a primary key like i just showed and symbol is text and company is also text and we specify not null to say you know every stock has to have this it's not optional and we that the symbol is a unique attribute okay so we got that um so that's how you write your first bit of sql that's the sql to create a table right and so i listed some of some of the topics i just discussed in the diagram i talked about uh columns primary and foreign keys and then let's go ahead and create the stock price table so i'll just click the copy button there and we're in sqlite i type that followed by a semicolon and now we have two tables so if i type dot schema to command and sqlite you see we have two tables a stock id is an integer and we specify stock id as a foreign key and it references the stock table and that id so each one of these records references a stock right so we have a relational database with two tables now and yeah and that's how you create um your first relational database and so uh that's gonna be it for this video in the next video i'm going to show you how to uh insert records into these tables and how to query and how to do a crud operations which is create read update and delete so we're going to insert stock records we're going to insert a stock price history and then we're going to show what we would do if a stock symbol changes or if we need to delete a particular stock from our database so we're going to learn sql we're going to learn four statements insert select update delete which you're going to use all the time so that's it for this video this one was just to design the initial version of the database and create a database structure and show you how to use sqlite to create a database and tables so stay tuned for the next video and we'll start talking about sql queries