Boston Linux & Unix (BLU) Home | Calendar | Mail Lists | List Archives | Desktop SIG | Hardware Hacking SIG
Wiki | Flickr | PicasaWeb | Video | Maps & Directions | Installfests | Keysignings
Linux Cafe | Meeting Notes | Blog | Linux Links | Bling | About BLU

BLU Discuss list archive


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Discuss] Financial database / balance?



>
> 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.

Since the system I ran was 'batch' it kept the ISAM or VSAM file as a database,
but didn't post anything there until 'closing' at the end of month.

The Journal Entry was ANYTHING that happened.
Date Stamp
Journal Entry # (so we could trace back to the paper and who approved it)
Gen Ledger acct# to debit
Gen Ledger acct# to credit
$ amount.
Notes - a text field I liked to see used for other related notes.

The GL accounts were 26 characters long (the first two were 'company code'
since we handled 78 or so 'subsidiaries' in the corporate accounting system,
and they didn't even have the same chart of accounts, in many of the companies
so we had to keep everything 'separate' while putting it all in one
set of files.

But it did help having the company code, when I did an automated tax return
system for them.  ...

>
> 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
>

Yep, basically the same.

> 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, having two accounts (or mirror_id) is the only way to deal easily
with double entry book keeping.  I hated that way of working, but it was
the CPA's tail on the line.  I was salaried and well paid for the day, so
I just tried to make sure whatever they asked for made sense in some way.

>> 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.
>

No at all.  I found that I did lots of 'clone and modify' to generate
reports.  Near the end of my time there, I finally determined there
were only about 4 different programs we would write.

setup/initialize - initial setup, zero accounts, etc
maintenance - add, change, delete, and produce audit
    trails and similar reports that would actually change things.
reporting - most of what is done.  I hate to think of the number
   of trees it took to generate all that green bar paper we printed.
auditing - audit for the IRS was just reports.  The audit programs
   I wrote were to ensure integrity of the files and VSAM/ISAM
   'databases'.

Eventually the tax system I built, loaded much of the accounting data
into a database (pre-sql, but could be used like we use SQL today).
And my audit programs basically ensured what we call referential integrity.


>> 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.
>
> -Daniel
> _______________________________________________
> Discuss mailing list
> Discuss at blu.org
> http://lists.blu.org/mailman/listinfo/discuss



BLU is a member of BostonUserGroups
BLU is a member of BostonUserGroups
We also thank MIT for the use of their facilities.

Valid HTML 4.01! Valid CSS!



Boston Linux & Unix / webmaster@blu.org