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.

Modern Javascript: 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.

Excel-Dna works great. C#, Doesn't need access to registry so easy install.

And combined with NetOffice for Excel COM interoperability without the headaches.

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.

Python+LibreOffice

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

Yup, javascript: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/c...

Ah interesting thanks, I'll have to try it out.

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).

Not sure how it compares to XLL but I'd wager the bottleneck, in my case, is the API calls.

Honestly, GPT is pretty good at this type of stuff.

GPT's code and docs for this is way out of date. I tried it a couple weeks 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

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.

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.

As my professor said: All good statistics is done before you have looked at the data.

But where do you get your hypothesis from?

your hypothetical model of reality.

But that's a lot of work so businesses don't want to pay for it.

Whatever orifice you desire.

True, for clean data. You can’t clean data without looking at it (a lot), though.

If you have data that's so 'dirty' that you can't decide on the filtering rules in advance (or based on only historic data), then what you have is garbage, not data. Therefore, we could call the art of shaping this into meaningful stories garbage science.

Tell me in a comment you have never worked with business data in your life.

Business data is full of minor inconsistencies which are not obvious until you sit in front of it. Products are sold by different units. Reporting ranges and aggregates are slightly different. Subsidiaries use categories which are close but not exactly identical.

There is generally plenty of massaging to do before you can get the information you need.

> I’m sure it’s being taught somewhere was that you “need to let the data tell it’s own story”.

AWS is pushing this message on every NFL broadcast with their Next Gen Stats ads. "The data tells us all"

I think a lot of times unless you are operating at hyperscale and bps matter, most business data analysis is more art than science. Ex, if you have < 200 sales in a month and you want to report this, it's totally normal to want to pull it into excel and fiddle with the numbers, make ad-hoc corrections, divide those 200 sales up in very subjective ways, etc. The smaller the company the more the reporting layer is only a weak approximation of reality, there isn't enough there to create a narrative around. "Hypothesis testing" such data is honestly a waste of time.

Of course you want to be intellectually honest about it, and I agree that cherry-picking the "right" data can be a big problem in the wrong kind of organization. I remember one time a Jr analyst I managed was asked by the CEO to create a certain chart. I was not looped in. The CEO then used that chart to convince himself and many others on the executive team to make a huge product change that ended up being a complete disaster.

Excel also makes it easier to experiment. Pandas requires you to change an entire column which can frequently be processor-intensive, take forever, and be hard to understand. With Excel, you can just take a random cell, try something out, and if it comes up with nonsense, undo it

Why not df.head()… ?

That can also take a while, and it isn’t as easy to view the values side by side. Excel is simpler (although way less capable) a lot of the time

It is sometimes more possible to determine if your analysis is wrong in excel because you can look at the numbers and the numbers have meanings. It can be harder to debug things if you can’t see intermediate computations. I guess I would say that excel prevents some kinds of bad problems with faulty analyses but opens you up to other problems like overfitting or the cherry-picking you describe. But it also seems impossible to produce a reporting tool that both gives useful analyses and doesn’t do incorrect analyses.

As one of those business people, I find I’m not very good at requesting reports. There is a gap between what I think is interesting and what actually is interesting. I use spreadsheets (and sometimes visualization tools) to help bridge the gap and make a better on going request to our reporting team.

Often, this is learning for me. I have a bunch of Stephen Few’s books and use exported CSV files to figure out which reports are useful to me and my org. When I find them, I do make a request for standardized reporting. These often become the basis for regular review meetings. In those meetings, we still come up with instances where we need to export to CSV and get into the data to understand what we are looking at.

Our work is going through a big shift this year that means our historic data is not helpful in predicting trends. That’s increased the need for this kind of engagement with the underlying data.

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.

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.

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.

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.

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.

I was hired a couple decades ago to replace a Microsoft Access system that was written by a non-software engineer and was running an entire company with around 50 users with all sorts of complex forms.

It got a little too big and I rewrote it as a C# desktop application talking to a SQL Server, but it was impressive what this person was able to create with no real programming knowledge and just Microsoft Access.

I like this comment.

A lot of comments on HN display disrespect for people using tools like Excel and Access instead of 'doing things properly'.

You acknowledge that the person who built what you replaced was working under constraints and created a system that ran a 50-person company.

This is exactly what I meant. For real world situations and when you don’t have SWE “to do a proper” job you can get very far fast with just access. Especially if you hook it up to a running db instead of using the internal one.

Which gains did you or the users get when rewriting to C#? What were the downsides?

> Which gains did you or the users get when rewriting to C#? What were the downsides?

The gains were performance. The MS Access application was not backed by a proper database, I forget the exact setup but it was more of just a shared MDB file over the network? Whatever Access was capable of at the time. There were contention/locking issues and all the other sorts of problems you'd expect given the setup. My memory of it is a bit hazy, but I know it was lacking a true database outside of its own MDB format.

Funny story, after the rewrite the software was so succesful internally that we decided to start selling it, and it became one of the industry leaders. The person who wrote it originally was highly knowledgable in their field, and I happened to have decent enough programming knowledge. The two put together ended up with something special.

Access is a bunch less flexible than excel. You need to plan how to query data more carefully and as soon as you want to do something weird, you basically need to dump your query result into excel anyway.

Access as an idea was awesome.

The execution sucked because Access is/was a terrible database. Poor data integrity, really low limits on the DB size, etc.

I think if Access was as good as SQLlite, it would have really taken off.

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.