Lots Architecture & Implementation Overview

Linas Vepstas <linas@linas.org> Last Revised May 2004

API Lots: Core Function for AR/AP, Inventory, Stock Lots, Cap Gains

One often needs to know that the item 'bought' in one transaction is the same one as the item 'sold' in a different transaction. Lots are used to make this association. One Lot holds all of the splits that involve the same item. A lot is typically formed when the item is bought, and is closed when the item is sold out. A lot need not be a single item, it can be a quantity of the same thing e.g. 500 gallons of paint (sold off a few gallons at a time). Lots are required to correctly implement invoices, inventory, depreciation and stock market investment gains.

'Lots' capture a fundamental accounting idea behind AR/AP, billing, inventory, capital gains, depreciation and the like. The basic idea is that a set of items is tracked together as a 'lot'; the date of creation of the lot helps determine when a bill is due, when depreciation starts, or the tax rate for capital gains on a stock market investment.

Definition

In GnuCash, a 'lot' will consist of a set of splits identified with a unique lot number. Any given split can belong to one lot and one lot only. All splits in a lot must also belong to the same account. Lots have a 'balance': the sum of all the splits in the lot. A lot is 'opened' when the first split is assigned to it. The date of that split is the 'opening date' of the lot, and its quantity is the 'opening balance'. A lot is 'closed' when its balance is zero; once closed, a lot cannot be re-opened. Open lots are always carried forward into the current open book; closed lots are left behind in the book in which they were closed.

Lots Are Used

The following sections review how lots are used to implement various accounting devices, and some of the related issues.

Billing

Tracking lots is the 'definition' of A/R and A/P. You want to be able to say that this bill is 120 days old, and its balance has been partly paid off; and you want to be able to do this whether or not there are also other bills, of different ages, to the same company. In GnuCash, a 'bill' is tracked as a lot: The 'billing date' is the opening date of the lot, and the 'balance due' is the balance of the lot. When the bill is 'paid in full', the lot is closed. The average age of receivables can be computed by traversing all lots, etc. Additional information about bills, such as a due date or payment terms, should be stored as kvp values associated with the lot.

Billing

Normally, there is a one-to-one correspondence between bills and lots: there is one lot per bill, and one bill per lot. (Note: this is not how gnucash invoices are currently implemented; this is a proposed implementation.)

For example: 

   invoice #258    customer XXX
   Order placed 20 December 2001
       quant (10)  gallons paint      $10/each   $100
       quant (2)   paintbrushes       $15/each    $30
                   sales tax                       $8.27
                                              ------------
                Total due                        $138.27

   Payment received 24 january  2002  $50      Balance Due: $88.27
   Payment received 13 february 2002  $60      Balance Due: $28.27
   Payment received 18 march    2002  $28.27   PAID IN FULL

In this example, the lot encompasses four transactions, dated December, January, February and March. The December transaction opens the lot, and gives it a non-zero balance. To be precise, the lot actually consists of four splits, belonging to four different transactions. All four splits are a part of an imagined "Accounts Receivable-Billing" account. The first split is for $138.27, the second split is for $50, the third split is for $60, and the fourth split is for $28.27. Note that the sales-tax split, and th paint/paint-brush splits are *NOT* a part of this lot. They are only a part of the transaction that opened this lot.

Note also that this example might also encompass two other lots: the transfer of paint may belong to a lot in the "Paint Inventory" account, and the split describing the paintbrushes might be a part of a lot in the "Paintbrush Inventory" account. These lots should not be confused with the invoice lot.

Inventory

The correct way of handling inventory under GnuCash is to have a separate account for each widget type. The account balance represents how many widgets there are in the warehouse. Lots offer an additional level of detail that can be useful when, for example, the widgets have an expiration date (e.g. milk) or vary slightly from batch to batch (e.g paint), and creating a new account to track these differences would be too heavyweight.

In order to track widgets as single units (and prohibit fractional widgets), set the currency denominator to 1. This will prevent fractional widgets from being transferred into/out of an account.

Note that using accounts to track the inventory of a grocery store causes an explosion of accounts, and this explosion would overwhelm many of the account GUI's in GnuCash. The GUI should probably be modified to treat inventory accounts in a special way, e.g. by not listing them in account lists, and providing an alternate management GUI.

Gains

In the United States, gains on stock investments are taxed at different rates depending on how long they were held before being sold. By using lots with an investment account, it becomes easy to track when any given share was bought or sold, and thus, the length of time that share was held.

Note, however, that using lots might cause some confusion for naive users, since some transactions might be split across different lots. For example, the user may have conceptually made the following transactions:

>        Date     Desc    Buy     Sell    Price    Value
>        18/1/01  XCORP   500             $10.00   $5000.00
>        21/3/01  XCORP   500             $12.00   $6000.00
>        14/7/02  XCORP           750     $20.00  $15000.00

