News for hackers. Stuff that matters.
Re:Excel Never Dies by aarondia on Monday March 08, 2021 @11:50AM (#26387915)
Excel, and more importantly, the spreadsheet is the best way to build intuition for a dataset, hands down. The alternatives, especially when it comes to Python and the default pandas output in a Jupyter Notebook are horrendous.

Where Excel falls short, is data size limitations + auditability. Putting more than 1M rows of data into Excel is not possible, and once you get into the low 100K's, it becomes almost unbearable. And handing off an Excel workbooks to a colleague is handing them hours of cell dependency tracing. On the other hand, data size + auditability are the super powers of Python data analysis.

I've been building a Python package, Mito (https://trymito.io/), its an interactive spreadsheet that automatically converts your spreadsheet analysis to the equivalent pandas code. You can write spreadsheet formulas, merge datasets, create pivot tables, etc. And because its implemented in Python, you can manipulate datasets with 10M rows of data with no problem. Our goal is to bring the intuitiveness of Excel data manipulation to Pandas.

Re:Excel Never Dies by algorithmsRcool on Monday March 08, 2021 @12:45PM (#26388861)
> Putting more than 1M rows of data into Excel is not possible, and once you get into the low 100K's, it becomes almost unbearable.

I dispute this. Yes, the normal spreadsheet view of excel will buckle under 1M rows, but excel has another feature called "Power Pivot" that is backed by an embedded database and scales into the high millions at least.

I've personally used excel on a dataset of 18M rows and PowerPivot handled it just fine.

[0] https://support.office.com/client/Data-Model-specification-a...

[1] https://support.office.com/client/power-pivot-powerful-data-...

Re:Excel Never Dies by stilisstuk on Monday March 08, 2021 @02:55PM (#26390677)
Yes pp can handle data. But vba can not. And most spreadsheets contain vba for reporting and magic interfaces for managers. Slow and unmaintainable.

Any day: rmarkdown and csv

Re:Excel Never Dies by texasbigdata on Monday March 08, 2021 @01:03PM (#26389172)
Big fan of this, but powerpivot sits on vertipaq (I believe) which is an in memory columnar DB or sorts (apologies if that’s incorrect). So at this point you’re getting awfully close to direct querying (another msft feature) which while analogous resembles more traditional db/client if you squint hard enough.

But yes, big fan of vertipaq which I believe also powers PowerBI.

Re:Excel Never Dies by craig_asp on Monday March 08, 2021 @04:08PM (#26391569)
Just to clarify.. Yes, vertipaq is the tech behind power pivot, power bi and sql server analysis services (in tabular mode) and the same column-oriented storage is also used in sql server. Excel generates queries against the data stored in a vertipaq model. You cannot write normal excel formulas on top of it and you have to use DAX (a unique to msft language, which is the replacement for MDX) instead, which is pretty much a no-go for anyone but well-trained power users.
Re:Excel Never Dies by aarondia on Monday March 08, 2021 @01:52PM (#26389867)
There's also a workaround in Google Sheets where you can store your data in BigQuery and use the spreadsheet to interact with it.
Re:Excel Never Dies by loudmax on Monday March 08, 2021 @12:11PM (#26388255)
IMHO where Excel falls short is in interoperability. Try processing .xlsx files in anything other than Excel and it can be painful. Let's see the new more open Microsoft really embrace competing on an even playing field that doesn't rely on ossified proprietary file formats.
Re:Excel Never Dies by bcoates on Monday March 08, 2021 @01:26PM (#26389499)
Not really. Processing xlsx files in full generality is basically reimplementing Excel, but if all you need to do is extract/modify values and formulas or produce a workbook excel will accept there's plenty of libraries that will do that, and it's not that crazy to just hack something up using an xml parser.

I had to port some stuff that was using the google sheets API over to manipulating xlsx files instead, and it wasn't a big deal.

Re:Excel Never Dies by rchaud on Monday March 08, 2021 @12:33PM (#26388646)
Whatever is in your XLSX can be converted to a static CSV file. Charts, pivot tables and formulas won't be transferred over, but then again I wouldn't expect them to.

The value Excel provides far outweighs the drawbacks of a vendor-specific solution.

Re:Excel Never Dies by LeifCarrotson on Monday March 08, 2021 @02:16PM (#26390216)
The data in an Excel file can be exported as CSV, but the value in an Excel file is the dependencies and formulas that have been built up into a representation of the business rules. The problem is that this often becomes the only representation of the rules, and auditing it or puzzling the data out of that Excel sheet after the fact is like pulling teeth.

A business might want to get to improve, say, their quoting accuracy. I've seen lots of places that write quotes using Excel. They use a complicated spreadsheet to estimate "We need $4500 in parts from vendor A, but in previous projects with components from vendor A often needed rework, so we multiply their quotes by 1.5 to account for the risk and for someone (typically Bob) to rework them; Bob's workload is over 90% and he's less efficient when he works overtime, so multiply his total hours by an additional 1.25, we also have to adjust his hourly rate by 1.5 to account for overtime..."

It's a Hard Problem to convert the quoting process from one of a few engineers who also do quoting by copying and modifying the blank Excel template and years of human domain expertise into a process where data entry techs input stuff to a CRUD webapp. This is fraught with peril because the Javascript/SQL guy you hired to write the webapp (or, heaven help you, the SAP consultant) hasn't been reworking gear from Vendor A for 15 years and sees what looks like an error when the formula for actual cost from vendors B, C, and D takes their quote price multiplied by 1.1 (for shipping? margin? ) and vendor A's quoted price is multiplied by 1.5, and, hold on, the VBA macro separately takes the the estimated dollar amount purchased from Vendor A, divided by 2000, and adds it to the head of maintenance's estimated hourly total for the project?

Making business decisions about logic tied up in Excel formulae is hard. Writing logic in something other than Excel where you can more easily see the business logic is probably harder. Convincing non-technical decision makers to learn VBA to evaluate their vendor selection is probably harder still.

Re:Excel Never Dies by rchaud on Monday March 08, 2021 @04:28PM (#26391838)
I wasn't really able to follow this, if I'm being honest. But given the number of dependencies you listed, both human and software-based, it does not seem like Excel is the problem here.

It sounds like VBA has allowed that team to build an advanced prototype of a quote generation web app. The next step seems to be to convert the Excel formulas and scripts into JS or Python. Quality assurance may be a hassle, but that is to be expected with any kind of refactoring.

Re:Excel Never Dies by LeifCarrotson on Tuesday March 09, 2021 @10:15AM (#26399543)
I'm sorry it was hard to follow. The Excel sheets I've seen have been hard to follow, too! Also, most of the work is not actually VBA (there's a little), but formulas in spreadsheet cells.

The key difference is that the Excel spreadsheet is not a prototype: It's an MVP, which includes "viable"; many businesses have been making money with them for years. Another key feature of the 'prototype' is that the team is able to edit the Excel formulas, but burying the formulas as JS or Python (whether locked away serverside or simply obfuscated by nature of being different language with a new learning curve) removes a critical feature.

Re:Excel Never Dies by lmm on Monday March 08, 2021 @09:49PM (#26394848)
If you want to convert a hacked-together VBA app into something more robust, you have a migration path via VB.net which will interop properly with C# or F# or more-or-less-Python (IronPython). Or you can use something like thrift/protocol buffers to have your hacky part talk to your nicer part. It's harder to do that with Excel - make an application that's half Excel sheet and half "real program" - that's the part that's missing (though I don't think it's actually as bad as grandparent is saying - there are options for integrating external datasources into Excel that do work).
Re:Excel Never Dies by zelphirkalt on Monday March 08, 2021 @02:07PM (#26390076)
Now we only need to get everyone to export to a standardized CSV, perhaps one adhering to its own name, comma separated values, and we are good.
Re:Excel Never Dies by aarondia on Monday March 08, 2021 @01:12PM (#26389294)
Lack of interoperability is an interesting angle, but not one that I've ran into quite as much. I've usually gotten around that by converting to a csv file in the case of data, or screenshotting graphs, etc.

Would love to hear a bit more about your workflows where you're trying to process an .xlsx file in another system. I'd imagine it would be a nightmare, but haven't ran into it myself :)

Re:Excel Never Dies by fartcannon on Monday March 08, 2021 @01:25PM (#26389489)
CSV files is one of the main problems with excel. Auto-guessing formats is the bane of many casual importers. And the warnings about tsv or csv when you open is tantamount to dark pattern. I can accept it once, but let me disable the pop up. Do Not auto save to xls or xlsx.
Re:Excel Never Dies by aksss on Monday March 08, 2021 @02:20PM (#26390270)
I don’t see that as a dark pattern because it’s honest - I will often open up a CSV and then turn on filters, sort the data, maybe highlight a few rows. The warning about losing that when trying to save as a csv is an honest warning. The same as opening a jpg in Photoshop or Gimp, adding layers and trying to save it back out - you’ll also get a warning that you’re about to lose work by saving to a simple file format.
Re:Excel Never Dies by fartcannon on Monday March 08, 2021 @02:32PM (#26390405)
Do it once, provide a "Don't remind me again" check box and save that for eternity.

The dark pattern is in repeatedly nagging me about this fact.

Re:Excel Never Dies by orhmeh09 on Monday March 08, 2021 @04:26PM (#26391816)
How is that a “dark pattern?” There’s no deception involved. You’re calling things “dark patterns,” but I think you think dark patterns are just UX you find annoying.
Re:Excel Never Dies by fartcannon on Monday March 08, 2021 @04:57PM (#26392208)
I call it a dark pattern because it indirectly discourages the use of interoperability in file formats.

The suggestion is 'use xlsx or suffer this annoying pop up'.

It's an easy fix. Have an option to disable it.

The other dark pattern, since we are chatting about them, is that you cant disable auto formatting. Excel will try to guess at your data, forcing you to jump through hoops to prevent it. The subtle suggestion is 'just use xlsx and this problem goes away' when in fact, they could offer an option to disable autoformating.

Re:Excel Never Dies by gsich on Monday March 08, 2021 @05:17PM (#26392404)
CSV is not interoperable as you lose the formulas.
Re:Excel Never Dies by RcouF1uZ4gsC on Monday March 08, 2021 @06:01PM (#26392831)
In terms of interoperability, on the greatest things about Excel is Ole Automation. You can write a C++ or Visual Basic or C#, program that can programmatically drive Excel. You can pretty much do whatever you want.
Re:Excel Never Dies by serjester on Monday March 08, 2021 @12:21PM (#26388424)
Horrendous? Strongly disagree if you're doing anything non-trivial. As soon as you start mixing the two, both become worse. You're basically losing the UX benefits of native Excel and adding confusion to Pandas.

Yes, Pandas has a learning curve but so does Excel once you get into advanced functionality. It's inevitable. Once you get through this it's a fairly intuitive powerhouse.

Re:Excel Never Dies by aarondia on Monday March 08, 2021 @12:39PM (#26388761)
That's a good point -- and maybe horrendous was too strong of a word. It's actually been really interesting talking to people who come from the Excel world vs the Python world first. There's a group of people we've ran into who have never used Excel before, and for them, you're right, giving them a spreadsheet interface to write formulas and manipulate their data is actually a huge disadvantage.

I think the way that Mito tries to walk the line is by making the Python code visible for the user to see what the equivalent Python looks like + easily usable in your analysis, but also completely generated for you. So hopefully, we're not introducing the confusion of pandas into your workflow.

Re:Excel Never Dies by screye on Monday March 08, 2021 @12:10PM (#26388238)
> best way to build intuition for a dataset

Could you elaborate on this please ? I work with a lot of datasets, and have found python + libraries (plt/pd/np/scipy/regex) to be far more useful. But, that might just be my inexperience with excel.

Can you give a few examples of analyses that work better in excel than python ?

Re:Excel Never Dies by csydas on Monday March 08, 2021 @12:57PM (#26389074)
Not the GP, but I think they are speaking specifically to non-programmers with this statement.

It's not about which analyses are more performant/easier in one object versus the other, it's how do you most easily introduce the general audience to big data, both reading, manipulating, and transforming.

I actually disagree with their statement tbh, as I think that it's too nuanced of a situation to scope like this.

I used to work in a university, and depending on the dataset and the intended output, I would switch between R and Excel for the students. Those who needed R level analysis eventually saw why it was more useful for them than Excel and got good at seeing when to use R versus when to use Excel.

Those who had datasets/output goals that didn't need heavy lifting really just needed Excel. It's not incorrect to say that learning heavier tooling/languages is a benefit, there is also a time consideration to learn and become efficient at a given toolset. The heavier toolsets have their nuances and accomplishing the same task in less robust toolings like Excel is the more efficient and better approach for those who have extremely limited time and for those who are not likely to need the heavier toolset in the future.

It's just a simple cost benefit analysis -- what tool is going to give the best return on time investment?

There is a very valid and reasonable argument that investing into the heavier toolsets will eventually reach a point where even the simple tasks that Excel and other tools allows users to perform more easily with less knowledge is faster/better with the heavier language; the question is "when is it optimal for a given person to invest the time to get to that stage?", and that's a question that doesn't always have all available data to make an informed decision on since it's hard to predict the future.

Re:Excel Never Dies by aarondia on Monday March 08, 2021 @01:35PM (#26389633)
You're definitely correct that its a nuanced question whether for a given (user, analysis) pair they are better off in Excel or Python/R/etc. Specifically with respect to building intuition for a dataset, however, there is a huge benefit of having an interactive data representation (if only for the ability to scroll and see all of your data).

Because you can think of Mito as a frontend interface to Pandas, using Mito doesn't prohibit you from building intuition or analyzing your data in the same way you would if you didn't have the spreadsheet frontend. It just helps you write the Python/Pandas code faster + see the most up to date version of your data set in live time.

The typical Mito user uses Mito multiple times throughout an analysis. A common pattern is: start by just visualizing the data in Mito, create a few graphs to help understand the distribution using matplotlib (right now we only have a tiny bit of graphing support), passing the data back into Mito to do some filtering and cleaning, then lastly creating a pivot table output using Mito. Of course, it varies greatly from user to user, but that's a general flow we see often!

Re:Excel Never Dies by iamacyborg on Monday March 08, 2021 @01:21PM (#26389425)
I think it's more simple than that. The way the data is presented to you in Excel makes it incredibly easy to grok.
Re:Excel Never Dies by F_J_H on Monday March 08, 2021 @01:42PM (#26389730)
For me it's SQL and then simply visualizing.
Re:Excel Never Dies by aarondia on Monday March 08, 2021 @02:10PM (#26390108)
As a bit of background on Mito, it works by passing the parameters from the frontend spreadsheet to the Python kernel backend, which transpiles the spreadsheet formula into Python/Pandas code [0]. So what we're hoping to do down the line is let the user pick which language to translate to, SQL and R being the obvious next steps. But that's a ways away :)

[0] https://trymito.io/blog/transpiler

Re:Excel Never Dies by fifilura on Monday March 08, 2021 @02:40PM (#26390507)
The problem with SQL is that it is not great with pivoting. But maybe that is not a big problem when you auto-generate the SQL.

I agree, SQL is what I like more for mangling. Except for the pivot/melt part that is

Re:Excel Never Dies by hypermachine on Monday March 08, 2021 @12:16PM (#26388332)
Mito looks really interesting, I look forward to trying it out. I'd there a way to sign up for Mito without the hubspot meeting?

For us we are going the opposite approach, we are building a VB interpreter to make it easier to run, build, and extend existing Excel programs. We allow calling libraries written in WebAssembly and GraalVM supported languages.

Re:Excel Never Dies by aarondia on Monday March 08, 2021 @12:42PM (#26388810)
Feel free to Twitter DM me @_aaronDR, would love to hear about what you're building + get you set up with Mito :)
Re:Excel Never Dies by agumonkey on Monday March 08, 2021 @01:01PM (#26389136)
Excel, depending on who organizes the data, can lead to god-table with 123 columns.

Now someone with a bit of balance, can go quite far with it.

Re:Excel Never Dies by 1980phipsi on Monday March 08, 2021 @01:33PM (#26389592)
New sheets are your friend.
Re:Excel Never Dies by Robotbeat on Monday March 08, 2021 @11:59AM (#26388063)
What non-spreadsheet programming languages/environments do you think work better than Python/Jupiter?

I’ve been fairly happy with the default Matlab IDE personally. Visibility and representation of data has the straightforwardness of a spreadsheet. But surely there must be others?

Re:Excel Never Dies by hypermachine on Monday March 08, 2021 @12:06PM (#26388169)
A lot of environments do not have easy to use hot reloading out-of-the-box (and also quick GUI creation support). This is the primary reason why development feels less intuitive in a lot of text based languages versus more visual tooling like Excel which can give instantaneous feedback.

Hot reloading is most famous for being a staple of Lisp languages (but they tie it to the repl rather than as a standalone feature). For Microsoft languages this is provided by Visual Studio (commonly known as edit-and-continue, it is available in some form or other since the original VB days). You can try it out with the embedded VBA interpreter in Excel (under the Developer tab).

For JavaScript this is a recent innovation (driven primarily by the React/SPA crowd). In Java, most IDEs have the feature but it requires a fair bit of setup and configuration (look up hot swap for Intellij). The closest thing Python has is Jupyter which admittedly is not that pleasant to use.

Re:Excel Never Dies by lispm on Monday March 08, 2021 @12:24PM (#26388477)
> but they tie it to the repl rather than as a standalone feature

Lisp has a function called LOAD, which can load source and/or compiled code.

Re:Excel Never Dies by hermitcrab on Monday March 08, 2021 @03:04PM (#26390773)
There are a number of visual data transformation environments, including https://www.easydatatransform.com (I am the developer), https://www.alteryx.com and https://www.knime.com .
Re:Excel Never Dies by twobitshifter on Monday March 08, 2021 @02:45PM (#26390558)
Julia in VSCode is nice. The speed gains from Julia are worth it alone if you are working with large datasets.
Re:Excel Never Dies by Robotbeat on Monday March 08, 2021 @06:47PM (#26393269)
I’m putting this on the long list of reasons to eventually migrate to Julia.
Re:Excel Never Dies by airstrike on Monday March 08, 2021 @12:10PM (#26388222)
> What non-spreadsheet programming languages/environments do you think work better than Python/Jupiter?

RMarkdown + RStudio + knitr

Re:Excel Never Dies by jdgoesmarching on Monday March 08, 2021 @12:25PM (#26388500)
I really wish Python had the kind of support for Markdown that R does. Notebooks are fine, but the simplicity of having essentially a plain text file is just amazing.
Re:Excel Never Dies by Something1234 on Monday March 08, 2021 @02:25PM (#26390315)
Rmarkdown theoretically supports python. I haven't used it though.
Re:Excel Never Dies by vharuck on Monday March 08, 2021 @04:58PM (#26392215)
Knitr theoretically supports anything you can call from R. Chunks pass their code and options to "engine" functions that return the results. Knitr provides a bunch of engines out of the box (Python, awk, SQL). But you can also write your own.

yihui.org/knitr/options/#language-engines

I once made a SAS engine to show coworkers how to adopt report automation without having to rewrite all existing code.

Re:Excel Never Dies by sonthonax on Monday March 08, 2021 @02:15PM (#26390183)
Nice product, I noticed that you are updating the next Jupyter cell; what was your solution to doing that reliably since `set_next_input` is so damn flakey?

I personally grew so frustrated with the state of GUI development in Jupyter that I tried to fix it in such a way that would allow proper message passing between cells and python code (because you can't wait on Comm events).

> https://github.com/ipython/ipykernel/pull/589

But sadly the priorities of big open source projects don't always match your own. So I had to extract that logic into my own kernel.

Re:Excel Never Dies by igorkraw on Monday March 08, 2021 @12:17PM (#26388349)
How does it compare to visidata?
Re:Excel Never Dies by aarondia on Monday March 08, 2021 @12:56PM (#26389044)
There's a bunch of ways that Mito and Visidata are different. The two largest probably being:

1) Mito is an extension to JupyterLab whereas Visidata is a CLI tool. As a result, Mito is a react frontend that is more of a traditional Excel-styled spreadsheet interface. You can use your mouse to perform point-and-click transformations, like writing configuring pivot tables or writing spreadsheet formulas.

2) Mito generates Python/pandas code for every edit the the user makes. So users are generating a script to manipulate their dataframes, running that script, and then continuing to use their dataframes throughout the analysis. People use Mito in a Jupyter notebook the way that they use pandas code, multiple times throughout their analysis, interspersed with graphing, ML, etc.

Re:Excel Never Dies by civilized on Monday March 08, 2021 @12:57PM (#26389064)
I'm a data scientist who isn't an Excel wizard and I'd like to have the other direction, turn my dplyr code into Excel
Re:Excel Never Dies by aarondia on Monday March 08, 2021 @01:07PM (#26389219)
I'm less familiar with the R ecosystem, but there are tools in the Python world to go that direction -- https://xlsxwriter.readthedocs.io/
Re:Excel Never Dies by tryitnow on Monday March 08, 2021 @12:48PM (#26388934)
What's the pricing model? There's a lot of potential here.
Re:Excel Never Dies by aarondia on Monday March 08, 2021 @01:00PM (#26389124)
We're still exploring the business model to support the project. Right now, Mito is free to trial, and we're experimenting with a subscription plan somewhere in the range of $10 per month. I'd say about half of our users are using it for free and half of them are on some sort of subscription.

We're also considering open sourcing the tool, and doing the classic Enterprise Sales / consulting / other value add services on top.

If you have ideas about which direction to take it, would love to hear!