Transaction Spill PRO

The following functions hot link a list of GL Journals by GL Code, Invoices by Contact Code or Stock Item Lines by Stock Item Code for a single company, in one call spilling out into adjacent cells.

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

   The Wizards "View GL Journals List", "View Invoice List" and "Stock Items Sold List" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Get Journals By GL Code

Get a Balance sheet, with Cr,db and Value columns, using excel form Xero or Quickbooks

Show Journals for GL 200
Click on any CXL figures in the sheet and selecting "Trans" menu will show the same Journals
And allow you to drill down to any Journal Detail

Syntax: =CXL.GLJournalsSpill( CompCode, GLcode, [UptoYear], [UptoPeriod], [CC_Track1_Class], [Dep_Track2_Loc], [NoPeriods], [Limit], [Options] )
Performance Rated:A+

Example

Get Journals for GL/Account "200":
  Journals list from year 2026: =CXL.GLJournalsSpill( "COMP_A", "200", 2026 )
By default returns 1 year for financial year 2026
  only 24 periods back: =CXL.GLJournalsSpill( "COMP_A", "200", 2026, 0, "", "", 24 )
  restrict to last 50 Journals in 2026: =CXL.GLJournalsSpill( "COMP_A", "200", 2026, 0, "", "", 0, 50 )
And by Analysis Codes:
  by Rep MARK: =CXL.GLJournalsSpill( "COMP_A", "200", 2026, "MARK" )
Where MARK is Tracking 1 or Class code
  by Area NORTH: =CXL.GLJournalsSpill( "COMP_A", "200", 2026, "", "NORTH" )
Where NORTH is Tracking 2 or Location code

   Wizard: "View GL Journals List" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Parameters:

Parameter Description
CompCode Single Company code
Short code assigned by Consolidated XL, or by you to identify your company
GLcode/ItemCode/ContactCode GL Code, Stock Code or Contact code to get
anything after "," is ignored
ContactTransSpill() - "Receivable" or "Payable" to get outstanding transactions
[UptoYear]/[UptoPeriod]/[NoPeriods] from UptoYear, get transactions for NoPeriods
NoPeriods default=12, Max=12 (1 year)
[CC_Track1_Class]/ [Dep_Track2_Loc] Optional - Filter by Cost Centre/Department
AKA as Tracking codes in Xero and Classes/Locations in QuickBooks
[Options] Optional - Comma delimited list
"AddTitle" add title on first line
"NoHeading" removes column headings

For GL's GLJournalsSpill()
"ShowTax" Adds Tax info
[FromYear]/[FromPeriod] Specify the point in time to Query
[Limit] Restrict number of lines returned to x
Default/Max=5000
[ExtraFilter] Optional - Extra Filter
Value to filter by

Get Invoices and lines By Contact Code

Get a Balance sheet, with Cr,db and Value columns, using excel form Xero or Quickbooks

Show Invoices for Accounts "PC001"
Optionally show the Invoice lines
Click on any CXL figures in the sheet and selecting "Trans" menu will show the same Transactions
And allow you to drill down to any Transaction Detail

Syntax: =CXL.ContactTransSpill( CompCode, ContactCode, [UptoYear], [UptoPeriod], [NoPeriods], [Limit], [Options] , [ExtraFilter] )
Performance Rated:A+

Example

Get for QuickBooks Vendor transactions with display name "Vendor1", Xero Contact "Vendor1":
  Outstanding Transaction list: =CXL.ContactTransSpill( "COMP_A", "Vendor1", 0 )
  Transaction list from year 2026: =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026 )
By default returns 5 years of data from financial year 2026
  only 24 periods back: =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026, 0, 24 )
  restrict to last 50 transactions: =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026, 0, 0, 50 )
All Outstanding Transactions
  All Debts/Receivables: =CXL.ContactTransSpill( "COMP_A", "Receivable" )
  All Creditors/Payable: =CXL.ContactTransSpill( "COMP_A", "Payable" )

   Wizard: "View Invoice List" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Parameters:

Parameter Description
CompCode Single Company code
Short code assigned by Consolidated XL, or by you to identify your company
ContactCode Xero Contact code, QuickBooks Display Name to get
anything after "," is ignored
if "Receivable" or "Payable" get all outstanding transactions
[UptoYear]/[UptoPeriod]/[NoPeriods] from UptoYear, get transactions for NoPeriods
NoPeriods default=60, Max=60 (5 years)
[CC_Track1_Class]/ [Dep_Track2_Loc] Optional - Filter by Cost Centre/Department
AKA as Tracking codes in Xero and Classes/Locations in QuickBooks
[Options] Optional - Comma delimited list
"AddTitle" add title on first line
"NoHeading" removes column headings

For Invoices ContactTransSpill()
"ShowTax" Adds Tax info
"ShowCurrency" Adds Currency info
Note: Base figures are valued at transaction original exchange Rate "ShowLines" Adds Lines info
[FromYear]/[FromPeriod] Specify the point in time to Query
[Limit] Limit List to x records (5000 is default and max)
[ExtraFilter] Optional - Extra Filter
Value to filter by

Get Stock Lines By Stock Code

Get a Balance sheet, with Cr,db and Value columns, using excel form Xero or Quickbooks

Show Stock Movements for "BOOK"
Click on any CXL figures in the sheet and selecting "Trans" menu will show the same Transactions
And allow you to drill down to any Transaction Detail

Syntax: =CXL.StockLinesSpill( CompCode, ItemCode, [UptoYear], [UptoPeriod], [CC_Track1_Class], [Dep_Track2_Loc], [NoPeriods], [Limit], [Options], [ExtraFilter] )
Performance Rated:A+

Example

Get for Stock/Product lines:
  Lines from year 2026: =CXL.StockLinesSpill( "COMP_A", "PROD01", 2026 )
By default returns last 6 periods of data from financial year 2026
  only 24 periods back: =CXL.StockLinesSpill( "COMP_A", "PROD01", 2026, 0, "", "", 24 )
  restrict to last 50 lines: =CXL.StockLinesSpill( "COMP_A", "PROD01", 2026, 0, "", "", 0, 50 )
And by Analysis Codes:
  by Rep MARK: =CXL.StockLinesSpill( "COMP_A", "PROD01", 2026, "MARK" )
Where MARK is Tracking 1 or Class code
  by Area NORTH: =CXL.StockLinesSpill( "COMP_A", "PROD01", 2026, "", "NORTH" )
Where NORTH is Tracking 2 or Location code

   Wizard: "View Item Sold List" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.

Parameters:

Parameter Description
CompCode Single Company code
Short code assigned by Consolidated XL, or by you to identify your company
GLcode/ItemCode/ContactCode GL Code, Stock Code or Contact code to get
anything after "," is ignored
ContactTransSpill() - "Receivable" or "Payable" to get outstanding transactions
[UptoYear]/[UptoPeriod]/[NoPeriods] from UptoYear, get transactions for NoPeriods
NoPeriods default=6, Max=12 (1 year)
[CC_Track1_Class]/ [Dep_Track2_Loc] Optional - Filter by Cost Centre/Department
AKA as Tracking codes in Xero and Classes/Locations in QuickBooks
[Options] Optional - Comma delimited list
"AddTitle" add title on first line
"NoHeading" removes column headings

For Stock Lines StockLinesSpill()
"ShowCurrency" Adds Currency info
[FromYear]/[FromPeriod] Specify the point in time to Query
[Limit] Restrict number of lines returned to x
Default/Max=5000
[ExtraFilter] Optional - Extra Filter
Value to filter by