Hledger: Allow reporting in an arbitrary commodity (Ledger's -X, or something like it)

[tphyahoo]

[Original title: implement Ledger's -V (& -X ?) feature, for converting to a desired currency and showing market value. Part of #1015. 2019 discussion starts [here](#issuecomment-493772892).]

Hledger should have some notion of exchange rates over time, as sketched out in

https://github.com/simonmichael/hledger/issues/115#issuecomment-21672724

The currency reported should be an allowed "view" that changes if currency rates over time data changes. To make it clear that this is a view and not core data, computed values should be reported in parenthesis, or otherwise distinguished somehow.

This is a feature intended for use in --require-balanced=true mode. Context: https://github.com/simonmichael/hledger/issues/115

Example of usage:
hledger print --currency-numeraire=USD --currency-conversions=currency-conversions.txt

where currency-conversions.txt file is something like

2013/01/01 USD1=CAD1.31
2013/01/02 USD1=CAD1.32

This is similar use case as print --cost, but requires --require-balanced=true.
For backwards compatibility, I think we should keep current behavior for print --cost/-B where require-balanced=false.

For concreteness, consider this transaction

    2013/01/01  Canadian in the USA Buys food with US dollars (1 USD = 1.30 CAD)
       assets:cash          -USD40
       equity:forex         USD40
       equity:expense:food  CAD52 
       equity:forex         -CAD52 

If conversions file (as above) specifies 1USD=1.31 CAD, contradicting recorded ledger rate of 1.30,
then for the food purchase above
print --numeraire=USD --numeraire-file=currency-conversions=conversions.txt
would yield something like

    2013/01/01  Canadian in the USA Buys food with US dollars (1 USD = 1.30 CAD)
       assets:cash          -USD40
       equity:forex         USD40
       equity:expense:food  CAD52 (USD39.69)
       equity:forex         -CAD52 (-USD39.69)

Q Does print --currency-numeraire=USD imply --currency-conversions specified somewhere, or can it work without the numeraire file?
A There are two reasonable behaviors
1) require the flag
2) don't require the flag, but have very boring behavior: compute the conversions from information available in file, and then reports numbers in parenthesis that agree everywhere like:

    2013/01/01  Canadian in the USA Buys food with US dollars (1 USD = 1.30 CAD)
       assets:cash          -USD40
       equity:forex         USD40
       equity:expense:food  CAD52 (USD40)
       equity:forex         -CAD52 (-USD40)

The numbers in parentheses are computed from the conversions file, unparenthesized are recorded direct in ledger file.
If it's desired, the user could correct these versions using some manual or (maybe later) automated means.

---

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

[simonmichael]

Is this a request for ledger's -X feature, mentioned at http://ledger-cli.org/3.0/doc/ledger3.html#Commodity-Reporting ? It lets you report all amounts in terms of a chosen commodity (if it knows how to make the conversion).

[tphyahoo]

Seems like it. Not implemented in hledger, right?
On Jul 29, 2013 4:36 PM, "Simon Michael" [email protected] wrote:

Is this a request for ledger's -X feature, mentioned at
http://ledger-cli.org/3.0/doc/ledger3.html#Commodity-Reporting ? It lets
you report all amounts in terms of a chosen commodity (if it knows how to
make the conversion).

—
Reply to this email directly or view it on GitHubhttps://github.com/simonmichael/hledger/issues/131#issuecomment-21759832
.

[simonmichael]

Right, not yet.

[simonmichael]

From the ledger manual:

-exchange COMMODITY
-X COMMODITY

Display values in terms of the given COMMODITY. The latest available price is used. The syntax -X COMMODITY1:COMMODITY2 displays values in COMMODITY1 in terms of COMMODITY2 using the latest available price, but will not automatically covert any other commodities to COMMODITY2. Multiple -X arguments may be used on a single command-line (as in -X COMMODITY1:COMMODITY2 -X COMMODITY3:COMMODITY2), which is particularly useful for situations where many prices are available for reporting in terms of COMMODITY2, but only a few should be displayed that way.

[thdox]

+1 :-)

[simonmichael]

Thanks for reminding me about this, thdox.

It's not something I've needed yet. In the simple case of travelling abroad I typically:

• record in the local currency specifying a unit or total price (@ or @@) in my home currency, and use -B/--cost when I want to see it converted to the home currency.
• more often, just record the amount in my home currency as converted by the bank (I may find out the exact amount later) with the local currency amount in a comment.

It would be useful to be reminded with an example or two of real-world situations where -B isn't enough and you need -X.

