commodity revaluation / income/expenses

[Christophe Rhodes]

Hi,

I'm trying to produce a simplified balance sheet and income/expense
statement for a set of accounts. I can produce the opening balance with:
ledger -f ledger.dat -V -X £ --end $begin -n bal Assets Liabilities
the income/expense report with
ledger -f ledger.dat -B -X £ --begin $begin --end $end bal Income Expenses
and the final balance sheet with
ledger -f ledger.dat -V -X £ --end $end bal Assets Liabilities
and this makes me generally happy.

However, in the presence of fluctuating non-£ commodities, the sum of
balances from report (1) and report (2) will not add to the balance of
report (3); there will be an unaccounted for gain/loss in the balance
from the change in valuations. That's fine, but I'd love to get at that
value to be able to display it, ideally on a per-account basis.

My first try:
ledger -f ledger.dat -G -X £ --begin $begin bal Assets Liabilities
does not work, as it does not capture any changes in accounts with
commodities from transactions earlier than $begin. It does produce a
useful-looking per-account set of balances that are right for the
changes that it has captured; it just doesn't get everything.

My second try:
ledger -f ledger.dat -G -X £ -d "d>=[$begin]" bal Assets Liabilities
gives me an error
While evaluating value expression:
(d >= [2012/01/01])
^^^^^^^^^^^^^^^^^^^
While comparing if 2013/03/09 22:31:02 is less than 2012/01/01:
Error: Cannot compare a date/time to a date
which seems to be trying and failing to compare now to the date I gave.

My third try:
ledger -f ledger.dat -G -X £ -d "d>=[$begin]" reg Assets Liabilities
does actually display all the expected revaluation transactions, but
doesn't give me a handy by-account summary (or indeed enough information
to reconstruct the accounts to which each revaluation would pertain).

Really I'd want my second try to work, to match my mental model of how
to drive ledger... but maybe that's just me.

Any help on this would be appreciated.

Thanks,

Christophe

[Christophe Rhodes]

[I wrote]:

> I'm trying to produce a simplified balance sheet and income/expense

> statement for a set of accounts. [depreciation/revaluation]

> My first try:
> ledger -f ledger.dat -G -X £ --begin $begin bal Assets Liabilities
>

> My second try:
> ledger -f ledger.dat -G -X £ -d "d>=[$begin]" bal Assets Liabilities
> gives me an error
>

> My third try:
> ledger -f ledger.dat -G -X £ -d "d>=[$begin]" reg Assets Liabilities

> Really I'd want my second try to work, to match my mental model of how
> to drive ledger... but maybe that's just me.

Thinking about this some more, I'm less convinced that my second try
should give me what I want, though I think it should probably not error.

What I actually want, I think, is the difference in value at $end and at
$begin in my preferred currency of the current contents of the accounts.
So, I read the overview of format strings, and tried
ledger -f ledger.dat --format "%A %(P(T,[$end],\"£\")-P(T,[$begin],\"£\"))\n" bal
and it is nearly there. However, for commodities where the first
recorded price is after $begin, this fails. Given the ledger file
below:

--- begin ---
2012/06/01 Foo
Assets:Foo FOO1
Assets:Checking £-1.00

P 2012/09/01 FOO £0.9

2013/02/01 Bar
Assets:Bar BAR1
Assets:Checking £-1.00

P 2013/03/01 FOO £0.95
P 2013/03/01 BAR £1.3
--- end ---

the ledger invocation above produces (reformatted slightly)

Assets:Bar BAR-1 {£1.00} [2013/02/01]
£1.30
Assets:Checking 0
Assets:Foo £0.05

So the Assets:Foo change from commodity fluctuation has correctly been
identified as +£0.05 (1 FOO changing from value £0.9 to £0.95); the
Checking account has correctly been identified as 0. However, this
hasn't worked for the Bar account: the call to P(T,[2013/01/01],"£") for
the BAR1 bought on 2013/02/01 has not successfully converted the
commodity to a market price in £. I think.

What would make this work for me is if the market price for a commodity
before the first price given were the earliest price (or lot price).
Then the BAR1 would be valued on 2013/01/01 as £1, giving a difference
of £0.30, which would be the right answer from the point of view of
commodity fluctuation. My first question is whether this is a sensible
definition of the commodity price at dates earlier than the first price
given, and whether making that definition would break anything. If that
definition is OK, my next question would be how to make that happen...

Thanks,

Christophe

[Christophe Rhodes]

Christophe Rhodes writes:

Sorry, not quite enough detail in my previous message to allow
brain-free reproduction of my issue:

