Our client works with an accounting program, but exports the accounting data to Excel, to be able to order at their convenience the presentation of the accounting accounts, in order to carry out the budgetary control, according to their criteria.
This task must be performed every month. The drawback is in the distribution of the exported data and the volume of data, which makes the task very laborious. Let’s take it one step at a time.
The accounting program, when exporting the data, presents in the Excel sheet, separate and unconnected, unordered lines of data. The first thing to do is to make a macro of relative references, which groups the data lines and distributes them correctly.
The data can then be integrated into a prepared template, with the presentation and distribution of the accounting accounts, according to the client’s criteria.
We show an example of data obtained in the export of the accounting program:
The image shows the accounting accounts in the first column and the literal of each account in the next column. But the accounts and their literals are on one line and the corresponding values are on a different line (below). In addition, the numerical values are shifted one column to the right and the value corresponding to the last column is in the same column, but another line below it. The function of the macro is to put each value in its corresponding line and column, and to eliminate the blank lines that remain after moving the values. In this way, the data is correctly grouped, making them in consecutive columns and rows.
We show the result, the presentation of the data, after using the macro:
With the data ordered, we can now use the template prepared in another Excel book, to carry out the Budget Control. This template uses two sheets, one to capture the data already ordered, and another to present the Budgetary Control.
In the data capture sheet we use formulas that establish a link with the initial sheet and add the values according to the accounting account. We show an image:
Once we have the linked data, we can move to the Budgetary Control sheet, where we will find all quantities in place, according to the customer’s criteria and the formulas entered. We show a part of the sheet:
Application made with Excel. To use this application you need to install the MS Excel program. The application is made in Catalan language. Note: The data inserted in the images are fictitious.
