Stock Item Turnover Extraction PRO
The following functions hot link individual Stock Item data by Stock Item 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 "Stock 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 5 Stock Items Turnover with 2 years Totals, and last 6 Periods
Get Top 5 Stock Items Quartly Sales for Rep "Sam".
Items Margin for the last 6 periods.
Get Top 5 Stock Profit for Rep "Sam"".
Stock Items
Get Single Item by Code
Syntax: =CXL.ItemSpill( CompCode, ItemCode, [TypeOfData], [NoYears], [NoPeriods], [Options], [Style], [FromYear], [FromPeriod] )Performance Rated:A+
Get Top items Or By Criteria
Syntax: =CXL.ItemBySpill( CompCode, [TypeOfData], [NoYears], [NoPeriods], [CC_Track1_Class], [Dep_Track2_Loc], [Limit], [Options], [Style], [FromYear], [FromPeriod] , [Filter] )Performance Rated:A+
Spill Functions - One function returns a report
Pull Profit and Loss, Age Debtors/Credits, Stock Turnover, Customer/Vendor Transation report, etc.
| Spill Sales, from COMP_A, For PROD01: | |
| 2 years totals, last 6 months: | =CXL.ItemSpill( "COMP_A", "PROD01", "Sales",2, 6 ) |
| Quantity: | =CXL.ItemSpill( "COMP_A", "PROD01", "Qty",2, 6 ) |
| Quantity in Accounting Quarters: | =CXL.ItemSpill( "COMP_A", "PROD01", "Qty",2, 6,"AQ" ) |
| Quantity in Calander Quarters: | =CXL.ItemSpill( "COMP_A", "PROD01", "Qty",2, 6,"CQ" ) |
| Total + 4 Calander Quarters for 2024: |
=CXL.ItemSpill( "COMP_A", "PROD01", "Qty",1, 6, "CQ", "", 2024 ) |
| Spill Top Stock Sales, from COMP_A, from one function: | |
| 2 years totals, last 6 months: | =CXL.ItemBySpill( "COMP_A", "Sales", 2, 6,"","",20 ) |
| For Rep MARK, in Calander Quarters: | =CXL.ItemBySpill( "COMP_A", "Sales", 2, 6,"MARK","",20,"CQ" ) |
| for specific year 2025: | =CXL.ItemBySpill( "COMP_A", "Sales", 2, 6, "MARK", "", 20, "CQ", "", 2025 ) |
| Qty: | =CXL.ItemBySpill( "COMP_A", "Qty", 2, 6,"MARK","",20,"CQ" ) |
| Margin: | =CXL.ItemBySpill( "COMP_A", "Margin", 2, 6,"MARK","",20,"CQ" ) |
| Stock Sales where sagles GL=200: | =CXL.ItemBySpill( "COMP_A", "Sales", 2, 6, "MARK", "", 20, "CQ,FilterSalesGL", "", 0, 0, "200" ) |
| and in Accounting Quarters: | =CXL.ItemBySpill( "COMP_A", "Sales", 2, 6, "MARK", "", 20, "AQ,FilterSalesGL", "", 0, 0, "200" ) |
| Combine values for parameters: | =CXL.Combine( D3, D4 ) more Combine Examples |
|
Returns "AQ, FilterSalesGL" from cell ref D3 and D4 Takes an array of referances. Thus we can put values in cells, and combine them to make a list of values for CXL function parameters. Blank values are ignores |
|
| to become: | =CXL.ItemBySpill( "COMP_A", "Sales", 2, 6, "MARK", "", 20, CXL.Combine( D3, D4 ), "", 0, 0, "200" ) |
Wizard: "Stock Wizard" 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 |
| ItemCode |
Item Code or Heading "^" adds Column headings such as Periods |
| [TypeOfData] |
For Stock ItemBySpill() "Sales" - gives a list by Highest Sales Turnover for year. Returning: Code,Name,Turnover cols. "Purchases" - gives a list by Highest Purchase Turnover for year. Returning: Code,Name,Turnover cols. "Qty" - gives a list by Sales Qty Turnover for year. Returning: Code,Name,Total Qty cols. "Profit" - gives a list by Sales Profit for year. Returning: Code,Name,Total Profit cols. "Margin" - Profit Margin based on Last Cost. "UnitProfit" - Profit Profit (Profit/Qty). Not analysed by Cost Centre/Department "PurchaseQty" - Purchase Qty. "UnitCost" - Cost of Each unit. |
| [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 "NoYTD" removes YTD Column "NoCode" Hide Code Column "NoDesc" Hide Description Column For Stock Lines ItemSpill() "ShowCurrency" Adds Tax info "ShowExtra" Add Qty, SalesPrice,CostPrice and Last Cost For Stock Lines ItemBySpill() "ShowCurrency" Adds Tax info "ShowPrice" Add SalesPrice "ShowQty" Add Qty column "ShowLastCost" Add Qty column (ItemBySpill only) "ShowYTD" Show NoYears YTD Columns "ShowBoth" Show NoYears Total and YTD Columns Filter "NoZero" or ">" removes zero value rows "FilterGroup" return under Stock Group (QuickBooks first one assinged) "FilterStockValGL" return Stock Valuation GL Code "FilterSalesGL" return Stock Sales GL Code "FilterPurchaseGL" return Stock Purchase GL Code "FilterIsTracked" filter is tracked items "FilterIsNotTracked" filter not tracked items "FilterParent" return Stock under parent Item (QuickBooks) "FilterVendor" return Stock under Vendor 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 |
| [Query] |
Optional Additional criteria see options |
Stock Item Drill Down (Pro)
Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts
showing the Stock Turnover Sale, Profit, Margin, etc in a chart.
The "Trans" menu shows all stock transactions for the current selected period.
Clicking on the chart columns wil drill down to the transactions in each period.