Trading Strategy Creation in BacktestingXL for Excel

To backtest a trading strategy, BacktestingXL iterates through all rows of historical data, executing strategy code for each row of data. Strategy code consists of these basic building blocks:

Function	Action	Syntax
DT	Returns Date	`DT(Day)`
OP	Returns Opening Price	`OP(Day)`
HI	Returns High Price	`HI(Day)`
LO	Returns Low Price	`LO(Day)`
CL	Returns Closing Price	`CL(Day)`
VOL	Returns Volume	`VOL(Day)`
OI	Returns Open Interest	`OI(Day)`
RNG	Returns Range Value	`RNG(UpperCell, Day)`
Buy	Creates Buy Signal	`Buy(NumberOfShares, SpecialOrder)`
Sell	Creates Sell Signal	`Sell(NumberOfShares, SpecialOrder)`

Day is a day reference to previous days in this format: Today - N. For example, CL(Today - 1) will return Yesterday's Closing price, CL(Today) or CL will return Today's Closing price.
UpperCell is an upper cell (the cell with a label) in the column of values that you wish to use in your strategy code. For example, RNG("G1", Today) will return values from cells under the "G1" cell.
NumberOfShares is the number of shares to Buy or to Sell.
SpecialOrder is the command to Buy/Sell at special price, different from default. For example, Buy(100, "Open") command will execute an order to buy 100 shares (contracts) at the opening price.

Backtesting Principles

There are two ways to create strategies:

Trading rules are programmed in a spreadsheet. This way is more time consuming, but does not require any special knowledge - only basic knowledge of Microsoft Excel.
Trading rules are programmed using VBA (Visual Basic for Applications) and stored in a special module of a workbook. This way is less time consuming, but requires basic knowledge of VBA.

Here is an example of a trading rule: Sell if Today's Open is greater than Today's Close, otherwise Buy. We can realize this rule in two ways:

1. Program the trading rule using a spreadsheet.

As you can see, the rule '=IF(B2>E2;"Sell";"Buy")' is located in each cell and produces buy/sell signals.

BacktestingXL code generated for this strategy can be re-used to produce buy/sell signals in other spreadsheets.

2. Program the trading rule using VBA.

There are no rules in the spreadsheet, just historical data.

Trading rules are written using VBA and stored in a special module.

Please see also software products of AnalyzerXL, LLC

Download Demo