7 ms·

I've been tasked with migrating an excel model to a "real language" (usually by breaking it apart and re-implementing it via a combination of ETL and data wareh

by haney 5y ago

I've been tasked with migrating an excel model to a "real language" (usually by breaking it apart and re-implementing it via a combination of ETL and data warehouse jobs). I've never found a great way to run excel in a headless way, so in addition to not having version control for it, it's hard to "deploy" it when it grows beyond a single person's machine. I wish there was more of a gradient between Excel and "real systems".

hugi 5y ago
A few years back, I was tasked with a similar thing. A government ministry was creating a complex calculator for a (very anticipated) public project that was supposed to go on their website. We started out using pure JS but the mathematician working on the project kept giving us new Excel documents with extremely heavy changes to the algorithms. In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever.
- antris 5y ago
  >In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever. This is the kind of simple and effective solution that programmers who think they know everything would scoff at. Love it.
  - tehbeard 5y ago
    It's simple and effective until a part of the rube goldberg machine breaks...
    - deleted 5y ago
      [deleted]
  - montecarl 5y ago
    I have used the google sheets API to implement something similar when working with a nonprofit. They needed a fairly complex listing on their website that needed search/sort/filter/mapping and needed to update this list regularly. So I just took their existing google sheets document, and accessed it as a read-only database in the browser using Google's REST api and it was fairly painless! If they ever broke anything I could easily go into the spreadsheet and fix it. This approach really reduced the effort needed. If I had to write a "proper" interface for them to enter and update their data I wouldn't have had time to work on their project.
  - shrikant 5y ago
    No. I flinched hard at this, because this only works until it doesn't. I've done the exact same thing: give a user a location to upload an Excel set up just the way we'd want to parse it. Good luck dealing with the absolute morass of formatting troubles that Excel throws at you because: 1) The user didn't format a date input correctly and now Excel treats it as a simple string instead of its internal Date representation 2) Excel mysteriously treats a random entry in a numeric or date column (PEBKAC? Who knows! The user denies all wrong-doing!) as a string and now it's got a leading apostrophe 3) The user used someone else's computer which has different Regional Settings, and suddenly: 3a) Commas are decimal points in numbers instead of periods 3b) Date formatting is messed up 3c) Months and days have their names in a different language ... And these are just the issues that I can remember without having to dredge through painful memories.
    - antris 5y ago
      Sure, you know better than the person that actually implemented it and tells us it worked out fine. Because you've done something similar before and failed. The exact kind of know-it-all attitude I was talking about. Thanks for the demonstration :)
      - isbvhodnvemrwvn 5y ago
        To be honest I also had bad experiences with processes like this - very simple data entry mistakes which were basically invisible to the user made the entire thing fail.
    - snoopen 5y ago
      Most of these are easy to work around our aren't unique to Excel: 1 & 2) isn't unique to Excel, incorrect inputs will give incorrect results in code too 3, 4 & 5) worked around by getting the internal raw numeric value then formatting it as required.
- WorldMaker 5y ago
  The Microsoft Graph APIs in Microsoft 365/Office 365 give you pretty much all of the Excel execution engine as REST endpoints "in the Cloud" if you just store your Excel files in SharePoint. It's not surprising the number of turducken business applications being built exactly this way. With Named Cells you don't even have to hard-code cell numbers, just tell them to name them specific things, and Excel users are very happy with the amount of flexibility to rewrite the spreadsheets at will. It's not necessarily the sanest approach to building software, but no one ever accused most enterprise software development of being sane.
  - pjmorris 5y ago
    > turducken business applications Great analogy
  - OskarS 5y ago
    As a rapid prototyping tool, it doesn’t sound terrible, honestly. Many people are comfortable with Excel, so let them use it! You’re gonna use some calculation engine on the backend, might as well be the tool that contains the ”reference” calculations.
- carpo 5y ago
  I did something similar for a few clients, but mainly for automating documents. People were copying and pasting between Excel and Word, so I made them systems that link the two together. I had enough clients asking for something similar that I made a SaaS product that does it. Gives them a nice little interface that links web form fields to named ranges, and then a simple templating language to insert those fields into a Word document. Instead of writing a calculation engine for our webforms, I just used Excel. It's pretty powerful, and more than I could have implemented if starting from scratch.
prionassembly 5y ago
There are a few. I use xlwings for https://github.com/asemic-horizon/stanton https://github.com/asemic-horizon/stanton , which is some bits of code to specify expert-led sensitivity analysis from Excel and use the results to emulate the spreadsheet from a ML model.
- LukeEF 5y ago
  That's github based for collaboration I think. The one mentioned in the post VersionXL [1] is based on the cloud version of TerminusDB (co-founder here), which is an open source revision control database. It uses delta encoding for updates, but is a proper DB optimized for the task. You get transaction processing and updates to an immutable database with version control features: branch, merge, rollback, searchable diffs, and time-travel. It also ships with a mature python client to allow you to manipulate the Excel data. [1] https://versionxl.com/ https://versionxl.com/ [2] https://github.com/terminusdb/terminusdb https://github.com/terminusdb/terminusdb
