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.