GL Turn/Bal Spill

GL Period Turnover/Bal Spill

On this Page:: GL Turnover/Bal Spill; Drill Down

The following functions hot link GL Period data, for many periods, to one GL or a Bunch by GL Heading Code/#CategoryCode/#ReportPL/#ReportBal, for a single company, in one call spilling out into adjacent cells.

Click for Xero, QuickBooks or Sage heading list and #CategoryCode.

You will get Excel error "#SPILL!" if the function has not got enough space to write to adjacent cells.

The Wizard "Get GL Values Example Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Get a Income Satement or Balance sheet using excel from Xero or Quickbooks

Get whole Balance Sheet or Income Statement from Xero, QuickBooks or Sage
Unless told otherwise will default to Balances Sheet current period
P&L 12 periods from current period

Get a Revenue from Xero or Quickbooks

Get REVENUE Total for section from Xero, QuickBooks or Sage

Add period headings, using "^"

Get a Income Sattement or Balance sheet using excel from Xero or Quickbooks

Get REVENUE section of Chart of Accounts for the Income Statement from Xero, QuickBooks or Sage
"+" Adds totals, "." Add child GL Accounts, ">" removes any zero lines

Get REVENUE section but in Accounting Quarter "AQ" for Rep "Sam"
with 2 years Totals and 6 Quarters in columns
"+" Adds totals, "." Add child GL Accounts, ">" removes any zero lines

Syntax: =CXL.GLValSpill( CompCode, GLCode, [NoYears], [NoPeriods], [CostCentre],[Department], [Options], [Style], [FromYear], [FromPeriod] )

For a tutorial on this function, click here

Parameters:

Parameter	Description
CompCode	Single Company code Short code assigned by Consolidated XL, or by you to identify your company
GLCode	Gl Code, List of GL comma seperated. g Or Major Heading,Report or Category #ReportBAL or #ReportPL to get Balance Sheet or P&L for one company If Major Heading end in "." then list child accounts, e.g. "#REVENUE." ";" as "." but do not indent Children (QuickBooks)" "#CatSales", "#CatCurLiab" give a all GL and Sections under "+" adds Totals" ">" Filter Zero Value Rows" "^" adds Column headings such as Periods These can be expressed in Options also Click for Xero, QuickBooks or Sage heading list.
[NoYears]	0-5 Number of year total columns to show before periods 102 or 105 wil show YTD 2 years or 5 years If option contains "ShowYTD", then x YTD columns If option contains "ShowBoth", then x Total and YTD columns
[NoPeriods]	Optional, if not specified then returns the Current Balance/YTD figure if NoPeriods=6 then it lists 6 periods from the current Year/Period if FromYear/FromPeriod are set then 6 from specified Year/Period Max value is 3 Years for Weeks (156), 5 years for 12 (60) month periods
[CostCentre]/[Department]	Optional - Filter by Cost Centre/Department AKA as Tracking codes in Xero and Classes/Locations in QuickBooks
[Options]	Optional - Comma delimited list by default it will list Code, Name, and Period figures. "^" or "AddTitle" Adds a title at top listing criteria "AddHeading" adds Column Headings for Periods "RevPeriods" reverse the order of periods "RevYears" reverse Year Total/YTD Columns "+" or "ShowTotal" adds a total at bottom "." or "ShowChildren" if Major heading specified (#REVENUE) then list all accounts under, "#REVENUE." will do same "Indent" indents child accounts (QuickBooks) ">" or "NoZeros" Remove Zero Rows Enable Columns "ShowYTD" Show NoYears YTD Columns "ShowBoth" Show NoYears Total and YTD Columns "NoCode" Hide Code Column "NoDesc" Hide Description Column Period “A” gives by Financial Accounting Period (Default) “AQ” Financial Accounting Period Quarter “CQ” Calendar Period Quarter “C” gives by Calendar Month “W” gives by Calendar Week (Week 1 starting first Monday in year) Reporting Basis: By default the Reporting Basis is Accrual, but is set in the Consolidated XL settings for each company "CASH" will override to Cash Accounting "ACCRUAL" will override to Accrual Accounting Currencies: "GBP", "USD", "EUR" etc will give all figures posted in that Currency "toUSD", "toEUR" will convert all Home currencies to Currency "toUSD202304", "toEUR202304" will convert all Home currencies to Currency as of the end of Year/Month 2023 April Currency Rates are updated every night
[Style]	Optional - Period Headings style
[FromYear]/[FromPeriod]	Specify the point in time to Query

GL Account Drill Down:

Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts:

Visualise your Xero or Quickbooks turnover

Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts
showing the GL Account Turnover in a chart.
The "Trans" menu shows all transactions for the current selected period and GL Account.
Clicking on the chart columns wil drill down to the transactions in each period.