A tool I built for myself: every day a supplier publishes its article list as a CSV file with about 47,000 articles, each with price, stock level, manufacturer and category. The list only ever shows today. This project keeps the history and shows how prices and availability develop per category and manufacturer. It is a Streamlit app in Python with a DuckDB file as its database, in use since November 2026.
Store changes, not lists
Importing 47,000 rows a day would grow the database quickly, and most rows do not change. So the import compares the new list with the current state and writes only what changed: separate history tables for price, stock level, listing and master data, each row valid from a date. An article that leaves the list becomes inactive and is reactivated when it returns. Because "listed or not" has its own history, the analyses know which articles were available throughout a period.
The import is careful about bad input:
- Encoding, delimiter and decimal format are detected automatically.
- A preview runs the import as a dry run and rolls it back; the real import runs in one transaction.
- Faulty rows are skipped and listed instead of stopping the import.
- If more than 10 % of the active articles would drop out, the file is probably incomplete: the app warns, the automatic import refuses it.
- Imports must arrive in date order, and the latest one can be rolled back completely.
Analyses
- Price index per category or manufacturer, starting at 100: the geometric mean of the price ratios (a Jevons index) over a fixed basket of articles that were listed throughout the period, so new and dropped articles do not distort it.
- Comparison between two dates, the biggest price jumps in both directions, and availability classes over time.
- Stock movements: outflow, inflow and balance in units from one list to the next. Outflow is not the same as sales, and above the list's cap of 200 units nothing is visible.
- New arrivals: articles that appeared in a list for the first time within the period, optionally only those that have been in every list since, so items that come and go do not count as new.
- Per article, price and stock as a step chart; per import, lists of new, dropped and returning articles and of every change.
- Full-text search, a category tree as a filter, saved filter presets, and exports to Excel with native charts or to CSV.
Operation
The app runs as a container on my own server behind a reverse proxy with a password. The daily list comes either by upload in the app or automatically by SFTP: a separate, locked-down container accepts only the sender's IP address, only SFTP and only into an inbox folder. A watcher imports a file once it has stopped changing and then deletes it, since its content now lives in the database.
The DuckDB file holds the whole history and cannot be rebuilt from the lists, which are not kept. It is therefore backed up regularly: as consistent copies from within the app, and by a script that pulls the live database to my computer after each upload.
Decisions
- DuckDB instead of a database server: a single file, fast for analytical queries over the history, easy to copy and back up.
- Streamlit for the interface, because the work is in the analyses, not in the web front end. The domain logic has no Streamlit dependency, so the interface could move to another framework later.
- Topic
-
- Data Analysis Collecting data, preparing it and evaluating it to answer questions, for example about prices, markets or competitors.
- Uses
-
- DuckDB Analytical SQL database that runs inside your own program without a server and can query CSV or Parquet files directly.
- Python General-purpose programming language with highly readable syntax. The standard for data analysis, automation and quick prototypes.
- Streamlit Python library that turns a script into a web interface with tables, charts and input fields.
- Tags