Monthly balance on the operating currency
93 views
Skip to first unread message
Máté Farkas
Oct 20, 2022, 3:37:37 AM10/20/22
to Beancount
How can I get my monthly balance converted to the operating currency?
Suppose that my beancount file is:
---------------------------------
option "operating_currency" "USD"
2020-01-01 open Assets:USD USD
2020-01-01 open Assets:CHF CHF
2020-01-01 open Income:Payment
2020-01-01 *
Assets:CHF 100 CHF
Income:Payment
2020-01-01 price CHF 1.0 USD
2020-02-01 price CHF 2.0 USD
2020-03-01 price CHF 3.0 USD
2020-04-01 price CHF 4.0 USD
2020-04-01 price CHF 5.0 USD
2020-05-01 *
Assets:CHF -100 CHF @@ 500 USD
Assets:USD
---------------------------------
Is there any way to query the monthly balance in USD? What I expect is:
--------------------
2020-01-01 100.0 USD
2020-02-01 200.0 USD
2020-03-01 300.0 USD
2020-04-01 400.0 USD
2020-05-01 500.0 USD
--------------------
What is exactly what I can see in balance sheet in Fava, but I need the textual data in any way.
I can query the monthly balance in the original currency. I do not know how to query the price rate for a given month.
Thank you,
Máté.
Máté Farkas
Oct 22, 2022, 1:49:17 PM10/22/22
to Beancount
With a small post-process step I can solve it with this BQL:
--------------------------------
SELECT
year, month, last(balance) as balance, last(getprice("CHF", "USD", date)) as price
WHERE
account ~ 'Assets'
GROUP BY year, month
ORDER BY year, month
--------------------------------
Then there will be columns for the actual balances in USD, CHF and the actual price.
(It requires that at least one transaction for every month to have every row filled.)
Do you have any better idea?
Thank you,
Máté.
Martin Blais
Oct 22, 2022, 5:31:13 PM10/22/22
to bean...@googlegroups.com
Yes
and you can call this from a Python script to avoid the parsing and get the right output types, see beanquery.query.run_query().
Search in the mailing-list, I believe this has come up a number of times.
Ultimately an even better way would be to support our custom types with some utilities for petl.
No language, just a generic ETL tool, but with a small library that supports aggregations on positions, inventory, etc.
--
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/ac04b8a5-0b27-4e6a-b46c-143d6d66e87dn%40googlegroups.com.
Daniele Nicolodi
Oct 25, 2022, 3:22:51 PM10/25/22
to bean...@googlegroups.com
On 22/10/2022 19:49, Máté Farkas wrote:
> With a small post-process step I can solve it with this BQL:
>
> With a small post-process step I can solve it with this BQL:
>
> SELECT
> year, month, last(balance) as balance, last(getprice("CHF", "USD",
> date)) as price
> WHERE
> account ~ 'Assets'
> GROUP BY year, month
> ORDER BY year, month
I think the convert() BQL function is what you are looking for.
> year, month, last(balance) as balance, last(getprice("CHF", "USD",
> date)) as price
> WHERE
> account ~ 'Assets'
> GROUP BY year, month
> ORDER BY year, month
Something like the following may do what you ask for:
SELECT
year,
month,
last(convert(balance, 'USD', date(year, month, 1))) AS balance
WHERE
account ~ 'Assets'
GROUP BY
year,
month
adjusting the third parameter of convert() to do what you need (which is
account ~ 'Assets'
GROUP BY
year,
month
not very clear to me from the problem statement).
Cheers,
Dan
Máté Farkas
Oct 26, 2022, 1:29:25 PM10/26/22
to Beancount
Hi Dan, thank you very much.
For me
convert(balance, 'USD', date)
is enough. Without a proper documentation it was hard to find out how can I use the built-in functions, but it was so easy.
For example I was not able to find the getprice function in
There are some convert functions documented there, but it was not clear for me.
Thank you again for helping me.
Reply all
Reply to author
Forward
0 new messages