> You could argue that I'm using the wrong tool for the job.
Without comparing Excel vs LibreOffice vs etc, I've found the category of spreadsheet software to be immensely helpful for analyzing, aggregating, visualizing and reducing small datasets.
You do have to end up falling back to python scripts using pandas or whatever if you need to run jobs that need to take data from one DB and put it into another DB or something like that.
But if my output is basically a reduced set of tables, series or graphs for presentation, spreadsheets are an immensely useful tool.
What I've specifically done is have one sheet of the spreadsheet represent the "raw, unreduced data", and write up formulae for aggregation in other sheets, based on the raw data and intermediate aggregations. Starts becoming less ideal when you have 10 or more sheets, but for simple cases, it's very much underrated by us engineering folks.
I always found that excel can be as simple and as complex as you need it to be. You can use it to write your shopping list (ok, not ideal), or a small budget, and you can ramp it up for really complex simulations. Once you know vlookup, indirect/match, sumif/countif and very few other formulas (for example text manipulation to clean and harmonise data inputs), you can do really interesting stuff even with datasets of thousands of lines.
I found that often in excel the most difficult stuff is not the back-end (data analysis and simulation) but the front end (i.e. giving the user a clear interface for inputing data and conditions in the model, and visualising outputs, for example with buttons, constrained inputs, conditional formatting etc.).
Why not ideal for shopping list? When we go skiing with a couple of friends, we setup a long shopping list in excel, there each row has the name, category, amount and amount unit.
Before heading into the store we have a Pivot table sum it all up, group everything by category sorted by the order those categories are encountered when walking though the store.
This way it takes 2-3 people less than an hour to shop for a 4 day trip for 10-20 people.
One holds the list, and then 1 or two runners go fetch stuff.
Hats off for this. I was thinking more about as a substitute of a simple paper list (or if on mobile, just a list on whatever default note app is you have in iOS or Android)
>What I've specifically done is have one sheet of the spreadsheet represent the "raw, unreduced data", and write up formulae for aggregation in other sheets, based on the raw data and intermediate aggregations
This is exactly what I do. When it works, it is just way too fast for ad-hoc reporting to consider doing anything else. Pretty much all of my first draft reporting is built this way, so i can cut through the back-and-forth of management change requests before going through the trouble of setting up a full thing.
It is amazing how much you can accomplish with appending a couple columns to your raw data for categorization and then pivoting.
when im in a rush and need another layer (pivottable of a pivot table), I even set up formulas to reference a produced pivottable and then pivot on the formulas. You can extend the formulas far beyond expected use-cases and filter out the irrelevant rows later
Without comparing Excel vs LibreOffice vs etc, I've found the category of spreadsheet software to be immensely helpful for analyzing, aggregating, visualizing and reducing small datasets.
You do have to end up falling back to python scripts using pandas or whatever if you need to run jobs that need to take data from one DB and put it into another DB or something like that.
But if my output is basically a reduced set of tables, series or graphs for presentation, spreadsheets are an immensely useful tool.
What I've specifically done is have one sheet of the spreadsheet represent the "raw, unreduced data", and write up formulae for aggregation in other sheets, based on the raw data and intermediate aggregations. Starts becoming less ideal when you have 10 or more sheets, but for simple cases, it's very much underrated by us engineering folks.