Import a Trial Balance using the Accurri format

To import a single Trial Balance into a new report using the Accurri format:

  • Click Inputs > Trial Balance
  • Click Import
  • Click Accurri
  • Select Number of Trial Balances to import - Single
  • Select the Division name 
  • Select the Trial Balance import method (unless using the Mapping module select Standard Trial Balance)
  • Select the file to import
  • Click OK
  • When importing a single Trial Balance, the first visible worksheet in the Excel file will be imported into the division selected


The following image shows a valid example file:

Image shows a valid Excel import file with the data in the correct columns.

  • Column A contains the account number
  • Column B contains the account name
  • Column C contains the current period balance
  • Column D contains the prior period balance
  • Column E contains the current period allocation
  • Column F (prior period allocation) is blank, so the prior allocation will be set the same as the current period allocation

As an alternative to including the allocations within the import file, columns E and F can be left blank. Then click the Map button after importing the file. Click here for more information on the different methods to allocate or map a Trial Balance.

Import multiple Trial Balances using the Accurri format

To import multiple Trial Balances into a new report using the Accurri format:

  • Click Inputs > Trial Balance
  • Click Import
  • Click Accurri 
  • Select Number of Trial Balances to import - Multiple
  • Select the Trial Balance import method (unless using the Mapping module select Standard Trial Balance)
  • Select the file to import
  • Click OK

The following image shows a valid example file. If it was imported using Multiple, then 'PAR' would be imported into the division with the alias 'PAR', 'Sheet2' would be ignored (even if it contains valid data) as it is not a valid division alias and 'MAN' would be imported into the division with the alias 'MAN'.

The division alias is set in Entity setup. A warning will be shown after the import is complete for any worksheets in the Excel file that do not have a matching division alias in the entity, any such worksheets are ignored.

To rename a worksheet in Excel, right mouse click the worksheet E.g. 'Sheet2' and select 'Rename', then type a new name.

Image shows an Excel file with multiple worksheets. They are labelled PAR, Sheet 2 and MAN.

Import a Trial Balance into a rolled report using the Accurri format

To import a Trial Balance into a rolled report using the Accurri format:

  • Click Inputs > Trial Balance
  • Click the Import button 
  • Click Accurri 
  • Select Number of Trial Balances to import
  • If importing a single Trial Balance, select the Division name to import the Trial Balance into
  • Select the Trial Balance import method (unless using the Mapping module select Standard Trial Balance)
  • Select the file to import 
  • Click OK
  • If importing a single Trial Balance, the first visible worksheet in the Excel file will be imported into the division selected


The following image shows a valid example file:

Image shows a valid Excel import file with the data in the correct columns.

  • Column A contains the account number
  • Column B contains the account name
  • Column C contains the current period balance
  • Column D (prior period balance) is blank as the Trial Balance will already have a prior period balance. There is no need to include the prior period balance in the file unless they are being updated.
  • Column E (current period allocation) is blank as the Trial Balance will already be allocated. There is no need to include the allocations in the file unless they are being updated.
  • Column F (prior period allocation) is blank as the Trial Balance will already be allocated. There is no need to include the allocations in the file unless they are being updated.
     

Importing Trial Balances examples using the Accurri format

Example 1 - importing an allocated Trial Balance with current and prior period balances into a new report
  • Column A contains the account number
  • Column B contains the account name
  • Column C contains the current period balance
  • Column D contains the prior period balance
  • Column E contains the current period allocation
  • Column F is the prior period allocation. As it is blank, then the prior period will be allocated using the current period allocation from column E

Image shows a valid Excel file import with data in the correct columns. Columns A to F have been populated.
 

Example 2 - Importing a Trial Balance to update account names and balances

After importing the Trial Balance in example 1 above.

  • Column A contains the account number
  • Column B contains the account name. The account name for account number 1127 will be changed from 'ABC Bank electronic clearing account' to 'AAA Bank electronic clearing account'
  • Column C contains the current period balance. All current period balances will be updated to the new balances in this Trial Balance
  • Column D is the prior period balance. As it is blank, the prior period balances will not be changed
  • Column E is the current period allocation. As it is blank, the current period allocations will not be changed
  • Column F is the prior period allocation. As it is blank, the prior period allocations will not be changed


Note: Not all columns have to be imported. In this example, the current period balances and an account name were updated.

Image shows a valid Excel file import with data in the correct columns. The account name for account number 1127 has been changed to AAA Bank electronic clearing account. Columns A to C have been populated.

Example 3 - Importing a Trial Balance to update prior period comparatives 
  • Column A contains the account number
  • Column B contains the account name
  • Column C is the current period balance. As it is blank, the current period balances will not be changed
  • Column D contains the prior period balance. All prior period balances will be updated to the new balances in this Trial Balance
  • Column E is the current period allocation. As it is blank, the current period allocations will not be changed
  • Column F is the prior period allocation. As it is blank, the prior period allocations will not be changed

Image shows a valid Excel file import with data in the correct columns. Columns A, B and D have been populated.

Example 4 - Importing an annual Trial Balance

When rolling forward an Interim Report the software will prompt the selection of an Annual Report to import the annual Trial Balance from.

