[gfolds]

True but there is a threshold. If the problem is small enough, you can't beat an Excel spreadsheet's flexibility and low startup cost.

Having worked with a number of finance teams, the biggest problem is not when Excel is used as an analytics tool but as a financial database. Excel as a source of truth for financial data is truly terrifying. Even financial audits are still often handled with custom data pulls exchanged over Excel spreadsheets.

Shameless plug: that's why we ended up developing one of those SaaS tools (http://modfin.io). It lets finance teams map their source data to a proper, easily auditable accounting ledger so that they don't need to do their "magic" in Excel.

[allenz]

Why is it terrifying?

I am a programmer and I use csv/Excel as the single-entry financial database for a small healthcare business ($500k revenue). The accountant is fine with it. Filtering and pivot tables are a joy in Excel, and I can still run Python scripts whenever I need (mainly to auto-categorize bank transactions). Most other businesses around here use QuickBooks, which has a proper system of accounts, but then I can't play with the data.

[screature2]

I'm guessing because of 1) lack of built in logging, 2) lack of change control and 2) difficult to automate testing and verification (because of it's relatively unstructured nature).

Excel as the source of truth for a financial system is particularly scary to me b/c it's so easy for someone/anyone to change entries or miscalculate so it's very difficult for me to get confidence in its completeness and correctness.

Completely agree about analytics and scripting, but I'd utilize the CSV/Excel reports as point-in-time analytics that can be tied back a structured source of truth (or a source of truth I can hold liable, e.g. a bank or credit card statement).

[allenz]

Logging: I just save a copy of the spreadsheet every time I make a tax filing. Do I need anything more fine-grained?

Validation: I'm confident that I can catch duplicated rows and mistyped amounts because Excel formulas verify that the sum of all transactions for each account equals the account balance, and that transfers between accounts add up to $0. Do I need anything more?

Point-in-time doesn't work because I need to keep track of info that doesn't show up in the bank statements. I run a Python script that adds pretty-prints names and adds categories, and I also manually enter check details, category adjustments, business purpose of meals and flight tickets, etc.

I don't use a proper database because to do so I'd have to give up Excel's nice features (or worse, switch back and forth between Excel and the database).

[dkersten]

> Validation: I'm confident that I can catch duplicated rows and mistyped amounts because Excel formulas verify that the sum of all transactions for each account equals the account balance, and that transfers between accounts add up to $0. Do I need anything more?

I'm sure that the people who made these billion dollar mistakes were pretty confident too... https://www.cio.com/article/2438188/enterprise-software/eigh...

"The error occurred when the accountant omitted the minus sign on a net capital loss of $1.3 billion" (Fidelity)

"There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard." (Fannie Mae, $1.36 billion loss)

There's a bunch of lower (million dollar) losses listed in the linked page too.

[allenz]

I don't doubt that people have made mistakes involving spreadsheets. But these mistakes are not unique to spreadsheets. Switching to an enterprise system doesn't magically prevent data entry problems.[1] Similarly, you're as likely to implement an accounting function incorrectly in Excel as in SQL or Java.

I think that you need to show that Excel requires more data entry, or riskier data entry, or that Excel can't catch as many errors, etc. For now I'm not convinced.

[1] https://www.accountingweb.com/aa/auditing/human-errors-the-t...

[jbergens]

This doesn't scale well. If only one person edits the spreadsheet and that person knows how to save copies and make sure there is only one latest version it may work. If you give this to 10 people they will start making copies, saving those with different names ans save the copies on some server or on their own machines and after a while you don't have a single source of truth anymore. And you almost never have logging when many people edit copies of Excel documents.

[allenz]

That's true. Currently I handle scaling by automating nearly all the work. Most routine transactions are automatically categorized without manual input; I just skim the report. We modify 0-2 transactions/day, which is low enough for Dropbox to work well. This wouldn't work for a Fortune 500.

[rbg246]

How do you know the account balance total they you are validating your line items against is correct? Is that coming from another accounting system?

[allenz]

I validate account totals against the balance reported by the bank.

[rbg246]

Oh I see so you are only reporting on cash receipts, so presumably there is an accounts receivable ledger to track the invoices raised with your cash received etc. Thanks for the reply

[gfolds]

Apart from the generic question of the pros and cons of using Excel as a data store and how you solve issues around access control, concurrent access, backups, etc., financial databases have some additional requirements to be properly auditable. For example, one of the biggest differences is that the data should be immutable with only additions allowed. E.g. if you made an error and need to correct it (say you sent the wrong amount of money to someone or simply just had a bug), you should keep the record of the erroneous transaction and reverse it or book an adjustment.

You can try to institute the same controls over Excel, either directly in code or just implicitly in terms of how it's used, but they tend to break down pretty quickly with scale.