Command to export transactions to csv

1,057 views
Skip to first unread message

Alex Johnstone

unread,
Aug 11, 2016, 11:32:07 PM8/11/16
to Beancount

I'm trying to export all my transactions to csv. I was using bean-report to do so with the following line;

bean-report -f csv -o ~/Documents/money.csv money.beancount register

I also tried journal instead of register.

However, the csv that gets exported is just the top line of each transaction:

2015-10-29,*,Netflix,,

If someone could point me in the right direction I'd appreciate that.

Thanks,
Alex

Martin Blais

unread,
Aug 13, 2016, 9:30:05 PM8/13/16
to Beancount
Hi Alex,

What this does is simply convert the same table output that you would have seen without the CSV format to CSV.
If you look carefully, you will see some of the numbers rendered from the postings.
The way you're invoking this  is not useful at the moment (though I admit it's not obvious why), for instance, the formatting of inventories with multiple currencies is split between multiples lines, as it would in the table rendering. This report outputs the same data that is being rendered in the HTML view. This makes little sense for a CSV output.

I have been thinking about rendering inventories in a single "cell", and to implement an explicit "flattening" of posting rows when an inventory with multiple currencies or lots has to be rendered, on multiple lines. The shell doesn't do this well at the moment, but ultimately, this type of output should be reduceable to a shell command.

Now, I have been thinking about building an explicit export report of all postings to a table format. 
I'm wondering if what I have in mind is what you're looking for.
What would you like to see exactly?
One row per posting?
And would you replicate the fields of each transaction?






--
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+unsubscribe@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/58e9eed0-2aef-4a89-b589-7cc804a8ace6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Alex Johnstone

unread,
Aug 14, 2016, 12:02:18 AM8/14/16
to Beancount
Ah yes, now that I think about it, it's not as straight forward as I originally (didn't) think.

My intent was to get some of the data into a spreadsheet to do some forecasting, when a foreign account is likely to get low so I can start preparing a transfer. Plus general trending and charting of various categories to see what's been happening over time.

I feel like I may need to write a script to output data from beancount when it meets certain criteria for what I'm looking for.

Martin Blais

