Trading model rules are based on IF-THEN statements, Variables, Loops, and Arrays.
If-Then Statements
An IF-THEN statement is a way of conditionally evaluating inputs against pre-set criteria or rules. It is based on sequential linear logic, which is the way specialized jobs are accomplished by humans (lawyers, soldiers, engineers) or machines (assembly line robots, microchips…and trading software). An example of IF-THEN statements are:
IF the sun is bright AND the temperature is greater than 75 degrees, THEN wear shorts.
This can be written in a more mathematical or pseudo-code way:
IF sun = bright and T > 75 THEN garment1 = shorts
Another example:
IF price exceeds 50 today and price was below 50 yesterday, THEN Buy 50 shares.
Likewise this can be written in a more mathematical or pseudo-code way:
IF P(today) > 50 and P(yesterday) < 50 THEN Order = BUY and Shares = 50
Variables
Variables are placeholders or buckets that holds a value of a specified type and format. Trading rule logic makes heavy use of variables, whether they are in Excel formulas or VBA. A variable is basically some piece of data that you expect to change, but has some constraints that you can pre-define.
A variable can be a number (integer or float), string (text), object (file or program), or any other type of data.
In last example above, there are 4 variables: P(today), P(yesterday), Order, and Shares. Each of these is assigned a value then evaluated in the IF-THEN logic. P(today) can be 600, 12. 8.534, etc. P(yesterday) can also be any price. Normally the price variable would be assigned a format that would only accept price and not some other type of data such as a string (“horse”) or object (“IMAGEFILENAME.JPG”). Price would not be an integer either, since prices have decimals, so it would probably be a float type variable. Likewise the Order variable can be BUY, SELL, or OUT but not a number. The Shares variable must be an integer greater than zero, since shares trade in whole units. There is no such thing as -100 shares (although this is often how a short position is represented).
Loops
Loops are a highly useful way of going through a list of data and running the same IF-THEN logic repeatedly. In Excel VBA the most commonly used loop is expressed like this:
FOR n = 1 TO LoopCount
IF X = expected value THEN execute buy/sell rules
Next n
Exit FOR
This is commonly applied when you want to calculate trading signals on a big list of stocks, say 500. When the loop runs it will start at the top of your list of ticker symbols (n = 1), checking to see if the trading rules give a buy/sell signal for each stock. If the first stock has a Buy signal then the trading rules are executed for that stock. If the first stock does not trigger the trading rules, nothing is done for that stock, and the loop proceeds to the next stock (n = 2) and so on. When it gets to the end of the stock list (n = LoopCount) the procedure ends and the signal calculating operation is complete for all your stocks.
In this case LoopCount is a variable you can specify — it can be the number of rows that are not empty or a number you type in a cell (LoopCount = value.cells(“A54”) ). You don’t need to use a variable here (i.e. just say FOR n = 1 to 500) but it adds some flexibility in case you want to use 10 stocks or 3200 stocks.
Arrays
Arrays are tables of data. They can be visible such as numbers typed in a spreadsheet. They can be invisible such as numbers held in memory. They can be 2-dimensional like a spreadsheet or 3-dimensional like a cube. They can even have more dimensions or they can be nested inside of each other (each item in the mother array being a child array) but this gets confusing fast.
The most commonly used array in trading models is price-volume data:
Date Open High Low Close Volume 1/3/10 54.80 57.30 54.00 54.65 100000000 1/4/10 55.11 59.00 55.11 58.35 150531453 1/5/10 59.00 61.89 58.26 60.20 251233333
