BQL: monthly balances of account(s)

[nug get]

Hi all
What would be the BQL query to get the balances at the end of each month for (a) given acclunt(s), converted to the base currency?

thanks for any help.
Thank you very much

[Chary Ev2geny]

Hi,

this is a query I use to get a Net Worth at a certain date, grouped by account 

    query = f"""

    SELECT account, convert(SUM(position),'{currency}',{date_iso}) as amount

    where date <= {date_iso} AND account ~ 'Assets|Liabilities'

    """

Net worth is being defined as a sum of balances of all Assets and Liabilities

Note, that following standard accounting principles, for Net Worth the conversion to the target currency is done using the exchange rate (price), dated with the date the report is generated for.

[fin]

hello,

thank you for this as it gave me a reason to finally get a
networth bash alias figured out that i could use in my day-to-day
beancounting. :) i really needed a replacement for the bean-report
in v2 before i could switch to v3 only. i also need one for the
bal bean-report but that isn't as critical.

for those who don't know what bash is, it is a shell scripting
language often available on unix/linux type systems and it is
useful even if it isn't particularly robust in some ways, but it
does what i need it to do (and there are other similar shells
called, sh, csh, zsh, dash, etc.).

and while bash aliases are recommended now to be replaced
by functions i've not gotten into that yet...

i did adapt what you wrote below and came up with the
following (and put in my .bashrc file - it's all one line):

alias netw="bean-query ledger.bc \"SELECT convert(SUM(position),'USD') as amount where account ~ 'Assets|Liabilities'\" | tail -1 | cut -d ' ' -f 1"

note that tail and cut are also unix/linux type commands that are
supplying some help so i didn't have to write a more complicated
script.

it's not as pretty as a result as the bean-report version but
that is ok with me - i can do that later, i just needed the number
for now.


fin



Chary Ev2geny wrote:
> ------=_Part_38978_728554756.1742331673953
> Content-Type: multipart/alternative;
> boundary="----=_Part_38979_1109216610.1742331673953"
>
> ------=_Part_38979_1109216610.1742331673953
> Content-Type: text/plain; charset="UTF-8"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
>
> this is a query I use to get a Net Worth at a certain date, grouped by=20
> account=20
>
> query =3D f"""
> SELECT account, convert(SUM(position),'{currency}',{date_iso}) as amoun=
> t
> where date <=3D {date_iso} AND account ~ 'Assets|Liabilities'
> """
>
> Net worth is being defined as a sum of balances of all Assets and=20
> Liabilities
>
> Note, that following standard accounting principles, for Net Worth the=20
> conversion to the target currency is done using the exchange rate (price),=
>=20

> dated with the date the report is generated for.
>
>

> On Monday, March 10, 2025 at 6:25:20=E2=80=AFPM UTC+1 nugget....@gmail.com =
> wrote:
>
>> Hi all
>> What would be the BQL query to get the balances at the end of each month=
>=20

>> for (a) given acclunt(s), converted to the base currency?
>>
>> thanks for any help.
>> Thank you very much
>>
>>
>>
>

> --=20
> 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 e=
> mail to beancount+...@googlegroups.com.
> To view this discussion visit https://groups.google.com/d/msgid/beancount/6=
> 72d1b4b-e690-4ee4-a9fa-975b741c6683n%40googlegroups.com.
>
> ------=_Part_38979_1109216610.1742331673953
> Content-Type: text/html; charset="UTF-8"
> Content-Transfer-Encoding: quoted-printable
>
> Hi,<div><br /></div><div>this is a query I use to get a Net Worth at a cert=
> ain date, grouped by account=C2=A0</div><div><br /></div><div><div style=3D=
> "color: rgb(0, 0, 0); font-family: Consolas, &quot;Courier New&quot;, monos=
> pace; white-space: pre;">=C2=A0 =C2=A0 query =3D <span style=3D"color: rgb(=
> 0, 0, 255);">f</span><span style=3D"color: rgb(163, 21, 21);">"""</span></d=
> iv><div style=3D"color: rgb(0, 0, 0); font-family: Consolas, &quot;Courier =
> New&quot;, monospace; white-space: pre;"><span style=3D"color: rgb(163, 21,=
> 21);">=C2=A0 =C2=A0 SELECT account, convert(SUM(position),'</span>{<span s=
> tyle=3D"color: rgb(128, 128, 128);">currency</span>}<span style=3D"color: r=
> gb(163, 21, 21);">',</span>{date_iso}<span style=3D"color: rgb(163, 21, 21)=
> ;">) as amount</span></div><div style=3D"color: rgb(0, 0, 0); font-family: =
> Consolas, &quot;Courier New&quot;, monospace; white-space: pre;"><span styl=
> e=3D"color: rgb(163, 21, 21);">=C2=A0 =C2=A0 where date &lt;=3D </span>{dat=
> e_iso}<span style=3D"color: rgb(163, 21, 21);"> AND account ~ 'Assets|Liabi=
> lities'</span></div><div style=3D"color: rgb(0, 0, 0); font-family: Consola=
> s, &quot;Courier New&quot;, monospace; white-space: pre;"><span style=3D"co=
> lor: rgb(163, 21, 21);">=C2=A0 =C2=A0 """</span></div><div style=3D"color: =
> rgb(0, 0, 0); font-family: Consolas, &quot;Courier New&quot;, monospace; wh=
> ite-space: pre;"><span style=3D"color: rgb(163, 21, 21);"><br /></span></di=
> v>Net worth is being defined as a sum of balances of all Assets and Liabili=
> ties</div><div><br /></div><div>Note, that following standard accounting pr=
> inciples, for Net Worth the conversion to the target currency is done using=
> the exchange rate (price), dated with the date the report is generated for=
> .<br /><div style=3D"color: rgb(0, 0, 0); font-family: Consolas, &quot;Cour=
> ier New&quot;, monospace; white-space: pre;"><span style=3D"color: rgb(163,=
> 21, 21);"><br /></span></div><br /></div><div class=3D"gmail_quote"><div d=
> ir=3D"auto" class=3D"gmail_attr">On Monday, March 10, 2025 at 6:25:20=E2=80=
>=AFPM UTC+1 nugget....@gmail.com wrote:<br/></div><blockquote class=3D"gmai=
> l_quote" style=3D"margin: 0 0 0 0.8ex; border-left: 1px solid rgb(204, 204,=
> 204); padding-left: 1ex;">Hi all<br>What would be the BQL query to get the=
> balances at the end of each month for (a) given acclunt(s), converted to t=
> he base currency?<br><br>thanks for any help.<br>Thank you very much<br><br=
>> <br></blockquote></div>
>
><p></p>
>
> -- <br />
> You received this message because you are subscribed to the Google Groups &=
> quot;Beancount&quot; group.<br />
> To unsubscribe from this group and stop receiving emails from it, send an e=
> mail to <a href=3D"mailto:beancount+...@googlegroups.com">beancount=
> +unsub...@googlegroups.com</a>.<br />
> To view this discussion visit <a href=3D"https://groups.google.com/d/msgid/=
> beancount/672d1b4b-e690-4ee4-a9fa-975b741c6683n%40googlegroups.com?utm_medi=
> um=3Demail&utm_source=3Dfooter">https://groups.google.com/d/msgid/beancount=
> /672d1b4b-e690-4ee4-a9fa-975b741c6683n%40googlegroups.com</a>.<br />
>
> ------=_Part_38979_1109216610.1742331673953--
>
> ------=_Part_38978_728554756.1742331673953--
>

