Building a trading model from scratch using a combination of Excel formulas and VBA can be a lot of work, but has the advantage of full control over the model and what it needs to do. Individual spreadsheets are used for data capture, trade rule logic, and calculating buy and sell signals. VBA can be used to move data around, do complex calculations, interact with other applications or Excel workbooks, and execute trading signals.
We recommend these Excel formula- and VBA-based products for trading, risk management and automation.
- Building an Automated Trading System in Excel
- Course in Excel, VBA, Formulas and User Forms
- Excel VBA for Trading, Risk and Automation
The benefits of using formulas and VBA are no constraints in how your model is laid out, how you interact with it, or the trading logic you use. Everything in the model is immediately available and not hidden in any way which makes developing, auditing, and explaining the model easy. Almost any calculation you need can be done in an Excel formula or in VBA code so there are virtually unlimited possibilities.
On the other hand, working entirely with Excel spreadsheets and VBA carries extra application and memory overhead since Excel is always working fully in the background — even if the trading model only uses a small set of Excel’s functions. This can slow the model down and makes coding a little more complicated.