unread,
Aug 14, 2016, 12:45:37 AM8/14/16
to Beancount
There are couple of issues. In particular, one needs to 
- duplicate the desired transaction-specific data for each row (perhaps that's okay)
- if the balance is to be rendered, if this balance contains multiple currencies, somehow replicate the row data for each of these
- if you want to calculate anything, you'll have to aggregate by currency
- convert to cost or market value if necessary.
So there's no straightforward general answer... depends what you're trying to do.

However, a basic table report can be produced with something like this:

  bean-query $L 'select date, flag, maxwidth(description, 64), account, number, currency'

In order to export, this would be great:

  bean-query -f csv $L 'select date, flag, maxwidth(description, 64), account, number, currency'

But I haven't implemented shell output to CSV yet.




--
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+unsubscribe@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/60512c29-710e-4b55-9b47-c6ff44e9db1e%40googlegroups.com.

Alex Johnstone

unread,
Aug 15, 2016, 9:08:19 AM8/15/16
to bean...@googlegroups.com
Hello,

I've used other finance programs and for "split transactions" they just output each account per row, so I think all parts of a transaction should have its own row. It means more transactions but it's predictable. 

I'd keep it to just the raw data as close as possible, no conversions or grouping etc. It adds complexity and it's something I can easily do in whatever tool I'm exporting to. However, that's just considering what I want to use it for. 

Thanks,
Alex





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/V-f6JFhR6R0/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.

Simon Michael

unread,
Aug 15, 2016, 11:32:56 AM8/15/16
to bean...@googlegroups.com
hledger does this - one row per posting, with the common transaction
fields repeated in each, and a transaction id field so you can re-group
them into transactions.

Martin Blais

unread,
Aug 18, 2016, 12:16:48 AM8/18/16
to Beancount
Yes.
The terminology you use is a bit different than the one I usually refer to, but I think we're saying the same thing.

  Transaction
    Posting1  Amount1
    Posting2  Amount2
    Posting3  Amount3

where "Transaction" stands for some fields of the transaction selected and "Posting" some fields for the posting, would result in

  Transaction Posting1  Amount1
  Transaction Posting2  Amount2
  Transaction Posting3  Amount3

The problem I was alluding to is that if you render repeated values, such as links or balance (a balance is of type "Inventory", which is a list of positions), the default table rendering would do this:

  Transaction Posting1  Amount1  BalancePosition1
                                 BalancePosition2
  Transaction Posting2  Amount1  BalancePosition1
                                 BalancePosition2
  ...

I changed the default to render inventories like this:

  Transaction Posting1  Amount1  BalancePosition1.BalancePosition2
  Transaction Posting2  Amount1  BalancePosition1,BalancePosition2

It just makes a large balance cell, but it's more rational and it's what I always thought it should be. 
Besides, if you have only a single currency, or if you convert to one (or market value), the inventory in the balance would have a single position and it wouldn't matter.
The shell now has an "expand" shell variable to control this and keep the old behavior. (Eventually I'll probably fold this into the SQL syntax itself, I think that's where it deserves to be.)

So, it turns out I recently was on vacation with my girlfriend and she was complaining tonight that my export of our expenses were difficult to review in text format, so after doing this I had a quick go at implementing CSV rendering for bean-query and so I did it... it works. bean-query now takes a -f/--format=csv option and you should be able to import that into a spreadsheet.

Note the existence of the upload-csv-google-sheet in Beancount, which allows me to provide an existing Sheets doc id and a list of filenames, and the script creates sheets automatically, uploading all the data. This is convenient. I just used it to share our vacation expenses. (It can be a little flaky but it works; I need to convert it to the recently released new Google Sheets API, at the moment it's using gdata, which is ancient, but still sort of works).

There's a final twist: Not expanding rows by default is probably not enough in some cases. If you select a column of type Amount (like "units", or "cost"), the value rendered will be the number and its currency in a single cell, like this: "324.34 USD". Importing this in a column won't make it possible to do calculations on it. What you could do is just select the number. Anyhow, I think I'll implement that behavior (rendering Amount's number and currencies as two columns) and add another option for this soon.

I hope this helps,



To unsubscribe from this group and all its topics, send an email to beancount+unsubscribe@googlegroups.com.

To post to this group, send email to bean...@googlegroups.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+unsubscribe@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/23ADF5F8C7089991.D68696F3-E461-486C-B331-53F7C73022BA%40mail.outlook.com.

buycounterf...@gmail.com

unread,
Mar 30, 2020, 2:24:57 AM3/30/20
to Beancount
UV light Undetectable AED, SGD, KWT, USD, CUD, INR, UK notes For Sale Whatsapp only +91 8929872551
We print and sell Grade A banknotes of over 52 currencies in the world
Our bills by pass the latest money detector machines such as the pen test ad UV light and even eye detection by counterfeit experts
. We also produce the new Plastic Polymer bills of Canada and UK 5 10 and 20 pound notes and Clean back notes of all currency .Our notes are simply the best.
INR (USD,EURO,AUD,CAD,POUND ETC)
Whatsapp only +447466119097




WHERE CAN YOU SPEND THE MONEY ? MC DONALD'S ,SHOPS,SUPERMARKETS , PETROL SHOPS, ATM, BANKS,SHOPPING MALLS , TAXI , Video Purposes


On Friday, 12 August 2016 09:02:07 UTC+5:30, Alex Johnstone wrote:

I'm trying to export all my transactions to csv. I was using bean-report to do so with the following line;

bean-report -f csv -o ~/Documents/money.csv money.beancount register

I also tried journal instead of register.

However, the csv gets exported is just the top line of each transaction:
Reply all
Reply to author
Forward
0 new messages