openerp-expert-accounting team mailing list archive

["Borja López Soilán (Pexego)" <borjals@xxxxxxxxx>]

Hi Ferdinand,

I'm not sure if I understand what are you trying to accomplish: Are you 
trying to have the period sums of the accounts precalculated so when 
doing the fiscal year closing (or printing balance reports) only the 
last period values have to be considered? Or do you mean than in Austria 
you do a sort of period-closing?

I think I better explain how spanish companies are required to work (by 
the spanish general accounting plan), and how most of the programs I know :)


     How do we work on Spain

First of all, in Spain we have a complex minimal tree-like account chart 
(take a look here: http://www.gabilos.com/webcontable/indexn.htm). Small 
and medium business are allowed to use a simpler chart (mostly removes 
the "8" and "9" accounts). This chart of accounts is required (you can't 
delete or modify these accounts, but you may create new sub-accounts) by 
Hacienda (the spanish Treasury Department) as it's used to provide 
several official reports.


       Period operations

Every period (quarterly for S&M business, monthly for big ones) we must 
report to Hacienda (Treasury) this official reports (there are more, but 
these are the important ones):

   * Report of the taxes by tax code (i.e. sum amounts of the taxes and
     base amount at 16% rate, at 7% rate, etc...).

     /Also called "303 model". This report
     (//https://www2.agenciatributaria.gob.es/es13/h/ie93030b.html)//
     currently has to be filled in by hand on OpenERP >_< with the
     values taken from the "taxes report" from the account module
     (though it doesn't seem to work for me: too slow and the RML
     doesn't seem to be properly parsed, instead of the amounts it
     spurts things like this:   "[[ [[  [[formatLang(o['debit'])
     formatLang(o['credit'])]] formatLang(o['tax_amount']...").
     /
   * Report of all the invoices received and sent with detailed taxes.

     /Also called "340 model". A (non-official) report with mostly the
     same info can currently be printed with the
     account_financial_report module. And there is a
     l10n_ES_aeat_mod340 being developed on the spanish localization
     (though it is not currently usable) to generate the official
     report file (an ASCII file with fixed length records).

     /
   * Report of retained taxes to your employees (part of the employee
     salary must be retained and delivered to the Treasury every period).

     /Also called "110 model". It has to be filled by hand on OpenERP// :(/

   * Report of profits from real state rentings

     /Also called "115 model". Currently there is no way on OpenERP to
     record or tell apart 'real state renting' invoices :( so it is
     obvious that this report must be generated by hand.../ :(

That means that (after) every period (when we have already received all 
the invoices of the period) we need to calculate the sums of the tax 
accounts on that period ("477" and "472" accounts) and leave them balanced.

(Notice that we don't calculate the P&L or the result of the fiscal year 
or anything else here: just taxes)


       End of year (fiscal year closing) operations

After the end of year (actually in March) some yearly reports must be 
sent to Hacienda (Treasury) or the Registro Mercantil (Commercial Registry):

   * P&L: Detailed report of the profit and losses by concept (not by
     account!, a concept is a sum of the values of several accounts) of
     the closed fiscal year compared to the previous fiscal year.

     /This report can be created using the account_balance_reporting
     module (that serves as a report engine) and the
     l10n_ES_account_balance_report module (the report templates for
     Spain). It gets the info from the "6" and "7" accounts.

     /
   * Net P&L: Detailed report of the profit and losses on the 'net'
     accounts by concept. Similar to the P&L report, but only for big
     companies with "8" an "9" accounts.

     /This report can be created using the account_balance_reporting
     module (that serves as a report engine) and the
     l10n_ES_account_balance_report module (the report templates for
     Spain). It gets the info from the "8" and "9" accounts./

   * Balance: Detailed report of the balance of all the accounting by
     concept (for example there is a concept "suppliers" that is the
     sum of the 400, 401, 403, 404 and 405 accounts minus the 406
     account) compared to the previous fiscal year. This report must be
     done after computing the P&L and, as the report name states, all
     the concepts should be balanced.

     /Again, this report can be created using the
     account_balance_reporting module (that serves as a report engine)
     and the l10n_ES_account_balance_report module (the report
     templates for Spain). It gets the info from the "1", "2", "3", "4"
     and "5 " accounts.

     /
   * The Memory: A complex report with several subreports that compare
     concepts with the previous fiscal years, and that details
     everything worth to notice about how the accounting was performed
     (for example if you change an account code you will have to
     explain here why).

     /This one is currently not supported on OpenERP, but I doubt that
     small business generate it by themselves (they usually have an
     external accounting manager/adviser)./

   * A report of some of the operations (sales, purchases, received
     payments) with partners. For example we must report all the
     parners that invoiced us (or that we invoiced them) more than
     3005.06 euro, all the partners that payed us more than 6000 euro
     in cash, etc.

     /Also known as "347 model". This report may be generated using the
     l10n_ES_aeat_mod347 module from the spanish localization.
     /

   * Summaries (complete fiscal year versions) of some of the "by
     period" reports.

     /For example the// "390 model" is a //summary// of the "303
     models", the// "190 model" is summary of the "110 models" of the
     fiscal year, and the "193 model" is a //summary// of the "115
     models"//./


There are more 'models' and reports (I think the accounting in Spain is 
too complex), but those are the important ones, and should be enough to 
give the accounting experts a good idea of why do the things we do.


So, this is what we do when closing the fiscal year (after 'end of year 
operations' like the inventory, the assets amortization/depreciation...):

- Generate the P&L report.
- Do the P&L "regularization": Sum all expenses and purchases accounts 
(those that start with "6") with the profit and sales accounts (those 
starting with "7") and store the 'profit of the fiscal year' into 
another account ("129") that would be used later on one of the concepts 
of the 'Balance' report.

- Generate the Net P&L report (if needed).
- Do the Net P&L "regularizations" (if needed): Sum some net expense 
accounts (those that start with "8") and net income accounts (those 
starting with "9") and store that sums on several accounts 
("800"+"801"+"802"+"803" into "133"; "810" + "811" + "812" into "134"; 
"900" into "135").

- Generate the 'Balance' report.
- 'Close' all the accounts: Sum all the balances of all the accounts 
(but the "6", "7", "8" and "9" accounts that already have been balanced) 
in a single account move (without using a centralized counterpart). If 
there are no errors in the accounting, the move should be balanced and 
all the accounts should have a balance of 0 after this operation.

If you think about how the accounting was done by hand (with pen and 
paper) this last move is indispensable if you want to assert that the 
accounting has no errors.

And this is what we do when opening the next fiscal year:

- 'Open' all the accounts: Make a move, inverse to the closing one, to 
restore the balance of the accounts.

*Notice this:* One account with debit "3000" and credit "1000" (balance 
2000) will be closed with a debit "0" credit "2000" entry, and will be 
reopened on the next year with debit "2000" credit "0". _Currently 
OpenERP always shows the cumulative debit/credit from the beginning of 
the times, so it will display debit "5000" and credit "3000" instead of 
the expected D "2000" C "0" :(_


     What Spanish programs do to calculate some reports faster

All the accounting programs I know (ContaPlus, ContaSol, ClassicConta, 
Eurowin, Primavera) do the same: They have a table with the 
precalculated debit, credit, balance and total amounts per month!
Every time you create or modify an account move, that table would be 
updated (sometimes using SQL triggers to ensure consistency).

When you want to print a balance like report (a general ledger for 
example) for a given month, or simply want to see the current 
credit/debit of the account, the program only has to look at the values 
of that summary table (a single and simple query!) instead of 
recalculating the sums of each account from the beginning of time (that 
is what OpenERP does).

Even Postgres (which is quicker than I thought when doing this 
aggregates) has a hard time when an OpenERP user tries to browse a chart 
of accounts with thousand of accounts. We have a client that has like 
3000 clients itself; opening the 'clients' branch in the account tree 
view brings Postgres and OpenERP to his knees, cause it just 
recalculates the debit and credit of 3000 accounts (Postgres would have 
to sum all the account move lines of those 3000 accounts) every time.

I think we could do something similar in OpenERP:

   * Have precalculated debit/credit/balance totals per account and
     period (i.e. an account_account_totals table with account_id,
     period_id, period_debit, period_credit, period_balance,
     period_draft_debit, period_draft_credit, period_draft_balance,
     total_debit, total_credit, total_balance, total_draft_debit,
     total_draft_credit and total_draft_balance fields). 
   * Use postgres triggers to update this totals (that would ensure
     ACID) each time a account_move_line is created/updated/deleted.
   * Use this precalculated totals on the account.account __compute
     method (that is used on the debit, credit, balance function fields).

That would pretty much speed up all the accounting in OpenERP. Just 
think about this: 

Every time we want to get the current balance of some accounts OpenERP 
does a query like this:

   * "SELECT l.account_id as id, COALESCE(SUM(l.debit),0) -
     COALESCE(SUM(l.credit), 0) as balance FROM account_move_line l
     WHERE l.account_id IN (%s)  GROUP BY l.account_id"

With the precalculated totals table it would be a much (several orders 
of magnitude*) quicker query:

   * "SELECT t.account_id as id, t.total_balance FROM
     account_account_totals t WHERE t.account_id IN (%s) AND
     t.period_id = %s" 


/(*) We turn the O(n*m) complexity of the first query into O(n)./



     Summary

   * The way OpenERP calculates the credit/debit/balance is toooooooooo
     slow, and can be improved a lot by using precalculated sums by period.
   * The way OpenERP calculates the current credit/debit (taking into
     account all the fiscal years instead of the current one) may not
     be valid (or confortable) on all the countries. We should add a
     (configuration) option to show only the current fiscal year
     amounts (using precalculated sums it would be much easier to
     implement).
   * The "close fiscal year" wizard of OpenERP is not valid on
     countries that need to regularize, close and reopen the accounting. 
   * OpenERP lacks lots of accounting/financial reports.


Well, I hope all this rants are useful for the OpenERP accounting experts :)
Best Regards.

Ferdinand Gassauer wrote:
> Hello !
>
> I just would like to ask if Spain and other countries really require 
> account move lines for closing a fiscal year
>
> For some (also very big) companies here in Austria we do not create 
> closing moves for each account but on a permanent basis we calculate 
> period sums
>
> * for the fiscal year of the move
>
> ** P&L account for all P&L moves - showing the result of the fy
>
> ** Balance account - for all P&L moves - showing the result of the fy
>
> * for all following fiscal years (!) : balance carried forward - which 
> is hold in a special period '00'
>
> the big performance advantage is that for all period orientated 
> reports and views no detail moves have to be accessed.
>
> We calculate these sums when a move state is set to 'done'
>
> I have created a proof of concept but working module for the balance 
> carried forward (with PSQL triggers) which I can provide or can be 
> seen here
>
> Demo 
> <https://openerp.chricar.at/login?user=demoe&password=demoe&db=demo_ger_2> 
>
>
> look at
>
> Finance / Charts / Accounts with postings
>
> look at 2010 - period 201000 holds the balance carried forward
>
> Please your opinion
--
Borja López Soilán
borjals@xxxxxxxxx

Pexego Sistemas Informáticos S.L.
Avenida de Magoi 66 - 27002 Lugo (España)
Tel./Fax 982801517
http://www.pexego.es