hledger-flow example

fields.rules

fields type, date, amount1, id, payee, memo, payee1, memo1, bal
date-format %Y-%m-%d
description %payee,%memo

code %id

currency1 GBP


account2 income:unknown

if %amount1 ^-
    account2 expenses:unknown

if %type BALANCE
    balance1 %bal
    description balance assertion
    account2

gistfile1.txt

.
├── Makefile
├── import
│   ├── 2023-include.journal
│   ├── all-years.journal
│   ├── fields.rules
│   └── me
│       ├── 2023-include.journal
│       ├── _manual_
│       │   └── 2023
│       │       └── post-import.journal
│       ├── all-years.journal
│       └── hsbc
│           ├── 2023-include.journal
│           ├── accountnumber
│           │   ├── 1-in
│           │   │   └── 2023
│           │   │       └── 2023-01-01_2023-10-28.ofx
│           │   ├── 2-preprocessed
│           │   │   └── 2023
│           │   │       └── 2023-01-01_2023-10-28.csv
│           │   ├── 2023-include.journal
│           │   ├── 3-journal
│           │   │   └── 2023
│           │   │       └── 2023-01-01_2023-10-28.journal
│           │   ├── all-years.journal
│           │   ├── hsbc-accountnumber.rules
│           │   └── preprocess
│           ├── accountnumber
│           │   ├── hsbc-accountnumber.rules
│           │   └── preprocess -> ../preprocess.py
│           ├── all-years.journal
│           ├── preprocess.py
│           └── rules.psv
└── resolve.sh

hsbc-accountnumber.rules

include ../../../fields.rules
include ../rules.psv
account1 assets:current:me:hsbc

Makefile

.PHONY: all clean
all: all-years.journal

clean:
	rm -f all-years.journal
	find import -name _manual_ -prune -o -name 1-in -prune -o -type f \( -name '*.journal' -o -name '*.csv' \) -print0 | xargs -0 rm -f

SOURCES=directives.journal
SOURCES+=$(wildcard import/me/_manual_/*/*.journal)
SOURCES+=$(wildcard prices/*/*.journal)
SOURCES+=$(wildcard import/*.rules)
SOURCES+=$(wildcard import/*/*.rules)
SOURCES+=$(wildcard import/*/*/*.rules)
SOURCES+=$(wildcard import/*/*/*/*.rules)
SOURCES+=$(wildcard import/*/*/*/construct)
SOURCES+=$(wildcard import/*/*/*/preprocess)
SOURCES+=$(wildcard import/*/*/*/*.awk)
SOURCES+=$(wildcard import/*/*/*/1-in/*/*)

all-years.journal: $(SOURCES)
	poetry run hledger-flow import $(pwd)

preprocess.py

#!/usr/bin/env python3
import codecs
import csv
import os
import sys

from ofxparse import AccountType, OfxParser

input = sys.argv[1]
output = sys.argv[2]
bankname = sys.argv[3]
accountname = sys.argv[4]
ownername = sys.argv[5]

bankrouting = {
    "hsbc": [
        # TODO: fill in with the account numbers that you expect to see in your OFX files
    ]
}

# TODO: fill in with the account number for the credit card,
# this one doesn't have a routing_number for me in the OFX
creditcardname = ""

