GLHistBal( Company, Account, Period, Year )
With the release of Pastel 2009 came the ability to report on historical balances
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.
Period - 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.
Year to Date x – This will return the cumulative balance for the account up to and including the period specified.
Net Change x – This will give you the movement for the period specified.
Year To Date – This will return the total of the account specified up to and including the current period.
Opening Balance - This will return the Opening Balance value for the year and account selected.
Year - this is a numerical number of either 3, 4 or 5 i.e Year 3, Year 4, Year 5. Even though the Pastel table has fields available for Year 1 and Year 2, these are not updated, so please use the GLBalance() function to retrieve this year and last year figures.
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:
GLBHistBal( "C:\pastel04\_demo", "1000000", "Month 1" , 3 )
In future examples we will use the cell referencing method.
GlHistBal( C1, A9, "Month 1", 3 )
GlHistBal(C1, A9, "Year to Date 2", 5)
GlHistBal(C1, A9, "Net Change 2", 4)
Keywords can be entered in upper or lower case.
|