FinanceFerret

Overview

FinanceFerret is the working name for an (eventually) open source personal finance manager application currently being developed by Woozle.

Goals

• To avoid shortcomings in existing money manager software
• To minimize the "setup curve", i.e. the amount of preparatory work needed before any useful amounts of data can be entered
• To provide all basic functionality needed for the following:
  • Tracking and balancing one's bank accounts, credit cards, and other basic types of money accounts
  • Tracking debts and loans involving one or more other parties of varying levels of disorganization (they might or might not send you regular statements or even reliable itemizations)
• To make data entry as painless as possible:
  • Easy identification of redundantly-entered transactions -- so you can Just Start Typing instead of worrying about where you left off last time
  • Deferred data massaging -- so you can Just Start Typing, and worry about assigning proper accounts and categories to things later; the program should keep track of incomplete data massaging so you can take care of it when you have time

Naming

Googling for "financeferret" did not turn up any apparent trademarks. The domain name "financeferret.com" was registered in 2006, but as of 2008-08-17 the associated web site still showed it as "coming soon" -- possibly a domain squatter.

From this, I determined that the name is more or less available for this use. "financeferret.net" and "financeferret.org" were available as of 2008-08-17, but it didn't seem worth the trouble and expense to register them (enough just-for-the-name domains sitting around sucking $12/year out of my bank account already).

When I checked again on 2009-06-06, financeferret.com had become available, so I registered it.

Status

The software currently exists as a Microsoft Access 97 program (written in VBA); I have migrated the tables to MySQL, and the basic functions are now working and usable. The rest of the application will be migrated to non-proprietary platforms as time permits, and I will be posting the source code as soon as I have any worth publishing.

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.

Early documentation for that version is on HypertWiki.

Concepts

• /mirror transaction

Tables

Transactions

<mysql>DROP TABLE IF EXISTS `Transactions`; CREATE TABLE `Transactions` (

 `ID` int(4) NOT NULL auto_increment,
 `ID_Type`      varchar(50) default NULL COMMENT "[Trx Types].ID",
 `ID_Acct`      int(4)      default NULL COMMENT "Accounts.ID - ID of account in which this transaction originates",
 `ID_Dest`      int(4)      default NULL COMMENT "Accounts.ID - ID of destination account",
 `ID_Equity`    int(4)      default NULL COMMENT "Accounts.ID - ID of equity account (if any)",
 `ID_TrxMirror` int(4)      default NULL COMMENT "Transactions.ID -- transaction of which this one is a mirror",
 `ID_TrxDup`    int(4)      default NULL COMMENT "[Trx Entered].ID -- transaction of which this one is a duplicate."
 `CheckNum`     varchar(50) default NULL COMMENT "check # or other identifying text unique to this transaction within the current account (optional)",
 `Amount`       DECIMAL(9,2) default NULL COMMENT "Amount added to ID_Acct account",
 `AmountOrig`   DECIMAL(9,2) default NULL COMMENT "Amount in check register, when bank disagrees",
 `DateAction`   datetime     default NULL COMMENT "Date action was taken (e.g. date on the check)",
 `DateEffective` datetime    default NULL COMMENT "Date as recorded by financial institution (on statement)",
 `WhenEntered`  datetime     default NULL COMMENT "When this transaction was entered into the system",
 `WhenChanged`  datetime     default NULL COMMENT "When this transaction was last edited",
 `WhoEntered`   varchar(63)  default NULL COMMENT "[user]@machine of who entered the transaction (see log for further edits)",
 `Accounted`    tinyint(1)   default NULL COMMENT "TRUE = assume transaction is accounted for by institution (use only if effective date is not available)",
 `Voided`       tinyint(1)   default NULL COMMENT "transaction voided",
 `ToVerify`     tinyint(1)   default NULL COMMENT "transaction needs to be verified -- created automatically",
 `IsApprox`     tinyint(1)   default NULL COMMENT "exact amount of transaction is not known yet",
 `SortEntry`    varchar(50)  default NULL COMMENT "transaction order as entered into checkbook register",
 `SortInst`     varchar(50)  default NULL COMMENT "Order shown by financial institution (on statement)",
 `BalanceImmed` DECIMAL(9,2) default NULL COMMENT "Balance as given by financial institution at time of transaction (customarily includes this transaction in the figuring, but ok if otherwise)",
 `BalanceStated` DECIMAL(9,2) default NULL COMMENT "Balance after this transaction as given by financial institution on regular statement",
 `BalanceEquity` DECIMAL(9,2) DEFAULT NULL COMMENT "equity balance for ID_Acct with respect to ID_Equity",
 `UseBalanceEffective` tinyint(1) default NULL COMMENT "TRUE = restart balance calculations using BalanceEffective; FALSE = continue calculating using calculated balance",
 `UseBalanceEquityEff` BOOL   default FALSE COMMENT "TRUE = reset BalanceEquity to 0 regardless of calculations",
 `Descr`        varchar(255)  default NULL COMMENT "text describing the transaction -- similar to memo field on check",
 `CalcBalance`  DECIMAL(9,2)  default NULL COMMENT "balance as calculated by FinanceFerret",
 PRIMARY KEY  (`ID`),
 KEY `Acct` (`ID_Acct`)

) ENGINE=MyISAM;</mysql>

