EDIT: I like a lot of the responses here. They clearly illustrate the disconnect between end users and IT/devs in many companies.
What matters in terms of better value is whether or not one can understand the problems space and find an appropriate solution for it. All sorts of solution space solutions can be used, some of which are better than others. The point is to try and keep it simple enough for the user, irrespective of how difficult it might be for the IT guy.
The knowledge of Hadoop in and of itself is not as valuable as one would suggest it is.
[Edit fix wording]
Hiring a hadoop guy for what used to work in excel is a bad decision in most ceases...
as opposed to fecking sharepoint
Yes but https://aadrake.com/command-line-tools-can-be-235x-faster-th...
"Since the problem is basically just to look at the result lines of each file and aggregate the different results" which is why this works on this data for this problem. Many problems are not efficient to solve in this way, for example searches through large products and expansions, especially where the evaluation function is costly.
Some problems are data bound - you are handling 100'sTb of data, hadoop can be a good fit.
Some problems are CPU bound, and embarrassingly parallel - you require massive parallelism to get through the core seconds that you need to solve the problem. If you need 1 million core seconds and you have 1000 cores, and a way of getting the 1000 cores to do 1/1000th of the work... all is good.
Some problems are memory bound. You can build single space machines with many TB memory now, also you can cache with SSD's and optanes more than in the past, but Hadoop does offer a utility for these problems.
But in a corporate all problems are admin and discovery bound. If your dataset is what I need and I don't know about it, can't find it and anyway if I did then you won't give it to me and I can't unlock it legitimately; I am screwed. If it's a proper corporate asset then I've got a fighting chance.
End users groan because people groan every time anything changes and they have to learn something new.
https://support.office.com/en-us/article/Connect-to-a-Hadoop...
I'd go so far and say: We programmers often make life more difficult for end users.
This has been a source of work for me over the years, providing a means to bypass the IT department to get end user work done.
However, the general view of IT departments is that they just dictate what they want and not help with what is needed. policy is set and then demanded to be followed with little or no justification to the end user why and how it will benefit the end user.
The number of times that I as IT professional (designer, developer, etc) have had to fight the IT department over some problem to get it solved is far too many. Instead of fixing or changing something that will allow end users to actually get on with their word, IT departments are seen as dictatorial time wasters who don't get back to you to sort out what is needed.
Part of our job is to think of failure scenarios and disaster recovery. This should not get in the road of those using the systems being put in place. The end user has a job to do, we as professionals should be providing good solution to enhance as well as protecting them from any disasters.
It was truly a disaster waiting to happen.
Engineering has overhead otherwise it's just a bunch of cowboys that are no better than Excel hackers - and probably worse, because they have a lot more rope to hang themselves.
Does your balance sheet balance? That's a pretty basic "unit test".
Do your calculated values match the financial statements values? No? Throw an error.
Divide by zero? Reference a blank cell (similar to an unset variable)? Using a value as a number? Referencing outside of a range? All immediately throw an error that will flow through your model.
And code review often does take place - in banking at least, models written by analysts are checked by associates and then VPs. People who understand what the numbers should look like can spot mistakes pretty quickly using a TI calculator.
Source control is a problem, but many models are one-offs for specific projects - no company (or deal) is structured identically.
You're identifying problems that have, for the most part, already been solved. Not perfectly, but in ways that work well within the structures that have been built around the use of Excel as a core tool.
As programmers, we write tests because we can't see what's happening inside the program while it's at work, so we have to give it a thorough exam before letting it do that. Spreadsheet jockeys can take a radically different approach to validation.
I can't guarantee the a programs output will be the same after every VM patch, component update, re-deployment, or even just re-compile. Generally we have multiple devs touching the same system, or same code. Generally we want that code to be robust in the face of reuse in other components and contexts. Generally we have no ability to see production errors unless they are properly logged and monitored, etc.
Worst of all I, a subject expert in nerdy things, am implementing a model in a domain I am an amateur in and have no fundamental context for assessing.
A spreadsheet jockey inverts all those problems. They know what they want, and are intimately connected to its operation.
More often than expected, there are mistakes and misunderstandings in some of the spreadsheets. There are inconsistencies in the spreadsheets. In IT we also get calls that a spreadsheet is "locked by someone." Or that someone deleted a spreadsheet.
With code, and databases, we can ensure consistency between operators. We can also leverage the data across other applications since it lives in a database now. We can pull reports for management more easily, and with more automation.
As some other comments mentioned, spreadsheets are great for prototyping, but also have their drawbacks.
https://pivottable.js.org/ and https://react-pivottable.js.org/
Sorry but I have to call nonsense on that part. I've seen recently a whole trading operation converted to a system very close to a web app within 6 months. The hard part of the job was untangling the mess they created. And knowledgeable quants who actually program told us it's typical.
The financial pros have horrible, horrible stuff in their spreadsheets. They are used to it and they want (perhaps unconsciously) to keep their dirty hacks out of sight. Lest they fall from heaven.
Even as a software developer I will freely admit that Excel is the right tool for many jobs. We shouldn't be so eager to transform workbooks into formal apps unless there is a really good reason to.
In that case Excel's instant update and flexible (but quite powerful) formula scripting is often the best tool for the job because it means you remove the programmer middle man. I know some shops prefer to just teach the analysts how to write code.
Often this is down to people adding/deleting/copying or modifying rows/columns and forgetting to change cell formula.
So, in theory you can modify a workbook and get instant results. In practice this seems to be nearly impossible to to get right.
In the end I'm personally still in favor of Excel because it has a much gentler learning curve, though you are right that bulk operations in Excel are ripe territory for errors. I don't think it's "nearly impossible" though, it comes down to following best practices the same way you would in any programming language.
I really doubt any major finance player allows someone to whip up a spreadsheet, plug some numbers in and start trading off that information. Excel is valuable in the early stages because it's fast, powerful and easy to collaborate in. Trying to eliminate it just because it's Excel is dogma.
Clearly you only stayed in finance for a brief time :-)
Your fin model has normal sanity checks and then functional checks.
And in the end the out put must be readable AND make sense.
And whatever bug you have is going to propagate down your projected time series.
Finance chaps use finance as their weapon of debugging. Sure mistakes get through. I’ve seen mistakes get through code as well though.
Also - analysts get fired a lot faster.
IT's and developers' job is support revenue generation, not block it because IT or developers think they have a better way. In most cases IT does not and neither do developers.
"Everything takes forever because you have to write requirements" is a feature of software development. At the end you get software that does something that you (presumably) understand. Excel is a gold mine of undocumented terms, processes and functionality.
It's fast to build but don't pretend it gives any of the benefits of proper software development processes. The value proposition is in fact that it has none of those guarantees.
There's usually more people who know Excel than people who can create systems. Meaning, the inefficiency is not that important. Some things just cannot wait or depend on a much smaller amount of people.
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.
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.
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.
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.
Additionally the original excel sheet made incorrect assumptions, resulting in a re-specification/design of what all the totals actually count.
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.
Excel is the most powerful REPL in the finance industry, and it's ubiquitous.
The jump is straight from Excel to full-on DB backend, with no transition in the middle.
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.
Wait, people are making API calls in VBA to insert into Excel spreadsheets?
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.