Get Balance

The Get Balance function is probably the most important function in GL Wand. It returns a General Ledger balance individually or summed based on the parameters that you enter. Clicking the Get Balance function on the GL Wand toolbar will open the Get Balance Wizard.

The following options are available in the Function Wizard:

Select a value from the drop down list. These lists are populated with the available values for each parameter or with the configuration data read directly from Oracle Cloud.
Click the button to link the parameter to an Excel cell.
With an Excel range reference in a parameter, use the F4 key to toggle the absolute and relative addressing as you would in Excel.

Parameter	Description
Period Name	The period name as configured in Oracle. This parameter must be entered in exactly the same format as the period names in Oracle. For example if your period names are configured as follows “Jan-07” in Oracle and you enter “Jan-2007” or “January-07” or “jan-07” for this parameter, GL Wand will not return any data, and will instead return an Error (Period) message. When used in conjunction with the special Balance Type CTD (Custom to Date) you can specify a custom period selection. For example “Feb-07-Aug-07” will add all the movement for the entered period range. You may enter multiple lists or ranges of periods and can use wild cards as well e.g. “Jan-0%” or “%-07”. When used in conjunction with the special Balance Types “JED”, “JEDU” or “JEDP” (Journal Effective Date) the period parameter becomes a date parameter e.g. “01/01/2007-05/01/2007,10/01/2007”. The format that your date is in is important. The “-“ character is used in GL Wand to denote a range of values and therefore it cannot be used as a date delimiter e.g. 01-01-2007 would not be valid. So take care if your Windows regional setting uses the “-“ as a date delimiter. For JED type calculations we recommend that you use the “/” as a date delimiter and that you always format the date as text in Excel otherwise Excel will convert the date based on your regional setting.
Balance Type	YTD, QTD, PTD, PJTD, CTD, FYS or FYE all in upper case. YTD – Summarizes the full years balance QTD – Summarizes the balance for the quarter PTD – Includes just the current period’s movement PJTD – Project to date CTD – Summarizes the custom period criteria entered FYS – Financial year start, valid with actual balances only. FYE – Financial year end JED – Summarizes the journal movements for the entered dates for both posted and unposted journals JEDP – Summarizes the journal movements for the entered dates for posted journals only JEDU – Summarizes the journal movements for the entered dates for unposted journals only
Currency	The currency code as configured in Oracle Cloud all in upper case e.g. “USD”.
Translated Flag	Enter E, T, C or Total. Use E to retrieve the entered amount and T for the translated amount. Use C to report on the converted amount i.e. the functional currency equivalent of a foreign currency amount. Only Total is valid if requesting a balance for the functional currency of the current Ledger. You must ensure that you have run the currency translation process in Oracle for the selected currency and period before reporting on translated balances. If you enter T to request the translated balance and there is no current translated balance available in the system a zero is returned. If you use the E (entered) flag in conjunction with the functional currency, any converted amounts will be excluded. To report on the total balance (including values entered in functional currency and values converted to the functional currency) use the Total option.
Actual Flag	A – Actual, B – Budget, E – Encumbrance.
Budget	If you entered B above, this field will become available and you will need to enter a budget name. Multiple budget names may be entered, separated by the List Delimiter character. Only budget values stored in the GL_BUEDGET_BALANCES table will be retrieved.
Encumbrance	If you entered E above, this field will become available and you will need to enter an encumbrance name.
Segment1 through Segment20	Enter the segment value here. You must enter it as a text value including any leading zeros e.g. enter segment value “10” as “0010” if this segment is configured as four characters with leading zeros in Oracle Cloud. You may enter your segment values in a variety of flexible ways including parents, child values, ranges, wildcards, excludes etc. To include all values simply enter “%”. When creating Get Balance formulas always use “%” to denote all values as this allows more efficient SQL to be generated. Do not use a range like “000-ZZZ” as they will require unnecessary processing. The segment value parameters can be entered as: a single value representing a child or parent account e.g. “1500” might represent a parent account. a range of values e.g. entering “1500-1600” in one of the segment parameters will sum all the segment values between 1500 and 1600 for that segment. a list of segment values e.g. entering “1500,1510,1590” in one of the segment parameters will sum the specific segment values entered. If any of the segment values are parents GL Wand will sum the hierarchy for that parent. wild card entries e.g. 1%00 will sum 1100, 1200, 1300, 1400 etc. or entering the value 1% will sum all values that start with 1. values to be included and then specific exclusions e.g. “1000-2000,~1500” will sum the values between 1000 and 2000 but will skip 1500. a combination of any of the above e.g. “1000-2000,2371,2375,2560-2580,3000,~3200,4000,~4161-4169”. In this way you can build up your own calculation definition in the segment parameter. a list of values is always processed as an OR condition e.g. “1500,1510,1590” will return all accounts where the the segment is 1500 OR 1510 OR 1590. Should you wish to use an AND condition you can use the applicable delimiter (“+” by default) e.g. “1500-1600+1550-1800” would only return the intersection of those two ranges i.e. from 1550-1600. The AND operator can be very useful when combined with DFF values and can be used to return very complex sets of criteria. a financial category representing all related parent and child segment values e.g. “CASH” would represent all segment values with the financial category of “CASH”
Ledger	This parameter can only be modified to another ledger using the same chart of accounts and calendar as the current ledger selected in the dropdown list on the toolbar. The Ledger defaults to the item selected in the dropdown list on the toolbar, or to the value in the function when editing an editing Get Balance. Enable the “Include the Ledger Id in the function” checkbox to select a different ledger. You can extend this parameter to report on multiple ledgers in a single Get Balance function. See Multiple Ledgers for the detail on this feature.
Include the Ledger Id	Choose to include the Ledger Id in the formula inserted in to Excel. This will also enable the Ledger list of values on the Ledger parameter.
Option – Journal Source	Limit the results to a specific journal source.
Option – Journal Category	Limit the results to a specific journal category.
Option – Movement	Show only the debit or credit amounts. Leave this field blank to show both.
Option – Multiply	Use this parameter to switch the sign of the balance by multiplying by -1. Also useful to show the number in a factor e.g. thousands by multiplying by 0.001. Using this parameter, rather than equivalent Excel functionality, ensures that when the Get Balance function is returning a message, this message will be displayed in the cell. For example, if you capture your Get Balance function as =OCL_GLW_Get_Balance(…) * -1 and the Get Balance result is Error (Period) due to an invalid period being entered, the cell will display #VALUE. If you use this parameter to multiple the balance by -1, the cell will return the Error (Period) message.
Option – Decimal Places	Round the balance to the specified number of decimal places. Useful to use in conjunction with the Multiply parameter to show the number in a factor e.g. thousands by multiplying by 0.001 and then round to zero decimals. See the Multiply parameter for an explanation of why it is recommended to use this parameter rather than Excel functionality to round the balance.