FinanceFerret/flaws in other software

HyperMoney: Flaws in Other Software

The two major contenders in the money manager software niche -- Quicken and Microsoft Money (Q/MM) -- seem to approach the problem more or less the same way. In areas where either of them fall short, the other one does too.

Reconciling Balances

The main problem that keeps coming up with Q/MM is in reconciling your accounting with the bank's. They take the following approach, with the assumption that you are working from a periodical bank statement:

1. You enter the beginning and ending dates of the bank statement
2. You enter the beginning balance of the bank statement
3. For each item shown on the bank statement, you check off the corresponding item in Q/MM -- or enter it, if Q/MM does not have a record of it
4. You cross your fingers and hope that Q/MM's ending balance matches what the bank shows

One problem is with the final step. If there's a discrepancy, how do you figure out where it occurred? You have two numbers that differ by some arbitrary amount. It could be a missing transaction, or it could be a typo. It could have occurred on any transaction between the starting and ending dates.

Most bank statements (printed ones, at least, and some online banking services do as well) will show you a day-to-day running balance.

Q/MM will show you the same thing, but it shows them in the order of the dates you entered for each transaction. This means, for example, that if you buy something with your credit card on June 1 but the bank shows it as June 5, your balance will disagree with the bank's from June 1 to June 5. If you made another purchase on June 4 and it didn't show up on your bank account until June 8, then you have a discrepancy from June 1 through June 8. If you make purchases every few days of a month, then your balance and the bank's will never agree.

Yes, it's possible that the transactions might still come in in the same order -- but there are inevitably other transactions, such as deposits, with differing lags. And let's not even talk about checks -- you write one to someone on June 2, but they forget to deposit it until July 1, when they go to the bank to deposit their paycheck. That throws you off for an entire month with just one transaction.

The whole problem would be solved, however, if Q/MM would let you also enter the date on which the bank shows the transaction, and then sorted by that date when calculating the balance. Q/MM's balance and the bank's would match line-for-line, unless there was an actual discrepancy -- and then you would know exactly where the discrepancy was.

Equity

