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.
On Saturday, March 22, 2025 at 1:40:06 AM UTC+1 fin wrote:
>
> 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, "Courier New",
> 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,
> "Courier =
> > New", 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, "Courier New", monospace; white-space: pre;"><span
> styl=
> > e=3D"color: rgb(163, 21, 21);">=C2=A0 =C2=A0 where date <=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, "Courier New", 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, "Courier New", 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,
> "Cour=
> > ier New", 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" 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--
> >
>
>
--
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+unsubscr...@googlegroups.com.
To view this discussion visit
https://groups.google.com/d/msgid/beancount/684f0fca-010a-4e2e-8e8c-ddef14b3dc45n%40googlegroups.com.
