Overview: This guide will assist users in importing a trial balance into an Active Ledger and Reporting dataset using an Excel source
A source is where the financial information is being imported from into Active Ledger & Reporting (e.g. MYOB, Xero, etc.).
If you have not yet connected to a source, see Knowledge Article: Managing Sources.
Please refer to the Glossary for definitions of key terms used in this article.
Note: Excel is usually used as a source when the user does not have direct access to the clients accounting software or if the software is not listed as one of the integrated cloud sources.
Watch the video
Import via Excel add-in
Import preparation
Once you have connected your dataset to Excel as a source, as below, the trial balance can be imported using the import function in the Active Ledger Excel add-in.
Note: If you have not already done so, you can download the Excel add-in directly from Microsoft AppSource by clicking here. Alternatively, you can follow the installation details in the knowledge article: How to install the Excel add-in (Active Ledger & Reporting).
To confirm this add-in has worked, open Excel. An Active Ledger toolbar should be visible in the Excel ribbon under the home tab.
Import formatting
To begin, export your financial data from the clients accounting file into an Excel spreadsheet, or construct a trial balance using Excel cells. The Active Ledger Excel import utility will accept data in one of four formats based on the number of columns you select.
Columns
Three columns
The three column method assumes that the first column is the account name, the second column is for debits and the third is for credits.
Note: If you have amounts in only one column with the credits displayed as negative amounts, you can still select three columns (selecting a blank column for the third column) and Active Ledger will split the amounts for the import journal.
Four columns
The fourth column method adds an account number column before the other columns described above.
Five columns
The fifth column method adds a classification column before the other columns described above. Refer to the 'Other notes' section of this article for more information.
Six columns
The six column method allows you to add either a memo, quantity or division column for the final column to further classify your data.
Additional notes
With the five or six column formats, the account numbers can be left blank if desired, as Active Ledger does not require an account number.
Active Ledger does not require headers to import correctly as it will assume your columns are in the orders provided above.
Active Ledger does not mind the use of blank lines or header accounts and the like, however it will assume that every line with a value in the debit or credit column is a valid account that needs to be imported. That is, you will need to remove any lines with totals in them (note that in the 'Importing trial balance' section below, when the data was highlighted, the total row was included in this)
If you used the three or four column format, you will need to select the classification for each account before the journal is able to be posted
The classification is used by Active Ledger to help automatically link accounts to the master chart of accounts. Once an account is linked to the master chart of accounts the classification loses its relevance. In the Excel import process, it will automatically link the following text to classifications as shown below.
Valid classification text strings
Classification | Accepted text strings or part of name |
Asset | "Asset", "Assets", "A" |
Liability | "Liability", "Liabilities", "L" |
Equity | "Equity", "Capital", "E" |
Revenue | "Revenue", "Income", "R" |
Cost of Goods Sold | "COGS", "Cost", "Direct Cost/s", "C" |
Expense | "Expense", "Expenses", "X" |
Other | Any other text strings, including "O" |
Importing trial balance
Highlight the data you wish to import, ensuring the total row is not highlighted, and select Import Data.
Once this is selected, a slide-out screen will appear. Select Login and the login page will pop-up on the screen. Enter your Active credentials.
Once logged in, you will be brought to a screen where you need to select the client Active Ledger file by using the search bar, and selecting the relevant dataset.
Also ensure the correct heading option has prefilled into the 6th column value. If this has not prefilled automatically or incorrectly done so, manually edit this to ensure the journal is prepared correctly.
Tip: The taskpane in Excel can be 'popped out' or resized, and moved to another screen for ease of use. Simply hover your cursor over the 'Active Workpapers' header until a cross appears and click and drag.
Click Prepare Journal.
After clicking the Prepare Journal button, a pop-up screen will appear where you can select the type of journal you want to import. Commonly for imports, you will be importing a trial balance so ensure you select 'Imported Trial Balance' as the journal type.
Note: When selecting an 'Imported' journal type it will override any existing import journal in the system for the same period. This is important if you are refreshing a trial balance.
Once you have made your selection, if you use an established account numbering system for the account number of each account, you can automate this process by selecting the numbering system used in the Classify Account Numbers dropdown menu.
Note: You will need to have input the account numbers for this function to work, however you will not need to input the account classification.
Once you have reviewed the journal, click Import Journal. A little pop-up message will appear in the bottom right corner if this has been posted successfully.
If you are wanting to confirm this journal has been posted, navigate to the dataset you just imported the data to and select the Journals tab.
If imported correctly, you will see the journal sitting in this page.
You are now ready to commence your tax work.
Import in-app
Import options
You have various options as to how you import a trial balance in-app into your dataset. These include:
Active Ledger Import Template
XPA, MYOB AE & MAS trial balance import
Journal
Note: If you are an XPA, MYOB AE or MAS user, we recommend using the relevant trial balance import feature available.
If you use another source, we recommend using the Active Ledger Import Template for larger imports and the journal option for simpler imports.
All of these options are explained in detail below.
Import preparation
Once you have connected your dataset to Excel as a source, the trial balance can be imported using the import function in-app.
If you have not yet created a dataset, refer to the following knowledge article for information on how to do this: How to work with datasets.
Click Import Data from the Information tab within the dataset.
The following pop up will appear where you are able to dropdown on the import format and select the appropriate option.
Active Ledger Import Template
Select the Active Ledger Import Template as the import format and proceed with downloading the template by clicking on the hyperlink, as below.
Open the import template and complete all relevant information, noting that the only mandatory columns are the Account Name and the Debit column (where you would input debit balances as positive and credit balances as negative).
Note: It is highly recommended to include the classification as the system will require this for new accounts imported to Active Ledger.
For this example, we will fill out all columns except the Qty, Division and Memo, noting that the relevant classification text strings are as below.
Classification | Accepted text strings or part of name |
Asset | "Asset", "Assets", "A" |
Liability | "Liability", "Liabilities", "L" |
Equity | "Equity", "Capital", "E" |
Revenue | "Revenue", "Income", "R" |
Cost of Goods Sold | "COGS", "Cost", "Direct Cost/s", "C" |
Expense | "Expense", "Expenses", "X" |
Other | Any other text strings, including "O" |
This is a snapshot of how your import template may look.
Once happy, save and close your Excel import template, navigate back to your client's Active Ledger file and upload your file.
Once you have uploaded your file, it will automatically create an Imported Trial Balance journal using the information in the file for you to review and approve.
If you are satisfied with the journal, scroll to the bottom and click Add Journal.
This journal will post and sit within the Journals screen as an imported journal.
You are now ready to commence your work.
XPA, MYOB AE & MAS trial balance import
If you are an XPA, MYOB AE or MAS user, you would opt to use this import option over the Active Ledger Import Template as it is far quicker.
To start, select the import format option that is relevant to you - i.e. XPA, MYOB AE or MAS.
The process is the same for all these options.
For this example, we will select MAS. Simply upload the relevant trial balance following the guide provided in the hyperlink to ensure the trial balance is extracted correctly.
Once you have uploaded your file, it will automatically create an Imported Trial Balance journal using the information in the file for you to review and approve.
If you are satisfied with the journal, scroll to the bottom and click Add Journal.
This journal will post and sit within the Journals screen as an imported journal.
You are now ready to commence your work.
Journal
If you are only importing a simple trial balance, you are able to add a journal. To do this, click + Add Journal.
You will be taken to a blank journal template where you can fill out the journal details.
Once you are happy with your journal, click Add Journal.
This journal will post and sit within the Journals screen as an imported journal.
You are now ready to commence your work.