The other major area in which Q/MM falls short -- and I can't really blame them, because it turns out to be surprisingly complicated -- is Equity. I'm not sure if I'm using it the same way it's normally used in accounting, but I had a concept for which I needed a word, and Equity seemed like a close match. (See wikipedia:Shareholders' equity and wikipedia:Ownership equity for an explanation of equity in accounting.) The following is my usage of the term.

The examples below might be a good illustration of the proverb "Neither a borrower nor a lender be", since if these characters had simply paid each other off right away instead of letting the situation become more tangled, there would be no need for complex accounting.

There are certain situations where it arises, nonetheless:

1. In small business partnerships, the partners will often spend money on the business without expecting payback in the short term; it is vital to be able to keep track of this.
2. One sometimes runs into individuals who are less than fastidious about keeping track of their borrowings and expenditures. In this case, it is vital to be able to keep track of the borrowings and repayings of money amounts so that you can state, quickly and definitively, how much you are owed, rather than deferring the calculations until you have time -- by which time the situation has grown yet more complex.
3. In cohabiting a household, often the expenses and borrowings/lendings come too quickly to allow simple payback; you don't want to be running to the bank five times a day with checks for tiny amounts.

Example 1

Let's say you're trying to keep track of how much you owe Fred. Fred lends you $100, so you set up a "Fred" account on your money manager program. You enter the $100 as a debit on the "Fred" account, so your "Fred" balance is -$100; it's clear enough that you owe "Fred" $100.

Next week, you give Fred $25. You enter this as a credit on the "Fred" account, and your money manager now shows a balance of -$75. So far so good.

The week after that, however, you buy Fred a hammer for $20, and Fred says to take it off your debt. But you've already entered the transaction in your money manager -- you used your check card to pay for the hammer, so you entered a check card debit on the "My Bank" account, paid to "Joe's Hardware". Possibly you could edit the transaction so the money went directly into the "Fred" account... but then when you want to see all the money you've spent at Joe's Hardware, the $20 hammer won't show up. "So what?", you think -- "It wasn't really my $20 anyway; it was Fred's." So you make the change.

This causes a minor problem later on, when the bank shows a payment to "Joe's Hardware" and your records show a payment to "Fred" instead, but you figure it out. Several months later, this causes another problem when Fred claims you still owe him $20, and your "Fred" balance shows zero. Fred claims you never gave him the $20 you show on June 1. After hunting around for a bit, you finally remember that the $20 didn't actually go directly to Fred; it went into a hammer which you then gave to Fred.

This is the sort of confusion which accounting software is supposed to prevent -- so the software clearly isn't doing everything you need.

Example 2

You have a personal bank account ("My Bank") and a business bank account ("Biz Bank"). They each have $500 in them. You buy $100 of supplies for your business, using your personal account (maybe the business account lacks a check card, or you didn't have it with you, or the CFO won't let you charge directly from the business account). You enter this as a $100 debit in "My Bank", leaving a balance of $400, and a $100 credit to Biz Bank... except that would give "Biz Bank" a balance of $600 (which would be inaccurate), and nowhere would it show that the business owes you $100.

So you set up a separate account called "Biz Owes Me", and put the $100 deposit in there -- but again, this causes problems later when you're trying to figure out where the $100 actually went.

And there's even greater confusion when the CFO writes you a $100 check, drawn on the business's bank account, which you deposit in your bank account. This clearly has to be debited from "Biz Bank" and deposited to "My Bank" -- but it also needs to reduce the amount in "Biz Owes Me".

What This Means

In the standard accounting practice of Double-entry book-keeping (I'm not sure their examples are quite right, but the explanation is otherwise clear enough), every transaction has two parts -- where it comes from, and where it goes to. If I buy a hammer, I release some cash (my net balance decreases) and the hammer store gets it (their net balance increases).

As the above examples -- especially #2 -- would seem to indicate, certain transactions imply a third part: If I buy a hammer for you, I lose some cash, the hammer store gets some cash, and you owe me.

Technical Interpretation

The data design I eventually arrived at for handling this involves a record for each end of the transaction -- one record each for source, destination, and equity; I am calling each of these "ends" a "TransPart". The Equity transpart may be omitted if the transaction results in no net change in equity (e.g. I buy something for myself). The destination transpart may be omitted if we aren't tracking transactions for that particular destination, e.g. I buy something at a store I'll probably never visit again; a notation in the source transpart should be enough to identify the transaction for reconciliation purposes, and if we want to track that purchase as part of a category (e.g. "vacation expenses" or "convenience stores"), then it may be assigned to one or more Categories (which are very similar to Equity accounts except that every penny must go into exactly one Equity account but that same penny can go into multiple Categories).

Data Format

Another more technical problem with Q/MM is that their internal data format used by each is "closed" (i.e. proprietary and unpublished) and does not appear to be database-driven. HyperMoney will use a separate database engine (the engine should ultimately be independent of the code, though it may not be at first) which can be accessed by other programs. This will bring the following benefits:

• Other programs can be written to extend HyperMoney's functionality, both in reading data and in writing to it. For example:
  • Bill payment reminders
  • Budget warnings ("You are within $x of exceeding your budget for Widgets for this month.")
  • Archiving -- moving old transaction and account data to offline storage
  • Document management -- tie in transactions with images of receipts; quickly print copies of all receipts assigned to a certain category, or view a transaction's paperwork with a single click
  • Graphing -- have your current financial health (for example) shown as a standalone graph on your desktop
  • Multi-user capability becomes an option when using a database engine, as they are designed to allow this
• Reporting can be more rigorous, i.e. rather than looking for all transactions containing the string "JOE'S HARDWARE" or "HARDWARE", we can make an SQL query to find all transactions to/from the "Joe's Hardware" account or assigned to the category "hardware".
• Users will not be stuck with the existing user interface. User data will not have to be "exported" in small chunks if the user wants to switch interfaces.
• Backups can be automated using existing database management tools.

HyperMoney's data design will be non-proprietary and thoroughly documented; the code which makes it work will be open-source, providing additional "how-to" documentation. The format in which the data is stored will be determined by the database engine. I will be starting with MySQL, which is itself open source.