FODS export and single-column balance with multiple columns

[Henning Thielemann]

Hi Simon,

I plan to add a FODS export additional to the CSV export in 'hledger
balance'. I wonder, why CvsUtils is in directory Read because it only
contains 'print' functions. Then I think I prefer to actually generate
multiple output columns in "single column" balance mode, one for each
currency. I assume, that cell contents like "10 EUR, 20 USD" can hardly be
processed by formulas in spreadsheet processors and it is also not a good
idea, say, to compute a sum of a column that contains both EUR and USD
numbers.

Advantages of FODS over CSV are: Character encoding is specified in XML
header, numbers can be separated from currencies, so you can immediately
use the numbers for computation but you also see the currencies in the
GUI, we can pin the header row.

[Henning Thielemann]

On Mon, 22 Jul 2024, Henning Thielemann wrote:

> Advantages of FODS over CSV are: Character encoding is specified in XML
> header, numbers can be separated from currencies, so you can immediately
> use the numbers for computation but you also see the currencies in the
> GUI, we can pin the header row.

Here is a pretty basic first attempt, that simply writes the data from
balanceReportAsCsv to a FODS file:

https://github.com/thielema/hledger/tree/balance-export-fods

[Simon Michael]

Hi Henning,

I wasn't aware of FODS
(https://kb.fileformat.app/extension/fods-file-info,
https://en.wikipedia.org/wiki/OpenDocument).

On Mon, Jul 22, 2024, at 06:16, Henning Thielemann wrote:
> I wonder, why CvsUtils is in directory Read because it only
> contains 'print' functions.

Good point. It is only used by Hledger.Cli.Commands.* except for CsvRecord which is used in RulesReader, feel free to move it.

> Then I think I prefer to actually generate
> multiple output columns in "single column" balance mode, one for each
> currency. I assume, that cell contents like "10 EUR, 20 USD" can hardly be
> processed by formulas in spreadsheet processors and it is also not a good
> idea, say, to compute a sum of a column that contains both EUR and USD
> numbers.

Have you seen the balance command's tall and bare modes (a row for each currency) and tidy mode (row per data point) ? https://hledger.org/dev/hledger.html#balance-report-layout

> Advantages of FODS over CSV are: Character encoding is specified in XML
> header, numbers can be separated from currencies, so you can immediately
> use the numbers for computation but you also see the currencies in the
> GUI, we can pin the header row.

Do you need to work with non-UTF-8 encodings ?

[Simon Michael]

On Mon, Jul 22, 2024, at 06:50, Henning Thielemann wrote:
> Here is a pretty basic first attempt, that simply writes the data from
> balanceReportAsCsv to a FODS file:
>
> https://github.com/thielema/hledger/tree/balance-export-fods

Some quick comments:

It's not required, but would be ideal for consistency, if it can be supported for multi-period and budget reports also.

Here the current system locale's encoding is embedded in the document header. Is the output encoded with that as well ? That could seem inconsistent as elsewhere hledger's policy is to read and write with utf-8 encoding. If deviating from that, I'd like to know more about the use case.

If we add this, let's mention fods at hledger.m4.md > Output format (and if it seems needed, in Balance.md).

Best

[Henning Thielemann]

On Mon, 22 Jul 2024, Simon Michael wrote:

> On Mon, Jul 22, 2024, at 06:16, Henning Thielemann wrote:
>
>> I wonder, why CvsUtils is in directory Read because it only contains
>> 'print' functions.
>
> Good point. It is only used by Hledger.Cli.Commands.* except for CsvRecord which is used in RulesReader, feel free to move it.

ok

>> Then I think I prefer to actually generate multiple output columns in
>> "single column" balance mode, one for each currency. I assume, that
>> cell contents like "10 EUR, 20 USD" can hardly be processed by formulas
>> in spreadsheet processors and it is also not a good idea, say, to
>> compute a sum of a column that contains both EUR and USD numbers.
>
> Have you seen the balance command's tall and bare modes (a row for each
> currency) and tidy mode (row per data point) ?
> https://hledger.org/dev/hledger.html#balance-report-layout

I have not found those options, but it seems the layout I like is still
missing. I want to get:

account balance
EUR USD
bla 10 20
foo 20
bar 10


FODS also allows to merge cells, such that 'balance' spans both EUR and
USD columns.

>> Advantages of FODS over CSV are: Character encoding is specified in XML
>> header, numbers can be separated from currencies, so you can
>> immediately use the numbers for computation but you also see the
>> currencies in the GUI, we can pin the header row.
>
> Do you need to work with non-UTF-8 encodings ?

My journals are in Latin-1 or Latin-9 (that is, Latin with Euro symbol).

When I run
$ LANG=de_DE hledger -f my-journal balance

then both input and output are in Latin encoding.

[Simon Michael]

PS, how widely used is .fods ?

[Simon Michael]

