Reports representing an accounts register

[Tim Tickner]

Hi all - I'm new to Beancount. I did a one-time translation from Quicken for Mac to Beancount with all transactions since 2024. I'm slowly onboarding to some of Red's features like the importer framework and a few plugins (shoutout effective_date and zerosum). I'm loving this tool so far!

Coming from a Quicken for Mac background, I'm used to single-entry accounting and viewing registers in respect to the physical account. This might be an issue of not having found the correct docs or thread on this Group yet, but I'm having trouble creating reports using BQL that recreate the register from Quicken.

My specific use case is CSV exports for import into Actual Budget (a great FOSS envelope style budgeting tool), where each CSV needs to be aligned to the physical account it came from, e.g. "Tim's Chase Card".

This is my attempt to explain the feature I'm looking for:

When generating reports, I want the ability to create a table of every posting from every transaction which contains the account I care about, but I want to exclude the specific posting of the account itself. For example:

2025-11-15 * "Joe Smith" "Venmo for lunch"

Assets:Banking:Checking:BofA -10.00 USD

Expenses:Dining:Restaurants

2025-11-15 * "Chase Bank" "Mortgage Payment"

Assets:Banking:Checking:BofA -2000.00 USD

Liabilities:Loans:Mortgage:123-Main-St 1200.00 USD

Expenses:House:Mortgage-Interest

Should generate 3 lines like the following:

Payee Narration Category Amount

--------------|---------------------|-----------------------|----------

Joe Smith Venmo for lunch Restaurants 10.00

Chase Bank Mortgage Payment Mortgage-Interest 800.00

Chase Bank Mortgage Payment 123-Main-St 1200.00

I know I will probably need to massage the final data to correct categories into their Actual Budget equivalents.

This is the closest I've gotten to a query that would work, where I dump that to CSV or a DataFrame, and remove any posting with the account for which I'm interested.

JOURNAL FROM HAS_ACCOUNT('Assets:Banking:Checking:BofA')

Am I on the right track? Is there a magic bullet here?

Also, how can I get tags and links in that JOURNAL query?

Thanks in advance!

Tim

[Chary Ev2geny]

Hi,

welcome to beancount!

Yes, it is a great tool!

This query shall work for you:

select payee, narration, leaf(account) as Category, position

where  'Assets:Banking:Checking:BofA' in other_accounts

Result:

payee      ! narration        ! category          ! position (USD)

------------------------------------------------------------------

Joe Smith    Venmo for lunch    Restaurants         10

Chase Bank Mortgage Payment    123-Main-St         1200

Chase Bank Mortgage Payment    Mortgage-Interest  800

You can check it here:

https://colab.research.google.com/drive/1BzdrUmWAd_0oxPqZ1G_rl3hD6RLWsVNH?usp=sharing

P.S. please let us now now you got on with  Red's importer framework, i still haven't got a time to get my head around it 

[Tim Tickner]

Perfect! That query is exactly what I was looking for. I didn't know `other_accounts` was a list I could look in. Is the beanquery source code the only resource to learn BQL syntax and tricks like that or is there a wholistic doc somewhere?

---------------------------------------------------------------

I'm liking Red's so far! I have all of my Checking/Savings/Credit Card imports working. All but one have OFX/QFX downloads, the other is a CSV. All of them were really easy to implement. I have made four changes to the base objects for reasons that suit my purpose:

1. I override the FLAG on each importer to use beancount.core.flags.FLAG_WARNING, so each transaction has a ! instead of a *. This helps me know which transactions I've reviewed after importing.
2. I'd rather have a payee than a narration. I know narration is required but for every Banking (Checking/Savings/Credit Card) account, I'll just set it to "" by default and set the importer narration to be the transaction payee. e.g. by default "SUNOCO 123" would be the narration and payee would not exist - I have it set so that payee is "SUNOCO 123" and narration is "".
3. I override the `build_metadata()` method on the Importer class to set the imported payee (referenced as "SUNOCO 123" in #1 above) to a metadata field on the base transaction called source_payee. I think this is a helpful debug field and like having it for reference. It makes lining up with the bank statement easy.
4. I subclass the PredictPostings class from smart_importer to set new weights that strongly favor the source_payee metadata from #3. This has vastly improved the quality of transaction import auto-categorization.

My next step is to set up downloads using `ofxget` which is documented on Red's blog.

I'm still running Quicken in parallel with Beancount and will likely do this until my annual subscription runs out. My hope is that by then I've fully vetted a workable automated process with Beancount; my target is to get as close to the one-click sync I get with Quicken as possible (I know it won't be as flawless but maybe I can get close!).

Tim

[Chary Ev2geny]

On Tuesday, November 18, 2025 at 2:12:34 AM UTC+1 timothy...@gmail.com wrote:

Is the beanquery source code the only resource to learn BQL syntax and tricks like that or is there a wholistic doc somewhere?

The official beanquery documentation is not up to speed with the latest changes to beanquery, but still a good start, as beanquery is kept backwards compatible.

The internal beanquery documentation seems to be up to speed (automatically)

Specifically the other_accounts  can be found if from the beanquery prompt you type:

.help where 

E.g.:

beanquery> .help where

A logical expression that consist of columns on postings and simple
functions.

Columns
-------

type: str

....

other_accounts: set
  The list of other accounts in the transaction, excluding that of this posting.

In general I tend to do basic extractions using beanquery and get the data into pandad dataframe. And then process the data further in pandas (see example)