In [ ]:

    

import pandas as pd
import numpy as np
import datetime
import matplotlib.pyplot as plt

# read Excel
excel = pd.ExcelFile('~/onedrive/Documents/Money1.2.xlsx')

uk = excel.parse('UK')
us = excel.parse('US')

def tidyxacts(df):
    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'])

def add_date_info(df):
    """
    Adds columns for year, month, quarter, week number
    """
    df['Year'], df['Month'] = df['Date'].dt.year, df['Date'].dt.month
    df['Quarter'], df['Week'] = df['Date'].dt.quarter, df['Date'].dt.week

def money_usd(x):
    """
    Format like money
    """
    return "${:,.2f}".format(x)
    
def money_gbp(x):
    """
    Format like money
    """
    return "£{:,.2f}".format(x)
    
tidyxacts(uk)
add_date_info(uk)
tidyxacts(us)
add_date_info(us)

"""
Weekly

    Top 10 expenses that week
    Balance of main accounts
    Week on week change
    Date of last update (or most recent transaction)

"""

today = datetime.date.today()


#table = pd.pivot_table(df,index=["Currency","Master Category","Sub Category"], columns=["Year","Month"], values=["Net"],aggfunc=np.sum,fill_value=0,margins=True)

#table.query('Currency == ["GBP"]')


    

In [ ]:

    




    

In [ ]:

    

# Set date as index, then can slice on it
uk2 = uk.set_index(['Date'])
uk2["2015-Jan-1":"2015-Feb-1"].head()


    

In [ ]:

    

# Just June 2016
uk2["2016-Jun"].head()


    

In [ ]:

    

pd.pivot_table(uk,index=["Account"], values=["Net"],aggfunc=np.sum)


    

In [ ]:

    

pd.pivot_table(uk,index=["Master Category","Sub Category"], columns=["Year","Month"], values=["Net"],aggfunc=np.sum,fill_value=0,margins=True)