Customer/Vendor Turnover PRO
The following functions hot link individual Customer/Vendor data by Customer/Vendor Code, or by criteria, for many periods, 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 Wizard "Customer/Vendor Wizard" by Code or Criteria shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.
Get Top 10 Customers Turnover with 2 years Totals, and last 6 Periods
Get Top 10 Vendors Turnover for last 6 Quarters.
Contacts
Get Single Contact by Code
Syntax: =CXL.ContactSpill( CompCode, ContactCode, [TypeOfData], [NoYears], [NoPeriods], [Options], [Style], [FromYear], [FromPeriod] )Performance Rated:A+
Get Top Contact Or By Criteria
Syntax: =CXL.ContactBySpill( CompCode, [TypeOfData], [NoYears], [NoPeriods], [Limit], [Options], [Style], [FromYear], [FromPeriod] , [Filter] )Performance Rated:A+
And By Filter by Cost Centre/Department
Sorry we don't support CC/Dep tracking on Customer/Vendor data as it could create a lot of data which most people would not need.
Example
| Spill Sales, from COMP_A, for customer CUST01: | |
| 2 years totals, last 6 months: | =CXL.ContactSpill( "COMP_A", "CUST01", "Sales",2, 6 ) |
| Top 20 Customers, from COMP_A: | |
|
2 years totals, last 6 months sort on array column 5 and 4 Desc: |
=CXL.ContactBySpill( "COMP_A", "Sales",2, 6, 20 ,"5,4" ) |
| and for year 2024: |
=CXL.ContactBySpill( "COMP_A", "Sales",2, 6, 20 ,"5,4", "", 2024 ) |
| end of 2024 in Calander 6 Quaters: |
=CXL.ContactBySpill( "COMP_A", "Sales",2, 6, 20 ,"CQ,5,4", "", 2024, 4 ) |
| end of 2024 in Calander 12 Months: |
=CXL.ContactBySpill( "COMP_A", "Sales",2, 12, 20 ,"C,5,4", "", 2024, 12 ) |
| Top 20 Ventors, from COMP_A: | |
|
2 years totals, last 6 months sort on array column 5 and 4 Desc: |
=CXL.ContactBySpill( "COMP_A", "Purchase", 2, 6, 20 ,"5,4" ) |
Wizard: "Vandor/Customer Turnover" 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 |
Contact Code "^" adds Column headings such as Periods |
| [TypeOfData] |
For Cust/Vendor ContactBySpill() "Sales" - gives a list by Highest Sales Turnover for year. Returning: Code,Name,Turnover cols. "Purchase" - gives a list by Highest Purchase Turnover for year. Returning: Code,Name,Turnover cols. |
| [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] |
By default it will list YTD 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 |
| [CC_Track1_Class]/ [Dep_Track2_Loc] |
Optional - Filter by Cost Centre/Department AKA as Tracking codes in Xero and Classes/Locations in QuickBooks |
| [Limit] |
Default=10 (Max = 500) |
| [Options] |
Optional - Comma delimited list by default it will list Code, Name, and figures. "AddTitle" Adds a title at top listing criteria "NoHeading" adds Column headings such as Periods "RevPeriods" reverse the order of periods "RevYears" reverse Year Total/YTD Columns "=" or "ShowTotal" adds a total at bottom Enable Columns "NoCode" Hide Code Column "NoDesc" Hide Description Column "ShowYTD" Show NoYears YTD Columns "ShowBoth" Show NoYears Total and YTD Columns For Stock Lines ContactBySpill() "NoZero" or ">" removes zero value rows "FilterGroup" return under Contact Group (Xero - first one assinged) "FilterSalesGL" return Contact Sales GL Code "FilterPurchaseGL" return Contact Purchase GL Code "FilterCurrency" return Contact Currency Code 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) 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 Sort By default is order by descending order of the value of Sales or what ever asked for Year asked for "SortByName" or "SortByCode" will overide this or "5" will sort by column 5 descending "5,4" will sort by column 5 then 4 descending "5,4,Asc" will sort by column 5 then 4 ascending 1 being the first column returned in array |
| Style |
Optional - Period Headings style |
| FromYear/FromPeriod |
Specify the point in time to Query |
| Limit |
Limit List to x records (500 is max) |
| Query |
Optional Additional criteria see options |
Customer Turnover Drill Down (Pro)
Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts
showing the Contact Turnover in a chart.
The "Trans" menu shows all outstanding transactions for the current selected period.
Clicking on the chart columns wil drill down to the transactions in each period.