FinanceFerret

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Revision as of 21:02, 31 August 2008 by Woozle (talk | contribs) (→‎Trx Types: comments from old Access version; notes)
Jump to navigation Jump to search

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 the web site still shows it as "coming soon" -- possibly a domain squatter. "financeferret.net" and "financeferret.org" are available as of 2008-08-17, and I don't plan to register them (got enough just-for-the-name domains sitting around sucking $12/year out of my bank account).

From this, I determine that the name is more or less available for this use. If the registrant of financeferret.com would like to donate it to this project, I'd be happy to accept it. --Woozle 09:26, 17 August 2008 (EDT)

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

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` float           default NULL COMMENT "Amount added to ID_Acct account",
 `AmountOrig` float       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` float     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` float    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",
 `Descr` varchar(255)     default NULL COMMENT "text describing the transaction -- similar to memo field on check",
 `CalcBalance` float      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",
 `Mode` INT NOT NULL              COMMENT "1 = source, 2 = dest, 3 = equity (ok to hard-code this)",
 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.
  • Mode: "source" means an increase to the source account and a debit from the destination account; "dest" means the opposite. (These terms may need to be revised later.) "equity" means an increase to the equity account (equity transactions can be negative).

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.
Retrieved from "https://htyp.org/mw/index.php?title=FinanceFerret&oldid=10324"