Hacker News Reader

Comment by justboxing | Hacker News Reader

One huge reason why Finance pros in general, and Fund Managers, Brokers, Traders in particular, love Microsoft Excel is because all your formulas and calculations update almost instantly (unless you explicitly disable this feature).

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

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

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

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

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

View on HN

I can see this in my company. IT has for some reason decided to put some data into Hadoop. Now the users need to write spec documents for each little thing and every trivial report takes months to get done. Before they had Excel and could create whatever they needed in minutes. Us programmers don't always make life easier for end users. Often we create a level of bureaucracy.

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

If you're putting Excel-sized data in Hadoop, something has gone seriously wrong in your IT department's decision-making process. It does make for a nice parable about the dangers of shiny-new-thing-syndrome though.

Yep. this is going on everywhere. How many solid but boring Java applications have been replaced by a microservice, cloud, NoSQL, NoieJS monster?

http://www.zdnet.com/article/disgruntled-developer-breaks-th...

They're practicing RDD -> "Résumé Driven Development"

Agreed. The problem is that RDD works extremely well if you want to get ahead.

you laugh, but a guy who knows Hadoop is worth more than a guy who doesn't all else being equal

One must disagree with this. It doesn't matter what the technology is, if one has the appropriate mindset and enough years under the belt, it will be picked up.

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

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

[Edit fix wording]

Unfortunately that's not how the job market works in most cases.

That's only true if the management level can't see that they are screwing themselves over, either willingly or ignorantly.

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

Well, if you are taking many hours to process said data on a single processor machine (say eight cores) then throwing it into an environment where you can crunch it with many cores (say 1440) then this does make some kind of sense. Also putting the data into a secured multi user environment with redundancy and also business continuity.

as opposed to fecking sharepoint

Sharepoint is where good information goes to die. It's a write only wiki - you put stuff in and you can never find it again.

Mervellous. Maybe this is true for a lot of DMSes, as well. But marvellous. :)

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

Sometimes, but it depends on what you are doing with a data set - even if it's 1.75Gb : from the link...

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

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

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

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

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

Not really. They could be trying to build a company-wide data lake. Some files in the lake will be very small, others will be very large. Because it's in HDFS it won't matter if the very small ones suddenly become very large.

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

I mean, end users also groan because the user experience for spreadsheets is far better than writing map reduce jobs. One is a skill that a large percentage of BS graduates have, another is a skill that a small percentage of CS graduates have.

You don't need to write map reduce jobs to access data in Hadoop. In fact, you can do it from excel:

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

If users were previously empowered to do a task themselves, and now need to request another team do that task on their behalf, that's a very valid reason to be groaning IMO.

You have to understand that enterprise class companies don’t want Access databases under people desks. It might be valid reason for the sales or ordering guy who loses control to complain. For the IT professionals at the company it allows them to create a scalable solution that can be used at an enterprise level.

And suddenly every addition that could be done in 5 minutes now takes months or gets rejected by the devs.

Hadoop isn't Oracle. If you need to write a file to the data lake, you write it. If you need to read something out you do that too. There's no schema, it's actually a file system with actual files in it, like your shared drive. Except that it's distributed, can handle arbitrarily large files and lets you perform queries on those files instead of downloading all 2TB to access a single row.

> Us programmers don't always make life easier for end users.

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

One would agree with this. Most IT groups I have come across seem to go out of their way to make life difficult for the end user.

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

Us programmers also think about things like failure scenarios and disaster recovery. It may be trendy to criticize us for overcomplicating designs--and many do--but non-tech people using Excel are often doing more than just getting the job done...they're creating a disaster waiting to happen.

I have been on both sides of the divide and my view is that the overcomplicated designs are more hazardous. If one understands the end user and their requirements, one is generally able to steer them in the appropriate direction.

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

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

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

I wouldn't generalize too much. I once took a job where a $50 million in revenue a year business was running on a single AWS server that included everything (web, database, cache, etc.) with no backup of the data and the server was hand-maintained.

It was truly a disaster waiting to happen.

Anything that can be done in minutes with Excel could be done in relatively short order via an actual programming language.

Adding a new screen to capture information? With a nice graph to display for output? Does it conform to your UI style guide? Does it have passing unit tests? Is it integration tested? Is it code reviewed? Any migrations necessary? Any business continuity ramifications? Etc.

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

Does your '=vlookup(x, ...)' have tests surrounding it? How about code review? How about source control?

Actually, in my experience "sanity checks" were very often built into financial models.

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

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

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

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

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

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

In my experience, the fact there is a human being staring at the output all day goes a long way towards mitigating the lack of automatic tests.

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

There's also a bevy of issues that impact programmed systems that (in theory!), don't apply to Excel.

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

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

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