[thdox]

In the simple case of traveling abroad

Fully agree, me too, I do not bother to complicate the things

example or two of real-world situations

The typical use case is an _account_ in _foreign_ currency

• probably a lot of people living near a border have an account in a foreign currency
• I was hired by a US company, and they offered me stock option in USD. So any employee hired by a foreign company. Also, your local company may be bought by a foreign company, this is more and more frequent.
• trading on a foreign stock exchange, with a foreign account
• and another example, that you may find weird, but that all people living in Europe faced, we can have _two_ national currencies during a period (could last several years depending on the country)

I am experiencing with the last 3

[simonmichael]

Great high-level examples, thanks. I guess I can assume -B is not practical in those situations ? Yes, without thinking too hard I can see it would be at least painful.

Related to this feature, we need to enable historical prices, which we currently ignore.

Edit: also harmonize fixed prices syntax.

[thdox]

I can assume -B is not practical in those situations ?

Well, --exchange (=-X) will give you, like --market (-V), the today evaluation of your assets, and when you have stock option, or long acquired shares, this is what you are interested in, the today value, not the --basis(-B) value.

[simonmichael]

There has been progress on this; the balance command now supports -V.

[simonmichael]

A puzzling interaction of -V and the default commodity directive, from private email:

; when you uncomment this directive, hledger bal -V's final balance changes from 0 to 0.48 D
; (also D symbol is placed inconsistently, cf #230)
D 1000.00 D

P 2015/08/14 AAAA   41.66
P 2015/08/14 BBBB   74.62
P 2015/08/14 CCCC  32.39

2015/08/15
    a  2.4120 AAAA @@ 100
    a  0.3350 BBBB @@ 25
    a  0.7718 CCCC @@ 25
    b

$ hledger bal 
         2.4120 AAAA   
         0.3350 BBBB   
         0.7718 CCCC  a
           -150.00 D  b
--------------------
         2.4120 AAAA
         0.3350 BBBB
         0.7718 CCCC
           -150.00 D
$ hledger bal -V
                D150  a
           -150.00 D  b
--------------------
              0.48 D

[simonmichael]

Quite a chase! Here's what was happening:

D 1000.00 H

P 2015/08/14 EEEE   41.66
P 2015/08/14 FFFF   74.62
P 2015/08/14 GGGG  32.39

2015/08/15
    a  2.4120 EEEE @@ 100
    a  0.3350 FFFF @@ 25
    a  0.7718 GGGG @@ 25
    b

$ hledger bal -V
                H150  a
           -150.00 H  b
--------------------
              0.48 H

• as the D directive was parsed, it set the default commodity (H) and
  amount style (right symbol, 2 decimal places)
• as the P market price amounts were parsed, they were assigned this
  default commodity and style
• as the posting amounts were parsed, their commodityless @@ prices
  were also assigned the default commodity and style
• after parsing, all of the journal's posting amounts and market price
  amounts were canonicalised. But, only the primary posting amounts
  determined canonical styles, so there was no canonical style for H,
  so all the H amounts were reset to the null style (left symbol, 0
  decimal places)
• a balancing amount of -150.00 H was assigned to the b posting

• the standard balance report summed the postings to get a and b's
  balances, and summed those to get the total. Respectively those were:

  a:     2.4120 EEEE @@ 100.00 H + 0.3350 FFFF @@ 25.00 H + 0.7718 GGGG @@ 25.00 H
  b:     -150.00 H
  total: 2.4120 EEEE @@ 100.00 H + 0.3350 FFFF @@ 25.00 H + 0.7718 GGGG @@ 25.00 H + (-150.00 H)
  

• -V called balanceReportValue to convert each of these amounts to today's value
  in the commodity in which they were most recently priced. So they became:

  a:     2.412 * 41.66 H + 0.3350 * 74.62 H + 0.7718 * 32.39 H
     = 150.480222 H
  b:     -150 H (no market price for H, remains unchanged)
  total: 150.480222 H + (-150 H)
     = 0.480222 H
  

• As each amount was valued, it was assigned the style from the market price,
  which had been reset to the null style, so a's balance got rendered confusingly as H150.

In short: a display problem. But 0 decimal places tends to be so
confusing, I'm thinking the default style should have 2 instead.

[simonmichael]

-X isn't implemented yet. Needed ?

[dashed]

@simonmichael I'm quite interested in the -X option which is basically -V used with the _last_ known price of each commodity from the ledger file(s).

[simonmichael]

I would say: hledger's -V uses the last known market price (and its commodity) as of report end date; -X adds the ability to select the target commodity.

[dashed]

@simonmichael Ah I see. Are there still plans for -X to implemented? Or is that a wontfix?

I'm interested in -X (especially when used together with -V), mainly because representing accounts in my local fiat currency is more meaningful to me in discerning if sub-total values have changed wrt to market prices.

[simonmichael]

I think we should implement it. Just needs some developer-hours.

[jeevcat]

Just started with hledger yesterday, and I'd love the -X option. I am constantly working with a number of different currencies.

As a workaround, I've made a number of market price-only journals with my desired currency always being the 2nd value, like so:

P 2017/06/30 USD 1.30248 AUD
P 2017/06/30 GBP 1.694173 AUD
P 2017/06/30 EUR 1.486247 AUD

I then include the corresponding journal before I run hledger bal -V. This would return balances converted to the AUD in this example.

This works but is not ideal. I have to ensure I never have any market value entries with a different 2nd currency, or I won't get a single currency output.

I'd be interested in helping implement this, but I'm not well-versed in any functional programming languages, let alone Haskell.

[simonmichael]

Neat trick @jeevcat. I can imagine making different shell aliases with the different -f args. But you sound like the ideal person to push this forward, please ask for support eg on #hledger channel. First step would be to follow the contributor guide and try to build latest hledger.

For future reference, currently the -V flag is defined here and here and applied here and here. I think you can handle -X COMMODITY there as well. -V and -X are alternatives and there should probably be an options validation step that complains if both are used at the same time. In ReportOpts perhaps we should change value_ :: Bool to value_ :: Maybe Commodity or something. That would be a good place to start.

[jeevcat]

Okay @simonmichael, I'd be happy to look into it. Please understand that it will take some significant time: turns out I'm learning Haskell 😄.

[simonmichael]

Here are some related docs from ledger --help:

     register [report-query]
              --exchange commodity (-X)
                               Render all values in the given commodity, if a price
                               conversion rate can be determined.  Rates are always
                               displayed relative to the date of the posting they are
                               calculated for.  This means a register report is a his-
                               torical value report.  For current values, it may be
                               preferable to use the balance report.

              --market (-V)    Show current market values for all amounts.  This is
                               determined in a somewhat magical fashion.  It is proba-
                               bly more straightforward to use --exchange option.

OPTIONS
     --exchange COMMODITY [, COMMODITY, ...] (-X)
              Display values in terms of the given COMMODITY.  The latest available
              price is used.

     --revalued
              Report discrepancy in values for manual reports by inserting <Revalued>
              postings.  This is implied when using the --exchange (-X) or --market
              (-V) option.

[NorfairKing]

"It would be useful to be reminded with an example or two of real-world situations where -B isn't enough and you need -X."

I naturally use multiple currencies in my life, but -B forces me to use one as the default, and I can never view reports in terms of the others.

Secondly: sometimes currency conversions only specify a conversion from B to C. If A is my default currency, and I have a price for B or C from A, then hledger should be able to connect the dots.

[simonmichael]

Possible good next step: add --exchange/-X COMM option that works exactly like our -V but automatically finds a path among known market prices.

Example doc:

  -V --value              convert amounts to their market value on the report
                          end date (using the most recent applicable market
                          price, if any)
  -X --exchange COMM      convert amounts to their market value in the given commodity
                          on the report end date, if possible, using the most recent applicable market
                          prices. Like -V but you can specify the target commodity. hledger will
                          convert using known market prices (exchange rates), possibly in multiple steps
                          (in whatever sequence it thinks best).

Question: -V currently shows amounts in their original commodity when a market price is not available (same as Ledger, I think). Should -X be similarly forgiving, or should it raise an error when it can't convert all amounts to the target commodity ? Should -V ?

[simonmichael]

Could also provide --value-in as a synonym if we want.

[gavinedwards]

I'm really keen on seeing the -X functionality implemented in hledger. This is currently the only reason I still revert to ledger by default.

[ony]

Question: -V currently shows amounts in their original commodity when a market price is not available (same as Ledger, I think). Should -X be similarly forgiving, or should it raise an error when it can't convert all amounts to the target commodity ? Should -V ?

As long as that error will report both currency and report entry it is probably fine. Though it might be hard to pinpoint actual posting.

As for using cross-rate via intermediate currencies - I'm not sure it is good for representing market value since conversion is asymmetric (by default P directive is not reversible and it shouldn't be). I.e. such conversion will not answer the question "How much I'll get if I'll exchange all I got for currency that I'm interested in?" which might be useful when you decide to which country you want to move and somehow useful when you plan budget in other currency.

