Excel's dominance in the field is because it is an _application container_ that _non_ dev people can use.

The workflow is this:

- old trader guy says to his junior guy: "hey can you look into xxx."

- junior trader guy says: "sure I'll make a spreadsheet for it"

- old trader guy: "great your model is all I need, let's trade"

- several weeks later, IT guy says: "hey you're running a $100m book out of a spreadsheet, we'll make you a nice system for it, cause your stuff will blow up."

- several months later the IT guy comes back with a web app that does the same thing as the spreadsheet.

- old trader guy says: "hey I can't copy shit around, my shortcuts aren't working, I need to be able to do basic maths on the side, I can't save my work, etc."

- IT guy: "ok I'll make you an export-to-Excel button"

Seriously I've seen this happen over and over again.

The issue is not how to get rid of Excel, it's how do we make a better spreadsheet...

The smartest thing that I did was make it accessible from Excel. You could build a spreadsheet off of my report. Refresh the spreadsheet, the report ran, you got updated data.

I got essentially 100% adoption, and the rest of my job was spent finding people who needed data and adding it as an option to the reporting system.

Usually you expect a complex reporting system to have features like graphing, pivot tables, etc, etc, etc. My answer to all of that was, "You can already do that in Excel. I could spend a lot of time on it but I'm not going to do it as well as what you already have."

My customers are not finance people, but they were using ad-hoc spreadsheets extensively and most of them through G suite. By implementing bidirectional sync for both master data and outputs they have the best of both worlds.

The Google Drive and Sheets APIs (and especially the on-change push notification callbacks) are not a pretty sight but once the twisty maze of nested data structures, webhooks and OAUTH2 and what-have-you were successfully navigated we got an efficient, complete and stable solution that everyone's happy with.

[0] https://support.office.com/en-us/article/Get-external-data-f...

Since it was a system for internal use, that was deemed acceptable.

So that gives the user the ability to query for a certain date, or sum for a date range, or to get a breakdown by some meta data. Being able to load multiple reports allows them to do deltas. And switching to a newer version of the report just takes changing the argument of the load function and press F9.

Worth also giving the users sample spreadsheet, or having a way to export the full report with all these formulas in Excel so that they can learn by example.

The only thing to watch for is memory clean up. You need somehow to have a way to unload all reports otherwise your memory blows up over time.

My Julia code creates Excel sheets including charts and sparks

I use this shiv to Python's XlsxWriter

https://github.com/lawless-m/XlsxWriter.jl

With a bit of effort you can even make it set up scenarios for Excel solver

The only people who think it can be or needs to be done better are primarily not the users.

Excel is the human condition, messy, varied, capable of doing many things and most of the features aren't used, and results are often horrifying. But it works.

That said, I want regex based search in excel, or better data cleaning tools.

At Uni, decades ago (and using Lotus-1-2-3 rather than Excel), we were given a set of spreadsheet templates for a business management exercise (the project ran much the duration of the course). I discovered looking at these that there was an error in the spreadsheet double-counting all inputs (rows and columns were added and the row-totals included in the grand total). Best I can tell, finding that bug was not in fact part of the course goal....

At an early gig doing analytics we hired an old fart who actually introduced some rigour to our project practices, including code walkthroughs. Some of this involved more obviously programmatic tools, but the process was also applied, over the strenuous objections of the analyst involved, to a project largely done in Excel. Numerous errors were found, and corrected. The spreadsheet was also significantly rationalised by having "dumb" data-entry area, and then relying on a sheet with VBA code rather than in-cell logic for the computations.

The analyst continued to object to the code review, after the errors were found and corrected, insisting that it was inappropriate because he "wasn't a programmer". (I looked him up recently, he's continuing to work on analytic projects, two decades on.)

This was also about the time that I first heard of Ray Panko, at the University of Hawaii, who'd found reliably that 88% of spreadsheets had errors. This still holds largely true.

http://panko.shidler.hawaii.edu/

I'm not saying "get rid of all Excel". But I am saying that the tool makes for very, very, very buggy processes, and this is itself a problem.

I'd love to see work toward alternatives that retain the flexibility and versatility of Excel (or other spreadsheets) but reduce the possibility of bugs and errors.

Multiple times I've come across issues like this when developing the replacement for an old access system. The numbers between the new an old system don't match, so you have to dig through both to find the error. If the error is in the new version that's great, but when you tell the boss that the error is in the old one and they've been off by a few million dollars over the years...

However, development costs are so expensive they might have broken even when factoring the cost of creating a system.

Now I just offer handy things like pre-sorting, filtering (some boolean columns to allow them to quickly filter stuff), better column names or reports with extra info. I try to figure out the things they find tedious and automate them once and for all. But I hold back as much as I can.

Couldn't agree more. It's also a huge, latent liability. In my experience, mostly because it's an opaque data store.

To use the parent's example. Turn's out the $100mm business is actually only worth $80mm because someone had accidentally coded GBPUSD directly into the spreadsheet. Before Brexit was announced. Say goodbye to senior trader.

This sort of thing happens though not as much as it used to as we don't allow Excel in the path that hits the ledger.

http://www.codedawn.com/excel-add-ins.php http://blog.malcolmp.com/2010/regular-expressions-excel-add-...

I'm a marketer who echos the "pry it out of my cold dead hands" statement, and this is an interesting aspect of it that I hadn't thought as much about before.

There's really something to be said for having ALL of the data and model at your finger tips for quick sidebar calculations, and there's something to be said for formatting tools to make it easier to read and work with. Often times I'm building a model or report, and need to do some quick checks against different assumptions, or have a little scratch pad of notes or placeholder values somewhere.

Being able to just toss those into somewhere a few cells over so I can see things in context while I work is a godsend. If the model was built as something with a front-end that didn't expose everything, I'd have to manually copy that info out and just do it in a spreadsheet anyway.

I get that there are times when it absolutely makes sense to not have things live in a spreadsheet, and what the risks are around that. But there's also risk in slowing things down, or not having certain things immediately jump out at you because you're seeing all the numbers change, and can quickly modify things as needed to check yourself.

Someone should write a book about productionizing excel models. It's not going away (and shouldn't).

