FINANCIAL STATEMENT MASTERS

Trial balance data from accounting software


In ExcelFSM, clicking 'Read Trial Balance Data' imports the data from the Excel file containing the trial balance produced by accounting software.

Most accounting software provide a feature to export the generated trial balance to Excel,

Note: the trial balance must be a cumulative one, ie giving Year to Date totals. ExcelFSM will allow you to show movement between two dates with its Balance Variation button.



so it should be straightforward to put the trial balance produced by accounting software into an Excel spreadsheet.



Make sure you have a row with the total for Debits and Credits.

Trial balances with debits and credits in one column can also be read by ExcelFSM.



Openning a blank workbook and clicking the 'Read Trial Balance Data' button from the FSM ribbon,
will result in the following Chart of Accounts in an 'FSM' workbook.





You may have to edit the sheet, to remove extraneous rows which don't contain account information (headers, footers, titles, page numbers, etc.), or to split the accounts number and accounts title in two columns.

Split the accounts number and accounts title in two columns




Select the column to split then click Data > Text to Columns


This starts the Convert Text to Columns Wizard

Select 'Fixed width'


Move the break lines at the end of the numbers and at the beginning of the titles


Specify an empty column to the right for Destination


The account numbers being in column E and account titles being in column G


you can simply edit the sheet to get the following trial balance



If the accounting software does not provide the 'Export to Excel' feature, it is mostly standard that accounting software have a 'Print to File' option,
allowing to export the trial balance either in a .CSV file (Comma Separated Values) or in a .TXT file (Ascii)


.CSV file

Printing to file as a CSV file


will result in the following file


To convert the .CSV file into an Excel file:
run Excel,
click File > Open,
select the file type,
select the file


You may have to edit the sheet, to remove extraneous rows which don't contain account information (headers, footers, titles, page numbers, etc.), or, as with the current sample, to fix the columns width.

To split the accounts number and accounts title in two columns, select the column to split then click Data \ Text to Columns (process described above).

When you're done editing the sheet,
click File > Save As
select the .xlsx file type
click Save.






.TXT file

Printing to file as an Ascii (Text) file


will result in the following file


To convert the .TXT file into an Excel file:
run Excel,
click File > Open,
select the file type,
select the file.


This starts the Text Import Wizard

Select 'Fixed width'


Scroll the trial balance to display the Total row to help you specify the column breaks.
Move the break lines
at the end of the account numbers,
at the beginning of the account titles,
before the first digit of the Debit Total, and
at the end of the last digit of the Debit total.


Use the default general data format and click Finish


The following excel sheet is generated


Edit the sheet to remove extraneous rows (headers, footers, titles, page numbers, etc.) and extraneous columns, and to fix columns width.


When you're done editing the sheet,
click File > Save As
select the .xlsx file type
click Save.







Back to top