Example of GL functions
In the following examples we are going to get data from our examples company which has the code "ZXERO01"
Hot Link Single values:
The function "CXL.GLVal()" will Hot Link a single GL, or total, value for a period, whole year or YTD, by Financial/Calander Period, Quarter or by Weeks optionally filtered by Cost Centres ( AKA Tracking code 1/2 in Xero,Classes and Locations in QuickBooks, and Cost Centres and Departments in Sage Accounting ).
One function to get the Value of GL Account 200 for period 1/2023
Granular functions are ideal for consolidating data from multiple companies into one sheet since they give cell-by-cell control.
For GL Account "200" Sales:
Current year turnover: =CXL.GLVal( "ZDEMO01", "200" )Year 2023 turnover: =CXL.GLVal( "ZXERO01", "200", 2023 )
Year 2023 period 1 turnover: =CXL.GLVal( "ZXERO01", "200", 2023, 1 )
YTD 2023 filter by Rep MARK: =CXL.GLVal( "ZXERO01", "200", 2023, 0, "MARK" )
and for the previous year: =CXL.GLVal( "ZXERO01", "200", 2022, 0, "MARK" )
Total turnover 2023 filter by Rep MARK: =CXL.GLVal( "ZXERO01", "200", 2023, 199, "MARK" )
same for periods 5 to 9: =CXL.GLVal( "ZXERO01", "200", 2023, 509, "MARK" )
Period 199 = total for year, 0 = YTD based on current period, 509 = Periods 5 to 9
Get Calculated Totals Net Profit:
Year 2023: =CXL.GLVal( "ZXERO01", "#CalcNetProfit", 2023, 199 )Year 2023, filter by Rep MARK: =CXL.GLVal( "ZXERO01", "#CalcNetProfit", 2023, 199, "MARK" )
Periods 1 to 6: =CXL.GLVal( "ZXERO01", "#CalcNetProfit", 2023, 106, "MARK" )
Periods 7 to 12: =CXL.GLVal( "ZXERO01", "#CalcNetProfit", 2023, 712, "MARK" )
Total Sales: =CXL.GLVal( "ZXERO01", "#SALES" )
Total Sales for MARK: =CXL.GLVal( "ZXERO01", "#SALES", 2023, 199, "MARK" )
Total Overheads: =CXL.GLVal( "ZXERO01", "#OVERHEADS" )
for MARK: =CXL.GLVal( "ZXERO01", "#OVERHEADS", 2023, 199, "MARK" )
Xero Major Headings, QuickBooks Major Headings and Sage Major Headings
Current Balance:
Bank 090: =CXL.GLVal( "ZXERO01", "090" )Total in all Bank Accounts: =CXL.GLVal( "ZXERO01", "#BANK" )
Total Liabilities: =CXL.GLVal( "ZXERO01", "#LIABILITY" )
Currenct + Long Term: =CXL.GLVal( "ZXERO01", "#CalcTotalLiab" )
See Tutorial on CXL.GLVal( CompCode, ... )
Hot Link Multiple Values:
The function "CXL.GLValSpill()" will Hot Link multiple GL values or sections of the chart of accounts. Or the whole income statement with one function. Also, return multiple columns aged by weeks, periods, months, quarters or years.
One function can hotlink the last 12 months of turnover broken down by months very fast from the F.A.S.T database. Optionally filtered by Cost Centres ( AKA Tracking code 1/2 in Xero,Classes and Locations in QuickBooks, and Cost Centres and Departments in Sage Accounting ).
Spill GL Account "200" Sales:
Last 6 months and 2 years summary: =CXL.GLValSpill( "ZXERO01", "200", 2, 6 )and filtered by rep MARK: =CXL.GLValSpill( "ZXERO01", "200", 2, 6, "MARK" )
and aged by Weeks: =CXL.GLValSpill( "ZXERO01", "200", 2, 6, "MARK","","W" )
and aged by Calendar Quarter: =CXL.GLValSpill( "ZXERO01", "200", 2, 6, "MARK","","CQ" )
Spill All Sales GLs:
Last 12 months and 3 years summary: =CXL.GLValSpill( "ZXERO01", "#Sales.+^", 3, 12 )and filtered by rep MARK: =CXL.GLValSpill( "ZXERO01", "#Sales.+^", 3, 12, "MARK" )
and aged by last 10 Weeks: =CXL.GLValSpill( "ZXERO01", "#Sales.+^", 3, 10, "MARK","","W" )
and aged by 6 Calendar Quarters: =CXL.GLValSpill( "ZXERO01", "#Sales.+^", 3, 6, "MARK","","CQ" )
and aged by 6 Accounting Quarters: =CXL.GLValSpill( "ZXERO01", "#Sales.+^", 3, 6, "MARK","","AQ" )
the "." ask for all under the heading, "+" add totals and "^" add period headings
Spill Whole P&L:
Last 12 months and 3 years summary: =CXL.GLValSpill( "ZXERO01", "#ReportPL", 3, 12 )and filtered by rep MARK: =CXL.GLValSpill( "ZXERO01", "#ReportPL", 3, 12, "MARK" )
and aged by weeks: =CXL.GLValSpill( "ZXERO01", "#ReportPL", 3, 12, "MARK","","W" )
and aged by Calendar Quarter: =CXL.GLValSpill( "ZXERO01", "#ReportPL", 3, 12, "MARK","","CQ" )
One function to get the whole P&L/Income Statement for year
Current Balance under GL Heading:
Total Bank Balance: =CXL.GLValSpill( "ZXERO01", "#BANKS" )All Bank GLs Balance with total: =CXL.GLValSpill( "ZXERO01", "#BANKS.+^" )
the "." ask for all under the heading, "+" add totals and "^" add period headings
All Current Bank Balances
Filter out 0 lines: =CXL.GLValSpill( "ZXERO01", "#LIABILITY.+^>" )
Current Liabilities Balances which are not zero
See Tutorial on CXL.GLValSpill( CompCode, ... )
Other useful functions
Get GL Name
Hot Link GL Name: =CXL.GLName( "ZXERO01", "200" )
Get GL Names of the GL Account List
See Tutorial on CXL.GLName( CompCode )
Get Company Status
Get Company Name and Sync Status: =CXL.GetCompanyStatus( "ZXERO01" )
Get Company Sync Status and Current Period
See Tutorial on CXL.GetCompanyStatus( CompCode )
Age Debt and Forecast Payments
Receivable Age Debt age by 7 days in 8 columns: =CXL.AgeDebtTotalSpill( "ZXERO01", "Receivable", 7, 8 )This also returns forecast payments based on overall/contact average pay days
Age Debt over 8 Periods in 7 Days Periods
See Tutorial on CXL.AgeDebtTotalSpill( CompCode, ... )
Get List of data
The CXL.CodesSpill() function Hot Links various lists of information. In the case of the GL Account list, these can be consolidated from multiple companies, and remove any not used for x years.
Consolidated for 2 Companies: =CXL.CodesSpill( "ZXERO01,ZDEMO02", "GLCodesByHead" )
And remove any GLs not used for 2 years: =CXL.CodesSpill( "ZXERO01,ZDEMO02", "GLCodesByHead", "Y2" )
Stock Items: =CXL.CodesSpill( "ZXERO01", "ItemSelectCodes" )
Tracking 1: =CXL.CodesSpill( "ZXERO01", "TRACKING1" )
See Tutorial on CXL.CodesSpill( CompCode, Code )