Comment by perl4ever
6 years ago
You can do nearly anything in Excel/Office with VBA, from automating manual processes, to BI reporting, to gluing together any and all data sources, it's just that CS type geeks hate it and refuse to learn it.
Being limited to only Office for a few months has not made me think "how horrible it is not to have real tools", but rather "how amazing is the amount of money and effort people waste on 'enterprise solutions' that are both orders of magnitude more expensive and inferior".
On the other hand I once had the enviable task of converting a spreadsheet to SQL - minus the data import, the calculations in Excel ran for hours, after the conversion the stored procedure took about 2 minutes to run.
And this was something that the business had to do daily - you can imagine how effective it was to start the calc in the morning, have it hog all your CPU for hours then get the results after lunch if you were lucky and did not have to restart the whole shebang.
Yes, Excel is brilliant at communicating with business users and getting stuff done quickly. But it does not scale and has some problems with the normal software development flow - just try to put your Excel files in version control and you'll see what I mean...
I am inclined to think that 99% of the time people blame a tool for being slow, there are very, very basic issues in how they use it. And often people are just not motivated to solve those issues if they don't like the tool anyway.
I was handed a T-SQL script once, that produced a report on a few thousand items in a document management system. I was asked to use it to get statistics on a few hundred databases with millions of documents each. Which would have taken a few centuries at the speed that it ran. So I looked at it and realized the core was a procedural loop, which ran a separate SQL query for every row of output. Once the procedural part was replaced with one SQL query, it ran thousands of times faster and then all that remained was to replicate it to a bunch of databases and build the report in parts.
My rule of thumb is that if anything is too slow these days, it's because something is terribly misconfigured or something is severely wrong with the algorithm.
>> the amount of money and effort people waste on 'enterprise solutions' that are both orders of magnitude more expensive and inferior".
Let me give you another perspective: One of the reasons the Enron scandal happened was because traders for a major canadian bank,my former employer, kept the trades on their deskop Excel and audit and risk had no way of knowing the actual risk to the bank. When I was hired to build an expensive enterprise app that allowed only limited set of financial models, every single user hated it at first: it was both orders of magnitude more expensive and inferior". But the bank now has full uptothe minute knowledge of its risk exposure and the system has proven itself.
That's not really what I'm talking about. That's back to front, where you're using Excel as the core repository for important stuff. I'm talking about using VBA/Office the other way around, to combine and report on stuff that lives in various databases and systems, and thereby prevents what you're talking about.
The kind of alternatives I was questioning are BI reporting solutions - I have especially painful memories of Qlikview, and I just googled the licensing fees and they are insane.
Finanacial models need to exist in a central repository where they could be audited or updated. The issue with Excel is that the model is created/managed on desktops and the enterprise has no way of knowing if the model is accurate. We use Tableau and we like being able to centrally manage the model.
I agree that people waste a lot of money on solutions without really understanding the problem.
I think the solution is rarely automate with VBA - however, using Excel with a little bit of VBA can be a useful first step on prototyping what a solution could look like.
But people do waste a ton of money on enterprise solutions that are just overkill for a given problem. This usually occurs in organizations where management has no real technical background and those who do don't have any real authority.