\nCode examples in relation to writing Tax and Financial Software<\/em>medium.com<\/a>
\n
![\"1*jpQZSf1XYnLB7bx2DlaOsg.jpeg\"](\"https:\/\/i0.wp.com\/cdn-images-1.medium.com\/max\/800\/1%2AjpQZSf1XYnLB7bx2DlaOsg.jpeg?w=750&ssl=1\")
<\/p>\nAt 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 \u2018Expense Debit\u2019, \u2018Credit\u2019, \u2018All Money\u2019.<\/p>\n
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.<\/p>\n
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\u200a\u2014\u200ayou 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\u200a\u2014\u200ayou know what I mean. I am not trying to be perfectionist here.<\/p>\n
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\u200a\u2014\u200awhat will go where. Then write the code to assign categories and calculate sum by categories.<\/p>\n
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.<\/p>\n
# some simple code are given below<\/strong><\/p>\n *** . *** *** . *** . *** . ***<\/p>\n Sayed Ahmed<\/strong> Training Courses: <\/strong>http:\/\/Training.SitesTree.com<\/a> <\/em> Facebook Groups\/Forums to discuss (Q & A): <\/strong><\/em> https:\/\/www.facebook.com\/banglasalearningschool<\/a> 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. <\/em>http:\/\/sitestree.com\/training\/<\/a><\/p>\n Join, Discuss, and Thrive:<\/strong> Bangladesh and Canada: https:\/\/www.facebook.com\/groups\/398414183875009<\/a><\/p>\n Bangladesh: IT & Eng: Canada: https:\/\/www.facebook.com\/groups\/480297675634630<\/a><\/p>\n BUETians in Ontario: https:\/\/www.facebook.com\/groups\/371982239854027<\/a><\/p>\n Worldwide IT: https:\/\/www.facebook.com\/groups\/28362061867<\/a><\/p>\nimport os;\nimport glob;\nimport pandas as pd;\nimport math;\ndata_folder = \u2018.\/\u2019;\nos.chdir(data_folder);<\/pre>\n
# read data, verify data manually\ndf = pd.read_csv(\u2018to_process_all_combined_transactions_assign_category.csv\u2019)\ndf.head(3)<\/pre>\n
#Save to a temp file, to be safe\ndf.to_csv(\u2018temp_to_process_all_combined_transactions_assign_category.csv\u2019);<\/pre>\n
# read data, verify data manually\ndf = pd.read_csv(\u2018temp_to_process_all_combined_transactions_assign_category.csv\u2019)\ndf.head(3);<\/pre>\n
# Find Donation amount<\/strong> : FB Fundraiser<\/strong><\/pre>\n
#check<\/strong><\/pre>\n
df[df[\u2018Description 1\u2019].str.contains(\u2018FBFUNDRAISER\u2019, na = False, case = False )][{\u2018Description 1\u2019, \u2018Card Owner\u2019, \u2018Card\u2019, \u2018Expense Debit\u2019}]<\/pre>\n# assign category<\/strong>\ndf.loc[df[\u2018Description 1\u2019].str.contains(\u2018FBFUNDRAISER\u2019, na = False, case = False ), \u2018Category\u2019] = \u2018Donation\u2019<\/pre>\n
# keep saving if you want<\/strong>\ndf.to_csv(\u2018temp_to_process_all_combined_transactions_assign_category.csv\u2019);<\/pre>\n
# Donation Sum<\/strong>\ndonation = sum(df[df[\u2018Description 1\u2019].str.contains(\u2018FBFUNDRAISER\u2019, na = False, case = False )][\u2018Expense Debit\u2019]);<\/pre>\n
print(\u2018Donation from Business Cards:\u2019, donation);<\/pre>\n
# Domain and Hosting Expense<\/strong>\ndf[df[\u2018Description 1\u2019].str.contains(\u2018AWS\u2019, na = False, case = False )][{\u2018Description 1\u2019, \u2018Card Owner\u2019, \u2018Card\u2019, \u2018Expense Debit\u2019}]<\/pre>\ndf.loc[df[\u2018Description 1\u2019].str.contains(\u2018AWS\u2019, na = False, case = False ), \u2018Category\u2019] = \u2018Domain and Hosting\u2019<\/pre>\n
df[df[\u2018Description 1\u2019].str.contains(\u2018HOSTGATOR\u2019, na = False, case = False )][{\u2018Description 1\u2019, \u2018Card Owner\u2019, \u2018Card\u2019, \u2018Expense Debit\u2019}]<\/pre>\ndf.loc[df[\u2018Description 1\u2019].str.contains(\u2018HOSTGATOR\u2019, na = False, case = False ), \u2018Category\u2019] = \u2018Domain and Hosting\u2019<\/pre>\n
df[df[\u2018Description 1\u2019].str.contains(\u2018godaddy\u2019, na = False, case = False )][{\u2018Description 1\u2019, \u2018Card Owner\u2019, \u2018Card\u2019, \u2018Expense Debit\u2019}]<\/pre>\ndf.loc[df[\u2018Description 1\u2019].str.contains(\u2018godaddy\u2019, na = False, case = False ), \u2018Category\u2019] = \u2018Domain and Hosting\u2019<\/pre>\n
df[df[\u2018Description 1\u2019].str.contains(\u2018VULT\u2019, na = False, case = False )][{\u2018Description 1\u2019, \u2018Card Owner\u2019, \u2018Card\u2019, \u2018Expense Debit\u2019}]<\/pre>\ndf.loc[df[\u2018Description 1\u2019].str.contains(\u2018VULT\u2019, na = False, case = False ), \u2018Category\u2019] = \u2018Domain and Hosting\u2019<\/pre>\n
df[df[\u2018Category\u2019].str.contains(\u2018Domain\u2019, na = False, case = False )][{\u2018Description 1\u2019, \u2018Expense Debit\u2019}]<\/pre>\ndomain_hosting = sum(df[df[\u2018Category\u2019].str.contains(\u2018Domain\u2019, na = False, case = False )][\u2018Expense Debit\u2019])<\/pre>\n
print(\u2018Domain from Business Cards:\u2019, domain_hosting);<\/pre>\n
df.to_csv(\u2018temp_to_process_all_combined_transactions_assign_category.csv\u2019);<\/pre>\n
# Section Find all Car Gas Expenses<\/strong><\/pre>\n
df.loc[ df[\u2018Description 1\u2019].str.contains(\u2018ESSO\u2019, na = False, case = False ), \u2018Category\u2019 ] = \u2018Car Gas\u2019;\ndf.loc[ df[\u2018Description 1\u2019].str.contains(\u2018SHELL\u2019, na = False, case = False ), \u2018Category\u2019 ] = \u2018Car Gas\u2019;\ndf.loc[ df[\u2018Description 1\u2019].str.contains(\u2018MOBIL Toronto\u2019, na = False, case = False ), \u2018Category\u2019 ] = \u2018Car Gas\u2019;\ndf.loc[ df[\u2018Description 1\u2019].str.contains(\u2018Petro\u2019, na = False, case = False ), \u2018Category\u2019 ] = \u2018Car Gas\u2019;<\/pre>\n
# Car Gas Sum<\/strong>\ndf[df[\u2018Category\u2019].str.contains(\u2018Car Gas\u2019, na=False, case=False)] <\/pre>\n
sum(df[df[\u2018Category\u2019].str.contains(\u2018Car Gas\u2019, na=False, case=False)][\u2018Expense Debit\u2019])<\/pre>\n
My goal was not to write great articles; however, share little bit of stuff that I have\n<\/pre>\n
\n<\/em>
\nBSc. Eng. in Comp. Sc. & Eng. (BUET)<\/strong><\/em>
\nMSc. in Comp. Sc. (U of Manitoba, Canada)<\/strong><\/em>
\nMSc. in Data Science and Analytics (Ryerson University, Canada)<\/strong><\/em>
\nLinkedin<\/strong>: https:\/\/ca.linkedin.com\/in\/sayedjustetc<\/a>
\n<\/em><\/p>\n
\nBlog<\/strong>: http:\/\/Bangla.SaLearningSchool.com<\/a>, http:\/\/SitesTree.com<\/a><\/em>
\n8112223 Canada Inc\/Justetc<\/strong>: http:\/\/JustEtc.net<\/a><\/em>
\nYoutube: <\/em>https:\/\/www.youtube.com\/channel\/UCHrtq0pV8mEuTLGd2-Lp9ig\/playlists<\/a>
\nMedium: <\/strong><\/em>https:\/\/medium.com\/@SayedAhmedCanada<\/a><\/p>\n
\nhttps:\/\/www.facebook.com\/salearningschool<\/a><\/p>\n
\nhttps:\/\/www.facebook.com\/justetcsocial<\/a><\/p>\n
\nFinance: https:\/\/www.facebook.com\/groups\/1792339521056034<\/a><\/p>\n