When backtesting stocks you often find the need to include data fields from external sources. For example, you might want to test a system that buys stocks that cross above their 20 EMA providing that they have also scored highly on a completely separate test.

One example of a separate test is the Top 50 GIR report, which is an Excel spreadsheet containing a list of stocks and dates that they appeared in the Top 50 GIR List (GIR Stands for Group Inclusion Report). Only 50 stocks are chosen per day for that list and they are chosen by the number of times they appear in various growth groups in an external program (HGSI).

The format of the Top 50 GIR report is shown here:

There’s an easy way to include this data when writing scripts within EdgeRater and it is done by use of the WB_SYMBOLLOOKUP statement. WB_SYMBOLLOOKUP stands for ‘Workbook Symbol Lookup’ and instructs EdgeRater to open the Excel workbook and use the specified worksheet. Then EdgeRater will Lookup whatever symbol it needs to from its current processing and construct a data series containing the external data.

WB_SYMBOLLOOKUP takes two parameters:

  1. Filename
  2. SheetName

If you have the above worksheet saved on your hard drive as C:\myData\Top50GIR.xlsx

Then you could access that data in EdgeRater ChartScript using the following statement:

myVal:= WB_SYMBOLLOOKUP(‘C:\\myData\\Top50GIR.xlsx’, ‘Values’);

That has the effect of loading the values from the worksheet for whatever symbol is currently being processed by EdgeRater. So in the above example, if the symbol AHH was being processed, there would be a value of 1 for the following dates:

7/29/2016, 7/28/2016, 7/25/2016 and 7/22/2016 (and all other dates that have a ‘Yes (1)’ value that appear further to the right in the worksheet). A zero value would be present for 7/27/2016 and 7/26/2016.

Use Any Script Function on the External Data

Suppose you wanted generate an event whenever there was a ‘Yes’ value today and there had been 20 ‘Yes’ values out of the past 80 days, here’s a script that would do that:

myVal:= WB_SYMBOLLOOKUP('C:\\myData\\Top50GIR.xlsx', 'Values');
cond1:= myVal == 1;
cond2:= COUNT(cond1, 80) >= 20;
event: cond1 & cond2;

You could then drop that script into the Entries and Exits tab and run it on a symbol list containing all the symbols that appear in the external file and you would get entry events that you could then test in the trade simulation module.

Combine with other signals

You are not restricted to using just the external data of course. You might want to find all 20 EMA crosses that occur only when the stock has appeared 20 times out of the last 80 from the external data. Note that the event no longer uses the condition that tests for the external data being a 1 value today.

Here’s a script that would do that:

myVal:= WB_SYMBOLLOOKUP('C:\\myData\\Top50GIR.xlsx', 'Values');
cond1:= myVal == 1;
cond2:= COUNT(cond1, 80) >= 20;
cond3:= CROSS(C, EMA(C, 20));
event: cond2 & cond3;

And here’s the entry signals that might be generated by the above script:

Summary

You can backtest using external data contained in Excel spreadsheets by using the WB_SYMBOLLOOKUP() function in EdgeRater. Using this function you can load the data as a data series into the program and then use other scripting functions on that data as if it were native to EdgeRater. This allows for great flexibility in backtesting.