4 ms·

For people that mention software like Ledger, Excel and Google Sheets to do their accounting, what do you do to make sure you don't accidentally delete/duplicat

For people that mention software like Ledger, Excel and Google Sheets to do their accounting, what do you do to make sure you don't accidentally delete/duplicate/edit a transaction and how do you import the latest transactions in an automated way? For example, I started using FreeAgent for taxes and the game changing feature for me is the (Open Banking based) transaction feeds where transactions are pulled daily from multiple bank accounts, where you're prompted that you need to explain/categorise each new transaction and the explanations + transactions get locked-in once they become part of a submitted tax return. You can easily change the category of a transaction in the UI but e.g. deleting or modifying a transaction amount will require multiple steps and for you to ignore loud warnings. The automated import takes care of avoiding duplicates and making sure nothing is missed compared to doing it manually. I've done accounting with just spreadsheets before and I hated how easy it would have been to make an accidental edit and not notice. Manually exporting and importing CSV files is fatiguing and error prone as well. I find this similar for invoicing software: you could keep a spreadsheet of who you need to invoice, if you sent it, if it's been paid, sending late reminders etc. but there's a lot of freedom to make mistakes and mistakes here are high impact. Invoicing software automates many of the steps, forces you to follow a set process and gives loud warnings if you deviate or make unusual edits.

barrucadu 6y ago
> what do you do to make sure you don't accidentally delete/duplicate/edit a transaction and how do you import the latest transactions in an automated way? I've never really had a problem with accidental deletions, duplications, or edits - it's a text file, and a transaction is at least 3 lines long (date and two postings), usually followed by a blank line. It's not like a CSV where the entire thing fits on a single line, and all the lines kind of blur together because they're so similar. I commit my ledger file to git weekly, but that's more of a backup than anything else. I manually enter all my transactions, so data import isn't really a problem I worry about (though I know ledger / hledger have ways of doing that).
Jtsummers 6y ago
> For people that mention software like Ledger, Excel and Google Sheets to do their accounting, what do you do to make sure you don't accidentally delete/duplicate/edit a transaction and how do you import the latest transactions in an automated way? I make sure by reconciling my accounts weekly (verifying them against the actual bank/brokerage accounts). I don't import automatically, I found that that led to laziness on my part. The value proposition of this style, for me, was better awareness of my money (not a vague, "I have enough" or I have around $xxxx in my checking account and $xxxxx in my brokerage account). However, there are tools to bring in transactions automatically and I did use them for a bit. There are tools that offer support for OFX, CSV, QIF and others listed on the linked page. I found OFX (not just with this, but in general) to be inconsistent across banks, and, when I did things "automatically", made it semi-automatic. I'd download the CSV of transactions and import them.
AlanYx 6y ago
One way you can make sure you don't inadvertently make changes is to use assertions, similar to how you'd use assertions in a programming language. For example, you can enter a balance assertion that's identical to the month-end balance on a paper statement you receive. If you somehow later mess up transactions somewhere so that the assertion becomes invalid, plain text systems will let you know. BTW, I think one of the main differences between Ledger/hledger and Beancount is whether assertions are interpreted per-file or globally.
nimih 6y ago
>For people that mention software like Ledger, Excel and Google Sheets to do their accounting, what do you do to make sure you don't accidentally delete/duplicate/edit a transaction and how do you import the latest transactions in an automated way? My general workflow is: 1. Pull tx data from bank websites, mostly via some hacky web browser automation scripts which log in and download the appropriate QFX/CSV. 2. Run `bean-extract [args] && bean-file [args]`, which processes the tx logs into a beancount file of newly seen transactions, and then move+renames the original files into an archive. 3. Copy entries from the generated beancount file to my permanent ledger via my favorite text editor. The processing rules I have (which get run during bean-extract) are comprehensive enough that this is mostly just copy-pasting large swaths of text to the appropriate section (e.g. "Balance Transfers", "Employment Salary & Benefits", "Everyday Expenses"), but I'll usually have a couple odd expenses which don't get automatically categorized. 4. Add a `balance` assertion for any accounts which have new transactions--this throws an error if my current calculated balance for that account doesn't match the assertion. 5. Run fava to check for errors and view any reports I'm interested in. 6. `git commit` the whole mess. In general, (3) is only adding new text so it's hard to make deletions/edits by accident, and (4)+(5) give a strong guard against duplicated/missed/incorrect entries. (6) lets me glance over the diff before committing, mainly to confirm it's an append-only operation (unless I changed the name of an account or something like that, which is both rare and can be done in a separate commit to keep things tidy). At the end of the day, the amount of supporting code I have for all the automation is around 1k lines, and it's been built up pretty slowly over the years--the initial automation got set up incrementally in order of how tedious I found various bank websites vs. the volume of transactions for the associated accounts, but at this point I can mostly just copy-and-paste my previous work on the rare occasion I need to set up a new account.
lasftew 6y ago
The advantage of text-based accounting is that files can be version controlled, just like software. I am using git and commit whenever I have a consistent state, as verified by (lots of) balance assertions. In some sense, git commits are "meta transactions" for my personal journal, leading from one consistent state to another consistent state.
hectormalot 6y ago
Versioning: I use git to version my ledger file. Preventing regressions: Every time I update with the latest transactions I close the last transaction with an assertion that ensures the balances are correct. (E.g. that the balance on checking is equal to the latest statement, or that outstanding VAT is 0 after paying taxes) I’ve been using ledger for my (small) business for about 5 years now. I’ve a script that calculates all the figures for my quarterly VAT submission by the categories that the tax authority requires (I use tags to label transactions). Importing transactions is still manual work, but given the low volumes it’s not a problem. I do find it frustrating that most banks are still so limited in providing API access to accounts. Invoices are a bit special, I can see outstanding amounts under Assets:Receivables, but haven’t found a good way to track this easily by invoice.

