Command to Formula

Using Commands to Operate Formulas

Convert Formula

Remove Formula

Compare Formula

Convert_Formula_Variable_to_Value

Convert_Formula_Variable_to_Value

This command can be used to calculate and convert to values for a set of LedgerBase variables, LedgerBase formulas and Excel formulas.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to configure either of LedgerBase variable, LedgerBase formula and Excel formula.

Command with Parameters
Convert_Formula_Variable_to_Value “C:\LB\Report.xlsx” “Y” “Y” “Y”

Note: Converted file will be saved as V_Report.xlsx of the same file folder.

Setting File Path

Specify full file path of an Excel Workbook which contains either LedgerBase variable, LedgerBase formula and Excel formula.
e.g. C:\LB\Report.xlsx

Target File Path (Optional)

Specify a full file path to save the converted results
e.g. C:\LB\Calculated_Report.xlsx

Is Converting LedgerBase Variable

Specify whether to convert LedgerBase variable to value
e.g. Y or N

Is Converting LedgerBase Formula

Specify whether to convert LedgerBase formula to value
e.g. Y or N

Is Converting Excel formula to Value

Specify whether to convert Excel formula to value
e.g. Y or N

Target Worksheet (Optional)

Specify to execute conversion for specific worksheet, if blank, will convert all worksheets

Target Worksheet (Optional)

Specify to execute conversion for specific worksheet, if blank, will convert all worksheets

Set_Var_Convert_Formula_Variable_to_Value

Set_Var_Convert_Formula_Variable_to_Value

This command can be used to calculate and convert to values for a set of LedgerBase variables, LedgerBase formulas and Excel formulas. A assigned variable will be returned a value (Y or N) to notify whether the processing is successful.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to configure the command “Convert_Formula_Variable_to_Value

Command with Parameters
Convert_Formula_Variable_to_Value “C:\LB\Report.xlsx” “Y” “Y” “Y”

Note: Converted file will be saved as V_Report.xlsx of the same file folder.

Variable Name

Specify a variable name to store a value upon completion of processing
e.g. Is Successful

Setting File Path

Specify full file path of an Excel Workbook which contains either LedgerBase variable, LedgerBase formula and Excel formula.
e.g. C:\LB\Report.xlsx

Target File Path (Optional)

Specify a full file path to save the converted results
e.g. C:\LB\Calculated_Report.xlsx

Is Converting LedgerBase Variable

Specify whether to convert LedgerBase variable to value
e.g. Y or N

Is Converting LedgerBase Formula

Specify whether to convert LedgerBase formula to value
e.g. Y or N

Is Converting Excel formula to Value

Specify whether to convert Excel formula to value
e.g. Y or N

Target Worksheet (Optional)

Specify to execute conversion for specific worksheet, if blank, will convert all worksheets

Target Worksheet (Optional)

Specify to execute conversion for specific worksheet, if blank, will convert all worksheets

Run_Report_Writer

Run_Report_Writer

This command can be used to calculate and convert to values for a set of LedgerBase query settings for report generation with or without details.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to configure LedgerBase query settings for report generation with or without details.

Command with Parameters
Run_Report_Writer “C:\LB\Report.xlsx” “Transaction Summary” “Query Setting”

Setting File Path

Specify full file path of an Excel Workbook which contains a set of LedgerBase query settings
e.g. C:\LB\Report.xlsx

Target Worksheet

Specify a worksheet which contains a report layout
e.g. Transaction Summary

Query Setting Worksheet

Specify a worksheet which contains LedgerBase query settings
e.g. Query Setting

Remove_LQT_Formula

Remove_LQT_Formula

This command can be used to remove LedgerBase Query Transaction formulas lqt() from worksheet(s) of an Excel Workbook.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to configure lqt() formula.

Command with Parameters
Remove_LQT_Formula “C:\LB\Report.xlsx” “LQT”

Setting File Path

Specify full file path of an Excel Workbook which contains a set of lqt() formulas
e.g. C:\LB\Report.xlsx

Target Worksheet

Specify a worksheet which contains lqt() formulas
e.g. LQT

Target Worksheet (Optional)

Specify another worksheet which contains lqt() formulas
e.g. LQT2

Compare_and_Reconcile_Spreadsheet

Compare_and_Reconcile_Spreadsheet

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.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to create a ledger with imported transactions and configure report using lq() formula.

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
e.g. C:\LB\Reconciliation\TB01.xlsx

Source File Path 2

Specify full file path of second Excel Workbook which contains lq() formulas
e.g. C:\LB\Reconciliation\TB02.xlsx

Worksheet Name

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
e.g. C:\LB\Reconciliaton\Report.xlsx

Font Color

Specify R,G,B, color code for specific area of the reconciliation results
e.g. 191,191,191

Reconcile Key

Define query formula field(s) as reconciliation key(s)
e.g. vou

Other Fields (Optional)

Retrieve data by lq() formula fields for display information only
e.g. des(All)vou(All)