
Applying programming language research ideas to transform spreadsheets - pjmlp
https://www.microsoft.com/en-us/research/blog/influencing-mainstream-software-applying-programming-language-research-ideas-to-transform-spreadsheets/?OCID=msr_blog_PLspreadsheets_popl_highlights
======
cm2187
It is great that Microsoft is investing in Office again. I just got upgraded
at work from office 2007 to 2016 (we like to take our time, I was on WinXP not
so long ago), and I can barely see the difference. They changed some colors,
there are menus that open in a panel instead of a modal box. I don’t think a
single user cares about these changes (and they seem to have carefully
preserved the bugs). And the problematic UI (for instance the tiny non
resizable modal when you click the fx button) have not been updated.

But there are still massive pain points or improvements that could be added.
For instance:

\- linking between excel and powerpoint. Almost anyone who will show an
analysis, whether in a bank, accounting firm, consultant, etc will prepare
some table and charts in excel and will want to use them in powerpoint. The
link between the two is unstable, screws the formatting, or requires lots of
manual steps.

\- Microsoft should really look into Apple’s take on excel, with a sheet being
a canvas on which you can add tables, charts, the tables have scroll bars if
they overflow. Instead of the rigid single grid per tab, where if you show two
tables one over the other you need to align the columns even when they have
different data types.

\- VBA hasn’t been updated in 20 years and it clearly shows. There needs to be
a more modern scripting language. I have heard rumors about javascript. Not
convinced it’s a great choice, but that would still be better than VBA.

