Find invoices yet to be payed

[Maurice de Laat]

Hello all!

First message, let me start by thanking for such a wonderful piece of software!

Currently seeking my way through beancount and fava and wondering what would be a good way to find invoices that have yet to be payed.

I could tag an incoming or outgoing invoice with p.e. "^tobepayed", and remove that tag when the invoice is being payed, but that would mean that I would have to change a previous transaction's tag which seems as not the most practical thing to do.

Currently I do have an unique tag for each invoice which is being used when the invoice is received as well as when the invoice is being payed. It would be great if I could use these tags to find the invoices yet to be payed.

Any thoughts?

Thank you!

Maurice

[Gary Peck]

I'm not sure if this is expressible in BQL (I'm not at my computer right now to try this out), but conceptually you could write a query that sums up the amount of each posting from your "Accounts Receivable" account, grouped by tag. Then filters out tags that don't match your invoice tag pattern, and then filters out groups that have a zero sum.

If this isn't expressible in BQL, you could use beancount's Python API to do this in Python with probably only a few lines of code.

Gary

[Paul Walker]

That's a fun query. This may you started (can use in fava's query). Include or remove "desc" to sort the non-zero (un-paid) invoices into view.

    select links, sum(position) as balance
    where account ~ "Liabilities:Invoices"
    group by links
    order by balance desc

You might also use transaction flags. This still requires editing old transactions, but "!" is intended for use with incomplete transactions. Other characters can be used as well, say "%". Search these out with fava filter  flag:"%"

    2024-05-01 % "Invoice"

      Liabilities:Invoices

      Expenses:Expense      123 USD

Paul

[Timothy Jesionowski]

Generally accepted practice as I understand it is to have an Assets:Receivables account and a Liabilities:Payables account. Essentially this let's you date the logical transaction (when they agree to pay you / when you send the invoice) to one date and put the money in a placeholder account, then date the physical transaction to whatever day the money lands in your actual bank account.

I don't know how you organize your invoices but you could put document entries with links to the invoices themselves and then use ^ABC123 as a link on the document entry and both transaction entries.

That's how I'd do it at least. Lets your receivables show up on a balance statement, and also gives you a way to track each invoice separately.

--
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/f973f028-a4fe-47e7-95d8-7e37acee3f6an%40googlegroups.com.

[Timothy Jesionowski]

Meant to add: ABC123 is just whatever invoice number you already have on file. Could use ^Invoice-123 or such. 

[google...@muisnetwerken.nl]

Hello Timothy,

Thank you for your reply

> Generally accepted practice as I understand it is to have an
> Assets:Receivables account and a Liabilities:Payables account.
> Essentially this let's you date the logical transaction (when they
> agree to pay you / when you send the invoice) to one date and put the
> money in a placeholder account, then date the physical transaction to
> whatever day the money lands in your actual bank account.
>

That's how I administer the invoices at this time.

>
> I don't know how you organize your invoices but you could put document
> entries with links to the invoices themselves and then use ^ABC123 as
> a link on the document entry and both transaction entries.
>

I give every invoice  a unique link as you describe.

>
> That's how I'd do it at least. Lets your receivables show up on a
> balance statement, and also gives you a way to track each invoice
> separately.
>

This indeed lets my receivables (and payables) show up on the balance
sheet, but does not give me an easy way to show invoices that still
needs to be payed. On the balance sheet, I can click (in fava) on the
receivables and on the payables account, but it will show me all
transactions on those accounts, including transactions that belong to
invoices that are payed. It clutters the list when looking for invoices
that need to be payed.

[google...@muisnetwerken.nl]

Paul & Gary, thank you

Will give these query's a try

[Daniele Nicolodi]

On 31/05/24 15:03, Maurice de Laat wrote:
> Currently I do have an unique tag for each invoice which is being used
> when the invoice is received as well as when the invoice is being payed.
> It would be great if I could use these tags to find the invoices yet to
> be payed.

If you record the invoicing and payments in transactions that look like

2024-04-30 * "Invoice Aaa"
invoice: 123
Assets:Receivable 2.00 EUR
Income:Work

2024-05-31 * "Invoice Bbb"
invoice: 124
Assets:Receivable 3.00 EUR
Income:Work

2024-05-30 * "Payment Aaa"
invoice: 123
Assets:Bank 2.00 EUR
Assets:Receivable

With a fairly recent beanquery, you can use a query like this one:

SELECT
entry.meta['invoice'] as invoice,
sum(position) as outstanding
FROM
#postings
WHERE
root(account, 2) = 'Assets:Receivable'
GROUP BY
entry.meta['invoice']
HAVING
not empty(sum(position))

With an older beanquery it would look like:

SELECT
entry_meta('invoice') as invoice,
sum(position) as outstanding
WHERE
account ~ '^Assets:Receivable'
GROUP BY
entry_meta('invoice')
HAVING
NOT empty(sum(position))

The HAVING clause and the empty() function are not in the bean-query
distributed with beancount. With such an old version, as suggested by
Paul, you can order by the outstanding balance and filter "by eye" the
rows with an empty field:

SELECT
entry_meta('invoice') as invoice,
sum(position) as outstanding
WHERE
account ~ '^Assets:Receivable'
GROUP BY
entry_meta('invoice')
ORDER BY 2 DESC

This approach allows to easily account for partial payments.

Cheers,
Dan

[Marcus Read]

Hi Maurice

You might find the beanahead extension of interest. It facilitates the administration of expected transactions (regular and ad hoc).

Cheers

Marcus