Beanborg automatically imports financial transactions from external CSV files into the Beancount bookkeeping system.

• Python 3
• Beancount v2

Beanborg has two main design goals:

• automatic matching of transaction data with the correct Expense accounts
• speed, the tool is designed to process several financial CSV files in few seconds

Given the following transaction from a CSV file:

04.11.2020;04.11.2020;Direct Debit;"Fresh Food Inc.";-21,30;EUR;0000001;UK0000001444555

Beanborg imports the transaction in Beancount and assign the Account "Expense:Grocery" to the transaction:

2020-11-04 * "Fresh Food Inc." ""
csv: "04.11.2020,04.11.2020,Direct Debit,Fresh Food,-21,30,EUR,0000001,UK0000001444555"
md5: "60a54f6ed13ae7b7e70fd475eb677511"
Assets:Bank1:Bob:Current  -21.30 EUR
Expenses:Grocery      

Other features:

• sophisticated and extendible rule based system
• duplicated transactions detection
• highly configurable
• smart archiving function: when archiving a CSV file, the file is renamed using the start and end date of the CSV file

A simple tutorial is available here

Currently, it is not possible to install Beanborg using pip. This feature will be added soon. Stay tuned!

Clone this repository and add the beanborg folder to your shell's path.

git clone https://github.com/luciano-fiandesio/beanborg

# Bash: add the following to your ~/.profile or ~/.bash_profile

PATH=$PATH:~/../beanborg/beaborg

# Fish: add the following to your config.fish file 

fish_add_path ~/../beanborg/beanborg

Beanborg is based on a very specific workflow, which may or may not work for you.

The workflow is based on 3 distinct stages:

• Move a CSV file downloaded from a bank/financial institution website into the stage area
• Import the CSV file into the Beancount ledger and automatically categorize the transactions
• Move the bank CSV file into the archive area

The first stage is executed by the bb_mover.py script.

The second stage is executed by the bb_import.py script.

The third stage is executed by the bb_archive.py script.

Each financial institution from which data will be imported must have a dedicated YAML configuration file. The configuration file is used by the import scripts to determine the CSV file structure and other information, including which rules to apply.

A Beanborg configuration must start with the --- !Config tag and has 3 main sections:

The csv section of the configuration file determines the options related to the structure and location of the CVS file to import. Here are the list of options for the csv section:

The indexes section of the configuration file allows mapping each CSV "column" (or index) to the information required to parse and import the data. In other words, each option is used by Beanborg to determine where the date or amount of each transaction is located on the CVS file.

Note that the first index starts from 0.

--- !Config
csv:
  download_path: "/home/mike/downloads"
  name: wells-fargo
  bank_ref: wfa
  date_format: "%d/%m/%Y"
  skip: 1
  
indexes:
  date:   1
  amount: 2
  counterparty: 6

rules:
  beancount_file: 'main-ledger.ldg'
  rules_file: well-fargo.rules
  account: 565444499
  currency: USD
  ruleset:
    - Replace_Asset
    - Replace_Expense

The Beanborg's rules engine comes with a number of preexisting rules. Rules are always referenced by name and can be used to assign an account to a transaction, ignore a transaction or replace the name of a transaction's counterparty. Some rules require a look-up table file to find the right value and execute the rule action.

A look-up table file is also a CSV file, composed of 3 columns: value, expression, result.

• The value represents the string that the rule has to search for.
• The expression represents the matching criteria: equals, startsWith, endsWith, contains
• The result represents the rule's output

The next section lists the rules which are available in Beanborg.

This rule can be used to replace the name of a counterparty. This rule requires a look-up file named payee.rules located in the directory defined by the rules.rules_folder option of the config file.

For example: we want to add this transaction to the ledger, but we want to replace "Fresh Food Inc." with "FRESH FOOD".

04.11.2020;04.11.2020;Direct Debit;"Fresh Food Inc.";-21,30;EUR;0000001;UK0000001444555

Add the Replace_Payee rule to the list of rules in the configuration file for the target financial institution and add this entry to the payee.rules file:

Fresh Food Inc.;equals;FRESH FOOD