However, the two buy transactions create different lots (because you can't add to a stock-investment lot after its been created, because the purchases occurred on different dates). Thus, when the shares are sold, the sale is split across two lots:

>        Date     Desc    Lot     Buy     Sell    Price    Value
>        18/1/01  XCORP   187     500             $10.00   $5000.00
>        21/3/01  XCORP   188     500             $12.00   $6000.00
>        14/7/02  XCORP   187             500     $20.00  $10000.00   
>        14/7/02  XCORP   188             250     $20.00   $5000.00

In the above, lot 187 was closed, and lot 188 has a balance of 250 shares. Note that we used a FIFO accounting method in this example: the oldest shares were sold first.

Note also, that by using lots in this example, we are able to accurately compute the gains in this transaction: it is 500*($20-$10) + 250*($20-$12) = $5000+$2000 = $7000. If we had used LIFO accounting, and sold the youngest shares first, then the profits would have been 500*($20-$12) + 250*($20-$10) = $4000 + 2500 = $6500. Thus, different accounting methods do affect income, and thus the tax rate.

Note that the two ledgers, the 'without-lots' and the 'with-lots' ledgers look different, even though the conceptual transactions are the same. If a naive user was expecting a 'without-lots' ledger, and is shown a 'with lots' ledger, they may get confused. I don't know of any simple way of dealing with this. In order to have lots work (thereby allowing cap gains reports to work correctly), the GnuCash engine *must* use the 'with-lots' representation of the data. If the GUI wants to hide the fact that there are lots under the covers, it must figure out a way of doing this (i.e. re-combining splits) on its own.

Closing of Books

A few words on lots and the closing of books. Without lots, there is really no way of correctly implementing book closing. That is because some reports, such as the capital-gains report, need to know not only the account balance, but also the purchase dates. Lots provide the natural mechanism for storing this information.

When a book is closed, any open lots must be moved into/kept with the open book. Since the splits in a lot belong to transactions, and transactions are 'atomic', this means that the associated transactions must be moved into the open book as well. A lot is considered closed when its balance is zero; when a book is closed, all of the lots that were closed stay with that book. That is, closed lots are not propagated forward into the currently open book.

Actually, its slightly more subtle than that. Not only must open lots stay with the open book, but so must all transactions that have splits that participate in teh open lot, and, by extension, all closed lots that participate in these 'open transations', ad infinitum.

The "Double Balance" Requirment

The following is a proposal for how to handle both realized and unrealized gains/losses by means of "adjusting transactions." It works for simple cases, but has issues for more complex cases.

Canonical transaction balancing: If all splits in a transaction are in the same 'currency' as the transaction currency, then the sum of the splits *must* equal zero. This is the old, existing double-entry requirement as implemented in Gnucash, and doesn't change.

If some splits are in one commodity, and others in another, then we can't force a zero balance as above. Instead, we will force a different requirement, the 'double-balance' requirement:

It is only by 'closing a lot' that one is able to regain 'perfect balance' in the books. That is, the 'double-balance' requirement is the generalization of the 'double-entry' requirement for stock accounts.

Note that because the 'adjusting transaction' has one split in dollars, and another split in RHAT shares (albeit for zero RHAT shares), it evades the old double-entry requriement, and will not be flagged as 'out of balance'. Note also that because the 'adjusting transaction' contains a split holding S (albeit zero S), it *must* be a part of a Lot.

The above seems to work for simple stock-transactions, but fails in other more complex cases. Here's an example.

Imagine 'S' is in euros, instead of 'RHAT'. So I sell 100 dollars, and buy 110 euros. This causes a lot to open up for the euros, with the lot currency 'L' in dollars. Now I try to transfer the euros to other euro accounts. What happens to the lot? Do I have to give up on it? How can I save this bad situation?

A similar problem occurs for more complex stock transactions: If I buy 100 shares of RHAT with Schwab, and transfer them to another account with Etrade, then I have the same lot closing problem. There's an even worse scenario, where I move to brazil, and take my RHAT stock (purchased in dollars) to my brazilian broker (who will sell them for cruzeiros).

Is the correct answer to just 'punt' in these cases? How is the closing of books to be handled in such a case?

GUI Elements:

FIFO's

What is a FIFO ? A FIFO is a type of accounting policy where income from selling inventory is accounted by selling the oldest inventory first. Thus, the profit/loss of the sale corresponds to the difference in price between when it was bought and sold. FIFO's are also used in depreciation schedules, and in other places.

Currently the only policy that is implemented in the cap-gains code is the FIFO policy. I beleive that it's been abstracted properly, so that it should be easy to add other policies, e.g. LIFO. See policy.c for what would need to be implemented.

Implementation

Every split has a pointer to a lot (which may be null). A lot has a list of splits in it (so that the other splits in the lot can be easily found). A lot does not need to maintain a balance (this is easy enough to calculate on the fly). A lot has a kvp tree (for storage of lot-related date, such as due dates for invoices, etc. A lot has a GUID.

From the memory-management and data-base management point of view, lots belong to accounts. The GnuCash account structure maintains a list of lots so that all lots belonging to an account can be quickly retreived. (In principle, the lots can be found by scanning every split in the account, but this is a painful process.)

Implementing Cap Gains (Is a Pain in the Neck)

Although Lots provide a good conceptual framework for determing gains or losses when a lot is closed, cap-gains on half-open lots present additional complexities. Consider the following stock purchase and subsequent sale of half the stock:

Account A is a stock account

Account B is a bank account

Account C is an income account 

                Acct A       Txn    Acct B    Acct C
Date     Action  Amt    Prc  Value    Amt      Amt
1/1/01   buy     100s   $10  $1000   ($1000)   -
2/2/02   sell    (50)s  $25  $1250    $1250    -
2/2/02   gain     -      -    $750            $750

The gain, shown in the third line, is computed as a straight sum of purchase price to sale price.

Should the above be reprsented as two transactions, or as three? One could, in principle, combine the second and third lines into one transaction. However, this has some drawbacks: computing the overall balance of such a transaction is tricky, because it has so many different splits (including, possibly, splits for brokerage fees, tax, etc. not shown). The alternative is to represent each line as a separate transaction. This has other drawbacks: If the date, amount, price or value is adjusted for the second transaction, the corresponding values must be adjusted for the third, and vice-versa.

Both schemes pose trouble for the register display: we want the stock register to show the gain as if it were a part of the stock sale; but the third line is a pair of splits, and we want to show only one of these two splits. Whichever method is chosen, the register has to filter out some of the splits that it shows.

The implementation that seems best is to represent the sale with two separate transactions: one for the sale itself, and a separate one for the gains. This makes computing the balance easier, although it makes the logic for setting the date more complex. Ughh..

Cap Gains Implementation Notes

Cap Gains will be handled by GnuCash as described above, using two distinct transactions. These transactions will be marked up using KVP, pointing to each other, so that the one can be found from the other. Implementation in src/engine/cap-gains.c

Quick API Overview:

Cap Gains Actual Implementation

Cap Gains are noted by creating a separate transaction with two splits in it. One of the splits is as described above: zero amount, non-zero value. There is a GUI requirement that when the looking at a gains transaction, certain things need to be kept in sync with the transaction that is the source of the gains. In order to accomplish this, the engine uses a set of 'dirty' flags, and a pair of pointers between the gains split and the source split, so that the one can be quickly found from the other.

Things kept in sync:

Things not kept in sync:

The posted date is kept in sync using a data-constraint scheme. If xaccTransactionSetDatePosted() is called, the date change is accepted, and the split is marked date-dirty. When the transaction is commited (using xaccTransCommitEdit()), the date-dirty flag is evaluated, and, if needed, the date changes are propagated/rolled back on the appropriate gains splits. Currently, one can only change the date on the gains-source transaction; the date on teh gains-recording split cannot be changed.

The value recorded by the gains transaction is updated whenever the value of the source changes. The actual update is done by the xaccSplitComputeCapGains() routine, via xaccScrubLot(), which is called at the time of xaccTransCommitEdit(). Note that two different things can affect the gains: a change in the value of the sale, and a change of the value of the purchase. A set of dirty flags are used to track these.

If the amount of a plit changes, then the lot that its in becomes potentially unbalanced. This requires the lot membership to be recomputed; this in turn may require the split to be split into peices, or to be recombined into one from several pieces.

TODO

Conversion

As Lots are put into production, old GnuCash datasets will need to be converted. Conversion will be done by running all splits in an account through an accounting policy (currently, there is only one policy, a FIFO). The goal of the policy is to match up purchases and sales so that these can be assigned to a Lot.

The conversion algorithm will work as follows: 

for each account {
   loop over splits {
      // perform the 'double-balance' check
      if (split commodity != transaction currency) account needs conversion
   }
   if account needs conversion
   for each split {
      If (split amount > 0)  create new lot, put split in lot.
      If (split amount < 0)  find oldest lot, put split in that lot
   }
}

See the file Scrub2.h for details of teh low-level API, and Scrub3.h for the high-level API.

There is a bit of a problem with this conversion proceedure: If the user had prviously recorded cap gains using a 'handmade' version of lots, those cap gains will be ignored and will throw off balances. User will need to hand-edit to recover.

GUI

A GUI for handling Lots is needed: ability to view a lot, and to cut/paste or move a split from one lot to another. Need a GUI to create a lot, maybe append some notes to it. Need ability to view account in lot-mode.

For automatically managing accounts, need to implement a variety of different accounting policies (of which the FIFO policy is currently implemented in the 'Scrub' routines).

Basic GUI

The goal of the basic GUI is to handle most usages of most lots in most places. There also need to be special-purpose dialogs for specific applications, e.g. stocks, inventory, etc.

Shows three areas:

Shows various buttons:

todo:

Status

Generated on Thu Sep 2 04:36:47 2010 for GnuCash by  doxygen 1.5.7.1