How to create financial reports with detailed information

Question

I'm working on an application where it has financial control for the incomes and expenses of the company. These informations can be controled by accounts (bank accounts - can be 1 or more), categories and subcategories.

The main objective is to display the current month financial information (or the same information on a specified period ex 15/02/2022 -> 15/04/2022). It would be something like the image below.

What I need to do now is to display, at the beggining of the period, the current balance of the account (or all accounts summed) and at the end the balance after sum/subtract the incomes/expenses of that month, see image below.

The problem I'm finding most difficult is to keep track of those balances whe the database has many entries, specially really old ones.

First approach: I was thinking about load all the information ever inserted on the database and start to sum/subtract until I get to the current period. Then I can create a more detailed list (for example, with balance for each day) without compromise performance. It doesn`t feel right, imagine I have financial data from 4-5 years ago. I would need to load thousands of rows from the database and do many math operations just to get the data from the current period.

Secont approach: After each time the user insert/edit/remove entries from the database I would update a table on the database where it would control the balance for each account. This seems to kind of solve the issue. But if I need to filter by categories/subcategories/accounts I'm not going to have the correct information. Also, this seems a bit complex to keep track of the correct balance with so many transactions. If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Third approach: After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months. From the financial point of view it`s not a "problem", since we can't change those informations anyway (unless human error detected later on). And then I can proceed with a static data from a period closes to "Today". The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

Database structure is pretty simple, see image below.

I have one more table that control installments, but it's not relevant to this part, since it's based on the finance_entries table.

Is there a better/proper approach to this type of data process? I'm really confused and don't know how to proceed without compromising the performance of the application.

Answer 1

The idea of your third approach is a step in the right direction:

Store the total of the account, for example, at the end of each month, as an additional datum.

Now to address your concerns:

After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months.

Technically, you don't have to "lock" anything completely (though from a legal or business perspective this might be required anyway). The important thing is that one can expect changes to older records to occur seldom. In the rare case of a late correction, there will be a recalculation necessary, which updates the totals of each month after the date in stake. In reality this shouldn't happen frequently. If you think this is necessary, warn your users that the following step might take a lot of time when they try to change transaction records from the past.

The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

"Filters" may affect the visible values in your report, but they don't affect the account's totals of each month. For example, the total of June 2022 isn't affected if you just make a report of the latest energy payments.

"Not a full month" is not an issue: you use the total of the last month and add or subtract income and payments after that date. Since the involved transactions include only the ones of a month at maximum, this is only a small fraction of all transactions for the account. This should keep your performance issues under control, and it works for the current date or for dates in the past as well. If a month's period is too long, you may switch to smaller periods, trading space for time.

If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Yes, but this is true with or without storing intermediate sums, so this is nothing but a straw man. Of course, using intermediate sums (and storing them) introduces a certain redundancy. This can either be an additional source of errors, or an additional means of detecting errors. You have to care for what to define as the leading data, what as derived data, and when to recalculate derived data from leading data.