How I AUTOMATE my FINANCES USING PYTHON
Vložit
- čas přidán 8. 07. 2024
- Thank you to Mailgun for sponsoring this video! Try Mailgun today by using my link mailgun.com/internetmadecoder
Today, we will learn how you can automate your personal finances using PYTHON and Google Sheets.
Premium Resources to Become a Software Engineer from Scratch:
🚀 Zero to Software Engineer with no Degree - bit.ly/48pFOhB
🚀 FREE Course to Start Your Career in Software Development - coursecareers.com/a/internetm...
Make Python your (unpaid) financial manager!
📈 GOOGLE SHEETS TEMPLATE (copy this or create your own) - docs.google.com/spreadsheets/...
1. Acces your transactions as CSV files - further detail on reading CSV data in Python realpython.com/python-csv/
2. Access Google Sheets using the spread library - detailed instructions for how to set up the API if I go too fast in the video - docs.gspread.org/en/latest/oa...
3. Clean up data in Python using DATA SCIENCE
4. Extract data to Google Sheets via gspread
5. Customize the financial spreadsheet to your liking and make your app categorize transactions automatically!
CHAPTERS:
0:00 What you will learn
1:00 Set up Transaction data Access
3:00 Set up Google Sheets API Access
5:15 SPONSOR: Mailgun
6:25 Clean up data in Python using Data Science Magic
8:10 Make your app INTELLIGENT
9:28 Extract data to Google Sheets (using magic)
13:30 Make your app even better (ideas)
OTHER VIDEOS
💻 How I Learned to Code in 4 MONTHS - & Got a Software Engineer Job (no CS Degree) - • How I Learned to Code ...
⌨️ How I'm Teaching Myself Computer Science using Notion (OSSU) - • How I'm Teaching Mysel...
🖱 CS50 FULL REVIEW - Best Course to Learn Computer Science in 2022? (edX, Harvard) - • CS50 FULL REVIEW - Be...
WHO AM I?
On this channel, my aim is to give you the tools, strategies and methods to learn to code effectively - according to science! In addition, I document my life as a self-taught software engineer.
DISCLAIMER: some of the links in the description may be affiliate links. If you purchase a product or service using the links that I provide I may receive a small commission. This is no extra charge to you! Thanks for supporting Internet Made Coder :)
Tags: python automation projects, automate the boring stuff with python, learn python, python data analytics, google sheets API, python finance, python csv tutorial, python automation, automate your life, learn python fast, python fundamentals, bash scripting, learn to code, coding projets, coding project ideas, python tutorial, desktop automation, coding tutorial, python 101, how to automate tasks for beginners - Věda a technologie
Thank you to Mailgun for sponsoring this video! Try Mailgun today by using my link Mailgun.com/internetmadecoder
Thanks for this video
Some suggestions: 1. Name the file 'hsbc_2022-06.csv' because it'll be easier to sort the files that way. 2. Don't use float for money, use Decimal from the decimal module to avoid rounding errors. 3. Lookup the categories in a dict using "category = CATEGORIES.get(name, 'other')". You can do something similar to lookup clearer names. 4. The best date format is YYYY-MM-DD because it's simpler to sort.
Thanks!
Floats are faster, and having rounding errors on them does not matter in this context - it's statistics, not business logic where you would want to be super-precise.
YYYY-MM-DD .... ew in most other cases lol who needs the year first!
@@Lockdown335 This format automatically sorts correctly. you can extend it by YYYY-MM-DD HH:MM:SS
@@Antash_ well you could put it the opposite way: we are talking about numbers so we want them to be precise, and we don't care if our script takes 10ms longer to run...right?
If anyone gets a cannot convert to float error, it's reading the column header which is a string. so add this line to skip the headers.
headers = next(csv_file)
for row in csv_reader:
This is awesome!! Recently I finished a python course,and was looking for projects to keep learning. I'll watch the rest of your channel with great interest :)
amazin!!
Thank you so much, I’m interested in AI application in automating the “boring” things and definitely your videos are helping out. Glad I discovered your channel! 👍
Awesome! It's so useful to me! This GSPREAD library is everything that i want.
THANK YOU SO MUCH!
Glad to hear that!
Some great suggestions like using Pandas to showcase data instead - you are most likely right, I wasn't aware of Pandas while making this! Also people have said Excel, I don't have Excel and I don't want to pay for it so hence I used Sheets.
You’re paying anyway by giving Google all of your financial transaction data.
Thanks for the awesome video footage! Now I know more about Python🥰
Few other items I ran into with Google Sheets. You might also need to activate Drive API and Sheet API after you get your JSON file. Thankfully the error messages for gspread are clear if you run into these issues.
thanks for the tips, yeah there are bound to be further issues to deal with with APIs, but as you said gspread seems to be pretty clear
Ayeee I was trying to figure out how to do this exact thing in JavaScript!
Just realized my bank is way behind because I've been coping am pasting my transactions instead of them just providing the option to download the data... Cool video!
Crazy powerful. Subscribed and liked, looking forward to your future content. More python tutorials like this man. You have me hook line and sinker. Loved this video sir.
Awesome, thank you!
you content is really inspiring for me, thanks for this video, please keep making projects like this, love you from India 😀.
Outstanding video, the project to track yo expenditures is both brilliant and very eye opening.. keep at it
oNe
Glad you enjoyed it!
Great video! New user of Python here. The program seems to work except only the last line of the csv file is added to the Google Sheets-file, anyone have any idea what could be the reason for this?
If/else seems like a terrible approach for categorizing. Use key value pairs to map name to category, then index off that to grab the category.
Could even put that as a sheet in your workbook so you can more easily maintain it from there.
Definitely gave me some ideas next time o take a stab at automating my budgeting workflow! Thanks for the inspiration!
I have a similar setup and that's exactly what I do.
My code imports rows of transactions (Description, Amount, Currency, etc.). I have a table called "Rules" with exactly the same columns, containing rows for all the transcations I want to match (say one row would for example describe a transaction with a certain description AND a specific amount). Then for each of these "search rows" I have rows that describe how the matched transactions should be updated (say add a tag/category, or even multiple rows to add multiple transactions for parts of the amount to split a transaction into different categories, etc.).
There is a column describing whether a row is a "search" (key) or an "output" (value) row. Then I match the corresponding search and output rows (key value pairs) with an additional ID column. With all this, updating the imported transactions is a relatively simple case of filtering for the "search" and "output" rows, and doing a series of "joins" (in R / dplyr / fuzzyjoin).
I don't know if this makes sense at all like this in text, but it's a super nifty solution and updating the "rules" table is incredibly straightforward once the code is in place. Maybe it helps someone!
you're probably right, there are definitely better ways to do many of the things here for sure. Going to be updating my program as time goes on!
I'm having trouble moving my json file into the correct directory (on mac).
Every time I try and make a new directory I get the error: "mkdir: /.config: No such file or directory"
The command I use is: "mkdir ~ /.config/gspread" and then I intend to move the file to that directory. Any tips or help would be appreciated.
Did you figure this out? I’m having the same exact issue
Been watching your amazing videos for a while now, they are helping immensely on my coding journey. What resources did you use to learn this automation in python. Please could you provide them with free options as well for students like us. Thanks and keep up the amazing work you're awesome man😎👨💻
Python for Everybody is a great free course to learn Python (you can audit it for free) - check my How to Learn Python fast video - then I just think about problems I’d like to automate and google myself through it
@@InternetMadeCoder Thank you Tuomas for the reply and the information, your content is the best . Thanks for everything will definitely check out the resources.
Does this sync your transaction data to your google sheet live? or do you need to export new transaction every time?
Yoooo! I've been watching your videos for a while. I really love it. It inspires me to learn coding. Keep up with the great work man!
Awesome, thank you! Glad to hear!
At 4:14 how exactly do you call the json file?
Followed steps exactly, get a Refresh Error during runtime while trying to run the sh=sa.open line, I have the sheet name correctly referenced and did everything as instructed in the video. Any tips?
When I run the code at the end it only loads one row into the Google Sheet, it doesn't continually load all the the rows from the CSV. Did you skip a section of the code at the end?
All this is much easier in Excel. Just Grab CSV there and filter data
@@BobAg_ my banks have an API?
You probably should have used the pandas library, that would have made the whole parsing much easier
you're probably right, tbh I had never used it before so I just went with a way that was familiar to me. Great suggestion!
@@InternetMadeCoder I use python for the analysis of large sets of data in one of the university classes, it is impressive how easy it is to use pandas to calculate, plot and convert large sets
Can you do this in Excel instead of google sheets? And secondly is it possible to import into your spreadsheet in real time?
Mooooooore!!!!!!
Please
It’s sooooo interesting and useful🤤🤤🤤
Is there a link to download the code used in this video ?
Is that an idea of the app you building right now?
Thanks for these Video 👍
Can you make a video how to setup python. Thanks :)
Can seem to be able to acess your spreadsheet template. Is the link still valid? Any chance of getting a working link if that one expired? Cheers!
I have problem importing the sheet on python, import gspread, any suggestion
the picture of the cute dog sold me
it's my secret weapon
Thaaaaanks bro
petl is a great python library for grabbing data from csv's, cleaning the data, aggregating the data and creating reports. I personally would not put any of my finance data on google.
Really love this video, I’m still very new to python, do you have any recommendation on how to create sub categories, in addition is there a way to sort for debits and credits in the csv for example if value is negative then transaction is debit is positive then credit
If not totally fine great amazing what you’ve done with this
Why row 1,23 when it’s Columns not rows (left to right) if you want date it’s the left column. No?
This can easily be done with SUMIFS and IF statements inside Sheets, no need for Python I think. I was really hoping to see a full automation from bank download into reports or some kind of withdrawal system to different account
I mean, anything is doable. Feasibility is the question here. I think this could have been a more in-depth as well. I was hoping to make some sort of machine learning to allow the transactions to sort themselves, instead of hardcoding the categories.
Liked and subscribed!!!
Haven’t you heard of panda’s framework in python?
Hey loving the tutorial but i just have one question, i don't know if I'm stupid but where can i find the .config/gspread folder to place the json file in? Because i just can't seem to find it. P.s I'm on mac
I know this is a year later, but I had to create the gspread folder within the .config directory(folder)
try running the command mv ~/Downloads/service_account.json ~/.config/gspread/service_account.json and if the folder doesn't exist mkdir -p ~/.config/gspread
Love the Graham stephan influence but in your own style.
Haha never thought about it but I guess I’m subconsciously influenced by him as I watxh him so much!
@@InternetMadeCoder he’s entertaining and informative. Good dude to watch
Could you do this with Excel too?
I have a question, currently a few seconds into the video, and such nice editing. Only noticed the 30 cuts in approx 30 seconds since i am getting into this as well.
What video editor do you use and are there any free ones you recommend?
I use Final Cut Pro wih some of the more complex graphics/edits done in After Effects. A recommended free editor is Davinci Resolve as it's very powerful even though it's free. iMovie is another great beginner option.
@@InternetMadeCoder Thanks a lot! Appreciate it :)
I don’t even know how to use python at all. I been searching what I can do with coding and how to apply into my life and definitely financing is something very important because sometimes I’m scared to look at my bank accounts out of fear of spending money maybe if I code this app for myself it can make everything about my finances clear.
So I do I need to watch a Separate video on how to use python?
You can skip Gspread/python and get something similar all with just importing your CSV's directly into sheets and using App Script & Gdrive
yeah probs many ways to do it!
Hey bro!
Can you make series of python programming starting from zero😊
I'm planning on this!
Interesting
Great video! for the data clean part 6:29 would be cool using pandas.🐼
Hey, what color-theme do you use? it looks really nice
I think it’s called Midnight blue
Use API is a much better route, or just use mint
What if you want to do it in Excel vs Google Sheets?
Excel has an api/library you can use too I believe
Good video but please show your screen more instead of showing your face. We can understand you just as well even if we are just looking at your code. Maybe add your camera angle to one of the corners so we can always be looking at your screen.
Cool!
what is your main programming language java script or python?
Python is what I use most atm
Hi, this is a fantastic video, and I like it. However, the pace is too fast, so can you slow down a little bit when you are showing your code so we can follow up with your pace?
you can always pause
Turn the playback speed on CZcams down is also a good method. Once I found that option I've used it A LOT👍
@@brucebarry1204 OK. Good suggestion, thank you.
All my data in the csv file is seperated using ; instead of ,
Any idea how to fix this?
nvm I learned how to use google
I thought excel is enough for most calculations through the GUI and if that is not enough you have VBS.
Mine is giving the following error - "IndexError: list index out of range". I've checked the csv file and the columns are properly identified 0, 1 and 2.
Anybody know why?
I've tried searching for a solution online but didn't come to any conclusion.
Thank you in advance
I had the same error and it came from the fact that the delimiter in my csv file were semi-colons instead of comas. So I added the delimiter in the function : csv.reader(csv_file, delimiter=';'). Hope this helps :)
My bank app doing it all lol
But thanks🧡
python 4ever 😍
Yaass!! 🐍🔥
Shouldn't you store the categories and keys in an extra file instead of hardcoding them?
probably
This is a cool video, but you only show changes to errors for a second or two, found my self having to time the pause button to see what you did. Other than that great video, and thank you!
That's a very Welsh accent you have there!
…😅
First I thought there was a bank that let you do that with api's.
i think you have to explain fashion but not coding
I feel like I'm missing something. Can't you create an excel template to do all this easier? I'm honestly curious what I'm missing. I'm not downing the content at all
Excel costs money and I don't have it, also not sure how you could automatically pull all that data with Excel. But of course, if you are more familair with Excel than Python and you can do it then obvs use whatever tool you like. I prefer coding.
@@InternetMadeCoder Thank you. Well said answer.
Would be way easier using pandas library
maybe
The csv's from my bank are completely useless for this, Everything I don't need gets a collumn and all the important information is packed in one collumn in non standardized text.
oh yeah I had that with one of my banks...I had to use some ingenuity and a million if statements to make it work lol. Nice challenge but annoying
Where are you from bro ?
Can't you tell from appearence and accent that he is northern European
I know it defeats the whole purpose, but couldn't you also just directly open the csv in sheets?
hmm maybe. I guess it's about whether you prefer to write Sheets functions to clean up the data or Python. I'm thinking Python is easier (at least for me because that is what I'm familiar with)
Why wouldn't I do this in Excel which would be much easier?
if you know how to use Excel better than Python, and you're paying for it anyway, then go for it!
I'm too late to comment in this video!
never too late😎
@@InternetMadeCoder 🙂👍
Use pandas
good idea too, I haven't familiarized myself with pandas yet
@@InternetMadeCoder It's really good for dealing with tabular data like this E.g. about 15 lines of your code in 3 with pandas:
SUBSCRIPTION_NAMES = {...}
df = pandas.read_csv('hsbc_may.csv', names=["date","transaction","amount"]) # read in csv file
df["category"] = df["transaction"].apply(lambda x: "subscription" if x in SUBSCRIPTION_NAMES else "other") # categorize
On top of that it has lots of functionality for filtering and processing the data.
Hi, first project, getting this error, please help
SyntaxError: invalid syntax
[Finished in 0.201s]
Hey man,
I know the intent is more to show the tools than the code itself, but as this is a tutorial, I feel like I'm forced to make a few comments for Code Quality....
Please separate every every 'functionality' into a function, this will turn your code much easier to debug in a later day.
Please don't make a lot of if statments like that... That is just ugly and unhelpfull... (you could separate the words by space and create another lookup table for matching those two)
Sugestion: Why didn't you use pandas? It has read_csv method and is much better for handeling tabular data.
If you are not familiar with Clean Code, check the book, by 'Uncle Bob', one of the creators of the Agile methodology.
Shame that my bank only allows pdf downloads!
Crap, I smashed the button :(
video does not even show the result at the end. too bad
Oh no, I'm not gonna let any code I've written near my finances
Prolly smart
Or you can just copy paste the data :-))))
Any bank that allows automated logins without 2FA or better is not a bank you want to be banking with.
My bank gives back pdf files
damn, yeah some banks may not have them in csv
First
thanks, please make the videos more in the code and less on you, cause I just want to see the code, or you can use facecam
I want to see the code, not your reactions
show more screen of code instead of showing your face
Stop showing ur face instead of the code
Vídeo not worth it.
nah, why bother touching trash, including money
Using worksheet.appendrows() means you can skip using sleep() and write all the data at once