Most financial firms need their quants to worry less about the new programming language hotness, and more about moving entire systems off unbelievably complicated Excel spreadsheets.
1. An incredibly powerful tool.
2. No bar of entry (cost aside, true in corporate environment).
3. Very gradual learning curve.
4. The efficiency gain vs time invested is exponential.
Power Excel users, much like their VIM/Emacs counter parts don't use a mouse. It is just keyboard short cuts [1][2]. This makes them insanely productive.
Excel is something managers and executives can understand, so it became the default language for data analysis. Now technologists trapped using it have to create ex post facto justifications for why it's really "just misunderstood."
Excel is massively slow, makes it easy for beginners to make massive mistakes, computes lots of types in very odd manners, performs floating point operations wrong, and leads to spaghetti code that is a rat's nest of incomprehensible cross-references.
Worst of all, the lack of code path visibility usually leads to a bus factor of 1.
Sure, one can learn to operate Excel for data analysis with a decent level of efficiency, in the same manner one can cross the Pacific in a canoe, but both are still terrible tools for the job.
No contradictions.
It would be interesting to see if anyone could get some power Excel users together and construct a next-generation spreadsheet that encouraged better practices and worked to prevent huge messes. Spreadsheets are like SQL, where the initial release was so far ahead of its time that it managed to entrench itself into the very fabric of computing, even though it's long overdue for a reimagining.
Hotkey training built into Excel Python as an optional language along side VBA Proper Data Tables with Types and Indices, or even SQL in Excel. Regex Search over Columns PowerPivot use case training Web publishing of reports made stupid easy
Python:
R:
http://rcom.univie.ac.at/download.html
However, officially MS have chosen to move ahead with Javascript for add-ins and VBA-like automation:
https://dev.office.com/docs/add-ins/develop/understanding-th...
http://rockthecode.io/blog/javascript-and-excel/
As for SQL, you can use Data Connections from the GUI or ADO with JET/ACE in VBA to query Excel sheets, CSVs, etc as you please.
Regex can be used with the VBScript.Regexp object, but it's a slightly funky (perl-like) syntax and not a great implementation.
There was a comment thread around here a week or two ago where someone pointed out it's kind of insane SQL has stuck around so long, and no one could point to any worthy potential replacements.
SQL is based on relational algebra -- so it's the model with the best theoritical justification out there, even if the syntax could be improved.
It's the other ad-hoc solutions that is crazy that they keep getting suggested. SQL/RDBMS were invented because we had those (key stores etc, tree dbs) and they were crap.
That thought has made me wonder if logic programmming has something to offer in the design of new APIs.
https://en.wikipedia.org/wiki/D_(data_language_specification...
More controversially, I question the entire intent of making the core query language something that is putatively declarative, but then in practice often requires extensive engine-specific annotations to tell the engine how to actually do the query. (More on that https://news.ycombinator.com/item?id=3506345#3507281 ). I think RethinkDB's query language was much more imperative, because of the level of development resources they had, and I bet it actually worked out OK. However, even if I could not sell the development world on making SQL++/SQL-replacement non-declarative, we certainly could do a better job this time around of separating query strategy from query contents in some deliberate manner, rather than hacking crap up.
Imagine if, for instance, you could feed the query optimizer a query, get back a query plan that was actually manipulable and executable, tweak that to your tastes, and then send it back to the DB, rather than working via hints and circumlocutions and hopes and dreams.
It would also be nice if SQL were more composible. The serialized version of SQL is not practical to use string manipulations to combine two queries into a larger query. Many languages have libraries that permit this, but they're always second-class citizens. If I were redesigning SQL I'd want something that handled this more cleanly. I'd seriously consider something RethinkDB-esque in the sense that it didn't have an "english" serialization, but was purely symbolic, leaving it to language authors to figure out how to best represent it in the local language.
Also, bear in mind that most if not all features I describe in this post exist in databases already. (Not sure about that last one.) What I'm saying is that SQL integrates poorly with all that, not that the features don't exist. Recursive queries and common table expressions also seem ripe for some serious rethinking. Plus I think for a long time SQL really kinda limited the sort of DBs that would be produced because if a feature integrated poorly with SQL, it was a lot less likely to come out. (In particular, structured cells took IMHO forever to come out. Possibly the massive market failure of "object databases" also scared DB developers off from that feature too, though. They aren't the same thing but may be closely enough related.)
The barriers to moving beyond Excel can be overcome, but it will take some serious effort on many fronts. Both Excel and SQL embody genius concepts, but are such poor implementations that it is easy to conflate the cruft with the advantages.
Regarding your "stockholm syndrome" comment above: Someone in his car hears a PSA about "some guy wrong-way driving" on the very road he is on and thinks "one? hundreds!". Unless you can beef up your argumentation you are that guy.
That's fallacious too. I can be right, even if my argument is incorrect or unconvincing.
Warren Buffet and Nate Silver are both driving against traffic and both of them are righter than everyone else combined.
> SQL [...] cannot be compared to Excel
What Excel and SQL have in common is that they're both a first attempt at a solution to (different) problems, and they've been too successful to properly iterate on. That's why everyone uses some proprietary extensions to SQL and everyone extends Excel with VB or C#.
For clarification, is the GP referring to Microsoft SQL Server when then they say 'SQL' or do they actually mean SQL?
Microsoft's product naming convention is confusing IMHO.
I can still take my ad hoc SQL query data and run decent analysis and produce graphical summaries in less time than it would take me to setup the boilerplate I'd need in C#.
Arguably something like Matlab or R would be similarly quick for a lot of things - but I'm not even slightly sold that they are safer based on my observations of their use. I've certainly seen plenty of formal code that was less readable than a decent spreadsheet.
I'm not really a fan of excel tools, and tooling. VBA has made me want to actually smash my computer in the past. But to claim that it isn't incredibly powerful at working with a few megabytes of raw data is flat out wrong.
I'm just a lowly DBA re-posting and summarizing comments [1] for karma.
All I see is the same old Martin Shkreli video that has been floated around before, and all you see him do is 'Vim' around as he explains his thoughts -- not on Excel, but on company financials.
Also, if you post a lopsided list of pros, it makes sense to the audience to see someone else post a lopsided list of cons. But then you reply with pettiness. Why?
http://www.bloomberg.com/news/articles/2013-04-18/faq-reinha...
It makes it slow, but usually for these sorts of things you want your data to be available on the scale of days, not nanoseconds so it works out. We have a complicated grading sheet here that manages all of the students information all one the same sheet in a google drive. Grades, attendance, recitation attendance, and at the end of the class homework.
It also verifies the test answers against the correct ones to make sure we score exams correctly.
Excel is great for fixed sets of data that need simple map/reductions & input verification because that's all we really use it for. After that move to something like Python&Numpy/R/Julia/Matlab.
Would love to work with a replacement, even if it is some sort of Pandas/Python/Matplotlibb derivative - but it takes too long to set up things with these tools, and it seems not all operations are as trivial as I want them to be.
Did it have bugs? No-one knew.
And there is the reason Excel (and spreadsheets in general) are dangerous.
Another two: Onboarding Process.
Even if they have a fancy tool, someone is using an Excel spreadsheet to figure out how to subvert it.
- Resolver One ( https://en.wikipedia.org/wiki/Resolver_One )
- Project Dirigible ( https://github.com/pythonanywhere/dirigible-spreadsheet )
However, it's not all sweetness and light. Excel even gets some basic calculations wrong - and those ignorant of its quirks happily propagate those errors. More problematically, it can easily be pushed to the degree that your modeling is really beyond the tool or spreadsheet's design strengths without knowing it. And debugging is a pain in the ass. As a result lots of erroneous outputs get presented as meaninful.
Does this actually mean anything?
Probably true for smallish values of competency, but it must be logarithmic after that.
OK, does this mean anything? How have you quantified efficiency? How have you quantified "learning curve"? What data do you have supporting that the relationship is exponential?
Nobody here has been able to elaborate on the initial statement "The efficiency gain vs learning curve is exponential". People are just rewording the sentence slightly and passing that off as an explanation. That seems to indicate that nobody knows what the statement means because the statement is vacuous.
This is true! Then you hit a pretty hard wall with the limitations of the tool.
Somehow, sloppily, "steep" has come to mean difficult to learn, rather than quick to learn.
In the original version: A steep learning curve means quick learning at the beginning. A shallow curve means that it takes a long time to build up skill.
If all the data you receiving is also coming to you as an Excel format (csv, xls, xlsx), but with major differences in formatting, or wholly inconsistent formatting. Now you have a multi-month long project just to have a consistent import script. Replacing a 1 second task done 2-3's times a day with a 4month project has an ROI on the scale of decades. Not worth it.
Then you add visualization. What is 3-4 keystrokes in Excel is a lot of back of forth, learning a new library, ensuring it works on your system. Vetting the visualizing, dealing with that weird bug on the triple line double axis line chart.
Then you have to validate integer handling and mathematics to ensure your newly written Python, Julia, etc. handles the same as your well vetted Excel Spread Sheet.
Replacing that one slow bloated spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel.
And now you have a scalable system. You can go from something one employee takes all day to look at 2x/day, to something anyone in the company can see in real time on a dashboard of some sort.
Is that worth it? Depends
http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm
Of course that raises the question would any other software environment have a lower error rate?
Quick and very, very dirty.
You have a Turing Complete spread sheet.
Excel is useful in one particular case only: when you don't want to build a GUI. It's great as a not-very-pretty interface for functionality written in DLLs.
For any process that's well thought out, you can write a Python script if it's not time critical. And it probably isn't if you were doing it in Excel.
The main problem with Excel is it's too easy to write an ad-hoc fix. Sounds like a weird reason, but in finance they just pile up and up and up. Finance Excel users also tend to know just enough coding to dig a huge hole, and just little enough to not understand this. Soon you have an unauditable mess, and the business is almost never going to spend time paying up technical debt.
There's also the philosophical issue of ever more complex models. If you have some sane coding practices, you will tend to favour more elegant code. Balls of spaghetti are more obvious in something like Python. More elegant code is connected to more elegant models. Inelegant models, such as the ones often bragged about by M&A guys (let's be honest, they're sales tools, not predictions) when written into an ordinary language, will look like the balls of spaghetti that they are.
Insane.
Ended up putting it all in a database and developing an excel add-in to pull it from the database as array formulae. Used a great library called Excel DNA to develop the add-in using C# if anyone is interested.
So you could build a sheet that pulls in portfolio holdings for yesterday where yesterday updates each day and then compute performance and risk stats referencing the data cells in the sheet and it would all update.
In that context it was just an easy way to build reports pulling data from a database but same applies to quickly doing one-off analysis in Excel pulling dynamic data from the database - guys in finance tend to not be programmers but they're really good at Excel.
The add-in approach was really useful too because you could create function that returns the holdings of a portfolio to an array of cells (an array formula) and have a drop-down box with all portfolios that fed the input of the formula so that when you change the combo box, it changed the portfolio data and then everything recalculated off the back of that :)
He worked for them till he got out on his own. All his backtracking software is written by him and is in C (nice GUI, graphing feature, etc). He uses it to find his edge.
His trading platform is Excel...Obviously he doesn't do HFT...his trades are measured in days.
I know - 1 data point, but if a software engineer who is better than me in both trading & coding is using Excel, I'm not going to knock it.
But pretty soon you are mired in spreadsheet hell. Nothing can be seen or understood, everything is invalid or valid - who knows and worst of all when something stops working you don't know why.
And you don't know when it will stop. Goodbye agility!
Any spreadsheet with more that 2 days of work to reproduce it should be counted as IT and put on a formal risk register until it is recoded and removed. But dream on..
Bonus points for the facilitation of any type of documentation, automated testing, or version control.
There can be no god.