Calculate Your Categorized Expenses for Tax Purpose.
The purpose of this note (i.e. code) is to calculate expenses (revenues) by categories. For the context, read the notes from
At this point, you have spreadsheets for all of your bank accounts and credit cards. You need to merge them into one master sheet. Make sure that you align/match the columns properly. Some cards will use two columns for debit and credit. Others will just use one with +ve and -ve signs. You will need to separate them into two fields. The master spreadsheet as this code will use used three columns such as ‘Expense Debit’, ‘Credit’, ‘All Money’.
First, you need to find the list of expenses and the associated categories and subcategories. You will also need to put thoughts and decide what goes on which expense categories. A simple way is keep this in your head or in paper/note, and use them in the code. The simple way.
Well if you are really making a software then it has to be way more structured. A better way to do can be: Put all categories and subcategories in a JSON file (can be enough), or in an xml file (considered worse than JSON), or in a database table such as MySQL/SQL server table (can be seen as overkill, however, depending — you might find it easier to update (based on size, your skills, tool availability, or writing an UI and DB update code) ). Sure, the last sentence got messy — you know what I mean. I am not trying to be perfectionist here.
The same way, you can keep what goes into what category in JSON/XML/Database. You might want to think based on expense description based on which words or cards or accounts — what will go where. Then write the code to assign categories and calculate sum by categories.
The code will simply look for words in the description and based on search result, the category column will be updated. Then the sum will be based on the categories. Yes, you can assign subcategory the same way. And then calculate sum of subcategories.
# some simple code are given below
import os; import glob; import pandas as pd; import math; data_folder = ‘./’; os.chdir(data_folder);
# read data, verify data manually df = pd.read_csv(‘to_process_all_combined_transactions_assign_category.csv’) df.head(3)
#Save to a temp file, to be safe df.to_csv(‘temp_to_process_all_combined_transactions_assign_category.csv’);
# read data, verify data manually df = pd.read_csv(‘temp_to_process_all_combined_transactions_assign_category.csv’) df.head(3);
# Find Donation amount : FB Fundraiser
#check
df[df[‘Description 1’].str.contains(‘FBFUNDRAISER’, na = False, case = False )][{‘Description 1’, ‘Card Owner’, ‘Card’, ‘Expense Debit’}]
# assign category df.loc[df[‘Description 1’].str.contains(‘FBFUNDRAISER’, na = False, case = False ), ‘Category’] = ‘Donation’
# keep saving if you want df.to_csv(‘temp_to_process_all_combined_transactions_assign_category.csv’);
# Donation Sum donation = sum(df[df[‘Description 1’].str.contains(‘FBFUNDRAISER’, na = False, case = False )][‘Expense Debit’]);
print(‘Donation from Business Cards:’, donation);
# Domain and Hosting Expense
df[df[‘Description 1’].str.contains(‘AWS’, na = False, case = False )][{‘Description 1’, ‘Card Owner’, ‘Card’, ‘Expense Debit’}]
df.loc[df[‘Description 1’].str.contains(‘AWS’, na = False, case = False ), ‘Category’] = ‘Domain and Hosting’
df[df[‘Description 1’].str.contains(‘HOSTGATOR’, na = False, case = False )][{‘Description 1’, ‘Card Owner’, ‘Card’, ‘Expense Debit’}]
df.loc[df[‘Description 1’].str.contains(‘HOSTGATOR’, na = False, case = False ), ‘Category’] = ‘Domain and Hosting’
df[df[‘Description 1’].str.contains(‘godaddy’, na = False, case = False )][{‘Description 1’, ‘Card Owner’, ‘Card’, ‘Expense Debit’}]
df.loc[df[‘Description 1’].str.contains(‘godaddy’, na = False, case = False ), ‘Category’] = ‘Domain and Hosting’
df[df[‘Description 1’].str.contains(‘VULT’, na = False, case = False )][{‘Description 1’, ‘Card Owner’, ‘Card’, ‘Expense Debit’}]
df.loc[df[‘Description 1’].str.contains(‘VULT’, na = False, case = False ), ‘Category’] = ‘Domain and Hosting’
df[df[‘Category’].str.contains(‘Domain’, na = False, case = False )][{‘Description 1’, ‘Expense Debit’}]
domain_hosting = sum(df[df[‘Category’].str.contains(‘Domain’, na = False, case = False )][‘Expense Debit’])
print(‘Domain from Business Cards:’, domain_hosting);
df.to_csv(‘temp_to_process_all_combined_transactions_assign_category.csv’);
# Section Find all Car Gas Expenses
df.loc[ df[‘Description 1’].str.contains(‘ESSO’, na = False, case = False ), ‘Category’ ] = ‘Car Gas’; df.loc[ df[‘Description 1’].str.contains(‘SHELL’, na = False, case = False ), ‘Category’ ] = ‘Car Gas’; df.loc[ df[‘Description 1’].str.contains(‘MOBIL Toronto’, na = False, case = False ), ‘Category’ ] = ‘Car Gas’; df.loc[ df[‘Description 1’].str.contains(‘Petro’, na = False, case = False ), ‘Category’ ] = ‘Car Gas’;
# Car Gas Sum df[df[‘Category’].str.contains(‘Car Gas’, na=False, case=False)]
sum(df[df[‘Category’].str.contains(‘Car Gas’, na=False, case=False)][‘Expense Debit’])
My goal was not to write great articles; however, share little bit of stuff that I have
*** . *** *** . *** . *** . ***
Sayed Ahmed
BSc. Eng. in Comp. Sc. & Eng. (BUET)
MSc. in Comp. Sc. (U of Manitoba, Canada)
MSc. in Data Science and Analytics (Ryerson University, Canada)
Linkedin: https://ca.linkedin.com/in/sayedjustetc
Training Courses: http://Training.SitesTree.com
Blog: http://Bangla.SaLearningSchool.com, http://SitesTree.com
8112223 Canada Inc/Justetc: http://JustEtc.net
Youtube: https://www.youtube.com/channel/UCHrtq0pV8mEuTLGd2-Lp9ig/playlists
Medium: https://medium.com/@SayedAhmedCanada
Facebook Groups/Forums to discuss (Q & A):
https://www.facebook.com/salearningschool
https://www.facebook.com/banglasalearningschool
https://www.facebook.com/justetcsocial
Get access to courses on Big Data, Data Science, AI, Cloud, Linux, System Admin, Web Development and Misc. related. Also, create your own course to sell to others. http://sitestree.com/training/
Join, Discuss, and Thrive:
Finance: https://www.facebook.com/groups/1792339521056034
Bangladesh and Canada: https://www.facebook.com/groups/398414183875009
Bangladesh: IT & Eng: Canada: https://www.facebook.com/groups/480297675634630
BUETians in Ontario: https://www.facebook.com/groups/371982239854027
Worldwide IT: https://www.facebook.com/groups/28362061867
JustEtc: https://www.facebook.com/JustETC-Technologies-Inc-289864738261
