Professional Expense Tracker in Python

Key Takeaways

what is going on guys welcome back in today's video we're going to build an expense tracking tool in the command line using python which will also make use of a database making it a perfect project for beginners to practice their skills so let us get right into it not a [Music] game all right so let us get started with a database first and for that we're going to create a new python file which we're going to call creatore DB and we're going to keep it simple here we're just going to import site 3 and we're going to establish a connection by saying sqi 3 connect expense. DB or expenses. DB and then we're going to say that we want to have a cursor which is going to be a connection. cursor and we just want to execute a simple create statement so cursor. execute and we're going to use a multi-line string here in which we're going to say create up here create table if not exists expenses and as I said we're going to keep it simple so we're going to say we have an ID which is an integer and a primary key we're going to have a date which is going to be a date we're going to have a description so what was the money spent on which is going to be a text then we're going to also have a category which is going to be a text as well and then we're going to have the actual amount so the actual price which is going to be a real which is the decimal data type if you want in um in sqi 3 and we're going to of course close this bracket here so this is our structure and in order to create this database we now say connection. commit to commit the changes and then connection. close to wrap everything up and once we run this you can see we have this expenses database here I can double click it here pie charm I can open it I can connect to it and then I can say select everything from expenses and when I run this you can see that we have the correct structure down here ID date description category price um and this is our basis here this is where we're going to store the expenses this is where we're going to get the expenses from so that we have a persistent storage uh across the individual sessions and now we're going to start with the main file so we're going to create this main.py file in here we're also going to import sqi 3 because we need to connect to the database and we're also going to import datetime so first we're going to again connect to the database so sqi 3 connect expenses DB and we're going to create a cursor connection. cursor this is going to be our basis for the individual actions and then we're going to have this basic menu or um main Loop if you want to call it that we're going to say while true so basically an endless loop we're going to print the following select an option and we're going to provide the user with different options we're going to say one enter a new expense and two is going to be view expenses summary or something and then we're going to say okay if the choice or actually we're going to say Choice equals the integer of the input then we're going to say if the choice is equal to one we're going to do one thing and otherwise L if the choice is equal to two we're going to do another thing and else if the choice is invalid we're just going to exit um and let's go ahead and Implement now the first section here which is entering a new expense now to do this we need some information from the users so what date was the expense on uh what was the money spent on and so on so we're going to say here first of all that the date is going to be input and we're going to prompt here enter the date of the expense and we're going to provide the format here year year year year so four digits for the year two for the month and two for the day and then we're going to do the same thing with the description but without a format obviously so description is going to be input enter the description of the expense and we're going to get rid of this format here um and then we can basically uh what we want to do is we want to be able to add a category but we want to also be able to choose an existing category so what we want to do here is we don't want to just ask the user for a string uh like give me the category of this purchase but we want to say okay here are the categories you already have if you want to choose one of those enter a number otherwise you can create a new one so what we want to do here is we want to use the cursor to execute the following statement we want to say select and then uh distinct category from expenses and what this does essentially distinct means that we want to have only the unique values so in the beginning we won't have any categories in the database so we're only going to be able to create a new one um but um over time if we have 100 entries but we have only three different categories across all these entries it will show us only those three distinct categories this is the the functionality of the distinct uh statement here and we want to get this we want to get the results categories by saying cursor. fetchall and then we're going to iterate over the categories that we have and we're going to also enumerate them so that we have an automatic menu so we're going to say here for or actually want to say first select a category by number or something and then we're going to say for index category in enumerate categories we're going to print an F string here with the index a DOT or actually an index plus one because we want to have options one two whatever and not zero um zero to whatever so we're going to say here Dot and then the category and we want to print uh the category zero so the actual category name that is that and in the end we want to also print here the following we want to print an F string we're going to have length of the categories plus one to add an additional object here and this object is going to be create a new category this is going to allow us to also have the final option of if you don't like all these categories you can create a new one and in order to do or in order to see what the user wants to do we're going to now say the category choice is going to be equal to input and then if category Choice equals length of the category ories if that is the case uh actually plus one this is what we did up here so if we have this particular choice what we're going to do is we're going to create a new category so the category is going to be equal to enter the new category name and otherwise if that is not the case provided we have a valid um input we're just going to assume now that the user is not going to enter a number that does not exist uh but provided that that is the case provided the input is valid we're going to say category equals categories category Choice minus one and from that index zero so the actual category um and for this we actually need to make this an integer otherwise it won't work there you go now you could also as I said um check if that is less than length of categories because otherwise you would say invalid solution but we're not going to take care of all the uh edge cases of all the ceptions that can arise because that would just not focus too much on the essence here but theoretically if you want to do that exception handling you would just have to say l if category choice is less than or less or equal to the length of categories and then process it otherwise um and of course above zero it's also important um and otherwise say that it's invalid and exit the script but you would have to do that on a bunch of occasions here so as I said we're not going to do that uh once we have that we're going to ask for the price so the price is going to be input enter the price of the expense and once all of this is done we're going to say cursor. execute and we're going to insert into expenses the date the description the category the price the following values which are going to be we're going to use a secure prepare prepared statement so question marks to avoid any SQL injections and here we're going to pass the date the description the category and the price and it's fine to pass the string because it's going to automatically be typ cast into a date or into a price that's not a problem and finally we just say connection. commit to commit the changes and that's it for the choice one for the choice two to actually display the summary to actually display the expenses um we're going to keep it simple we're going to have two options so select an option and the two options are going to be one view all expenses and then to view monthly expenses by category and of course you can add more um customized options like weekly expenses or yearly expenses or all expenses by category I'm just going to use those two and I think if you follow along with a code you will be able to change them or add additional options here so we're going to say here the view choice is going to be equal to input um yeah actually just to input we don't need to ask for anything specific here and we're going to again turn this into an integer and we're going to say if view Choice equals to one is going to be one thing and L if view Choice equals to two is going to be another thing in this case I'm going to once again add an else with an exit just because it's simple and we don't need to do too much to prevent uh wrong input being processed and for the first option we just have to view everything so we're going to say cursor. execute select everything from expenses that's quite simple and then we're going to say expenses is going to be cursor. fetchall and then for expense in expenses we can just print the full expense that's quite simple and if we want to have it by month and by category we're going to get the month and the category so we're going to ask first of all input enter the month mm then we also want to know which year of course because I cannot just say January I need to know January 2021 for example so enter the year as well with four years and then we're going to also say um actually we don't need to enter the category because we're going to list all the categories but we're going to group by category that's what we want do here so we're going to say here now cursor. execute and we're going to say select category sum of price so we're going to aggregate here in the SQL statement so we're going to say category the sum of the price column from expenses this is now a little uh SQL magic here uh if you're a beginner this might be magic otherwise it's quite simple uh where where the not actually the category sorry with the string format of the time St strf time we're going to get the month so percent M actually we need to use single quotations here to make this happen so month of the date is going to be equal to a question mark again a prepared statement we're going to inject later on um and Str strf time year of the date here has to be uh also equal to a question mark maybe can I can I maybe do this on a new line there you go this should work or maybe let's do it do it as a multi-line string to have a better overview here there you go ant uh actually not ant this was equal to another question mark and then we have the statement Group by category so basically what we do is we select the category and the price sum for this category we Group by the category so we sum up the price column uh for each category where the month is equal to something and the year is equal to something and this something will now be passed here as a tuple month and year that were entered here again we're not going to check if the input is correct we're just going to assume that the user enters something valid um and then as a result we're going to say expens equals cursor. fetchall for expense in expenses we're going to print an FST string where the category is going to be expense zero because that's the first thing that we ask for here and then the total is going to be expense one which is the second thing the sum of the price per category uh that's it and once we have that all we need to do here is after this um after all of this here we're going to say repeat equals input would you like to do something else and we can say yes or no maybe you want to add back slash in and then we can say if repeat do lower is equal to yes we're going oh actually it's not equal to yes sorry uh if it's not equal to yes we're going to break out of the loop otherwise we're just going to repeat so if we enter a no or an N or anything else but a yes so everything else better a y we're going to break out of the loop otherwise we're going to repeat the process and in the end we want to close the con Connection connection. close so if we didn't do any mistakes here or if I didn't do any mistakes here this should work we should be able to enter a new expense let's say we want to enter 2020 January 12th enter the description uh I don't know bananas the category we're going to create a new one it's going to be food and the price I bought quite a lot so $150 would you like to do something else yes I want to enter a new expense 2020 January 5th want to enter uh I don't know skateboard that I bought is going to be you can see I can choose now food or I can create a new category I will name it fun and then I will say $300 and then I would like to add one more enter a new expense or actually two more because I want to show some stuff here I'm going to enter one more in 2020 J January let's say 27th it's going to be uh groceries it's going to be food so I'm going to just pick one and it's going to be 120 and then I would like to do one more thing enter new expense let's say 2021 February 10th and then this is going to be I don't know again bananas food $20 and I would like to do something else I would like to view the expenses summary now so view all expenses there you go we can see a list of all the uh all the stuff that we have here and I would like to do something else I would like to also show monthly expenses by category the year uh the month is going to be January so 01 and the year is going to be 2020 and you can see now here we have category food a total of 270 and category fun a total of 300 so so this is how you build a simple expense tracker in Python so that's it for today's video I hope you enjoyed it and I hope you learned something if so let me know by hitting a like button and leaving a comment in the comment section down below and of course don't forget to subscribe to this Channel and hit the notification Bell to not miss a single future video for free other than that thank you much for watching see you in the next video and bye [Music]