17 ms·

Finance dev guy here. Excel's dominance in the field is because it is an _application container_ that _non_ dev people can use. The workflow is this: - old t

by d--b 9y ago

Finance dev guy here. 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...

djtriptych 9y ago
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).
- d--b 9y ago
  > Someone should write a book 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...
  - deleted 9y ago
    [deleted]
  - toomuchtodo 9y ago
    Sounds like Excel needs a git extension!
    - yosyp 9y ago
      A quick google search shows some people are already working on that, including https://www.pathio.com https://www.pathio.com
      - uridium 9y ago
        Schematiq - https://www.schematiq.com https://www.schematiq.com also handles source control for spreadsheet and a lot more. The platform is about making sheets enterprise grade applications - regression, testing, CI etc and introduces new features to simplify spreadsheet logic.
        ccozan 9y ago
        I remember I wrote not long ago a VB Macro for Excel, where all the VB code inside was imported/exported, git pull/commit+push, etc, all of a button push. It became an instant hit in that company and I short of a medal :).
      - fzumstein 9y ago
        Note that Pathio has been relaunched as https://www.xltrail.com https://www.xltrail.com - it supports the standard Git workflow and every Git system (GitHub, Bitbucket, Gitlab etc.).
- jimmcslim 9y ago
  Or seek greater adoption of standards around spreadsheets, such as FAST [1].. Flexible, Appropriate, Structured, Transparent. [1] http://www.fast-standard.org/ http://www.fast-standard.org/
- opportune 9y ago
  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.
  - deleted 9y ago
    [deleted]
  - djtriptych 9y ago
    it's different now but historically quants were not well versed in software engineering. If anything it would have been R 10-15 years ago.
    - kgwgk 9y ago
      R was almost irrelevant in finance 10 years ago, Matlab was much more widely used. R may have more momentum now, but I’m not sure it has overtaken Matlab yet. And C++ was, and probably remains, king.
      - vostok 9y ago
        I think a lot of the discrepancy is explained by the fact that quant is a very widely applied title. There are quants who spend most of their day in Excel and quants who spend most of their day in C++.
    - hardlianotion 9y ago
      10-15 years ago it was excel excel excel for quants in most investment banks.
sct202 9y ago
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.
- Declanomous 9y ago
  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.
  - deleted 9y ago
    [deleted]
  - agar 9y ago
    Version control in Excel VBA is easy. 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...
    - Declanomous 9y ago
      That's true. Good luck doing anything with that though. 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.
codingdave 9y ago
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.
- deleted 9y ago
  [deleted]
- ng12 9y ago
  > 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.
  - twic 9y ago
    Excel sheets aren't very maintainable. You get a big grid, plus named ranges. There aren't good tools for generalisation or abstraction, so big sheets have all the usual problems of bad software: hard to understand, hard to change, full of inconsistencies, etc. There are also no good options for source control, modular reuse, etc. 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.
    - roenxi 9y ago
      Excel spreadsheets aren't maintainable, but in context there are some subtleties. If the process is upgraded to a full-blown piece of 'real' software, now you need a full-blown software dev to maintain it. A better result from more resources isn't a clean improvement, it is just a change. 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.
jimmcslim 9y ago
> 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. [1] https://excel-dna.net/ https://excel-dna.net/ [2] https://dev.office.com/docs/add-ins/excel/custom-functions-overview https://dev.office.com/docs/add-ins/excel/custom-functions-o...
- d--b 9y ago
  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.
  - cm2187 9y ago
    I am a typical excel power user, and have started to increasingly do work outside of Excel (C# mostly). I find however that Excel is still a formidable GUI, particularly for tabular inputs. 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.
    - rjbwork 9y ago
jimmcslim 9y ago
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.
- d--b 9y ago
  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. I believe OnservableHQ has a good shot at it.
- analog31 9y ago
  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.
shostack 9y ago
>"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.
btilly 9y ago
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."
- sdrinf 9y ago
  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?
  - emidln 9y ago
    Excel has a feature for pulling data stored in HTML tables into a sheet called "Web Queries"[0]. It also has a feature for automatically building these in the form of .iqy files. When I worked at Abbott Labs, it was a big deal to be able to offer export-to-Excel in a way that Excel could refresh automatically (or manually). This made it a breeze since we could register an .iqy serializer for a dataset and just give a download link to it in place of a CSV, JSON, or XML file. [0] https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba https://support.office.com/en-us/article/Get-external-data-f...
    - btilly 9y ago
      That was exactly how I did it. The only major downside was that I had to be sure to keep the html consistent, and couldn't require login. Since it was a system for internal use, that was deemed acceptable.
  - 9y ago
rukuu001 9y ago
"How do we make a better spreadsheet..." 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).
- listenallyall 9y ago
  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.
  - starik36 9y ago
    There are a number of add-ins that do just that. I've never used any of them, so I can't speak to the quality.
  - lzhou 9y ago
    Assuming your result set isn’t massive, it’s not very hard to have a 30-40 line array function (that takes a query) and returns a sql resultset.
  - rukuu001 9y ago
    Heh, I didn't mention that because of this: https://news.ycombinator.com/item?id=13114421 https://news.ycombinator.com/item?id=13114421
  - bigger_cheese 9y ago
    One of my biggest concerns about Excel is it integrates poorly with databases. 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-onprem 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.
