In [ ]:
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from dateutil.parser import parse
import seaborn as sns
import matplotlib.pyplot as plt
from pandas.tseries.offsets import *
%matplotlib inline
%load_ext autoreload
%autoreload 2
pd.set_option('float_format', '{:.2f}'.format)
plt.style.use('seaborn-colorblind')
cm = sns.light_palette("green", as_cmap=True)
In [ ]:
excel = pd.ExcelFile("C:\\Users\\aowd\OneDrive - Chevron\\Special Projects\\littlefinger\\Money1.2.xlsx")
uk = excel.parse('UK')
us = excel.parse('US')
funds = excel.parse('Investments')
prices = excel.parse('Funds')
forex = excel.parse('Forex')
now = datetime.now()
weekago = now - timedelta(7)
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
rows = ['Car', 'Entertainment', 'Fees', 'Food', 'Gift', 'Health', 'Home', 'Kids', 'NBC', 'Pets', 'Shopping', 'Travel']
In [ ]:
def tidyxacts(df):
"""
Fix formats of columns for transactions in dataframe df
Parameters
----------
df : Dataframe
Returns
-------
Date formatted as Date
Amounts as numeric
Adds columns for year, month, quarter, week number
Sets date as index
"""
df['Date'] = pd.to_datetime(df['Date'])
df['Inflow'] = pd.to_numeric(df['Inflow'])
df['Outflow'] = pd.to_numeric(df['Outflow'])
df['Net'] = pd.to_numeric(df['Net'])
# df['Master Category'] = df['Master Category'].astype("category")
# df['Sub Category'] = df['Sub Category'].astype("category")
# df['Year'], df['Month'] = df['Date'].dt.year, df['Date'].dt.strftime('%b')
df['Year'], df['Month'] = df['Date'].dt.year, df['Date'].dt.month
df['Quarter'], df['Week'] = df['Date'].dt.quarter, df['Date'].dt.week
df.set_index(['Date'], inplace=True)
def xact_type(df):
"""
Adds transfer, expense, income category column to df
"""
def categorise(row):
if row['Master Category'] == "Transfer":
return 'Transfer'
if row['Master Category'] == "Income":
return 'Income'
return 'Expense'
df['Type'] = df.apply(lambda row: categorise(row), axis=1)
def summary_tables(df, currency, type):
tidyxacts(uk)
tidyxacts(us)
xact_type(uk)
xact_type(us)
In [ ]:
forex.set_index(['Date'], inplace=True) # Set date to be index
forex = forex['Exchange Rate'] # Only keep exchange column
usex = us.join(other=forex, how='outer')
usex['Exchange Rate'].fillna(method='ffill', inplace=True)
usex['Net GBP'] = usex['Net'] / usex['Exchange Rate']
usgbp = usex.drop(['Net', 'Exchange Rate'], axis=1)
usgbp.rename(columns={'Net GBP':'Net'}, inplace=True)
allgbp = pd.concat([uk, usgbp], axis=0)
In [ ]:
summary = pd.pivot_table(allgbp['2017'].query('Type == ["Expense"]'), index=['Master Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
summary.columns = months[:now.month] # Renames to months based off current month
summary = summary * -1 # We're just looking at expenses
summary['Total'] = summary.sum(axis=1) # adds total to categories
summary['Average'] = summary.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
summary.loc['Total']= summary.sum() # adds total row at bottom
summary['EOY'] = summary['Total'] + (summary['Average'] * (now.month - 1))
summary.style.format("£{:,.2f}")
In [ ]:
# Expense per month Heatmap
# Ignoring housing and total rows - tend to mask the rest
summary.style.background_gradient(cmap=cm, subset=pd.IndexSlice[rows,summary.columns[:-2]], axis=1).format("£{:,.2f}").set_caption('Monthly Summary')
In [ ]:
income = pd.pivot_table(allgbp['2017'].query('Type == ["Income"]'), index=['Sub Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
income.columns = months[:now.month] # Renames to months based off current month
income['Total'] = income.sum(axis=1) # adds total to categories
income['Average'] = income.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
income.loc['Total']= income.sum() # adds total row at bottom
income['EOY'] = income['Total'] + (income['Average'] * (now.month - 1))
income.style.format("£{:,.2f}")
In [ ]:
# Savings Rate
1- summary.at['Total', 'EOY']/income.at['Total', 'EOY']
In [ ]:
usi = pd.pivot_table(us['2017'].query('Type == ["Income"]'), index=['Sub Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
usi.columns = months[:now.month] # Renames to months based off current month
usi['Total'] = usi.sum(axis=1) # adds total to categories
usi['Average'] = usi.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
usi.loc['Total']= usi.sum() # adds total row at bottom
usi['EOY'] = usi['Total'] + (usi['Average'] * (now.month - 1))
usi.style.format("${:,.2f}")
In [ ]:
use = pd.pivot_table(us['2017'].query('Type == ["Expense"]'), index=['Master Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
use.columns = months[:now.month] # Renames to months based off current month
use = use * -1
use['Total'] = use.sum(axis=1) # adds total to categories
use['Average'] = use.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
use.loc['Total']= use.sum() # adds total row at bottom
use['EOY'] = use['Total'] + (use['Average'] * (now.month - 1))
use.style.format("${:,.2f}")
In [ ]:
uki = pd.pivot_table(uk['2017'].query('Type == ["Income"]'), index=['Sub Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
#uki.columns = months[:now.month] # Renames to months based off current month
uki.columns = months[:now.month - 1]
uki['Total'] = uki.sum(axis=1) # adds total to categories
uki['Average'] = uki.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
uki.loc['Total']= uki.sum() # adds total row at bottom
uki['EOY'] = uki['Total'] + (uki['Average'] * (now.month - 1))
uki.style.format("£{:,.2f}")
In [ ]:
uke = pd.pivot_table(uk['2017'].query('Type == ["Expense"]'), index=['Master Category'], columns=['Month'], values=['Net'], aggfunc=np.sum, fill_value=0)
uke.columns = months[:now.month] # Renames to months based off current month
uke = uke * -1
uke['Total'] = uke.sum(axis=1) # adds total to categories
uke['Average'] = uke.iloc[:, :-2].mean(axis=1) # adds average column for categories, iloc to ignore total col, -2 to only have full months
uke.loc['Total']= uke.sum() # adds total row at bottom
uke['EOY'] = uke['Total'] + (uke['Average'] * (now.month - 1))
uke.style.format("£{:,.2f}")