# ERP KPI Snapshot to Excel Python Script Generator

> Source: https://netray.co/tools/erp-kpi-snapshot-python-export-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your CSV file name and column names to get a short pandas script that computes KPIs and saves a dated Excel snapshot.

## Category

developer

## Audience

- ERP analyst
- Controller
- Report developer

## Tool

**Kind:** template

**Output:** code

**Language:** python

**Fields:**

#### Order lines CSV file

**Key:** csv_file

**Type:** text

**Default:** order_lines.csv

#### Promise date column

**Key:** promise_col

**Type:** text

**Default:** promise_date

#### Ship date column

**Key:** ship_col

**Type:** text

**Default:** ship_date

#### Line value column

**Key:** value_col

**Type:** text

**Default:** line_value

#### Plant column

**Key:** plant_col

**Type:** text

**Default:** plant

#### Grace days allowed

**Key:** grace

**Type:** number

**Default:** 0

#### Output file prefix

**Key:** out_prefix

**Type:** text

**Default:** kpi_snapshot

**Blocks:**

#### Python script

**Body:** # Requires: pip install pandas openpyxl
import pandas as pd
from datetime import date

SOURCE = '{{csv_file}}'
GRACE_DAYS = {{grace}}

df = pd.read_csv(SOURCE, parse_dates=['{{promise_col}}', '{{ship_col}}'])

# Only lines that have shipped count towards on time delivery
shipped = df[df['{{ship_col}}'].notna()].copy()
shipped['late_days'] = (shipped['{{ship_col}}'] - shipped['{{promise_col}}']).dt.days
shipped['on_time'] = shipped['late_days'] <= GRACE_DAYS

# Recompute from totals per plant, do not average percentages
by_plant = shipped.groupby('{{plant_col}}').agg(
    lines=('on_time', 'size'),
    on_time_lines=('on_time', 'sum'),
    shipped_value=('{{value_col}}', 'sum'),
)
by_plant['otd_pct'] = (by_plant['on_time_lines'] / by_plant['lines'] * 100).round(1)

total_lines = int(by_plant['lines'].sum())
total_on_time = int(by_plant['on_time_lines'].sum())
company_otd = round(total_on_time / total_lines * 100, 1) if total_lines else 0.0

summary = pd.DataFrame({
    'KPI': ['On time delivery percent (lines)', 'Shipped lines', 'Shipped value'],
    'Value': [company_otd, total_lines, float(by_plant['shipped_value'].sum())],
})

out_name = '{{out_prefix}}_' + date.today().isoformat() + '.xlsx'
with pd.ExcelWriter(out_name, engine='openpyxl') as writer:
    summary.to_excel(writer, sheet_name='Summary', index=False)
    by_plant.reset_index().to_excel(writer, sheet_name='By plant', index=False)
    shipped.sort_values('late_days', ascending=False).head(50).to_excel(writer, sheet_name='Worst 50 late lines', index=False)

print('Wrote', out_name)

**Notes:** Export order lines from your ERP as CSV with a header row and ISO style dates, then check that date columns parse correctly. Treat the result as a starting point, test it against a manually counted sample, and keep exports on a secure location since they may contain customer data.

## Content

**Intro:** Many controllers rebuild the same KPI spreadsheet each month from an ERP export. This generator writes a short Python script that reads your CSV, calculates on time delivery by plant and saves a dated Excel file, so the monthly job runs in seconds.

**Sections:**

#### How to use the generator

**Body:** Export shipped and open order lines from your ERP into CSV, then enter the file name and the names of the promise date, ship date, value and plant columns. Copy the script, install pandas and openpyxl, and run it in the same folder as the CSV. It writes a workbook with a summary, a plant table and the 50 latest lines for follow up.

**Bullets:**

- Install pandas and openpyxl with pip
- Keep column names exactly as in the CSV header
- Set grace days to match your contract terms

#### The calculation

**Body:** A shipped line is on time when the ship date minus the promise date is no more than the grace days. The script counts lines per plant and recomputes the percentage from totals. The company figure is built from summed lines, so large and small plants are weighted correctly instead of averaging plant percentages.

#### Extending it

**Body:** Add more KPIs the same way: read another export, group, compute from totals and write a sheet. Schedule it with Windows Task Scheduler or cron after the nightly export. If you need quantity based or customer date based on time delivery, change the date columns and the filter rather than the structure.

#### Common mistakes

**Body:** Including unshipped lines in the denominator, comparing text dates instead of parsed dates, and mixing partial shipments are the usual errors. Decide whether a partially shipped line counts on the first shipment or the last, and make the export match that rule.

**Cta:** If scripts like this multiply, Netray can provide a governed, read-only reporting layer on your ERP data so KPIs are not rebuilt by hand each month.

**Faq:**

#### Do I need to know Python?

**Answer:** Not much. Install Python, install the two libraries, save the script as a file and run it. Changing column names and file names is done in the generator before copying.

#### Does it connect to my ERP?

**Answer:** No. It reads a CSV export, so it needs no database access or credentials. If you later connect to a database, keep it to a read-only account.

#### Why recompute from totals?

**Answer:** Averaging plant percentages gives a small plant the same weight as a large one. Summing on time lines and total lines first gives the correct company figure.

#### Can I use it for other KPIs?

**Answer:** Yes. Replace the on time logic with another formula, such as scrap rate from good and scrap quantities. The group, total and write structure stays the same.

## Search terms

python script erp csv to excel, kpi snapshot python, pandas kpi report script, automate monthly kpi report python, erp export csv python, on time delivery python pandas, excel report automation python

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json