It strikes me that excel appeals to people for exactly the same reasons that lighttable appeals to developers. You put numbers and equations in and instantly see the results. If you need to do something complicated you can build it up, one step at a time and see how the numbers flow through the system. You can even try different scenarios by just tweaking the numbers.
Yes, for me at least. (IAMA non-coder, Excel power user)
SQL is the same way- it suits my "power user" style by allowing me to type in a piece of script, hit the checkbox to find errors (or run it in a test environment), debug, rinse, repeat. Then I can stitch together all these pieces into one giant operation and let it run.
I would love to learn Python but as OP says I also have a job to get done. Every time I sit down with a Python tut, it seems (maybe just my perception) that there's so much fundamentals, coding, etc before I see the output what I'm actually doing. That there is a larger upfront investment than there is when learning Excel or SQL.
Since you are an Excel power user who also knows SQL, I would highly recommend that you check out the O'Reilly book "Python for Data Analysis." Pretty much the first chapter just has you very quickly run through examples of using a library of Python called PANDAS. Python is an intuitive enough coding language that you can focus on using it first, understanding it later, since large segments of it will make sense to you off the bat.
I could go on and on, but the short order is this: Pandas has an object called a DataFrame which maps 1 to 1 with a sheet from an Excel file. You can pull in two sheets of data from Excel, do merges, joins, iterations, you name it. I am a professional who also "has a job to do", and after two 1 hour sessions in the evening with Pandas, I immediately began using it at work to manage the huge quantity of spreadsheets my team is generating, combined with output from various databases. Anyone who knows SQL will love Pandas, because it allows you to very simply and easily conduct SQL style set logic on data without having any of it in a database.
...you know that there's a significant group of, uhm... "programmers", that are really afraid of SQL and do everything to wrap it in things like ORMs and hope they'll never have to write it or think about it, right? As a programmer, I only started to like and "understand" SQL after learning more about the underlying relational algebra and seeing its beauty, but it was just a stream of WTFs for me before that... and some kids that I recognize as "thinking the way I do" can learn programming in Python starting from 0 in less than week but look at SQL with a complete "wtf" face ...I guess different people really think differently ...sometimes it's scary how differently
indeed ...but: try explaining to someone who has just went through the effort of picking up a general purpose programming language why does he need to learn "a retarded query language that doesn't even have functions, loops and assignments" :) ...of course, you can answer him "well... but actually it does" and expose him mercilessly to the world of sql extensions before he even learned basic sql.
I don't think of SQL as programming -- programming is telling the computer what to do, SQL is describing the data that you want and letting the computer figure out how to get it to you. Regular expressions work the same way.
The worst SQL is done by people who treat it like a programming language.
I've taught SQL to non-developers and most pick it up pretty easily as long they're taught the right way.
Small correction: The worst SQL is done by people who treat it like an imperative programming language.
SQL is a declarative programming language. It's probably much easier to learn for people with experience in functional programming (where you also specify what you want, not how to get it), and tools like Excel, than people who are used to thinking about telling the computer what to do.
You also might want to look into R (I would recommend using R with RStudio, a fairly powerful, but easy to setup IDE). I have a feeling that if you know SQL, than learning a programming language is well within your abilities.
The biggest difference that I have seen regarding the Excel vs programming issue is the level of transparency that is provided in each. In excel, you get immediate validation of your functions and operations because you can see the results at each step. However, the formulas/functions themselves are 'hidden' and as the number of operations gets larger, it gets increasingly difficult to track and control the process. This makes debugging a nightmare. With programming, the results of your operations are much less transparent, but as you get familiar with the language you learn tricks to quickly summarize your data in ways that provide meaningful feedback for what you are trying to accomplish. More importantly, your script contains a concise record of all of the data manipulations and operations that you are performing, making debugging a much simpler task (compared to a monstrous spreadsheet).
An additional benefit to programming is that, after a certain level of familiarity with the language, your productivity increases dramatically. People using excel tend to be much more deliberate and cautious when creating the spreadsheet because debugging is such a pain, which means they tend to operate at a much slower pace. With programming, you can quickly pound out a script (often without even testing the various pieces of it) because you know you can easily debug the thing afterwards. From my experience, the difference in working styles results in almost an order of magnitude difference in productivity (what would take me an hour or two to create with a programming language could take a day or two to whip up in excel) and even with the extra deliberation/time, errors are still just as prevalent in excel. (And this doesn't even take into account the fact that it is much, much easier to reuse code than it is to reuse a spreadsheet).
Bingo. This and the ease-of-use/approachability for non-developers are why Excel is still absolutely huge in the finance world, and why it's not something you just "grow out of" when you start hiring developers.
I agree, but the easy evolvability of a spreadsheet is a weakness as well as a strength. When you lower the barriers to entry this much, you get "developers" who don't have much experience with development. This is a good thing when you are creating simple spreadsheets that deal with minor matters. It vastly lowers the cost and speeds up your ability to get answers to questions that aren't worth a lot of resources.
It's a bad thing, though, when it is used for a very consequential analysis. If the right answer is very important, you have to do what it takes to make sure it's the right answer, regardless of the tool you use. It's okay to use Excel (I love it), but you really ought to have an "expensive" developer using it.
I was doing a contract for a financial firm, and I prototyped my solution with Excel. They were so pleased, they started using the prototype immediately and begged for additional features. I complied, but told them I should do some serious testing first. They were confused by this. "What is this 'testing' of which you speak? Are you saying it doesn't work?"
I tried to explain "testing" to financial people who had been using Excel for years. They weren't buying it. "If it works, it works," they informed me. I had to sneak my testing in to the new feature dev schedule.
At a later point, I told them I couldn't add anymore features until I had refactored what we already had. Of course, if they didn't understand testing, they really didn't understand refactoring, but I had noticed similar functionality emerge in multiple locations, and I wanted to put it in one place, test it, make it unmodifiable by users, call it from wherever it was (or would later be) needed, and use my testing system to make sure the spreadsheet still produced the same tested outputs from the same inputs after each refactoring.
These experienced Excel users were quite sure that this "refactoring" nonsense was all gobbledygook that had nothing to do with Excel. "You don't have to do all that stuff if you use a spreadsheet; that's why people use spreadsheets."
Again, I think Excel is a terrific tool, but as self-serving as it is to say so, I think that if you are using it for very consequential analyses, you really need to treat it like a "real" software development platform and use "real" software dev people and practices for your development.
