GLBalance( Company, Account, Period )
This function will return the required balances, figures from your general/nominal ledger accounts.
The arguments are as follows:
Company: This is the path to your Pastel directory. For example: c:\PastelData\_demo would enable you to retrieve information from the Pastel Partner demo company.
Account No: This is the Pastel GL account number that you wish to retrieve information for; for example this could be account no 1000000 which is the Sales account (with Pastels demo data). The account number can also be a range of accounts, for example:
1000010, 1000020 This would return the balance for accounts 1000/010 and 1000/020.
1000001..1000999 This would return all the balances for all accounts between and including 1000/001 and 1000/999.
Warning: When using this type of range and sub accounts you must ensure that you do not include the main account, as the main account holds the total balance of all the sub-accounts below it.
When using a range of accounts such as 1000001..1000999 PaXcel will need to check each account for each balance, in the above case this will be 999 times, if you where to try a large range of say 1000001..9999999 it could potentially try 1 million times.
The period can be one of the following keywords to retrieve the required balance: (x represents a period number from 1 – 13).
Month x – Will retrieve the figures for the account and period specified. If the account is for a balance sheet it will give you the total balance up to and including that period. If you require the movement for that period you should use the Net Change option.
Last Year Month x – This is the same as Month x but will retrieve the balance for the Last Year of the period specified.
Year to Date x – This will return the cumulative balance for the account up to and including the period specified.
Last Year to Date x – This will return the cumulative balance for the account up to and including the period specified for the Last fiscal year.
Net Change x – This will give you the movement for the period specified.
Last Year Net Change x – This will give you the movement for the period specified for the last fiscal year.
This Month – This will return the balance of the account based on Pastels current period.
This Month Last Year – This will return the balance of the account based on Pastel's Current period for the last fiscal year.
Year To Date – This will return the total of the account specified up to and including the current period.
Last Year to Date – This will return the total of the account specified up to and including the current period for the last fiscal year.
In the following examples we have entered the company path in cell C1 and the account number in cell A9. We could also enter the periods as cell headings.
In full without cell referencing it would be:
GLBalance( "C:\pastel04\_demo", "1000000", "Month 1" )
In future examples we will use the cell referencing method.
GlBalance( C1, A9, "Month 1")
GlBalance( C1, A9, "Last Year Month 1")
GlBalance(C1, A9, "Year to Date 2")
GlBalance(C1, A9, "Last year to Date 2")
GlBalance(C1, A9, "Net Change 2")
GlBalance(C1, A9, "Last Year Net Change 2")
GlBalance(C1, A9, "This Month" )
GlBalance(C1, A9, "This Month Last Year" )
GlBalance(C1, A9, "Year to Date")
GLBalance(C1, A9, "Last Year to Date")
Multiple accounts
GLBalance(C1, "1000010, 1000020, 1000030", "Month 1")
GLBalance(C1, "2000001..2000999", "Month 1")
Keywords can be entered in upper or lower case. |