odonnellryan 9y ago
Export to excel is a great solution.
oldandtired 9y ago
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.
zgao 9y ago
Exactly this. 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 :)
- apthnz 9y ago
  I'm a Product Manager who uses Excel extensively at work and is also learning Python in the evenings. This is the sexiest thing I've seen since I kissed my girlfriend goodbye this morning.
smallnamespace 9y ago
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.
intended 9y ago
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.
- teekert 9y ago
  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.
- dredmorbius 9y ago
  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. http://panko.shidler.hawaii.edu/ 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.
Kluny 9y ago
I think the spreadsheet is fine, it just needs a source control and debugging framework on the site.
SolarNet 9y ago
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.
gaius 9y ago
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.
- ghostly_s 9y ago
  Derail but, no, I've never seen such a thing as you describe. Any movies in particular that feature this?
  - sls 9y ago
    If you search for "desk pad" on Amazon, Google images, etc. you'll see them. A large rectangle of paper, typically being held in a base at the corners, top and corners, etc., sometimes printed with a grid, calendar, etc. My father's desk featured one of these, used exactly as described.
jchw 9y ago
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.)
arkh 9y ago
The column / line filter menu is hard to beat and one of the little things making Excel impossible to replace.
drumhead 9y ago
So True! 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.
MrMattWright 9y ago
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?
- osullivj 9y ago
  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
1_2__4 9y ago
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.
osullivj 9y ago
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.
hyuuu 9y ago
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?

by d--b 9y ago

djtriptych 9y ago

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

d--b 9y ago
> Someone should write a book 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...
- deleted 9y ago
  [deleted]
- toomuchtodo 9y ago
  Sounds like Excel needs a git extension!
  - yosyp 9y ago
    A quick google search shows some people are already working on that, including https://www.pathio.com https://www.pathio.com
    - uridium 9y ago
      Schematiq - https://www.schematiq.com https://www.schematiq.com also handles source control for spreadsheet and a lot more. The platform is about making sheets enterprise grade applications - regression, testing, CI etc and introduces new features to simplify spreadsheet logic.
      - ccozan 9y ago
        I remember I wrote not long ago a VB Macro for Excel, where all the VB code inside was imported/exported, git pull/commit+push, etc, all of a button push. It became an instant hit in that company and I short of a medal :).
    - fzumstein 9y ago
      Note that Pathio has been relaunched as https://www.xltrail.com https://www.xltrail.com - it supports the standard Git workflow and every Git system (GitHub, Bitbucket, Gitlab etc.).
jimmcslim 9y ago
Or seek greater adoption of standards around spreadsheets, such as FAST [1].. Flexible, Appropriate, Structured, Transparent. [1] http://www.fast-standard.org/ http://www.fast-standard.org/
opportune 9y ago
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.
- deleted 9y ago
  [deleted]
- djtriptych 9y ago
  it's different now but historically quants were not well versed in software engineering. If anything it would have been R 10-15 years ago.
  - kgwgk 9y ago
    R was almost irrelevant in finance 10 years ago, Matlab was much more widely used. R may have more momentum now, but I’m not sure it has overtaken Matlab yet. And C++ was, and probably remains, king.
    - vostok 9y ago
      I think a lot of the discrepancy is explained by the fact that quant is a very widely applied title. There are quants who spend most of their day in Excel and quants who spend most of their day in C++.
  - hardlianotion 9y ago
    10-15 years ago it was excel excel excel for quants in most investment banks.

sct202 9y ago

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.

Declanomous 9y ago
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.
- deleted 9y ago
  [deleted]
