Customizable Reporting - Feature Request

[crparis]

I would like to see highly customizable multi-column reports that can be easily run when called. Currently Hledger gives an excellent "Trended" financial report which is the main reason I use Hledger for my personal finances, however I am looking for additional ways to look at financial data. I am a corporate accountant and during my financial review I look at multiple reports to see numbers and trends in different perspectives. In my personal finances I am looking to do the same.

I am looking for a way to create "CurrentPriorBudget" type reports that can be customized in some sort of config file or maybe .rule file such as "import" calls on. Below I have a custom Income Statement mockup that ideally would be created and could be run in Hledger as "cpb" or "iscpb" in place of the default "is". My ideal customizable report would be: the ability to set columns in whatever arrangement you want, include visual separators (as seen in the mockup), and basic calculation columns (for example below I have "Var%" or "Var$"/"Bud").

Please let me know if anything is amiss in this feature request. Though this is a potentially inconsequential issue submission, it is my first foray into opensource development.

Income Statement - 02.2019 - Current Prior Budget

                         ||        Feb  |        Jan    Var%     Var$   |     FebBud     Var%      Var$  |     FebYTD     BudYTD  VarYTD%     VarYTD$  |
 ========================++=============+===============================+================================+=============================================+
 Revenues                ||             |                               |                                |                                             |
 ------------------------++-------------+-------------------------------+--------------------------------+---------------------------------------------+
 Income:Work             ||  $3,000.00  |  $3,000.00      0          0  |  $3,500.00   (14.3%) ($500.00) |  $6,000.00  $7,000.00   (14.3%) ($1,000.00) |
 Income:Interest         ||     $20.00  |     $20.00      0          0  |     $40.00   (50.0%)  ($20.00) |     $40.00     $80.00  (200.0%)    ($40.00) |
 Income:Misc             ||          0  |          0      0          0  |     $50.00  (100.0%)  ($50.00) |          0     $50.00  (100.0%)    ($50.00) |
 ------------------------++-------------+-------------------------------+--------------------------------+---------------------------------------------+
                         ||  $3,020.00  |  $3,020.00      0          0  |  $3,590.00   (15.9%) ($570.00) |  $6,040.00  $7,130.00   (15.3%) ($1,090.00) |
 ========================++=============+===============================+================================+=============================================+
 Expenses                ||             |                               |                                |                                             |
 ------------------------++-------------+-------------------------------+--------------------------------+---------------------------------------------+
 Expenses:Food           ||    $800.00  |    $700.00  (14.3%) ($100.00) |    $900.00    11.1%   $100.00  |  $1,500.00  $1,900.00    21.1%     $400.00  |
 Expenses:Housing        ||    $500.00  |    $600.00   16.7%   $100.00  |    $600.00    16.7%   $100.00  |  $1,100.00  $1,200.00     8.3%     $100.00  |
 Expenses:Gifts          ||     $50.00  |          0      0    ($50.00) |     $75.00    33.3%    $25.00  |     $50.00    $100.00    50.0%      $50.00  |
 Expenses:Supplies       ||    $100.00  |    $125.00   20.0%    $25.00  |    $125.00    20.0%    $25.00  |    $225.00    $250.00    10.0%      $25.00  |
 ------------------------++-------------+-------------------------------+--------------------------------+---------------------------------------------+
                         ||  $1,450.00  |  $1,425.00   (1.8%)  ($25.00) |  $1,700.00    14.7%   $250.00  |  $2,875.00  $3,450.00    16.7%     $575.00  |
 ========================++=============+===============================+================================+=============================================+
 Net:                    ||  $1,570.00  |  $1,595.00   (1.6%)  ($25.00) |  $1,890.00   (16.9%) ($320.00) |  $3,165.00  $3,680.00   (14.0%)   ($515.00) |

[simonmichael]

Welcome @crparis. Thanks for writing up this idea, and for the concrete mockup making it clear. Being able to generate such flexible reports would be nifty indeed.

