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.
That is an oddly fascinating video. He's quite good. I mean, he's out on bail pending charges for securities fraud, but in a way it makes sense, this would be an important skillset for that sort of grift.
He's being charged with running a Ponzi-like scheme and being "engaged in multiple schemes to ensnare investors through a web of lies and deceit". A lot of people in his business are aggressive and less than forthright. Securities fraud is _quite a bit more serious and less common than those personality flaws_.
The name Shkreli didn't immediately ring a bell for me, and I found this very candid interview with him on CBS News. He is very open and no-filter on every question, some of it makes a lot of sense, some of it is quite far out.
It's not a "good deal" because it's not consumption for the sake of consumption. High drug prices hit those unfortunate enough to be born with a sickness, and some of those people might have a hard time with their lives if majority of their time is spent earning enough to pay their medicine.
However, I like that he then goes on to say that there should be a system that takes care of the people who CANNOT afford the drug (government-funded or a corporate-driven solution of some sort).
The whole business take on medication and health care just seems crazy for a scandinavian citizen like myself.
Some CBS news affiliate brought him on to talk about the Epipen situation.
Honestly, he's very candid, and he says things people don't want to hear. Sometimes he says things that are just wrong and inconsiderate, but other times he says things that are simply inconvenient truths.
For example, he brings up the Epipen situation in a different light: He says $300 ($600 for 2) is significantly cheaper than the alternative (a trip to the ER, which can be thousands of dollars). He goes on to say it's a relative bargain, and that insurance companies should be more than willing to pay for Epipens because of the potential cost savings. To him the problem isn't Mylan (which has become the target of everyone's ire) but the insurance companies or medicaid.
Whether that's true or not it's a different perspective, and as a society we need to hear things that the masses don't necessarily agree with. The US system of healthcare is highly flawed, it's not a good idea to just make a boogeyman who will hide the real issues.
Giving a mugger $300 is also cheaper then treating a gunshot wound. Doesn't make it right. These companies are exploiting corners of IP law to block competition, using the US armed law enforcement as the muscle in their mugging.
The free market take is that competition allows for faster development of more newer, better, cheaper drugs overall. That may mean the occasional crazy pricing, especially in a constrained market where only a few entities can supply a need.
The socialist take is that the government should intervene in cases where a need isn't being met in a sustainable way by the market, and people are suffering.
Another way to look at it might be that one prioritizes the future over the present.
I don't think it's controversial to say that both these are true in certain situations, and sometimes those situations overlap (such as with medicine). In a perfect world, we would have all the benefits of both the free market and socialist takes, without the downsides associated with each. It's possible we might be able to approximate this somewhat, such as with Shkreli's suggestion that the government take over production of generics. Other options are better public funding of research and development, so drug companies can shoulder less of that cost (and thus charge less to recoup investment and make a profit).
In the end, I think neither system is perfect, and both have very real advantages that we need to be careful not to discount.
He did a podcast episode on the Milo Yianoupoullis (I tried okay) podcast. It was...interesting. Like you said, some of it makes sense, some of it is out there. Was really engaging to listen to though.
Well, I'm not going to force you, so feel free to abstain. Personally I like listening to all kinds of people and in different contexts. I found it interesting, and was recommending it to someone else who seemed to be in the same boat.
Although somewhat impressive, majority of us are similarly skilled with our editor/IDE of choice. My Emacs and IntelliJ workflows would probably look similarly advanced to non-programmers.
Ah, interesting. Tbh I rarely used spreadsheet capabilities and I usually used ob-ipython to load table to pandas in python and after manipulation write it back to org. I mostly use org tables for occasional manual data manipulation.
One problem with excel is that temporary steps you went through are hidden - it's hard to reproduce the excel spreadsheet. With org mode + ipython workflow I can trace all transformations that led to the end table, but hide them when they are not relevant.
I believe this gets me the best of both worlds - some form of WYSIWYG tables like excel + IDE-like programming environment in python and version control. Excel obviously lacks proper programming tools. Jupyter notebook does not treat printed tables in a special way org does.
Does everyone love this guy all of a sudden? I thought he was the most hated person in the world but I don't see a single bad comment about him here or in the YouTube comments. Quite on the contrary.
Well, first of all, he is mentioned here solely on his Excel process, and in that regards, his tweets about Harambe or purchase of a hip hop album are completely irrelevant, and honestly it is nice to see a community that can discuss a part of a persons work without tainting the view of that with other workings of said person.
But if you do hate him, you can apparently call him and tell him why and he'll take the time to discuss with you why you hate him, and he does this quite calmly and rationally (given the situation)[1].
Might be of help to someone else to realize that you are talking about Martin Shkreli, rather than Spolsky.
I missed the parent you were referencing, and thought one of the child comments was saying that Joel Spolsky was out on bail for securities fraud (which was the result, obviously, of me not catching who you were talking about)
Ooooh thanks. I've been reading a lot of Spolsky's blog posts from 2005-2006... I was thinking, what's happened to this guy in the last 10 years? Everyone hates him? Securities fraud? What?
It feels like people react to him the way they do Al Capone or Jordan Belfort. Obviously he's a jerk, it looks like he was probably eyeballs deep in securities fraud, but it's so audacious and disconnected from real life that it's like watching a scumbag in a movie.
Probably we're all better off with the law coming down on him, but that doesn't preclude a day-to-day fascination.
My opinion of the guy rose sharply after I learned that his drug pricing practices were standard for the industry, and that he did nothing that other companies like Valeant and Allergan have been doing at 100x the scale.
Yes he acts like a giant dick, but he's got great knowledge of how to analyse an investment, and recommends great books and so on. If his life would've turned out a little different and he was less publicly abrasive, he could've been celebrated as a child prodigy or an investment wizard...sort of like this Japanese trader cis: https://www.youtube.com/watch?v=S-e1MxcjFDk (an amazing guy).
Yeah, everyone condemns Martin Shkreli, but having watched the video Benjammer5 linked to, he seems like a regular bright dude / techbro (ugh..). I would not be surprised that a significant number of people who lambasted Shkreli, if given a similar situation, would end up making the same choices for that $$$.
I have found that most people make horrid moral decisions when it comes to money/career. They go into this weird denial, or fall back on, "Hay--it's legal!". I've had my fill of people like this, including a particularly greedy sister.
It seems like ethics, and morals are just missing in some people, and most of these people wear ties, and do it all in a legal fashion.
I have watch a few of his videos months, maybe a year ago? It seems like it was a while ago.
I watch his videos for two reasons; He was obviously opening up his life to the world, and I was curious to what this guy was all about.
I came away with, like myself, he seems to have a real lack of friends. And like myself, he definetly needs a girlfriend. Not someone he could buy, but an honest caring relationship. I definetly got the feeling he wanted people out of his league, and it really bothered him. Maybe I'm completely wrong?
>I have found that most people make horrid moral decisions when it comes to money/career. They go into this weird denial, or fall back on, "Hay--it's legal!"
I was just having a discussion with someone last night who was complaining about executive bonuses (in health insurance specifically), and how he thought that was unfair. He then went on to describe how he's moving into government work for the high pay, low effort, and how he would try to do the same pension spiking practices that's got California in so much trouble. After all, it's not illegal.
Were it not my house, I feel like I might have put my head through a wall.
So, on the topic of people making horrid decisions when it comes to money, I recommend a movie called "The Box" inspired by a sci-fi short novel by Richard Matheson called "Button! Button!" (Wikipedia https://en.wikipedia.org/wiki/Button,_Button_(The_Twilight_Z...). Not a spectacular moving, but two hours or so well invested, I believe.
> If his life would've turned out a little different
You know you can say the same thing about Pablo Escobar, and Hitler, and many others. Just because someone has an innate talent at something, perhaps even a genius, does not make them worthy of the rhetoric "if only things had turned out a bit different for them...". People do what they do, we shouldn't question what they could have done.
Stipulating that Shkreli is guilty of securities fraud, theft, and is a dick of a person, it's still awful to compare him to people who are responsible for the deaths of hundreds or millions.
Godwin's law (or Godwin's rule of Nazi analogies) is an Internet adage asserting that "As an online discussion grows longer, the probability of a comparison involving Nazism or Hitler approaches 1"—that is, if an online discussion (regardless of topic or scope) goes on long enough, sooner or later someone will ...
Godwin's law (or Godwin's rule of Nazi analogies) is an Internet adage asserting that "As an online discussion grows longer, the probability of a comparison involving Nazism or Hitler approaches 1"—that is, if an online discussion (regardless of topic or scope) goes on long enough, sooner or later someone will ...
His method is known. This is why the study of history is important.
Holy crap that's super useful. For whatever reason I never could figure out (or remember) a shortcut for the 'filldown button'. Instead I would copy the cell I wanted, and then control+arrow my way to the fill destination, shift+control+arrow my way back, and then paste.
This way is a lot simpler and should cause fewer mistakes.
Handy in conjunction with End,<arrow key>, which offhand (I don't have Excel on this PC) does something like: find the next entirely blank row/column in that direction, then extend selection to it (exclusive). If you've got gaps you can just repeat it.
So, if you've got one column filled in for every row, and one row filled in for every column, you can quickly select the whole box and fill it down from the first row.
You don't need to press end to enter end mode - holding down ctrl toggles it. So you can hold down ctrl and use arrow keys and, like it does with text, it will jump entire logical blocks - either continuous cells with content or continuous empty cells. Add shift as well as you select all that stuff instead of just bouncing across it.
The drug, called Daraprim, was acquired in August by Turing Pharmaceuticals,
a start-up run by a former hedge fund manager [Shkreli]. Turing immediately
raised the price to $750 a tablet from $13.50, bringing the annual cost of
treatment for some patients to hundreds of thousands of dollars.
I really like the subtle animation on all updated cells as he types in a value. By default excel just updates it instantly but that makes it hard to see the scope of your change by eye.
A quick scope through the options doesn't show what makes it do that. Anyone know?
Update to answer my question: my work PC had 'turn off all unnecessary animations (when possible)' ticked in the ease of access centre under the 'make your computer easie to use'. I don't know if this is by default. Unticking it made these animations occur but wow it creates a bit of lag in excel even on my workstation. This is probably some configuration thing on my work's SOE and why they have it disabled in the first place.
By default this animation is on, yes. It's one of those (many) subtle things that makes Excel so awesome for non-trivial spreadsheet use, despite it being derided in certain circles.
Microsoft totally wrecked font rendering in Windows 10 (W8 too?). Edge browser doesn't have ClearType either, which is braindead for a program you spend so much time reading in.
Edge 14 in the Anniversary Update actually brings back ClearType support for at least some text, so there's still a glimmer of hope that Microsoft will work to improve text rendering in Windows. Raymond Chen had an interesting post on how subpixel scrolling makes ClearType smoothing impossible: https://blogs.msdn.microsoft.com/oldnewthing/20150129-00/?p=...
I work as a scientific programmer and I used to have that reflex; even deriding others who couldn't or wouldn't automate such things for wasting time. To me, 'data' was just a blob, to be looked at as little as possible.
I've done a 180 on that. The other day I started work on making a plant database, by hand; from designing the schema (columns and sheets in Excel really - blasphemy!) to typing in the values from encyclopedias, wikipedia and books by hand (ok, the latin names I copy/paste). Yes I could just use one of the several large, well-known databases; or one of the hundreds of specific-purpose ones. But making this database has taught me so much already, things I never would have learnt if I'd spend that time on writing import scripts.
Nowadays I let my students/analysts first do extensive eda, which is usually lots of tedious work that seems a waste of time to the programmer instinct. But it's not.
That makes a lot of sense, and is why retyping code snippets from SO or wherever is more helpful than a straight copy-paste. There's something about manually typing information that makes you instantly more familiar with it.
When I was in college I noticed that when professors allowed you to make "cheat sheets" I rarely had to use said cheat sheet - the act of actually writing out the cheat sheet put the material in my head. It ended up being the most effective study method for me.
Data is mostly spatial so sometime with ArcGIS or QGIS, or R depending on what exactly it's about; R for statistical properties of data sets, scatter plots for variable relationships etc. Or Matlab for people with engineering backgrounds. I don't generally care what tools people use and everybody has their own experiences and background. Never need nosql databases (or RDBMS for that matter) for our type of work.
I'm experimenting with physical notebooks myself since a few weeks actually. No electronic note taking has ever really worked well for me, although I've been getting by for 15 years. I'm not sure I'll ever find a system I'll really like - it always feels that as long as I find a way that forces me to get intimately familiar with data sets (to the point where I'm re-doing or at least re-thinking the ways the data was made to begin with), the insights bubble up by themselves. In other words I've come to the (regrettable) conclusion that methods and tools don't matter that much, it's the elbow grease that does. (of course I'm not claiming that I could analyze 15gb of data spread over 50 tables with Notepad...)
The best part about paper notebooks is being able to page through them years later. I experimented briefly with a computational notebook (tiddlywiki) in grad school, since my work was primarily computational, and unfortunately those were lost when my laptop died.
It's entirely intentional. The point isn't just to transpose the numbers from one medium to another; it is to understand what the numbers mean. The best way to do that is the way Martin does it in that video. That's how anyone who does this professionally does it.
Absolutely. But the OPs comment is spot on. Do it manually when you start out and you'll quickly learn both the importance of the numbers you're typing in and common issues you can run into with either manually inputting data or in structuring the model (or that one time you fat fingered a number and blew out everything driven by it)
I assumed it's because he's teaching a lesson. Doing it by hand shows the video viewers how to go find these numbers in the public record, and along the way, what each number means and why it matters.
Just pulling them all in at once from an API would be like a "magic" step to most people who are trying to learn about the basics of analyzing a stock.
He explains that by using a line he can more easily move it "when the quarters move".
Using a border would require removing the border and adding a new one every three months. A line can be dragged around at will. This also makes it more useful for miscellaneous illustrative purposes.
Possibly because a border is part of a column so if you insert a new column adjacent to a border you have to remove the old one and redraw it which is pretty annoying because its tucked behind a few clicks and menus.
A line however floats relative to the columns so you don't have to remove/redraw it. At worst you might have to move it.
More about finger speed. The excel part isn't that impressive. It is impressive, just that Excel has a lot to offer in terms of formulas and matrix generations that I didn't see here.
I have to show this to emacsers, they're gonna feel jealous a bit.
In my line of work I often see tech vendors and consultants trying to plan infrastructure changes using Excel (or worse, tables in Word, but we won't go into that). The results are horrendous.
They start with a manual inventory of the configuration in question, type it ad-hoc into a spreadsheet, then go through making manual changes, notes and additions until it feels good. There's lots of highlighting, different coloured text, etc like layers of sedimentation. Formulas are only used for sums and counts in an ad-hoc way.
The end plan always contains mistakes and omissions that really hurt during deployment time.
The correct way requires discipline: immutable input sheets (from machine-generated config dumps), a parameter sheet, output tables (with consistent usage of formulas with "$" notation to lock rows and columns), and cross checks with conditional highlighting.
Unfortunately this is really hard for people without programming instincts/experience. Good job security I guess. But if you can do it, it's both faster and safer than the manual free-form method.
In my line of work I see an organisation regularly burning $50m in IT budget for developping a software that is completely flawed, horribly designed, not fit for purpose, though take years to develop and for that amount of efforts and money, could be much more efficiently done in Excel for 1/10000th of the cost!
This week I am trying to extract data from a data cube that was created for reporting. Problem, the cube was designed so that only one node can be retrieved at a time, and so if I need a bulk retrieval, which I need, it needs to be queried a billion times, sinking the server, timing out the queries, taking hours. The IT team is trying to figure out a way to run an overnight job so that it sinks the server during quiet hours. Basically these guys designed a vault where you can only add data but never retrieve it. They should be fired.
[edit] and before someone starts to think "yeah but this is big data", the underlying populated numbers in this cube would fit in a 5MB spreadsheet.
I am not sure that the audience of hacker news realises how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.
Excel is not great but one has to have a hard look at the alternative.
Absolutely agree- however, in many cases these types of companies can still be decent businesses. Being able to work with and adjust "moderately complex" Excel sheets requires a certain level of training and skill- often these startups can remove the requirement for expertise to perform a task, which can be worth quite a bit to a business.
The biggest feature of Excel is its flexibility. That moderately complicated spreadsheet can and does change all the time, to accomodate new requirements and optimize workflow. A startup (or the internal IT department) doing one-size-fits-all solution can't really do that.
This is probably its biggest flaw also — chances are you don't need all that flexibility within a scope of particular spreadsheet; and sometime you may prefer a solution that was specifically targetting this exact usecase.
Yeah, but a programming language would be even more flexible, and yet nobody says they don't need software because they have all the tools to implement its functionality themselves.
In fact, I worked on a project that largely aimed to replace a bunch of internal spreadsheets because giving groups a spreadsheet to maintain is a little like handing Phaeton the reins to Apollo's chariot.
Sure a programming language would be even more flexible, but it does not have an instant feedback loop like Excel does. Not to mention that the extra flexibility comes with the cost of dealing with all the arcane bullshit programming involves - toolchains, APIs, what have you. Excel is flexible enough to be useful by non-tech people and doesn't bring in so much cruft.
As a programmer I deeply appreciate the hate towards IT in companies and replacing Excel with complex internal apps. Those apps pretty much never capture the actual workflow (by virtue of requirements being provided by management instead of actual users), and even if they did, the workflow constantly changes. Excel spreadsheets may be cringeworthy sometimes, but they're used by people because you don't have to be a trained expert to make them work, and you don't have to submit requests to other departments to change something simple.
> As a programmer I deeply appreciate the hate towards IT in companies and replacing Excel with complex internal apps
You have to know the right point at which to make the transition. We had an Excel spreadsheet used for managing 10-digit sums of investments, held together with sticky tape, integrating SQL queries, XLL C++ addins (plural), Corba, creative use of the Excel solver and pivot tables in addition to custom data overrides (being Excel, without version control or sane error handling).
That point where operational risk exceeds the benefits of flexibility is a sign that you want to get some programmers on it - and we did, even at a cost of 10 programmer person-years or more in replacing that spreadsheet, and a horrendously slow and cumbersome process to get even trivial bugfixes or features added.
> I am not sure that the audience of hacker news realises how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.
Usually decisions like building a $50m application aren't made by devs but by clueless managers.
I do agree with the sentiment though, I see a lot of wheels reinvented everywhere I look. Recently I had to deal with a scheduler and a file remover/archive that were built in house. God know how many hours were wasted on this but the whole thing would work better as a batch script and a scheduled task.
I agree that there are often absurd requirement on the business side, just because they don't understand the tech and the problems a minor feature in their mind will cause down the line.
That's where project managers/change people are supposed to help, acting as an intermediary, understanding both the tech and the business. But in my experience they usually understand neither the tech nor the business and are adding more confusion, the whole thing works better without them.
But the state of corporate IT is sad to look at. I work in a large organisation where one of the executives was telling us proudly at a townhall that we have more developers than Facebook and Google together (though we are in the financial sector). Well, to say the least, we have little to show for it. Nothing works properly. Our core systems still run on programs written decades ago and we have to pull people from retirement if, after having exhausted all other possible options, we have to make a minor change. The network drives have a top speed of 1MB/s, and hang sometimes for minutes. As for the business applications. Any minor change results in ETA in months if not years and multi-millions quotes.
I remember discussing with some devs about an Excel VBA addin they were taking over from the business. They re-wrote most of the features in .net, which is very good, but somehow half of their application was still written in VBA, and I mean tons of VBA. I wondered why. They told me the library they were using only allowed to expose a function to Excel, but not to hook to or manipulate the UI. So they have written a VBA wrapper around all of their features that calls the .net excel functions.
There was a long silence after I showed them how they can get a handle to the COM interface of Excel in a single line of .net code using that library.
> Our core systems still run on programs written decades ago and we have to pull people from retirement if, after having exhausted all other possible options, we have to make a minor change. The network drives have a top speed of 1MB/s, and hang sometimes for minutes. As for the business applications. Any minor change results in ETA in months if not years and multi-millions quotes.
That's what happen when IT is treated as a cost center instead of a co-leader of innovation together with business.
Bad hires, no real objectives, no liberty to innovate: do that constantly for decades and you end up in the situation you describe.
You can't really blame the IT guys that are thrown in that mess.
It's almost always a top management fault.
> There was a long silence after I showed them how they can get a handle to the COM interface of Excel in a single line of .net code using that library.
Looks like they are lacking a good tech lead, why not apply yourself? :)
A lot of large corporate IT is outsourced to the likes of HP. It's not unknown for them to put in the least qualified folks they think they can get away with - which can often mean in the case of unusual infrastructure, libraries or languages that they sent someone on a basic training course the week before starting. This leads to the quality you might expect.
The two most "interesting" contracts of my career were in financial. One was trying to reverse 25+ years of bad decisions and no end of duplicated, wrong, and unsanitised garbage in the live DB along with some dependency on CICS that fortunately I was far away from. The other will remain undiscussed. :)
That explains it. I'm working in finance at the moment too, it's where you find the worst developers/management/products.
I'm betting the current situation there is caused by some combination of technical debt and the dead sea effect (also coined by spolsky I believe). Once things get to a certain point competent people leave and by attrition you're left with the ones unemployable elsewhere.
>> though we are in the financial sector
> That explains it. I'm working in finance at the moment too, it's where you find the worst developers/management/products.
I second that opinion. There's a lack of depth of knowledge on the IT/Coding side, and people think it's just another little cog that they need to get their deals done.
So you get things like
- Complex derivatives calculated on Excel. You wonder why people make millions on this? It's because you never really know what the heck you're calculating.
- Missed deadlines that should be cron jobs. I remember a guy coming into the office, down a good few hundred grand because he'd forgotten to exercise options that had a dividend on them. I mean that's just stupid, if you had a proper system it would tell you, and the calendar would be synced with Bloomberg.
- Ridiculous budgets. A friend at a major bank is on a $27M project that is essentially a calculator that adds two numbers together. Other people are attempting to break into HFT with zero competent coders, no network other than what can be leased cheaply, and no systems engineers.
- Balls of spaghetti of unimaginable proportions. One c++ guy at a big bank told me he takes 7 hours to compile. He's writing execution code, which you'd think would be relatively snappy.
>- Balls of spaghetti of unimaginable proportions. One c++ guy at a big bank told me he takes 7 hours to compile. He's writing execution code, which you'd think would be relatively snappy.
How long should it take? The last time I did anything of note in c++ was on a computer of 2000's vintage and 7 hour builds were pretty common.
If you're taking seven hours to build, you have too few computers per programmer. Compiling C++ parallelizes well, and computers are dirt cheap compared to the cost of employing people.
> how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.
Large non-tech hyper-bureaucratic common-sense-free companies are especially hostile toward good IT and dev so good people just don't work there ;)
[and even if they did, they still couldn't get anything done]
Somehow IT manages to be an order of magnitude more bureaucratic than the rest of the company. Perhaps a case of IT people trying to be more catholic than the pope in a bureaucratic company!
> This week I am trying to extract data from a data cube that was created for reporting. Problem, the cube was designed so that only one node can be retrieved at a time, and so if I need a bulk retrieval, which I need, ...
Sorta OT, but this is one of the more sci-fi sentences one could say that actually makes sense.
Spreadsheets put together two aspects:
- data (raw and calculated)
- presentation
The original mistake is that both were made interdependent. Excel tries to fix that the hard way by introducing various reference modes, a lot of "do what I mean" magic and even nested spreadsheets/databases (aka "tables").
But it still leads to scary bugs, as the video shows: if you do things the wrong way the numbers you derive from your data are wrong and you don't even see it if you don't pay attention.
While watching the video I was wondering if all those features were brilliant or insane. The answer to me is that it's insanity.
If nothing else, HTML has shown for a long time already how one can - one should - make the content (data, programming) separate from the presentation. This idea has to be back-ported to spreadsheets now.
The first half of the video is really just keyboard and drag'n'drop tips, while the second half is really just brutal hardcore programming: "match", "if", "lookup", references, indirections, "debug mode", ...
Acknowledge that spreadsheet documents really are programs. Make a spreadsheet DSL then apply ideas from Lighttable to make it as "visual" and "easy" as possible.
Worse, try putting all the user stories and bug reports into Jira or similar tools only to have managers request to duplicate it and have email ping-pong about project status with Excel sheets as attachments.
But the problem you point to seems more related with the process than with the technology, right?
In any case, there is this Excel alternative, Resolver One, which has what I believe it is a killer feature: you can expose the Excel sheet as a web server. (With Excel, you would need to have a Sharepoint server for that, I believe).
That way any user can iterate a lot on an initial excel sheet but then their group can just work with the web version as an "immutable structure" sheet, and if a change needs to be done, you don't need to go and contact someone with appropriate permissions to upload a new version to the server, etc.
> They start with a manual inventory of the configuration in question, type it ad-hoc into a spreadsheet, then go through making manual changes, notes and additions until it feels good. There's lots of highlighting, different coloured text, etc like layers of sedimentation. Formulas are only used for sums and counts in an ad-hoc way.
Oh man that hits home hard. And all the sums and counts are in random cells "beside" the tables right?
First, I learned a lot from this. But, here's some light criticism:
1. Joel saying "I didn't understand that question" and then moving on might have been succinct and practical, but it was just not a good reflection of him.
2. He acts like R1C1 mode is the only way handle relative references for the first 13 minutes. One of the first things I learned in Excel was $ to pin a reference to row or column in what he calls "baby mode". I think it's not babyish to use $ which is more succinct; you can edit the formula and see the calculated value right away. It seemed like he waited a long time to talk about that.
3. "Almost none of which you can do in Google spreadsheets" at 18:15. Sounds so pro-Microsoft, right? Yet, if you look, he's obviously using OS X, which is surprising to me, because MS Office has historically sucked on OS X compared to its Windows counterpart, and it's been incomplete: https://9to5mac.com/2016/01/21/windows-mac-ipad-microsoft-of... even though, yes, it's a lot better than it used to be. Also, Google docs is free.
He's presenting this to his company. I don't think this video was originally intended for mass consumption, but I am glad it was made available.
So this is the CEO presenting to a group of people who know him and his presentation style, I think at that point much of the stuff you're complaining about can be thought of as humor or house style, especially when you consider that Joel worked at Microsoft on Excel. Context matters.
As someone who watched the live stream of the original presentation from the Stack Overflow lunchroom I can confirm that the video is missing some context. Definitely a bit of house style and a throw back to some old school memes, but overall an informative and humorous training session that I am glad was made public.
Joel usually gives great presentations, and recently he even started personally editing our internal company update videos (and complaining when we don't show appreciation by up voting them :-) because the first one was a bit dry.
> 1. Joel saying "I didn't understand that question" and then moving on might have been succinct and practical, but it was just not a good reflection of him.
He's presenting in the style of You Suck at Photoshop, a series of Photoshop videos in the same style.
At the beginning he says something like "this is basic to intermediate, but for you this is going to be stupid hard", which is from the first (?) YSAP, "Distort, Warp, & Layer Effects" [1].
Reading your single comment (and watching the linked clip) was very much worth my time! I never before realized that you can address Excel cells via a symbolic name instead of row/column-wise. That is very cool and probably very basic. Now I'm going to watch the rest of Joel's video!
Regarding point 1, blowing off that question was an inside joke. I get why it comes off as dismissive and rude without context - it was originally an internal presentation - but the asker was Michael Pryor. He's Joel's co-founder at FogCreek and the CEO of Trello. It was playful banter between very senior peers.
As mentioned elsewhere, he was a Program Manager for Excel. His story ("My First BillG Review") about spearheading the effort to bring VBA into Excel is a great read:
It's missing everywhere. Sheets, libreoffice, office for Mac, anything... Ok, office for Mac does have tables, but I keep running into issues when referencing them - some values just end up as error until you change the source (even to the same value)
Google Sheets is still painful in many areas. Cell/chart/pivot formatting is a big one.then there are little things like the pivot table interface. Sheets has come a long way but when I need to be efficient and effective, I roll up my sleeves and dive into Excel.
Agreed on the R1C1 mode. Its very helpful to explain "this is what is actually going on behind the curtain", which is likely why he stresses it so much when speaking with developers. However, its really unnecessary in the 'real world'.
I am curious about the reason for the repeated potshots at Google Sheets. The way he talks about it he's playing the taunts for laughs, but usually there's an interesting story or grain of truth behind something like that.
I do notice the big google watermark in the top left of the video so at first I thought maybe he was doing this presentation for some google engineers and playfully poking fun, but the description says this is a presentation he gave for the benefit of his companies Fog Creek and Trello. So I don't know.
You know, how google walked into the whole GIS and Geography business and clicked its heels together once - closing the shop. I guess the very same thing is going on here- wounded pride.
We fought and worked to make this happen- you can not give it away for free.
I think the gp means that Google came in gis, looked around and decided 'nope, nothing for us here', and left? I mean, there's google maps, but that's it - while widely used, not exactly special.
Office for Mac was certainly a third-class citizen for a long time. Thankfully its improved quite a lot Office 2016 for Mac, and they are somewhat comparable now. That is until you have a MSSQL connection in your Excel sheet... it was surprising to find people that do that.
Re 3: he does say (at some point in the latter half of the video, after the OS X beach ball spins for several seconds when he tries to do something very simple) that Excel for OS X is a lot less stable than for Windows.
I think he's wrong on the R1C1 mode. At least in the latest version of excel on mac, you can observe this behavior:
1) In cell A, reference cell B
2) Cut (not copy) cell C and paste it over cell B
3) Observe cell A throwing a #REF! error
To be concrete, type 10 in cell A1, then type `=A1` into cell A2. A2 now shows 10. Now click cell B1, cmd-x to cut, then move the cursor to A1, cmd-v to paste. Now cell A1 is empty and cell A2 shows #REF!
This will not change if you use the R1C1 mode.
So internally it's neither R1C1 nor is it A1. Internally it's a reference to the cell object directly.
I just tried this. In "baby mode" it does what you say. In R1C1 mode, A2 becomes 0 when the blank cell B1 is cut/pasted to A1. So in my experience (Windows 10, Excel 2016), R1C1 references the cell, not the object, whereas baby mode references the object.
Yes. Word and Excel were ported to Mac OS before Windows even existed to be ported to, and PowerPoint was a Mac-exclusive product created by another company.
Is there a standardized excel knowledge test and or certification? As a senior analyst excel expertise is requisite, but is very hard to interview for...I had to make an excel test just to empirically rate our analyst candidates. I know there are the excel competitions (http://www.modeloff.com/) and great resources (http://www.chandoo.com/wp), but no rating system for knowledge (even basic) of excel. Think the big investment banks would be interested in "testing" their analysts empirically before hiring them?
everyone employed to a relevant position at our company takes a pretty in depth excel assessment so we know what they need taught on Day 1.
Named ranges/tables is always top of the list!
edit: business schools usually make you take an excel course/exam your first year. Its always awful, using some absurdly priced flash-based webapp from 2003 that has such strict validation on answers/inputs, you often struggle just to input your answer because you solved it in a different manner than they expect. So, the IBs likely think you know Excel before getting in the door (which is not always the case).
Investment Banks do not really expect juniors to have advanced excel skills before they join. In fact they normally do not expect them to know anything about their job, outside of having a personal interest and curiosity about finance and be eager to learn.
Now in practice it doesn't always work like that but I still agree with that philosophy. We should be hiring smart people, not people with knowledge. Smart people can always acquire or build the knowledge they need quickly. Knowledgeable people can't really get smarter.
From what I've seen at my work most of the analysts tend to use specialized BI tools rather than excel.
Excel is mostly used for data transformations. Pretty much every tool can "speak" excel so typical work flow is to extract data using tool A dump it into excel. Save it and Upload from excel into tool B.
This is for business analytics rather than Finance/banking. Is excel really that common in that world?
I work at an industrial plant. Excel is used a lot here too. But not for technical analytic work. Nothing you'd need to pass an exam to know how to do.
The Analysts here tend to use programs like Cognos, SAP Business Objects etc.
Interestingly, as part of my college (pre-University, as I'm UK, and I'm unsure what the American equivalent is (think the gap between high-school and University)) – we had to do a module called ECDL (European Computer Driving License) which was basically the Microsoft Office suite, this would apparently be a boon for employers wanting to guarantee a basic level of competence... and I still don't know Excel (but passed that course.)
Simple, timed exercises are highly effective. I (and almost everyone I know) has one built that they use. I can look through their work and know (1) if they are really an export, and (2) if they're even trainable.
Happy to share noah (dot) barr (at) GOOG's email service
There are lots of things he does that are still rookie mistakes! Like index/match without doing a strict match (microsoft: when are you going to add a shorter syntax for that??).
Then he probably does that to make it visual but he should be using his mouse a lot less. F2, CTRL Enter on a range to apply the first formula without applying the formatting.
Also two data tables one above the other. As one expands it will hit into the other. That's where excel lacks a feature that apple introduced in numbers: not using a unique grid but a table being its own grid, placed as a shape on a sheet. That solves lots of problems.
Range names are not a good solution with experience. You get name clashes when merging spreadsheets, ambiguous duplicate names when duplicating tabs. For formula auditing F2 is your friend. And there are some third party add-ins to go to a reference within a formula and come back with keyboard shortcuts.
And no demo of excel is complete without showing the power of array formulas. In particular SUM(Col1 * Col2 * (Filter1=Col3) * (ABS(Col4)<4)) to do a sophisticated conditional sumproduct.
Well, that's the case for many developers, they are often not the user of their own software.
The most spectacular exception is Visual Studio, written by developers for developers and one can tell. They re-write it from scratch regularly and add major features that really make sense, catering for both basic and very advanced users. Visual Studio is by far the best software that Microsoft makes.
Office on the other hand is a software written by developers who are trying to imagine what a user does with it. And it shows. No major new feature since Excel 2007, and particularly very bad at certain scenarios that are really core usage of excel (linking a powerpoint deck to a spreadsheet, formula auditing (visual studio had "go to reference" forever), a VBA editor that hasn't changed since 1999, etc.
If the dog doesn't die it means the food is good enough!
[edit] VBA is a fantastic tool, but it's frozen in time. The worst is that Microsoft did attempt to fix it, it was called VSTA, and was basically a light version of visual studio embedded in office like the VBA editor, to be able to script office in .net. That would have been a great feature. And they shipped a version, if I remember the early version of visio had VSTA. But for some reason they killed it. Let's not change the dog's habits!
There are two major improvements to Excel that came in 2010 and 2013.
These are Power Pivot and Power Query (Power Query is now backported to 2010 as well).
Power Pivot straight up is SQL Server Analysis Services. It's a heavily optimized in-memory columnstore database engine that is exposed natively through pivot tables and charts in Excel. The fun demo is loading up a table of several hundreds of millions of rows in Power Pivot and interacting with it in a pivot table in Excel. That was back when I had 8GB of RAM.
Power Query is a data shaping and transformation tool, with an expression language largely based on F# (though with an entirely different standard library and some major functionality changes). The single best feature of Power Query, though, is that it provides an audit trail of all steps applied to a dataset. This completely bypasses one of the major problems in Excel, that intermediate steps are impossible to recreate accurately.
Full disclosure I am a consultant with a Microsoft partner and spend a lot of time with these tools.
I appreciate that and I am sure it may be useful to people who do data analysis against a SQL server, but I see very few of those around me. Most use excel to calculate something and for the analysis behind a powerpoint deck. None of that is ever a simple sum or average of a column. If it does involve data, it usually involves non trivial calculations that are not really suited for a database. And if it involves things like aggregating P&L or any financials, unlikely an IT dept in a large organisation will leave that in a SQL server available for any user to mess with.
I don't know what proportion of users Microsoft thinks it caters to with these features but I haven't met any in my 10 years in banking.
Users who do vlookups, use VBA, who need to audit a formula, who need to link a powerpoint deck to excel, etc, that I see every day. And to those, no, no new feature since 2007.
And I see people every day using exactly these pieces of functionality to do nontrivial summarization of data, a thriving blog and forum ecosystem, and Microsoft knocking down my door because their own sales teams can't keep up with the customers wanting to use these features and the Power BI ecosystem.
We all live in bubbles. I recognize mine as the above, but I don't deny that yours exists.
While Visual Studio is a testament to understanding your users, it's not great software. Randomly slow downs (even with Visual Assist turned off) and hanging every time you open a moderate (~50 project) solution.
I am sure we can find flaws. In fact the VS2015 intellisense is a major step back. But compared to the VBA editor which litterally hasn't changed since the 90s, I mean litterally not a single new feature in 20 years! Like if that was a feature no one was using!
There are cases where it is useful. Like I want to retrieve the FX rate for a given day but my time series might be missing days, so finding the nearest result is practical.
Also the default assumes that the values are in ascending order, which is computationally efficient as it can do a binary search.
But very dangerous in the most common scenario.
What would be even better is to be able to tell Excel to create a hash table / dictionary in memory from a table so that it can be queried very efficiently.
But as I posted everywhere on this page, Microsoft doesn't seem to be minded to add new features. Changing the color scheme between versions, making minor improvements, keeping bugs as they are, incrementing the version and shipping it!
It's great to categorize number ranges, like people's age for example.
You have a line with each person and their age.
In you list you have age groups, looking like
Age Age Group
...
19 10-19
29 20-29
39 30-39
49 40-49
...
Then you use vlookup to find the group that matches the age, and the non-strict will match 12 to 19, 33 to 39 and so on, returning the age group for that value.
If you have large sheets, you might also care that a strict match necessarily involves a linear search, O(N), while a "non-strict" match uses a binary search, O(log N).
Strict search is slower, in other words.
You just need to know what you want, really. (Even if you want a strict match, it might be faster to sort once, then do non-strict match, then compare for identity).
I'm a developer on XL (at Microsoft), only half way through the video and have learnt a thing or two. Out of academic curiosity I sometimes ask old-timers if they ever worked with Joel. So far nobody recognizes his name, though there is next to none left in Excel who were XL in early 90s or before.
I think I semi-subconsciously left that ambiguous on purpose. A fair amount of people have certainly heard of him, but when I asked a couple of old-timers in the recent past they didn't seem to recognise the name.
They did have the code owners' name in nice formatted comments but they don't do that anymore, at least not in the Excel codebase. There are plenty of artifacts with the code owners names left behind (many having since moved on from Excel and Microsoft).
Mr Spolsky was a program manager, so I doubt he wrote much if any code that made it into the Excel binaries, but I'm interested to know if he did.
His name doesn't appear in either the Excel 97 flight simulator monolith credits, or the Excel 2000 "dev hunter" driving game, so he may have joined the Excel team after that, or they may not have been exhaustive lists.
They're still very cool ways to put developer credits into enterprise software :-)
Version control systems are better these days, so you don't need to. Even us C programmers have stopped putting names, dates, revisions numbers and other gunk in the comments.
As a side note, current versions of Excel still opens and even can save Excel 5.0 files by default but no longer ships with VBACV10.DLL that converts the VBA code. It was easy to find bugs in this code when I was fuzzing using HxD years ago.
I'm sorry, you're probably a nice person, kind to animals and children but still I kind of, well...
Excel, just say NO. Friends don't let friends use excel. Ever.
Microsoft has no interest in fixing the bugs, the bugs are extremely costly. People regularly misallocate resources based on excel bugs. These screw ups happen at vast scale with hugs sums of money and worse.
Here's a couple of links I didn't read closely. Any web search will find so very many it's silly.
Excel is super useful for the range of tasks where speed and transparency to business execs is more important than flexibility or maintenance.
Its great to rattle something off in a couple of hours that you can email to your boss to tinker with
It's absolute hell flicking through tabs trying to find a bug or trawling through stack overflow looking at grim VBA contortions which have one-liner equivalents in python.
The problem is if enough people have been working on a sheet for long enough it becomes very difficult to replicate your work. this is particularly true for projects involving data processing and if they have poor version control practices.
Paste values isn't almost always what I want. When copying formulas around I usually want the actual formulas which have been judiciously constructed with fixed row and column references where needed. The default paste is ideal for people doing actual calculation in Excel.
I've often wanted some references in the formula to shift when I paste, while having others be fixed. This can be accomplished by putting the fixed references as string argument to the INDIRECT function (ex `indirect("F7")`).
Stop using vlookup. Use index and match, either through named references as Joel does it or just manually even. It's more powerful, faster and less prone to failure because vlookup relies on an assumed order of sorting but most people won't know this and will wonder why it's cocking up.
I forget the syntax but you can also write cell references that shouldn't change as $A$5 ; that fixes both row and column. Just put a $ in front of the dimension fixed.
Definitely don't want to use INDIRECT in large sheets, as it is volatile (i.e. always dirty).
(Just to clarify: Excel maintains a DAG (directed acyclic graph) of dependencies, and upon changes marks cells dirty, then only recalculates what is required when F9 is hit. Cells containing volatile functions, being always dirty, always trigger a recalc of all their children.)
Does anybody else feel that the vast majority of these things are either available in Google sheets or worth just doing in R? My last consulting project was on a VaR model built in VBA/SQL with a bunch of Excel sprinkled about and it was horrendous. Not Excel, VBA or SQL's fault, but it felt like a more thoughtful architect would have used better tools.
Check Power Pivot if you just want an Excel interface to lots of data. I posted elsewhere in this thread about it, but the fun demo is to load up a dataset with a largest table of several hundred million rows. This is still quite snappy on a typical desktop machine.
I went to Google Sheets and tried it and it was a pretty bad experience compared to what Joel shows in the video. Excel Online worked very well though.
I think the most interesting thing in this video was the explanation of R1C1. I've always thought of the apparent automatic changing of references as almost magic. Now I know.
For me, the most useful things he talked about was, in order; giving names to cells and columns, use of INDEX and MATCH, leaving space around tables, defining and using tables, pivot-tables, copying formatting with the paintbrush, goal-seek, and finally, control-semicolon to insert today's date.
The various forms of paste special, I knew about from before, and I also knew about the dragging to fill in values stuff.
All in all, a very good video with a lot of things I didn't know.
This is not a judgment on you, but I've seen this echoed in a couple places in this thread and yours is a top-level comment that starts with it.
I really grokked the concept illustrated with R1C1 notation within a day or two of being exposed to $-pinning with A1 notation, and the concept of a relative offset was my mental model in Excel for a long time before I even learned about R1C1.
Yes, R1C1 notation is helpful because you literally type in an offset if you want it, but it seems pretty apparent in playing with Excel that the automatic reference rewriting is based on the relative offset of one cell compared to another. Seeing it rewrite a range as you drag a formula around seems to illustrate this concept very clearly.
On the other hand, I came from a non-technical background and came up as an analyst in a sales department. The person I learned Excel from didn't know about pivot tables, so I didn't learn about pivot tables for some time. What I did learn was how to make my own (much less flexible) pivot tables by hand very quickly. I got to the point that I put off learning pivot tables better once I was introduced to them, because they were an inefficiency for me. I eventually learned better.
I thought it was instructive to see that in A1 mode the cell formula magically changed as it was copied, while in R1C1 mode it did not; it was good circumstantial evidence that R1C1 is what Excel uses internally. But then I tried an experiment, deleting a column that was referenced by a formula - the formula was replaced with =#REF! indicating that there's still magical changes being applied.
I should probably have phrased what I said a bit differently. I have not had trouble understanding relative offset and how to use it. What I've always wondered about and finally got an answer to in this video is how the implementation works so that Excel is able to preserve the offsets even when pasting a cell in a random location into a spreadsheet :)
Ah gotcha. Yeah, for that I just had an implicit mental model of R1C1 without realizing it was physically implemented.
I interpreted you (wrongly it seems) as saying that the relative offsets were confusing and I responded to that. You were curious about the implementation instead.
I didn't care about the implementation when I was learning Excel, and simply assumed a mechanism like R1C1 without much interest in it.
That's a pretty good demonstration of some of the strengths of the ribbon interface.
After watching this, I went to Google Docs to try to reproduce some of this and it felt very clunky. The first thing I realized I didn't have is a styles manager but I was able to install an add-on. For other parts (like creating tables) I was stuck.
I wonder if a similar demo in Libre Office would be as impressive?
Edit: I just tried Excel Online and it worked surprisingly well. Last time I tried it, Google Sheets felt way better but now I'm not so sure.
I had a chuckle at Joey calling @ "a bagel". I guess that's a New York thing.
Over here we call them "elephant-trunk-A" (interestingly elephant symbols are quite common here in e.g. royal/government coats of arms and the "Order of the Elephant" being the highest chivalric order).
I've been using Linux on the desktop for years and have never missed Microsoft Office (except for Project, a little bit). Now seeing R1C1 notation is making me want Excel... Any recommendations for FOSS spreadsheet software that supports R1C1 notation or that is generally better than LibreOffice Calc?
The most interesting spreadsheet is http://siag.nu/siag/, but it's obviously not what you're looking for.
Honestly? Excel is a premier application, that microsoft spends extreme amounts of effort making great. There isn't a FOSS spreadsheet program that really comes close. It's not that there can't be, it's that the excel line is moving faster forward than current FOSS spreadsheet projects.
To be honest I'd recommend an Office 365 subscription and use Excel in the browser, if you are unwilling to get a mac/windows installation with a desktop app.
Perhaps its a 'purest' mentality, but if you're going to learn how to use a spreadsheet app, may as well learn the one that is most well known and widely used. As the video shows, Google Apps, Numbers, and the rest of them really are missing core features.
Joel didn't even get into VBA scripting - thats some advanced stuff that once you realize you need it, but you're stuck in Google Apps...... well, maybe this crowd is smart enough just to make a web app to solve the issue haha! Myself? I stick to Excel.
The point is that it is much more intuitively understandable what happens when you fill whole columns with that formula.
In R1C1 notation it's obvious because the formula doesn't change. In normal notation the exact same thing happens under the hood, but the cell references you're seeing change.
I recently had to use a spreadsheet for a first time in a few years, and LO Calc seemed to be dramatically better than OOo Calc back when I last used it.
Thanks - I used Gnumeric on a netbook for a long time. It was indeed a good choice at a time when I needed something very lightweight. I do recall it being quite limited, however... I'd be pretty surprised if it had more of the features Joel Sposky was showcasing than Open/LibreOffice Calc.
It's been a year or two since I've used it, but the last time I checked, it hasn't improved all that much. It was generally rather slow and limited.
This is really a shame, because of Gnumeric's history. It was (along with Gimp) one of the very first GTK applications, and present at the beginning of the Gnome desktop suite.
One of my many assignments at my job is to work with excel on a weekly basis exporting data dumps from MYSQL to excel ranging from 50k to 100k rows.
At first it was a daunting task but once I learned how to build some solid templates then all I had to do was to dump the data set into a tab and let the formulas do the work, everything was already formatted so I could just start validating the data then email it to the sales team for further analysis.
If you take the time to analyze your data sets and get to know how to handle each type of data then you can build some really solid template in a fairly short amount of time.
I try to avoid using excel as much as possible but this damn thing is so deeply rooted into the "business analyst" world that you can't really escape it so this is why I told myself that I would be better off mastering it and build solid reporting templates instead of trying to change their mind about using alternative software.
On a side note we started using WebFocus... that's another monster to tame...
Once you know what to do, yes. But for scenario analysis and prototyping, anything with a code/run cycle is just painful. I've tried switching many times.
Plus, Excel has a 'gui' (of sorts) build in. (Shiny is not a substitute for that - here too, shiny is fine for productizing once you know what you want to do, but not for quick one offs)
> for scenario analysis and prototyping, anything with a code/run cycle is just painful
For scenario analysis and prototyping, you'd use RStudio as GUI and the command line for REPL. But personally, I find Jupyter notebooks with Pandas even better.
RStudio is not a gui, it's an ide. If you change a parameter somewhere you still have to re-run the code and hunt for whatever it is you're calibrating. In Excel, you just change values and you see immediately what has changed. In Excel, you can trivially show subsets (with a dropdown that show you all values in a range), or checkboxes for booleans, do conditional formatting to highlight special values (< 0, within ranges etc). All of which is a pita with R and with anything that has a code/view report cycle, basically.
This is the first time in my computer science career that I've heard Excel (and spreadsheets in general) called a functional programming language! What an excellent, excellent point!
The lambda calculus is incredibly value-oriented. Consider that it is impossible to write a formula that injects another formula into a blank cell: all values flow from other values. To overcome this, you need to write a side-effecting Excel macro. This is why some people have integrated Python into Excel. It's a shame that the CLR (and more functional languages like Clojure) is not better integrated into Excel.
Story time (MS rant):
When Microsoft pulled Access (the best relational DB UI) to avoid competition with SQL Server, stopped adding features to IIS (still no wildcard domains!) and did not fix the botched Visual Studio deployment story (publish 10MB binaries for 1-line changes when you control the web server and the OS?), they ceded the "IDE" wars. Excel is sort of a business IDE, except sharing spreadsheets is a nightmare, which is what Google Sheets is really good at. I reluctantly started switching to other "IDEs" like IntelliJ (still worse than VS) and the Google Apps suite. However, SQL Server Management Studio is still the best SQL writing interface for me.
And despite this, and despite the mind boggling amount of money at stake, no one seems to making moves to actually do something about it. It's like the finance industry has collectively decided that a few billion in losses each year is an acceptable price to pay to get to keep using Excel.
I’m in the industry, everyone here uses spreadsheets for everything. They only have us working on other stuff now because we’re reaching memory limits and row limits for calculations.
But primarily we have a few factors working in excels favor.
1. Inertia.
2. Difficulty in switching to a more advanced solution for non-technical folks.
3. Speed. If you can get a MVP up and running before another firm you have an advantage. Even if it’s in Excel and held together with spit and string.
Just to expand on that - many banks employ Excel basically as a frontend for their own analytics/database (using own functions with addins). A popular trick is to allow functions to return handles that stand in for objects.
Using that, you can run not only Monte Carlo simulations and PDE solvers in a pricing sheet, but even run a whole trading book including risk in Excel.
Of course, you can then also overwrite certain cells to pad your PnL, as a few Credit Suisse traders did in 2007/2008 to hide their losses in mortgage backed securities. They basically overwrote bond prices to some fictitious value, overstating the PnL of their position by $540m or more...
like he said the rounding made 975000 an impossible result.
if you have rounding or some kind of non-continuous function the right feature to use is actually Solver (which is an included add in you have to enable in Excel options) which provides you with an fmin function (or several) as opposed to goal seek's fzero.
To give a serious answer I read somewhere: unicycling. Very few people can actually ride one, so if you can ride a regular bicycle you are probably above average at unicycling.
I've bought it because of everyone posting about how awesome it is and how their six year old sister is good at it and so on.
I've read all kinds of tips and tricks guides.
I still haven't found out how coming back from a fall off the platform works. I can hammer the key that the Nintendo leaflet and all the online guides say and nothing happens. Probably there is some other prerequisite to it. Or whatever.
Personally I don't want to get good at Excel. Its a horrible piece of software for the majority of the tasks it is used for. It would probably make a decent spreadsheet but I hardly ever see it used as that.
No Excel with vim bindings or model editing. Excel has its own set of arcane keybindings that make using it really fast. If you are a heavy user, you can print out a reference sheet just like you did with vim when you started out.
Org-mode is not an alternative, there is zero overlap in functionality between it and Excel.
I can't agree with'always name a cell/range' and dont put numbers. This gets very confusing for a second person working on the workbook. Typically sheets get large and they get all these names building up over time. You end up with no idea with what someone is referring to.
Also for 'paste special' I dint notice him mention Crt+Alt+V which is really shortcut vs using menu dropdown.
I disagree with your disagreement. The principle of naming your cells/ranges is basically the exact same principle as "don't put magic numbers in code, use named constants".
I mostly agree with you. Where the names are clear and obvious, naming is very helpful.
However, when it is no longer obvious what the name refers to, there is no more benefit than the original cell reference. In fact it is even worse because it's not immediately apparent where the cell is now the reference isn't showing. I often run into this problem with very large spreadsheets where every cell is named.
Our workplace has a strict "everything has to be named" in Excel and it really makes life much more difficult when working with extremely large calculations. Often I will want to know what a certain formula is doing. I will look at the formula. There are about 6 named cells that I have no idea what they refer to. I then have to use the drop down list (which includes about 500 names) to find the cell it refers to. Looking through this list is a lot slower than just reading the cell reference and looking at that cell. It's also quite easy to pick the wrong name in a list of 500, because there is likely to be a lot of similarly named cells.
I've thought that before but every time I've named important ranges I've come to regret it.
Someone at some time will decide to nuke an entire worksheet and paste it back in without the named ranges, and then everything will look the same but various lookups and macros will break. (And they will somehow be able to do this even though you've password-protected everything in sight, too. Not sure how that happened.)
Or someone will insert a row somewhere and the named ranges will be missing the last row of the data and any named ranges beneath them won't shift down. That kind of thing.
Maybe I'm just missing some named range best practices.
Yeah I'm familiar. It can be useful. I just find it easier to move around workbooks/sheets via sheet/cell reference, especially with multiple users. I used to do the naming thing but I found it less efficient.
talking about the importance of variable input cells in minute 43 smh. working in finance this is not very impressive. also excel on a mac is inferior.
I think only about 1% of excel users actually build models with it so the need for advanced skills in using the program just aren't necessary, such as for storing data and maybe making pivot tables...
Being an Excel jockey was "cool" in the 90s and 00s but all the cool number crunching kids are much more likely to be using something like Jupyter Notebook, R or homebrew command like stuff these days.
One thing I find interesting is that on the Mac OS X Excel, Cmd+T is the toggle for the relative/absolute cell references, but in Google Sheets it's F4, just like Windows Excel.
Good content, but could he please stop calling listener a dummy baby!
I understand that he is presenting to his employees or to some company, but it's still disrespectable. If he is not my greatest buddy - don't call me a dummy baby.
Dunno why you're getting hammered, this is part of a good Excel workflow. Do what you can using the nice GUI, if you have bulk changes to make that are easier to script than find-and-replace then dump it to CSV, do the grunt work with some command line tools or a script, and re-import it.
While this is a fine workflow if you're more familiar with command line tools than Excel, bulk changes are usually easily doable in Excel with judicious use of cell formulas and copy-pasting of values.
On a lark, I did a couple dozen Euler problems in Excel a couple years ago and found it was actually quicker and easier than doing it in a 'real' programming language; at least part of that is that loops and recursions are easy to visualize and debug in Excel because you can just break intermediate calculations into cells.
Not to mention the famous mangling of gene names that look like dates. Yes, I know scientists shouldn't be using Excel -- I agree. But I'm a computational biologist. Experimentalists know how to use exactly three programs 1) Microsoft Word 2) Microsoft Excel 3) Microsoft Powerpoint.
I find myself working with four letter codes to identify wards and facilities at my work, inevitably I have to compare my SQL output to someone's spreadsheet. No excel, I didn't mean March 02, just give me MAR2 like I asked. Drug codes that start with a '.' are another headache, they will be auto formatted as a decimal number. Worse is when they want to put info from a spreadsheet in to the database, no one understands the need for consistent field formatting :'(
I feel your pain. We have a lot of ID values with leading zeroes, which Excel loves to strip, and data with numbers in parentheses, which Excel's CSV loader thinks should be negative numbers (this is apparently common in book-keeping).
I keep getting sent spreadsheets which were made from CSV data by people who didn't know how to set the columns to text - to be fair, if the file extension is .csv then Excel won't even let them set the column formats - and having this latter category turn up as -ve numbers not only looks weird but ruins the sorting. Even 1, 11, 2 is better than sorting "backwards".
I haven't tried pulling Excel -> SQL with PowerQuery yet, only really dabbled in presenting data the other way. Do you know if this works easily with a document that utilises a lot of macros and VBA? Or are we still talking flat CSV.
What are you talking, Excel with VBA is importing/consuming the data, or it is the datasource for something else?
I assume Excel with VBA is importing/consuming the data in which case Power Query / Power Pivot would likely solve most of your problems. Refactoring a large complicated existing spreadsheet to use PQ rather than your existing peocess, hard to say how tough that would be.
i was reading a paper about how Excel gets some standard statistical tests wrong too - the US Stats Association i think did a compare/contrast with a range of packages. some of these bugs have been there since 2002 or so. i lived in Excel for many years but wasn't a power user - i got to like it but sadly, it got used for everything even when it really, really shouldn't have.
Often, you can strip the BOM (which isn't supposed to be there to begin with), convert from excel's proprietary character set to UTF-8, and translate the line endings and things may work - if it hasn't decided to randomly change things that weren't dates into dates, strip leading 0's from your zip codes, reformat things that you didn't want reformatted, and otherwise mangle your data. (Nevermind that Excel can't handle dates properly anyway.)
What's worse is that many people play with excel and use things like coloring, fonts, and visual layout to distinguish things, and/or combine multiple unrelated chunks into one sheet so that they won't have to switch tabs (which of course makes for a scrambled mess of data). I try to suppress the memories of it taking 36 tries to get a usable CSV from business people (after having given clear instructions in advance and doing postprocessing afterward to clean it up).
Excel should never be used for working with data. But it is, so that's job security if you can clean up the messes that it makes.
> So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places.
Whoops, busted. I very rarely deal with localised data so I can get away with ASCII. Excellent point, though, my case doesn't generalise as well as I thought it did.
I use LibreOffice just for this purpose.
ie Creating csv files from xls files.
Open Excel file in LibreOffice Save as text >> UTF-8 >> quote all text cells.
Works perfectly every time, Excel is brilliant for most things but is useless with csv, all kinds of strange bugs will arise.
Exporting from and importing to Excel from CSV becomes a life-saver sometimes. Once upon a time, I had to work on a 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter. Writing VBA to export and then letting Cygwin do my work: curling regexing and sorting was the only option.
> 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter.
This is implausible. I worked with far bigger datasets, and far more complex calculations, and it didn't take minutes - several seconds, maybe.
I'm talking about a filter, not calculations. And I'm so glad you mystically decided that your calculations were much more complex than whatever I was doing.
Actually, one of the safest option I have found to import/export from Excel without the encoding going wild is to just copy/paste TSV data, and treat it as UTF-8 in your programming language of choice.
Spreadsheet programs are great for data entry if we take the care before hand to design the data structures well so that they easily export and can be easily analyzed by a computer. Easy stuff like small org budgets can be done with spreadsheets, but masse data analysis simply requires industrial grade tools.
well, the main feature Excel really suck, and makes it almost impossible to use: Undo/Redo. Why the hell did they make undo/redo work across spreadsheets (eventually reopening closed spreadsheets)?
And then, why is it impossible once you wrote some content, to copy it, undo writing it and paste it? The copy gets undoed!
To me, that behaviour makes Excel unusable.
And anyway, why would any decent SW engineer want to use Excel, which is like a 2 dimensional view of the world, whereas after a career designing software I can see the multiverse.
[1] https://www.youtube.com/watch?v=jFSf5YhYQbw&feature=youtu.be...