6 ms·

Recently I started building Excel addons with custom formulas (e.g. =company.inventory(upc, WAREHOUSE), which call an api. This has blown the minds of non-tech

Recently I started building Excel addons with custom formulas (e.g. =company.inventory(upc, WAREHOUSE), which call an api. This has blown the minds of non-tech folks among my clients. They equate this to magic. I was forced into doing this because after a year of digging to find out what reporting they wanted in the dashboard (“oh a thousand things… where’s the Excel spreadsheet export button?”), I gave up and now default to giving them all reporting via Excel, using these custom formulas.

rr808 4y ago
Any new or good ways to do this? I used Excel-Dna 10 years ago.
- alar44 4y ago
  Honestly, GPT is pretty good at this type of stuff.
  - unsupp0rted 4y ago
    GPT's code and docs for this is way out of date. I tried it a couple weeks ago.
- unsupp0rted 4y ago
  Yup, javascript: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview https://learn.microsoft.com/en-us/office/dev/add-ins/excel/c...
  - rr808 4y ago
    Ah interesting thanks, I'll have to try it out.
  - cm2187 4y ago
    Is it fast? For instance there is a large overhead to using VBA UDF, whereas XLL (and ExcelDNA) is pretty fast (matters if the formula is repeated over and over on a spreadsheet).
    - unsupp0rted 4y ago
      Not sure how it compares to XLL but I'd wager the bottleneck, in my case, is the API calls.
ayemojay 4y ago
What is your language of choice for building custom formula? Python + xlwings: most successful (relatively speaking) to build custom finance functions in my line of work. Downside is the slowness and deploying on other computers. Rust + xladd: really enjoyed this but feels immature still. Better performance than python and easier to distribute as single dll. VBA: options above make this almost obsolete, however can’t beat being embedded.
- unsupp0rted 4y ago
  Modern Javascript: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview https://learn.microsoft.com/en-us/office/dev/add-ins/excel/c... For the "task pane", if you need one, you can use React or Angular, but I prefer to use Vue (or no front-end framework is fine too). For the backend logic, just straight up Node + webpack. You could probably do this better with Vite, but I think Microsoft's starter templates all use webpack. You end up distributing a manifest file that tells Excel which server your assets and API live at. Warning: hot reload and dev debugging is quite a pain on macOS.
- rr808 4y ago
  Excel-Dna works great. C#, Doesn't need access to registry so easy install.
  - cm2187 4y ago
    And combined with NetOffice for Excel COM interoperability without the headaches.
- auxym 4y ago
  You can use C# or F# using excel-DNA. I've played with excel add-ons (xll files) in F# in the past, pretty neat.
- Iwan-Zotow 4y ago
  Python+LibreOffice
phpisthebest 4y ago
Yep, we have a expensive reporting tool, can too all kinds of things, query every type of database we have... Number one function used. Export to Excel, and normally it is on reports of basic table join queries, none of the advanced things it can do with the data
- SoftTalker 4y ago
  This is because businesspeople like to fiddle with formulas, graphs, and filters untill they isolate (i.e. cherry-pick) the subset of data that [makes them look good | supports their agendas]. They don't want canned reports. Excel makes this easy because they already know how to do those things in Excel.
  - Godel_unicode 4y ago
    People get really mad when you call this p-hacking, btw. I used to try and make this point to ”analysts” that I worked with, that science involves coming up with a hypothesis and then looking at the data. The response I got often enough that I’m sure it’s being taught somewhere was that you “need to let the data tell it’s own story”. Turns out lots of people have decided what the conclusion is and are taking that conclusion and the data, and then blindly munging until they get a path which connects them.
    - axelf4 4y ago
      As my professor said: All good statistics is done before you have looked at the data.
      - sideshowb 4y ago
        But where do you get your hypothesis from?
        beckingz 4y ago
        your hypothetical model of reality. But that's a lot of work so businesses don't want to pay for it.
spockz 4y ago
Isn’t this exactly what Microsoft access was invented for? I’ve seen technical but non-cs people built magical things in forms by (or views). And when access got the ability to talk to MSSQL it really blew up. The quintessential low-code environment. I wonder why it never caught on.
- jiggawatts 4y ago
  Microsoft essentially abandoned it. They hoped that a bunch of random Azure services would be its replacement, but they're too scattered and incomplete compared to Access.
  - trobertson 4y ago
    It's not abandoned. Access still has an engineering team working on it, and that team still goes to conferences to talk with their users. Source: I work in the same org as them, and ate lunch daily with a few of them before the pandemic hit.
    - jiggawatts 4y ago
      Sigh. Silverlight had positive sounding blog posts, etc... right up until its official death. These megacorps never officially announce a product's demise, not while they can sell it. You have to read between the lines.
- jimmygrapes 4y ago
  My theory is that InfoPath killed Access. People wanted to use the "easy interface for Nancy in Marketing to submit a purchasing request" and got bogged down with XPath and SharePoint/ActiveDirectory fuckery, and weren't quite to the level where they could grasp MSSQL (or IT had disabled this or that, forcing the one ambitious "computer guy" in Finance to cludge together something with the tools available). Using Access would have been ideal but it was a time when MS was pushing things like InfoPath and Sway and the like, and simultaneously there were issues with concurrency and "omg macro virus" cybersecurity focus. Access got better but it was already tainted.
- 4y ago
chiph 4y ago
This is probably the second-most-proud-of thing I worked on in my career, based on an off-hand comment from one of our salespeople: "Why can't our customers use Excel to view their data?" Another developer and I wrote the first version in a couple of weeks - an Excel VBA add-in that used IBM SNA APPC to communicate with their AS/400 (I later upgraded it to use TCP/IP sockets). Because the salespeople didn't know how to use or sell it yet, I got to be the one to travel to our first customer and train them on it. After showing their CFO how to add our formulas to a couple of cells, make a chart from it, and then hit Shift+F9 to refresh the formulas and get current data from their AS/400, he pushed me away from the keyboard and quickly entered several more formulas to bring in additional data. And then told me I had just saved him three day's work every month when he created charts for his reports to the board. I eventually left the company, and they got bought a couple of times by ever-bigger software companies. But the product is still out there and is very versatile, having been extended to talk to many different ERP & accounting systems. It even still has the same product name: Spreadsheet Server.

rr808 4y ago

Any new or good ways to do this? I used Excel-Dna 10 years ago.

alar44 4y ago
Honestly, GPT is pretty good at this type of stuff.
- unsupp0rted 4y ago
  GPT's code and docs for this is way out of date. I tried it a couple weeks ago.
unsupp0rted 4y ago
Yup, javascript: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview https://learn.microsoft.com/en-us/office/dev/add-ins/excel/c...
- rr808 4y ago
  Ah interesting thanks, I'll have to try it out.
- cm2187 4y ago
  Is it fast? For instance there is a large overhead to using VBA UDF, whereas XLL (and ExcelDNA) is pretty fast (matters if the formula is repeated over and over on a spreadsheet).
  - unsupp0rted 4y ago
    Not sure how it compares to XLL but I'd wager the bottleneck, in my case, is the API calls.

ayemojay 4y ago

What is your language of choice for building custom formula? Python + xlwings: most successful (relatively speaking) to build custom finance functions in my line of work. Downside is the slowness and deploying on other computers. Rust + xladd: really enjoyed this but feels immature still. Better performance than python and easier to distribute as single dll. VBA: options above make this almost obsolete, however can’t beat being embedded.

unsupp0rted 4y ago
Modern Javascript: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview https://learn.microsoft.com/en-us/office/dev/add-ins/excel/c... For the "task pane", if you need one, you can use React or Angular, but I prefer to use Vue (or no front-end framework is fine too). For the backend logic, just straight up Node + webpack. You could probably do this better with Vite, but I think Microsoft's starter templates all use webpack. You end up distributing a manifest file that tells Excel which server your assets and API live at. Warning: hot reload and dev debugging is quite a pain on macOS.
rr808 4y ago
Excel-Dna works great. C#, Doesn't need access to registry so easy install.
- cm2187 4y ago
  And combined with NetOffice for Excel COM interoperability without the headaches.
auxym 4y ago
You can use C# or F# using excel-DNA. I've played with excel add-ons (xll files) in F# in the past, pretty neat.
Iwan-Zotow 4y ago
Python+LibreOffice

phpisthebest 4y ago

Yep, we have a expensive reporting tool, can too all kinds of things, query every type of database we have... Number one function used. Export to Excel, and normally it is on reports of basic table join queries, none of the advanced things it can do with the data

SoftTalker 4y ago
This is because businesspeople like to fiddle with formulas, graphs, and filters untill they isolate (i.e. cherry-pick) the subset of data that [makes them look good | supports their agendas]. They don't want canned reports. Excel makes this easy because they already know how to do those things in Excel.
- Godel_unicode 4y ago
  People get really mad when you call this p-hacking, btw. I used to try and make this point to ”analysts” that I worked with, that science involves coming up with a hypothesis and then looking at the data. The response I got often enough that I’m sure it’s being taught somewhere was that you “need to let the data tell it’s own story”. Turns out lots of people have decided what the conclusion is and are taking that conclusion and the data, and then blindly munging until they get a path which connects them.
  - axelf4 4y ago
    As my professor said: All good statistics is done before you have looked at the data.
    - sideshowb 4y ago
      But where do you get your hypothesis from?
      - beckingz 4y ago
        your hypothetical model of reality. But that's a lot of work so businesses don't want to pay for it.

spockz 4y ago

Isn’t this exactly what Microsoft access was invented for? I’ve seen technical but non-cs people built magical things in forms by (or views). And when access got the ability to talk to MSSQL it really blew up. The quintessential low-code environment. I wonder why it never caught on.

jiggawatts 4y ago
Microsoft essentially abandoned it. They hoped that a bunch of random Azure services would be its replacement, but they're too scattered and incomplete compared to Access.
- trobertson 4y ago
  It's not abandoned. Access still has an engineering team working on it, and that team still goes to conferences to talk with their users. Source: I work in the same org as them, and ate lunch daily with a few of them before the pandemic hit.
  - jiggawatts 4y ago
    Sigh. Silverlight had positive sounding blog posts, etc... right up until its official death. These megacorps never officially announce a product's demise, not while they can sell it. You have to read between the lines.
jimmygrapes 4y ago
My theory is that InfoPath killed Access. People wanted to use the "easy interface for Nancy in Marketing to submit a purchasing request" and got bogged down with XPath and SharePoint/ActiveDirectory fuckery, and weren't quite to the level where they could grasp MSSQL (or IT had disabled this or that, forcing the one ambitious "computer guy" in Finance to cludge together something with the tools available). Using Access would have been ideal but it was a time when MS was pushing things like InfoPath and Sway and the like, and simultaneously there were issues with concurrency and "omg macro virus" cybersecurity focus. Access got better but it was already tainted.
4y ago

chiph 4y ago

This is probably the second-most-proud-of thing I worked on in my career, based on an off-hand comment from one of our salespeople: "Why can't our customers use Excel to view their data?" Another developer and I wrote the first version in a couple of weeks - an Excel VBA add-in that used IBM SNA APPC to communicate with their AS/400 (I later upgraded it to use TCP/IP sockets). Because the salespeople didn't know how to use or sell it yet, I got to be the one to travel to our first customer and train them on it. After showing their CFO how to add our formulas to a couple of cells, make a chart from it, and then hit Shift+F9 to refresh the formulas and get current data from their AS/400, he pushed me away from the keyboard and quickly entered several more formulas to bring in additional data. And then told me I had just saved him three day's work every month when he created charts for his reports to the board. I eventually left the company, and they got bought a couple of times by ever-bigger software companies. But the product is still out there and is very versatile, having been extended to talk to many different ERP & accounting systems. It even still has the same product name: Spreadsheet Server.