Fetching prices

Associated directory: 16-fetching-prices

Multiple commodities and reporting

Most of the time you have a single preferred commodity (in the scope of this tutorial, it is £), and you want all your reporting to be in that commodity only. Transactions and balances in other commodities should be converted to your preferred one, as you might not remember the exchange rate for different commodities from three years back -- and even if you do, you would probably not want to do the mental math every time you look at your financial reports.

Requesting the conversion is easy: if you add --cost --value=then,£ to your hledger invocation, all other commodities would be converted to their cost in £, at the time of transaction.

But how would it be converted? What would be the conversion rate? Hledger manual describes the process at great length in its sections on Value reporting, but ultimately hledger needs to know the conversion rate.

Sometimes (as we did in the section on foreign currency) the transaction itself provides the conversion rate, implicitly, via cost notation (see Cost reporting in hledger manual). In other cases, however, we would like to avoid manually specifying conversion rates. Can we fetch the rates somehow? Turns out, we can.

Pricehist and automating price fetching

Problem statement: we want to automatically add price declarations to our journal so that we can convert any commodity to our default one (£) using up-to-date conversion rates.

Those price declarations need to cover every date on which we have transactions in non-default commodities. So it looks like the process would have to be multi-step:

• First, we need to determine the dates on which those transactions took place

• Then we need to fetch the prices/conversion rates for the given commodity on those dates

The first step could be accomplished using hledger itself: we can ask for all the transactions in commodity or currency XYZ by using the query cur:XYZ, and then grab the dates of these transactions.

The second step could be performed by the wonderful tool called pricehist which can fetch prices from a variety of sources and emit them in the syntax of hledger price directives.

Tying it all together, we can build the following automation:

• User requests the prices by writing include ./import/prices/2017-USD.prices in their journal. Our build system, trying to provide this file, could extract the year (2017) and desired commodity (USD) from the filename and pass it to the next step

• Build system then uses the hledger print -f export/<year>-all.journal --output-format=csv to extract the dates of all transactions in that <commodity> and saves them in import/prices/<year>-<commodity>.dates

• List of dates and commodity is passed to pricehist, which fetches prices and they are saved in import/prices/<year>-<commodity>.prices, and hledger could now include this file and take those prices into account when producing various reports

Implementation notes

In 16-fetching-prices/export you will find price_dates.sh and prices.sh that perform the steps described above.

I found it easier to write both steps as simple shell scripts so that you can implement small tweaks for different commodities. For example, some currencies would be quoted by Yahoo as <CUR>/GBP, while others would be GBP/<CUR>. Sometimes both pairs are provided, but one of them has more data, and then you potentially might need to pass "--invert" to pricehist to make sure that prices are quoted the right way.

I've experimented with fully automatic price discovery where the list of non-default commodities is automatically computed, and all of them are priced on every day we have transactions in them, but I found out that a simpler approach where you explicitly request the price file via include is more flexible and robust - I frequently had transactions in made-up commodities (like UNITS from the previous chapter), and it was a hassle trying to exclude them from the pricing process.

Reporting

Now it is a simple matter of adding --cost --value=then,£ to all hledger invocations in export.hs and all the non-default commodities should now be correctly priced!

You can find the resulting setup in 16-fetching-prices or diffs/15-to-16.diff.

Next steps

To be continued