Export beancount ledger to PostgreSQL

[Tuno Tunante]

Dear all,

Long time using beancount and more and more happy. Thank you!

Sometimes you could need to make more complex searches or accounting. I would like to propose you a script to help to 

export the ledge to a PostgreSQL database.

The file import_csv.sh do:

• Delete (if exist) the old table with all the transactions
• Generate the CSV from the current bean file
• Create the table in the database
• Import the data to the table
• Erase all the spaces in front and behind all the string entries.

All the information and some examples for translate the bean-query to psql commands are here: 

https://github.com/tinotuno/Beancount-PostgreSQL

Regards.

[Martin Blais]

It's already been there for a while:

https://github.com/beancount/beancount/blob/master/beancount/scripts/sql.py

The problem is that one still needs to aggregate positions together by currency and cost and so on, so it's difficult to use a regular database unless you have everything in a single currency.

Otherwise we'd already be using an existing SQL database...

--
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/26b171bc-6280-48a9-b098-72cd57dfb743o%40googlegroups.com.

[Daniele Nicolodi]

On 27/06/2020 15:46, Tuno Tunante wrote:
> Dear all,
>
> Long time using beancount and more and more happy. Thank you!
>
> Sometimes you could need to make more complex searches or accounting. I
> would like to propose you a script to help to 
> export the ledge to a PostgreSQL database.
>
> The file import_csv.sh do:
>

> * Delete (if exist) the old table with all the transactions
> * Generate the CSV from the current bean file
> * Create the table in the database
> * Import the data to the table
> * Erase all the spaces in front and behind all the string entries.

>
> All the information and some examples for translate the bean-query to
> psql commands are here: 
>
> https://github.com/tinotuno/Beancount-PostgreSQL

Hello,

I quickly skimmed through the script and it looks like you are using
real numbers to represent amounts in the PostgreSQL table.

This is not the correct way to represent financial quantities: because
many finite decimal numbers do not have a finite representation in
binary, they need to be stored with rounding errors. The rounding errors
on most functions computed on these values depend on the order in which
the values are used. Thus it is very likely to end up with discrepancies
on computed values.

The right data type to use is "decimal" (or "numeric" in SQL) which
allow exact representation and computation (up to a fixed number of
digits). This is supported by PostgreSQL:

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

Cheers,
Dan

[Tuno Tunante]

Thanks Dan!

I tested and worked perfectly! I did the commit/push to the repository with your modification.

Cheers.

Tino

--
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/8df36167-1851-8b16-7a7e-dd3aa35d2bf3%40grinta.net.

[Tono Riesco]

Hi Martin,

Yes I know, I was testing a bit. Really a good code.
I tried to do simple and “dirt” in case you need some complex SQL requests.

Is just 20 lines of shell code to put all the data without any treatment to the DB.

Thank you very much for your work. Using Beancount for a while and more and more happy.

Cheers.

Tuno.

> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhPg7qauy3rDdewjF0D-X1WC35MRPyK-%3DrY%3DmN_wifU0rA%40mail.gmail.com.

[Martin Blais]

Thank you!

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/F526E141-7FEF-48DC-BEA5-D02272756EA2%40riesco.ch.