Hello,
I am wondering what sorts of things can you do with VBA in Excel. I know that it is used for automating the processes/routine work but how helpful it would be for a non-quant role such as a reserach/investment analyst?
Also, how reguarly do you use it if you don’t work in quant/risk management type of role?
Thanks
I would love to hear this too. I hear it being discussed, but I do not understand what exactly it offers in a tangible, practical sense.
Can someone provide an example of an instance in which one would use it?
Databases kept in spreadsheets…very common at asset management firms. You have a variety of spreadsheets linked together which I think is infinitely retarded because links go out of date, rows get moved, columns get added etc. First, get all data into a normalized database either on an SQL server, access database, etc. Then use VBA to connect, write queries, and create standard consistent analysis/reports that you can be confident hasnt been f****d up by some random row inserted or your “datafeed” excel sheet being slightly different. You can insert, edit, and consume data with VBA and create simple to use forms and tools for your colleagues to use in Excel. You dont know s**t about Excel if you cant use VBA.
Be as specific as you can about your exact job and role, and I will do my best to give you a concrete example of what can be done.
VBA, executed correctly and automating or organizing any process that your manager or team finds time-consuming or tedious can make you look like a bona fide rock star at work.
Well, I personally use it to automate certain, mundane and time consuming manual work.
For eg. Leave a line after every subtotal or copying a certain column/cell from a specific place in all the workbooks in folder X- can be automated using VBA. These are the basic things that any person will be able to use.
sonic in excel is my fav vba program
How extensively is VBA used by financial analyst? I see it is mostly used for creating subroutines like reading databases or maybe automating some small process while doing financial modeling as mentioned above but is it often used for creating the UI aspect also in financial modeling and is it used for extensive programming as any other programming language such as C/C++/Java?
I do believe languages such as C/C++/Matlab are used for financiancial modeling…but are these used by financial analyst…or is it left to the software developers to deal with them?
I feel as an Analyst, even if you know VBA, you are already distinguishing yourself from the crowd by getting your work done faster than the rest. Is knowledge of programming langauges *required*? I feel it would be a plus to have, but not a requirement (I checked a few listing for IB vacancies at GS).
Those languages are used for complex financialing modeling in financial engineering. This is different from the financial modeling we’re discussing.
My issue with VBA is that every time someone else opens your VBA-enabled file, microsoft says “THIS FILE MIGHT HAVE A VIRUS, ARE YOU SURE YOU WANT TO OPEN IT.” For stuff that only I will use, I will sometimes add VBA, but I hate getting that message from a file someone sent me and not knowing whether it’s a real virus, or something someone put in there to make a cell blue on alternate wednesdays or something.
I tend to avoid VBA to keep from putting other users of the file in that position if I can help it. I will also try to mention that the files have macros in them if they do have them, so they know I know they see that message. However, if a macro virus infects a file that has macros in it already, then the user is likely to open it up. So I just try to use as few as possible.
When I was consulting with Mitsubishi Nuclear Energy Systems I built an Excel model for analyzing the risks in the construction of a third nuclear reactor at an existing power plant. We had to submit a monthly report that included a section on risk management; originally, the customer had no idea what they wanted to see in that section, but over time they asked for more and more charts and data.
I used VBA to import data, categorize it, sort it, and create multiple graphs. Probably the most useful application was in creating bar charts in which I color-coded the bars based on certain characteristics of the data; this would have been absurdly time-consuming to do manually, but was trivial in VBA, and pleased the customer no end. Make no mistake: customers (whether external or internal) love to see colors on charts, and VBA is ideal for automating that.
To add to other people’s responses, I use it to compare balance sheets, income statements and cash flow statements from the same company. It’s helpful when the spreadsheets or data is pretty much the same line for line. If not then you have to use conditional ifs and all that to get it to work properly, so it’ not as automated as people would have you believe.
The nice thing about VBA in excel is when you can make user defined functions to calculate values that require complex math. An easy and relevant example to CFA curriculum and work is the BSM model. You create a function that returns a value of the call or put and name it as BSMCallPrice or w/e and instead of you writing the formula in an excel box each time you just enter =BSMCallPrice(parameters needed, x , y, z) and will return the value. Once you do that you can save yourself quite a bit of time if the function you created is used often.
In general, this is how I use programs at work
Excel / VBA = financial statements, small databases of info and calculating simple statistics
SPSS = larger databases, Regression, Multiple Regression, distribution fitting
Matlab / Stata = very large databases, stochastic calcuations, AR/ARCH/GARCH time series in addition to what SPSS can do
I also use this program @Risk by Palisades. It does monte carlo simulations and distribution fitting within excel and is very user friendly. It has a ton of other features as well which I have yet to use.
Palisade, no “s”.
I use @Risk all the time in my risk management work. The functions work just like built-in Excel functions, so it’s quite easy to use.
Thank you for that correction, S2000. Good to know other people here, especially you that use @Risk. A professor of mine gave all the students free copies for coursework and I’ve been using it ever since!
Interestingly, I just got an e-mail (20 minutes ago) from Palisade about a professor at a university in Puebla, Mexico who uses @Risk for option pricing. Instead of assuming normal distributions for returns (and arriving at the Black-Scholes model), he uses @Risk with historical data to generate nonnormal return distributions, and uses those in the option pricing model.
Too coincidental to be a coincidence, don’tcha think?
(Here’s the link to the case study: http://www.palisade.com/cases/Esparza_BUAP.asp)
Received this email as well, Michael Corbett right? I read the blurb in the email but will read the full case study later tonight.
Edit: on an unrelated note, s2000, have you ever used copula methods in financial work? I’ve read a few papers and watched some short presentations on it but it’s still very unclear to me how you could apply it. Anyone with info and can explain it in layman’s terms would be of great help!
Yes, from Michael Corbett.
I’ve never used copula methods. Sorry.
No worries, are you familiar with textbooks by Cherubini? There is a recent one titled ‘Dynamic Copula Methods in Finance’ published by Wiley. I’m thinking about buying it and see if I can self learn the concepts