Building a trading model in Excel involves developing the trading rules or logic that drive the buy/sell signals, capital allocation, and risk management.
There are several types of trading models, each requiring a different approach to the trading rules:
- Fully automated trading model (aka “trading robot”)
- Trading signal model with human decision-making to execute trades or not (hybrid)
- Portfolio management model
- Analytics-oriented “what if” model
- Pattern recognition model
- Cycle analysis model
Regardless of the model type, they all have common requirements:
- Process market data inputs
- Apply system logic to the inputs
- Generate buy and sell signals
In addition, some types of models have more elaborate functionality such as:
- Comparison and selection of buy and sell signals against a current portfolio
- Simulation or prediction of future market states
- Calculation of performance or risk (in isolation or versus a benchmark)
- Interacting with other applications, databases or trading models
- Creating graphical or analytical output for the user
- Accepting user input or input from other systems
- Running pre-scheduled or dynamic response actions
- Sending alerts and error messages
- Logging trading activities and performance measures
- Managing multiple user actions
- Automatically executing trading signals in the markets
There are two basic methods for building up a functioning trading model.
- The first method is to code all the desired functionality using a combination of Excel formulas and VBA code.
- The second method is to use an toolkit to add-in trading or analysis functionality to Excel.
Each method has its strengths and weaknesses. In many cases they can be mixed, so they are not mutually exclusive approaches.