Tools for Excel/VBA and C++ Programming

In this course you will do a lot of programming. Most projects can be done using Excel and the VBA language. Some will run very slowly in VBA and may take several days. For these, you will need to code part of the project in C++. Don’t worry!

Remember the economics of software engineering: There is a constant tradeoff between

1. How fast the code runs.
2. How long the code takes to write (and debug), and
3. How much the code can be reused in the future.

Keep these in mind as you balance the amount of time you spend writing, debugging, and waiting.  There is a time to invest in writing good (fast or reusable) code and a time to write disposable code.   TIme management is important.

Also, keep in mind that you may be able to leverage the work of others.  Sometimes, however, it’s much easier to write what you need than to figure out how to adapt the work of someone else.  Also, you’re much less likely to be sued using your own code!

Excel:VBA

Here are some (possibly) useful links for Excel. There are some links to commercial tutorials ($$), but many free resources as well.

http://www.mindspring.com/~tflynn/excelvba.html
http://www.exceltip.com/exceltips.php?view=excel_links

http://www.mathtools.net/Excel/VBA/index.html

Some finer points of programming in VBA

Always declare your variables: http://www.cpearson.com/excel/variables.htm

Dealing with arrays: http://www.cpearson.com/excel/ReturningArraysFromVBA.aspx and http://home.pacbell.net/beban/.

C/C++

You don’t need to use any of the advanced features in C++. Knowing the bare basics will take you a long way. Using C++ has three advantages:

1. Properly implemented, C++ makes very efficient use of computational resources and results in much faster execution time than simliar VBA code. It also lends itself well to modular code that can be easily reused, modified, and extended.
   • There is an 80/20 rule in software: 80% of the run time is spent in 20% of the code. For numerical work, it is more like a 98/2 rule. Most time is spent inside a few “for” loops, which may need to be optimized. A good strategy is to isolate these sections and implement them in C++.
   • There is an 80/20 rule with respect to programming: 80% of your coding time is spent debugging, while only 20% is spent programming. For C++ it may be more like a 90/10 rule.
2. Several mathematical, financial, and statistical libraries can be called from C++ that cannot be directly used from Excel.
3. Being able to work with C++ translates directly into a professional advantage in entry level quant jobs. It really stands out on a resume and is absolutely necessary for many jobs on (or near) Wall Street.

That being said, there are some disadvantages. Learning C++ properly takes a long time, and it’s a terrible first language to learn. So is Visual Basic (but for different reasons). If you want develop your software engineering skills, you would do well to learn (i) strongly typed, object oriented language like Java, (ii) a simple, loosely typed, efficient language like C, and a powerful scripting language like Python, Ruby, or Perl. While these languages are useful, they don’t translate directly into extra cash on the quant job market. Employers tend to care mostly about Excel/VBA and C++.

The Tools: One combination of free tools that will let you compile and run C++ code on Windows is this:

• Microsoft Visual C++ Express Edition 8.0 (2005)
  You will also need to download and install the Platform SDK from the same site. Registration is required, but the products are free. There is a new version of MSVC++ Express (9.0, 2008 beta), but it may be difficult to compile some third-party packages with it.

If you have lots of money, you could always use the full version of Visual Studio, but it is not required. If you prefer to use open-source software, the package Dev-C++ will give you similar functionality to MSVC Express, but is much more light-weight and uses the GCC/Mingw compiler, which is also what is used on OS X and linux.

You may find the following resources useful:

• Quantlib: An open-source library in C++ for doing many standard tasks in Financial Engineering. Has interfaces to many other environments, including Excel.
• Numerical recipes in C: The text is free and has code that you may copy for use in your own programs.
• GSL: An open-source library to perform a number of tasks in scientific computing.
• Gopi Goswami’s course on advanced Monte Carlo techniques has lots of code in C++ and R (an open-source clone of Splus).
• Boost: A standard extension to the C++ standard library. Has some support for random number generation, numerics, and matricies. Maybe more horse-power than you really need right now.
• An overview of C. Introduction to C++. Other tips and tricks.

Linking C++ to Excel: Excel has the ability to directly invoke C functions compiled into DLLs. The process of building the DLLs and pulling them into Excel is very painful. It is outlined here. There is an easier way. The XLW library takes your C++ code and generates all the (rather complex) interface code necessary to build an Excel add-in.

Here are some other links to useful Excel/VBA/C++ resources:

• http://www.fontstuff.com/vba/vbatut08.htm
• http://www.geocities.com/WallStreet/9245/
• http://www.cpearson.com/excel/variables.htm Always use ‘option Explicit’ to enforce strong type checking.
• http://www.cpearson.com/excel/ReturningArraysFromVBA.aspx Returning arrays from a VBA function is always a pain. http://home.pacbell.net/beban/
• http://tiger.la.asu.edu/Quick_Ref/vbasic_quickref.pdf A quick reference for the language.
• http://home.uchicago.edu/~rmyerson/addins.htm An add-on for doing statistics.
• http://www.cpearson.com/excel/MainPage.aspx General useful information.
• http://www.mindspring.com/~tflynn/excelvba.html Examples of various tasks in VBA.
• http://www.iro.umontreal.ca/~simardr/random.html#Rand Lots of resources.
• http://www.markjoshi.com/ Various useful information.