[Vasily M]

Hi,

To add a bit of information on more general case, I recently finally found a way to generate weekly net worth / account balance reports I needed.
It relies on a more recent version of https://github.com/beancount/beanquery that contains the ```date_bin``` function:

    SELECT
    date_bin(interval('7 days'), date, 1970-01-01) + INTERVAL('6 days') as datebin,
    CONVERT(LAST(balance), '{{ledger.ccy}}', date_bin(interval('7 days'), LAST(date), 1970-01-01) + INTERVAL('6 days')) AS value
    WHERE account_sortkey(account) ~ '^[01]'
    GROUP BY datebin

https://github.com/Evernight/lazy-beancount/blob/111cf88cd213d409e5e2a933b3cc477848203726/example_data/dashboards.yaml#L1967

There were also multiple other ```date_``` functions in recent ```beanquery``` that I think should be useful for even more similar use-cases.

[Floris Kruisselbrink]

Op zaterdag 22 maart 2025 om 13:22:52 UTC+1 schreef Vasily M:

It relies on a more recent version of https://github.com/beancount/beanquery that contains the ```date_bin``` function:

I can only find one released version of beanquery, version 0.1.0 from november 2024. Looked on pypi and in the github repo, but it appears there is no release, only the master-branch of the sourcecode that contains these date_xxx functions? 

Tnx,

Floris.

[Chary Ev2geny]

date_bin is available in the current github directory  

https://github.com/beancount/beanquery

you shall be able to install it like this:

git clone https://github.com/beancount/beanquery.git
cd beanquery
python -m pip install .

[Daniele Nicolodi]

On 24/03/25 21:13, Chary Ev2geny wrote:
> date_bin is available in the current github directory
>
> https://github.com/beancount/beanquery
>
> you shall be able to install it like this:
>
> git clone https://github.com/beancount/beanquery.git
> cd beanquery
> python -m pip install .

Or simply:

python -m pip install git+https://github.com/beancount/beanquery.git

Cheers,
Dan

[Daniele Nicolodi]

On 24/03/25 11:51, Floris Kruisselbrink wrote:
>
>
> Op zaterdag 22 maart 2025 om 13:22:52 UTC+1 schreef Vasily M:
>
> It relies on a more recent version of https://github.com/beancount/

> beanquery <https://github.com/beancount/beanquery> that contains the

> ```date_bin``` function:
>
>
> I can only find one released version of beanquery, version 0.1.0 from
> november 2024. Looked on pypi and in the github repo, but it appears
> there is no release, only the master-branch of the sourcecode that
> contains these date_xxx functions?

I've just released beanquery 0.2.0

Cheers,
Dan