with codecs.open(input) as fin:
    ofx = OfxParser.parse(fin)
    account = ofx.account
    if account.account_id[-4:] != accountname:
        print(f"Account name mismatch: {account.account_id} and {accountname}")
        sys.exit(1)
    if account.routing_number == "" and accountname != creditcardname:
        if int(account.routing_number) not in bankrouting[bankname]:
            print(f"Unknown bank routing number: {account.routing_number}")
            sys.exit(2)
    with open(output, "w", newline="") as fout:
        writer = csv.writer(fout)
        statement = account.statement
        for tx in statement.transactions:
            memo1 = tx.memo.split()[0] if tx.memo else ""
            payee1 = tx.payee.split()[0] if tx.payee else ""
            writer.writerow(
                [
                    tx.type,
                    tx.date.date(),
                    tx.amount,
                    tx.id,
                    tx.payee,
                    tx.memo,
                    payee1,
                    memo1,
                    None,
                ]
            )
        bal = statement.balance
        # there is a date next to balance, but that is not correct, except for CC
        # and for CC the date is usually outside of the reported range (e.g. future year)
        # fix this up
        if AccountType.CreditCard != account.type:
            writer.writerow(
                [
                    "BALANCE",
                    statement.end_date.date(),
                    0,
                    None,
                    None,
                    None,
                    None,
                    None,
                    bal,
                ]
            )

resolve.sh

#!/bin/bash
# based on resolve.sh from https://github.com/adept/full-fledged-hledger
set -eu -o pipefail

hledger -f all-years.journal reg unknown -O csv|csvtool col 4 -|sort|uniq -c|sort -g|tail

while true ; do
    # Choose one of the unknown transactions' description
    description_account=$(hledger -f all-years.journal print -I unknown \
                      | hledger -f - register -I -O csv \
                      | grep -v ":unknown" \
                      | grep -v -f <(cat ./import/me/*/rules.psv | cut -d'|' -f1 ) \
                      | csvtool col -u '|' 2,4-6 -  \
                      | sk --header="Choose transaction" --tac -d '\|' --header-lines=1 --nth 2 \
                      | csvtool col -t '|' 2,3 - \
                      | sed -e 's/[*]/./')

    # Description_account is "<description>,<source account that money came from>"
    # We can use account to figure out which rules file we need to modify
    account=$(echo "${description_account}" | csvtool col 2 -)
    description=$(echo "${description_account}" | csvtool col 1 - | sed -e 's/^"//' -e 's/"$//')
    case "${account}" in
        assets:*:me:hsbc*|liabilities:creditcard:me:hsbc)
            dir="./import/me/hsbc/" ;;
        expenses:amazon)
            dir="./import/me/amazon/" ;;
        *)
            echo "Unknown source dir for ${account}"; exit 1 ;;
    esac   

    # Description as it is often can't be used as regexp.
    # It could contain special character or can match too many lines.
    # This step allow us to fine-tune it, by seeing what it matches in real time
    regexp=$(sk --header="Fine-tune regexp. Searching in ${dir}/**/*.rules and 2-preprocessed" \
                --cmd-query="${description}" --print-cmd --ansi -i \
                --bind 'ctrl-d:delete-char' \
                -c "rg --no-filename -i --color=always --line-number \"{}\" $(find ${dir} -name '*.rules' | paste -s -d' ') ${dir}/rules.psv $(find ${dir} -name 2-preprocessed | paste -s -d' ')"| head -n1)

    # Now lets choose account
    # or just enter new one
    hledger -f all-years.journal accounts >tmp_accounts.txt
    account=$( (sk --header="Choose account (prepend : to inhibit selection)" --print-cmd --ansi -i \
                  -c "rg --color=always --line-number \"{}\" tmp_accounts.txt" || true) | tail -n1 | sed -e 's/^[0-9]*://')

    cat ${dir}/rules.psv | cut -d '|' -f3 | sort -u > tmp_comments.txt

    # ... and comment. Comment could be either selected from existing comments
    # or just entered. When entered comment is a substring match of one of the existing comments,
    # you can prepend your comment with ":" to prevent the match from being selected
    comment=$( (sk --header="Enter comment (prepend : to inhibit selection)" --print-cmd --ansi -i \
                  -c "rg --color=always --line-number \"{}\" tmp_comments.txt" || true) | tail -n1 | sed -e 's/^[0-9]*://')

    echo "${regexp}|${account}|${comment}" >> "${dir}/rules.psv"
    poetry run make
done

rules.psv

if|account2|comment2
CASH IN AT|assets:cash|