Hacker News Reader

Another answer:

> 1. Entries in one month's CSV file may be repeated in the previous or following ... Is your system robust against it?

People use various strategies for this. You can treat the data as partitioned (eg in months), or as a continuous stream. In general you can't rely on unique ids, including ones generated from the data (because identical transaction records are common).

hledger has a simple built in method, which works pretty well for most people: it remembers the latest transaction date processed (for each input source). https://hledger.org/hledger.html#date-skipping

> 2. Credit card: the total amount for a month is one single entry in the bank's CSV file, while a separate CSV file contains all the details. Do you rely on accounts to handle this indirect flow, e.g. one transaction of 1000$ from checking to cc, based on the single entry of the bank's CSV, then several transactions from cc to the various expense categories, based on the details CSV, and checking that the cc account has a zero balance?

Yes, purchases made with a credit card and paying off the credit card balance are all separate transactions, so the most standard way is record them as such - either manually, or by downloading/converting/importing the data from both accounts. And for extra assurance, assert that the credit card's balance is zero after payoff.

When downloading from both accounts, you get the payoff transaction in both downloads; one of them can be ignored by rule or manually, or both can be recorded with a dummy transfer account keeping things balanced.

If you don't care enough about the detailed credit card purchases, you'd just record the checking data.

> 3. Some utility companies bill every 2 or 3 months. This makes monthly stats meaningless...

One workaround is to split the expense posting into several expense postings, each with its own date (still keeping them within the one journal entry). A more strictly correct one is to add transactions and accounts like in evrimoztamur's answer. Most often I'll just use hledger's -MA flags (--monthly --average).

> One workaround is to split the expense posting into several expense postings, each with its own date.

Can a rule expeess such calculated fields like "set virtual date at 3 month before transaction date"?

> A more strictly correct one is to add transactions and accounts like in evrimoztamur's answer.

That's good for forecasting, but I don't see how to write automatic rules that ensure the accrued amount = billed amount.

> Can a rule expeess such calculated fields like "set virtual date at 3 month before transaction date"?

hledger's CSV rules can't do this, no. You'd need to either do that manually, or write a custom conversion script (or a custom preprocessor that modifies the CSV before hledger sees it).

> That's good for forecasting, but I don't see how to write automatic rules that ensure the accrued amount = billed amount.

Similar answer, if you wanted to generate those verbose entries from a single CSV record, it would need some manual entry or a custom preprocessor script. hledger's CSV rules will only generate 0 or 1 transactions per CSV record.

What about the --average option ?

With 3, this is called an accrued expense. You book the estimated expenses of that specific month not against your accounts payable (to later reconcile against the invoice, also in accounts receivable) but against an accrued expense account. Once you receive the invoice, you also book it against the accrued expense account to reconcile it there. This way, your bookings might look like:

1 May 2025 Debit 100.00 Utilities Credit 100.00 Accrued (utility) expenses

1 May 2025 Pre-booking May usage Debit 100.00 Utilities Credit 100.00 Accrued (utility) expenses

1 Jun 2025 Pre-booking July usage Debit 100.00 Utilities Credit 100.00 Accrued (utility) expenses

30 Jun 2025 Received the invoice for May/Jun 2025 Credit 200.00 Accounts payable Debit 200.00 Accrued (utility) expenses

6 Jul 2026 Paying off the invoice by deducting cash from the bank account and removing the payable liability Credit 200.00 Cash Debit 200.00 Accounts payable

More info at https://en.m.wikipedia.org/wiki/Accrual#Accruals_in_accounti...

And here's that (approximately) in h/ledger-ese:

  2025-05-01 Pre-booking May usage
      Expenses:Utilities                           100.00
      Liabilities:Accrued (utility) expenses      -100.00
  
  2025-06-01 Pre-booking June usage
      Expenses:Utilities                           100.00
      Liabilities:Accrued (utility) expenses      -100.00
  
  2025-06-30 Received the invoice for May/Jun 2025
      Liabilities:Accrued (utility) expenses       200.00
      Liabilities:Accounts payable                -200.00
  
  2025-07-06 Paying off the invoice by deducting cash from the bank account and removing the payable liability
      Liabilities:Accounts payable                 200.00
      Assets:Cash                                 -200.00

> Entries in one month's CSV file may be repeated in the previous or following month's CSV file

