Article Objective: To assist users in navigating the Active Platform to import a trial balance using Excel 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.
Context: A source is where the financial information is being imported from into Active Ledger & Reporting (e.g. MYOB, Xero, etc.).
Please refer to the Glossary for definitions of key terms used in this article. |
Import preparation
Once you have connected your dataset to Excel as a source, as below, the trial balance can be imported using the import facility in the 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.
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:
1. 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.
2. Four columns:
The fourth column method adds an account number column before the other columns described above.
3. 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.
4. 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.
Other 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:
Classification
Valid Text Strings
Asset"Asset", "Assets" or "A"Liability"Liability", "Liabilities" or "L"Equity"Equity", "Capital" or "E"Revenue"Revenue", "Income", or "R"Cost of Goods Sold"COGS", "Cost", "Cost of Goods Sold", "Direct Cost/s" or "C"Expense"Expense", "Expenses" or "X"OtherAll 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.
