bomkit

A Python tool for flattening multi-level bills of materials (BOMs) from Excel files. It combines part quantities across the hierarchy, computing total quantities, minimum purchase quantities, and extended costs. BOM structure can also be exported as a tree for visualization.

The functionality can be accessed in three ways:

Method	Description
Interactive TUI (terminal-based interface)	Launch with bomkit [directory]
API	In Python, use from bomkit import BOM, then BOM.from_folder() or BOM.single_file()
Command line	Run with bomkit [-h] (-f FILE | -d FOLDER) action

Motivation

When the same part appears in multiple sub-assemblies across your BOM, the total quantity you need isn't obvious from any single assembly. Flattening resolves this by aggregating quantities across every level of your product structure.

This is useful for purchasing because many parts are sold in packs larger than one. Knowing the true total quantity lets you calculate the minimum number of packs to buy.

Excel is used as the input format since it's widely available and requires no additional software or server.

Structure

There are two methods for storing data for parts and assemblies: multi-file or single file.

Multi-File

In a separate directory, put an Excel file named Parts List.xlsx to serve as the master parts list "database". Then, each additional assembly is described by a separate .xlsx file. Thus you might have:

my_project/
   Parts list.xlsx     <-- master parts list
   SKA-100.xlsx        <-- top level/root assembly
   TR-01.xlsx          <-- subassembly
   WH-01.xlsx          <-- subassembly

Root and sub-assemblies are inferred from item number relationships and are not required to be explicitly identified.

Parts list.xlsx serves as the single point of reference for part information. For example, it may have the following:

PN	Name	Description	Supplier	Supplier PN	Pkg QTY	Pkg Price
SK1001-01	Deck	Pavement Pro 9" Maple Deck	Grindstone Supply Co.	BRX-02	1	67.95
SK1002-01	Truck	HollowKing Standard Trucks	Grindstone Supply Co.	TR1-A	1	28.95
SK1003-01	Bearing	ABEC-7 Steel Bearings	BoltRun Hardware	74295-942	1	9.95
SK1004-01	Wheel	SlickCore 54mm Cruiser Wheels	Grindstone Supply Co.	WHL-PRX	4	44.95
SK1005-01	Screw	10-32, 1", Phillips	BoltRun Hardware	92220A	25	12.49
SK1006-01	Nut	10-32	BoltRun Hardware	95479A	25	9.89
SK1007-01	Grip Tape	SuperStick 9"	BoltRun Hardware	GTSS99	1	8.95

For each assembly, all that is required is the part identification number and quantity which correspond to the following fields:

• PN
• QTY

Example wheel assembly (1 wheel + 2 bearings):

PN	QTY
SK1004-01	1
SK1003-01	2

Certain fields are used in calculating totals, such as in BOM.BOM.summary, which are:

Pkg QTY : The quantity of items in a specific supplier SKU (i.e. a bag of 100 screws)

Pkg Price : The cost of a specific supplier SKU

Single File

A single Excel file is used to store all part and assembly data through the use of Excel tabs.

The conventions for the single file approach are the same as the multi-file approach, with the following exceptions:

• The first (left-most) Excel tab is treated as the Parts List "database", regardless of its name
• All tabs/sheets to the right are interpreted as assemblies, with the sheet name as the assembly part number (PN)

Installation

Install with pip:

pip install bomkit

Usage

Set up your data with either the multi-file or single file approach.

TUI Browser

In a terminal, browse to the folder containing your BOM files and issue the command bomkit with no arguments (or issue the path to your directory, e.g. bomkit /path/to/your/project). This will cause it to enter the browser mode where you can interact with your BOM hierarchy and view derived properties such as the aggregated parts list and tree structure.

The default screen shows the top-level assembly and its direct-child parts and assemblies. You can navigate down the hierarchy with the ⬆️ and ⬇️ arrow keys and by selecting an assembly and pressing Enter to view its child parts and assemblies. Pressing Enter on a part will show its details. Use the left arrow key ⬅️ or Esc to return to the parent assembly. You can also access different views and derived properties using the command keys listed at the bottom of the screen, such as t for a tree view. Assemblies are shown in cyan and bold text. The top row shows a breadcrumb of the current location in the BOM hierarchy.

The commands at the bottom of the screen are:

• t for a tree view of the full BOM hierarchy
• p for a part list view (all the parts in the Parts List file
• a for a list of all the assemblies in the BOM
• s for a summary view (aggregated parts list with total QTY and purchase QTY)

API Usage

from bomkit import BOM

# Multi-file
bom = BOM.from_folder(FOLDER)

# Single file
bom = BOM.single_file(FILENAME)

This returns a BOM object with properties on it you can retrieve:

BOM.parts : Get a list of all direct-child parts

>>> print(bom.parts)
[Part SK1001-01, Part SK1005-01, Part SK1006-01, Part SK1007-01] 

BOM.assemblies : Get a list of all direct-child assemblies

>>> print(bom.assemblies)
[TR-01]

BOM.aggregate : Get the aggregated quantity of each part/assembly from the current BOM level down

>>> print(bom.aggregate)
{'SK1001-01': 1, 'SK1005-01': 8, 'SK1006-01': 8, 'SK1007-01': 1, 'SK1002-01': 2, 'SK1004-01': 4, 'SK1003-01': 8}

BOM.summary : Get a summary in the form of a DataFrame containing the master parts list with each item's aggregated quantity and the required packages to buy (Purchase QTY) if the Pkg QTY field is not 1.

>>> print(bom.summary)
        PN       Name                    Description  ... Total QTY Purchase QTY  Subtotal
0  SK1001-01       Deck     Pavement Pro 9" Maple Deck  ...         1            1     67.95
1  SK1002-01      Truck     HollowKing Standard Trucks  ...         2            2     57.90
2  SK1003-01    Bearing          ABEC-7 Steel Bearings  ...         8            8     27.92
3  SK1004-01      Wheel  SlickCore 54mm Cruiser Wheels  ...         4            1     44.95
4  SK1005-01      Screw            10-32, 1”, Phillips  ...         8            1     12.49
5  SK1006-01        Nut                          10-32  ...         8            1      9.89
6  SK1007-01  Grip tape                  SuperStick 9”  ...         1            1      8.95

BOM.tree : Return a string representation of the BOM tree hierarchy

>>> print(bom.tree)
SKA-100
├── Part SK1001-01        
├── TR-01
│   ├── Part SK1002-01    
│   └── WH-01
│       ├── Part SK1004-01
│       └── Part SK1003-01
├── Part SK1005-01        
├── Part SK1006-01        
└── Part SK1007-01  

Calling this on child assemblies shows the tree from that reference point:

>>> bom.assemblies
[TR-01]
>>> print(bom.assemblies[0].tree)
TR-01
├── Part SK1002-01
└── WH-01
  ├── Part SK1004-01
  └── Part SK1003-01

Command Line Usage

Functionality is extended to the command line where the flags -f and -d are used to specify the name of a file for single-file mode or a folder for multi-file mode, respectively.

action is what to do with the imported data, which just maps to a property on the top-level BOM object.

This method is not persistent and is meant for quick one-off retrieval of information.

> bomkit [-h] (-f FILE | -d FOLDER) action

> bomkit -d Example tree
SKA-100
├── Part SK1001-01        
├── TR-01
│   ├── Part SK1002-01    
│   └── WH-01
│       ├── Part SK1004-01
│       └── Part SK1003-01
├── Part SK1005-01        
├── Part SK1006-01        
└── Part SK1007-01 

Dependencies

• pandas
• anytree
• openpyxl
• textual