Note that it should be possible to "play" with "reference reports" introduced by @adept for budgeting for -V and -X, I guess.

[simonmichael]

Thanks. I think it's ok to just not convert amounts we can't convert, as -B and -V do currently.

As for using cross-rate via intermediate currencies - I'm not sure it is good for representing market value since conversion is asymmetric (by default P directive is not reversible and it shouldn't be). I.e. such conversion will not answer the question "How much I'll get if I'll exchange all I got for currency that I'm interested in?" which might be useful when you decide to which country you want to move and somehow useful when you plan budget in other currency.

True. There are "forward" declared prices, and "reverse" prices we could infer from those.

Should it prefer (possibly many) forward conversions instead of even one reverse conversion ? I think.. yes.

Should it allow reverse conversions if it's the only path ? I think yes.

If it makes forward conversions only, will the result accurately reflect source-to-destination exchange rate ? I think not really, it's still an approximation. But that's ok.

[simonmichael]

If it resorts to using a reverse price, should it warn about this ? Maybe, except we have avoided warnings so far, and it's always an approximation anyway.

Should there be a strict mode to prevent it using reverse prices ? Probably not needed.

Implementation: quick and dirty exhaustive search, or use one of the graph libraries and their shortest-path algorithm ? I guess either, the latter for bonus points.

