Setup Reportance File
Prior to importing data from Excel, you must setup a Reportance file with a Dataset to receive an Excel source.
To do this, create or open a Reportance file, and create an Entity and a Dataset in the Client Info screen. In the newly created Dataset, select Excel as the Source Type, and click Add Source. An Excel source simply requires a name to distinguish itself from other Excel imports, generally, it is recommended to create one Excel source for every different system you will be importing via Excel, see Sources for more information.
Your dataset should look something like this:
Once you have saved this Dataset, you can now import some data from Excel into this dataset.
Import from Excel
To start, export your financial data into an Excel spreadsheet, or construct a trial balance yourself using excel cells.
The Reportance Excel Import Utility will accept data in one of three formats, based on the number of columns you select:
Note, with the five columns format, you can leave the account number blank, as Reportance does not require an account number. More details on the Classification are provided at the bottom of this article.
Reportance does not care if you have headers or not, as it will assume your columns are in the orders provided above.
With the data in the right format, click and drag your mouse to select the data that you wish to import so that it is highlighted like so:
Note, Reportance 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.
Then, either go to the Reportance toolbar in Excel, or right click on the selected range and select Import Trial Balance. This will open the import screen.
Click the Select File button to navigate to the chosen Reportance file where you want to import data. If this is a valid file, the name and location of the file will appear next to Reportance File.
Next, select the Entity, and the Dataset which you setup earlier to use an Excel source.
If you used the three column or four column format, you will need to select the Class (Classification) for each account that you wish to import. 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 in use at Automatically Classify, then pressing Auto Classify, where Reportance will automatically determine the classification for each account.
You can then choose to Import (as a trial balance) or Import as a Journal.
Finally, you can edit any accounts or numbers listed. The result of importing a trial balance should look something like the following:
Or to import a journal
Once complete, press the Import/OK button to import.
The Classification is used by Reportance 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"|