bean-query to get one single number

[Chary Chary]

Hi, everybody

is there a way to get a single number report with bean-query

e.g. I want all Expenses for all the period in one number.

The maximum I got so far is listing all expense accounts and their balance. To to get a total expenses I would have to dump it all in Excel and sum there.

So, I want something like 

SUM(COST(position)) WHERE account ~'Expenses'

But this does not work

Regards.

[Patrick Ruckstuhl]

Hi,

Do you have different currencies? Here is a (complex) example that I run to value and convert with a specific year.

number(only("CHF", convert(sum(value(position, #"2020-12-31")), "CHF", #"2020-12-31")))

Let's deconstruct that from the inner parts

sum(value(position, #"2020-12-31"))
this sums up the value of a position (using prices from 2020-12-31)

convert(..., "CHF", #"2020-12-31")
convert everything to CHF with FX as of 2020-12-31

number(only("CHF", ...))
Now take only the CHF out of this (as everything was converted to CHF, this is actually only CHF)) and then take the number

If you don't have different currencies or prices involved it becomes a lot simpler and you can drop those parts.

Regards,

Patrick

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/31497363-986a-4745-a202-526236c532d8n%40googlegroups.com.

[Chary Chary]

Patrick,

thanks for the answer, but I am afraid I do not have enough knowledge to understand your answer.

What you shave show is not a bean-query language, this looks time elements of python code

Can you in this case give me some broader and complete source of code and I will try to get my head around

Regards.

[Patrick Ruckstuhl]

Hi,

this is bean-query, I just limited it to the select part, for your case it would be something like

number(only("CHF", convert(sum(value(position, #"2020-12-31")), "CHF", #"2020-12-31"))) WHERE account ~'Expenses'

Regards,

Patrick

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/3ca0d4b1-d0ad-4d8c-be56-01a449ec4c90n%40googlegroups.com.

[Chary Chary]

I actually found the solution for my questions

select SUM(COST(position)) WHERE account ~'Expenses'   works just fine

[Chary Chary]

Patrick,

I am still puzzled with your example.

What is this only function, for instance, where is it documented?

[Martin Blais]

The query code is not super well documented (nor well tested). It was intended originally as a proof of concept and grew to become the main interface.

I'm going to rewrite it completely in v3, with documentation.

For now you can use the source code:

https://github.com/beancount/beancount/blob/master/beancount/query/query_env.py

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/5545ee62-b2fd-4f06-be60-2cd4317fc808n%40googlegroups.com.

[Chary Chary]

Martin,

thanks. It helps!

Regarding documentation,  does it make sense to insert in it links to a relevant parts of the source code (like you pointed me now to).  So, whilst it will probably mot meet google documentation quality requirements, it will definitely help users. 

Unfortunately google docs, whilst being very good for real time collaboration, do not have "offline draft version" feature (like wiki), which makes it difficult to contribute and/or submit pull request. 

[Chary Chary]

In general, beancount documentation is very thorough, this is one of great advantages of the tool, along with functionality

[Martin Blais]

Yes, we should insert a link.

You're always welcome to propose a change via the Suggest feature, though I had to lock it down because I was getting too many accidental edits.

Just request suggest ability by clicking on the top right button if you want.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/69a20d6d-e762-45e3-95db-94c46c155895n%40googlegroups.com.