~~~
kgwgk
Microsoft want’s to kill VBA but customers won’t allow that.
[https://en.m.wikipedia.org/wiki/Visual_Studio_Tools_for_Offi...](https://en.m.wikipedia.org/wiki/Visual_Studio_Tools_for_Office)
may make sense for developers, but users like the ability to do things in the
“wrong way”.

~~~
cm2187
VSTO isn't great. There was something called VSTA earlier, basically a little
visual studio embedded in Office, you could do with any .net language what you
could do with VBA. Unfortunately they canned it.

~~~
clausok
Especially with ExcelDna, it's hard to make an argument for using VSTO. And
even with ExcelDna (i.e., an xll written in F#\C#), I find there are still
niche areas where vba is effective, particularly when working with the Excel
object model. The split I like is to have vba orchestrate interactions with
the object model, Sheets, Books, Ranges, Range.Find(), etc. So for example,
have your F#\xll code parse "60d" into the last 60 trading days, and then use
vba to select those days in Excel's autofilter.

~~~
cm2187
Possibly stating the obvious, but you know that you can manipulate the excel
object model from ExcelDNA in the same way that you can with VBA (thanks to
VSTO!).

Add a reference to Microsoft.Office.Interop.Excel, and get a handle on the
Excel Application object using ExcelDna.Integration.ExcelDnaUtil.Application.

Alternatively you can use the excellent NetOffice.Excel (instead of
Office.Interop.Excel), which will have less functionalities but with better
version support.

~~~
clausok
Where has NetOffice.Excel been hiding?! Thank you for that.

What I've found with interop is there's a little more debuggability/edit-and-
continue friction that can accumulate into a large loss of time during
development. Maybe that loss is offset, or more than offset, by doing the
interop with a vastly superior language. I'm not sure. Any thoughts about this
trade-off?

~~~
cm2187
I don't know. I find VBA to lack so many modern features (type inference,
linq, generics, etc) that I favor using a more modern language over
edit&continue. VBA and interop have nearly the same syntax so you can always
toy in VBA and then implement it in .net. NetOffice sometimes has a different
syntax so is a bit more iterative.

------
leibnitz27
Banks do crazy stuff with excel. I embedded a higher order functional language
natively in excel 10 years back....

[http://cufp.org/2009/fmd-functional-development-
excel.html](http://cufp.org/2009/fmd-functional-development-excel.html)

~~~
tluyben2
Banks, insurers, accountants; I know a company with 100M euro rev per year
that runs entirely on Excel with VBA. Their office car park gate is opened,
closed and managed with Excel.

It sounds crazy but the CTO is a cofounder and he found it is much cheaper to
just do everthing that way. They have been running like that for over 20
years.

~~~
ramraj07
One possibility is this - the people who mess with Excel VBA are generally
very smart folk who just never went too techy, but then got really good at
Excel and just learned vba as the next logical step. That means the code might
not be kosher but it will be thoughtfully written and encompassing all the
practical use cases.

Contrast that with a 20something can grad who while competent isn't as smart
as that non tech guy, and has indoctrinated the cs way of doing things w.r.t.
test coverage and unit tests but often will lack the insight of what the
program is actually trying to do. Frankly I would also like to choose the
former than the latter.

~~~
cm2187
A good recent example of that. We were asking a dev team to build a dashboard
(we are a bank) showing capital ratios. The team came back saying voila! We
looked at it, and the headline number in bold at the centre of the dashboard
was showing a cet1 ratio of 1300% (a cet1 ratio is typically in the 5%-20%
range). I am sure they used modern programming techniques, but they left an
error that would have shocked even the least sophisticated user.

I have many similar annecdotes. Having a good understanding of the underlying
domain and what numbers to expect is critical to writing correct models and
too often I see dev teams in banks that have no understanding or curiosity of
what they work on.

~~~
antpls
On the other hand, a dashboard is probably one of the most boring stuff to
work on when you have a CS degree. Typically, a dashboard is asked as a
deliverable to keep the dev team busy while also making sure other people's
jobs in the bank arent rendered obsolete if the dev team were used to its full
potential.

The people saying Devs dont do enough effort to understand the domain are the
same people who try to keep the "domain" overly complicated to justify their
jobs/positions

~~~
angry_octet
You're right, a dashboard is a project for when you don't know if they can do
anything useful.

I think the fault is actually the split between IT (a support function) and
the product group, which often doesn't have/isn't allowed embedded devs.

IT managers are often toxic for delivering, never take ownership, SLA
bullshit, too much PRINCE2 and ITIL.

If you can get developers embedded, huge wins are possible. But they have to
bring the client with them, not code away in isolation.

------
bvaldivielso
It's interesting to note that one of the people behind this work is Simon
Peyton Jones, known (among other things) for creating haskell and being the
lead developer of the GHC.

~~~
quickthrower2
Isn’t he busy at Facebook or is he though with that?

~~~
xfer
SPJ never worked at facebook.. maybe you are thinking of Simon Marlow?

------
chrispsn
This is awesome (let expressions in formulas!!), but Mesh Spreadsheets has
offered most of this for a couple of years, and in a free, open-source form.

Specifically:

\- arrays and objects living in a cell

\- mix of spreadsheet and textual views

\- functions in spreadsheet cells, and running spreadsheets as a messaging
service (as a lambda function / API)

\- dynamic array sizes

We deliver this by offering a grid view of (structured) JavaScript code.

[http://mesh-spreadsheet.com/](http://mesh-spreadsheet.com/)

~~~
snazz
I had never heard of Mesh before. It looks very cool. I wonder if the concept
would work well on mobile devices (unfortunately the editor itself doesn’t, as
it stands now).

~~~
chrispsn
Thanks! Yeah, marketing has been limited as I want to improve the error
experience first.

To be honest, mobile is not a focus as I think it's hard to be productive
without a proper keyboard. I'd like to make the iPad + kb experience better in
the long-term though.

------
dtech
If we're talking about Spreadsheet programming, the work of Felienne Hermans
[1] [2] (former mentor of mine) is definitively worth a read. She and her
group have done a lot of research into how spreadsheets are used as software
in companies and how software engineering research can be applied to them.

[1] "Spreadsheets are Code" \-
[https://www.youtube.com/watch?v=bdfNvYPxkOY](https://www.youtube.com/watch?v=bdfNvYPxkOY)

[2]
[http://www.felienne.com/publications](http://www.felienne.com/publications)

------
sorenjan
I rarely use Excel nowadays, but if I could choose one thing to improve it
would be to let me use the English names for functions. Excel is localized to
my language, and that includes function names. That probably made sense when
targeting non technical people 30 years ago, but it makes it harder to search
online and is non intuitive when you're used to other programming languages.

~~~
sbierwagen
Set your system locale to US/English, then.

~~~
sorenjan
It's not always my choice, but I also don't want American dates, decimal
point, and so on.

------
thibautg
The ideal spreadsheet should be a mix of Excel, Geogebra, reactive Jupyter
Notebook (like ObservableHQ or Vue.js computed properties), programmable in
Python, C#, JavaScript and/or other languages with the ability to embed third-
party widgets like maps or custom charts. And decent data connectors with SQL-
like query syntax (including JOINs).

~~~
dvdhsu
Hi — that’s what we’re working on! Retool
([https://tryretool.com](https://tryretool.com)) is similar to Excel, except
the cells are themselves React components. And you can refer to other
components inside the props for each component. You can pull in data either
via SQL, APIs, or any other data source we support. Everything inside Retool
is also JavaScript, so it’s pretty hackable.

If you’ve got any feedback, please let me know! I’m david@. Thanks!

~~~
james_s_tayler
I read the domain name as Tyre Tool.

~~~
dvdhsu
Thanks for your feedback! I’ll look more into what we can do. We tried
purchasing the .com for around 6 figures, but the buyer wouldn’t sell. I’ll
try sending him an email again.

~~~
Intermernet
I thought I'd seen a .tool TLD that may have helped you but unfortunately it's
.tools instead.

------
jugg1es
As a programmer, excel would be so much more useful if it allowed you to do
things like filtering and selects using SQL or some other code so you didn't
have to rely on the UI so much to do filtering.

~~~
andylynch
Try powerQuery (Microsoft’s addon for just this) - it does it really well

~~~
gerdesj
As well as that you can roll your own functions which can be used in cells
just like the native ones and call out to other languages and the shell. It
really is rather powerful for minimal effort.

However, with great power comes great responsibility ...

------
Cieplak
This is amazing and much-needed progress for Excel, which I think is arguably
one of the greatest software products ever (except perhaps the web browser).

Unfortunately for Excel, it’s essential to Microsoft’s product strategy for
keeping people on Windows, which means that Excel on other platforms feels
almost as if it were sabotaged compared to the “native” experience in Windows.
I feel like Excel could really thrive if only it could separate itself from
the mothership and focus on its own interests.

~~~
pjmlp
Actually, it is essential to keep people on Office, Windows is secondary,
hence the big push for Office 365 subscriptions.

[https://www.theverge.com/2019/1/18/18187990/microsoft-365-co...](https://www.theverge.com/2019/1/18/18187990/microsoft-365-consumer-
subscription-satya-nadella-comments)

------
ohazi
Types and units would be super useful. I recently lost a few days of work
after realizing that a part analysis spreadsheet I was working on for
evaluating low noise op-amps was producing incorrect results because I
accidentally divided by 1000 instead of a million in a unit conversion
somewhere.

I knew the starting and ending units, but Google calculator and Wolfram alpha
have made me sloppy.

~~~
selimthegrim
Hey Oren, (I was a few years ahead of you, in Ruddock), you might be
interested in my friend’s product I mention below.

------
bsder
The thing I need most is for "Save" to do "Pull in dependencies and and check
into version control".

That way my people can still use Excel but I have a way of validating and
controlling it.

~~~
angry_octet
A way to diff would be amazing. Textual diff (these formulas changed) and
visual (ie highlighting block formula differences).

------
Aardappel
TreeSheets (hierarchical spreadsheet:
[http://strlen.com/treesheets/](http://strlen.com/treesheets/)) has a simple
visual programming language that is part of the sheet (doesn't hide formulas):
[http://strlen.com/treesheets/docs/images/screenshots/screens...](http://strlen.com/treesheets/docs/images/screenshots/screenshot_sales.png)

------
tluyben2
Having Excel save and load readable and editable source file would be the best
improvement for me... Version control, easy editing with vim etc while
interoperable for people who like Excel. No programming language research
needed for that; just normal programming best practices.

~~~
imhoguy
XLSX files are just zipped XML files:
[https://en.m.wikipedia.org/wiki/Office_Open_XML](https://en.m.wikipedia.org/wiki/Office_Open_XML)

Hint: as far as I remember compression ratio is 0 (stored) - best to keep such
on re-save for compatibility.

~~~
tluyben2
Not really readable or editable though.

~~~
pjmlp
They are with the help of tooling.

[https://docs.microsoft.com/en-us/office/open-xml/open-xml-
sd...](https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk)

------
aapjaap
I always think a spreadsheet that understands units would be nice: A1=10A,
A2=2Ω, A3=A1*A2=20V, something like that

~~~
petepete
If only ResolverOne had taken off or been open sourced.

[https://en.m.wikipedia.org/wiki/Resolver_One](https://en.m.wikipedia.org/wiki/Resolver_One)

------
RcouF1uZ4gsC
Excel is probably the most successful programming environment in the history
of computers. It is great that instead just trying to tell all those people
that they need to migrate to something else, and path forward is being
provided.

------
sctb
Related presentation from SPJ et al.:
[https://www.slideshare.net/kfrdbs/peyton-
jones](https://www.slideshare.net/kfrdbs/peyton-jones).

------
loktarogar
Programmers: Let's make something better than a spreadsheet

Microsoft: Let's make better spreadsheets

------
p33p
This is pretty neat. Tangentially, does anyone have a good way of version
controlling excel files? I deal with people in the finance domain all the
time, and for some files that come across my desk, it's terrifying to see
multi-million dollar decisions being made in a file where anyone could change
anything and not know any better.

~~~
AareyBaba
Excel in office 365 cloud allows multiple users to edit the file at the same
time, autosave and version control.

~~~
p33p
Can you provide a link to the O365 version control? I can't find anything
online about it.

------
Apaec
Excel seems like the ideal frontend for CRUD apps. It already has all the
controls one needs, reusing these would be better than reimplementing them
again and again in custom webapps.

Has anyone integrated Excel with a db like PostgreSQL?

------
aj7
A simple scripting language, like LabView’s C subset, that could take
arguments out of cells, and write to cells, would be welcome. VBA has too much
gobbledegook for a user like me. Python could also be embedded.

~~~
zokier
Excel support JS these days [https://docs.microsoft.com/en-us/office/dev/add-
ins/excel/ex...](https://docs.microsoft.com/en-us/office/dev/add-
ins/excel/excel-add-ins-core-concepts)

~~~
fcrick
mostly useless as if you do anything, the undo queue gets cleared.

------
d--b
Crédit Suisse had the ability to create lambdas in excel in 2005. It only took
14 years for Microsoft to figure out it was a good idea

------
tunesmith
I was on a spreadsheet yesterday and I had range of cells that I really just
wanted to "map" into another range of cells. I wish scala notebooks could be
viewed spreadsheet style, something like that. Just to be able to write a
function (formula) in the language of my choice, and then reference it from a
cell.

------
rdlecler1
We’re starting to use Airtable in a pretty sophisticated way. It allows you to
organize yourselves and be pretty dynamic as you learn and adapt your process.
We can then have our engineers come in once the data model is robust but
frankly I see our team using the Airtable UI for a long time.

------
KLVTZ
Love the article's main photo. The first thought that came to mind: now THAT
is a programmer.

~~~
quickthrower2
Not sure what you mean but who knows if they are programmers with so much
stock photo usage these days.

~~~
peterkelly
The guy in the photo is Simon Peyton Jones, one of the creators of Haskell,
who works for Microsoft Research.

