Read Trial Balance Data reads the data from the file containing the trial balance produced by accounting software.
The data read from the file by ExcelFSM is the account number, the account Title and the account balance (Debit or Credit).
The file can be an Excel file (*.xls, *.xlsx) or a QuickBooks Desktop file (*.qbw) or a QuickBooks Online file (*.txt).
Reading a Trial Balance File
Once Excel is running, starting with a blank workbook, select the FSM tab in the ribbon:
Clicking the Read Trial Balance Data button displays the following dialog (Options not installed are disabled)
If the Consolidated FS feature is installed and For Consolidated is selected, clicking OK will display the Consolidation list dialog. See 'Consolidated FS' section further below.
If the General Ledger Drill Down feature is installed and With GL transactions is selected, see 'General Ledger Drill Down' section further below.
Trial Balance in an Excel File
For Excel files, ExcelFSM assumes that trial balance data is stored in Excel spreadsheets. It should be straightforward to put the trial balance produced by accounting software into an Excel spreadsheet, but as each accounting program has varying capabilities, the exact details are left up to you. Typically you would choose the Export, Save As, or Print To File functions within the accounting program.
For the easiest path to get the trial balance data into an Excel spreadsheet:
1. If your accounting software exports to Excel spreadsheets (.xlsx or .xls????) directly, choose this first. These files can be read by ExcelFSM directly.
2. If your accounting software exports to Comma Separated Value files (.csv), choose this. These files can be read by ExcelFSM directly.
3. If your accounting program exports or prints to text (.txt) files, choose this. Then open this file in Excel and use the Text Import Wizard activated by Excel to break up lines into columns. After ensuring that each account's fields were properly put into the correct columns, save as a spreadsheet (.xlsx), and use the resultant .xlsx file for ExcelFSM.
NOTE: Often, trial balance files contain extraneous lines of headers, footers, titles, page numbers, etc. ExcelFSM attempts to ignore them and to read only the lines which contain true account information (start with the first account with a non zero balance). If you encounter problems, you can workaround them by manually opening the trial balance .xlsx file in Excel, and removing all extraneous rows which don't contain account information.
Trial Balance in an Intuit QuickBooks file
For Intuit QuickBooks files, ExcelFSM retrieves the data directly from QuickBooks, without any need to export or reformat the data.
Setting up QuickBooks to work with ExcelFSM
QuickBooks Desktop
NOTE: The Intuit QuickBooks software must be installed on the PC running ExcelFSM.
First time you will Read Trial Balance Data in ExcelFSM, QuickBooks must be running with the company file open, and you'll be asked to allow ExcelFSM to read and modify QuickBooks company file
(Note that ExcelFSM does not modify the QuickBooks company file. It only reads data)
Select oYes, always; allow access even if QuickBooks is not running
Click Continue
Click Done
QuickBooks Online
An "ExcelFSMQBO" company file must be created to allow ExcelFSM to read a QuickBooks Online company file. To create an "ExcelFSMQBO" company file, you will use the ExcelFSMQBO.exe utility program installed on your Windows Desktop in a folder called ExcelFSM Demo Files.
See how in the section 'Creating an ExcelFSMQBO company file with the ExcelFSMQBO.exe utility program' at the bottom of this topic.
Reading the accounting data
The next steps depend on the input source file.
Excel File (*.xls, *.xlsx)
Clicking OK opens the standard Windows dialog, with the type of file (bottom right) set to excel files:
Figure 1: Trial Balance File selector
Select the folder where the file is stored.
Select the file containing the desired trial balance data, and click Open.
If the file contains more than one worksheet, you will be asked to select the one containing the Trial Balance data.
ExcelFSM inserts a new sheet named 'Data', temporarily, into your workbook. This inserted 'Data' sheet contains the trial balance data.
ExcelFSM then shows the following dialog to allow you to specify additional information about this trial balance file:
Figure 2: Trial Balance Information
Name of enterprise - The name of the enterprise will be used to identify the Excel workbook (along with the as of date) , to initialize a cell with the Enterprise Name button, in the financial reports and working papers..
As-of-Date for this Trial Balance - The date of the trial balance will be used, along with the enterprise name, to identify the Excel workbook, to initialize a cell with the As-of Date button, to compute balance variation between two financial periods, as well as to select the Comparative periods balances.
NOTE: The above dialog (Figure 2) will be empty if it is a blank workbook. Make sure you put the right as-of date.
For subsequent financial periods, you will be updating an existing FSM workbook by reading the trial balance data with another as-of date. The above dialog will be pre-filled according to the previous financial period. It will be important that you update the as-of date to reflect the new period; otherwise, ExcelFSM will assume you are re-reading an amended trial balance for an existing period and, if you confirm to proceed, will update the existing period's data instead of processing a new period (Yes, ExcelFSM has the capacity to process amended trial balances and automatically update the financial reports). ExcelFSM has also the capacity to add prior periods to an existing FSM workbook.
Specify Worksheet Columns - Specify the letter of the worksheet column containing the account number, the letter of the worksheet column containing the account title, the letter of the worksheet column containing the debit balance and the letter of the worksheet column containing the credit balance, in the trial balance file. If no account number, specify the letter of an empty column. For trial balances with debits and credits in one column, simply specify the letter of that column in both fields.
NOTE: If account numbers contain '-', the '-' will be replaced by '_'
Copy adjusting entries - Check if you want to copy the current adjusting entries for the new financial period. You will be able to edit or delete these according to the new financial period. This feature is useful, for example, if the adjusting entries must be repeated one financial period after the other.
QuickBooks Desktop
If QuickBooks is not running with a company file open, clicking OK opens the standard Windows dialog, with the type of file (bottom right) set to QuickBooks files:
Figure 1: Trial Balance File selector
You can search your file by typing the file extension in the Search field (top right).
Select the file containing the desired trial balance data, and click Open.
QuickBooks Online
Clicking OK opens the standard Windows dialog, with the type of file (bottom right) set to QuickBooks Online files:
Figure 1: Trial Balance File selector
Select the file containing the desired ExcelFSMQBO company file, and click Open.
Note: a demo file is installed on your Windows Desktop (in a folder called 'ExcelFSM Demo Files').
QuickBooks File
ExcelFSM shows the following dialog to allow you to specify additional information about this trial balance:
Figure 2: Trial Balance Information for QuickBooks file
Name of enterprise - The name of the enterprise identifies the Excel workbook (along with the as of date), fills the selected worksheet cell when the Enterprise Name button is clicked.
As-of-Date for this Trial Balance - The date of the trial balance will be used, along with the enterprise name, to identify the Excel workbook, to initialize a cell with the As-of Date button, to compute balance variation between two financial periods, as well as to select the Comparative periods balances.
NOTE: The above dialog (Figure 2) will be empty if it is a blank workbook. Make sure you put the right as-of date.
For subsequent financial periods, you will be updating an existing FSM workbook by reading the trial balance data with another as-of date. The above dialog will be pre-filled according to the previous financial period. It will be important that you update the as-of date to reflect the new period; otherwise, ExcelFSM will assume you are re-reading an amended trial balance for an existing period and, if you confirm to proceed, will update the existing period's data instead of processing a new period (Yes, ExcelFSM has the capacity to process amended trial balances and automatically update the financial reports). ExcelFSM has also the capacity to add prior periods to an existing FSM workbook.
With Classes - Check if you want the "classes" to be part of the trial balance (QuickBooks Desktop only).
The classes will appear in the Chart of Accounts and identified by " | <Class name>:<Subclass name>" inserted at the end of the account's title.
If one or more classes of an account has a non-zero balance, then all classes of that account will appear.
If one or more subclasses of a class has a non-zero balance, then all subclasses of that class will appear.
If all classes (or subclasses) of an account have zero balances, they will not appear.
Non-zero balance only - Select that option if you do not want the classes of an account with a zero balance to be part of the trial balance.
Copy adjusting entries - Check if you want to copy the current adjusting entries for the new financial period. You will be able to edit or delete these according to the new financial period. This feature is useful, for example, if the adjusting entries must be repeated one financial period after the other.
Results
Click OK. The following actions occur:
•The trial balance data is imported and organized
•A new FSM workbook is created. It's name is
<Enterprise> <As-of date>.
Note: If the workbook contains Consolidated trial balances, its name would instead be:
FSM <Parent Enterprise> <As-of date> Consolidated.
•A new worksheet named 'ReadTBData printout' is inserted into this workbook.
(The account's number and title are displayed in cell E1 during the process.)
•The Chart of Accounts is opened.
Updating the Chart of Accounts
If this is the first time the workbook has had a trial balance read into it, a Chart of Accounts is built.
If the workbook contained previous trial balances, the Chart of Accounts, which was previously built, is updated. The number of new accounts that were added to the Chart of Accounts is displayed:
The new accounts are shown at the top of the Chart:
You may reorder these new accounts by dragging and dropping with the mouse or by clicking the 'Reorder new accounts' button, whenever you wish. A good time to do so is after you’ve grouped them into the proper financial reports Items.
Note: Account titles changed in the accounting software will likewise be changed in ExcelFSM.
ReadTBData printout
The ‘ReadTBData printout’ shows the data read by ExcelFSM:
•Name of enterprise
•As-of-Date
•With Classes (checked or not)
•Copy adjusting entries (checked or not)
•Data of the trial balance
•Number of accounts
•Computed totals for Debit and Credit
Note: The only purpose for this worksheet is to help you verify that the Trial Balance was read correctly. You may print it, delete it, etc.
Updated Reports
Any existing reports in your workbook are automatically updated with the new trial balance data:
•Item balances are updated to reflect the new account balances.
•Totals are then updated according to the new Item balances.
•As-of dates are updated.
•If Transfer adjusting entries was checked, the adjusting entries of the previous financial period are copied into the new financial period.
New Workbook created for each subsequent financial period
When the trial balance data for a new financial period (i.e. the As-of date has changed) is read and this new financial period is subsequent to the last financial period read, i.e. not a prior period added to an existing FSM workbook, the existing workbook is copied, and the new period's data is read into the new copy of the workbook.
Your original workbook will not be altered, and thus will contain only the data from the previous financial period. As more financial periods are read, you will retain a historical series of workbooks, one for each of their respective financial periods.
Note: Since the name of the workbook contains the Enterprise name and As-of date, the workbooks are easy to identify.
Note: Account balances of previous financial periods are preserved in the new workbook containing the latest financial period and are used for the Comparative presentation. However, you might need to access a historical workbook to see things like the Chart of Accounts as of a previous period, and the Adjusting Entries used in a previous period.
Consolidated FS
ExcelFSM shows the following dialog to allow you to specify the name of each enterprise to be included in the consolidated financial statements, and to select the file containing the desired trial balance data for each enterprise:
Consolidation list dialog.
The Consolidation list dialog is movable and resizable.
Insert - Insert a new enterprise above the currently selected enterprise.
1.A blank row is inserted into the list.
2.The first column being managed by ExcelFSM, type the enterprise name into the second column.
3.Double-click the third column or click 'Filepath' to open the standard Windows dialog (Figure 1: Trial Balance File selector).
Select the type of file (bottom right).
Select the folder where the file is stored.
Select the file containing the desired trial balance data, and click Open.
If the file contains more than one worksheet, you will be asked to select the one containing the Trial Balance data.
NOTE for AMENDED trial balances: ExcelFSM can read selected amended trial balances for an existing FSM Consolidated workbook.
From the existing list of enterprises, select a line and, double-click the third column or click 'Filepath' to open the standard Windows dialog (Figure 1: Trial Balance File selector). Select the file and click OK, or click Cancel to clear the field. ExcelFSM will read the trial balances specified in the list.
Delete - Delete the currently selected enterprise. The enterprise will be deleted only if it was not part of the consolidation of a previous financial period.
Once the list is completed, click Proceed:
ExcelFSM start with the first enterprise of the list and shows the Read Trial Balance Data dialog (figure 2) to specify additional information about the trial balance file.
Specify the trial balance information for this enterprise and click OK.
ExcelFSM will read the trial balance of the enterprise as usual.
ExcelFSM will repeat the above process, one enterprise after the other.
Note that a ‘ReadTBData printout’ worksheet will be generated and inserted into the workbook at the end of the reading process. The worksheet is named sequentially by a letter, starting with letter ‘A’ for the parent company.
Note: This letter (e.g. 'A') is also added to the account numbers in the Chart of Accounts.
Note that the FSM workbook will be named with the word ‘Consolidated’.
Following financial periods
Updating a Consolidated FSM workbook with trial balances for a new financial period is similar to the above. The Consolidation list is remembered from the previous financial period. All you must do is to provide the file path of each enterprise.
General Ledger Drill Down
QuickBooks File
If input source file is a QuickBooks file, there is no further steps to import the GL transactions.
Excel File (*.xls, *.xlsx)
After specifying the additional information about the trial balance file has been specified, ExcelFSM opens the standard Windows dialog to select the General Ledger file:
Figure 1: Trial Balance File selector
Select the folder where the file is stored.
Select the file containing the desired GL transactions, and click Open.
If the file contains more than one worksheet, you will be asked to select the one containing the Trial Balance data.
ExcelFSM then shows the following dialog to allow you to specify additional information about this GL transactions file:
Figure 3: GL transactions Information
Here is a sample of the GL transactions information to specify:
Note: it might be a good idea to export the General Ledger to a CSV (Comma Separated Value) file from the accounting software, and then to convert this CSV file into an excel file with File > Save As. This eliminates special formatting that can cause problems for the reading process.
Once read and organized in the central memory of your computer by ExcelFSM, double clicking an account's balance will display the account unadjusted balance detail. (See the GL Drill Down page of the website)
Creating an ExcelFSMQBO company file with the ExcelFSMQBO.exe utility program
An "ExcelFSM QBO" company file containing the keys must be created, to allow ExcelFSM to read the trial balance data from a QuickBooks Online company file.
To create this file, you will use the ExcelFSMQBO.exe utility program installed on your Windows Desktop in a folder called ExcelFSM Demo Files
Double click the ExcelFSMQBO.exe.
The following dialog is displayed
Follow the instructions below to get the data required to fill the fields and create the ExcelFSMQBO company file.
Company ID:
In your QuickBooks Online Company, click ‘Your Account’
Copy&Paste the Company ID in the ExcelFSMQBO Company ID field
Accounting method (Accrual or Cash):
Click ‘Company Settings’, in your QuickBooks Online Company
Set the ExcelFSMQBO company file Accounting method according to the Company Settings
First month of fiscal year and Account numbers (On or Off):
Click ‘Advanced’ in the Company Settings
Set the ExcelFSMQBO First month of fiscal year according to the Company Settings
Set the ExcelFSMQBO Account numbers according to the Company Settings
Consumer Key, Consumer Secret, Access Token, Access Token Secret:
Having an account on QuickBooks Online, you have access to the Intuit App Center Playground, which allows to generate the required keys/tokens to be specified in the remaining fields.
- Click this link https://appcenter.intuit.com/Playground/OAuth
- Copy&Paste the following code >981ccce7bc5cfb47aabb918b288e0fa4bdf3< in the App Token field of the OAuth Playground
Click ‘Get Key and Secret using App Token’
The Consumer Key and Consumer Secret are generated
Click ‘Get Request Token using Key and Secret’
The Request Token and Request Token Secret are generated
Click ‘Authorize Request Token’
The following dialog appears
Click ‘Authorize’
The following dialog appears
Click ‘Return to ExcelFSM’
The Access Token and Access Token Secret are generated
Copy&Paste the contents of the four fields in the corresponding ExcelFSMQBO four fields
Click ‘Save to file’
Select a folder and specify a name for the ExcelFSMQBO file('<enterprise name>ExcelFSMQBOfile', for example).
Click ‘Save’
ExcelFSM will now be able to read the trial balance data from your QuickBooks Online company file, by simply clicking the ‘Read Trial Balance Data’ button in the FSM ribbon, and selecting the ExcelFSM QBO file you created.