Nah, this is not gonna work... The only way out is to make something that's somewhat like a spreadsheet but with source control, a nicer language etc.

I was writing it and then the firm I was working out threatened to sue me for bullshit IP infringement...

It became an instant hit in that company and I short of a medal :).

[1] http://www.fast-standard.org/

Story 1: I Inherited a system once that had 1000s (yes thousands) of excel spreadsheets checked into SourceSafe (yes Source Safe) each sheet represented an Equity Derivatives trade, which was checked out and used by traders when pricing a deal. Also...now it gets fun...there were common functions for calculating the present value and risk metrics for each trade...so when we needed to calculate the risk the traders were running (intraday and end of day) we had a huge compute grid that would check the spreadsheets out and run them on virtualized windows boxes and sum the results to produce the official risk metrics for the bank's trading desk. Absolutely not making that up. I should really write a blog about it...it's truely terrifying.

Story 2: What if I told you that Excel has this little know function called RTD (real time data) that let's you stream data into sheets in well....real time. So you can see all your prices ticking away without refreshing the sheet. I also took over a system that had hundrends of sheets that did this (at this point you must be thinking this guy is a sucker for punishment) one of the problems here was latency...so each trader's sheet used their local machine to get prices...and the analytics library we used to calculate prices to an 'indeterminant' amount of time to execute. Oh...and one of the sheets published the banks prices out to Bloomberg...for trading. This whole mess was sorted out by eventually pricing everything centrally and pushing consistent numbers to bloomberg, the spreadsheets and the risk systems.

My overall summary is that excel is "what is dead but may never die" and you'd better embrace it if you want to deal with front office systems. That and I think people would be surprised to see how much systemic risk some of these places are running (i'm talking about a Top 15 bank here).

I sort of came to love it as a tool, but that's what people with Stockholm syndrome say right?

Pharma company - took the manual Excel process and automated the living daylights out of it so that what the departing analyst took days to do would now be done in minutes.

Petrochemical company - took the reports being done in Excel by a petroleum engineer (each taking 2 hours to do each) and automated to allow him to do the reporting for 50 client companies in under a minute.

Whether Excel or Access, these tools will be used by the SMEs and nothing any IT department does will change this. What IT needs to do, is make sure that the models used are accurate and that the spreadsheets and databases are correct.

Most people who use either Excel or Access make interesting blunders in their creations and end up basing decisions on incorrect output from their creations. It takes an experienced and clue-in developer (IT person) to speak with and then analyse the creations to make sure they actually match what is required.

For those of us who do this kind of work, we must understand that the SMEs are those trying to use Excel and Access and that we are there to help them achieve the SME's desired outcome. The SME's need to understand that they are NOT SME's when it comes to Excel and Access, which most seem to never get this point.

Or how do we better support custom tools integrating with Excel.

Excel has a number of extensibility models... maybe too many;

* VBA,

