Ledger Processing 2

Advanced Functions of Ledger Processing

Calculate_Balance_by_Column_Header

Calculate_Balance_by_Column_Header

This command can be used to calculate and build balance tables of ledgers of current workspace for ledgers are included in a list.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to use the basic command “Import Transaction” and “Calculate_Balance”.

Command with Parameters

e.g. Calculate_Balance_by_Column_Header “C:\LB\Data.xlsx” “Transaction” “Ledger”

Source File Path

Specify full file path of an Excel Workbook which contains a list of ledger identification
e.g. C:\LB\Data.xlsx

Source Worksheet

Specify worksheet name of the file
e.g. Transaction

Source Column

Specify relevant column header of the worksheet
e.g. Ledger

Set_Var_Calculate_Balance_by_Column_Header

Set_Var_Calculate_Balance_by_Column_Header

This command can be used to calculate and build balance tables of ledgers of current workspace for ledgers are included in a list. 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 use the basic command “Import Transaction“, “Calculate_Balance” and “Set_Var”.

Command with Parameters

e.g. Set_Var_Calculate_Balance_by_Column_Header “Is Successful” “C:\LB\Data.xlsx” “Transaction” “Ledger”

Variable Name

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

Source File Path

Specify full file path of an Excel Workbook which contains a qualified transaction list
e.g. C:\LB\Data.xlsx

Source Worksheet

Specify relevant worksheet name of the file
e.g. Transaction

Source Column

Specify relevant column header of the worksheet
e.g. Ledger

Calculate_Balance_by_File_List

Calculate_Balance_by_File_List

This command can be used to calculate and build balance tables of ledgers of current workspace for ledgers are included indirectly in a list of file paths which contain transactions.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to use the basic command “Import Transaction“, “Calculate_Balance” and “Set_Var”.

Command with Parameters

e.g. Calculate_Balance_by_File_List “C:\LB\Append Posting.xlsx” “Task List” “File List” “Transaction”

File Path of File List

Specify full file path of an Excel Workbook which contains a file list to locate relevant transactions
e.g. C:\LB\Append Posting.xlsx

Worksheet Name of File List

Specify relevant worksheet of the file
e.g. Task List

Column Header of File List

Specify relevant column header for a list of full file paths to locate each transaction file
e.g. File List

Column Header of Worksheet Name

Specify relevant column header for a list of worksheet names of these transaction files which contain transaction list
e.g. Transaction

Set_Var_Calculate_Balance_by_File_List

Set_Var_Calculate_Balance_by_File_List

This command can be used to calculate and build balance tables of ledgers of current workspace for ledgers are included indirectly in a list of file paths which contain transactions. A 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 use the basic command “Import Transaction“, “Calculate_Balance” and “Set_Var”.

Command with Parameters

e.g. Set_Var_Calculate_Balance_by_File_List “Is Successful” “C:\LB\Append Posting.xlsx” “Task List” “File List” “Transaction”

Set Variable

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

File Path of File List

Specify full file path of an Excel Workbook which contains a file list to locate multiple transaction files
e.g. C:\LB\Append Posting.xlsx

Worksheet Name of File List

Specify relevant worksheet of the file
e.g. Task List

Column Header of File List

Specify relevant column header for a list of full file paths to locate each transaction file
e.g. File List

Column Header of Worksheet Name

Specify relevant column header for a list of worksheet names of these transaction files which contain transaction list
e.g. Transaction

Clear_Transaction_by_Column_Header

Clear_Transaction_by_Column_Header

This command can be used to delete a group of ledger transactions of current workspace by a list of filtering criteria. You can configure the list by a combination of column names you have implemented for target ledgers with filling corresponding values for each column.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to create a ledger with transactions by using commands “Import_Master” and “Import_Transaction“.

Command with Parameters

e.g. Clear_Transaction_by_Column_Header “C:\LB\List.xlsx” “Combination” “d” “Ledger,Analysis 1”

Source File Path

Specify full file path of an Excel Workbook which contains a list of filtering criteria
e.g. C:\LB\List.xlsx

Source Worksheet

Specify worksheet name which contains the list of filtering criteria
e.g. Combination

Filtering Criteria

Specify any one of below filtering criteria
e.g. d

y – filtering year of transaction date
m – filtering year and month of transaction date
d – filtering year, month and day of transaction date

Date Column Header

Specify column header of a date column
e.g. Date

Filtering Criteria (Optional)

Specify a combination of column headers
e.g. Ledger, Analysis 1

Learn Backward

Command “group_column_list_by_file_list” and “group_combination_column_list_by_file_list” can be further used to support automation of filling corresponding values for each column.

Learn Forward

Clear_Transaction_by_Column_Header_and_First_Row

Clear_Transaction_by_Column_Header_and_First_Row

This command can be used to delete a group of ledger transactions of current workspace by a filtering criteria. You can configure the filtering criteria by a combination of column names you have implemented for target ledgers with filling one row of corresponding values for each column.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to create a ledger with transactions by using commands “Import_Master” and “Import_Transaction“.

Command with Parameters

e.g. Clear_Transaction_by_Column_Header_and_First_Row “C:\LB\Column_Form.xlsx” “Common Value” “d” “Ledger,Analysis 1”

Source File Path

Specify full file path of an Excel Workbook which contains one row of filtering criteria
e.g. C:\LB\Column_Form.xlsx

Source Worksheet

Specify worksheet name which contains the list of filtering criteria
e.g. Combination

Filtering Criteria

Specify any one of below filtering criteria
e.g. d

y – filtering year of transaction date
m – filtering year and month of transaction date
d – filtering year, month and day of transaction date

Date Column Header

Specify column header of a date column
e.g. Date

Filtering Criteria (Optional)

Specify a combination of column headers
e.g. Ledger, Analysis 1

Upload_Analysis_Path_File_by_Column_Header

Upload_Analysis_Path_File_by_Column_Header

This command can be used to improve performance of complex report processing which contains mass volume of formulas using multi-level analysis..

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to use the command “Upload_Analysis_Path_File“.

Command with Parameters

e.g. Upload_Analysis_Path_File_by_Column_Header “C:\LB\Analysis Path.xlsx” “Ledger” “Ledger” “Analysis Path”

“C:\LB\Analysis Path.xlsx”

analysis_path_sample_01

“Ledger” “Ledger” “Analysis Path”

chart_of_account_analysis_path_01

Analysis Path File

Define full file path of a file which contains a list of analysis path
e.g. C:\LB\Analysis Path.xlsx

Setting Worksheet Name

Specify a worksheet name which contains a list of ledger. Normally it is equivalent to the worksheet “Ledger” of the Ledger Master File.
e.g. Ledger

Setting Column Name

Specify a column name which contains a list of ledger. Normally it is equivalent to the column “Ledger” of the Ledger Master File.
e.g. Ledger

Setting Column Name

Specify a column name which contains a list of full file path of an analysis path file. Normally user can add a new column “Analysis Path” into the Ledger Master File.
e.g. Analysis Path