A question is how users would configure them. Eg I can imagine two CSV-like lines. The first line would define the column headings, and the second would define the row values. Row cells would hold spreadsheet-like expressions referring to other cells, with calls to hledger calculation and date helpers. Heading cells would need to be dynamic too, eg to be current-date-aware.

Or, since that's a costly thing to build and would never cover all wishes, would it be better to focus on making it easier to build custom reports using hledger's native programming language (Haskell) ?

And in either case, how specialised is this need and how many people would actually use it ?

[crparis]

Your idea of a csv type configuration is what I had in mind as this would declare each column's headings and declare custom heading's calcs for their row values to give quick flexibility in report creation and would be best case, however usually someone would create 1 - 3 custom reports and once they are created will use these exclusively for monthly, quarterly, yearly financial review. Making these easier to create using Haskell with some docs would be a sound option depending on the cost comparison for each option and the target audience. Another option at least to test feedback could be no custom reports but the addition of a few standard complex reports, the mock-up I sent is probably the most commonly used standard monthly report that I am aware of or at least slight variations/simplifications of it.

To me personally and those in my situation the caveat to using Haskell to create these reports is that I am terrible at programming which has taught me on more than one occasion to stick to accounting. Though, with some documentation, simplification, and determination I think I could stumble through it.

Now, to address your question on how many people would actually use this functionality. I do not know, I would use this regularly but I am an accountant who prefers the terminal. I landed on hledger a few years ago after searching for open source accounting software with good reporting, and hledger was the only one I could find that had some of the flexible quality reporting I was looking for. My guess is the parties interested in more in depth reporting beyond accountants would be small business owners who typically need high visibility on budget information and performance for daily/monthly operations. I have a few small businesses that I help out with their books which are currently on Gnucash who are looking for better reporting and would highly consider switching, and I know would switch with the addition of simple 12 month csv budget import/querying to go along with it (Icing on the cake for them would be monthly reporting in web).

So to my point, if there are not a lot of accountants or small business owners using hledger, or they will not be a targeted user base this may not be something that is in high demand. For those who need high budget visibility not only for the month but the year and need variances for a reasonableness test to check for errors or explain or plan for events the cpb is an extremely useful report.

[simonmichael]

Thanks for the info! I have not seen a Current Prior Budget report before. I guess it shows

1. for the current month: expenses (& income) to date
2. for the previous month: expenses and how they differ from 1
3. for the current month: the budget and how it differs from 1
4. for the year to date: the cumulative expenses, budget, and how the latter differs from the former

I think you can get most of this information, as in the examples at https://hledger.org/hledger.html#budget-report , but not in a single compact report. I imagine this format is one of many variations, or is it quite standard ?

[crparis]

No problem, I am glad I was able contribute some new information!

You are correct in your interpretation, this information is mostly available and can be built in a spreadsheet with some manipulation and simple formula work. The most difficult part I believe is that the budget is not retrievable in a csv column format that can be easily manipulated.

In my experience it is by far the most used standard format for general use, I have run into some that are simplified that just have current, just YTD, or perhaps just do not have variance percentages. These are usually omitted to save print space. There are some specialty reports that include an additional 2 to 3 prior months that are a hybrid with the trended report (CPPB,CPPPB), or add projected variances for the month (CPBProj).

A couple examples of other reports that come to mind that I have used on occasion that are more specialty reports are the ActualBudgetProjection (ABProj) report which replaces prior with projected numbers, Actual-Budget Trended, where you get full year trended report with Actual numbers for current and past months and budget numbers for future months with YTD calcs.

[simonmichael]

the budget is not retrievable in a csv column format that can be easily manipulated.

That's true ("Sorry, CSV output is not yet implemented for this kind of report"), and this would be the easiest thing to build next, allowing you to at least extract the data into a spreadsheet. Maybe we can attract a developer to make line 320 more like line 329.

[Seriousness]

As a start it would be awesome to have some "single value" function.

eg. I could either get relative changes over a timespan or absolute on a specific date.

for example, 0440 is my alias for "Machinery":

hledger var 0440 -p 2020/10