mcdonje 5y ago
Yeah, you either build a pipeline that generates/updates excels that get emailed or self-service downloaded, or you teach them how to use powerquery to get the data from the enterprise db.
elliekelly 5y ago
Have you tried AirTable & their API?
- haney 5y ago
  I haven't tried it, it does look really interesting, although most of the time the problem is that the finance/ops/etc. team already had something really complicated in Excel and the question is "what should stay in excel, and what should be reimplemented in some other system".
stonemetal12 5y ago
Isn't that the only reason access exists? Import from excel and build forms on top.
munchbunny 5y ago
Personally, I've found that Jupyter notebooks occupy that niche pretty well. When authoring, you have something that shows intermediate results just like Excel, making troubleshooting without dedicated debugging still pretty doable. And then you can still run them headless, and you can check them into version control, and diffs are readable enough.
ebiester 5y ago
Depending on budget, it might be less expensive to look at a tool like https://app.molnify.com/#ajax/examples https://app.molnify.com/#ajax/examples (or its 5 competitors from a google search.) It feels like a subset of this should be an open source app (that is, turn an excel spreadsheet into a C# app) for anyone looking for an idea.
TTPrograms 5y ago
In the past I wrote a simple formula evaluator in Python I used to replicate some multicell calculation - the spreadsheet I had took the form of mostly simple algebra being performed in a scanning pattern against various small windows in time (rows) from a set of columns. I just extracted the cell formula definitions and transformed them. It may not be that hard to replicate the set of formulas you need to get 90%+ of your excel model. If someone implements a 90% reimplementation of Excel in Python that would be a really useful library for stuff like this. You could do some neat stuff with dependency identification too.
- makapuf 5y ago
  There is https://pyspread.gitlab.io/ https://pyspread.gitlab.io/, not sur if it fits your use case?
speed_spread 5y ago
> I've never found a great way to run excel in a headless way Do not ask me for source, but I remember opting to do just that and making it work after being unable to replicate Excel's results in an app that was supposed to replicate it. This was using Delphi, and the solution was to load the Excel spreadsheet as a COM object, programmatically write input data and collect the results. Loading the COM object was as slow as starting Excel but no Excel window ever showed up. This was circa Windows NT 4.0, so possibly 1998? And I would bet this would still work.
- topspin 5y ago
  "And I would bet this would still work." Yes, it would. You can operate a spreadsheet in that manner using any .NET[1] language. You could do it with any COM aware platform before .NET existed. Today you could use Python if you want to remain among the cool kids while doing it[2]. Judging by the popularity of that repo there are a bunch of people doing exactly that. This is a late 90's era problem. I have a hard time imagining any programmer having difficulty with this. Then or now. Literally anything that could run a VB macro could programmatically manipulate an Excel spreadsheet. Such approaches are fragile. No doubt about it. They rarely survive a major version upgrade of any component without some fussing. On the other hand the same basic APIs that emerged in the 90s work today with little conceptual change, so the value of the investment in this knowledge has never been wasted. [1] https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.worksheetclass?view=excel-pia https://docs.microsoft.com/en-us/dotnet/api/microsoft.office... [2] https://github.com/mhammond/pywin32 https://github.com/mhammond/pywin32
btown 5y ago
There are systems that can do this e.g. https://github.com/stephenrauch/pycel https://github.com/stephenrauch/pycel as described here https://web.archive.org/web/20210308015732/https://dirkgorissen.com/2011/10/19/pycel-compiling-excel-spreadsheets-to-python-and-making-pretty-pictures/ https://web.archive.org/web/20210308015732/https://dirkgoris... If you're deploying large-scale models, https://timelydataflow.github.io/differential-dataflow/ https://timelydataflow.github.io/differential-dataflow/ may be of interest (this is used, for instance, by https://materialize.com/ https://materialize.com/)
wjnc 5y ago
So I started on the same work with the idea of extracting all relevant information from all cells (value ranges, values, formulas), exporting to TXT and then building a conversion layer. I didn’t get there but what was informative was the sparsity of information in a big spreadsheet. Most cells in the few models I had for testing have only a few that actually do meaningful stuff. Most are fluff. One of the yet untouched harder parts is the conversion of Excel-specific formulas. The Excels I had used mainly basic math and lookups (thousands of them), not many of the built-in Excel formulas. (Any tips on my approach are very welcome, VBA for Excel isn’t hard but quite hard to Google.)
- snoopen 5y ago
  Not sure what you are trying to achieve but the majority of Excel formulas I see used most should be relatively easy to reimplement. Some like SUMPRODUCT, AGGREGATE and the specialised finance ones might be harder to reimplement. The trickier bit might be getting the evaluation order right.