On Mon, Jul 22, 2024, at 07:22, Henning Thielemann wrote:
> I have not found those options, but it seems the layout I like is still
> missing. I want to get:
>
> account balance
> EUR USD
> bla 10 20
> foo 20
> bar 10

Ok, if you do that, any chance it can be implemented for the other output formats also ?

(I'm trying to reduce the inconsistency of supported features across output formats over time)

> FODS also allows to merge cells, such that 'balance' spans both EUR and
> USD columns.

Interesting, I'll see what that means in due course I expect.

>> Do you need to work with non-UTF-8 encodings ?
>
> My journals are in Latin-1 or Latin-9 (that is, Latin with Euro symbol).
>
> When I run
> $ LANG=de_DE hledger -f my-journal balance

Interesting! I seem to be wrong then.

[Simon Michael]

PS, if there's anyone else out there interested in helping fill out .fods support, let us know.

[Henning Thielemann]

On Mon, 22 Jul 2024, Simon Michael wrote:

> PS, how widely used is .fods ?

Certainly less than ODS. However, the XML data of FODS and ODS is
essentially the same. FODS contains meta data and table data in one XML
structure, whereas ODS is a PKZIP archive which contains meta data and
table data in two separate XML files. Thus, if we have FODS support, we
can also use that code for ODS support.

[Henning Thielemann]

On Mon, 22 Jul 2024, Simon Michael wrote:

> On Mon, Jul 22, 2024, at 07:22, Henning Thielemann wrote:
>> I have not found those options, but it seems the layout I like is still
>> missing. I want to get:
>>
>> account balance
>> EUR USD
>> bla 10 20
>> foo 20
>> bar 10
>
> Ok, if you do that, any chance it can be implemented for the other output formats also ?
>
> (I'm trying to reduce the inconsistency of supported features across output formats over time)

I'll try to, but it will need some time.

>>> Do you need to work with non-UTF-8 encodings ?
>>
>> My journals are in Latin-1 or Latin-9 (that is, Latin with Euro symbol).
>>
>> When I run
>> $ LANG=de_DE hledger -f my-journal balance
>
> Interesting! I seem to be wrong then.

What do you use for writing text data? I assume, all readFile and
writeFile functions use the local encoding.

[Simon Michael]

On Mon, Jul 22, 2024, at 07:35, Henning Thielemann wrote:
> On Mon, 22 Jul 2024, Simon Michael wrote:
>
>> On Mon, Jul 22, 2024, at 07:22, Henning Thielemann wrote:
>>> I have not found those options, but it seems the layout I like is still
>>> missing. I want to get:
>>>
>>> account balance
>>> EUR USD
>>> bla 10 20
>>> foo 20
>>> bar 10
>>
>> Ok, if you do that, any chance it can be implemented for the other output formats also ?
>>
>> (I'm trying to reduce the inconsistency of supported features across output formats over time)
>
> I'll try to, but it will need some time.

Thanks! Though, don't let me generate a lot of work if it's an impractical layout that users don't want. I guess you want it in the FODS export for convenient display and readability. Perhaps it won't combine well with per-period columns.

>>> My journals are in Latin-1 or Latin-9 (that is, Latin with Euro symbol).
>>>
>>> When I run
>>> $ LANG=de_DE hledger -f my-journal balance
>>
>> Interesting! I seem to be wrong then.
>
> What do you use for writing text data? I assume, all readFile and
> writeFile functions use the local encoding.

https://hledger.org/dev/hledger.html#text-encoding suggests we only read utf-8. But your report throws all my recollections in doubt, so trust tests, not me right now.

[Henning Thielemann]

On Mon, 22 Jul 2024, Simon Michael wrote:

> On Mon, Jul 22, 2024, at 07:35, Henning Thielemann wrote:
>> On Mon, 22 Jul 2024, Simon Michael wrote:
>>
>>> On Mon, Jul 22, 2024, at 07:22, Henning Thielemann wrote:
>>>> I have not found those options, but it seems the layout I like is still
>>>> missing. I want to get:
>>>>
>>>> account balance
>>>> EUR USD
>>>> bla 10 20
>>>> foo 20
>>>> bar 10
>>>
>>> Ok, if you do that, any chance it can be implemented for the other output formats also ?
>>>
>>> (I'm trying to reduce the inconsistency of supported features across output formats over time)
>>
>> I'll try to, but it will need some time.
>
> Thanks! Though, don't let me generate a lot of work if it's an impractical layout that users don't want. I guess you want it in the FODS export for convenient display and readability. Perhaps it won't combine well with per-period columns.

We could have:

account 2020 2021 2022
EUR USD EUR USD EUR USD
bla 10 20 30 40 50 60

This would also be achievable in HTML and CSV. HTML and FODS are pretty
similar in the set of features. I have already done those table exports
for CSV, HTML and XML/FODS in two other projects.