FinanceFerret/HyperMoney: Difference between revisions

From Woozle Writes Code
Jump to navigation Jump to search
m (update)
 
(19 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{stub:Woozle}}
'''HyperMoney''' is my attempt to write a money-management program (as in [[Quicken]] or [[Microsoft Money]]) that is actually usable and has no Stupid Features. It will be [[FOSS|Open Source]], as soon as I have source worth publishing (as of 2005-08-06, it's all written in Microsoft Access 97). The next step may be to convert it to web-based application, as HTML could possibly overcome many of the interface design issues I'm encountering.
[[Category:Projects/Software]]
[[User:Woozle|Woozle]]: Projects: '''HyperMoney'''


[[HyperMoney]] is my attempt to write a money-management program (as in Quicken or Microsoft Money) that is actually usable and has no Stupid Features. It will be [[FOSS|Open Source]], as soon as I have source worth publishing (as of 2005-08-06, it's all written in Microsoft Access 97). The next step may be to convert it to web-based application, as HTML could possibly overcome many of the interface design issues I'm encountering.
'''2008-08-17''' Update: I'm renaming the project [[FinanceFerret]] <s>and moving the documentation over to [[htyp:FinanceFerret|HTYP]]</s> (this has been done, and is what you are looking at except [2020-10-10] that it is now on wooz.dev and is an old version).


==Project Goals==
==Data Design==
The two major contenders in this 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.
There are two main areas of concern: Accounts and Transactions. All other tables are supporting either or both of those, sometimes mainly for user-friendliness and not part of the core data design. User-friendliness functions include Grouping and Pre-entry (deferred data massaging).
===Acccounts===
{| style="background: #eeeeff;"
|+'''Accounts''' table
|-
!Field||Type||Description
|-
|'''ID'''||int(4) autonumber
|-
|'''Name'''||text||short name for account
|-
|'''Descr'''||text||description and notes
|}


===Reconciling Balances===
Accounts are in a hierarchical tree, but this is mainly user-friendliness (though it can also be used for reports). The tree is in a separate table, Topics (see [[#Grouping]]), and the mapping from Accounts to Topics is in [Accounts x Topics].
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:
# You enter the beginning and ending dates of the bank statement
# You enter the beginning balance of the bank statement
# 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
# 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.
{| style="background: #eeeeff;"
|+'''Accounts x Topics''' table
|-
!Field||Type||Description
|-
|'''ID_Acct'''||int(4)||Accounts.ID
|-
|'''ID_Topic'''||int(4)||Topics.ID
|}


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


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.
Every transaction consists of one or more [[TransPart]]s associated with a master Transaction record. Inforamation about balances will be stored separately.


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.
{| style="background: #eeeeff;"
|+'''Trxacts''' table (Transactions)
|-
!Field||Type||Description
|-
|'''ID'''||int(4) autonumber
|-
|'''Descr'''||text||description of transaction
|-
|'''When'''||date||date when the transaction occurred, as recorded by user
|}


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.
{| style="background: #eeeeff;"
|+'''TrxParts''' table ([[TransPart]]s)
|-
!Field||Type||Description
|-
||'''ID'''||int(4) key autonumber
|-
|'''ID_Trx'''||int(4)||Trxacts.ID of master transaction
|- valign=top
|'''ID_Mode'''||int(4)||TrxModes.ID of transaction mode (source, target, equity, topic)
|- valign=top
|'''ID_Acct'''||int(4)||Accts.ID of account for this part of the transaction
May be NULL to indicate that this is a "loose" piece of information
|-
|'''Seq'''||int(4)||Order in which this transaction part appears, among others of the same mode
|-
|'''ID_Type'''||int(4)||TrxTypes.ID of transaction type for this part
|-
|'''When'''||date||date when the transaction occurred, according to the institution hosting the account
|- valign=top
|'''Amount'''||currency||amount of transaction; positive = deposit, negative = withdrawal.
*Sum of all source parts plus all target parts must equal zero.
*Sum of all equity parts must equal sum of all target parts.
|}


===Equity===
===Grouping===
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.
{| style="background: #eeeeff;"
|+'''Topics''' table
|-
!Field||Type||Description
|-
|'''ID'''||int(4) autonumber
|-
|'''Name'''||text||short name for topic, as shown in tree
|-
|'''NameFull'''||text||(optional) longer form of name for display outside of tree context
|-
|'''Descr'''||text||(optional) description of topic
|-
|'''ID_Parent'''||int(4)||Topics.ID of parent topic; NULL = this is a root topic
|}


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.
==Interface Design==
 
There are certain situations where it arises, nonetheless:
#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.
#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.
#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 in... wait a minute. That would give "Biz Bank" a balance of $600, 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. 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 [[Wikipedia:Double-entry book-keeping|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''.


==Links==
==Links==
*http://braincore.blogspot.com/2005/05/koding-what-am-i-koding-then.html - A Dutch developer discusses accounting software, briefly. Apparently they don't use checkbooks in Holland.
*http://braincore.blogspot.com/2005/05/koding-what-am-i-koding-then.html - A Dutch developer discusses accounting software, briefly. Apparently they don't use checkbooks in Holland.

Latest revision as of 13:54, 10 October 2020

HyperMoney is my attempt to write a money-management program (as in Quicken or Microsoft Money) that is actually usable and has no Stupid Features. It will be Open Source, as soon as I have source worth publishing (as of 2005-08-06, it's all written in Microsoft Access 97). The next step may be to convert it to web-based application, as HTML could possibly overcome many of the interface design issues I'm encountering.

2008-08-17 Update: I'm renaming the project FinanceFerret and moving the documentation over to HTYP (this has been done, and is what you are looking at except [2020-10-10] that it is now on wooz.dev and is an old version).

Data Design

There are two main areas of concern: Accounts and Transactions. All other tables are supporting either or both of those, sometimes mainly for user-friendliness and not part of the core data design. User-friendliness functions include Grouping and Pre-entry (deferred data massaging).

Acccounts

Accounts table
Field Type Description
ID int(4) autonumber
Name text short name for account
Descr text description and notes

Accounts are in a hierarchical tree, but this is mainly user-friendliness (though it can also be used for reports). The tree is in a separate table, Topics (see #Grouping), and the mapping from Accounts to Topics is in [Accounts x Topics].

Accounts x Topics table
Field Type Description
ID_Acct int(4) Accounts.ID
ID_Topic int(4) Topics.ID

Transactions

Every transaction consists of one or more TransParts associated with a master Transaction record. Inforamation about balances will be stored separately.

Trxacts table (Transactions)
Field Type Description
ID int(4) autonumber
Descr text description of transaction
When date date when the transaction occurred, as recorded by user
TrxParts table (TransParts)
Field Type Description
ID int(4) key autonumber
ID_Trx int(4) Trxacts.ID of master transaction
ID_Mode int(4) TrxModes.ID of transaction mode (source, target, equity, topic)
ID_Acct int(4) Accts.ID of account for this part of the transaction

May be NULL to indicate that this is a "loose" piece of information

Seq int(4) Order in which this transaction part appears, among others of the same mode
ID_Type int(4) TrxTypes.ID of transaction type for this part
When date date when the transaction occurred, according to the institution hosting the account
Amount currency amount of transaction; positive = deposit, negative = withdrawal.
  • Sum of all source parts plus all target parts must equal zero.
  • Sum of all equity parts must equal sum of all target parts.

Grouping

Topics table
Field Type Description
ID int(4) autonumber
Name text short name for topic, as shown in tree
NameFull text (optional) longer form of name for display outside of tree context
Descr text (optional) description of topic
ID_Parent int(4) Topics.ID of parent topic; NULL = this is a root topic

Interface Design

Links

Retrieved from "https://wooz.dev/mw/index.php?title=FinanceFerret/HyperMoney&oldid=581"