CCTrans( Company, CostCode, GLAccount, Period )
This function will return the required balance for the transactions flagged with the required cost code and posted to the indicated GL 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.
Cost Code: This should be a valid cost code, you can only use a single cost code
GLAccount: 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.
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.
Prior Year – If there are any third year transactions then this period indicator will calculate the balance for those transactions
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:
CCTrans( "c:\PastelData\_demo", "GOV01", "1000000", "Month 1" )
In future examples we will use the cell referencing method.
CCTrans( C1, "GOV01", A9, "Month 1")
CCTrans( C1, "GOV01", A9, "Last Year Month 1")
CCTrans( C1, "GOV01", A9, "Year to Date 2")
CCTrans( C1, "GOV01", A9, "Last year to Date 2")
CCTrans( C1, "GOV01", A9, "Prior Year")
Multiple accounts
CCTrans(C1, "GOV01", "1000010, 1000020, 1000030", "Month 1")
CCTrans(C1, "GOV01", "2000001..2000999", "Month 1")
Keywords can be entered in upper or lower case.
|