All Collections
Updating stock levels and stock values in bulk
Updating stock levels and stock values in bulk

You can update stock levels and values through a spreadsheet import

Dayvid Lorbiecke avatar
Written by Dayvid Lorbiecke
Updated over a week ago

You can update stock quantities and stock values for products and materials in bulk via a spreadsheet import.

You can also follow this guide for adding initial stock or check out this guide.

The most typical use case is recording the results of stocktakes (i.e. stock counts) in Katana.

If you are using batch tracking for your items (a Standard plan or higher feature), you can also use the following approach for updating your stock levels and values for batches in bulk. See more information about updating stock for batches in the dedicated batch tracking section below.

You can choose to update stock levels only by keeping the current Average cost for items or update both stock quantities and values (which might result in a changed Average cost as a result) with the template. Choose the preferred approach before confirming the import when uploading your data:

  • By default, Katana will only update stock quantities from the file and keep the current Average cost for items in Katana. In this case, you do not need to change the "Value in stock" column in the template. This is a typical use case for stock takes.

  • You can update quantities In Stock and Value in stock, which might result in a changed Average cost for items. Average cost is a calculated field in Katana as Average cost = Value in Stock / Quantity In stock. When you start uploading the file back into Katana, you can choose to import the Value in stock column from the file.

  • Update Value in stock only and keep the current quantity In stock. This will result in a changed Average cost for items.

To update stock levels in bulk (no batch tracking):

  1. Navigate to Settings screen > Data import in Katana.

  2. Find the Update stock levels and stock values section and select Download data.

  3. A spreadsheet (XLSX format) that includes all your products and materials with current Katana "In stock" levels and "Value in Stock" will be downloaded. This template includes a Katana ID column, which will be used for matching the imported items with items existing in Katana.

    If you created multiple Locations in Katana, the template will include a separate row for each Location of each item. For example, if you have 3 Locations in Katana, there will be 3 rows for the same item in the file, which enables you to import stock levels for each Location.

  4. Make the preferred changes to the Stock quantity and Value in Stock columns.

    - "Stock quantity" is the "In stock" quantity for the item in Katana.

    - "Value in stock" is the total stock value for the entire Stock quantity. The Average cost for the item is calculated in Katana as "Value in stock" divided by the "In stock" quantity.

    - Stock quantities and values in the spreadsheet are shown per Location. If you created more than one Location in Katana, each row in the file is per Location of each product.

    - If you use batch tracking for your items (a Standard or higher plan feature), the file will include your batch information. See more details below.

    - Only Stock Quantities and Values are imported to Katana. Any changes to Item names, Variant codes, Item variant columns, or Unit of Measure will not be imported. These columns are included in the spreadsheet purely for better usability purposes.

  5. Import the XLSX file back into Katana by selecting Upload data next to the "Update stock levels and stock values" in the "Import data" section. You can change the file name before import if necessary (it does not have to match the name of the downloaded template file).

  6. Before proceeding with the import, Katana will ask whether you wish to use the "Value in stock" from the file or keep the Average cost for items in Katana. By default, Katana will keep the Average cost, so if the quantity for the item is changed, the total Value in stock for that item is changed accordingly as the Average cost is not changed.

If the Value in stock column is empty in the imported file, Katana will not ask this question and will update the In stock quantity in Katana by keeping the existing Average cost.

If the Average cost and In stock quantity for items is zero in Katana (for example, when entering the initial stock), Katana will use the Default purchase price for items as the Average cost if you have the price defined in Katana.

If you opt into recalculating the Average cost in Katana using the new Value in stock in the file, Katana will import the "Value in stock" from the file and your Average cost for items might change.

Stock adjustment(s) will be created (one Stock adjustment per Location) to record the differences between the imported Stock quantities and Values and your existing stock in Katana.

Tip: Creating .xlsx on Mac. Open the spreadsheet in Numbers and choose File -> Export to -> Excel... from the menu at the top of your screen.

Tip: Creating .xlsx in Google Sheets. Open the spreadsheet in Google Sheets and choose File -> Download as -> Microsoft Excel (.xlsx).

Rules applied to imports

  1. Imported items are matched to existing items in Katana by the Katana ID included in the spreadsheet. This ID is not visible inside Katana and is purely used for matching items.

  2. Imported products with no "Katana ID" and "Location" match with Katana will be skipped during import.

  3. Stock Quantities and Value in Stock for items are overwritten on a per Location basis.

  4. Empty cells are ignored during import. Existing data in Katana will not be overwritten with blank values.

  5. If you only change the "Value in stock" for an item, but do not change the Quantity, on the Stock Adjustment that is created in Katana for recording the differences, Katana will create 2 adjustment rows for the item: one with quantity adjustment -1 and the other with quantity adjustment +1. This is necessary for recording the value differences in Katana.

To update stock levels in bulk (with batch tracking):

If you are using batch tracking (a Standard or higher plan feature) in Katana, you can use the same approach as described above (all the same rules apply), but there are a few additional things to note.

  • If you enabled batch tracking for some, but not for all items in Katana, the exported file will still include stock levels for all items.

  • For batch-trackable items, each row in the file is a specific batch at a specific Location with the batch number and expiration dates indicated in the respective columns.

  • For items that are not batch-trackable, each in the file is the total stock quantity for the item at a specific Location (no batch info).

  • The exported file will also include batches with 0 quantity and "Unbatched" batches (except "Unbatched" batches with 0 quantity).

  • You can remove the entire quantity from any batch (including the "Unbatched" batch) by changing the Stock Quantity and Value in Stock to 0 in the file.

  • You can assign Value in Stock per batch in the file. However, Average cost in Katana is not batch-specific. After importing the batches with varying Average Costs for the same item variant, Katana will calculate the Average cost for the total quantity of the item variant. This cost is assigned to the entire stock for that variant.

  • You can only update stock quantities and values for existing batches with this template. If the item is batch-trackable, but the "Batch number" field in the file is empty of unknown (not matching any existing batches in Katana), then this row is skipped during import. You cannot import new batches with this file. Use the following tutorial for importing new batches.

Did this answer your question?