[simonmichael]

Quite relevant: https://memo.barrucadu.co.uk/currency-conversion.html

[danielverdugo]

I often use "-X h" in ledger to see my expenses valued in hours. I like to value my time in money (and vice versa), because it's a useful psychological trick.

And sometimes I need to see my P&L in USD as well (I'm not in the United States, so it's not my default currency), so "-X" is quite handy for me.

[simonmichael]

This relates to #329 (setting the valuation date with -V/--value-at).

In Ledger -X is a superset of, and supersedes, -V.

Question: Should we think of choosing the valuation date/strategy, and choosing the currency to report in, as separate operations ?

Converting to a reporting currency does also require a date, let's call that the conversion date. So:

• valuation date: the date(s) whose market prices should be used for converting to value. Eg now, some other date, period end dates, date of posting.

• conversion date: the date whose market prices should be used for converting the final report to a desired reporting currency.

Is it (a) sensible and (b) worth the effort, to allow valuation date and conversion date to be different ? An example: you have stocks purchased in the US. Their market prices are in USD. You'd like to see their monthly values, but converted to EUR, JPY, and GBP. The commands would be bs -M --value-at=period -X EUR, bs -M --value-at=period -X JPY etc.

[MisterY]

Also, if there are no prices for the specified date(s), what is the most sensible value to use? The last existing before the date specified, I guess?

Is it (a) sensible and (b) worth the effort, to allow valuation date and conversion date to be different ?

Clearly, we might not have the prices for every day of the past, so this will be a must, should we choose an arbitrary date i.e. last year.

[simonmichael]

Yes, we define "market price on DATE" to be the latest-dated P record whose date is <= DATE. And when there's no such record, there's no known market price and no conversion is done.

[MisterY]

An example: you have stocks purchased in the US. Their market prices are in USD. You'd like to see their monthly values, but converted to EUR, JPY, and GBP.

That is an interesting question. In this example, the question is do we want to see the value of our holdings in today's EUR value or at the stock's valuation date.
Ledger has --current and --now options that limit the data that goes into the calculation, for example. But for this breakdown report, that might not be applicable.
Thinking about this, those two are the only dates that make sense to me for the currency valuations - the date of the stock valuation, and today.

[simonmichael]

Exactly. I'm reviewing historical stock values, I want to see the stock's USD value at period ends, but converted to EUR using: today's USD-EUR exchange rate ? the period end exchange rates ? For full control, would I need, eg, both --value=VALUATIONCOMMODITY,VALUATIONDATE and --in=REPORTCOMMODITY,REPORTCONVERSIONDATE ?

Or YAGNI.

[simonmichael]

I think valuation and reporting currency conversion dates can be the same, see this #329 proposal.

[simonmichael]

-X has landed in PR #1044. If you can, please help by testing with your data, reviewing docs or code, and benchmarking.

[simonmichael]

Done! docs.

Only six years later @tphyahoo !

[the-solipsist]

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

[simonmichael]

@the-solipsist sounds good.. would you be able to take a crack at it ? Did you write this ?