A solution to the commodity pricing problem
John Wiegley
One of the things which stalled Ledger development recently is that I'd never
found a truly satisfying way to handle the "commodity pricing problem". That
is, the current day value of a commodity can mean different things to
different people, depending on the accounts involved, the commodities, the
nature of the transactions, etc.
After experimenting with several different concepts and syntaxes, I found none
of them were either satisfying or general enough. My one attempt at providing
"fixated prices" was too specific, and too inelegant for many usage patterns.
This morning, however, I think I've finally cracked this nut; and in a way
which fits harmoniously into the Ledger architecture as if it were meant to be
there all along...
Here's how the new scheme will work: When a user specifies '-V', or '-X COMM',
they are requesting that some or all of the commodities in their Ledger file
be valuated as of today (or whatever --now is set to). But what does such a
valuation mean? This meaning shall be governed by the presence of a "VALUE"
metadata property, whose content is an expression used to compute that value.
If no VALUE property is specified, each posting is assumed to have a default,
as if you'd specified a global, automated transaction as follows:
= expr true
; VALUE:: market(amount, date, exchange)
This definition emulates the present day behavior of -V and -X (in the case of
-X, the requested commodity is passed via the string 'exchange' above).
One thing many people have wanted to do is to fixate the valuation of old
European currencies in terms of the Euro after a certain date:
= expr commodity == "DM"
; VALUE:: date < [Jun 2008] ? market(amount, date, exchange) : 1.44 EUR
This says: If --now is some old date, use market prices as they were at that
time; but if --now is past June 2008, use a fixed price for converting Deutsch
Mark to Euro.
Or how about never re-valuating commodities used in Expenses, since they
cannot have a different future value:
= /^Expenses:/
; VALUE:: market(amount, post.date, exchange)
This says the future valuation is the same as the valuation at the time of
posting. post.date equals the posting's date, while just 'date' is the value
of --now (defaults to today).
Or how about valuating miles based on a reimbursement rate during a specific
time period:
= expr commodity == "miles" and date >= [2007] and date < [2008]
; VALUE:: market($1.05, date, exchange)
In this case, miles driven in 2007 will always be valuated at $1.05 each. If
you use -X EUR to expressly request all amounts in Euro, Ledger shall convert
$1.05 to Euro by whatever means are appropriate for dollars.
Note that you can have a valuation expression specific to a particular posting
or transaction, by overriding these general defaults using specific metadata:
2010-12-26 Example
Expenses:Food $20
; Just to be silly, always valuate *these* $20 as 30 DM, no matter what
; the user asks for with -V or -X
; VALUE:: 30 DM
Assets:Cash
This example demonstrates that your VALUE expression should be as symbolic as
possible, using terms like 'amount' and 'date', rather than specific amounts
and dates. Also, you should pass the amount along to the function 'market' so
it can be further revalued if the user has asked for a specific currency.
Or, if it better suits your accounting, you can be less symbolic, which allows
you to report most everything in EUR if you use -X EUR, except for certain
accounts or postings which should always be valuated in another currency. For
example:
= /^Assets:Brokerage:CAD$/
; Always report the value of commodities in this account in terms of
; present day dollars, despite what was asked for on the command-line
; VALUE:: market(amount, date, '$')
I think this scheme, of using predicated value expressions which can be
generalized in automated transactions, and made specific via transaction and
posting-based metadata, provides sufficient flexibility to express most of the
use cases which have occurred on this topic.
Comments on the design welcome, as I haven't begun coding yet. Feel free to
pose scenarios, and I'll try to model them for you.
John
Russell Adams
The whole idea of making the VALUE of a commodity programmable makes
perfect sense.
Thanks!
------------------------------------------------------------------
Russell Adams RLA...@AdamsInfoServ.com
PGP Key ID: 0x1160DCB3 http://www.adamsinfoserv.com/
Fingerprint: 1723 D8CA 4280 1EC9 557F 66E8 1154 E018 1160 DCB3
Martin Michlmayr
> Comments on the design welcome, as I haven't begun coding yet. Feel
> free to pose scenarios, and I'll try to model them for you.
Thanks for posting this proposal. I have to think through some more
scenarios but I can already see how this VALUE property could be used
in a number of cases.
My main concern is that while the subject says it's a solution to the
commodity pricing problem, afaik it mostly addresses how -V is
handled, i.e the market value. While I find -V useful from time
to time, I'm generally more interested in -B, i.e. the cost basis.
I have three specific comments/questions:
a) How can you handle cost basis methods like FIFO and LIFO? Your
examples with market() only pass an amount and date, which isn't
enough for FIFO and LIFO since you need to know about your assets.
Could there be a built-in function to do FIFO and FIFO?
b) I don't see how this VALUE property can differentiate between -V
and -B. Does this imply that you want to get rid of the -B option and
simply let users define what VALUE they get with -V? If so, I think
this would be a bad idea... I really like the ability to see different
valuation methods using command line options (i.e. -B for cost basis
and -V for market value). (Incidentally, while I initially liked your
example of using the posting date for Expenses, I later realized that
I sometimes use -V to see what my expenses (in a foreign currency)
would have been if I bought everything at today's exchange rate.)
c) I never fully understood what -X does exactly but afaik -X is a
special version of -V. However, I believe that -X should _only_ do
conversion. This would allow -X to be combined with other options,
such as -X and -V. Example: let's say I bought 10 shares for 10.00
GBP and they are now worth 15.00. Because my main assets are in EUR,
I want to see what those shares are worth in EUR. Since I'm
conservative I want to see the cost basis, i.e. I want to use -B and
-X EUR together. (This actually works today but I'm told this is an
accident and won't work in all cases.)
Any comments on these points?
--
Martin Michlmayr
http://www.cyrius.com/
John Wiegley
> a) How can you handle cost basis methods like FIFO and LIFO? Your
> examples with market() only pass an amount and date, which isn't
> enough for FIFO and LIFO since you need to know about your assets.
> Could there be a built-in function to do FIFO and FIFO?
Ledger presently has no way of handling such things as FIFO and LIFO.
If you specify an unadorned commodity name, like AAPL, it will balance
against itself. If --lots are not being displayed, then it will appear
to balance against any lot of AAPL.
If you specify an adorned commodity, like AAPL {$10.00}, it will also
balance against itself, and against any AAPL if --lots is not specified.
But if you do specify --lot-prices, for example, then it will balance
against that specific price for AAPL.
I may, for the sake of reporting *only*, be able to implement some sort
of guessing strategy, based on the order in which transactions appear in
the data file... But I'll have to think about this a lot more, and it
would be a 3.1 thing.
> b) I don't see how this VALUE property can differentiate between -V
> and -B. Does this imply that you want to get rid of the -B option and
> simply let users define what VALUE they get with -V? If so, I think
> this would be a bad idea... I really like the ability to see different
> valuation methods using command line options (i.e. -B for cost basis
> and -V for market value). (Incidentally, while I initially liked your
> example of using the posting date for Expenses, I later realized that
> I sometimes use -V to see what my expenses (in a foreign currency)
> would have been if I bought everything at today's exchange rate.)
-V and -B are entirely unrelated. Perhaps I could support a BASIS
property setting, for customizing -B in the same way VALUE
customizes -V...
> c) I never fully understood what -X does exactly but afaik -X is a
> special version of -V. However, I believe that -X should _only_ do
> conversion. This would allow -X to be combined with other options,
> such as -X and -V. Example: let's say I bought 10 shares for 10.00
> GBP and they are now worth 15.00. Because my main assets are in EUR,
> I want to see what those shares are worth in EUR. Since I'm
> conservative I want to see the cost basis, i.e. I want to use -B and
> -X EUR together. (This actually works today but I'm told this is an
> accident and won't work in all cases.)
-V asks for the present day value of all commodities, and lets Ledger
pick the target commodity based on its own hueristics. -X is the same
as -V, except that it overrides those hueristics and forces the target
commodity. (Although, as you've seen, the VALUE property could now
countermand that).
There are reasons why -X can't be applied to any report. Mainly it has
to do with rounding. For example, let's say I have 10 postings that
each trade 1 DM, and the value of 1 DM is 0.001 EUR. If I add all
10 DM and then apply -X, I get 0.01 EUR. But if I apply -X to each
1 DM and *then* total them, I get 0.00 EUR.
This becomes very important to Ledger because -X is applied to totals,
not just to individual amounts. I'm going to have to use some magic
internally to avoid this problem with the VALUE property (in most, but
not all, cases).
And so, -X gets applied after, when the posting-origin of the
commodities has been lost -- required information if a basis cost
calculation is to be deferred.
The alternative would involve ever-growing lists of individual amounts,
which would slow many parts of Ledger from O(N) to O(N^2). Plus, it
still wouldn't solve the rounding problem.
John
Zack Williams
>> examples with market() only pass an amount and date, which isn't
>> enough for FIFO and LIFO since you need to know about your assets.
>> Could there be a built-in function to do FIFO and FIFO?
>
> Ledger presently has no way of handling such things as FIFO and LIFO.
The fundamental question is whether ledger should get full "inventory
management" functionality.
I'm thinking it shouldn't, as it introduces a whole range of issues -
for example, if you're doing FIFO or LIFO, how do you explain to
someone who purchased 4 widgets from you why some are one price, and
some are another? Assuming you're using this for sales of goods,
then you get into dealing with markup, sales tax, etc.
While this would be nice, I'm thinking that creating a separate
program that handles an inventory system and could interact with
ledger may be the more sane course of action.
I also tend to think that having VALUE:: be behind a semicolon could
be confusing to users (as it would be assumed to be a comment), and a
pain for parser writers as they couldn't just discard everything from
the semicolon to EOL as being a comment - they'd have to try to parse
it.
- Zack
John Wiegley
> I also tend to think that having VALUE:: be behind a semicolon could
> be confusing to users (as it would be assumed to be a comment), and a
> pain for parser writers as they couldn't just discard everything from
> the semicolon to EOL as being a comment - they'd have to try to parse
> it.
They will already have to parse it, since this is the syntax for
metadata annotations.
John
Zack Williams
>> be confusing to users (as it would be assumed to be a comment), and a
>> pain for parser writers as they couldn't just discard everything from
>> the semicolon to EOL as being a comment - they'd have to try to parse
>> it.
>
> They will already have to parse it, since this is the syntax for
> metadata annotations.
Ah, gotcha - I haven't had the occasion to use those yet, thus the
oversimplification.
Thanks,
Zack
Martin Michlmayr
> The fundamental question is whether ledger should get full "inventory
> management" functionality.
I think it needs this functionality because it's required for any
serious financial accounting. It's not just about the management of
inventory per se. You'll also need FIFO/LIFO/AVCO for tracking
shares/funds, which a lot of people have.
> I'm thinking it shouldn't, as it introduces a whole range of issues -
> for example, if you're doing FIFO or LIFO, how do you explain to
> someone who purchased 4 widgets from you why some are one price, and
> some are another?
I think you're confusing purchase price with sales price.
Let's say a company buys 3 widgets at 15 USD and 1 at 20 USD and sells
the 4 widgets for 25 USD each, it would then make 10 USD on 3 widgets
and 5 USD on 1.
Martin Michlmayr
> Ledger presently has no way of handling such things as FIFO and LIFO.
Yeah, I know... but I think it's a feature that ledger should
eventually get (obviously not for 3.0).
> If you specify an adorned commodity, like AAPL {$10.00}, it will also
> balance against itself, and against any AAPL if --lots is not specified.
> But if you do specify --lot-prices, for example, then it will balance
> against that specific price for AAPL.
>
> I may, for the sake of reporting *only*, be able to implement some sort
> of guessing strategy, based on the order in which transactions appear in
> the data file...
Why for reporting only? It seems to me that ledger has all the
information to do FIFO and LIFO properly (i.e. to remove the right
commodities from the list). Let's take this example:
2011-01-01 * Buy AAA
Assets:Shares 5 AAA @ 10.00 EUR
Assets:Cash
2011-01-03 * Buy AAA
Assets:Shares 2 AAA @ 10.00 EUR
Assets:Cash
2011-01-11 * Buy AAA
Assets:Shares 5 AAA @ 12.00 EUR
Assets:Cash
2011-01-21 * Buy AAA
Assets:Shares 5 AAA @ 13.00 EUR
Assets:Cash
So we end up with (ledger --lots):
5 AAA {10.00 EUR} [2011/01/01]
2 AAA {10.00 EUR} [2011/01/03]
5 AAA {12.00 EUR} [2011/01/11]
5 AAA {13.00 EUR} [2011/01/21] Assets:Shares
So if I sell 6 shares now, according to FIFO, I would do:
2011-02-01 * Sell AAA
Assets:Shares -5 AAA {10.00 EUR} [2011/01/01] @ 13.50 EUR
Assets:Shares -1 AAA {10.00 EUR} [2011/01/03] @ 13.50 EUR
Assets:Cash
ledger --lots:
1 AAA {10.00 EUR} [2011/01/03]
5 AAA {12.00 EUR} [2011/01/11]
5 AAA {13.00 EUR} [2011/01/21] Assets:Shares
According to LIFO, I would do this instead:
2011-02-01 * Sell AAA
Assets:Shares -5 AAA {13.00 EUR} [2011/01/21] @ 13.50 EUR
Assets:Shares -1 AAA {12.00 EUR} [2011/01/11] @ 13.50 EUR
Assets:Cash
In other words, you can manually do FIFO and LIFO with ledger already.
However, it would be great if ledger would make this easier, e.g. that
you could specify:
2011-02-01 * Sell AAA
Assets:Shares -6 AAA {FIFO} @ 13.50 EUR
Assets:Cash
and ledger would iterate through all AAA commodities and take out the
right ones (after all, it knows the date and price).
The only thing I don't think is possible with ledger at the moment is
average cost. I'm also not sure how --lot-dates should behave for
average cost.
> There are reasons why -X can't be applied to any report. Mainly it has
> to do with rounding. For example, let's say I have 10 postings that
> each trade 1 DM, and the value of 1 DM is 0.001 EUR. If I add all
> 10 DM and then apply -X, I get 0.01 EUR. But if I apply -X to each
> 1 DM and *then* total them, I get 0.00 EUR.
Thanks for the explanation... what I was thinking of is that ledger
would just produce a report according to -V or -B or whatever and
*then* convert it with -X. I use a shell script to do this for now:
GBP2EUR="117/100"
eurgbp=$(ledger -f $FILE -p "until $YEAR-$NEXT_MONTH-01" -B bal "^assets" "^liabilities" | egrep " (EUR|GBP)$" | tail -n 2)
eur=$(echo "$eurgbp" | grep "EUR" | sed 's/ EUR//')
gbp=$(echo "$eurgbp" | grep "GBP" | sed 's/ GBP//')
eur=$(echo "$eur" | sed 's/\..*//')
gbp=$(echo "$gbp" | sed 's/\..*//')
gbpineur=$(($gbp*$GBP2EUR))
echo " " $(($eur + $gbpineur)) " EUR Total"
I'm kinda surprised that you no longer think it's a good idea to split
-X from -V. Last time I brought this up on IRC, you thought it was a
good idea:
10:44 < johnw> I think having -H, in addition to -X, may make what you want
to see both natural and simple
10:45 < johnw> you'd use -H for income/expense accounts, and -X for
assets/liabilities
10:45 < johnw> -H = historical values
10:45 < johnw> -X = current exchange values
10:45 < tbm> so what's the difference between -X and -V again?
10:45 < johnw> -V is an automated version of -X
10:45 < johnw> it tries to figure out what the reported commodity should be
10:45 < johnw> we may then need an automated version of -H, to complete the
reflection
10:46 < johnw> btw, this is just an inside-out version of my "final"
feature :)
10:46 < tbm> why not change the meaning of -X to _only do conversion_? And
then you could combine -X with -B, -V or -H
10:46 < johnw> instead of having it be syntactic, we're moving the semantic
difference to a difference in options
10:46 < johnw> oh HMM
10:46 < johnw> -X with -B, -V and -I
10:46 < johnw> (and -O, incidentally)
10:46 < johnw> O = amount, B = cost, V = market value, I = price
10:47 < johnw> that's really an excellent suggestion
10:48 < johnw> i'd still need a flag to mean "historical" vs "current"
10:48 < johnw> as well as "target commodity" (-X)