I think you missed the point. The comment you replied to is stating that excel does not have these things. Proper software engineering will have these. Excel is fast, but also easy to repeat a mistake. With unit tests, software is much less likely to repeat a mistake. Once the mistake is made, found, corrected, unit tested, making the mistake again is likely to be caught by the unit test.

With Excel any reasonably capable business analyst can do sophisticated calculations quickly without needing anything else. With a web app, SQL database and whatever the ramp up cost is huge in comparison.

With Excel I can do things fast, at the beginning, but then I find that it get a bit more tricky, I get lost, I find out that things are wrong, I do more, I want to move to R. I can't move to R. It will take me a week. I have a day. Then I want to move to R, it will take me a month, I have a week, then I want to move to R, it will take me a year, I have a month. Then, I want to drink bleach.

The ramp up cost isn't months vs minutes. That is just pure nonsense.

I disagree. I am working on something right now at my employer where we are taking spreadsheets that have been used for quite a long time (and were created fairly quickly) and they are converting all the business logic to code. Each workbook is taking weeks for them to code and test and then send back to us to sign off on.

I'm a coder DOING some of these conversions. I see both sides of the coin here. One additional point I wanted to mention is that once we have done one or two, or a dozen of these conversions, we start to see common themes.

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

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

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

It certainly could be if these tasks now need to be delegated to a centralized software dev team who is fitting them into their project queue.

Erm, they would also need a programmer at that point. More to the point, excel is more of a thinking tool that allows for lots of tinkering with real time feedback. Programming languages require lots of thinking up front and are not meant for thinking without significant augmentation (REPL, Jypiter notebooks).

Excel is a garbage environment and its pretty trivial to find environments that provide both a capable environment AND immediate feedback.

Please tell me where every user can create his own gui within 5 minutes with shapes, activex controls, pictures, keyboard shortcuts, combine those with code which was created by macro-recording (and as such needs the most basic knowledge of programming if any), that can be opened by almost every PC in the business world (as 99% has excel installed), which comes with 100's functions and formulas allowing to create anything and everything. On top of that, please no higher costs than ~50$/user.

Delphi, before Embarcadero shat the bed

It might take a time till coder has time for it. And coder may misunderstand requirements and needs time to get them.

if it's important then coder has time, coder will understand, and coder will spend the time.

By accountants and financial analysts?

You ever tried to implement a generic pivot table builder? Good luck. See you in 2019

I have! Twice :)

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

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

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

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

And now that it's a proper application they have to worry about authentication, down time, security, documentation, maintenance, and the customization and changes they could once do in an instant now requires filing a feature request, waiting for it to be implemented and deployed...

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

Excel is good for many things, but I'm not sure that complex financial models is one of them. Its convenience is balanced out by its lack of testing facilities. If you assume the industry standard of 1-5 bugs per 100 lines of code, you've got a huge liability on your hands.

Depending on what kind of firm or sector of the financial industry you're in, financial models have a problem of constantly changing. Web apps and other programmed solutions work well when you have to repeat the same model over and over, but they don't work well if you're constantly adjusting the model or changing its parameters/calculations.

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

Nearly every Excel wordbook I've ever reviewed has errors caused by updating a complicated model.

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

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

Maybe that implies that we just need to start teaching analysts to write code, but the problem is that when you switch from spreadsheets to honest-to-goodness text-based code, you're just trading one set of problems (spreadsheet mistakes) for another (bugs in your code).

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

I only worked in finance for a brief time, but the operating procedure at my job was to formulate models in Excel, then validate them in a formal QA environment before using them in production.

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

> I really doubt any major finance player allows someone to ...

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

Models have lower bugs.

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

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

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

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

Also - analysts get fired a lot faster.

IT and developers need to realize that they are expense generators not revenue generators in 99% of the companies.

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

Except excel is a generic solution. If you have to build a specific solution for each processes your costs skyrocket and people lose the ability to quickly implement something on their own. Everything takes forever to get done because you're constantly haggling with development and writing requirements instead of just opening excel and getting work done.

I've never seen two consistently created Excel workbooks. There is a lot of wheel inventing going on in the Excel world. It's possible that a more formal system would have less duplicated effort. It's also possible that doesn't matter.

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

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

> There is a lot of wheel inventing going on in the Excel world.

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

On the other hand I spent a lot of time as a data analyst explaining to business folks who "know excel" that their spreadsheets don't match because they used a different definition of week, or revenue.

Not really. In the long term, reg fines and dev costs balance out.

Citation please.

Pretty much every rogue trading scandal in the last few years is caused by some guy coming to the front office from the back and gaming the system with knowledge of its weaknesses. In more or less every case, poor front office controls, systems or data practices were identified as the root cause. Example https://www.google.co.uk/amp/s/amp.theguardian.com/business/...

