periodic value reports should use the market prices of each period

[vessenes]

I am having trouble with hledger's balance sheet reporting and I think there's a bug in how the reports work.

For reference, I have a business I'm modeling, something like a gold mine where I get revenues directly in a commodity. The business has a hedging component, so it might hold or sell the commodity on any given day. It also has (dollar-denominated) expenses.

To test out the hedging strategy, I'm using hledger against a file to see what period balance sheet reports would be, and I want to incorporate the mark-to-market impacts of hedging or not.

The --value flag sounded good, but it does something very strange: the balance command books commodities at what looks like their final price in the journal.

Perusing this conversation https://groups.google.com/forum/#!topic/hledger/e8Ss7ZL4ADI makes me think that might have been the intent for the initial push on this very, very useful functionality. But, I might be doing something wrong -- let me know.

My lines in the ledger for pricing are like this: P 2015/03/16 AU 1200.23 implying a $1200.23 price per ounce of gold. There is one per day, and they are in order with other journal transactions.

My reasoning on why this is wrong is pretty simple: balance sheets / statements should never change based on future information. They are supposed to be an immutable record of the state of finances at a moment in time.

Am I understanding the current situation correctly, and if so, any thoughts on a fix? I'm a total haskell newbie but would be willing to give it a go if you pointed me in the right direction.

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[simonmichael]

