Process CSV export from 'My Expenses' app, save in sqlite, and make pie chart / time series using matplotlib.

pexp.py

"""
Columns for reference:
cols = ('_split', 'Date', '_payee', 'Income', 'Expense', 'Category',
        'Subcategory', 'Notes', '_method', '_status', '_refnum', '_unknown')
"""

import csv
from datetime import datetime
import matplotlib.pyplot as plt
import os
import sqlite3
import sys
import random


def random_name(length):
    chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
    return ''.join(random.sample(chars, length))


def plot_timeseries(rows_iter):
    x_dates, y_values = [], []
    for row in rows_iter:
        x_dates.append(datetime.strptime(row[0], '%Y-%m-%d'))
        y_values.append(row[1])
    with plt.style.context('fivethirtyeight'):
        plt.figure(figsize=(len(x_dates)//4, 4))
        plt.plot(x_dates, y_values)
    plt.savefig('exp-ts.png')


def plot_pie(rows_iter):
    vals, labels = [], []
    colors = ['yellowgreen', 'gold', 'lightskyblue', 'lightcoral',
              'violet', 'green', 'orange', 'indigo', 'red', 'blue']
    explode = [0.01]
    for row in rows_iter:
        labels.append(row[0])
        vals.append(row[1])
        explode.append(0)
    explode.pop()
    with plt.style.context('fivethirtyeight'):
        patches, texts, _ = plt.pie(vals, colors=colors, startangle=90,
                                    autopct='%1.1f%%', explode=explode)
        plt.legend(patches, labels, loc=(0.05, 0.55))
        plt.axis('equal')
    plt.savefig('exp-pie.png')


if __name__ == "__main__":
    if len(sys.argv) < 2:
        sys.stderr.write(('[!] No file supplied\n'
                          'Usage: python3 {} /path/to/file.csv\n').format(
            __file__))
        sys.exit(-1)
    if os.path.exists(sys.argv[1]):
        print('[+] Reading {}...'.format(sys.argv[1]))
        conn = sqlite3.connect('/tmp/exp.db')
        c = conn.cursor()
        try:
            c.execute('CREATE TABLE expenses'
                      '(Date date, Income float, Expense float, Category char,'
                      'Subcategory char, Notes text);')
        except sqlite3.OperationalError:
            d = '/tmp/expdump-{}.csv'.format(random_name(6))
            with open(d, 'w') as tmp_dump:
                csv_writer = csv.writer(tmp_dump)
                for r in c.execute('SELECT * FROM expenses'):
                    csv_writer.writerow(r)
            print('[+] Dumped existing data to {}'.format(d))
            c.execute('DELETE FROM expenses')
        with open(sys.argv[1], 'r') as f:
            csv_reader = csv.DictReader(f, delimiter=';', quotechar='"')
            rows = []
            for row in csv_reader:
                _date = datetime.strptime(
                    row['Date'], '%d/%m/%Y').strftime('%Y-%m-%d')
                rows.append((_date, row['Income'], row['Expense'],
                            row['Category'], row['Subcategory'], row['Notes']))
            c.executemany('INSERT INTO expenses VALUES (?,?,?,?,?,?)', rows)
        plot_pie(c.execute('''SELECT Category, SUM(Expense) as Exp
                              from expenses GROUP BY Category having Exp > 0
                              ORDER BY Exp DESC LIMIT 8'''))
        plot_timeseries(c.execute('SELECT Date, SUM(Expense) '
                                  'from expenses GROUP BY Date'))
        conn.commit()
        conn.close()
        print('[+] All done!')