* XLL add-ins (which is a C++ API, but check out the excellent Excel-DNA [1] which is a C# wrapper around this),

* Visual Studio Tools for Office, which doesn't support the ability to implement your own custom functions (e.g. =MyAddIn.GetStockPrice() unlike the previous two options, and

* The more recent 'Apps for Office' extensibility model across the whole Office suite, which is basically an embedded web browser instance in your Office app which gets a Javascript API for interacting with your Office document. This is also getting custom functions support in Excel [2]

At the energy consultancy I work for we have built a suite of Excel add-ins that allow analysts to get the data that they want easily into their sheets, but don't impose any (unreasonable) restrictions on how they work with the data.

[1] https://excel-dna.net/ [2] https://dev.office.com/docs/add-ins/excel/custom-functions-o...

Instead of doing one model, you may now be able to do a time series of models, or calculate The VaR (Value at Risk).

There is no end to what people can ask from data. The only limitations are technological. And whatever extension you may have, your users will always push Excel to its limits.

One middle ground is libraries like SpreadsheetGear that give you a UI that has an almost perfect excel look and feel while giving you more control. You don't need to integrate your tool in Excel, you can integrate a quasi-Excel in your (.net) tool.

I don't even have excel installed on my windows desktops anymore. A 50 dollar tool from an upstart programmer is better for me than excel.

Let me give you a practical example I ran last week. I want to construct a portfolio of loans starting with an existing portfolio, such that it follow a certain total balance and maturity profile through time, by replenishing it every month with new loans of various maturity (I am solving for the individual notionals of these new loans).

So inputs are

1. the existing portfolio, hundreds of entries with various parameters: start, end, notional, etc

2. the target maturity profile at every point in time

3. the target balance of the portfolio at every point in time

A simple LINQ query won't do. For the example above, you want to model it in C#, but the inputs are lots of tables. In that case I find the optimal way is for the script to accept spreadsheets as input files (wrote a "xlsx (de)serialiser") and output files. Effectively Excel becomes the UI of the script. Nothing beats the Excel UX in giving you the ability to build a table, change or copy/paste data in bulk, etc.

If it were too big (probably not if Excel can handle it) I could also just load up the data into memory via a TSV/CSV deserializer, of which there are a few for .NET. If it comes as excel from the source, excel extraction is a piece of cake these days (the codebase I work on takes excel as raw input for data feeds).

As for nothing else letting you build/change/copy/paste data into a table, Sublime Text Advanced CSV package is fantastic for editing tabular data, and Sublime Text has no problem handling files many gigabytes in size.

Shameless plug: I am a founder of AlphaSheets, a company working on solving all of these issues. It's quite scary (building a spreadsheet is like boiling an ocean) but our mission feels very meaningful, we're well-funded, and we are now stable and serving real users.

A big problem in finance workflows is that there is a tradeoff between several factors: correctness, adoption / ease-of-use, rapid prototyping, and power. We aim to solve several of these major problems. We've built a real-time collaborative, browser-based spreadsheet from the ground up that supports Python, R, and SQL in addition to Excel expressions.

Correctness is substantially addressed, because you don't need to use VLOOKUP or mutative VBA macros anymore. Your data comes in live, and you can reference tables in Python as opposed to individual cells. A lot of operational risk goes away as well, because the AlphaSheets server is a single source of truth.

We help with adoption of Python and adoption of correct systems as well. You can gradually move to Python in AlphaSheets -- many firms are trying to make a "Python push" and haven't succeeded yet because the only option is to move to Jupyter and that's too much of a disruption. It's less brittle than Excel. The important keyboard shortcuts are there.

And finally, the entire Python ecosystem of tools (pandas, numpy, etc.) and all of R is available, meaning that many pieces of functionality that had to be painstakingly built in-house in VBA and pasted around are simply available out of the box in well-maintained, battle-tested packages.

Our long term plan is to broaden our focus into other situations in which organizations are outgrowing their spreadsheets. We think there's a lot of potential with the spreadsheet interface but the Excel monopoly has prevented meaningful innovation from happening. For example, every BI solution tries to be "self-serve" and "intuitive" these days, but encounters resistance from users who end up sticking with spreadsheets due to their infinite flexibility and immediate familiar appeal.

We hope to bring the spreadsheet in line with the realities of the requirements of the modern data world -- big data, tabular data, the necessity of data cleaning, data prep / ETL, the availability of advanced tooling (stats, ML), better charting -- because we think there's a giant market of people waiting to move to a modernized but familiar spreadsheet.

If there's anyone interested, contact me, because I'd be very interested in chatting! I'm michael at alphasheets dot com :)

This is the sexiest thing I've seen since I kissed my girlfriend goodbye this morning.

My father's desk featured one of these, used exactly as described.

IT found out when they disabled a library (DAO) because "cryptolockers" could use it, only to break almost every single process at my organization. The IT director was pissed, but he had to reenable the library because everything ground to a halt.

Bonus points: he told my boss that VBA macros were bad because hackers could use them to take over the mouse and click on things.(Edit: I also distinctly remember the IT director accusing me of being a hacker when he found out I was using VBA.)

I still wake up in a cold sweat with nightmares that something broke in that system. The VBA editor in Office is an absolute nightmare when it comes to debugging. No version control either.

Just do a Save As..."Mission Critical Finance Model v2.xlsm", then "Mission Critical Finance Model v3.xlsm", then "Mission Critical Finance Model v3a (temp with changes for Jim).xlsm" and then "Mission Critical Finance Model v4 (maybe-trying to merge 3a back-DO NOT USE).xlsm" and so on...

My main database was up to version 2051 by the time I left.

The class I have nightmares about is the "compile and backup" class I wrote, that decompiled the database, appended the date to each file, compared all of the non-data and non-binary components, recompiled the database with the most recent versions, and then made sure every database was linked to the most recent versions of other databases.

Why did I decompile everything? Well I learned that unless the file size changed markedly, the deduplication feature of the file replication system that our company used assumed the files were the same. Well Access databases can stay the same size for a long time unless you run a compact and repair on them. So I lost several weeks of work more than once because the system decided that an older version and a newer version of a database were the same.

Without that stupid "feature" I would have just had the production Access file be named Access-prod.accdb and updated it each day.

I honestly have so many nightmares about that job I'm intentionally doing easy work until I can convince myself that files don't magically delete themselves without a trace and administrators aren't deliberately evil.

The foremost issue was that the timeserver was virtualized, and drifted to an unbelievable degree. This issue was compounded by the fact that we used citrix virtual machines for some applications, and our local desktops for other applications.

The latter issue wasn't a problem until they moved the citrix servers from an on-site server room to cloud provider about 40 miles away. (I think this is when the timeserver became virtualized as well) At that point the latency between the citrix sessions and the local desktops was such that I could create a document on the file share, edit it on my local desktop, close it, save it, and then open it on the remote desktop and it would lock the replication from my computer and lose all my work.

My department managed to wipe out the changes every other department had made to the yearly budget workbook because of this interaction. Of course part of the issue was using Excel as a shared document, but that issue was hugely compounded by the fact that our network infrastructure was bananas.

For instance, building access databases using the Microsoft recommended separate front-end/back-end files, with each individual user having their own front-end file actually managed to make the issue occur more frequently. If you used a single access database file on a shared drive the issue was substantially less likely to occur. I can only imagine this is because the dedupe was postponed until everyone was done using the file, and the file was more likely to substantially change in size when multiple people were using it.

The IT director had an incredibly fragile ego, and he attacked anyone who challenged him in any way. He hired a truck driver (with absolutely no IT experience) to work the IT help desk, which I assume was because he wanted a sycophant. He reported me to HR any time I did anything he didn't understand or which perceived to be a challenge of his power. He reported me to HR for using open-source software, with a description of open source software that made it sound like Wikipedia meets programming, and only used by hackers. He also reported me to HR after he heard I called our IT situation Kafkaesque.

As far as nightmares go, that might have been my favorite, because my defense was literally "it is Kafkaesque, and I can prove it." I managed to successfully convince HR that the IT situation was Kafkaesque using examples from Kafka's work, whose response was "well there isn't anything we can do about it, but you aren't wrong."

The sensible thing would have been to leave as soon as possible, but I really liked the field I was in, which was kind of specialized. Looking back I stayed way too long.

But thanks for the horror story. What a nightmare.

Stories like that, and my experience, tell me that IT staff in large companies and institutions ensure everything works by making it unusable. After all, users won't break stuff if they are not actually using the infrastructure.

When I started, I was backfilling someone who created the Excel model and was responsible for batch exporting data from SAP, crunching the numbers in Excel, and sending that out to the sites every morning. My onboarding consisted of being given his laptop and his login credentials[1], and being met with a desktop full of dozens nested folders of Excel sheets in that exact version control format. I still have nightmares of that week.

[1] My manager had successfully kept IT from being able to get the laptop for re-imaging. But my senior VP located on a different continent had to get involved to get IT to reset the guy's password for us.

   unzip *.xlsm
   git add .

Also, I wasn't allowed to install version control of any sort, so that's really the bigger problem.

I have no problems about it...less work for me, and they can do their work easily.

It was pretty hacky, but you could pull prices for any security or book in the system, or call any options pricer, as long as the user had the right permissions.

Since the ORM had bindings to stuff like convex quadratic optimizers (IMSL), you could build spreadsheets that let users input linear constraints (e.g. I want to spend less than $50mm and buy between 0-5 units of X and 2-10 units of Y) and then plop the solution vector right back in their workflow. Basically magic to the user, and much better than Excel's 1D root-finder.

Most quants are notoriously bad at building UX, so this was often a much better solution than trying to write a dedicated tool from scratch.

Some things I'd like to see:

- "Git for Excel" Diffs, tags, push changes to individual sheets or regions.

- Snapshots: Create 'Time Machine' type interface for spreadsheet regions.

- Collaborative editing.

- Access control.

- First-class support for model verification (folks roll their own at the moment).

Currently, running SQL and returning the results to a sheet is possible, but very cumbersome now: either .ODC files, VBA, or PowerPivot, none of which are flexible or developer-friendly.

I used ODBC connections to pull in data for further manipulation in Excel to run a ~$200m project for a couple of years. It saved a great deal of time compared to the manual system that was in place prior whilst presenting the data in the familiar excel system which didn't scare away the old baby boomer who was doing all the manual work beforehand. Once I set up the queries and showed him how to refresh the data from the ribbon, he was able to do it without breaking it and whilst still feeling at home enough to get the job done on his own initative.

edit: I note that the above all falls apart somewhat when you need R/W access to your DB. I've only ever done SELECTs because I want to know info from a DB, crunch numbers, then feed decisions into another system. I don't know if you can UPDATE, INSERT, etc. via Excel/MS Query as I've simply never tried it.

A notorious thing that happens in my org is an expert user will extract a bunch of data from the source DB tables and dump the data into an excel spreadsheet. The spreadsheet will pass into the hands of an analyst who will make some tweaks maybe paste in some data maybe manually adjust a few cells here and there and so on and so forth. The spreadsheet will pass into hands of someone else and they will make their own 'adjustments' and so on down the line until finally I will get a phone call along the line of.

"The numbers I have look different to the numbers this other department has in their spreadsheet which is the 'real' value".

Eventually I'll track down the source database and lo-and-behold neither spreadsheet accurately reflects the original database number.

The more hands a spreadsheet passes through the greater the chance of original data being modified into something irreconcilable.

I've played around in the past with linking spreadsheets to DB directly and populating cells via SQL but it fell afoul of requiring various DB/2, oracle, OLEDB etc drivers to be installed on every PC you wanted to open the excel file on and each of those computers required having data source correctly configured via control panel - nightmare for places like my workplace because Windows PC's are all locked down so you'd need to submit a service ticket for every PC you wanted to be able to open spreadsheet on.

This was some years ago but I don't believe anything has changed with way database connection configuration works in Windows client world.

I much prefer the way Dedicated BI tools like COGNOS / SAS etc work. In that situation the user authenticates to the BI software (running on a central server) - the server has all of the DB connections configured and the server executes the DB query on behalf of users.

Our org is currently evaluating a newish Microsoft product called "Power BI" which uses something called an enterprise gateway more info here https://docs.microsoft.com/en-us/power-bi/service-gateway-on... I don't know all the details but seems promising solution for our org's data sharing issues at least.

I really thought PowerPivot could be this excellent data tool, but the DAX formula language is just such a pain. I really dislike it. Using SQL og C# is just much more intuitive - especially SQL.

1: https://querystorm.com

But basically, I kind of just want a simple API for interacting with workbooks and providing functions, maybe using embedded JS or Python scripting. That, to me, sounds like it would make Excel a lot more palatable toward developers like it is toward end users. (Honestly, I think what I'm describing is a bit like WebExtensions but for Excel workbooks. I'd dig it.)

Spend a few million on a web based reporting system. Allows self service reports using real time data. As it runs on a browser literally anyone can use it.

Only problem - formatting and printing is for shit and users want it in excel so they can manipulate and format data how they want, on the pages they want, in the colors and font sizes they want.

I agree with Excel not being the most stable or error free platform for reporting, but is there any easy to use alternative? Whatever we try we keep coming back to Excel/spreadsheets.

I believe OnservableHQ has a good shot at it.

Why? Excel is ubiquitous, powerful, and accessible. It only really falls apart for huge data sets -- at which point you build a cluster and feed the output back into Excel anyway.

I'll never understand why software engineers feel like everybody else is a caveman banging rocks together. Excel replacements are a hard sell because, by and large, they're not needed.

I work on a desk which has a lot of vital calculations in Excel spreadsheets. They work, and we make money, but it takes a lot of human effort (expensive trader effort!) to keep them working properly. If we want to scale up - more people, more products, etc - we need to move more and more of those calculations into software.

Improvements to or replacements for the spreadsheet as tools for non-programmers would certainly be helpful. But better tools and techniques for programmers to port functionality from spreadsheets to 'real' software would also be helpful.

Straddling the line between 'technically best outcome' and 'cheapest workable outcome' is what businesses strive to do. In most mature businesses change should be approached extremely cautiously; everybody loves changing things and the costs are frequently big.

The big grid of cells is the weakest part of Excel by far!

As someone that spent years being buried in complex spreadsheets for hours a day (and supported and trained others who spent literally 12 hours a day building models), I can't think of a better combination of functionality, capability, and usability than that big grid of cells.

Then we move on to math, and we still do it with paper, almost as an extension of writing. When doing calculations in class it is natural to keep calculations and values in some type of invisible grid pattern that makes sense to the student. Then we get to the computer, and that format still follows.

Except that the first information processing a person does isn't reading at all. It's learning sounds and words, and we do that by listening and watching others do it. And the first thing we create isn't words on a page, it's likely something physical, in the form of building blocks or some other toy. This type of learning is great, but it can't be replicated well in a school system because it is expensive and doesn't scale well to the real world. If you're going to engineer and design a bridge, you're not going to start by making a bridge. So we're taught to make lines on paper that represent a bridge.

This is where (I hope) AR/VR becomes disruptive. An infinite amount of objects that can represent any piece of data on any scale in non space.

Think about how successful minecraft is. When I first played it made me realize how much creativity I had lost to pen and paper through no fault of my own. I was building logic gates without even realizing what formal logic was until I came to college.

Now make it engulf me, make it blank, and give me a wand and an in game terminal.

Since this thread probably has more Finance users than usual - most people have seen a bloomberg terminal. Its ugly, and filled with arcane short cuts.

Its extremely extremely good at getting its job done, and keyboard short cuts and commands are the fastest system once you gain expertise.

Keyboard short cuts and memorized lists of invocations will regularly be the fastest way of activating commands on a system for experts. Especially once it becomes muscle memmory.

For most abstract symbol manipulation, most jobs can get away with a stack of 2D pages.

If you are working in 3D, then you are doing a pretty unique set of modifications, and the average case will not apply to you.

Which is to say that AR VR will be only for a very niche set of uses.

Operators are so in tuned with the incoming data that they intuitively make 3D maps in their head, while requiring 2D data for quick observation, comprehension, and real-time monitoring.

Working in a 3D space makes sense for blocking out movies, exploring physical architectures, or certain kinds of research. For everything else there universe has given us an amazing parallel processor connected to eyes that work really nicely on tabular data.

If something comes out that can help students who struggle with math visualize those concepts in a more (to them) natural way I will be thrilled. To me these concepts were always very visual, and allowed me to pick up concepts in class by thinking that way. I only have experience in the U.S. education system, and in my experience it felt that my teachers always taught in a way that emphasized problem memorization and notation over actually understanding, mentally, the abstract concepts and problem solving. This seems like a barrier for the average student, and leaves a lot of people feeling left behind or "not smart" even though it is likely a fault in the way they are taught.

If something comes out that will allow those mental barriers to be broken, I will consider it a win.

America's favorite villain Martin Shkreli was pretty amazing.

https://www.youtube.com/watch?v=jFSf5YhYQbw&feature=youtu.be...

https://youtu.be/0nbkaYsR94c

ie. what Shkreli is doing there most would do with MSXML + SEC feeds or with the Bloomberg Excel Add-On

Immediately started learning it that day.

edit: I entire a whole.

This should also be the reason for getting rid of Excel, it is basically inauditable. But then again I guess all the auditing firms (e.g. Deloitte, KPMG, EY) probably have a vested interest in keeping it that way, not to mention all the persons who use this deniability for fudging numbers. I guess we'll always have ethics to fall back on /s

Your exact comment happens constantly in global banking firms. If the banking arm happens to be in a stronger management position, they push towards locked down system with tight development cycle controls. Typically the bankers have low risk tolerances and their systems (ex. savings and payment client account ledger systems, ATM ops, etc.) reflect it. That said, even in a true banking company, the closer the department is to the interest rating trading operations the more the banking environment looks like a securities operation. You will find critical on-the-fly modeling taking place, data integration, data reintegration all happening in Excel spreadsheets and these XLSX will be driving business decisions.

In securities operations the pace of change (demands originating from regulatory bodies, external clients, and internal trading operation, etc.) inevitably push the boundaries of what the bankers can support. If your securities side regulatory body says the firm must implement X in order to report Y, the firm is obligated to do it, regardless of what banking IT risk and change control framework the bank thinks should be followed. If the regulatory body says X should be in place by Z date, and banking assessment / change control procedures can not be finished by Z date, guess what X gets implemented by Z date. There is no choice. Same goes for client system demands. Woe be the IT staff who tries to tell a securities side broker their client demand can not be fulfilled by some change control / risk assessment procedure. Do such a thing enough and the desk's profitability will take a hit. Which means the trader's bonus will take a hit. Which means IT will take a hit.

If a proprietary / quant / interest rate trader can't respond quickly enough to market change X because it took 3 days for a revised real time report to be created and then vetted by a formal change management process, the IT staff pushing those processes will be replaced.

Looking at the situation another way, having these time sensitive demands in Excel instead of in trading systems is actually kind of smart. Try to integrate the rapid trading desk decision making that happens in Excel into the trading / transaction ledger / transaction settlement system will just endanger the smooth operation of the those systems. Fewer changes the better in these core systems.

These ancient systems are one more reason why Excel spreasheets exist. The Excel file backfills missing functionality in the ancient system to permit the end user to do their job as defined some 30-40 years after the legacy system first came online!

Now, maybe I'm excusing myself for being relatively un-disciplined, and in fact I am working on improving my programming discipline. For instance a colleague and I have begun to use git for a code that we share.

But when I hear people being told that they shouldn't use ad hoc programming tools, and the resulting reaction from those people, I imagine myself in a similar conversation.

Users have job demands, so they find an IT tools to do their work better / faster / smarter. The request goes to the IT department that needs to manage support costs, firm wide licensing requirements, ITIL process demands, and the user request easily goes into a back hole. User base unhappy, pressures IT, IT feels heat and worries about their job so IT fudges X in their process to implement, wash rinse repeat.

Yes, it's a problem when it gets expanded into massive, ridiculous spreadsheets-from-Hell, but most spreadsheets are a tool that let everyone program. People keep talking about some magic new visual programming REPL-like tool that will revolutionize programming, but I think we already have it: it's Excel.

I mean many obvious ways it can be improved have been discovered during the time passed.

Although it obviously makes no sense to give up the whole thing just because it has been introduced long ago it still is not a bad idea to consider minor and major improvements or even rethinking the whole idea occasionally. We could still be using good old typewriters/teletypes if the fact they worked was stopping us from thinking about inventing tools to do the same job with less labour and more fun.

That’s where a basic programming training at school or university would be priceless. Not to make everyone a programmer. But to make everyone able to better perform and automate mundane tasks.

IT departments would hate that even more than business users using Excel. But in the mind of most IT departments, business users should only be allowed to click a couple of buttons and should budget & prioritise an IT project if they need anything else.

2. Most of the data sources I've seen are heterogenous or very expensive. It comes in from big companies via secure FTP overnight, small companies via pdf. Big companies won't change their rules for you, small ones don't have the staff to do it. You can quietly and immediately export to excel from a terminal at 5pm, or be charged an extra 200K a year for the overnight deluxe data export package and still not get the same bond valuation you need to reconcile. (looking at you, Bloomberg!).

3. Every rockstar portfolio manager has their own models, and not even the VP of IT dares tell them what to use. There's no "bog-standard model" that everyone uses. That's the expensive PM's edge.

4. Speed. Requirements, test cases, test server, scheduling personnel, project planning, testing, promotion to prod, signoff, takes a month for trivial stuff if you're lucky. Trading opportunity long gone. Analysts grab a spreadsheet, do it in half a day, make or reject the trade.

5. Capability. Pivot tables. Compact, resizeable, color-coded, read/writeable grid, notations on the side, instant recalc, reorientable text. Row inserts. Printing. Ugh.

6. Risk. Analyst does the spreadsheet, IT hands are clean. "Not our fault if you screw up".

7. Resumes. What developer wants to spend 10 years coding models and reports that are regularly modified or discarded with each new analyst and PM's whim?

8. Time. FO spends 16 hours a day trading or researching under immense stress. Asking them to find, let alone spend, 2 hours a day explaining workflows to devs is... unrealistic.

9. Education. Devs know development, not financial instruments and regs. SME's who understand both software design and finance are rare and expensive therefore used on major projects. This may hurt egos here but most devs are some combination of untrained/low social sensitivity/ESL/fragile egos/tend to start explaining a solution instead of first listening to the problem.

Devs do major dev stuff where it's warranted. Analysts can keep Excel where it's not.

I think it's more like how society likes to treat devs. I'm telling you now, fund managers are bigger prima donnas than devs. But they make tons more money for the fund and so everybody steps inline.

Developer: overhead

If you do not believe me, go ask your manager.

It is not different from food service

Customer => profit

Restaurant staff => overhead

The job of overhead is to enable making of profit. Without profit, developer would be out of a job. Without developer, fund manager will not be out of a job - he would be using Excel.

The other thing is that people here are comparing moving from Excel to some sort of webapp/db or general purpose language, when actually they should be comparing to numerical computing tools.

R is a far more robust tool for numerical computing than Excel. It doesn't hide errors in formulas not copy-pasted enough, or references that have subtly shifted to an unrelated cell, or the garbage data from a sort that missed a few columns. The entry barrier is higher, but for the complex spreadsheets and models we're talking about, the investment in skills is similar.

My boss is an incredibly smart guy, but he worked in consulting for 15+ years and sometimes cannot view data from outside of an Excel perspective. It can be frustrating to have him think that every problem is a vlookup and pivot table from being solved. I think it's easy to become a bit myopic with Excel. It's like if the only tool you have is a hammer, then every problem looks like a nail.

Calc is basically a toy compared to Excel, and this is quite evident once you step out of the basic things.

Anecdotally: a while ago i wanted to live-update a cell in my spreadsheet using python . Nothing fancy, just update the cell with the latest price of bitcoins and see if it's time to sell (at the time bitcoin was at 500$).

I wasted a morning on that thing and I managed to get it working but oh boy I am so ashamed of the code i wrote... Also OpenOffice/LibreOffice APIs are arcane and basically undocumented. Examples are basically non-existing.

https://www.npr.org/sections/money/2017/05/17/528807590/epis...

There's a World Championships for Financial Modeling in Excel, it's mentioned in that episode.

https://www.modeloff.com/

Even with all the various viruses floating around, opening a spreadsheet is a common and allowed task as opposed to downloading, installing, and running software.

Edit: I suppose this point is mostly about the ubiquity of Excel as a platform. The interesting thing to me was that even otherwise prorietary models are more accessible distributed as Excel sheets than better-secured inside a custom tool.

If you are talking about WScript on Windows (I doubt it), it has an even worse reputation than PowerShell.

Are you talking about Same-Origin-Policy? This shouldn't prevent a script being executed local. It only limits what it can do. Because of security, there is heavy sandboxing with javascript.

Anyway, it wouldn't be hard to transform browsers in full fledged local scripting-engines, without dismanteling security.

The hard thing for me in Excel is visualizing the data flow. Long ago I wrote a VBA macro that would return the formula contents of a cell, and I'd just put that next to each formula cell. That way, I could see what all of the formulas were doing. My sheets were hideous, but if I was working through something complicated, it helped me troubleshoot mistakes.

I now prefer Jupyter/Python, but still can't claim that it's quicker for the smallest jobs.

Word and PowerPoint can be easily replaced.

Using it to handle all the pricing structure for a few thousand clients on the other hand is another issue by having underwriters plug in values that they got from an online application, though, not so much.

See also: https://www.theatlantic.com/technology/archive/2017/09/savin...

> On the surface, Eve is an environment a little like Excel that allows you to ‘program’ simply by moving columns and rows around in tables. Under the covers it’s a powerful database, a temporal logic language, and a flexible IDE [Integrated Development Environment] that allows you to build anything from a simple website to complex algorithms.

Now, I guess they're doing something completely different.

- Spreadsheets are programs: https://medium.com/@hjalli/3-things-you-dont-understand-abou...

- Spreadsheets made the PC market: https://medium.com/@hjalli/3-things-you-dont-understand-abou...

- Spreadsheets are everywhere: https://medium.com/@hjalli/3-things-you-dont-understand-abou...

The big problem is when people promote it to collaboration and workflow management software.

Excel certainly has limitations, but it has many features and is widely used.

"All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us?"

Also, there's a TON of .xlsx files in the wild with years of business knowledge and processing. Everyone uses about the same 50% or so of Excel, which is captured in Google Sheets and any other spreadsheet. It's the extra 20% that everyone else uses -- and it's a different 20% for everyone -- that causes the issue of entrenchment.

In addition to the things you mentioned, Excel is an offline, native app, with much more speed and efficiency when dealing with complex or large spreadsheets, IMO.

Those things. I literally run and pay for Parallels on my Mac for the Windows version of Excel. (Mostly for the keyboard shortcuts.)

But Excel is used because it's very very good. Many of the people use it need this one small weird thing, and Excel has it and none of the others do yet. And that one small weird thing is different for everyone.

It's a bit scary to see how much stuff is kept going with scary hairballs of VBA and macros and god-knows-what.

The EUSPRIG has more information. For example, this about spreadsheets in clinical medicine: http://www.eusprig.org/2006/spreadsheets-in-clinical-medicin...

I use both on a daily basis. Our org is on the Google Apps for Business stack which is why I use Google Sheets, but I'm considering getting us on Office 365 for the collaboration capabilities of Google Sheets paired with the functionality and advanced features of Excel.

Simply put, Excel can do way more, is faster, and frankly, takes less time to do things in because I'm deeply familiar with it from years of repetition and troubleshooting. There's also WAAAY more documentation and tutorials available for it when you do encounter a problem.

There are semiconductor fabs running most of their machinery on Windows XP for the foreseeable future.

Or is your aversion mostly to the M- word?

I find it funny that when it comes to vi vs emacs (or any such similarly geek-approved thing), fans of each will jump up in arms about the precise reason their platform is better, but the same kind of people would not want to take hundreds of thousands of business professionals at their word about why they like using a particular piece of software.

People used to use Excel to run regression, visualize data, etc. which I consider inferior to R / Python.

I can't speak about other uses of Excel, e.g. financial modeling, insurance.

Excel is faster to get started. But for advanced tasks, the time it takes to learn Excel / R is about the same.

A big reason why people don't make the leap is because they already spent too much time becoming an Excel expert. Indeed, for young analysts with no baggage, a lot of people learn and use R instead.

So they can apply their business and process knowledge to create tables and formulas, pull data from various sources, and create something that is self-contained, stored locally (inside the lan, security reasons), is blazing fast, and helps them make money and generate profits for the company.

I've seen very sophisticated OMSs (Order Management Systems) built completely using excel workbooks, with 1 sheet containing orders and formula's to pull realtime pricing from bloomberg, reuters, another sheet for pre-trade analysis, another for the actual trades, and a summary sheet with pivot tables and charts to show the trade progress and summary.

As and when the last_price changes, the formulas update, so the trader is looking at a real-time view of his situation.

The same excel workbook that let's the traders do their job would take months / years to convert to a web app, and even then, the speed may not match up.

I know devs hate Excel, in part, because you are the "IT guy" they call (been there many times) when the spreadsheet gets out of hand, or slows down, or keeps crashing excel after a certain inflection point.

EDIT: I like a lot of the responses here. They clearly illustrate the disconnect between end users and IT/devs in many companies.

What matters in terms of better value is whether or not one can understand the problems space and find an appropriate solution for it. All sorts of solution space solutions can be used, some of which are better than others. The point is to try and keep it simple enough for the user, irrespective of how difficult it might be for the IT guy.

The knowledge of Hadoop in and of itself is not as valuable as one would suggest it is.

[Edit fix wording]

Hiring a hadoop guy for what used to work in excel is a bad decision in most ceases...

as opposed to fecking sharepoint

Yes but https://aadrake.com/command-line-tools-can-be-235x-faster-th...

"Since the problem is basically just to look at the result lines of each file and aggregate the different results" which is why this works on this data for this problem. Many problems are not efficient to solve in this way, for example searches through large products and expansions, especially where the evaluation function is costly.

Some problems are data bound - you are handling 100'sTb of data, hadoop can be a good fit.

Some problems are CPU bound, and embarrassingly parallel - you require massive parallelism to get through the core seconds that you need to solve the problem. If you need 1 million core seconds and you have 1000 cores, and a way of getting the 1000 cores to do 1/1000th of the work... all is good.

Some problems are memory bound. You can build single space machines with many TB memory now, also you can cache with SSD's and optanes more than in the past, but Hadoop does offer a utility for these problems.

But in a corporate all problems are admin and discovery bound. If your dataset is what I need and I don't know about it, can't find it and anyway if I did then you won't give it to me and I can't unlock it legitimately; I am screwed. If it's a proper corporate asset then I've got a fighting chance.

End users groan because people groan every time anything changes and they have to learn something new.

https://support.office.com/en-us/article/Connect-to-a-Hadoop...

I'd go so far and say: We programmers often make life more difficult for end users.

This has been a source of work for me over the years, providing a means to bypass the IT department to get end user work done.

However, the general view of IT departments is that they just dictate what they want and not help with what is needed. policy is set and then demanded to be followed with little or no justification to the end user why and how it will benefit the end user.

The number of times that I as IT professional (designer, developer, etc) have had to fight the IT department over some problem to get it solved is far too many. Instead of fixing or changing something that will allow end users to actually get on with their word, IT departments are seen as dictatorial time wasters who don't get back to you to sort out what is needed.

Part of our job is to think of failure scenarios and disaster recovery. This should not get in the road of those using the systems being put in place. The end user has a job to do, we as professionals should be providing good solution to enhance as well as protecting them from any disasters.

It was truly a disaster waiting to happen.

Engineering has overhead otherwise it's just a bunch of cowboys that are no better than Excel hackers - and probably worse, because they have a lot more rope to hang themselves.

Does your balance sheet balance? That's a pretty basic "unit test".

Do your calculated values match the financial statements values? No? Throw an error.

Divide by zero? Reference a blank cell (similar to an unset variable)? Using a value as a number? Referencing outside of a range? All immediately throw an error that will flow through your model.

And code review often does take place - in banking at least, models written by analysts are checked by associates and then VPs. People who understand what the numbers should look like can spot mistakes pretty quickly using a TI calculator.

Source control is a problem, but many models are one-offs for specific projects - no company (or deal) is structured identically.

You're identifying problems that have, for the most part, already been solved. Not perfectly, but in ways that work well within the structures that have been built around the use of Excel as a core tool.

As programmers, we write tests because we can't see what's happening inside the program while it's at work, so we have to give it a thorough exam before letting it do that. Spreadsheet jockeys can take a radically different approach to validation.

I can't guarantee the a programs output will be the same after every VM patch, component update, re-deployment, or even just re-compile. Generally we have multiple devs touching the same system, or same code. Generally we want that code to be robust in the face of reuse in other components and contexts. Generally we have no ability to see production errors unless they are properly logged and monitored, etc.

Worst of all I, a subject expert in nerdy things, am implementing a model in a domain I am an amateur in and have no fundamental context for assessing.

A spreadsheet jockey inverts all those problems. They know what they want, and are intimately connected to its operation.

More often than expected, there are mistakes and misunderstandings in some of the spreadsheets. There are inconsistencies in the spreadsheets. In IT we also get calls that a spreadsheet is "locked by someone." Or that someone deleted a spreadsheet.

With code, and databases, we can ensure consistency between operators. We can also leverage the data across other applications since it lives in a database now. We can pull reports for management more easily, and with more automation.

As some other comments mentioned, spreadsheets are great for prototyping, but also have their drawbacks.

https://pivottable.js.org/ and https://react-pivottable.js.org/

Sorry but I have to call nonsense on that part. I've seen recently a whole trading operation converted to a system very close to a web app within 6 months. The hard part of the job was untangling the mess they created. And knowledgeable quants who actually program told us it's typical.

The financial pros have horrible, horrible stuff in their spreadsheets. They are used to it and they want (perhaps unconsciously) to keep their dirty hacks out of sight. Lest they fall from heaven.

Even as a software developer I will freely admit that Excel is the right tool for many jobs. We shouldn't be so eager to transform workbooks into formal apps unless there is a really good reason to.

In that case Excel's instant update and flexible (but quite powerful) formula scripting is often the best tool for the job because it means you remove the programmer middle man. I know some shops prefer to just teach the analysts how to write code.

Often this is down to people adding/deleting/copying or modifying rows/columns and forgetting to change cell formula.

So, in theory you can modify a workbook and get instant results. In practice this seems to be nearly impossible to to get right.

In the end I'm personally still in favor of Excel because it has a much gentler learning curve, though you are right that bulk operations in Excel are ripe territory for errors. I don't think it's "nearly impossible" though, it comes down to following best practices the same way you would in any programming language.

I really doubt any major finance player allows someone to whip up a spreadsheet, plug some numbers in and start trading off that information. Excel is valuable in the early stages because it's fast, powerful and easy to collaborate in. Trying to eliminate it just because it's Excel is dogma.

Clearly you only stayed in finance for a brief time :-)

Your fin model has normal sanity checks and then functional checks.

And in the end the out put must be readable AND make sense.

And whatever bug you have is going to propagate down your projected time series.

Finance chaps use finance as their weapon of debugging. Sure mistakes get through. I’ve seen mistakes get through code as well though.

Also - analysts get fired a lot faster.

IT's and developers' job is support revenue generation, not block it because IT or developers think they have a better way. In most cases IT does not and neither do developers.

"Everything takes forever because you have to write requirements" is a feature of software development. At the end you get software that does something that you (presumably) understand. Excel is a gold mine of undocumented terms, processes and functionality.

It's fast to build but don't pretend it gives any of the benefits of proper software development processes. The value proposition is in fact that it has none of those guarantees.

There's usually more people who know Excel than people who can create systems. Meaning, the inefficiency is not that important. Some things just cannot wait or depend on a much smaller amount of people.