This is pretty much the Access version, with a minor tweak for multiuser support (WhoEntered).

• AmountOrig: not sure if this is actually being used; I think it was a sort of "back up" for one of the balance numbers, but it might be superceded by the Immed/Stated balance fields. Look at the data... but I know I haven't looked at it directly in years. See if the code does anything with it.
• ToVerify: also never used so far; it might be better just to have a separate table, since this flag will only ever be set temporarily.
• IsApprox: this is for attempting to budget ahead -- you know your electric bill is coming in two weeks, but you don't yet know the total; this lets you enter an approximate amount to deduct from your available funds while still keeping track of the fact that you don't actually know the amount
• CalcBalance: one tricky issue here is that the balance should only be calculated based on the bank's sorting order, but the transactions can be displayed in lots of different sortings. Should there be an index field to show the order of all transactions at the time of the last balance, to make recalculation faster? Or would it not even have that effect?

Future changes:

• Change terminology from source/destination to local/remote -- local is the account whose transactions you're looking at, and remote is the other one:
  • ID_Acct -> ID_Local
  • ID_Dest -> ID_Remote
• ...or possibly it's not necessary to represent double-entry accounting with two actual records, if we can generate both views from the same data.

Trx Types

Transaction types

<mysql>DROP TABLE IF EXISTS `Trx Types`; CREATE TABLE `Trx Types` (

 `Code` varchar(50) NOT NULL            COMMENT "identifying abbreviation",
 `Descr` varchar(50) default NULL       COMMENT "description",
 `IsDebit` BOOL NOT NULL DEFAULT FALSE  COMMENT "TRUE = debits from source account; FALSE = credits to source account",
 `IsEquity` BOOL NOT NULL DEFAULT FALSE COMMENT "TRUE = affects equity only; FALSE = regular transaction",
 PRIMARY KEY  (`Code`)

) ENGINE=MyISAM DEFAULT;</mysql>

• The name of this table should be changed later because "Types" is a keyword and confuses the syntax display parser
• Transaction amounts are generally entered as positive numbers; the direction in which the amount is moving is determined by the transaction type.

Event Log

Copied, with only slight changes, from VbzCart tables. This version is suitable for re-use in other projects, but perhaps should be renamed EventLog or Events because the MySQL syntax highlighter thinks "log" is a keyword.

<mysql>DROP TABLE IF EXISTS `Event Log`; CREATE TABLE `Event Log` (

   ID INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
   EvWhen DATETIME NOT NULL COMMENT "when the event started",
   EvWhere varchar(255) COMMENT "where in the code the event happened (suitable for filtering)",
   Params varchar(255) COMMENT "any relevant parameters",
   Descr varchar(255) COMMENT "description of event",
   Code INT DEFAULT NULL COMMENT "numeric event code unique to location (EvWhere)",
   AppUser varchar(127) COMMENT "application username, for when we have a user-security system",
   SysUser varchar(127) COMMENT "who logged into the operating system (username)",
   Machine varchar(64) COMMENT "network name of machine from which the event was initiated, if applicable",
   isError BOOL COMMENT "FALSE = this is just a message or normal event; TRUE = there is a problem to fix (error)",
   isSevere BOOL COMMENT "TRUE = important enough to send email to admin immediately",
   Notes varchar(255) DEFAULT NULL COMMENT "manually-entered notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>

• Have to use "Ev" prefix because When and Where are keywords.