After adding Excel as a source to Active Ledger (see the Dataset - Manage Sources - Excel Connection knowledge article), the trial balance can be imported using the import facility in the Excel add-in. If you have not already done so you can download the Excel add-in directly from Microsoft AppSource by clicking here. Alternatively, follow the installation details that will appear after creating the data source as shown in the Installation knowledge article.
An Active Ledger toolbar will then be visible in the Excel ribbon under the Home tab as shown below:
Import from Excel
To start, export your financial data 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, 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 fourth column method adds a Classification column before the other columns described above. There are more details on Classifications provided at the bottom of this article.
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.
Choose the relevant option from the 6th Column Value dropdown menu in the import facility so that Active Ledger prepares the journal correctly.
- With the five or six column formats the account numbers can be left blank as Active Ledger does not require an account number.
- Active Ledger does not care if you have headers or not, 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.
With the data in the right format, click and drag your mouse to select the data that you wish to import so that they are highlighted. Then go to the Active Ledger toolbar in Excel (under the Home tab) and click on Import Data. This will bring up a task pane to select the Entity and the Dataset where the data are to be imported. Click Prepare Journal.
After clicking the Prepare Journal button, you can select the type of journal you want to import.
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.
If you used the three or four column format, you will need to select the Class (Classification) for each account to be imported. 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"|
|Other||All other text strings, including "O"|
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.
Finally, add a journal number and date and click on Import Journal. The data have now been imported into Active Ledger. Return to Active Ledger to begin working on the file.
Next article: Datasets - Journals