pjmlp 5y ago
What about Powerapps?

by haney 5y ago

hugi 5y ago

A few years back, I was tasked with a similar thing. A government ministry was creating a complex calculator for a (very anticipated) public project that was supposed to go on their website. We started out using pure JS but the mathematician working on the project kept giving us new Excel documents with extremely heavy changes to the algorithms. In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever.

antris 5y ago
>In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever. This is the kind of simple and effective solution that programmers who think they know everything would scoff at. Love it.
- tehbeard 5y ago
  It's simple and effective until a part of the rube goldberg machine breaks...
  - deleted 5y ago
    [deleted]
- montecarl 5y ago
  I have used the google sheets API to implement something similar when working with a nonprofit. They needed a fairly complex listing on their website that needed search/sort/filter/mapping and needed to update this list regularly. So I just took their existing google sheets document, and accessed it as a read-only database in the browser using Google's REST api and it was fairly painless! If they ever broke anything I could easily go into the spreadsheet and fix it. This approach really reduced the effort needed. If I had to write a "proper" interface for them to enter and update their data I wouldn't have had time to work on their project.
- shrikant 5y ago
  No. I flinched hard at this, because this only works until it doesn't. I've done the exact same thing: give a user a location to upload an Excel set up just the way we'd want to parse it. Good luck dealing with the absolute morass of formatting troubles that Excel throws at you because: 1) The user didn't format a date input correctly and now Excel treats it as a simple string instead of its internal Date representation 2) Excel mysteriously treats a random entry in a numeric or date column (PEBKAC? Who knows! The user denies all wrong-doing!) as a string and now it's got a leading apostrophe 3) The user used someone else's computer which has different Regional Settings, and suddenly: 3a) Commas are decimal points in numbers instead of periods 3b) Date formatting is messed up 3c) Months and days have their names in a different language ... And these are just the issues that I can remember without having to dredge through painful memories.
  - antris 5y ago
    Sure, you know better than the person that actually implemented it and tells us it worked out fine. Because you've done something similar before and failed. The exact kind of know-it-all attitude I was talking about. Thanks for the demonstration :)
    - isbvhodnvemrwvn 5y ago
      To be honest I also had bad experiences with processes like this - very simple data entry mistakes which were basically invisible to the user made the entire thing fail.
  - snoopen 5y ago
    Most of these are easy to work around our aren't unique to Excel: 1 & 2) isn't unique to Excel, incorrect inputs will give incorrect results in code too 3, 4 & 5) worked around by getting the internal raw numeric value then formatting it as required.
WorldMaker 5y ago
The Microsoft Graph APIs in Microsoft 365/Office 365 give you pretty much all of the Excel execution engine as REST endpoints "in the Cloud" if you just store your Excel files in SharePoint. It's not surprising the number of turducken business applications being built exactly this way. With Named Cells you don't even have to hard-code cell numbers, just tell them to name them specific things, and Excel users are very happy with the amount of flexibility to rewrite the spreadsheets at will. It's not necessarily the sanest approach to building software, but no one ever accused most enterprise software development of being sane.
- pjmorris 5y ago
  > turducken business applications Great analogy
- OskarS 5y ago
  As a rapid prototyping tool, it doesn’t sound terrible, honestly. Many people are comfortable with Excel, so let them use it! You’re gonna use some calculation engine on the backend, might as well be the tool that contains the ”reference” calculations.
carpo 5y ago
I did something similar for a few clients, but mainly for automating documents. People were copying and pasting between Excel and Word, so I made them systems that link the two together. I had enough clients asking for something similar that I made a SaaS product that does it. Gives them a nice little interface that links web form fields to named ranges, and then a simple templating language to insert those fields into a Word document. Instead of writing a calculation engine for our webforms, I just used Excel. It's pretty powerful, and more than I could have implemented if starting from scratch.

prionassembly 5y ago

There are a few. I use xlwings for https://github.com/asemic-horizon/stanton https://github.com/asemic-horizon/stanton , which is some bits of code to specify expert-led sensitivity analysis from Excel and use the results to emulate the spreadsheet from a ML model.

LukeEF 5y ago
That's github based for collaboration I think. The one mentioned in the post VersionXL [1] is based on the cloud version of TerminusDB (co-founder here), which is an open source revision control database. It uses delta encoding for updates, but is a proper DB optimized for the task. You get transaction processing and updates to an immutable database with version control features: branch, merge, rollback, searchable diffs, and time-travel. It also ships with a mature python client to allow you to manipulate the Excel data. [1] https://versionxl.com/ https://versionxl.com/ [2] https://github.com/terminusdb/terminusdb https://github.com/terminusdb/terminusdb

