Quick Examples

Example of GL functions

On this Page:: Get GL 200; Get Net Profit; Get Current Balance; Spill GL 200; Spill Sales GLs; Spill Whole P&L; Spill Current Balances; Get GL Name; Get Company Status; Get Age Debt; Get List of GLs

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 ).

CXL Company Code

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.

GL Totals

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" )

GL Totals

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" )

CXL Company Code

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

Copy REVENUE
All Current Bank Balances

All Liabilities with total: =CXL.GLValSpill( "ZXERO01", "#LIABILITY.+^" )
Filter out 0 lines: =CXL.GLValSpill( "ZXERO01", "#LIABILITY.+^>" )

Copy REVENUE
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 function
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.

Spill gl Accounts codes to sheet

GL codes sort by headings: =CXL.CodesSpill( "ZXERO01", "GLCodesByHead" )
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 )