Traders use Excel all the time, but how do they make their trading VBA models work in a fast-paced environment full of massive quantities of data? This is one of the main challenges when programming VBA for capital markets applications. The techniques for implementing a capital markets Excel model are well-known on Wall Street. This article discusses coding VBA in a trading environment for the best effect.
An initial concern is how the underlying Excel model will be used. Is it a rapid-fire trading platform with direct order entry, automated P&L calculations, and a computation engine behind it? Or is a specialized valuation or position keeping engine with complex models and formulas? Is it used by a trader, risk analyst, data analyst, statistician or quant, or accountant? The Excel model’s trading VBA will be applied differently in each use case.
Secondly, do you intend to implement the entire model in VBA with Excel only capturing the inputs or underlying data? Or will the VBA be combined with formulas, manual activities, charting, or other Excel features? VBA can be used to do almost anything from valuation to trade execution to risk analysis and market data handling.
Quality trading VBA should be coded in a highly modular fashion. Using Microsoft’s native VBA functions, you can build a library of custom functions that can then be called within your code and with user controls such as buttons, sliders, etc. The more modular you make your code, the better. Once your code library gets large enough, it’s time to consider building it into an add-in library instead of housing it in a single workbook or worksheet. This allows you to share your trading VBA among multiple users, for example, a group of trading desk analysts tracking P&L and entering trades, or a group of traders across different geographic locations.
There can be an overlap between the capabilities of a VBA-driven Excel trading model and dedicated trading software. You can use VBA to automate data import and analysis, create dynamic blotters, generate and execute trade orders, log trade actions, measure risk and exposure limits, track current and cumulative profits and losses, run risk models, and analyze historical trading performance. Although VBA is not the fastest language, it is highly practical and saves a lot of time compared to doing everything in Excel manually.
Asking these questions before developing your Excel trading VBA will help you plan and code your project the right way to maximize success.
