Generating Expense Reports

[Alok Parlikar]

I need to periodically send a report of expenses to clients, and get them reimbursed. As I incur expenses, I add entries into an "Assets:Unbilled:ClientX" account, and then move them to "Assets:Receivables:ClientX" when I send the invoice.

What's the best way to get a list of transactions in "Assets:Unbilled:ClientX" that haven't already been moved into "Receivables"?

Is ZeroSum the correct plugin to use here?

Do people use links to tag together expenses with their corresponding invoice?

Alok

[Martin Blais]

On Fri, Aug 21, 2015 at 7:22 AM, Alok Parlikar <happ...@gmail.com> wrote:

I need to periodically send a report of expenses to clients, and get them reimbursed. As I incur expenses, I add entries into an "Assets:Unbilled:ClientX" account, and then move them to "Assets:Receivables:ClientX" when I send the invoice.

What's the best way to get a list of transactions in "Assets:Unbilled:ClientX" that haven't already been moved into "Receivables"?

So IIUC, there are multiple expenses, and then the sum total of the amounts gets moved over to an invoice.

I think one way to deal with this would be by date order, that is, you could write a script that finds the last invoice creating transaction and list only the transactions occurring after that one (you'd have to write a script because at the moment the SQL syntax does not support nested queries - otherwise that'd work).

Or, if the expenses may straddle the date at which the invoice is sent, you could use tags (using pushtag/poptag in this case, to make it easy) to mark the transactions as invoiced. I'd mark them with a tag unique for that invoice, e.g. with the invoice number, and then all the untagged transactions with one posting in that account are the leftovers. So you would like the transactions with one leg to the unbilled account which also do not have a tag (you can likely do this with the SQL).

 

Is ZeroSum the correct plugin to use here?

Not sure.

Do people use links to tag together expenses with their corresponding invoice? 

Alok

--
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 post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/8c628892-a6cb-49de-b56f-cc52e89206eb%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alok Parlikar]

Ah. pushtag/poptag seems to be what I want. Wasn't aware of that feature. Will look up the docs on it.

The tags work like code blocks? Or they are also date-specific (like the balance assertion) ?

--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/1GhV4aztt6Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhNOSz0cvAaWt3vmwNiiAY2nwyXWcJmCVDicCawTnEZNoQ%40mail.gmail.com.

[Alok Parlikar]

Answering myself, having read the docs: the pushtag/poptag work as codeblocks.

[Martin Blais]

Yes. They're just syntactic help, and that's why I didn't normalize their syntax to have a date like directives. Once parsed, they disappear, they're not included as directives

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CACq9jsBQ3VSfhM0cvzOhbAVjBnM8gAempT87c0dEPLRXA7UTWg%40mail.gmail.com.

[bw4...@googlemail.com]

Alok,

I'll share how I am setting expense handling up — I have not tested it thoroughly so far (it is the first approach, don't expect wonders):

2015-07-24 * "HostingPlanet" "15E9 Web server cost"

  knr: "ClientX"

  Assets:Bank:DE:MyAccount:CurrentAccount    64.80 EUR

  Expenses:Tax:DE:VAT19 -10.35 EUR

  Expenses:Advertising

knr is short for customer number.

15E9 is receipt ID which is file 15/E/9.pdf

Usage:

./auslagen.py -o|a|r [-d YYYY-MM-DD] <client|billno|narration pattern>

-o totals open expenses for <client>

-a totals all expenses for <client> (showing which are open)

-r returns total all open expenses in EUR for <client> and True, if only EUR transactions have been considered, else False

-d considers only transactions on or after YYYY-MM-DD

./auslagen.py -o ClientX will return open expenses for ClientX.

How does it work?

1. Find bookings with knr: "ClientX" not having verrechnet:

2. Determine for each booking net amount (exclude VAT).

3. Create sum for each currency.

4. Return sum.

./auslagen.py -r ClientX will return

54.45 True

54.54 is sum of open expenses for ClientX, True indicates whether all open currencies are EUR only (single currency)

You may intend to use this for scripting purposes. If False do ./auslagen.py -o ClientX and require user to construct total expense bill based on currency conversions

./auslagen.py -r ClientX -d 2015-09-12

consider transactions not earlier than 2015-09-12

Now you can query for each customer history and open expenses.

I plan to bill a customer based on output of ./auslagen.py -r ClientX

When bill has been prepared, mark all expenses considered off:

2015-07-24 * "HostingPlanet" "15E9 Web server cost"

  knr: "ClientX"

  rnr: "15R9"

  Assets:Bank:DE:MyAccount:CurrentAccount    64.80 EUR

  Expenses:Tax:DE:VAT19 -10.35 EUR

  Expenses:Advertising

You see, rnr is bill no. 15R9.

./auslagen.py -a 15R1 lists all those transactions containing 15R1 in either rnr or narration.

To mark all open transactions per client off, I use a bash (more precisely, awk) function, which you may call like

hake_auslagen_ab ClientX bill_id where bill_id is outgoing bill number you wrote (because of this bill this expense is no longer open).

hake_auslagen_ab(){

    awk -v RS= -v FS='\n' -v OFS='\n' -vrnr="$2" -vknr="knr: \"$1\"" '

    { for (i=1;i<NF;i++)

      if ($i ~ knr && $(i+1) !~ /verrechnet/) {

          $i = $i"\n  verrechnet: \"" rnr "\""

          break

          }

      printf("%s\n\n",$0)

    }' "$buha/buch" > "$buha/buch.neu"

    mv "$buha/buch.neu" > "$buha/buch"

}

The awk script requires any two transactions (each beginning with YYYY-MM-DD) to be delimited by an empty line.

buch should be your_file.beancount

Would be happy, if you can use this for your situation.

How could I simplify this script further? I feel a need to delete some stuff (law of orthogonality).

Benjamin

[Alok Parlikar]

Thanks for sharing this, Benjamin! Will take a look at this.

When you say "When bill has been prepared, mark all expenses considered off:", do you do this manually ?

Alok

--

You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/1GhV4aztt6Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/f359b41d-e656-40a7-96a4-269cb08ad14e%40googlegroups.com.

[bw4...@googlemail.com]

Thanks for sharing this, Benjamin! Will take a look at this.

When you say "When bill has been prepared, mark all expenses considered off:", do you do this manually ?

No. That's what hake_auslagen_ab is for.

Let me know your feedback.

Benjamin