This command can be used to calculate monthly movement and relevant foreign currency translation based on your monthly year-to-date trial balance. Account balance and analysis account balance tables can be built for ledger(s) of current workspace automatically to support your preparation of consolidated financial statements with compliance of the standard "IAS 21 - The Effects of Changes in Foreign Exchange Rates".
Prerequisite KnowledgeBefore learning how to use this command, users shall understand how to use the commands "Import_Master" and "Import_Transaction". In addition, an understanding of the standard "IAS 21 - The Effects of Changes in Foreign Exchange Rates" can empower user to verify the processing results.
Command with Parameters
e.g. Consol_YTD_TB "C:\LB\Trial Balance.xlsx" "Data" "C:\LB\Setting.xlsx"
Note: Prior to use this command, users shall use the command "Import_Master" to configure Ledger, Chart of Account, Analysis Category and Currency.
Source File Path
Specify full file path of an Excel Workbook which contains a month-end trial balance
e.g. C:\LB\Trial Balance.xlsx
Source Worksheet Name
Specify worksheet name of the source file which contains the trial balance
Setting Worksheet Name
Specify full file path of an Excel Workbook which contains a worksheet "Parameter"
Note: Based on the above setting, the imported trial balance shall be included the column of "Analysis 1" "Analysis 2" to support relevant data amendment. In addition, users shall also learn how to implement analysis path for better performance when the number of analysis category is more than one type. Other default settings per row 1,2,4 are to support some of the exceptional requirements.
This command can be used to output detail data reconciliation results based on two reports configured by query formula. It supports reconciliation of drill down results for every pair of LedgerBase Query formulas lq() if calculated balances of these lq() pairs are not identical.
Command with Parameters
Compare_and_Reconcile_Spreadsheet “C:\LB\Reconciliation\TB01.xlsx” “C:\LB\Reconciliation\TB02.xlsx” “Trial Balance” “C:\LB\Reconciliaton\Report.xlsx” “191,191,191” “vou” “des(All)vou(All)”
Source File Path 1
Specify full file path of first Excel Workbook which contains lq() formulas
Source File Path 2
Specify full file path of second Excel Workbook which contains lq() formulas
Specify a common worksheet name for both files which contain lq() formulas
e.g. Trial Balance
Output File Path
Specify full file path of an Excel Workbook to output reconciliation results
Specify R,G,B, color code for specific area of the reconciliation results
Define query formula field(s) as reconciliation key(s)
Other Fields (Optional)
Retrieve data by lq() formula fields for display information only