Question regarding handling of donor information in beancount for a small non-profit

[Rick F]

Hi.. I work at a small non-profit organization and am looking at whether any of the PTA apps out there (ledger, hledger or beancount primarily) can be used to handle our accounting needs or not. One big area that I'm having difficulty with is tracking donations that we receive with (hopefully) full contact info so we can pull reports at year-end of person X's donations are ... blah blah blah (report info).

I found someone's prior work associated with Ledger (using lots of tags) for much of what I'm after but still nothing that allows me to track donor information aside from a simple name. 

After reading through the docs for beancount, I'm hoping that there's a way to use meta-data to help with this perhaps..? 

I was hoping someone here that knows beancount way better than I do could give me the 50,000ft answer of whether this will be like pushing a boulder up hill or not.. Thanks much!

[Martin Blais]

Just use a metadata field with a unique id per client

and maintain a spreadsheet with all the details,

just join the two with a script.

You can extract that metadata using queries or a script.

--
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/30808cf2-ce0f-42b3-88a7-b822c51d0cf4n%40googlegroups.com.

[Rick F]

Thanks Martin!  I'll keep that suggestion in mind but before I saw your reply I started playing with the custom directive and was able to get an example that passes muster with bean-check.  The example is below:

2013-01-01 open Assets:US:WellsFargo:Checking4431
  institution: "Wells Fargo Bank NA"
  address: "123 America Street, LargeTown, USA"
  phone: "+1.012.345.6789"

2013-01-01 open Income:US:Donations

;; fields are as follows :
;;      donor-id : a string defining a unique identifier that can be used to identify this donor
;;      donor-name : a string containing the name of the donor
;;      donor-address : a string of the address for the donor
;;      donor-city : a string containing the city the donor lives in
;;      donor -state : donor's state
;;      donor-zipcode : donor's zip code
;;      donor-phone : donors phone number
;;      donor-email : email address of donor
2024-09-28 custom "Donor" "john-smith" "John Smith" "123 First Street" "Palo Alto" "CA" "93501" "123-456-7890" "f...@bar.com"

2024-09-28 txn "john-smith"
  Assets:US:WellsFargo:Checking4431 -100.00 USD
  Income:US:Donations

The big question for me is can bean-query search in the custom directive fields and so forth -- such that I could potentially run a report against my file asking for any donations by a specific donor (e.g. "john-smith")?  I did a quick internet search for bean-query and custom but didn't really find anything.  

Thanks much!!

[Rick Flower]

Thanks Martin!  I am just now seeing your reply.  I'll keep that in mind, but a little while ago I started working on an experiment using the "custom" directive and came up with this and it appears to pass muster with the syntax checker (bean-check) :

2013-01-01 open Assets:US:WellsFargo:Checking4431
  institution: "Wells Fargo Bank NA"
  address: "123 America Street, LargeTown, USA"
  phone: "+1.012.345.6789"

2013-01-01 open Income:US:Donations

;; fields are as follows :

;;      donor-id : a string defining a unique identifier that can be used to identify this donor in transactions.

;;      donor-name : a string containing the name of the donor
;;      donor-address : a string of the address for the donor
;;      donor-city : a string containing the city the donor lives in
;;      donor -state : donor's state
;;      donor-zipcode : donor's zip code
;;      donor-phone : donors phone number
;;      donor-email : email address of donor
2024-09-28 custom "Donor" "john-smith" "John Smith" "123 First Street" "Palo Alto" "CA" "93501" "123-456-7890" "f...@bar.com"

2024-09-28 txn "john-smith"
  Assets:US:WellsFargo:Checking4431 -100.00 USD
  Income:US:Donations

Do you know if bean-query would allow me to query all transactions with "john-smith" as a donor?  If that's possible then I think I've got a viable path forward here that is completely within BC.

I did a quick search of the internet for "custom" and bean-query but didn't really find anything.  Thank you!

--Rick

[Rick Flower]

Thanks Martin!  I'll keep that suggestion in mind but before I saw your reply I started playing with the custom directive and was able to get an example that passes muster with bean-check.  The example is below:

2013-01-01 open Assets:US:WellsFargo:Checking4431
  institution: "Wells Fargo Bank NA"
  address: "123 America Street, LargeTown, USA"
  phone: "+1.012.345.6789"

2013-01-01 open Income:US:Donations

;; fields are as follows :
;;      donor-id : a string defining a unique identifier that can be used to identify this donor

