MySQL is Way Cooler than Quicken
Date Posted: October 30th, 2009
You’ve been keeping track of your expenses for the past month; now you want to analyze the data.
I used to use Quicken and Aaron used Mint.com, and while these tools are convenient and pretty, they don’t really work well for us. One annoyance is that these tools use credit card statements and the like to populate the tables. What do you do when you buy lightbulbs and groceries at the grocery store? Are the lightbulbs also food? You also probably didn’t keep the receipt so you don’t know how much they cost. Because of that, the graphs are never accurate. Another annoyance is that Quicken had a difficult time actually pulling my information from the different institutions. I got tired of it quickly and didn’t buy the next year’s upgrade.
MySQL to the rescue. Admittedly if you’re not comfortable with computers this maybe a bit scary to use, but if you want to be able to answer the questions you have about your finances and don’t care about a pretty interface it could be the right tool for you. This is what we use to analyze our financial data, and I’ll show you how to use it too.
MySQL is a relational database management system, meaning that data within it is stored as tables and the relationships between tables are also shown as tables. For this exercise the multiple tables thing isn’t important. What is important is that with RDBMSs you can ask whatever questions you want about the tables, as long as you form those questions in the query language. We only have one table, so this is how we’re using MySQL.
Step 1) Copy the Spreadsheet
You have your table in Google Docs. The first step is to make sure your columns are in the right format. MySQL will complain if they aren’t.
- Amount column values must not have a preceding $. (Notice the spreadsheet picture)
- Date must be in YYYY-MM-DD format
Now that your format’s all set, copy the cells with the information. If you have a “Cleared?” column don’t copy that. Also, don’t copy titles. You’ll be making special titles soon enough. Throughout the rest of this tutorial I’ll be assuming you have the same columns that I do.
Step 2) Paste into Text Editor
I’m using a Mac, so I copied the text into pico in the terminal. Save it as a .txt file. Mine is called ledger.txt. Make sure you use a text editor and not a word-processor. Word-processors will add information to the text that you DO NOT WANT. If you’re using a Windows machine use notepad.
Step 3) Get XAMPP
The easiest way to get MySQL onto your computer is to use XAMPP. If you install MySQL directly you’ll have to do some configuration magic that is a pain, and really unnecessary for the task at hand. You aren’t setting up a production web server, so you don’t have to worry about security or anything. XAMPP is as close to plug and play as you get with this. It’s also free.
You can get XAMPP here. Follow the instructions on the site for how to install and get started for your operating system.
Step 4) Set up DB and Import Data
Open up a terminal (on windows: run –> cmd) and move to the XAMPP directory. Then move to xamppfiles; then to bin. The full path of the bin directory for me is:
/Applications/XAMPP/xamppfiles/bin
The command mysql should be in this directory. Stuff in bold is stuff you type.
Open MySQL:
$ ./mysql -u root
“-u root” just means that you are logging in as the user “root”.
You should get the following prompt:
mysql>
Now that you’re in, the next step is to create your database and use it.
mysql> create database finances;
Query OK, 1 row affected (0.00 sec)
mysql>use finances;
Database changed
Next create your table. Again, this is assuming you have the same columns.
mysql> CREATE TABLE 'expenses' (
-> 'date' date default NULL,
-> 'store' varchar(255) default NULL,
-> 'item' varchar(255) default NULL,
-> 'tag' varchar(255) default NULL,
-> 'subcategory' varchar(255) default NULL,
-> 'amount' decimal(10,2) default NULL,
-> 'source' varchar(255) default NULL
-> );
Query OK, 0 rows affected (0.13 sec)
mysql> delete from expenses;
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile '/Users/mrio/ledger.txt' into table expenses;
Query OK, 30 rows affected, 7 warnings (0.05 sec)
Records: 30 Deleted: 0 Skipped: 0 Warnings: 7
Here’s an easy to copy and paste version of the Create table command:
CREATE TABLE 'expenses' ( 'date' date default NULL, 'store' varchar(255) default NULL, 'item' varchar(255) default NULL, 'tag' varchar(255) default NULL, 'subcategory' varchar(255) default NULL, 'amount' decimal(10,2) default NULL, 'source' varchar(255) default NULL );
In case you’re wondering what each command does:
“create table” creates the table into which your data goes.
“delete from expenses” removes any data which may have been in expenses before. Perhaps from a previous month?
“load data local infile” loads the data from your text file. Change the text file path to the appropriate one for your file.
Step 5) Query Query Query
Below are a bunch of questions you might want to ask about your expenses. In the code you’ll see how those questions are translated into SQL and how the answer looks for the sample data I intered into the table.
How much did I spend in each tag? I’d like the data in descending amount spent.
mysql> select tag, sum(amount) as mysum from expenses group by tag order by mysum desc;
+-------------------+--------+ | tag | mysum | +-------------------+--------+ | Food-Normal | 722.64 | | Food-Out | 449.59 | | Wedding | 205.21 | | Household | 125.73 | | Recreation | 76.63 | | Utilities | 70.34 | | Medical | 40.00 | | Food-Entertaining | 32.54 | | Car | 27.72 | | NULL | NULL | +-------------------+--------+ 10 rows in set (0.00 sec)
How Much did I spend total?
mysql> select sum(amount) from expenses;
+-------------+ | sum(amount) | +-------------+ | 1750.40 | +-------------+ 1 row in set (0.00 sec)
How much did I spend each week?
mysql> select week(date), sum(amount) from expenses group by week(date) order by week(date);
+------------+-------------+ | week(date) | sum(amount) | +------------+-------------+ | NULL | NULL | | 39 | 1421.73 | | 40 | 328.67 | +------------+-------------+ 3 rows in set (0.00 sec)
How much did I spend in each category and subcategory?
mysql> select tag, subcategory, sum(amount) from expenses group by tag, subcategory order by tag,subcategory;
+-------------------+---------------+-------------+ | tag | subcategory | sum(amount) | +-------------------+---------------+-------------+ | NULL | NULL | NULL | | Car | Gas | 27.47 | | Car | Parking | 0.25 | | Food-Entertaining | | 32.54 | | Food-Normal | | 722.64 | | Food-Out | | 34.42 | | Food-Out | Work | 415.17 | | Household | | 26.73 | | Household | Garden | 33.33 | | Household | Hygiene | 38.79 | | Household | Repair | 26.88 | | Medical | | 40.00 | | Recreation | | 10.00 | | Recreation | Books | 10.99 | | Recreation | Music | 30.00 | | Recreation | Video games | 25.64 | | Utilities | Internet | 70.34 | | Wedding | Wedding Dress | 205.21 | +-------------------+---------------+-------------+ 18 rows in set (0.00 sec)What percentage of my total spending did I spend in each category?
mysql> select tag, sum(amount)/1750.40 as mysum from expenses group by tag order by mysum desc;
+-------------------+----------+ | tag | mysum | +-------------------+----------+ | Food-Normal | 0.412843 | | Food-Out | 0.256850 | | Wedding | 0.117236 | | Household | 0.071829 | | Recreation | 0.043779 | | Utilities | 0.040185 | | Medical | 0.022852 | | Food-Entertaining | 0.018590 | | Car | 0.015836 | | NULL | NULL | +-------------------+----------+ 10 rows in set (0.00 sec)This is the same as sum by tag except I divided sum(amount) by the total spent which was calculated earlier. Here you have basically what you need for YMOYL summary.
How much did I spend at each store?
mysql> select store, sum(amount) as mysum from expenses group by store order by mysum desc;
+---------------------+--------+ | store | mysum | +---------------------+--------+ | Kroger | 524.61 | | Starbucks | 400.30 | | Jo Ann Fabric | 205.21 | | Co-op | 141.74 | | Trader Joe's | 105.63 | | Comcast | 70.34 | | Rite-aid | 40.00 | | English Gardens | 33.33 | | Ark | 30.00 | | Rite Aid | 28.49 | | Marathon | 27.47 | | Carpenter Bros | 26.88 | | Kmart | 26.73 | | Target | 25.64 | | Jersualem Garden | 15.37 | | Amazon.com | 10.99 | | Damon's Grill | 10.52 | | Salvation Army | 10.00 | | Zingerman's | 7.25 | | Colin's Coffee | 5.30 | | Coney Island | 4.35 | | Parking 5th&William | 0.25 | | NULL | NULL | +---------------------+--------+ 23 rows in set (0.00 sec)And that’s all there is to it. Not as pretty as Quicken, but it’s way more flexible in terms of how I organize my data and what questions I ask of it.
If there’s anything in this tutorial that doesn’t make sense or if there’s a question you’d like to ask about your data that isn’t listed here, leave a comment and I’ll update this tutorial.
You can leave a response, or trackback from your own site.
