I've had colleagues that disconnected their juniors' mice to ensure this.
The reliance on excel in all aspects of investment banking is remarkable. From the simple stuff in corporate finance (merger analysis, capital issuance etc.) to complex derivatives pricing on the market side.
On the pricing side, external libraries typically handle the heavy lifting, but excel is the glue that brings it together. Certainly not the most efficient tool - I've had spreadsheets that take over an hour to calculate. You'd hit F9 and head out to lunch.
Good point. The upside of Excel is that in return for predictably slow execution, you get a tool that can be used on the fly without learning SQL or adding a DBA layer to every task.
If you and your parent poster are right about why Excel is so prominent, and I personaly think both of you are, then we might be able to see a shift in economics and businessmanship and in the way Excel formulas play a large part of what it is that makes the world go 'round, once business men start to learn how to program, or when Excel start's to learn to talk "business", but no earlier than that.
Which movement do you think is strongest? The AI camp or the ones that think programming should absolutely be a part of a proper education in business or economics? I hope for the latter, because I think it will be the end of the huge gap there is today, between the decision makers and the engineers, two groups who quite often but for no good reason feel alienated from each other.
The division of labor here is due to time constraints, not a failure to understand value. A lawyer can also be a doctor, if not for the fact that they'd get little of either done.
If you make programs that 'speak business', then you make programs that are significantly more complex than those that 'speak computer'. Learning how to handle this complexity and optimize this kind of code is not something a business/economics person wants to spend their time doing. So they leave it to someone who specializes in it.
Yes, I see what you mean. But surely, just adding Programming to the curriculum of businessmen, who I'm sure are quite intelligent or else they should be considering other occupations, does not equate to a person having to learn Another Occupation. Or do you think programmers, good ones, just know Programming?
Some folks do combine excellent programming skills with excellent business skills, but that's not necessarily what everyone should aim for.
I find that you can score someone on a whole range of skills, much as you can on computer games. The more overall skills you have, the more you will cost. If a job requires excellent programming skills but doesn't need any business skills, then paying for someone that has excellent business sills would probably be a waste of money.
In terms of your parent point about whether all business analysts should learn some programming, I think that programming skills are definitely helpful for business analysts, but they are neither necessary nor sufficient. Different skills help in different situations.
I'm curious, is there a way to "profile" an excel application? If you suspect a sheet is taking longer to calculate than it should are there tools that can help you drill down to discover the bottleneck?
ex Excel PM. There is a project that was between the Excel team and the high performance computing team at Microsoft for exactly this purpose. Not surprisingly mostly used by investment banks and insurance companies. (https://msdn.microsoft.com/en-us/library/ff877825(v=ws.10).a...)
I don't think you even need to go that far. Can you optimise your implementation so that parallelisable chunks could run across all your cores? This sounds like the kind of think Excel may even do "automatically" but like many such things you usually need to structure your implementation in a suitable fashion.
Excel uses multiple cores for calculations. You can configure how many to prevent it sucking up the entire CPU for long calculations.
It's a pretty easily parallelizable problem since the sheet is just a dependency tree with the cells as nodes.
The build-in functions (e.g. probability distributions etc.) could also be multi-threaded, although I'm not sure if they are. Our external API called from cells was written in C++ and already multi-threaded.
It's a NP complete problem. What I mean is, there is no generalisable solution to this. Optimising algorithms employed by sophisticated technical users (think `-O`optimisation; Java hotspot) typically employ sets of heuristics that run the code in more optimal ways based on certain patterns. To trigger these you usually have to be aware of them and write your code in a certain way.
Given that Excel isn't really targeted at these kinds of user (heavy lifting typically offloaded to external libraries) it wouldn't be "Sad" if they didn't already do it, and I would in fact be pleasantly surprised. Which is why I'm asking the question.
To trigger these you usually have to be aware of them and write your code in a certain way.
Compiler writers also look for common patterns in people's code and figure out how to optimize them. when you have both years (decades) of legacy code and developers who don't even know what patterns are optimized, you as a compiler writer need to optimize the code that is being written.
On decisionmodels, the website looks like it is coming straight from the 90s (but after all so does excel) but it is a gold mine of stuff to know before one can claims to really understand excel. I highly recommend the reading to anyone aspiring to be a "poweruser".
Seconded. decisionmodels.com has excellent stuff on the difference between F9, sh-F9 and ctrl-sh-F9, and explains when the formula graph gets rebuilt. When you're building calc heavy XLL addins it's important to have a clear understanding of the behaviour of the calc engine code invoking your addin.
Unfortunately, these shortcuts are language-dependent. Just as the function names.
And you cannot switch Excel to English, but leave Outlook in German. Language setting is global over all Office programms.
And even if you could, at work the IT department has greyed out that setting via some policy. Even though I am Administrator on my work desktop, I cannot change the language!
>And you cannot switch Excel to English, but leave Outlook in German. Language setting is global over all Office programms.
Non native english speaker, but I use English menus for all programs, and English menu language for the OS.
As long as I can write and spellcheck in my native language, I am fine.
Unless I didn't speak english at all, why would I chose to see my language's word for Edit instead of "Edit", and never be able to take advantage of 2.000.000.000 tutorials, instruction videos, forum posts, that almost all assume english menu items?
> Non native english speaker, but I use English menus for all programs, and English menu language for the OS.
So do I - on workstations I control. Alas the mandatory corporate Windows laptop isn't one of them, so I have to constantly switch between English on my own devices and French on that laptop... Definitely hampers shortcut memorization.
Also, a curse on Microsoft for localizing Excel formulas - what were they thinking ?
Well I see the point to try to cater to users that might not be fluent in english - the target userbase for Excel is much more broad than a programming language, and if you want someone with low knowledge of both computers and english to be using Excel effectively, it helps a lot that e.g. your rows column is called "rows" and not asdfghjkl or something gibberish for you.
You seem to be french - so I am confident you can picture someone (dad, mom, an old uncle perhaps?) that would have an easier time remembering that a function is called RECHERCHEV than if it were called VLOOKUP or ASDFNASF!#^E3 or what have you.
As long as there are not namespace clashes, it seems to make sense to allow both, simply as a parent said the pervasiveness of English language documentation (that MS do a lot to promote through awards and certificates).
I don't know who downvoted you without any comment, I for one believe that that could be a good idea: just support all languages as alias and hide what is not your language - but don't cap the use of the foreign functions. If the interface is gentile whenever you see a spreadsheet from another country, it could be feasible.
MS actually did that with Visual Basic for Applications in Excel 95.
Umlaute in keywords, yay!
Example stolen from Wikipedia:
Prüfe Fall wd
Fall 1
' Auf Sonntag wird Datum vom letzten Freitag zurückgegeben
VorherigerGeschaeftstag = dt - 2
Fall 2
' Auf Montag wird Datum vom letzten Freitag zurückgegeben
VorherigerGeschaeftstag = dt - 3
Fall Sonst
' Andere Tage: vorheriges Datum wird zurückgegeben
VorherigerGeschaeftstag = dt - 1
Ende Prüfe
Ugh, I did some VBA in Germany around 2000. So crazy that keywords were in German (and command and periods have different meanings in numbers). Totally unportable code.
The non-programmers in other countries are actually more confused with the English formulas than with their native ones. I know as, not living in the English speaking country, I had to explain various Excel functionality (and formulas) to such people.
And it's true even for those who during some time in their life spent some years in some English speaking country.
I do the same thing, and one of the reasons why I always do that is that it makes it way easier to troubleshoot stuff when you're using English locale.
Googling will give you a much broader pool of solutions, and having to translate the names of options and commands you need help with just to gain access to this pool is an unnecessary extra step
If you have your os locale set to non-native language, then most of the sites will default to English language as well. That's something that annoys me as hell and I wonder if there is any workaround...
it wasn't IT. on XP Microsoft realized everyone used the computer in English on all markets, so they started to charge for it as an extra language pack if you bought the os license in another region.
Apple doesn't charge you for a language pack in OS X, it ships with all the supported languages included.
And also doesn't charge for OS X at all.
I can't see how MS is better given that you pay for it.
That's a sensible comparison only if you forget that you must to pay Apple premium for the hardware on which OSX is capable of running - the price is folded in there.
Sadly Excl has caused so much human induced error that is not audit-able. Learn to program in a statistical language. Billions are lost due to using the wrong tool for the wrong job.
- programmers never make copy and paste errors in other languages
- programmers never reverse a plus and minus sign in other languages
- programmers never break things with a last minute change
- there are never errors due to the size of code bases
Oh, right. Nearly all of those errors happen no matter what language you are using. Because they are humans giving instructions to a computer, who just does what the human says.
(Ok, the version control complaint might be better handled by merging in git or other DVCS. And there are troubles when people give you a csv where a field is the text 1e24 and excel turns it into a number because you told it to be magic when importing, rather than specifying that column was text)
Edited to add: the focus of that article is on accounting. Double entry accounting was created to make you input everything twice to help ensure you are doing the correct thing. You mess up one of the two items and stuff doesn't balance. Modern accounting software automatically creates the second entry, so you have no idea whether you did something wrong. That's the real issue. We came up with an accounting system that is slower to help make sure you did everything correctly, then we automated it so you only have to input one entry so accounting is faster and all of a sudden, we are surprised that more mistakes are made!
Your missing the point PEOPLE make mistakes. Where will you see the mistakes in Excel or a program. In Excel it is or close to impossible to see the steps someone did to get to the output. In programming you can see every single step and have version control.
> Because:
> - programmers never make copy and paste errors in other languages
But they are discover-able and you can audit them and then fix them
> - programmers never reverse a plus and minus sign in other languages
But they are discover-able and you can audit them and then fix them
> - programmers never break things with a last minute change
Version control!
> - there are never errors due to the size of code bases
TL;DR - Excel is just a really accessible programming language, and as such it's used by a lot of amateurs. Like Perl in the 90's, the ease of getting started means a lot of people can use it, but the average quality of their code is abysmal, since the average level of experience is very low. That doesn't mean the tool itself is necessarily bad.
-
Did you watch the Spolsky video? I only ask because this is all child comments from a top level comment about a Martin Shkreli video, and since I watched that first, you might have as well and not gotten to the original video. The way Joel suggests using Excel is very like how developers program when using good practices. Always name ranges and cells before using them. Never use numbers directly in formulas (unless obvious, like 365), use the named cell/range you set up. Use tables when you want tabular data so you are prevented from accidentally using the wrong formula for portions of ranges. Use the included named formatting themes for cells that are meant to be user editable, and those that are computed.
I watched the video and very quickly came to the conclusion that Excel is an IDE+Interpreter, like what people refer to when talking about Smalltalk (I assume, I've never actually used Smalltalk). The other thought I had is that it's probably the closest we've ever gotten to what people mean when they describe a visual programming language that just about anyone can use. Predictably, we often see the counterarguments to a system like that exhibited in Excel spreadsheets, which is poorly architected and designed spaghetti-code from people that don't have enough experience to know any better because they are amateurs.
Copy and paste errors look far more discoverable when you use R1C1 mode, and look to be outright prevented in a lot of cases when using tables.
Version control might be a good argument if it actually applied. Excel is a language, and I don't see any special version control baked into other languages programmers often use. It sits on top of them, and I don't see any reason why it couldn't sit on top of Excel as well. Export to a format that is version control friendly (perhaps with the help of an extra tool) and commit to VCS. Want to make a change? Check out the spreadsheet, convert as needed, and start working. Problem solved.
Really, I came away with a much clearer picture of what Excel specifically is after the Spolsky talk, and it probably wasn't even that advanced. What it did do was give a crash course for programmers that let you make the clear associations to what you do in other languages, so there was communication going on at a few levels.
Version control is about more than just holding different versions and being able to restore them. It makes it so much easier to discover what changed between one version and another. Excel files are too opaque for that.
I'm not sure how they are too opaque for that. If you are converting into an intermediary format, and saving that, depending on the format, it may even be easy. Here's someone that rolled their own solution, and they say they can edit it through a regular text editor[1]. Here's a company that provides a solution, which also purports to offer team collaboration[2]. Here's a free set of tools that provide versioning, change tracking,and diffing[3]. That's from a single google search, the first three results. I'm dubious that there's not a way to deal with this problem that fits most the sensible criteria you could come up with, given that this is obviously an need people have been working to serve for quite some while now.
I've worked a lot on trading floors the last 20 years. Excel was king in 90's and 2000's, recently there is a lot of Python & R around. Not that most of the really complex analytics and data come from C++/C#/Java DLLs or libraries, so many spreadsheets are complex but not that bad.
Excel is still king on the trading floors I've seen (mostly fixed income). There's always a push from management away from trader created spreadsheets but it's so easy for them to mock up a quick calculation, and then they start using it daily...
Not just IB. I'm in digital media and many managers/analysts have comparable skills. Really we are just looking at different metrics. Lots of the same data munging and modeling.
A lot of what he was doing wasn't even that fancy, just lots of holding ctrl to navigate cell ranges quickly and pasting of formulas and such. I'm surprised he didn't format his data more nicely though, but if this is just for his own personal use I get it. Color formatting for inputs vs calculated metrics are really helpful though.