;;      donor-name : a string containing the name of the donor
;;      donor-address : a string of the address for the donor
;;      donor-city : a string containing the city the donor lives in
;;      donor -state : donor's state
;;      donor-zipcode : donor's zip code
;;      donor-phone : donors phone number
;;      donor-email : email address of donor
2024-09-28 custom "Donor" "john-smith" "John Smith" "123 First Street" "Palo Alto" "CA" "93501" "123-456-7890" "f...@bar.com"

2024-09-28 txn "john-smith"
  Assets:US:WellsFargo:Checking4431 -100.00 USD
  Income:US:Donations

The big question for me is can bean-query search in the custom directive fields and so forth -- such that I could potentially run a report against my file asking for any donations by a specific donor (e.g. "john-smith")?  I did a quick internet search for bean-query and custom but didn't really find anything.  

Thanks much!!

On Saturday, September 28, 2024 at 9:39:42 AM UTC-7 Martin Blais wrote:

[Rick F]

Is there a way in bean-query to dump the table of Entries (I'm referring to the code in query_env.py) containing a function called EntriesTable() which seems to be talking about directives and their attributes.  I'd love to be able to dump that table after loading my beancount file.  I'm feeling rather lost with trying to figure out whether BQ can help me with querying directives  -- custom directives in my case.  

When I fire up bean-query and it dumps me to a command prompt, if I then type help it comes up with commands that are available but there's nothing mentioned on how to use these commands.. I just get syntax errors when tying to use "describe" for instance.

thx!

[Chary Chary]

On Saturday, September 28, 2024 at 11:40:50 PM UTC+2 Rick F wrote:

;; fields are as follows :
;;      donor-id : a string defining a unique identifier that can be used to identify this donor

;;      donor-name : a string containing the name of the donor
;;      donor-address : a string of the address for the donor
;;      donor-city : a string containing the city the donor lives in
;;      donor -state : donor's state
;;      donor-zipcode : donor's zip code
;;      donor-phone : donors phone number
;;      donor-email : email address of donor
2024-09-28 custom "Donor" "john-smith" "John Smith" "123 First Street" "Palo Alto" "CA" "93501" "123-456-7890" "f...@bar.com"

Why do you want to keep all this information in the beancount ledger?

I would just keep keep it somewhere else (e.g. in Excel file). What is important in beancount is to put in a transaction some piece of information, which will allow you to uniquely identify a donor. In a simplest term this can be just a payee field

E.g.:  

2024-09-28 * "john-smith" "Donation"

  Assets:US:WellsFargo:Checking4431 -100.00 USD
  Income:US:Donations

You can then run a query, which will show you a total amount of donations per payee. E.g.:

select payee, sum(position)
where account = 'Income:US:Donations' and year 2014

You then import query result in Excel and just VLOOKUP address, name, city etc in Excel.

There is no reason to do all of this directly in beancount. 

And if you want to automate it further, you can build all this logic somewhere in Jupyter notebook.

Does it make sense?

 

[Rick F]

Maybe for me it's because I'm used to having all the data together, and shifting to something else where some is stored here and other parts there is a bit, perhaps, disconnected I suppose in my head ATM. Maybe I just need to stand back and try to picture a system where things don't quite work as you picture them in your mind and try to look at them from different angles.  In our case I also need to look at things from our business needs and I'll admit that using some of the ancillary packages I see for Beancount (e.g. beanhub-forms, etc) I might like to use at some point .. maybe.. thanks for the nudge.. I'll try to figure all this out.  

[Daniele Nicolodi]

On 30/09/24 05:16, Rick F wrote:
> Is there a way in bean-query to dump the table of Entries (I'm referring
> to the code in query_env.py) containing a function called EntriesTable()
> which seems to be talking about directives and their attributes.  I'd
> love to be able to dump that table after loading my beancount file.

The only way to print the content of the ledger without any filtering is
the PRINT directive. However, it does not return a tabular format, but
the content of the ledger in beancount format after it has been
interpreted by beancount.

Different beancount directives have different structure and fields, thus
it is not really possible to dump all of them in the same table.

> I'm
> feeling rather lost with trying to figure out whether BQ can help me
> with querying directives  -- custom directives in my case.

"custom" directives do not have a fixed structure, thus there isn't an
useful way to represent them in a relational table (having a list of
strings doesn't seem very useful to me).

> When I fire up bean-query and it dumps me to a command prompt, if I then
> type help it comes up with commands that are available but there's
> nothing mentioned on how to use these commands.

The existing documentation for bean-query is here

https://beancount.github.io/docs/beancount_query_language.html

It is a bit outdated, but should give you something to start from.

> I just get syntax
> errors when tying to use "describe" for instance.

All commands that are not BQL directives must be prefixed with a dot.

[Daniele Nicolodi]

On 29/09/24 03:41, Rick Flower wrote:
> Thanks Martin!  I'll keep that suggestion in mind but before I saw your
> reply I started playing with the custom directive and was able to get an
> example that passes muster with bean-check.  The example is below:
>
> 2013-01-01 open Assets:US:WellsFargo:Checking4431
>   institution: "Wells Fargo Bank NA"
>   address: "123 America Street, LargeTown, USA"
>   phone: "+1.012.345.6789"
>
> 2013-01-01 open Income:US:Donations
>
> ;; fields are as follows :
> ;;      donor-id : a string defining a unique identifier that can be
> used to identify this donor
> ;;      donor-name : a string containing the name of the donor
> ;;      donor-address : a string of the address for the donor
> ;;      donor-city : a string containing the city the donor lives in
> ;;      donor -state : donor's state
> ;;      donor-zipcode : donor's zip code
> ;;      donor-phone : donors phone number
> ;;      donor-email : email address of donor
> 2024-09-28 custom "Donor" "john-smith" "John Smith" "123 First Street"
> "Palo Alto" "CA" "93501" "123-456-7890" "f...@bar.com"
>
> 2024-09-28 txn "john-smith"

>   Assets:US:WellsFargo:Checking4431-100.00 USD

>   Income:US:Donations
>
> The big question for me is can bean-query search in the custom directive
> fields and so forth -- such that I could potentially run a report
> against my file asking for any donations by a specific donor (e.g.
> "john-smith")?  I did a quick internet search for bean-query and custom
> but didn't really find anything.

How you structure the data is mostly determined by how you foresee
querying the data. From your example above, you can easily use
bean-query for getting all the donations by a donor:

SELECT *
FROM #postings
WHERE
account = 'Income:US:Donations' AND
narration = 'john-smith'

You choose to have the donor name in the narration field, but you could
have it in a metadata entry:

2024-09-28 * "Donation"
donor: "john-smith"
Assets:US:WellsFargo:Checking4431-100.00 USD
Income:US:Donations

In this case, the query would be:

SELECT *
FROM #postings
WHERE
account = 'Income:US:Donations' AND
entry.meta['donor'] = 'john-smith'

And you could use the narration to record other relevant information,
for example the circumstance in which the donation was collected
(particular donation campaign, or event, for example).

On the other hand, keeping the donor information into a custom directive
does not seem optimal to me. As your big comment block above the custom
directive suggests, the custom directive does not enforce any structure
on the fields (and allows only strings and not more complex data types,
but this may not be a limitation of this specific use).

If you want to keep all data in the ledger, one way of doing it is to
assign a sub-account to every donor and record the donor data in the
account open directive:

2013-01-01 open Income:US:Donations

2024-09-28 open Income:US:Donations:John-Smith
name: "John Smith"
address: "123 First Street"
city: "Palo Alto"
state: "CA"
zipcode: "93501"
phone: "123-456-7890"
email: "f...@bar.com"

2024-09-28 * "Donation"

Assets:US:WellsFargo:Checking4431 -100.00 USD

Income:US:Donations:John-Smith

However, I think using a software dedicated to record personal data to
keep donor information and simply mark transactions with an unique donor
id. You can then use an external script for collating the ledger and
roster information to generate the reports.

Cheers,
Dan

[Daniele Nicolodi]

On 06/10/24 19:39, Daniele Nicolodi wrote:
> On the other hand, keeping the donor information into a custom directive
> does not seem optimal to me. As your big comment block above the custom
> directive suggests, the custom directive does not enforce any structure
> on the fields (and allows only strings and not more complex data types,
> but this may not be a limitation of this specific use).

Thinking about it a tiny bit more, if you really want to use a custom
directive to record the donor data, using metadata for the different
fields seems much nicer:

2024-09-28 custom "donor" "john-smith"

name: "John Smith"
address: "123 First Street"
city: "Palo Alto"
state: "CA"
zipcode: "93501"
phone: "123-456-7890"
email: "f...@bar.com"

However, there is still no way to extract this using bean-query (and
while typing this I also realized that Emacs beancount-mode also does
not do syntax highlighting for the custom directive).

Although, if someone has a concrete use-case and a nice idea ion how to
expose custom directive in a BQL table, it would not be difficult to add
it to bean-query.

Cheers,
Dan

[Rick F]

Thanks Dan.. I'll have to chew on some of this as time permits..  I appreciate the thoroughness of your replies!  

--Rick