-
Notifications
You must be signed in to change notification settings - Fork 34
Maintaining CSV rules
Associated directory: 06-maintaining-lots-of-csv-rules
Eventually you will reach a point where your rulefile is so big that every time you add a new rule, you are faced with a choice: either search for an existing rule to add a new pattern to, or create a new rule, writing the boilerplate of:
if
PATTERN
account2 ...
, which is rather wasteful and quite a lot to type.
My solution to this was to write rules in a simple pipe-separated file, and have a rule that preprocesses them into the format suitable for hledger.
The file would have the following format:
PATTERN|account2|comment
, where the comment could be empty and tailing separator could be omitted. Lines that begin with #
are
ignored. The pipe was chosen as a separator because it is very unlikely to appear in either pattern, account name or comment.
For example:
$ cat import/lloyds/rules.psv
INTEREST|income:interest
EMPLOYER INC|income:employer
TRANSFER|assets:Lloyds:transfers
WAITROSE|expenses:groceries
OASIS COFFEE|expenses:coffee
AVIVA|assets:pension:aviva
An account could also be left blank, in which case generated rule will just add a comment. This could be used to augment more general rule, for example, you can annotate a transaction on a particular date:
2017-10-11.*EMPLOYER INC||per diem for the trip to New York
File in this format that could be easily edited or manipulated in Excel/OpenOffice.
If you adopt a convention of calling this file import/<source>/rules.psv
, and calling generated file import/<source>/generated.rules
then it is possible to write a build rule which would automatically convert this file to hledger syntax whenever necessary and redo CSV-to-journal conversion when you add or modify rules.
Example above would be converted to these rules:
$ cat import/lloyds/generated.rules
if
INTEREST
account2 income:interest
if
EMPLOYER INC
account2 income:employer
if
WAITROSE
account2 expenses:groceries
if
AVIVA
account2 assets:pension:aviva
if
TRANSFER
account2 assets:Lloyds:transfers
if
OASIS COFFEE
account2 expenses:coffee
File generated.rules
could now be include
d into your main CSV rules file:
$ cat import/lloyds/lloyds.rules
fields date,code,sortcode,account1,description,amount1-out,amount1-in,balance1
skip 1
date-format %d/%m/%Y
currency1 £
account2 expenses:unknown
include generated.rules
See the changes in 06-maintaining-lots-of-csv-rules.
Now that importing basic statements should not be an issue anymore, we can tackle more complex subjects, such as tracking investments.
- Key principles and practices
- Getting started
- Getting data in
- Getting full history of the account
- Adding more accounts
- Creating CSV import rules
- Maintaining CSV rules
- Investments - easy approach
- Mortgages
- Remortgage
- Foreign currency
- Sorting unknowns
- File-specific CSV rules
- Tax returns
- Speeding things up
- Tracking commodity lots manually
- Fetching prices automatically
- ChangeLog