This new template in the EdgeClub allows HGSI Investment Software users to do walk-forward testing on exported warehouse files.
Instructions
Export a warehouse file from HGSI
To export a warehouse file from HGSI Investment Software, choose File->Save As HTML…
Choose a warehouse view that has fields for ERG, EPS Rk, RS Rk, Grp RK, HGS Boxes and A/D Letter Rank as these are the fields which will be used by the EdgeClub software when building the report. It is also a good idea to include the Last Date and Last Close fields as these will help when later validating the generated report.
You can create a view to use for this purpose and you can also include any other fields that you would like to keep a record of.
Once you have created the view, you can follow the above steps to save that view of the ‘All Securities’ group with no filter applied so that the entire database of securities is included. Save the view once a day, week or month depending on your requirements.
Select the Warehouse Report Template
In the EdgeClub software, select the HGSI Woodward and Brown category and then select the Warehouse Report template.
View Settings
In the ‘Template Info’ worksheet of the template you will see the currently selected settings
The settings include:
Warehouse fie: the full path and filename of the previously saved warehouse file
Report From: the date from which to run the report (this should coincide with the Last Date of the warehouse)
Report To: The date to run the report to. This can be any date after the ‘Report From’ date.
Auto-Generate Snapshot: True/False setting which if set to true instructs the software to build a data snapshot using the warehouse symbols. This should be set to true the first time you run the report to ensure that you have the latest available data for all the symbols. Subsequent runs of the template on the same day do not need to generate a snapshot because the data will not have changed. Generating a snapshot can take a few minutes for a large symbol list such as is exported from the ‘All Securities’ group and you can avoid this overhead after the first run. The ‘Auto-generated’ snapshot will be given the name ‘ExcelReportsSymbols.txt’ and will be shown in the Symbol Lists pane:
Change Settings
Press the Change Settings button on the Template Info page. The following dialog box will appear:
Use the top text field to select the warehouse file. The ‘Browse’ button will allow you to navigate to the file to select it, or you can just type the entire path and filename into the box.
Use the Report Date Range controls to select the date range. If you have a date range that you consistently use then you can add it to your list of custom date ranges to allow for faster selection in the future. If not, just select the From and To dates.
Check the ‘Auto-create’ snapshot box if you want a data snapshot to be created from the warehouse symbols. See above for a description of how this works.
Press ‘OK’ to accept these settings and you will be returned to the ‘Template Info’ worksheet and you should see the new settings reflected there.
Press Run
The template will start to generate the report. Wait a few seconds for it to complete.
View the Report
When the template has finished generating the report, use the various template worksheet tabs to see the output. The ‘Dashboard’ worksheet shows a summary of all the other worksheets.
Here’s what the report tabs contain:
Parameters worksheet: All the settings used by this template
Warehouse worksheet: A direct copy of the input warehouse file with the missing symbols removed
Missing Data worksheet: A list of all the symbols for which data couldn’t be found in the selected snapshot. The reason for a symbol being missing could be that the symbol was changed between the time that the warehouse file was saved and the time that the report was generated. It might also have been de-listed. Another reason is that there isn’t enough data in the snapshot to generate the entire report. For instance if you choose to run the report from an earlier date than the warehouse start date, the data snapshot might not have all that data. If any symbol doesn’t have all the history for the entire date range it will be added to the missing data list and the report will not include this symbol.
%Gains worksheet:
This is the worksheet from which all the remaining worksheets are calculated. It contains a copy of the warehouse worksheet on the right hand side and on the left shows the applied ‘From’ date and close price, the ‘To’ date and close price and the % price change for each symbol. To verify that you have set the date ranges correctly you can see if the warehouse ‘Last Close’ matches the first column close price. Note that some of the prices might not match due to splits and dividends that have affected the historical view of the price. The majority of prices should match with the warehouse Last Close price.
Clicking on any header cell will sort the worksheet by that column. You can also double click on any cell to see a chart of the security:
If you set the date range of the chart to the date range of the report, it will be used for all subsequently viewed charts and so you can use the keyboard down and up arrow keys to quickly view charts of the listed securities over the report time frame.
ERG Worksheet:
The chart of ERG performance along with the calculated bucketed values. Note that the ‘0’ value is not plotted on the chart as this represents securities that did not have an ERG value defined in the warehouse.
EPS Rk Worksheet:
The chart of EPS (Earnings Per Share) Rank performance along with the calculated bucketed values. Note that the ‘0’ value is not plotted on the chart as this represents securities that did not have an EPS Rank value defined in the warehouse.
RS Rk Worksheet:
The chart of RS (Relative Strength) Rank performance along with the calculated bucketed values. Note that the ‘0’ value is not plotted on the chart as this represents securities that did not have an RS Rank value defined in the warehouse.
Grp Rk Worksheet:
The chart of Group (Industry Group) Rank performance along with the calculated bucketed values. Note that the ‘0’ value is not plotted on the chart as this represents securities that did not have a Group Rank value defined in the warehouse.
A/D Letter Rank Worksheet:
The chart of A/D Letter Rank performance along with the calculated bucketed values. Note that even though the warehouse names the field A/D Letter Rank, the actual letter ranks are A through E.
The chart of the HGS Boxes performance along with the calculated bucketed values. Note that the only securities that have a value for HGS Boxes appear in this report. The majority of securities do not fit into any of the boxes by design.
Dive Deeper
The template is able to read the file in the native HTML output format or in a re-saved .xlsx format. This allows you to further filter the report in Excel before processing it in the EdgeClub software if desired. Using this technique it is possible to see the performance of any field that was exported during the warehouse export. It is important to know that the EdgeClub software will process the ‘first’ worksheet that it finds in the input workbook and so when re-saving a warehouse file in .xlsx format, make sure that it is the first worksheet. The best way to do that is to copy the ‘warehouse’ worksheet from a generated report into a new workbook and use that as the new input file for the template.
Summary
The HGSI Warehouse Report template shows the walk-forward performance of securities that have previously been saved during the export of an HGSI warehouse file. If you regularly save warehouse reports you can construct your own view of how the markets respond in different time frames and based on different events. You can be sure that the warehouse view actually represents what you were looking at on the day it was saved, rather than a re-constructed set of data using currently available data sets. This allows you to test fundamental data as well as technical data and is one of the most accurate forms of testing available.