|
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.
|