Now I can see how my machinery assets have changed in Sept 2020, maybe I bought some new printer for 5k€ so it should return the sum of 5k€ minus depreciation in this month, so eg. simply return: 3345,42€

hledger var 0440 -p 2019/12/31 not:desc:Jahresabschluss:

where Jahresabschluss are my closing/opening statements should show me the absolute value (hope this is the right term in English, not a native speaker especially not in bookkeepers stuff).

Using this I - without haskell knowledge - could easily create my custom reports with piping or retrieving this from any other language with bash access. ATM I do a lot of sed/gawk/tail-stuff from reports to gain specific numbers. But its definitely not failsafe, so whenever the layout is gonna change a lot of work is upcomming.

I could also write my own routines on value added taxes when I dont simply do assertions that erase any "errors". I have already tried to work myself through haskell-lib but I simply suck at coding. Having this function should give plenty of options for future "custom reports" shouldnt it?

[simonmichael]

Hello, forgotten issue #1155..

@Seriousness, isn't balance that command ? Despite the name, it is a general-purpose report that sums arbitrary sets of postings, showing balance changes over a period. With -H/--historical it shows end balances on a date. More tips available on the #hledger chat.

[Seriousness]

Yes, I do know that but I am actually looking into preparing reports in latex for printing. So in theory the numbers are all there, but getting single numbers would make preparing individual reports eg. in latex a lot easier instead of cropping tabular output.

[alerque]

@Seriousness The balance command is one that does already support CSV output. Have you tried using that instead of parsing the tables yourself? There are some good CLI tools for querying CSV data (for example CSVKit) and getting just the results you want out of them. That's how I typically fetch data from hledger for placement in reports.

Obviously it would be nice if all commands that output tabular data had CSV output options, but in the example you are working on it seems like it's already there.

[simonmichael]

bal --budget now supports CSV output in master, any testing is welcome.

[simonmichael]

I think the next step for custom reports, is either:

• a detailed mockup of a configuration (config file, command line options, other...) that could plausibly produce a report like @crparis's above
• or, a mockup or prototype of a haskell script producing such a report

Both are interesting, but a haskell script is cheaper to implement, so is likely what we'll achieve first. Once we have that, it might be possible to polish it into an EDSL and process that could be pretty approachable even for non-programmers.

[the-solipsist]

I'd think of balancesheet, cashflow, and incomestatement as 3 default templates. But one should be able to create a report hledger report --template=balancesheet-new, and it should generate the relevant report.

Some very roughly sketched out ideas, but which can serve as a springboard for others to reject, criticise, and improve upon.

The template balancesheet-new.report could look like:

table: Balance sheet for {{period}}
| Assets         | Running balance        |
| -------------- | ---------------------- | 
| {{asset.acct}} | {{asset.acct.balance}} | 
| Total          | {{asset.acct.total}}   |

| Liabilities        | Running balance            |
| ------------------ | -------------------------- | 
| {{liability.acct}} | {{liability.acct.balance}} | 
| Total              | {{liability.acct.total}}   |

While balancesheet-new.rules could look like:

---
asset: Assets, not:Assets:Wallet:Points, amt:'>=0'
liability: Liabilities, not:Liabilities:Temp
period:
  - begin: 2020-04-01
  - end: 2021-03-31
...

A better way might be to look at how pandoc handles its templating and to consider borrowing ideas from that.

[alerque]

bal --budget now supports CSV output in master, any testing is welcome.

Wow, thank you! A quick glance at some of my makefiles suggests I can now replace this snippet:

define budget2csv =
    sed -E \
        -e 's/ *$$//' \
        -e '1,2d;4d;3{s/.*/\L&/g;s/^ *\|\|/period/;s/ +/,/g}' \
        -e '5,$${s/ \|\| */,"/;s/$$/"/;s/\] +/]","/g}' \
        -e '5,$${/,/!d;s# \[#\\\n[#g}' \
        -e '5,$${s/\[/*[/g;s/\]/]*/g}'
endef

...with -O csv or something close to it. :+1: