[Discuss] Financial database / balance?

[Jerry Feldman]

On 01/15/2012 01:25 PM, Daniel C. wrote:
> On Fri, Jan 13, 2012 at 7:43 PM, Richard Pieri <richard.pieri at gmail.com> wrote:
>> You could go with a double-entry system.  It's more complex to write and use but double-entry book-keeping ensures that mistakes and errors are caught immediately.
> I considered double-entry, but most accountants I've spoken to say
> that it's overkill for personal finance.  Also, I could never figure
> out how paychecks are supposed to work.  Do I have an account called
> "Job" that I just debit eternally so that I can then credit that
> amount to my "funds available" account?  I guess it would be kind of
> nice to know where exactly my money is coming from, but to be honest
> most of the time I just have one job and that's it.  My primary
> concern is with tracking savings, expenses, and (ultimately) how much
> I can spend on beer tonight without infringing on either of the first
> two items.
>
> On Fri, Jan 13, 2012 at 8:13 PM, Jack Coats <jack at coats.org> wrote:
>> When I did mainframe bean counting in the last century, we basically
>> kept a database with the 'end of year' and each 'end of month' amounts
>> for each account, and a MTD (month to date) summery.  We kept the in
>> the month journal entries with each transaction so they could be
>> updated during the month (and that would be used to keep the MTD
>> amounts up to date.
> That seems like a pretty good compromise between the two extremes.  So
> would the MTD summary be updated continuously every time a new journal
> entry is posted?  I'd have to add another table (or two?) to keep end
> of month / end of year summaries, but that's not such a big deal.
>
> I'm assuming a "journal entry" means a payment, a deposit, a transfer,
> or something else.  I'm going back and forth on how to store these as
> well, and I've settled on a table that looks like this:
>
> id
> account_id
> mirror_id
> date
> amount
> comment
>
> The only thing not self explanatory is mirror_id.  This would point to
> another record in the same table with a different account ID and the
> opposite (from 0) amount.  It indicates a transfer between two
> accounts - a debit in one, and a credit in the other.  Otherwise, one
> record in this table = one debit or credit to a single account.  I've
> been working on a trigger to update the running balance on the account
> every time a new row is inserted, similar to your MTD example.
>
>> Yes, I understand abhoring anything but fully updated information, and
>> having to re-process old transactions.  But after supporting both tax
>> departments and general accounting (I stayed away from HR and payroll
>> like the plague -- different issues there), being ready to explain
>> your system and how you can validate it to auditors is a "good
>> thing"(tm).
> So, I'll add a feature request for some kind of auto-audit and/or
> auto-re-processor.  It shouldn't be too difficult to write.
>
>> I hopes this helps.
> This has been tremendously helpful, thank you.
>
> Also, I double checked my DB and I believe I'm now using BCD's.  To be
> honest it doesn't matter to me right now - part of the design of this
> project is that I will slightly overestimate expenses by just a bit (I
> usually round up to the nearest quarter) and slightly underestimate
> income (I usually round paychecks down to the nearest dollar).  Over
> time this will create a discrepancy between what my finance software
> shows and what's actually in my bank account, but that discrepancy
> will always be on the safe side.  Plus, when it gets big enough to
> notice, it means a "free" meal at Passage to India.  I'm sure this
> would drive other people insane, but I've found that not knowing
> precisely how much is in my bank account leads me to spend less money.
>  If other people want to use the software in future versions, I'll
> probably make it so you can turn that "feature" off.
>
> Richard, I'm kind of curious what you have to say about double-entry
> bookkeeping.
>
>
Double entry is just a technique to help maintain accuracy. For
instance, a ledger has 2 parts, debit and credit, or assets and
liabilities+equity. The basic equation is that assets = liabilities +
owners equity.
So:
In a very simple system you have cash in the bank (asset), and owners
equity of the same amount:
You borrow $100, the journal entry would be:
1. add $100 to cash
2. add 100 to liabilities.

The bottom line is that journal entry balances because your assets and
liabilities + OE are equal.

Dan Bricklin and Bob Frankston did not invent the spreadsheet, it has
been in use for hundreds of years, but on paper. What they did was apply
it to a computer. The imporant thing about double entry is that all
transactions should have equal amounts on the asset and L+OE side.
(accounting 101).

Effectively your net worth is the sum of your assets less the sum of
your liabilities (eg credit card debt).

When you get paid, your cash is increased and your OE or just equity) is
also increased.

Basically that is it. In a personal finance system, you don't need
double entry but you want to keep track of your cash and your
liabilities. In a more formal system, you have the value of your car and
other assets. You also have future expenses such as rent utilites,
taxes, loan payments. Most simple accounting systems, such as GNU Cash
or Moneydance can already do that.

-- 
Jerry Feldman <gaf at blu.org>
Boston Linux and Unix
PGP key id:3BC1EB90 
PGP Key fingerprint: 49E2 C52A FC5A A31F 8D66  C0AF 7CEA 30FC 3BC1 EB90