The Spreadsheet/Dashboard Tradeoff

Recently I have been seeing on medium articles extolling the great libraries there now are that let you ingest spreadsheet data and do concise calculations using pandas, numpy, scipy, and the like, suggesting that a spreadsheet is no longer needed. But I find that the authors are missing an issue with that approach. That is superb if you are facile with pandas, numpy, and scipy -but what if you have to consult the calculations and are not?

The idea to a spreadsheet is to allow a reactive view to calculations with -very important point- a minimum of programming knowledge.

When the output of numpy/scipy calculations are rendered in a dashboard, as opposed to a sheet or workbook (a collection of related sheets…), although dashboards have come a long way, offering nifty filtering and sorting capabilities, they still fail when they do not explain how the calculations are composed in a way that a non-programmer can follow. Yes: you as a programmer could add those explanations to the interface; but now you are increasing the complexity and time of development, and also inviting opportunities for the explanations to be out of sync with what you calculated. A much larger population of power users who lack programming knowledge can examine spreadsheet formulas and functions and understand how the calculations were made; the formulas and functions insure that explanation and serve to document the calculations (especially if descriptive intermediary range names are used…)

So for dashboards, nothing compares to a spreadsheet/workbook. Fortunately there are now modules that let you fabricate an Excel spreadsheet/workbook from python (openpyxl is a good one that comes to mind, and you won’t even need an Office license for the processes generating them…)

Specialization in spreadsheet application development

I would also like to discuss a poor practice that I am seeing many large companies engage in. An older model of development in finance relied on the time-tested model of specialization. You would have a “quant” who focused on the calculations underlying a workbook (they could compose complex automated sheets that performed data ingestion, but it wasn’t their forte…), and another developer who concentrated on the algorithmic automated integration with databases and information services. The two worked side by side (or can often do so nowadays remotely…) and insured the highest quality software product. And people using the workbook could easily rejigger a calculation if need be without extensive programming knowledge -which you can’t do with pandas/numpy/scipy/whatever that happens to be wired up to a dashboard.

Due to an increased cost of specialized tech labor (due to the increased demands being made of it…), a too-clever-by-half approach to spreadsheet or dashboard development has been to get employees who now wear two hats: ones that possess and work on the often complex quantitative aspects of a model expressed in a spreadsheet or dashboard, and also simultaneously possess a wide knowledge of technical facilities to implement that approach and integrate it with far-flung data sources. But both the quantitative and programmatic implementation aspects of the workbook suffer from a “Least Common Denominator” approach. Perhaps the C-suite would like to believe there are quantitative software developers on top of their game; but I have rarely seen anyone match what a two-person team of specialists can do (or two teams for that matter: one focusing on the quantitative design -staying up on the latest theories and mathematics- and the other focusing on programmatic implementation -using the most cost-effective information APIs and algorithmic techniques…) A one-man-band approach might be suitable for a small company; but it’s not for an enterprise with various special needs and requirements.

For example, personally I have had to come in on voluminous spreadsheet codebases where they were not done by software-proficient people, who lacked a “Murphy’s Law” design principle -designing the code to be bulletproof from all circumstances, including those that in theory should never happen. When the stars aligned, the numbers calculated correctly. But one adjustment to ingested data -and it was a mess. This is what you get with a one-man-band LCD approach; you think it will save you money -it won’t! Well, it might save you money at the expense of the quality of your product(s).

Microsoft Excel versus LibreOffice Calc as a spreadsheet dashboard

I would very much like to see better integration of python with LibreOffice Calc (and Calc does not require a cloud instance…) In terms of generating formulas and charts, the capabilities are more constrained in Calc than they are using openpyxl -I often just generate an Excel format book with openpyxl knowing that LibreOffice can usually handle it, and check it afterwards.

As you might have seen the memos, Microsoft has recently decreased the comfort level of the operating system experience for Excel, Windows 11:

• forced updates that can mess you up on a deadline (this started in Windows 10…);
• forced increased cost for AI functionality you didn’t ask for (fine if they let you easily switch it off and make the cost elective…); and
• advertising in an operating system you already paid for!

Add to that: the python integration in Excel has a big gotcha -it can only work via the Internet, connected to an Azure cloud instance. On a flight with spotty WiFi and you want to work on your projections before landing for that meeting? Hurts to be you! There was just no good reason to FORCE that Azure dependency, other than for Microsoft to try and puff up their Azure numbers. They clearly could have designed it to interoperate with a local python install.

So if LibreOffice Calc would offer much better python integration with a local python instance, it would be my go-to for a dashboard. If there were better python integration in Calc, it could only serve to stimulate further improvements to Calc.

Note that the mark of good python integration would be if a “macro recorder” could easily assemble the python you need to generate a desired workbook (Microsoft Excel, as of this writing, still can’t do that…)

Hopefully this article might get the LibreOffice team to consider improving their integration with python, now that there is greater interest in the suite thanks to Microsoft making their platform fairly unlivable. Until there is good python integration for Calc, if I am on Linux I install Windows 10 and Excel/Office (for security reasons WITHOUT communication or browser apps…) in vmware (FREE and functionally replete!) and use Excel that way. I also make sure the Linux’s firewall is enabled (it should be anyway…) and set up a share in Windows for my Linux communication apps to easily retrieve and save workbooks to the Windows instance’s Documents folder.

Set up Excel to use Wine on Linux? It doesn’t work with recent Office versions, I imagine due to Microsoft anti-pirate protections. Most people who beckon others to do this imply that an old version of Excel will be adequate -for many shops it will not be. And there are other Windows VM solutions on Linux, but they are not as easy-to-use as vmware. Dual-boot Linux and Windows? As somebody who has tried and occasionally revisited it for years, I find the day comes when a Windows update screws around with the Linux boot and makes it unbootable -accidentally on purpose, to discourage you from using Linux? They used such a tactic once before (https://en.wikipedia.org/wiki/AARD_code)