mcdonje 5y ago

Yeah, you either build a pipeline that generates/updates excels that get emailed or self-service downloaded, or you teach them how to use powerquery to get the data from the enterprise db.

elliekelly 5y ago

Have you tried AirTable & their API?

haney 5y ago
I haven't tried it, it does look really interesting, although most of the time the problem is that the finance/ops/etc. team already had something really complicated in Excel and the question is "what should stay in excel, and what should be reimplemented in some other system".

stonemetal12 5y ago

Isn't that the only reason access exists? Import from excel and build forms on top.

munchbunny 5y ago

Personally, I've found that Jupyter notebooks occupy that niche pretty well. When authoring, you have something that shows intermediate results just like Excel, making troubleshooting without dedicated debugging still pretty doable. And then you can still run them headless, and you can check them into version control, and diffs are readable enough.

ebiester 5y ago

Depending on budget, it might be less expensive to look at a tool like https://app.molnify.com/#ajax/examples https://app.molnify.com/#ajax/examples (or its 5 competitors from a google search.) It feels like a subset of this should be an open source app (that is, turn an excel spreadsheet into a C# app) for anyone looking for an idea.

TTPrograms 5y ago

In the past I wrote a simple formula evaluator in Python I used to replicate some multicell calculation - the spreadsheet I had took the form of mostly simple algebra being performed in a scanning pattern against various small windows in time (rows) from a set of columns. I just extracted the cell formula definitions and transformed them. It may not be that hard to replicate the set of formulas you need to get 90%+ of your excel model. If someone implements a 90% reimplementation of Excel in Python that would be a really useful library for stuff like this. You could do some neat stuff with dependency identification too.

makapuf 5y ago
There is https://pyspread.gitlab.io/ https://pyspread.gitlab.io/, not sur if it fits your use case?

speed_spread 5y ago

> I've never found a great way to run excel in a headless way Do not ask me for source, but I remember opting to do just that and making it work after being unable to replicate Excel's results in an app that was supposed to replicate it. This was using Delphi, and the solution was to load the Excel spreadsheet as a COM object, programmatically write input data and collect the results. Loading the COM object was as slow as starting Excel but no Excel window ever showed up. This was circa Windows NT 4.0, so possibly 1998? And I would bet this would still work.

topspin 5y ago
"And I would bet this would still work." Yes, it would. You can operate a spreadsheet in that manner using any .NET[1] language. You could do it with any COM aware platform before .NET existed. Today you could use Python if you want to remain among the cool kids while doing it[2]. Judging by the popularity of that repo there are a bunch of people doing exactly that. This is a late 90's era problem. I have a hard time imagining any programmer having difficulty with this. Then or now. Literally anything that could run a VB macro could programmatically manipulate an Excel spreadsheet. Such approaches are fragile. No doubt about it. They rarely survive a major version upgrade of any component without some fussing. On the other hand the same basic APIs that emerged in the 90s work today with little conceptual change, so the value of the investment in this knowledge has never been wasted. [1] https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.worksheetclass?view=excel-pia https://docs.microsoft.com/en-us/dotnet/api/microsoft.office... [2] https://github.com/mhammond/pywin32 https://github.com/mhammond/pywin32

btown 5y ago

There are systems that can do this e.g. https://github.com/stephenrauch/pycel https://github.com/stephenrauch/pycel as described here https://web.archive.org/web/20210308015732/https://dirkgorissen.com/2011/10/19/pycel-compiling-excel-spreadsheets-to-python-and-making-pretty-pictures/ https://web.archive.org/web/20210308015732/https://dirkgoris... If you're deploying large-scale models, https://timelydataflow.github.io/differential-dataflow/ https://timelydataflow.github.io/differential-dataflow/ may be of interest (this is used, for instance, by https://materialize.com/ https://materialize.com/)

wjnc 5y ago

So I started on the same work with the idea of extracting all relevant information from all cells (value ranges, values, formulas), exporting to TXT and then building a conversion layer. I didn’t get there but what was informative was the sparsity of information in a big spreadsheet. Most cells in the few models I had for testing have only a few that actually do meaningful stuff. Most are fluff. One of the yet untouched harder parts is the conversion of Excel-specific formulas. The Excels I had used mainly basic math and lookups (thousands of them), not many of the built-in Excel formulas. (Any tips on my approach are very welcome, VBA for Excel isn’t hard but quite hard to Google.)

snoopen 5y ago
Not sure what you are trying to achieve but the majority of Excel formulas I see used most should be relatively easy to reimplement. Some like SUMPRODUCT, AGGREGATE and the specialised finance ones might be harder to reimplement. The trickier bit might be getting the evaluation order right.

pjmlp 5y ago

What about Powerapps?