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...
At a former job I had to build a reporting system. And had the trust to build it however I wanted. But it was my job to make it sufficiently useful that everyone would use it.
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."
The best non-core feature I ever added to my SaaS service remains live import/export to Google Sheets.
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.
This sounds an incredibly useful capability to have. May I ask which data connector did you used to pull data, and how did you made it available in that format from the app side?
Yep, the most useful program I ever wrote for business was a Perl script that ran SQL against a Sybase database and sent the output as a spreadsheet. Multiple return sets went on multiple sheets. Took the column names from the return sets and did decent formating of data. Then the user would do all the fun stuff.
That’s a very good approach. I found the best way to do that it to be able to query the report with Excel formula (which is probably what you did). It gives an incredible flexibility to the business to build tables and charts for whatever presentations they need to do.
As unpopular as it would be to say - Stop trying. Excel is a phenomenal accomplishment, and while everyone may talk about a better spread sheet, the market really doesn't want it or need it. Its basically craigslist.
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.
The problem with Excel is that whilst it's an easy development environment, and a vastly flexible one, it's absolute death to debug.
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.
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.
I agree and I have stopped trying as well. I'm in bioinformatics and I do the big data, Linux cluster, data pipeline stuff. Then in the end I dump it all in Excel and let the very smart and knowledgeable (lab) biologists play with it. It's important that they play and have flexibility to massage data as they see fit, they understand the data deeply. I started out constantly trying to figure out what they want and build it into the pipeline... It doesn't work because often they don't know what they want exactly when starting.
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.
Yep and that's the same reason Matlab et.al. stay in their dominant positions. Sure, these tools can be a pain to work with and a lot of alternatives do a lot of things better, but most heavy users are happy doing the things they need to do.
>"I need to be able to do basic maths on the side"
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.
I think pragmatic businesses that employ finance pros / quants are wise to include Excel as an essential part of their product development workflow. Excel is a great prototyping tool, and people are going to use it whether you want them to or not. These folks are motivated by money and don't give a shit about your tool if (they think) it costs them money (or time). Besides that, how many enterprise applications really offer opportunity for prototyping and running small experiments? Excel is to modeling as wireframing is to UI design.
Someone should write a book about productionizing excel models. It's not going away (and shouldn't).
Ehh I doubt most quants are interacting much with Excel except when they need to work with non-quants. There's not much of a reason to use Excel when you could use something like Pandas instead, plus the Python libraries/environment will make everything non-tabled better to deal with anyway.
Just for fun I have a couple of nice Excel based stories from my time in Investment Banking.
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?
Story 1 sounds totally nuts, but Story 2 is a common setup in my experience. Using Bloomberg prices on the desktop is a lot cheaper than BPipe - the server side solution for BBG market data. And quoting indicative prices on BBG from Excel is common too. An interesting new startup that I follow - pricingmonkey.com - is doing BBG driven IRD pricing in the browser with an IR pricing library written totally in JS. There are more details on my blog at etrading.wordpress.com
For many years I was the contractor that came in to work with the Subject Matter Experts (SME) using Excel and Access, bypassing the IT department. My role was to review with each user their Excel and Access usage and make sure that we could make life simpler for them. If this meant automating some process they did with Excel then that's what I did.
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.
> The issue is not how to get rid of Excel, it's how do we make a better spreadsheet...
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.
Better addins only means you get to do more stuff with Excel.
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.
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 :)
Exactly this. I dunno if you have seen old movies where there is a guy at a desk and in front of him is a pad - built into the desk - with large stacked sheets of paper tucked in at the corners to hold them down, A3 size or larger. This was used to just do any quick bit of writing, make a note, do a calculation, sketch something out, whatever. When a page had been sufficiently dooodled on it was just peeled off the top and there was a fresh one underneath ready to go. THAT is the experience Excel replicates. A dedicated, single-function piece of software to do a particular task COMPLETELY misses the point.
Where I work, we get to the part IT guy part, and then the quote comes out to be like $200k+ (and 6 months) to duplicate a SAS program, Excel spreadsheet, or Access database that an analyst hacked together in their spare time.
Or it could be like the place I worked where IT made as many hurdles as possible to the installation of new software. So I, as a database marketing associate, linked 4 different CRMs used by different departments together using the magic of Access, Excel and VBA.
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.
I work in a large manufacturing company that gets lots of data about the manufactured product...The business people received a single excel reports from a database. They wanted a fancy UI to export the data (more queries, pull from other databases, etc) into Excel because they do all their analysis in there.
I have no problems about it...less work for me, and they can do their work easily.
GS a decade ago had Excel connectors to its internal quant IDE/ORM/trading system.
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.
Excel should have a built-in, top-notch SQL editor/IDE. Existing SQL IDEs return resultsets in a grid, thus, Excel is a natural fit. Excel would allow developers to go beyond a SQL IDE by subjecting the results to the rest of the spreadsheet's functionality. Would work great for exploratory analysis or for production reporting.
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 mean personally, I'm OK with just continuing to use Excel. If anything it'd be nice if they made newer APIs. I actually like COM for some reason, but it does feel dated at this point. I bet there are some other APIs, but I'm not in the biz of writing Excel plugins, so I'm not up to date on it. I did it once for fun and never spoke of it again.
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 think part of the problem is the mindset of devs that try to tackle this. Excel is the dynamically typed python to the statically typed and compiled C++ of most web apps. The power of excel is that people can build their own little apps in it as needed. Any replacement to Excel will require those features first, and then you can build specialized integrations.
I also wonder if there is a middle way here... in encouraging greater use of interactive notebook computing such as that espoused by Mathematica, Jupyter Notebook, IPython, etc.
Man, I'd love to be able to edit tabular data within a Jupyter notebook. Every few months I look to see if this problem has been solved in a tolerable way.
I strongly believe these are almost there. The interface is what sucks with notebooks. Code should be hidden, cells should be smaller so you can pack a lot more info in them. 2D organization of data is a lot more effective that a long 1D list of things.
Excel is old-school Business Intelligence. We don't need to make a better spreadsheet, we need to make a better toolkit for data-driven decisions. There is very little reason that all users need to engage with their data in a grid of cells.
When BI tools are as usable and flexible as Excel, that is when Excel will dwindle.
> we need to make a better toolkit for data-driven decisions
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've seen that repeating pattern many times too, and I've been the IT guy as well. IMHO the solution is to serverize and automate the Excel models. See my profile for links to my product that does just that, even for sheets with VBA and addins.
this is a very accurate observation of the state of excel. I have always thought that the solution is the have an optimized portion of spreadsheets, specific to the use case, in your example a model that is deployed as a web app. Making a better spreadsheet is a very good point, I wonder, if there are attempts already to solve it from this angle?
In business individual productivity is not necessarily the most important thing. Companies are about collaboration and communication and if the tooling doesn’t support that then it’s at odds with the company’s needs. Maybe those two finance guys are less productive but now it’s auditable, regulatory compliant, redundant/backed up, and resilient to either of those two people leaving. That seems like a win for the company even if it’s not a win for employee productivity.
Watch some youtube vids of Excel pros moving around the UI quickly. There's no other UI I can think of where so much is being done so quickly except maybe a bash pro.
America's favorite villain Martin Shkreli was pretty amazing.
There's honestly nothing impressive about copy+paste data and basic cell manipulation and formatting - you could do that in Google Docs. Where Excel is really entrenched is in VBA scripts and macros that are written and shared
ie. what Shkreli is doing there most would do with MSXML + SEC feeds or with the Bloomberg Excel Add-On
I'm ok with vi, and ol' Martin 'wurmtongue' Shkreli is pretty good, but what is more amazing is that he has people watching him stream. When you try telling your uncle at thanksgiving (who 'dabbles' in finance) that someone like Grimma there would stream his analysts, let alone that people would watch him, they look at you like you have 10 green heads. Shkreli there is a perfect example of a generation gap.
He has a cult of personality like all scumbags who make money. Everyone will kiss their ass to find out their "secret sauce" when it's usually just exploiting people and general psychopathy. He's a big favorite of the 4chan-types because he makes money while pissing people off and is the embodiment of capitalism.
The issue in finance is the speed of change in demands. No formalized system platform with proper change control can keep up. The ability to play with data, the ability to break your work is valuable in end user computing and the type of thing that is hard to recreate in a formal financial system that must pass internal and external audits.
> hard to recreate in a formal financial system that must pass internal and external audits.
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
I've worked in global banking environments (where one holding company contains a traditional banking arm and a securities firm) with a slight lean towards securities operations, although I've serviced both the banking and securities operations.
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.
Everyone talks about trying to create a new type of programming environment, something intuitive and visual and that lets you think in new and creative ways. Excel is exactly that. It's easy and nonintimidating to nonprogrammers. It's visual, intuitive, and with a basic understanding of math and an idea of cells you can do a large number of tasks that would otherwise require a program.
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.
"Everyone talks about trying to create a new type of programming environment, something intuitive and visual and that lets you think in new and creative ways. Excel is exactly that" - yes, it is, except it is a way too old already though all the notable competitors seem dedicating all the efforts to reproducing its legacy as closely as possible. I'd love to see an attempt to design a brand-new spreadsheet from scratch, built around a modern programming language (like Python instead of fossil VBA), modern practical file formats (like HDF5 and SQLite instead of obscure and clumsy xls, xlsx and ods) and all the experience and advances in the UX science accumulated during the recent years. Good news are some projects are heading this way already, e.g. pyspread and jamovi (they can hardly be taketn too serious at this moment but at least they illustrate there are people that see the problems of "classic" spreadhseets and have viable ideas on solving them).
How do you mean it's "way too old already"? If it works, it works. You're guaranteed to introduce problems every time you introduce change so there needs to be a strong reason to do. Why change what works?
To be honest there is a case for teaching business users to program. But the blank page of the IDE is intimidating for people who do not know where to start, particularly if they have their boss breathing down their neck to get it done today.
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.
Former finance developer:
1. Without Excel, every finance company will need another 100 developers, BA's, managers and QA to do programming and updates. $$$$.
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.
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.
"untrained/low social sensitivity/ESL/fragile egos/tend" I don't think that's dev's problem.
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.
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.
I think this whole thread is too strongly biased to assuming that millions of analysts know what the ideal skills and tools for their job are, rather than they started with Excel and they continued with 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.
And RStudio is an incredible IDE that can help ease that transition.
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.
Yes, and after all, it's not as if learning R will prevent you trying out some models in Excel. In a life-time career, a 40-60 hour commitment for a new string to your bow that is as powerful as R is not a big investment.
Since were all finally praising Excel for the marvel it is, I think it's also time for the OSS community to realize and acknowledge that OpenOffice/LibreOffice is quite crappy and should really see some serious improvement.
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.
I actually abandoned all Libre/Openoffice and I moved on Google Sheets. The number of add-ons is growing and I enjoy working in the collaborative mode with my partner.
Readit News
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.
Deleted Comment
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.
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.phphttp://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...
[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.
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.
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.
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.
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.
The big grid of cells is the weakest part of Excel by far!
Deleted Comment
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.
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.
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.