Mar 21

Python: Generate Expense Report from RBC MasterCard Transaction Data

Python: Generate Expense Report from RBC MasterCard Transaction Data

For the context read the following two first

Python: Read RBC Canada: Mastercard PDF Statement Transaction Data into CSV file

By Sayed Ahmed

medium.com


Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time

By Sayed Ahmed

medium.com

After, you have created the merged i.e. combined csv file, you might want to create reports for your purpose. For example, you can calculate how much was spent on Amazon.ca. The code example will show similar how much was spent on Amazon.ca.

Just by changing the variab;e

spend_calculation_for = ‘AMAZON.CA’;

you will be able to calculate how much was spent on other places such as BestBuy, Ebay or similar. The code is case sensitive; Hence, you need to provide the value exactly for the above mentioned variable.

Statements such as dollar, no_dol_sign_dollar, you might want to use print for them. This code was written in Jupyter Notebook and then was exported to .py file.

#!/usr/bin/env python
# coding: utf-8# In[1]:import os;
import glob;
import pandas as pd;
data_folder = ‘./’;
os.chdir(data_folder);# In[2]:Spend_calculation_for = ‘AMAZON.CA’;# In[3]:df = pd.read_csv(‘to_process_rbc_mastercard_data_combined.csv’)
df.head(3)# In[4]:transactions = df[ df[‘2’].str.contains(Spend_calculation_for) ]# In[5]:dollar = transactions[0:50][‘3’]
dollar = sorted(dollar)
dollar# In[6]:no_dol_sign_dollar = [x.strip(“$”) for x in dollar]
no_dol_sign_dollar# In[7]:no_dol_sign_dollar = [float(x.replace(“-$”, ‘-’)) for x in no_dol_sign_dollar]
no_dol_sign_dollar# In[8]:import math
total_spent = sum(no_dol_sign_dollar)
#sum = sum + [ float(x[1:]) for x in aliexpress_dollar ]
total_spent# In[9]:# just spent without refund
pos_no_dol_sign_dollar = [ x if x > 0 else 0 for x in no_dol_sign_dollar]
pos_no_dol_sign_dollar[0:10]# In[10]:spent = sum(pos_no_dol_sign_dollar)
spent# In[11]:# just spent without refund
neg_no_dol_sign_dollar = [ x if x < 0 else 0 for x in no_dol_sign_dollar]
#if (len(neg_no_dol_sign_aliexpress_dollar)) > 0:
# neg_no_dol_sign_aliexpress_dollar[-1], neg_no_dol_sign_aliexpress_dollar[-2], neg_no_dol_sign_aliexpress_dollar[-3]
neg_no_dol_sign_dollar# In[12]:got_refund = sum(neg_no_dol_sign_dollar);
got_refund# In[14]:total_spent = spent — abs(got_refund);
total_spent# In[ ]:


***
Note: Older short-notes from this site are posted on Medium: https://medium.com/@SayedAhmedCanada

*** . *** *** . *** . *** . ***
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

Blog: http://Bangla.SaLearningSchool.com, http://SitesTree.com
Training Courses: http://Training.SitesTree.com

Facebook Groups/Forums to discuss (Q & A):
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/