📊 Sending KPI Values from Excel to ValueStreamer via Python Script

🔍 Contents

Prerequisites
Authentication
How the Script Works
Example Code
Technical Notes
Tips & Best Practices
FAQ

📋 Prerequisites

Python 3.8+ installed
Installed Python packages:

pip install requests python-dotenv openpyxl
A .env file containing valid API credentials:

API_USERNAME=<Your Username> API_PASSWORD=<Your Password> TENANT=<Your Tenant Name> # e.g., showroom
An Excel file with planned and actual values:
- Planned values in cell F11
- Actual values in cell G11

ℹ️ Note: The script is built for the standard structure in the ValueStreamer showcase.
You may need to adjust cell addresses, KPI IDs, or Team IDs.

🔑 Authentication

Authentication is done using HTTP Basic Auth.
Credentials are loaded from .env and automatically added to the API request header.

Example header:

Authorization: Basic <Base64(API_USERNAME:API_PASSWORD)>
Content-Type: application/vs.v2.0+json

📜 How the Script Works

Load credentials from .env
Read planned/actual values from the Excel file
Convert values to numeric format
Build the target URL for the PUT request
Create the JSON body with kpiValueId and value
Send the PUT request to the KPI API
Print the API response in the terminal

💻 Example Code

# -*- coding: utf-8 -*-
import os, sys, json, datetime as dt, requests
from requests.auth import HTTPBasicAuth
from dotenv import load_dotenv
from openpyxl import load_workbook

# Paths & IDs
BASE_DIR = r"C:\Path\To\API"
EXCEL_PATH = os.path.join(BASE_DIR, "Automate Show Case.xlsx")
ENV_PATH = os.path.join(BASE_DIR, "Zugang.env")
TEAM_ID = "<TEAM_ID>"
KPI_ID = "<KPI_ID>"
KPI_VALUE_ID_PLANNED = "<KPI_VALUE_ID_PLANNED>"
KPI_VALUE_ID_ACTUAL = "<KPI_VALUE_ID_ACTUAL>"
CELL_PLANNED = "F11"
CELL_ACTUAL = "G11"
HEADERS = {"Content-Type": "application/vs.v2.0+json", "Accept": "application/json"}

# Load credentials
load_dotenv(ENV_PATH)
auth = HTTPBasicAuth(os.getenv("API_USERNAME"), os.getenv("API_PASSWORD"))
tenant = os.getenv("TENANT")

# Read Excel values
wb = load_workbook(EXCEL_PATH, data_only=True)
ws = wb.active
planned = float(ws[CELL_PLANNED].value or 0)
actual = float(ws[CELL_ACTUAL].value or 0)

# API URL
date_str = dt.date.today().strftime("%Y-%m-%d")
url = f"https://api-{tenant}.valuestreamer.de/api/exchange/kpi-data/{date_str}/{TEAM_ID}/{KPI_ID}"

# JSON payload
payload = {
"values": [
{"kpiValueId": KPI_VALUE_ID_PLANNED, "value": planned},
{"kpiValueId": KPI_VALUE_ID_ACTUAL, "value": actual}
]
}

# PUT request
resp = requests.put(url, headers=HEADERS, auth=auth, json=payload, timeout=20)
print(resp.status_code, resp.text)

⚠️ Important Notes:

TEAM_ID, KPI_ID, KPI_VALUE_ID_PLANNED, and KPI_VALUE_ID_ACTUAL must match the target KPI in ValueStreamer.
Values will be overwritten if entries already exist for the given date.

📸 Screenshot: Example terminal output upon successful transmission.

📌 Technical Notes

Date format: YYYY-MM-DD
Excel parsing: Script correctly converts formats like 7.777,50 into 7777.50
Timeout: 20 seconds
Error handling: If an HTTP error occurs, the exact API error code is displayed in the terminal

✨ Tips & Best Practices

First run the script in a test/showroom tenant before using it in production
For recurring tasks, schedule the script via Windows Task Scheduler or cron
If sending multiple KPIs, make the KPI_ID and kpiValueId logic modular

❓ FAQ

Can I send multiple KPIs at once?
→ Yes, by adding more entries to payload["values"].

What happens if an Excel cell is empty?
→ The value is sent as 0.0.

Does the .env file need to be in the same folder as the script?
→ Not necessarily — the path can be adjusted in the script.