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 Customer/Vendor trunover using excel form Xero or Quickbooks

Get Top 5 Stock Items Turnover with 2 years Totals, and last 6 Periods

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

Get Top 5 Stock Items Quartly Sales for Rep "Sam".


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

Items Margin for the last 6 periods.


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

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], [CostCentre], [Department], [Limit], [Options], [Style], [FromYear], [FromPeriod] , [Filter] )
Performance Rated:A+

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
[CostCentre]/[Department] 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

"FilterGroup" return under Stock Group (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
[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

Stock Item Drill Down (Pro)

Stock Turnover Charts

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.