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.

Did you find this useful? If so please share!