Thanks for the report. Yes, balance -V uses the price as of the report period end date (or the end of your journal) (http://hledger.org/manual.html#market-prices).

As I said in the thread, I think using the price at the end of each report interval (for a multi-column balance report) would be more useful. But this might still not be what you want; can you give an example showing how you think it should work ?

What you say about statements depending only on past information makes perfect sense. Though, *ledger's basic commands, especially balance, are more low-level than standard accounting reports; hledger's balance sheet/income statement/cashflow commands are supposed to be a bit more like real accounting statements. I agree balance -V could probably do something better here. I'm not sure if that will make it work differently from Ledger's balance -V.

[vessenes]

I guess I am mostly motivated by having 'real' balance sheets, and I don't know enough about hledger's internals to speculate how you'd want to implement.

Here's how I, as a business person, would want to look at a Balance Sheet with some Gold on it.

Say I obtain 1 oz of gold a week, and the prices are 500, 1000, 250 respectively at the end of the week per oz. The cumulative balance sheet without the -V I would expect to look like this:

                                  Week 1    |    Week 2    |   Week 3
-------------------------------------------------------------------
Assets:AU                           1  AU   |      2 AU    |     3 AU

And the Cumulative P&L would read

Income:Mining:AU                    -1 AU   |     -2 AU    |    -3 AU

I then run the cumulative reports dollar denominated. (with -V as we've been discussing). I would expect something like the following:

                                                     Week 1    |    Week 2    |   Week 3
------------------------------------------------------------------------------------------
Assets:AU                                              $500    |      $2000   |   $750

The P&L would Contain

Income:Mining:AU                                       -$500   |     -$1500    | -$1750
Other Income: Mark To Market                               $0  |     -$ 500    |  $1000

Those Income statements would obviously flow through to Equity on the Balance Sheet.

Interpreted: Week 1 we made $500 mining. Week 2 we made $500 mining and $500 in market appreciation from holding the gold. Week 3 we made $250 in Mining Income and Lost $1000 due to gold prices dropping.

NB, I'm also looking at beancount right now, which seems to have a lot here, including basis calculations. So, don't push ahead on this just on account of me. 😄 But I think this would add significant correctness to reports. I'd hate to think of people filing reports with tax authorities without this working.

[simonmichael]

Yes beancount has more in this area, but this is very helpful - thanks. It sounds like using the price at the end of each report period will work well - good.

"Mark to market" is a useful term I didn't know. Ledger reports that change as "capital gains/losses"; hledger doesn't report this explicitly yet. Isn't there some error in the last P&L example ? In week 2 I'd think you made $1000 mining (1 oz of gold at that week's price of $1000) and $500 on the revaluation of week 1's oz from $500 to $1000.

[vessenes]

Capital Gains/Losses makes sense to me as well.

I think it's right -- it's a cumulative P&L by period. If the P&L isn't cumulative, then you're correct. Net Income for the week (marked to market) was $1500. In my example I carried forward the prior week's mining income (hence cumulative). This has the impact of allowing market change impacts on holdings to be pulled out into a separate row.

[devbanana]

This seems like an old issue, but I would also like to see this sort of functionality. I was surprised when the multi-column balance view showed the prices as of the latest date.

[jeauxlb]

Just adding my voice to the chorus of support for this feature. I have recently incorporated stocks into my hledger reporting and was also surprised to find hledger not reflecting then-current prices over reporting windows.

[simonmichael]

Would someone like to work on this ? I guess it will be not too hard.

[simonmichael]

Recent work following on from #999 has moved this forward. The new --value-at flag allows calculating value as of period end, or transaction date, or an abitrary date: https://hledger.org/manual.html#value-at-another-date .

[simonmichael]

It seems not quite baked yet though; some reports work better than others. Robust soon I hope.

[erikryb]

Great to see this moving forward! I tested it and think I found an issue. If I run hledger bal assets:investments -V -H -D -E --value-at=period -p thismonth and the account assets:investments has no transactions in the selected period, the value-at flag seems to have no effect.

[simonmichael]

This turns out to be quite involved.. valuation strategies interact with every report mode! I've just pushed more work on it, here are the latest docs:
https://hledger.org/manual.html#value-at-another-date
https://hledger.org/manual.html#reports-supporting-value-at
and tests:
https://raw.githubusercontent.com/simonmichael/hledger/master/tests/journal/market-prices.test

@erikryb thanks for the testing. Would you mind retesting with the latest and sending a reproducible example.

[erikryb]

I tested the latest version. I have the following ledger:

2019/04/01 Test
    account1  1A
    account2 -1B

P 2019-04-01 A B 1.0
P 2019-04-08 A B 1.1
P 2019-04-15 A B 1.2
P 2019-04-22 A B 1.3
P 2019-04-29 A B 1.4

The command hledger bal account1 -VHWE --value-at=p -b 2019-04-01 -e 2019-04-30 gives me

          || 2019/04/07  2019/04/14  2019/04/21  2019/04/28  2019/05/05
==========++============================================================
 account1 ||      B 1.0       B 1.1       B 1.2       B 1.3       B 1.4
----------++------------------------------------------------------------
          ||      B 1.0       B 1.1       B 1.2       B 1.3       B 1.4

as is expected. However, if I change the begin date to 2019-04-08, running the command hledger bal account1 -VHWE --value-at=p -b 2019-04-08 -e 2019-04-30, this gives me

          || 2019/04/14  2019/04/21  2019/04/28  2019/05/05
==========++================================================
 account1 ||      B 1.4       B 1.4       B 1.4       B 1.4
----------++------------------------------------------------
          ||      B 1.4       B 1.4       B 1.4       B 1.4

I would expect that the values should be the same as in the first example, but it seems that the last price is used for all periods.

[simonmichael]

Agreed. Thanks for the test case!

[simonmichael]

Here's a new UI proposal:

-B       show amounts at cost, using transaction prices (similar to Ledger)
-V       show amounts converted to their default valuation commodity using current market prices (similar to Ledger)
-X COMM  show amounts converted to the specified commodity using current market prices (similar to Ledger)
--value=(cost|end|now|DATE)[,COMM]
         show amounts converted to:
          cost (like -B),
          default valuation commodity (or COMM) using market prices at period end(s)
          default valuation commodity (or COMM) using current market prices
          default valuation commodity (or COMM) using market prices on DATE

Flag combining: rightmost B/V/X/value flag wins.

[simonmichael]

Amendment: in multiperiod reports, -V/-X use period end prices.

[danielverdugo]

in multiperiod reports, -V/-X use period end prices.

That's perfect.

[simonmichael]

This has landed in master, except for the -X/[,COMM] part. Basically --value-at=transaction has been dropped for now, --value=cost has been added, -V now has the ledger-compatible --market long form, --B/-V/--value are unified and things are simpler.

I also thought of using --at VALUATIONTYPE and --in COMM instead of --value, it reads nicely but maybe it's more complex.

[the-solipsist]

I went with the last (-H disables -T), so the above command now produces:

I'm not sure this is a good solution, since --historical can be used with ought to be able to be used with --change, in which case it makes sense to combine it with --row-total.

[simonmichael]

I believe --change/--cumulative/--historical are mutually exclusive, and the rightmost is used. It looks ok from a quick test.

[simonmichael]

A vagueness in my proposal above: it suggests --value=cost,COMM would be allowed. I was thinking that should mean: "convert to cost using transaction price, then convert that to COMM using the market prices at the posting date". This gives you something like what we had before with --value-at=transaction.

Alternately, it could mean "convert to cost, then convert to COMM using market prices at the (sub)period end". This is easier to code, so I'll just try this first.

[the-solipsist]

I believe --change/--cumulative/--historical are mutually exclusive, and the rightmost is used. It looks ok from a quick test.

--historical and --change ought not be mutually exclusive, even if that is how things currently are. --cumulative and --change ought to be mutually exclusive, since logically they mean the opposite of each other. However, speaking from first principles, --historical may logically be cumulative or non-cumulative. By default it is cumulative, but with --change one should be able to make it produce a non-cumulative period report if one so desires.

Example:

$ h print -f /tmp/temp
2019/05/01 Transaction 001
    A           ₹1.00
    B          ₹-1.00

2019/05/08 Transaction 002
    A           ₹2.00
    B          ₹-2.00

2019/05/14 Transaction 003
    A           ₹3.00
    B          ₹-3.00

2019/05/21 Transaction 004
    A           ₹4.00
    B          ₹-4.00

2019/05/28 Transaction 005
    A           ₹5.00
    B          ₹-5.00

When I query this with --historical and --row-total:

$ h bal -f /tmp/temp -W --historical --row-total
Ending balances (historical) in 2019/04/29-2019/06/02:

   || 2019/05/05  2019/05/12  2019/05/19  2019/05/26  2019/06/02    Total 
===++=====================================================================
 A ||      ₹1.00       ₹3.00       ₹6.00      ₹10.00      ₹15.00   ₹35.00 
 B ||     ₹-1.00      ₹-3.00      ₹-6.00     ₹-10.00     ₹-15.00  ₹-35.00 
---++---------------------------------------------------------------------
   ||          0           0           0           0           0        0 

As you can see, the "Total" is pointless since --row-total is meaningless when a cumulative table is created (whether by using --cumulative or by using --historical (which by default is cumulative).

And when I query this with --change as well:

$ h bal -f /tmp/temp -W --historical --row-total --change -b 2019-05-01
Balance changes in 2019/04/29-2019/06/02:

   || 2019/04/29w18  2019/05/06w19  2019/05/13w20  2019/05/20w21  2019/05/27w22    Total 
===++====================================================================================
 A ||         ₹1.00          ₹2.00          ₹3.00          ₹4.00          ₹5.00   ₹15.00 
 B ||        ₹-1.00         ₹-2.00         ₹-3.00         ₹-4.00         ₹-5.00  ₹-15.00 
---++------------------------------------------------------------------------------------
   ||             0              0              0              0              0        0

This is a meaningful report (though, interestingly, the column headers are drastically different from the previous report for reasons I'm unable to grasp). Also, strangely, the balance change is from "2019/04/29" instead of "2019-05-01". "Total" makes sense here, since it is non-cumulative.

$ h bal -f /tmp/temp -W --row-total --change -b 2019-05-06
Balance changes in 2019/05/06-2019/06/02:

   || 2019/05/06w19  2019/05/13w20  2019/05/20w21  2019/05/27w22    Total 
===++=====================================================================
 A ||         ₹2.00          ₹3.00          ₹4.00          ₹5.00   ₹14.00 
 B ||        ₹-2.00         ₹-3.00         ₹-4.00         ₹-5.00  ₹-14.00 
---++---------------------------------------------------------------------
   ||             0              0              0              0        0 

As you point out --change negates --historical, but I would have expected a report like this:

   || 2019/05/06w19  2019/05/13w20  2019/05/20w21  2019/05/27w22    Total 
===++=====================================================================
 A ||         ₹3.00          ₹3.00          ₹4.00          ₹5.00   ₹15.00 
 B ||        ₹-3.00         ₹-3.00         ₹-4.00         ₹-5.00  ₹-15.00 
---++---------------------------------------------------------------------
   ||             0              0              0              0        0 

There is no reason why "historical" should not be non-cumulative.

[simonmichael]

Thanks for the examples, I haven't worked through them yet. I defined those terms at https://hledger.org/manual.html#multicolumn-balance-report , and you seem to be proposing new definitions. Could you write these out briefly and say how they are an improvement ?

[the-solipsist]

The only difference is that in place of:

With --historical/-H: each column shows the actual historical ending balance for that period, accumulating the changes across periods, starting from the actual balance at the report start date. This is useful eg for a multi-period balance sheet, and when you are showing only the data after a certain start date:

"With --historical/-H: each column, by default is cumulative, and shows the actual historical ending balance for that period, starting with the historically accurate balance (including undisplayed prior postings) at the report start date. This is useful, e.g., for a multi-period balance sheet, and when you are showing only the data after a certain start date. You can override the default and choose to show only the difference between the periods/columns, instead of the ending balance, by using --change flag. This will still start with the historically accurate balance at the report start date."

Explanation: "Historical" means that the balance will take into account undisplayed transactions prior to the -b date. This is the meaning it has with the register command. There is no need to create a new, and different meaning for multi-column layouts. But a "default" is needed as to whether the --historical flag will display total balance for each column (i.e., "cumulative"), or only the difference in balance (i.e., "change"). The only option right now is "cumulative". I'm suggesting that that be the default rather than the only option.

[simonmichael]

Sorry, it's not clear to me. In my usage, "historical" is equivalent to the real world balances you'd see on a bank statement. "Cumulative" is the word I picked to mean "accumulates across subperiods, but starts from zero at report start date". (Cumulative mode is very seldom used.)

…

On May 29, 2019, at 9:43 AM, the-solipsist ***@***.***> wrote: The only difference is that in place of: With --historical/-H: each column shows the actual historical ending balance for that period, accumulating the changes across periods, starting from the actual balance at the report start date. This is useful eg for a multi-period balance sheet, and when you are showing only the data after a certain start date: "With --historical/-H: each column, by default is cumulative, and shows the actual historical ending balance for that period, starting with the historically accurate balance (including undisplayed prior postings) at the report start date. This is useful, e.g., for a multi-period balance sheet, and when you are showing only the data after a certain start date. You can override the default and choose to show only the difference between the periods/columns, instead of the ending balance, by using --change flag. This will still start with the historically accurate balance at the report start date." Explanation: "Historical" means that the balance will take into account undisplayed transactions prior to the -b date. This is the meaning it has with the register command. There is no need to create a new, and different meaning for multi-column layouts. But a "default" is needed as to whether the --historical flag will display total balance for each column (i.e., "cumulative"), or only the difference in balance (i.e., "change"). The only option right now is "cumulative". I'm suggesting that that be the default rather than the only option. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#329?email_source=notifications&email_token=AAACPIE6SJP4IMZSKMZGDI3PX2XDBA5CNFSM4B5QMLQKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODWP542I#issuecomment-497016425>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAACPIEWTW34UXHFOCQ2PCTPX2XDBANCNFSM4B5QMLQA>.

[the-solipsist]

Sorry I wasn't clearer. Let me try again.

There are essentially two different variables:

1. Does the balance start from the date of the report period or reflect previous transactions? (This can be a question for any kind of report: for "register" as well as for period-based column reports.)

2. In a period-based report, should the balance value reflect the difference/change from one period to another, or the total balance?

I see these as independent variables.

The first, to my mind, can be controlled by --historical (to include prior transactions' value) or nothing (the default, which is not to include prior transactions).

The second, to my mind, can be controlled by --changes (to reflect the differences between each column), or --cumulative (to reflect the total in each column).

So, you should in theory be able to combine "include prior transactions' value" with "reflect the differences".

There is no reason why "cumulative" should have "but starts from zero at report start date" as part of its definition, nor why "historical" should have "accumulates across subperiods" as part of its definition. They are two distinct variables.

[simonmichael]

@the-solipsist: maybe. Please describe concisely the new report mode(s) you are imagining!

[the-solipsist]

The only change from the current that I envision is the ability to combine --change with --historical in period-based (column) reports.

For the example file given above, if I try:

$ h bal -f /tmp/temp -W --row-total --historical --change -b 2019-05-06

I would expect either:

   || 2019/05/06w19  2019/05/13w20  2019/05/20w21  2019/05/27w22    Total 
===++=====================================================================
 A ||         ₹3.00          ₹3.00          ₹4.00          ₹5.00   ₹15.00 
 B ||        ₹-3.00         ₹-3.00         ₹-4.00         ₹-5.00  ₹-15.00 
---++---------------------------------------------------------------------
   ||             0              0              0              0        0 

or:

   ||  Past balance 2019/05/06w19  2019/05/13w20  2019/05/20w21  2019/05/27w22    Total 
===++===================================================================================
 A ||         ₹1.00         ₹2.00          ₹3.00          ₹4.00          ₹5.00   ₹15.00 
 B ||        ₹-1.00        ₹-2.00         ₹-3.00         ₹-4.00         ₹-5.00  ₹-15.00 
---++-----------------------------------------------------------------------------------
   ||             0             0              0              0              0        0 

[simonmichael]

My github reply got delayed. Thanks, I see what you're saying. I'll respond to a few things separately:

As you can see, the "Total" is pointless since --row-total is meaningless when...

Agreed, I think that's from an out of date build. If you build master you should not see a Total column in that example (I don't). "-H disables -T" as I said earlier.

interestingly, the column headers are drastically different from the previous report for reasons I'm unable to grasp)

It's because --historical and --cumulative reports show ending balances, on a date such as "2019/06/02", while --change shows the change during a period, such as the week before 6/2 (which we display as "2019/05/27w22", week 22 of the year, beginning 2019-05-27).

Also, strangely, the balance change is from "2019/04/29" instead of "2019-05-01"

I can see that might be surprising. It's because https://hledger.org/manual.html#multicolumn-balance-report : "the report start/end dates will be adjusted if necessary so that they encompass the displayed report periods. This is so that the first and last periods will be “full” and comparable to the others.".

I would expect either: || 2019/05/06w19 2019/05/13w20 2019/05/20w21 2019/05/27w22 Total ===++===================================================================== A || ₹3.00 ₹3.00 ₹4.00 ₹5.00 ₹15.00 B || ₹-3.00 ₹-3.00 ₹-4.00 ₹-5.00 ₹-15.00 ---++--------------------------------------------------------------------- || 0 0 0 0 0

I see what you're proposing. Currently --historical/--cumulative/--change affects all periods in a multiperiod report. This is consistent across balance-style and register reports. You're showing a hybrid report: historical balance for the first period, but just the changes in the other periods. That's interesting, but I'm not convinced it's useful enough to be worth the added confusion. Do you have a real world use case in mind ? What do others think ?

or: || Past balance 2019/05/06w19 2019/05/13w20 2019/05/20w21 2019/05/27w22 Total ===++=================================================================================== A || ₹1.00 ₹2.00 ₹3.00 ₹4.00 ₹5.00 ₹15.00 B || ₹-1.00 ₹-2.00 ₹-3.00 ₹-4.00 ₹-5.00 ₹-15.00 ---++----------------------------------------------------------------------------------- || 0 0 0 0 0 0

I think this one is clearer. I'd still want to see a good use case, as it requires more complicated flag behaviour.

[the-solipsist]

I think this one is clearer. I'd still want to see a good use case, as it requires more complicated flag behaviour.

The use case, essentially, is the same as for --change by itself.

I'm wondering: Would it be possible to remove the "accumulation" code from -H and simply add a call to --cumulative whenever -H is called for a period report (to make it -H --cumulative)? If --change is added, then you can make that equivalent to appending --change. Since the last flag between --cumulative and --change will apply, that would automatically override --cumulative. That way, the default behaviour of -H can be preserved neatly.

I'm sorry, I don't know the code-base, so I'm just spitballing.

[simonmichael]

The code for this is easy, the hard part is keeping intuitive UX (writing the doc is a good test of this) and demonstrating real value from the change. I'm going to let others chime in on this.

[simonmichael]

With #131 merged, the new UI proposal above is complete, and we can close this. (Our side discussion about --historical could move elsewhere, but I've left it as is for now.)

[the-solipsist]

The text in the Track Investments page should be updated to reflect this issue being closed.