When not rolling forward or to import the annual period manually the file format would be as follows, assuming current and prior periods are already imported.

  • Column A contains the account number
  • Column B contains the account name
  • Column C contains the current period balance. As it is blank, the current period balances will not be changed
  • Column D is the prior period balance. As it is blank, the prior period balances will not be changed
  • Column E is the current period allocation. As it is blank, the current period allocations will not be changed
  • Column F is the prior period allocation. As it is blank, the prior period allocations will not be changed
  • Column G contains the annual period balance. All annual period balances will be updated to the new balances in this Trial Balance
  • Column H is the annual period allocation. As it is blank, then the annual period will be allocated using the current period allocation from column E

Image shows a valid Excel file import with data in the correct columns. Columns A, B and G have been populated.

Importing Trial Balances examples using Excel

Example 1 - Importing Trial Balances contained in one worksheet
  • Column A contains the account number
  • Column B contains the account name
  • Columns C to E contain the prior period balances for multiple divisions
  • Columns F to H contain the current period balances for multiple divisions

  • Column headers have been matched to the corresponding data. For example Column A is allocated All divisions - Account number.
  • Header rows to skip is 2, resulting in the first two rows being unticked. They will not be imported.

Image shows the trial balance with the column headers matching the data contained in the columns

Example 2 - Importing Trial Balances in separate worksheets
  • Column A contains the account number
  • Column B contains the account name
  • Columns C contains the mapping code. See Mapping by code for further information.
  • Column D contains the prior period balances
  • Column E contains the current period balances
  • The worksheets have been named according to the division alias the Trial Balance corresponds to. The division alias is set in Entity setup.

Excel file with multiple Trial Balances across separate worksheets

  • Worksheet name matches division is ticked as the worksheets have been named according to its division alias
  • Column headers have been matched to the corresponding data. For example Column A is allocated Account number.
  • Column C remains 'NOT MATCHED'. The column will not be imported.
  • The columns in the remaining 'MAN' and 'RET' worksheets have been matched to the corresponding data
  • Header rows to skip is 4, resulting in the first four rows being unticked. They will not be imported.

Image shows columns of data with headings

Importing Trial Balances examples using Copy and paste

Example 1 - Data is contained in one column
  • Column A contains the account number, account name and both current and prior period balances for multiple divisions
  • The data has been separated by the delimiter "_"
  • Select the data in Column A, then copy and paste the data into the Import trial balance screen

Image shows the data in one column

  • The column delimiter "_" has been entered under Column delimiter

Image shows the data copied into the Import trial balance screen

Example 2 - Data is contained in separate columns
  • Column A contains the account number
  • Column B contains the account name
  • Columns C to E contain the prior period balances for multiple divisions
  • Columns F to H contain the current period balances for multiple divisions
  • Select the data in Columns A to H, then copy and paste the data into the Import trial balance screen

Image shows Excel file with data separated into columns.

  • Tab is ticked as the data is separated into columns

The image shows data copied into the Import trial balance screen

Import Trial Balances using Mapping by Account

In this example, a mapping by account file has been imported in the Mapping module and looks like the following image:

Image shows an excerpt of the Mapping by Account screen in the Mapping module found in the software. The columns Mapping account, Description and Allocation have been populated.

  • Click Inputs > Trial Balance
  • Click Import
  • Click Accurri 
  • Select Number of Trial Balances to import
  • If importing a single Trial Balance, select the Division name
  • Select the Trial Balance import method - Mapping By Account Trial Balance
  • Select the file to import
  • Click OK

Alternatively:

The following image shows a valid example file:

Image shows a valid Excel import file. In Column A are the account numbers. Column B is the account description. Column C is the current period balance. Column D is the prior period balance.

  • The software searches the mapping file for the account numbers in column A. It allocates based on the allocation for that account number in the mapping file
  • In cell A1 is account number 1001. It will search for account number 1001 in the mapping file and see it is CACOH. Account 1001 will then be allocated to CACOH
  • In cell A2 is account number 1002. It will search for account number 1002 in the mapping file and see it is CACAB. Account 1002 will then be allocated to CACAB
  • This process continues until the end of the Trial Balance is reached
  • The allocations that accounts are mapped to can be changed at any time in the Trial Balance screen, or at the top level in the mapping module

Import Trial Balances using Mapping by Code

In this example, a mapping by code file has been imported in the Mapping module and looks like the following image:

Note: The mapping code can be a number, a code, text or any combination of letters, numbers and characters.

Image shows an excerpt of the Mapping by code screen in the Mapping module found in the software. The columns Mapping code, Description and Allocation have been populated.

  • Click Inputs > Trial Balance
  • Click Import
  • Click Accurri 
  • Select Number of Trial Balances to import
  • If importing a single Trial Balance, select the Division name
  • Select the Trial Balance import method - Mapping By Code Trial Balance
  • Select the file to import
  • Click OK

The following image shows a valid example file:

Trial Balance example mapping code

  • The software searches the mapping file for the codes in column E, and other allocation columns if applicable. It allocates based on the allocation for the code found in the mapping file
  • In cell E1 is code 1.1.100. It will search for code 1.1.100 in the mapping file and see it is CACOH. Account 1110 will then be allocated to CACOH
  • In cell E2 is code 1.1.250. It will search for code 1.1.250 in the mapping file and see it is CACAB. Account 1124 will then be allocated to CACAB
  • This process continues until the end of the Trial Balance is reached
  • The allocations that accounts are mapped to can be changed at any time in the Trial Balance screen, or at the top level in the mapping module