Plugin: Import Budget

• Maintainer: Logilite Technologies (India), Adaxa (Australia)
• Creator: Adaxa (Australia), Logilite Technologies (India)
• Copyright: Adaxa (Australia), Logilite Technologies (India)
• Source: github
• Version: Master branch tested with 5.1 and 7.1
• License: GPLv2 - Free as in Freedom not Free Lunch
• Price: Free for usage
• Issue tracker: Issues
• Credit: Original content written by Steven Sacket from Adaxa.

If you want to provide additional comments, please use the Discussion page.

What it Does.

This plug-in simplifies the importation of a budget that has been prepared in a spreadsheet. It can also be used for importing financial history from a superseded accounting system as part of the migration to iDempiere.

How it Works.

A new import table is added that has columns as shown in the screenshot of the ‘Import Budget’ window below.

An Import Loader Format must be created that contains (a) as many of the Accounting Schema Dimensions as you wish to post to (b) $ amount columns for Period_0 to Period_11 (c) Qty columns for Period_0 to Period_11 [Note: you can include values for (say) 6 months and tell the system to import only 6 columns]

A new process is added that transfers the data from the ‘Import Budget’ table to a GL Journal Batch created by the process.

The GL Journal Batch is then Completed to import the Budget.

The Import Budget window.

A new “Import Budget” process is added that behaves like all the other Import processes inherited from Compiere.

An Import Loader Format must be defined (such as below)

note that extra lines exist but are not shown for periods 9 to 11 for money amounts (the first period is Period_0) for monetary values and 12 columns of Qty for period_0 to period_11.

The Import Loader Format can be defined at Client level by copying from the one defined at System level and modifying as required.

The Budget Spreadsheet to be imported is created generally in the format shown below with all required period columns for the Accounting Schema Dimensions, money amounts and qty as are required by the Client/Tenant. (note not all periods shown in screen capture)

Conventions are that debits are entered as a positive number and credits as a negative number.

The file is saved, typically as a CSV, then imported using the “Import File Loader” process. The comments below assume the Import Loader Format is defined to be in CSV format.

The Import Budget button is clicked to display the import parameters.

• Account Date selects the first period that data will be entered into using the default Calendar for the system.
• No of Periods allows you to import less than 12 periods of data if desired.
• Organisation provides a default Org in case none is specified in the CSV.
• Batch Description provides a default Batch Description in case none is specified in the CSV.
• Document Action should always be set as Draft as the process will create a GL Journal Batch which needs to be checked before Completion.

A GL Journal Batch will be created by the process if it processes correctly.

Open the GL Journal Batch record and check for correctness of ‘Posting Type’, ‘Date’ and ‘Period’ on the Batch Header record.

Open each of the GL Journals in the Batch and check for ‘Posting Type’, ‘Date’. ‘Period’ and ‘Budget’

Complete the Journal Batch to post the budget.

Using the Import Budget Process to import historical ‘Actuals’ at Go-Live.

When data is being loaded into a new system the Import Budget process can be used to insert one or more years of financial history extracted from the ‘old’ system provided the data can be exported from the old system as a CSV or spreadsheet with columns for account code and a column for each month.

Trading statements import quite easily. Balance Sheets frequently require some manipulation so that the net movement in each account in each month can be determined before the import can be done.

Things to watch out for….

The spreadsheet must have data that is a proper number type in the value and qty columns. Frequently data extracted from the ‘old systems’ reports may not be understood as numbers and unhelpful error messages will be shown.

Make sure all values have been rounded to integers or to the currency precision of the Client/Tenant. Refer anybody who budgets in any values other than integers for psychiatric assessment.

Check that all data columns of ‘amount’ do really add to zero for each period.

If you want to provide additional comments, please use the Discussion page.