barrucadu 6y ago

> what do you do to make sure you don't accidentally delete/duplicate/edit a transaction and how do you import the latest transactions in an automated way? I've never really had a problem with accidental deletions, duplications, or edits - it's a text file, and a transaction is at least 3 lines long (date and two postings), usually followed by a blank line. It's not like a CSV where the entire thing fits on a single line, and all the lines kind of blur together because they're so similar. I commit my ledger file to git weekly, but that's more of a backup than anything else. I manually enter all my transactions, so data import isn't really a problem I worry about (though I know ledger / hledger have ways of doing that).

Jtsummers 6y ago

> For people that mention software like Ledger, Excel and Google Sheets to do their accounting, what do you do to make sure you don't accidentally delete/duplicate/edit a transaction and how do you import the latest transactions in an automated way? I make sure by reconciling my accounts weekly (verifying them against the actual bank/brokerage accounts). I don't import automatically, I found that that led to laziness on my part. The value proposition of this style, for me, was better awareness of my money (not a vague, "I have enough" or I have around $xxxx in my checking account and $xxxxx in my brokerage account). However, there are tools to bring in transactions automatically and I did use them for a bit. There are tools that offer support for OFX, CSV, QIF and others listed on the linked page. I found OFX (not just with this, but in general) to be inconsistent across banks, and, when I did things "automatically", made it semi-automatic. I'd download the CSV of transactions and import them.

AlanYx 6y ago

One way you can make sure you don't inadvertently make changes is to use assertions, similar to how you'd use assertions in a programming language. For example, you can enter a balance assertion that's identical to the month-end balance on a paper statement you receive. If you somehow later mess up transactions somewhere so that the assertion becomes invalid, plain text systems will let you know. BTW, I think one of the main differences between Ledger/hledger and Beancount is whether assertions are interpreted per-file or globally.

nimih 6y ago

>For people that mention software like Ledger, Excel and Google Sheets to do their accounting, what do you do to make sure you don't accidentally delete/duplicate/edit a transaction and how do you import the latest transactions in an automated way? My general workflow is: 1. Pull tx data from bank websites, mostly via some hacky web browser automation scripts which log in and download the appropriate QFX/CSV. 2. Run `bean-extract [args] && bean-file [args]`, which processes the tx logs into a beancount file of newly seen transactions, and then move+renames the original files into an archive. 3. Copy entries from the generated beancount file to my permanent ledger via my favorite text editor. The processing rules I have (which get run during bean-extract) are comprehensive enough that this is mostly just copy-pasting large swaths of text to the appropriate section (e.g. "Balance Transfers", "Employment Salary & Benefits", "Everyday Expenses"), but I'll usually have a couple odd expenses which don't get automatically categorized. 4. Add a `balance` assertion for any accounts which have new transactions--this throws an error if my current calculated balance for that account doesn't match the assertion. 5. Run fava to check for errors and view any reports I'm interested in. 6. `git commit` the whole mess. In general, (3) is only adding new text so it's hard to make deletions/edits by accident, and (4)+(5) give a strong guard against duplicated/missed/incorrect entries. (6) lets me glance over the diff before committing, mainly to confirm it's an append-only operation (unless I changed the name of an account or something like that, which is both rare and can be done in a separate commit to keep things tidy). At the end of the day, the amount of supporting code I have for all the automation is around 1k lines, and it's been built up pretty slowly over the years--the initial automation got set up incrementally in order of how tedious I found various bank websites vs. the volume of transactions for the associated accounts, but at this point I can mostly just copy-and-paste my previous work on the rare occasion I need to set up a new account.

lasftew 6y ago

The advantage of text-based accounting is that files can be version controlled, just like software. I am using git and commit whenever I have a consistent state, as verified by (lots of) balance assertions. In some sense, git commits are "meta transactions" for my personal journal, leading from one consistent state to another consistent state.

hectormalot 6y ago

Versioning: I use git to version my ledger file. Preventing regressions: Every time I update with the latest transactions I close the last transaction with an assertion that ensures the balances are correct. (E.g. that the balance on checking is equal to the latest statement, or that outstanding VAT is 0 after paying taxes) I’ve been using ledger for my (small) business for about 5 years now. I’ve a script that calculates all the figures for my quarterly VAT submission by the categories that the tax authority requires (I use tags to label transactions). Importing transactions is still manual work, but given the low volumes it’s not a problem. I do find it frustrating that most banks are still so limited in providing API access to accounts. Invoices are a bit special, I can see outstanding amounts under Assets:Receivables, but haven’t found a good way to track this easily by invoice.