Should you use Macros in Financial Modelling?
03 September, 2011
To celebrate the launch of Chandoo’s next batch of VBA classes, I thought I’d take look at the use of macros in financial modelling.
Whilst it’s not necessary to become a super VBA programmer in order to be a good financial modeller, it’s not a bad idea to have a working knowledge of macros - and the language they are built in, Visual Basic for Applications (VBA). The best sort of financial model uses the simplest tools, and introducing macros to a financial model brings a whole new level of complexity. For example, users wanting to run your macro will need to their security settings in order to allow macros to run, and if you are using Excel 2007 or 2010, the file containing macros needs to be saved as a different file format. For this reason, macros should only be included where the functionality is not available any other way.
Basically, there are more things that can go wrong if a financial model contains macros and sometimes formulas or filtering will achieve the desired result, without the need for building a macro. Always try a formula or standard Excel solution first before considering the inclusion of a macro in your model. However, even if you decide to keep your model as simple as possible, and have steered clear of VBA in your model building, the chances are that you are going to come across macros in other people’s models eventually, and so you’ll need to be able to recognise, edit, audit and check the macro for accuracy.
What is a Macro, Anyway?
A macro is a collection of commands that are performed in a set order. A macro enables you to repeat operations that you would normally do by hand, but it is much faster, and when written correctly, much more reliable. Often, a macro will do in seconds what takes hours or days by hand. It can also perform tasks that are physically impossible manually. If you find you are performing the same commands or actions over and over again, in exactly the same sequence, you can create a macro to record all those actions for you. You can then assign the macro to a button and then run the macro using a single click and even assign the macro to a keyboard command.
What can a Macro do for me?
There are loads of analytical situations where macros can save time and increase accuracy. They might not necessarily be built and saved within financial models, but they could be used in the collection of the data that goes into model. Here is a small sample of the kinds of situations I’ve worked on recently where macros come in handy for financial modelling and analysis:
- Fifty identical budget templates have been created, and you discover a formula needs to be changed. Instead of making the change fifty times, record a macro – it will be much quicker, and far less prone to error.
- A non-profit organisation sets their pricing so that all costs are fully recovered to break even. The costs keep changing though and the user is a senior account manager who doesn’t know how to do a goal seek in Excel. The modeller creates a simple macro using a goal seek so that all the user needs to do is change the costs and press the button to find out how much the pricing needs to change to under the new costing.
- A dump of information containing several thousand rows is exported from a database into Excel every day. The data needs to be formatted and manipulated manually to be used in a daily takings report. Automating this with a macro can literally save hours of manual data manipulation.
- A reporting model is built using a pivot table, but when new data is entered, the user sometimes forgets to refresh the pivot table. The modeller builds a button to refresh a pivot table every time it’s pressed.
So how do I learn VBA?
VBA in Excel is a huge topic in itself and one that we really only touch on during Plum Solutions Training courses. Once you start writing macros it’s quite easy to get hooked as you can do some truly amazing things in Excel which will bring the functionality of your work in Excel to a whole new level. If you do develop an interest in the subject, there are lots of books and training courses specifically on macros and VBA. Chandoo has just launched an online VBA training course, and as many of you know, I’m a big fan of his blog and online courses. Quite a few of you did Chandoo’s Excel School, and we got some really good feedback .
From only US$97 you can get:
- 12 weeks of VBA lessons
- Code examples & excel workbooks
- 6 Month Access to Online Classroom
- Free bonus – Chart Design E-book
- 30 Day money back guarantee
The VBA Class is reopening in January 2012.
I signed up for Chandoo's VBA course. I think it will clear up some mystery and make me more efficient in writing modest macros.
Submitted by Chris (not verified) on Wed, 11/05/2011 - 06:09.
I would like to know if there any further session on VBA as I am interest in enrolling.
Submitted by Dinesh Ramu on Mon, 25/07/2011 - 19:16.
The VBA class is reopening again in September. You can register here:
http://chandoo.org/wp/vba-classes/
Submitted by danielle on Tue, 26/07/2011 - 07:39.
Post new comment