> Given the ledger file below:
>
> --- begin ---
> 2012/06/01 Foo
> Assets:Foo FOO1
> Assets:Checking £-1.00
>
> P 2012/09/01 FOO £0.9
>
> 2013/02/01 Bar
> Assets:Bar BAR1
> Assets:Checking £-1.00
>
> P 2013/03/01 FOO £0.95
> P 2013/03/01 BAR £1.3
> --- end ---
>
> the ledger invocation above produces (reformatted slightly)

The ledger invocation referred to, with all variables resolved, is
ledger --format '%A %(P(T,[2013/03/31],"£")-P(T,[2013/01/01],"£"))\n' bal
(and a suitable way of pointing ledger to the data file)

> Assets:Bar BAR-1 {£1.00} [2013/02/01]
> £1.30
> Assets:Checking 0
> Assets:Foo £0.05

Best,

Christophe

[Christophe Rhodes]

Christophe Rhodes writes:

[ More discussion below, but first a statement of exactly what I think I
want ]

Consider the following ledger file:

--- begin ---
2012/06/01 Foo
Assets:Foo FOO1
Assets:Checking £-1.00

2012/06/01 Quux
Assets:Quux QUUX2
Assets:Checking £-2.00

P 2012/09/01 FOO £0.9
P 2012/09/01 BAZ £0.9

2013/02/01 Bar
Assets:Bar BAR1
Assets:Checking £-1.00

2013/02/01 Baz
Assets:Baz BAZ1
Assets:Checking £-1.00

2013/02/01 Quux
Assets:Checking £1.20
Income:Capital Gain £-0.20
Assets:Quux QUUX-1

P 2013/03/01 FOO £1.1
P 2013/03/01 BAR £1.2
P 2013/03/01 BAZ £1.2
P 2013/03/01 QUUX £1.3
--- end ---
and the following reports, first a balance sheet as of 2013/01/01:

ledger -f foo.dat --end 2013/01/01 -X £ bal Assets Liabilities

£-0.10 Assets
£-3.00 Checking
£0.90 Foo
£2.00 Quux
--------------------
£-0.10

then an income/expense for the period 2013/01/01--2013/04/01:

ledger -f foo.dat --begin 2013/01/01 --end 2013/04/01 --invert -X £ bal Income Expense

£0.20 Income:Capital Gain

then a balance sheet for 2013/04/01:

ledger -f foo.dat --end 2013/04/01 -X £ bal Assets Liabilities

£1.00 Assets
£1.20 Bar
£1.20 Baz
£-3.80 Checking
£1.10 Foo
£1.30 Quux
--------------------
£1.00

Now, the "problem" is that the difference in the balance sheets is not
equal to the income/expense over the period. What I am trying to do is
generate the report which "explains" the difference -- calculates the
difference in account at the end of the period due to commodity value
fluctuations. Specifically, the report that I would like to generate
is:

£0.90 Assets
£0.20 Bar
£0.20 Baz
£0.20 Foo
£0.30 Quux
-------------------
£0.90

(To explain these figures: the one remaining QUUX has gone up in value
by £0.3 since 2013/01/01; the FOO which has been held throughout the
period has gone up by £0.2. The value of the BAR which was bought
during the period has gone up by £0.2, and the value of the BAZ likewise
-- the tricky bit there is that the price of BAZ at the start of the
period is irrelevant; it's the value at purchase which is relevant.
And of course £-0.10 + £0.20 + £0.90 = £1.00 and the world is once more
in balance.)

I can /nearly/ generate this report. With

ledger -f foo.dat --format '%A %(P(T,[2013/03/31],"£")-P(T,[2013/01/01],"£"))\n' bal Assets

I get (slightly reformatted)
Assets BAR-1 {£1.00} [2013/02/01]
£2.00

Assets:Bar BAR-1 {£1.00} [2013/02/01]

£1.20
Assets:Baz £0.30
Assets:Checking 0
Assets:Foo £0.20
Assets:Quux £0.30

BAR-1 {£1.00} [2013/02/01]
£2.00

So here the answer for FOO and QUUX is exactly right. Unfortunately,
the answer for BAR and BAZ is not; for BAR, ledger doesn't give me a
value for the commodity at all, and for BAZ it takes the too-early value
(before the commodity was purchased).

If instead of P (market) in the value expression above, there was a
valuation function which valued at the later of the date given and the
lot purchase price, I think the report would be exactly what I want
(well, minus all the fancy formatting :-). I have made several false
starts on this already, so I could be wrong, but I did draw a diagram on
my office wall to convince myself this time...

Thoughts? I hope that this example is clear enough to explain in one
place what I'm trying to do.

Thank you,

Christophe