Extracting bucket balances from MoneyWell data file using sqlite

[Lance]

For anyone interested, here is how I managed to get a script working
that extracts out the current bucket balances and sends them out via a
nicely formatted email that I keep on my iPod touch.

Keep in mind, this is something I hacked together in a couple hours by
poking around the MoneyWell DB file and there are likely better ways
to do this, but it worked for me. Also keep in mind that this is
totally unsupported, and will likely break in some future version of
MoneyWell. Finally, you should ensure MoneyWell is not open when
running scripts like this to avoid any DB issues.

With that out of the way, here is what I did ...

After realizing that the "Export" feature of MoneyWell did not export
bucket balances (or money flows for that matter) I started poking at
the MoneyWell data file itself and realized it is simply a SQLite3
database. Assuming you have Developer Tools installed, you can get
everything you need out of this file using the "sqlite3" command and
some SQL knowledge.

I was hoping that MoneyWell kept sort of a "cached" table of bucket
balances in the data file that I could simply spit out (and perhaps it
does somewhere), but I couldn't find one. That means we need to
reconstruct the bucket balances by looking at the transactions and
money flows associated with each bucket for the desired time period.

From what I can tell, the list of buckets and their various properties
are kept in the "ZBUCKET" table, money flows are kept in the
"ZMONEYFLOW" table, and transactions in the ZTRANSACTION table. Simple
enough.

The first query I use is to get the list of bucket names (ZNAME) and
the order they're arranged in the GUI (ZSEQUENCE) so I can maintain
this ordering in my email. I also filter out the special top level
buckets (i.e. "Income", "Expenses") and any hidden buckets:
SELECT ZNAME,ZSEQUENCE FROM ZBUCKET WHERE ZTYPE > 0 AND ZISHIDDEN
= 0;

The next query I use is to get the money flows INTO each bucket for
the current month. Here I do a join on the ZBUCKET and ZMONEYFLOW
tables using the bucket ID (Z_PK/ZBUCKETIN). The date part was a
little tricky due to MoneyWell storing dates in a slightly modified
"unixepoch" format. Also in my script I replace the hardcoded dates
with variables that hold the start/end dates for the current month:
SELECT ZBUCKET.ZNAME, SUM(ZMONEYFLOW.ZAMOUNT) FROM
ZBUCKET,ZMONEYFLOW WHERE ZBUCKET.Z_PK = ZMONEYFLOW.ZBUCKETIN AND
datetime(ZMONEYFLOW.ZDATE,'unixepoch','31 years') >= '2009-03-01
12:00:00' AND datetime(ZMONEYFLOW.ZDATE,'unixepoch','31 years') <=
'2009-03-31 12:00:00' GROUP BY ZBUCKET.ZNAME;

The next query is very similar but gets the money flows OUT OF each
bucket for the current month. This probably could have been combined
with the above query with some slick SQL, but I was interested in
quick results, not pretty code:
SELECT ZBUCKET.ZNAME, SUM(ZMONEYFLOW.ZAMOUNT) FROM
ZBUCKET,ZMONEYFLOW WHERE ZBUCKET.Z_PK = ZMONEYFLOW.ZBUCKETOUT AND
datetime(ZMONEYFLOW.ZDATE,'unixepoch','31 years') >= '2009-03-01
12:00:00' AND datetime(ZMONEYFLOW.ZDATE,'unixepoch','31 years') <=
'2009-03-01 12:00:00' GROUP BY ZBUCKET.ZNAME;

Next I use a very similar looking query to pull out all the
transactions affecting each bucket for the current month. I also
filter out future pending transactions and handle that later:
SELECT ZBUCKET.ZNAME, SUM(ZTRANSACTION.ZAMOUNT) FROM
ZBUCKET,ZTRANSACTION WHERE ZBUCKET.Z_PK = ZTRANSACTION.ZBUCKET AND
datetime(ZTRANSACTION.ZDATE,'unixepoch','31 years') >= '2009-03-01
12:00:00' AND datetime(ZTRANSACTION.ZDATE,'unixepoch','31 years') <=
'2009-03-31 12:00:00' AND ZTRANSACTION.ZISPENDING = 0 GROUP BY
ZBUCKET.ZNAME;