I am aware of this problem. The approach in the blog post definitely helps with that because the data is imported fresh every time again. So you could export your bank data with overlapping dates and do something like this prior to importing:

    cat *.csv | sort | uniq >clean.csv

Great questions.

1. This is a familiar problem and it is where I spend the majority of time. If a bank provides a unique transaction ID the it would be a simple matter of code; however, none of mine do. What I do when I spot problems is run `hledger areg <account> --begin=<last month> --end=<this month>` and compare with the CSV. Opening the output of that command + this months CSV in vim using vertical split (`vsp`) and scrollbind (`scrollbind`) so each line scrolls together works well and is pretty quick. It's still a problem and a pain.

2. This is solved in my system in a robust way (I believe) with a "virtual account." I do most of my spending on my credit card. Then every month I pay off the card from my checking. To represent this I need to see:

- liabilities:credit with any balance I carry

- expenses should show a transfer from liabilities:credit to the correct expense category/account

- I should avoid being "double charged"; i.e., when I pay off liabilities:credit it should NOT be an expense.

I've set it up like this:

- rules/debit/journal.rules: if (payment to credit) then account2 assets:transfer:credit

- rules/credit/journal.rules: if (payment from debit) then account2 assets:transfer:credit

This becomes a way to spot problems. If assets:transfer:credit ever has a balance then something may be awry and you should spend some time digging. I do the same thing for Amazon. Each Amazon order is for multiple expenses. I have assets:transfer:amazon. This is how I learned the the $0.29 Colorado retail delivery fee is not universally applied to orders from Amazon—that was a frustrating journey!

3. I haven't worried about this. Looks like other good responses in this thread.

> 1. This is a familiar problem and it is where I spend the majority of time. If a bank provides a unique transaction ID the it would be a simple matter of code; however, none of mine do.

This is an annoying problem I've had to deal with also (banks have some unique ID somewhere, why not provide it?).

I create my own unique ID to the extent that it's possible. Some txns have the exact same info but are 2 sep txns, requires a bit of messy logic.

> 2. Credit card: the total amount for a month is one single entry in the bank's CSV file, while a separate CSV file contains all the details. Do you rely on accounts to handle this indirect flow, e.g. one transaction of 1000$ from checking to cc, based on the single entry of the bank's CSV, then several transactions from cc to the various expense categories, based on the details CSV, and checking that the cc account has a zero balance?

Not OP but this is certainly how I've always done it. A bank account and a credit card are very much two separate accounts, they may even be with different institutions and one represents an asset whereas the other generally represents a liability. So in an accounting context it makes sense to treat them separately.

Same approach for me and I think I learnt it from using YNAB, I struggled a bit with the mental model at first but after it clicked it made a lot more sense to treat a credit card as a separate account.

My approach for number 3 is to look at previous months and escrow money for it every month, and then pay it off once the bill comes due. From the accounting side you only see the monthly cost of the bill, even though it looks big on the bank statement.

> Some utility companies bill every 2 or 3 months. This makes monthly stats meaningless (why is September so high compared to August? did I spent too much or is it the effect of the phone bill of the previous quarter?).

According to Claude 3.5, ledger/hledger handles that situation like this:

    ; Automatically allocate $60 quarterly phone bill across 3 months
    ~ monthly
      Expenses:Phone          $20
      Liabilities:Accrued    $-20
    
    ; When actual bill comes
    2023-09-15 Phone Company
      Liabilities:Accrued     $60
      Assets:Bank            $-60

This approach:

1. Accrues monthly portions ($20) to a liability account

2. When the real bill arrives, clears the accrued amount

I think that's a miss for Claude, this doesn't look right at all. The accrual account is an okay solution, but the syntax is wrong! That syntax is only used for budgeting and forecasting.

I think the solution is effective dates, there is an example pretty close to this scenario in the manual:

https://ledger-cli.org/doc/ledger3.html#Effective-Dates

I don't think it's terribly wrong. It will generate the desired entries (internally) and proper reports, if you add the appropriate option (--forecast=... for hledger, something else for Ledger). Or you could use it once to generate permanent journal entries, in your main journal or a forecast journal:

  hledger print --forecast='2024-11-01..+3 months' >>$LEDGER_FILE

Effective dates are another solution, but IMHO a misfeature, I mention this for newcomers: https://hledger.org/hledger.html#secondary-dates

Thank you for pointing this out, will check your link instead.