When learning tools like excel, sas or programming language, the most important thing to understand that all languages and tools are the same. Once you grasp concepts such as arrays, list, maps, sets, functional constructs such as map, flatMap, filter etc, understand inheritance and composition, learn proper encapsulation tehcniques, become proficient in boolean algebra, you will realize that all languages are very very very similar.
Learning specifics of a tool is good, but who said that tool will be around long enough for you to gain benefit.
But i regress, i believe excel, bloomberg and other toosl will be here for quiet some time, so it is definitely worth learning, it is just computer scientist in me prefers theory over application
I agree that understanding that programming languages all have more or less the same tools, and the rest is theme and variation is true, but part of the point of Excel is that you don’t have to be a fully fledged computer scientist and programmer to use it.
I like how in general, the comments here reveal that not many people really know how to use VBA, though there are plenty of VBA critics (for every skilled VBA person, there are 3 or 4 people that criticize VBA, even though they aren’t able to program in ANY language). Python, C++, etc. are great, but you know, everyone uses Excel. It’s kind of like that line of thought where people claim the MBA degree is “commoditized” and use that argument to rationalize that they shouldn’t get it, when it is clear that people with MBAs still have an advantage, even if the degree has become diluted.
The same with VBA. Learning basic VBA commands and getting Excel to do some boring thing automatically for more times than a human has patience > any combination of Excel function knowledge, Pivot tables, etc. (although, to be fair, knowing the other stuff is cool too – I just think that those things are even more powerful when called programatically through a module). And if you can effectively make an application or protected program within an Excel spreadsheet, well, how can your students not hold themselves out as Excel rockstars if they can do that?
The most important investment you can make for your students is to buy a copy of John Walkenbach’s “Excel 2010 – Power Programming with VBA.” The book even comes with a CD loaded with a PDF copy of the book itself, you know, for your students to “borrow.” Learn to program some basic financial mathematical functions yourself and spend 20-25 minutes of your class going over the mathematics of the technique, then the remainder of class with the VBE editor up on a projector screen, explaining the code line-by-line, then get them excited to see that they can write something in 10 or 15 minutes that can immediately be executed to generate results. Just my two cents, not that I know anything about teaching college classes in finance.
I’ve got several students working through Walkenbach’s book as I write. Although we don’t currently have a VBA class, I encourage my better students who want to differentiate themselves to learn some VBA.
Yes, that’s good. I always wondered why VBA classes are so scarce, when it is in such widespread use. Like I said, it is one of those things that people take for granted, until you have an interviewer that understands the power of a candidate who is in command of it…I marvel at how otherwise bright and intelligent analysts I work with will spend entire days repetitively doing something I can write code for and have done in 10 minutes. It is the greatest sin in investments today, the widespread lack of VBA knowledge combined with hordes of overconfident candidates inaccurately describing themselves as “expert” Excel users.
Thank you. I long wondered to myself why so many of my colleagues stay past 10 or 11pm (this job is seriously 9-7pm max, I can generally sneak a 5:30pm or 6pm cutoff though at least twice a week) and when someone asked for some Excel help, I realized why. Little manual things that add up make people stay here longer…some guy here had a chart with ~70 series where he wanted to change the data label from value to category name…was doing it by clicking each one individually since you cant change them all at the same time in Excel. When he got to #5 he thought there might be a quicker way so asked me if I knew…I just wrote a for loop in VBA that did this for however many series there were, quick, simple, beautiful.
I agree that I’d expect an “expert” Excel user to know how to use VBA, though my own VBA is a bit rusty.
I think “advanced” Excel wouldn’t necessarily have much exposure to VBA (I’d expect them to have seen it once or twice, but not necessarily remember much), but would know how to do pivot tables, goal seek, solver, clever uses of SUMIF and SUMPRODUCT, and perhaps some matrix stuff.
VBA is on my resume, but I try to avoid it as much as possible in practice. The whole “module” approach to incorporating VBA into excel strikes me as very inelegant, and it also sets off all sorts of alarms about viruses that users down the road won’t feel comfortable with. So though I know how to do things in VBA (or used to), I try to avoid it as much as possible. I probably also suffer from the (probably-not-true-anymore) stereotype that BASIC is kind of a toy language.
Whenever I get to a point in Excel where I start thinking about using VBA, I generally decide it’s time to move the project to an R implementation. I have some tools in R that help me transport stuff back and forth between Excel when I need to do stuff like that.
I recently finished a consulting job with Mitsubishi Nuclear Energy Systems: risk management for a construction project for a third nuclear reactor at an existing power plant.
When I was developing the risk management section for the monthly report, I found that it was useful to the client to have bar graphs in which the size of the bars represented the magnitude (expected value) of the risk, and the color of the bars represented the likelihood of the risk event occurring. It would have been incredibly tedious to change the color of each bar manually, but it was almost trivial to program in VBA. I designed my spreadsheet so that I could click a button to import the data, then click a series of buttons to process the data, one of which set all of the colors on the bar graphs.
That’s the advantage of VBA. It was invaluable in that application.
^Wouldn’t it have been easier to graph them on a scatterplot? Magnitude on the Y-axis and likelihood on the X-axis?
EDIT - not to take away from Magician’s VBA skills, but this is an example where you could apply the KISS principle. Knowing what you’re trying to present and making it as simple as possible is a skill–Excel or no Excel. And I think that’s part of what a lot of students are missing.
This type of insight, when coming from a fresh graduate, is the most useful “Excel” skill they can possess. Of course it is transferable to other appications, but it is still an Excel skill as long as you use the built-in graphing, charting and analysis tools to achieve your goal. Putting a graph and color coding may sound trivial, but I am certain 95+ % of new analysts would miss something like this even if they are loaded with VBA knowledge. Do I care if they have to pull an all-nighter to color-code the chart bars when it can be done in 5 mins with VBA script? It’s their problem to figure out. On the contrary, realizing that such and such visualization would enhance our presentation to a client is invaluable.
Too many people put too much emphasis on technical skills, but that doesn’t make them stand out as much.
@Ramos4rm I think there’s excel add-ins that can easily set the data labels equal to a range (and I think newer versions of excel might have this built-in).
@Mobius Strip I completely agree with what you’re saying. How to present data is incredibly important (and a whole field called data visualization), and yet unfortunately most people receive zero training in it. You’d think that business school classes might include a week or two as part of the required curiculum. The skill of creating a scatter plot in excel is different from knowing why you should create one, instead of N other types of charts.
That’s definitely a useful application of VBA. I love data visualization tasks and if you are not in one of the standard chart forms that Excel likes, customizing charts can be a PITA. I’ve not used VBA for that. I should look into it.
And I’d definitely recommend “The Visual Display of Quantitative Data” and “Envisioning Information” by Edward Tufte. A lot of great stuff in there.
I do a lot of presenting at conferences, and academics usually suck at presenting, which is ironic - we’re in the business of teaching, and you’d think conveying stuff clearly and memorably would be something that we’d focus on.
Surprisingly, what made my presentation skills improve the most was teaching my student-managed investment fund puppies how to make presentations that were low on the S-factor (Suckage) - they did several presentaitons to faculty, alumni, and industry folks each semester. SInce their presentations affected how these groups viewed them (and me, by extension), they reflected on me. So, to better teach them, I read a lot of material on presentations (both on the presentation materials they used and on their “acting” skills).
As a result, mine got better - nothing improves your comprehension of a topic like teaching it to someone else.
Sweet stuff man, thanks. I’ve been getting fairly good at charts and learning how to combine multiple chart types together to give more information. I then send it to colleagues who aren’t the most analytical and tech savy to see if they understand ha ha. This website looks great and i’ll be buying one of those books soon