Finally I do one last query to get all the future pending transactions
as I like to know this as well as the current balance:
SELECT ZBUCKET.ZNAME, SUM(ZTRANSACTION.ZAMOUNT) FROM
ZBUCKET,ZTRANSACTION WHERE ZBUCKET.Z_PK = ZTRANSACTION.ZBUCKET AND
datetime(ZTRANSACTION.ZDATE,'unixepoch','31 years') >= '2009-03-01
12:00:00' AND datetime(ZTRANSACTION.ZDATE,'unixepoch','31 years') <=
'2009-03-01 12:00:00' AND ZTRANSACTION.ZISPENDING = 1 GROUP BY
ZBUCKET.ZNAME;

Once I have all of this data, it's a simple matter of calculating
transactions + money flows in - money flows out to get the bucket
balance. I then use some Perl modules to create a nicely formatted
HTML email that has a color coded list of buckets (green = money left,
red = no money left) with their current balance and future balance.
Also, I group the expense buckets into some categories of my choosing
(with subtotals) so my email isn't a long hard-to-read list. Finally,
I send it off to my wife and I using the command-line "mail" command.

Now every time I update the MoneyWell file, it's a simple matter of
doubling clicking an icon on my desktop to run my script and I have it
on my iPod. This sure beats manually maintaining a Notes file or using
screenshots :)

Hopefully others can make use of what I did. Like I said, this was
something I hacked together in a couple hours, so I'm sure there are
better ways to do it. It also may break for some usage models I didn't
consider. Writing something in AppleScript or Cocoa that uses the Mail
application would probably be a lot more portable than I what I did,
but it works for me.

Enjoy!

[Lance]

I should clarify that in my queries above, the starting date
(2009-03-31 in my examples) should actually be your cash flow starting
date, not the first date of the month.

In my case those dates are the same since I started using MoneyFlow
this month, but obviously it will be different for others.

-Lance

[Matt Gregory]

Awesome, thanks! I hadn't figured out the exact relationship between
the zbucket, ztransaction, and zmoneyflow tables.

I used it to create my own script, so I thought I'd share it as well.
I found that to make my balances match what's displayed in the UI I
had to discount pending transactions and negate flows associated with
zbucketout. I also pulled the cash flow start date from the zrollover
table and didn't cap the end date.

Attached is the script, it just outputs the bucket names and their
amounts in tabular format. It can be run with a command similar to:

> sqlite3 moneywellfile.moneywell < bucketreport.sql

Hopefully this list doesn't strip attachments ...

[Lance]

Hey Matt,

This is pretty slick! Since I had a lot of manipulation I wanted to do
for email purposes I lazily put most of the logic inside my Perl
script, but this pushes most of the logic into the SQL itself, which
is much more portable.

Regarding the pending transactions, if you typically hide all future
transactions, you can ignore them as you have done here to match the
GUI. However, if you show future transactions, any ones that will
occur in the current month will count against your bucket balances,
which is why I do a separate query and use the last day of the current
month in order to match the GUI when you pick "Show Future Pending
Transactions". I like to see both numbers since the current amounts
can be misleading.

Good idea on grabbing the cash flow info from the ZROLLOVER table. I
think I'll update my script to do this as well so I don't have to
hardcode these numbers in.

-Lance

[Lance]

Matt,

I tried out your SQL file and was getting a syntax error on the first
few lines of comments (it didn't seem to like the /* */ syntax). I
converted this to the "--" style comments and then it worked.

Looking at the output, everything seems to match what I would expect
with a couple minor differences:

1) Buckets that do not have any transactions/money flows yet do not
show up in the list. This happens in my file only because I've started
as of this month and haven't had those type of expenses yet. I handled
this in my script by explicitly querying for all the non-hidden
buckets to come up with my listing, but admittedly most people
probably won't care about this, but it was something my wife noticed
and requested to be fixed :)

2) I use a custom bucket order (not alphabetical) and this query
returns an alphabetically sorted list. In my script I order by
ZBUCKET.ZSEQUENCE to get the order I expect. Again, this might not
matter if people simply suck this output into another script that
manipulates it further.

-Lance