And Lord knows software devs never implement poorly secured and/or buggy systems.

I work with engineers (mostly mechanical) who are in the same boat. We built a quick python app to do in ~30 seconds what their excel/VBA shit fest did in 45 minutes.

Excel and the like are better than nothing and for most non programmer numbers people probably fine - but if you need some real heavy lifting I would bring in the bug guns.

I like that -- Programming should be called a bug gun more often. How can we get more bugs in here, lets bring out the bug guns!!

Not saying that I don't believe you, but I've never seen a VBA code that executes longer than 1-2 minutes. What exactly does one do that takes so long?

copy the spreadsheet, modify some code because the need is sightly different (or the spreadsheet need adjustment) and voila. Either it works just fine on the first time, either you just begun a one hour debugging session.

To me excel is just fine, except when its not. A French bank lost 6 billion ten years ago because one market maker made false entry on a spreadsheat and no one in the back office noticed for more than 2 years. And the back office was not at fault here.

e.g. when reading in data one cell at a time instead of reading in a range at once. Basically inefficient programming. It's probably not that much about Python vs Excel but more difference in knowledge/expertise/capabilities.

As someone who works with these guys daily, you're 100% correct! The only thing that makes them change is new regulations telling them they have to.

Yes. I use Excel every day and while there are some serious cons (like a large data sets eating up tons of memory, crashing as a result, etc.) it is still an extraordinarily powerful tool. It is also extremely intuitive for the basic user and insanely powerful for the super user who really knows how to take advantage of the more advanced features. And with VBA integrated, it gets even more powerful.

I've seen some pretty amazing sheets/templates put together in a few days that would take programmers weeks or months to put together. And since you don't have to know how to program at all to use it, it's easy to see why it remains around.

The same excel workbook that let's the traders do their job would take months / years to convert to a web app

Simply taking an Excel workbook -- even data connected -- and using that as the requirements that drive an implementation would be extremely quick and speedy, the result having a litany of advantages.

But that's never how such projects go. Instead everyone has to greenfield some idealized pinnacle product.

I don't know your background but "taking an Excel workbook and using that as requirements to drive an implementation" is not speedy. I've done this several times in my career and it's very, very difficult. Not because there is any "idealization", but because the reason the spreadsheet works is because a human is doing half of the work. These spreadsheets are not some "hello world" easy to understand product - they are the result of months/years of effort by a team of individuals which has grown beyond Excel's ability to handle. Translating that into code is not "quick and speedy" even if one is doing a like-for-like implementation.

Currently web-ifying a huge pivot table (30+ columns) into a dynamic library SQL report for access via the web. It's a nightmare of horribly complicated aggregate grouping/totalling across many columns.

Additionally the original excel sheet made incorrect assumptions, resulting in a re-specification/design of what all the totals actually count.

NOT speedy.

"Additionally the original excel sheet made incorrect assumptions ... NOT speedy."

So...not as I described then? If you are evaluating assumptions and going back to the drawing board, of course it won't be speedy. The point is that if it's simply a requirement to be implemented as is, it is a quick and speedy process.

And it is. I mean, there are dozens of products that will automate the entire process it's so rote.

No but I don't think what you described exists but for the most basic scenarios.

And I absolutely agree. My point was that Excel workbooks exist in a no man's land, where untenable practices are allowed by necessity. When we transition away inevitably we add layers of error checking and validations and security controls and processes, etc, that turn a simple project into a monstrosity. But it isn't that Excel was efficient anymore than having the random cowboy coder throw together the most adhoc thing imaginable (but quickly) is efficient.

Someone on HN said this once, and I found it profound and have stolen it:

Excel is the most powerful REPL in the finance industry, and it's ubiquitous.

What I don't understand is when shops start stressing the scalability of Excel, such as accountancy shops that share their spreadsheets around through primitive systems of locking (even some of the big 10 accountancies do this), why they never considered anything like Access.

The jump is straight from Excel to full-on DB backend, with no transition in the middle.

The transition isn't free. Additional software means more technical overhead. Now you need to worry about training your teams to understand and maintain an Access DB instead of pure spreadsheets. You'll now also have to worry about hiring people who'll be able to adapt to the new system. You'll probably need to hire a full time DBA too.

Also, if it ain't broke, why fix it? I feel like a transition to a new system at times is akin to a software rewrite.

If there's something the world needs even less than more Excel, it's more Access.

Errm, I've seen spreadsheets that take an hour to open and "overnight" to update.

Unless it's changed in recent versions Excel isn't threadsafe, so using realtime updates is something that works 99.9% of the time and blows up the other time leaving no evidence behind it when it comes to audit.

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

Wait, people are making API calls in VBA to insert into Excel spreadsheets?

Reply on news.ycombinator.com