- agar 9y ago
  Version control in Excel VBA is easy. 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...
  - Declanomous 9y ago
    That's true. Good luck doing anything with that though. 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.

codingdave 9y ago

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.

deleted 9y ago
[deleted]
ng12 9y ago
> 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.
- twic 9y ago
  Excel sheets aren't very maintainable. You get a big grid, plus named ranges. There aren't good tools for generalisation or abstraction, so big sheets have all the usual problems of bad software: hard to understand, hard to change, full of inconsistencies, etc. There are also no good options for source control, modular reuse, etc. 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.
  - roenxi 9y ago
    Excel spreadsheets aren't maintainable, but in context there are some subtleties. If the process is upgraded to a full-blown piece of 'real' software, now you need a full-blown software dev to maintain it. A better result from more resources isn't a clean improvement, it is just a change. 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.

jimmcslim 9y ago

> 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. [1] https://excel-dna.net/ https://excel-dna.net/ [2] https://dev.office.com/docs/add-ins/excel/custom-functions-overview https://dev.office.com/docs/add-ins/excel/custom-functions-o...

d--b 9y ago
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.
- cm2187 9y ago
  I am a typical excel power user, and have started to increasingly do work outside of Excel (C# mostly). I find however that Excel is still a formidable GUI, particularly for tabular inputs. 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.
  - rjbwork 9y ago

jimmcslim 9y ago

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.

d--b 9y ago
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. I believe OnservableHQ has a good shot at it.
analog31 9y ago
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.

shostack 9y ago

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

btilly 9y ago

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

sdrinf 9y ago
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?
- emidln 9y ago
  Excel has a feature for pulling data stored in HTML tables into a sheet called "Web Queries"[0]. It also has a feature for automatically building these in the form of .iqy files. When I worked at Abbott Labs, it was a big deal to be able to offer export-to-Excel in a way that Excel could refresh automatically (or manually). This made it a breeze since we could register an .iqy serializer for a dataset and just give a download link to it in place of a CSV, JSON, or XML file. [0] https://support.office.com/en-us/article/Get-external-data-from-a-Web-page-708f2249-9569-4ff9-a8a4-7ee5f1b1cfba https://support.office.com/en-us/article/Get-external-data-f...
  - btilly 9y ago
    That was exactly how I did it. The only major downside was that I had to be sure to keep the html consistent, and couldn't require login. Since it was a system for internal use, that was deemed acceptable.
- 9y ago

rukuu001 9y ago

"How do we make a better spreadsheet..." 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).

listenallyall 9y ago
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.
- starik36 9y ago
  There are a number of add-ins that do just that. I've never used any of them, so I can't speak to the quality.
- lzhou 9y ago
  Assuming your result set isn’t massive, it’s not very hard to have a 30-40 line array function (that takes a query) and returns a sql resultset.
- rukuu001 9y ago
  Heh, I didn't mention that because of this: https://news.ycombinator.com/item?id=13114421 https://news.ycombinator.com/item?id=13114421
- bigger_cheese 9y ago
  One of my biggest concerns about Excel is it integrates poorly with databases. 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-onprem 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.

odonnellryan 9y ago

Export to excel is a great solution.

oldandtired 9y ago

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.

zgao 9y ago

Exactly this. 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 :)

apthnz 9y ago
I'm a Product Manager who uses Excel extensively at work and is also learning Python in the evenings. This is the sexiest thing I've seen since I kissed my girlfriend goodbye this morning.

smallnamespace 9y ago

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.

intended 9y ago

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.

teekert 9y ago
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.
dredmorbius 9y ago
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. http://panko.shidler.hawaii.edu/ 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.

Kluny 9y ago

I think the spreadsheet is fine, it just needs a source control and debugging framework on the site.

SolarNet 9y ago

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.

gaius 9y ago

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.

ghostly_s 9y ago
Derail but, no, I've never seen such a thing as you describe. Any movies in particular that feature this?
- sls 9y ago
  If you search for "desk pad" on Amazon, Google images, etc. you'll see them. A large rectangle of paper, typically being held in a base at the corners, top and corners, etc., sometimes printed with a grid, calendar, etc. My father's desk featured one of these, used exactly as described.

jchw 9y ago

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

arkh 9y ago

The column / line filter menu is hard to beat and one of the little things making Excel impossible to replace.

drumhead 9y ago

So True! 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.

MrMattWright 9y ago

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?

osullivj 9y ago
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

1_2__4 9y ago

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.

osullivj 9y ago

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.

hyuuu 9y ago

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?