This rule is used to assign an Account to a transaction based on the value of the counterparty index of the CSV file. This rule requires a look-up file named account.rules located in the directory defined by the rules.rules_folder option of the config file.

For example: we want to add this transaction to the ledger and we want to assign the Account Expenses:Grocery to the transaction.

04.11.2020;04.11.2020;Direct Debit;"Fresh Food Inc.";-21,30;EUR;0000001;UK0000001444555

Add the Replace_Expense rule to the list of rules in the configuration file for the target financial institution and add this entry to the account.rules file:

Fresh Food Inc.;equals;Expenses:Groceries

Assigns an "origin" account to a transaction, based on value of the account index of a CSV file row. This rule is useful to assign the correct source account of a CSV transaction. This rule is implicitly added to the ruleset, even if it doesn't get declared

The rule can resolve the origin account in two ways:

• using a look-up file named asset.rules located in the directory defined by the rules.rules_folder option of the config file
• using the value of the property rules.origin_account of the config file in use

As an example, let's take this CSV transaction. We want to import the transaction so that the origin account is set to Assets:Jim:Current.

04.11.2020;04.11.2020;Direct Debit;"Fresh Food Inc.";-21,30;EUR;0000001;UK0000001444555

Add the Replace_Asset to the ruleset and create an asset.rules file. Add the following snippet to the asset.rules file:

value;expression;result
UK0000001444555;equals;Assets:Jim:Current

The rule will match the value of the account CSV index (UK0000001444555) to Assets:Jim:Current and create the Beancount transaction. If no match is found, the rule will default to Assets:Unknown.

In a scenario where a CSV file does not contain any account index, it is possible to specify the account value by setting the account property in the config file in use.

--- !Config
...
rules:
  account: UK0000001444555

Note that in the majority of situations, it is more intuitive to set the origin_account property on the rules block and skip this rule completely.

--- !Config
...
rules:
  origin_account: Assets:Jim:Current

This rule does set the origin and destination account for a given transaction, based on one or more values of a given CSV index.

As an example, let's take this CSV transaction - an ATM withdrawal from a bank.

01.12.2020;01.11.2020;Cash Withdrawal;Bank Of Holland;-100;EUR;0000001;UK0000001444555

When such a transaction is imported, we would like to set the origin account to Assets:Jim:Current and the destination account to Assets:Jim:Cash.

This is how the Set_Accounts rule can help:

- name: Set_Accounts
      from: Assets:Jim:Current
      to: Assets:Jim:Cash
      csv_index: 2
      csv_values: Cash Withdrawal

With the above rule configuration, we are pointing the rule to the index 2 (remember index count starts at 0) and if the value of the index matches Cash Withdrawal, then the origin and destination accounts are set on the Beancount transaction. This rule supports multiple csv_values, separated by ;. If any of the values matches, the rule is applied:

The CSV values are case-insensitive.

- name: Set_Accounts
      from: Assets:Jim:Current
      to: Assets:Jim:Cash
      csv_index: 2
      csv_values: Cash Withdrawal;Retiro de efectivo;Ritiro contanti

This rule can be used to ignore a transaction based on the value of the counterparty index.

- name: Ignore_By_Payee
      ignore_payee:
        - Mc Donald
        - Best Shoes

The counterparty names are case-insensitive.

This rule can be used to ignore a transaction based on the value of a specific CVS index.

- name: Ignore_By_StringAtPos
      ignore_string_at_pos: 
        - abc0102;4

The values are case-insensitive.

TODO

Download a CSV bank file from your bank and move it to a staging area. The script tries to find a file ending with .csv and starting with the provided String name and moves it to the target folder. If more than one file is found matching the criteria, the operation is aborted.

Script to use: bb_mover.py

Arguments:

-f: configuration file

Examples:

bb_mover.py -f ~/config/wells-fargo.yaml

Import the data from the CSV file into the ledger.

Script to use: bb_import.py

Arguments:

-f: configuration file

Examples:

bb_import.py -f ~/config/wells-fargo.yaml

Move the downloaded CSV file into an archive folder.

Script to use: bb_archive.py

Arguments:

-f: configuration file

Examples:

bb_archive.py -f ~/config/wells-fargo.yaml