4 ms·

All data states and all logic are in one file

cpeterso 7y ago
But spreadsheet logic is buried unseen in cells and spreadsheets don't have good version control for reviewing new changes and auditing change history.
- thedudeabides5 7y ago
  Yes, but to do good version control on code based systems you need to version both the code, and the data being run through the pipes. While there are lots of good tools to version code (git etc), and lots of tools to version control data (bitemporality being the usual requirement), does anyone know a tool which allows you to do both, at the same time?
  - heavenlyblue 7y ago
    Git LFS?
- paulfitz 7y ago
  Grist spreadsheets have a page where you can review all formulas as code https://support.getgrist.com/formulas/#code-viewer https://support.getgrist.com/formulas/#code-viewer - I find this handy when figuring out someone else's spreadsheet, or my own once I've forgotten how they work, and would love to see this become a standard spreadsheet feature (disclosure: I work on Grist)
  - heavenlyblue 7y ago
    But I can review my code as code?
- skylanh 7y ago
  As someone that spent many countable (billable) hours doing Excel audits--yes there is. You build them yourself. The fallacy that you're bringing to Excel is pretending that it's a "all-and-be-all" in a package. It's a tool with an embedded programming environment. Much like you'd take a basic Python install, and add on additional tools, the same applies to Excel. The embedded programming language (VBA) is a fantastic language--it has absolutely no libraries out of the box which makes it a poor development /environment/. To directly address your question: - include a "version" cell on a "Config" worksheet (optionally include values for "audited by", "audited date", "audit version", "audit type", note this could be a multirow, so you could actually specify "full" audits, and mini audits) - build a function that maps all formulas to an "Audit" worksheet - build a function to export to CSV with the date and version tagged - build a function to export all code modules to text files - create whatever release-versioning you want around this: zip of exports + "deployment" XLS, GIT repo with tagged version and exports and a binary blob of the XLS, etc, etc. (Edit: workbook vs worksheet; each tab in a workbook is a worksheet.)
joe_the_user 7y ago
Like a lot of things, I think you have two part here. A small spread can be more understandable than a small program because you have all the logic apparently visible. A large spreadsheet can become harder to understand and verify than the medium-sized program that could duplicate it's logic.
- mxschumacher 7y ago
  Yes. I wonder whether it is feasible to run unit tests in spreadsheets: they are essentially long chains of functions. Debugging a larger sheet seems really tough.

But spreadsheet logic is buried unseen in cells and spreadsheets don't have good version control for reviewing new changes and auditing change history.

thedudeabides5 7y ago
Yes, but to do good version control on code based systems you need to version both the code, and the data being run through the pipes. While there are lots of good tools to version code (git etc), and lots of tools to version control data (bitemporality being the usual requirement), does anyone know a tool which allows you to do both, at the same time?
- heavenlyblue 7y ago
  Git LFS?
paulfitz 7y ago
Grist spreadsheets have a page where you can review all formulas as code https://support.getgrist.com/formulas/#code-viewer https://support.getgrist.com/formulas/#code-viewer - I find this handy when figuring out someone else's spreadsheet, or my own once I've forgotten how they work, and would love to see this become a standard spreadsheet feature (disclosure: I work on Grist)
- heavenlyblue 7y ago
  But I can review my code as code?
skylanh 7y ago
As someone that spent many countable (billable) hours doing Excel audits--yes there is. You build them yourself. The fallacy that you're bringing to Excel is pretending that it's a "all-and-be-all" in a package. It's a tool with an embedded programming environment. Much like you'd take a basic Python install, and add on additional tools, the same applies to Excel. The embedded programming language (VBA) is a fantastic language--it has absolutely no libraries out of the box which makes it a poor development /environment/. To directly address your question: - include a "version" cell on a "Config" worksheet (optionally include values for "audited by", "audited date", "audit version", "audit type", note this could be a multirow, so you could actually specify "full" audits, and mini audits) - build a function that maps all formulas to an "Audit" worksheet - build a function to export to CSV with the date and version tagged - build a function to export all code modules to text files - create whatever release-versioning you want around this: zip of exports + "deployment" XLS, GIT repo with tagged version and exports and a binary blob of the XLS, etc, etc. (Edit: workbook vs worksheet; each tab in a workbook is a worksheet.)

joe_the_user 7y ago

Like a lot of things, I think you have two part here. A small spread can be more understandable than a small program because you have all the logic apparently visible. A large spreadsheet can become harder to understand and verify than the medium-sized program that could duplicate it's logic.

mxschumacher 7y ago
Yes. I wonder whether it is feasible to run unit tests in spreadsheets: they are essentially long chains of functions. Debugging a larger sheet seems really tough.