your latest blog post on backtesting with external data

[nibor200]

Chris,

In the externally called spreadsheet do the stock names have to be in a column and the individual dates in a row, or can it be switched around?

Do the data values in the externally called spreadsheet for the dates, have to be 1s and 0s, because they are only to be used in the variable data series as true/false indications, or can they be other values that are simply stored in the variable data series.

Thanks,
Ed S.

[Chris White]

Hi Ed,

The external workbook can contain a worksheet in one of two formats:

1. The first accepted format is a worksheet containing symbols in one column and dates in a row with the numeric data at the intersection of symbol and date:
Example worksheet:

ExternalDataFormat1Example.zip

(13.78 KiB) Downloaded 939 times

The worksheet needs to contain the following defined names that mark the different sections of the data:
ER_SYMBOL_COL: The name for the entire column containing symbols (eg: A:A)
ER_DATE_ROW: The name for the entire row containing dates (eg: 6:6)
ER_DATE_START_COL: The name for the column where the dates start (eg: C:C)
ER_DATA: The name for the data range (everything under the header row (eg: A7:Q16)

Attached example file for this is called 'ExternalDataFormat1Example.xlsx'
Note that the data in this example workbook is contained in a worksheet named 'Values'. If you wanted to use the data in your scripts, you could place the file in a folder named c:\mydata\ and then access it in script using the following:

Code: Select all

myVal: WB_SYMBOLLOOKUP('c:\\mydata\\externaldataformat1Example.xlsx', 'Values');

2. The second possible format is a worksheet containing symbols in one column and dates in another. A 1 value is produced by the script for date that has a matching symbol. For example if you have the following in your worksheet:

AAPL 07/25/2016
AAPL 07/26/2016
MSFT 07/25/2016

Then

Code: Select all

myVal: WB_SYMBOLLOOKUP('c:\\mydata\\externaldataformat2Example.xlsx', 'Values');

Will produce a 1 value for MSFT on 07/25/16 and a 0 value for all other dates . For AAPL it would produce a 1 value for 07/25/2016 and 07/26/2016 and a 0 value for all other dates.

This type of worksheet is recognized by EdgeRater if it contains the following named ranges:
ER_SYMBOL_COL: The name for the entire column containing symbols (eg: A:A)
ER_DATE_COL: The name for the entire column containing dates (eg: B:B)
ER_HEADER_ROW: The name for the entire row containing the column headers (eg: 6:6) everything under this row is considered data

Here's an example of this type of format:

ExternalDataFormat2Example.zip

(13.28 KiB) Downloaded 847 times

Note that this second format is automatically produced when you run an entries or exits script and use the 'List' view option (instead of 'Grid'). You can pop-out the resulting workbook and save it to your file system and then use it in your scripts. Of course you wouldn't need to do that because the entry/exit script has already generated the set of 1s and 0s. But you might want to manually change the values or just use it as a starting point for your own manually generated 'Entry' or 'Exit' signals.