Re: Introducing Beanpost: A PostgreSQL-Based Experiment for Beancount Users

[ben]

Chary,

Thanks for your interest and good questions!

> On Apr 24, 2024, at 3:22 PM, Chary Chary  wrote:
> 
> Question 1: What can you do with beanpost, what you can't do with beanquery?

I think there are two key differences between beanpost and beanquery: the SQL 
ecosystem and integration with other systems.

At a basic level, the query syntax for beanquery is similar to PostgreSQL as 
used in beanpost. However, as Martin points out in the "Motivation" section of 
the beanquery documentation 
(https://beancount.github.io/docs/beancount_query_language.html), writing pure 
SQL queries for Beancount data can be challenging, which is why he created 
beanquery to offer a "SQL-like query client." The beanquery client has extra 
features that simplify query writing compared to pure SQL. I have already 
implemented some of these features in beanpost, but working with pure SQL may 
never be as straightforward as using a specialized query language like 
beanquery.

However, beanquery is an "SQL-like" language, while beanpost uses PostgreSQL. 
This means we have the full power of a real database with standard SQL and 
aren't limited to the features provided by beanquery. In theory, any query or 
data manipulation is possible. For example, I wrote a function in PostgreSQL to 
calculate an average cost basis—a feature not provided by Beancount (as far as 
I know; someone correct me if I'm wrong), which would probably be impossible to 
implement with beanquery.

Additionally, PostgreSQL is an industry-standard tool and can be used as a 
back-end for various clients, such as custom web apps, reporting tools, and 
more. These kinds of integrations aren't really feasible with beanquery.

> Question 2: The documentation says:
> 
> Transaction dates: Each posting can have its own date, allowing transactions 
> to balance even if individual postings have different dates. This helps with 
> common issues when transferring money between accounts where withdrawal and 
> deposit dates differ.
> How do you achieve this, if initially the database is created by importing 
> beancount file, which has only one date per transaction?

On import into the database, we assign the beancount transaction date to each 
posting. On export to a beancount file, since beancount doesn't support dated 
postings, we use the date of the first posting for the transaction date.

I hope this answers your questions. If you have more queries or need further 
clarification, I'm happy to help.

Best regards,
Ben

-- 
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 on the web visit 
https://groups.google.com/d/msgid/beancount/8811E442-DE73-406F-AA47-47AC302C4802%40gerdemann.org.

Re: Introducing Beanpost: A PostgreSQL-Based Experiment for Beancount Users

[Chary Chary]

Ben,

thank you very much for for sharing your tool.

May be a stupid questions but

*Question 1*: What can you do with beanpost, what you can't do with 
beanquery?

*Question 2: *The documentation says:


   - *Transaction dates: Each posting can have its own date, allowing 
   transactions to balance even if individual postings have different dates. 
   This helps with common issues when transferring money between accounts 
   where withdrawal and deposit dates differ.*

How do you achieve this, if initially the database is created by importing 
beancount file, which has only one date per transaction?

On Wednesday, April 24, 2024 at 1:20:16 PM UTC+2 bl...@furius.ca wrote:

> Oh wow, that's crazy cool. I didn't know postgresql supported that!
> Will have a more detailed look, sorry for not digging in deeper
>
> On Mon, Apr 22, 2024, 14:46 Ben Gerdemann  wrote:
>
>> Hi Everyone,
>>
>> I hope this email finds you well. I've been using Beancount for many 
>> years. It's been an amazing tool, and I'm grateful to this community and 
>> especially to Martin Blais for maintaining such a fantastic project.
>>
>> I'm sharing a project I've been working on called Beanpost, which you can 
>> find on GitHub at https://github.com/gerdemb/beanpost. This started as 
>> an experiment to see if I could recreate plain text accounting 
>> functionality using a PostgreSQL database, and it turned out more 
>> successful than I expected. I thought the Beancount community might find it 
>> useful and interesting.
>>
>> Beanpost consists of a PostgreSQL schema and import/export commands that 
>> let you transfer data between a beancount file and a PostgreSQL database. 
>> Much of Beancount's functionality is implemented using custom PostgreSQL 
>> functions, allowing for complex queries and data manipulation. This setup 
>> provides a flexible backend that can integrate with other tools like web 
>> apps or reporting systems.
>>
>> One of the reasons I created Beanpost was to build a mobile-friendly 
>> custom front-end. Fava is a fantastic tool, but it's not very 
>> mobile-friendly, isn't intuitive for beginners, and is hard to customize. 
>> With Beanpost, you have more flexibility to create a custom front-end that 
>> suits your needs.
>>
>> The included functionality is as follows:
>>
>>- Functions to calculate account balances or changes over a specific 
>>period.
>>- Support for calculations on individual accounts or account 
>>hierarchies (e.g., "Assets").
>>- Check balance assertions.
>>- Verify that transactions balance.
>>- Support for explicit precisions for each currency to use for 
>>balance tolerances.
>>- Display the running balance of an account by posting.
>>- Convert between currencies, including converting baskets of 
>>currencies.
>>- Store documents as byte-data in the database.
>>
>> I'm curious to hear your thoughts on this approach. Would love to hear 
>> any feedback. I think I've implemented the majority of the required 
>> features, but maybe I'm missing a few things?
>>
>> Maybe we could call this "database-based accounting" instead of "plain 
>> text accounting." 
>>
>> Thank you for your time, and I look forward to hearing from you.
>>
>> Best regards,
>>
>> Ben
>>
>> -- 
>>
> 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/143e6263-3db3-4532-ae0e-eac6d7e6ffebn%40googlegroups.com
>>  
>> 
>> .
>>
>

-- 
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 on the web visit 
https://groups.google.com/d/msgid/beancount/b66aa343-3cbb-4038-aa03-995c29e08071n%40googlegroups.com.

Re: Introducing Beanpost: A PostgreSQL-Based Experiment for Beancount Users

[ben]

> On Apr 24, 2024, at 8:19 AM, Martin Blais  wrote:
> 
> Oh wow, that's crazy cool. I didn't know postgresql supported that!
> Will have a more detailed look, sorry for not digging in deeper


Martin, 

Thanks for your interest. This started as a simple experimental side project, 
but I was able to take the implementation much further than I expected, and I 
thought it was cool enough to share with the Beancount community. To be honest, 
I'm not really sure what I want to do with it next, but I thought it would be 
interesting to get some feedback from the community to validate the idea. The 
current implementation covers my personal use cases, but I know that Beancount 
is used in many different ways, and I wonder if there is any other core 
functionality that might be missing.

The foundation of the idea is defining a custom type `amount` in PostgreSQL 
that combines a numeric (decimal) number with a commodity code. Then we can 
write custom functions to add and aggregate amounts to create balances of 
currencies, aggregate postings into lots for cost-basis calculations, and 
convert between different currencies.

I would say that currently, the biggest missing features I’m aware of revolve 
around validation. Although there are checks that transactions are balanced and 
that balance assertions are correct, there are some minor validation points and 
edge cases that are not yet addressed. I've made a list of the ones I can think 
of in the README, but there are probably more.

Another major missing feature is statement import. Since the data is just 
stored in a PostgreSQL database, it should be straightforward to write an 
importer—or convert an existing Beancount importer—to INSERT data directly into 
the database. However, I would consider this out of the scope of this project.

For my personal project, I am using Postgraphile to automatically expose a 
GraphQL API of the database and am writing a custom React-based front-end as a 
client to the data. For now, I’m still importing the Beancount data into the 
database and then exporting the changes back to a Beancount file, as Beancount 
still provides functionality that I need. But eventually, I could imagine 
moving everything to the database.

By the way, I just finished adding LIFO/FIFO cost-basis functions. Check the 
README for more details.

https://github.com/gerdemb/beanpost

Best Regards,
Ben

-- 
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 on the web visit 
https://groups.google.com/d/msgid/beancount/51BA23C9-E9E6-44FE-AA18-5287C5C06068%40gerdemann.org.

Re: Introducing Beanpost: A PostgreSQL-Based Experiment for Beancount Users

[Martin Blais]

Oh wow, that's crazy cool. I didn't know postgresql supported that!
Will have a more detailed look, sorry for not digging in deeper

On Mon, Apr 22, 2024, 14:46 Ben Gerdemann  wrote:

> Hi Everyone,
>
> I hope this email finds you well. I've been using Beancount for many
> years. It's been an amazing tool, and I'm grateful to this community and
> especially to Martin Blais for maintaining such a fantastic project.
>
> I'm sharing a project I've been working on called Beanpost, which you can
> find on GitHub at https://github.com/gerdemb/beanpost. This started as an
> experiment to see if I could recreate plain text accounting functionality
> using a PostgreSQL database, and it turned out more successful than I
> expected. I thought the Beancount community might find it useful and
> interesting.
>
> Beanpost consists of a PostgreSQL schema and import/export commands that
> let you transfer data between a beancount file and a PostgreSQL database.
> Much of Beancount's functionality is implemented using custom PostgreSQL
> functions, allowing for complex queries and data manipulation. This setup
> provides a flexible backend that can integrate with other tools like web
> apps or reporting systems.
>
> One of the reasons I created Beanpost was to build a mobile-friendly
> custom front-end. Fava is a fantastic tool, but it's not very
> mobile-friendly, isn't intuitive for beginners, and is hard to customize.
> With Beanpost, you have more flexibility to create a custom front-end that
> suits your needs.
>
> The included functionality is as follows:
>
>- Functions to calculate account balances or changes over a specific
>period.
>- Support for calculations on individual accounts or account
>hierarchies (e.g., "Assets").
>- Check balance assertions.
>- Verify that transactions balance.
>- Support for explicit precisions for each currency to use for balance
>tolerances.
>- Display the running balance of an account by posting.
>- Convert between currencies, including converting baskets of
>currencies.
>- Store documents as byte-data in the database.
>
> I'm curious to hear your thoughts on this approach. Would love to hear any
> feedback. I think I've implemented the majority of the required features,
> but maybe I'm missing a few things?
>
> Maybe we could call this "database-based accounting" instead of "plain
> text accounting." 
>
> Thank you for your time, and I look forward to hearing from you.
>
> Best regards,
>
> Ben
>
> --
> 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 on the web visit
> https://groups.google.com/d/msgid/beancount/143e6263-3db3-4532-ae0e-eac6d7e6ffebn%40googlegroups.com
> 
> .
>

-- 
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 on the web visit 
https://groups.google.com/d/msgid/beancount/CAK21%2BhPwsZmzukX5vfxodBXBhoQ9U8vU_nArs%2Bed5SYDoOWz2A%40mail.gmail.com.

Re: Introducing Beanpost: A PostgreSQL-Based Experiment for Beancount Users

[ben]

Hi Martin,

Thanks for your reply and for creating such a valuable tool in Beancount. I 
wrote a response yesterday, but I haven't seen it posted to the mailing list 
yet, so apologies if there's some repetition in this message.

Beanpost does support multiple currencies using a custom PostgreSQL type and 
custom aggregator functions. For example, the query SELECT sum(amount) FROM 
posting; will return a summed balance (a basket of currencies) for the 
postings, allowing you to work with different commodities and calculate 
balances across various accounts. I'm sorry I didn't make that clearer in my 
earlier message.

Regarding cost basis, I went ahead and implemented a basic version. I added 
functions for matching augmentations and reductions in an inventory, equivalent 
to the "STRICT" booking method. There's also a function for calculating an 
average cost basis. The query to retrieve these results looks like this:

SELECT
  inventory(posting.*) AS booking_method_none,
  cost_basis(posting.*) AS booking_method_strict,
  cost_basis_avg(posting.*) AS booking_method_average 
FROM
  posting;

There's no validation yet, so lots can go negative, and currencies between 
augmentation and reduction don't have to match. I think these checks should be 
fairly straightforward to add later. If you'd like to explore the 
implementation, you can find it in the new branch of my repository: 
https://github.com/gerdemb/beanpost/tree/inventory. I'm also considering how to 
implement FIFO and LIFO cost-basis methods.

Thank you again for your interest and for your valuable feedback. I'm looking 
forward to hearing what you think.

Best regards,
Ben


> On Apr 23, 2024, at 12:12 AM, Martin Blais  wrote:
> 
> Thanks for sharing, Ben,
> Broadly speaking this approach will work if you don't have to match cost 
> basis and if you have only a single currency.
> Note that export to SQL has existed for a while, but I think to be able to 
> most generally replace the functionality from Beancount one would need a 
> custom aggregator implementation (to accumulate positions with a variety of 
> commodities and track individual lots, i.e., the Inventory class).
> 
> 
> 
> 
> 
> On Mon, Apr 22, 2024 at 2:46 PM Ben Gerdemann  wrote:
> Hi Everyone,
> I hope this email finds you well. I've been using Beancount for many years. 
> It's been an amazing tool, and I'm grateful to this community and especially 
> to Martin Blais for maintaining such a fantastic project.
> I'm sharing a project I've been working on called Beanpost, which you can 
> find on GitHub at https://github.com/gerdemb/beanpost. This started as an 
> experiment to see if I could recreate plain text accounting functionality 
> using a PostgreSQL database, and it turned out more successful than I 
> expected. I thought the Beancount community might find it useful and 
> interesting.
> Beanpost consists of a PostgreSQL schema and import/export commands that let 
> you transfer data between a beancount file and a PostgreSQL database. Much of 
> Beancount's functionality is implemented using custom PostgreSQL functions, 
> allowing for complex queries and data manipulation. This setup provides a 
> flexible backend that can integrate with other tools like web apps or 
> reporting systems.
> One of the reasons I created Beanpost was to build a mobile-friendly custom 
> front-end. Fava is a fantastic tool, but it's not very mobile-friendly, isn't 
> intuitive for beginners, and is hard to customize. With Beanpost, you have 
> more flexibility to create a custom front-end that suits your needs.
> The included functionality is as follows:
> • Functions to calculate account balances or changes over a specific 
> period.
> • Support for calculations on individual accounts or account hierarchies 
> (e.g., "Assets").
> • Check balance assertions.
> • Verify that transactions balance.
> • Support for explicit precisions for each currency to use for balance 
> tolerances.
> • Display the running balance of an account by posting.
> • Convert between currencies, including converting baskets of currencies.
> • Store documents as byte-data in the database.
> I'm curious to hear your thoughts on this approach. Would love to hear any 
> feedback. I think I've implemented the majority of the required features, but 
> maybe I'm missing a few things?
> Maybe we could call this "database-based accounting" instead of "plain text 
> accounting." 
> Thank you for your time, and I look forward to hearing from you.
> Best regards,
> Ben
> 
> -- 
> 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 on the web visit 
> https://groups.google.com/d/msgid/beancount/143e6263-3db3-4532-ae0e-eac6d7e6ffebn%40googlegroups.com.
> 
> -- 
> You received this message because you are 

Re: Introducing Beanpost: A PostgreSQL-Based Experiment for Beancount Users

[Ben Gerdemann]

Hi Martin,

Thanks for your feedback, and thanks for maintaining Beancount. Your work 
has made it such a great tool for so many of us.

I wanted to clarify that Beanpost does support multiple currencies using a 
custom PostgreSQL type and custom aggregator functions. I apologize for not 
making that clearer in my initial message. For example, the query SELECT 
sum(amount) FROM posting will return a summed inventory of all the 
currencies for the postings, allowing you to work with different 
commodities.

Regarding cost-basis, I haven't personally needed it yet, so that's why 
it's not implemented. However, I do import the cost and price information 
for each posting, which would be the basis for the calculation. I would 
need to review the documentation about how to calculate cost-basis 
accurately, but my basic idea is to write custom PostgreSQL functions to 
perform different cost-basis calculations. I'll also need to think about 
how to represent the matching of lots in SQL. Perhaps using foreign keys to 
link back to the matching lots could be a potential solution.

Thanks again for your guidance and insights.

Best regards,

Ben

On Tuesday, April 23, 2024 at 12:12:57 AM UTC-3 bl...@furius.ca wrote:

> Thanks for sharing, Ben,
> Broadly speaking this approach will work if you don't have to match cost 
> basis and if you have only a single currency.
> Note that export to SQL has existed for a while, but I think to be able to 
> most generally replace the functionality from Beancount one would need a 
> custom aggregator implementation (to accumulate positions with a variety of 
> commodities and track individual lots, i.e., the Inventory class).
>
>
>
>
>
> On Mon, Apr 22, 2024 at 2:46 PM Ben Gerdemann  wrote:
>
>> Hi Everyone,
>>
>> I hope this email finds you well. I've been using Beancount for many 
>> years. It's been an amazing tool, and I'm grateful to this community and 
>> especially to Martin Blais for maintaining such a fantastic project.
>>
>> I'm sharing a project I've been working on called Beanpost, which you can 
>> find on GitHub at https://github.com/gerdemb/beanpost. This started as 
>> an experiment to see if I could recreate plain text accounting 
>> functionality using a PostgreSQL database, and it turned out more 
>> successful than I expected. I thought the Beancount community might find it 
>> useful and interesting.
>>
>> Beanpost consists of a PostgreSQL schema and import/export commands that 
>> let you transfer data between a beancount file and a PostgreSQL database. 
>> Much of Beancount's functionality is implemented using custom PostgreSQL 
>> functions, allowing for complex queries and data manipulation. This setup 
>> provides a flexible backend that can integrate with other tools like web 
>> apps or reporting systems.
>>
>> One of the reasons I created Beanpost was to build a mobile-friendly 
>> custom front-end. Fava is a fantastic tool, but it's not very 
>> mobile-friendly, isn't intuitive for beginners, and is hard to customize. 
>> With Beanpost, you have more flexibility to create a custom front-end that 
>> suits your needs.
>>
>> The included functionality is as follows:
>>
>>- Functions to calculate account balances or changes over a specific 
>>period.
>>- Support for calculations on individual accounts or account 
>>hierarchies (e.g., "Assets").
>>- Check balance assertions.
>>- Verify that transactions balance.
>>- Support for explicit precisions for each currency to use for 
>>balance tolerances.
>>- Display the running balance of an account by posting.
>>- Convert between currencies, including converting baskets of 
>>currencies.
>>- Store documents as byte-data in the database.
>>
>> I'm curious to hear your thoughts on this approach. Would love to hear 
>> any feedback. I think I've implemented the majority of the required 
>> features, but maybe I'm missing a few things?
>>
>> Maybe we could call this "database-based accounting" instead of "plain 
>> text accounting." 
>>
>> Thank you for your time, and I look forward to hearing from you.
>>
>> Best regards,
>>
>> Ben
>>
>> -- 
>> 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/143e6263-3db3-4532-ae0e-eac6d7e6ffebn%40googlegroups.com
>>  
>> 
>> .
>>
>

-- 
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 on the web visit