Systematic Trading research and development, with a flavour of Trend Following
Au.Tra.Sy blog – Automated trading System header image 2

e-ratio calculation in TradersStudio and Excel

November 2nd, 2009 · 4 Comments · Backtest, Code, Development, Software

Here is one method to implement an e-ratio calculation.
As we say in developerspeak, the following is a quick and dirty approach to calculating the e-ratio. But as far as I am concerned, it does the job! It can probably be programmed more elegantly in TradersStudio but I still have to climb some of that software learning curve…

I will show you how I calculated the e-ratio for a 17-day Donchian Channel breakout coupled with a 108-day moving average filter (the red curve on this chart). All the code referenced is provided at the end of this post.

As we saw in the last post about the e-ratio, you need to run the same signal over multiple fixed trade durations and record trade data. For this the TradersStudio optimisation and custom reports functionalities fit the bill.

System Code

Step 1: code up the system to test in TraderStudio

As you can not have simultaneous open Buy and Sell positions, there are actually 2 systems (long-only and short-only). The below will be based on the Buy-only system.
The entry signal is a breakout of the Donchian channel if the MA filter conditions are met (price > MA and MA rising).
In TradersStudio this translates to:

MA = ScriptAve(Close, MALength,0)
If MA > MA[1] And Close > MA Then
    Buy(EntryName,1,donchianHigh+MinMove ,Stop,Day)
End If

The exit is simple: close the trade after n days (tradeLength):

If BarsSinceEntry=tradeLength And MarketPosition=1 Then
End If

Step 2: retrieve trade data in custom report

As discussed in the e-ratio post we need to get MAE, MFE and ATR for each trade. For this we use a TradersStudio custom report. The report code is executed at the end of the system test and retrieve trade info with the following command:

GetInfoAboutTrades(Info, i + 1)

we are interested in the MAE and MFE which can be derived off the Maximum Profit and Maximum Loss of each trade:

SetCell(row, 13, 2, 10, Info[16]) 'Max Pos Profit
SetCell(row, 14, 2, 10, Info[17]) 'Max Pos Loss

For the ATR value, we need to use a little trick as it is not directly available. We record it as part of the entry name which can be output to the report.
In the system code we have:

EntryName = "DonchianBreakoutBuy" + "|" + indLength + "|" + tradeLength  + "|" + avgtruerange(indLength)

which concatenates the entry name with the ATR value as well as the length of the indicator (Donchian Channel) and trade duration – this will come in handy when we do the e-ratio calculation.

Step 3: run the system through the optimizer

The system is called DonchianChanBrkoutFiltBuy and has 3 parameters:

Sub DonchianChanBrkoutFiltBuy(indLength, tradeLength, MALength)

In the optimization process, step through all values of trade duration required (e.g from 1 to 100 in increments of 1) – warning TradersStudio will take a few hours to do this (sic!).
This will generate a custom trade report for all trades containing all the data required.

Step 4: paste data in Excel

Once you run the long-only and short-only systems, we are done with TradersStudio. We need to move the data over to Excel for analysis and the e-ratio computation.
Copy the custom trade report from the short-only system run to one sheet and from the long-only run to another sheet (watch out for the number of rows: if you have more than 65,536 and a version of Excel earlier than 2007 you will need to do that in several chunks).

Step 5: Run e-ratio macro

Copy the macro code from the text file, create a new VBA module in Excel and paste the code in it.
Go back to both sheets and run the macro. This should manipulate the data and create an aggregation pivot table on a separate sheet.

Step 6: Compute the e-ratio

On a brand new sheet, copy both pivot tables (I recommend a paste/special – values only) side by side so that each trade length values corespond with each other. Sum each Total value (column D and J) together for the same Buy and Sell parameters (in column N in example file). Divide each Sum of MFE by Sum of MAE: this gives you the e-ratio for the given trade length.


Please find below code files to support the example below (all txt files except the Excel workbook). This should be easily portable to any system (i.e. just change entry criteria in the system).
Donchian Channel Indicator
Custom trade Report
Buy System
Sell System
Excel e-ratio macro (text file)
Excel example workbook (does not contain underlying raw data as it is 60MB!)

Or if you prefer a zipped version of all files – here it is.

Any questions, clarifications, etc. please let me know how I can help.

Related Posts with Thumbnails

Tags: ···

4 Comments so far ↓

  • Milktrader

    Thanks for the post. I’m gonna go over the code and see if it’s better to use Excel or R for the data crunching.

    You mentioned something about AmiBroker. Woodshedder prefers it and it would be interesting to see a comparison of the two by real traders. Keep us posted if you look into AmiBroker.

  • Jez

    I have actually spent part of the weekend playing with a test version of Amibroker.

    Calculating the e-ratio this way with TradersStudio works but the optimizer run takes way too long for any volume testing (ie over 2-3 hours for running the same strategy over 100 times – which is required to give you one curve of the e-ratio). You can see that it is not really scalable if one wants to increase the number of systems tested.

    I was planning on featuring a speed comparison between the 2 platforms for computing the e-ratio for the same underlying data and same system so I will let you know..

    Also I was doing some google research to see if people had compared both platforms and found 2 webpages with droskill’s take on both – might be of interest to you:

  • Ian

    Hi Jez

    I received this through the TS forum some time ago but I’ve finally got up to speed enough to properly review it. You’ve put a lot of work into getting Faith’s e-ratio system into TS and it appears to work well, thanks for sharing.

    I hope you wont mind me asking some questions on the usefulness of the code however (which are more questions for Curtis Faith than you I suppose, but your thoughts would be useful). Do you find the e-ratio useful for evaluating systems? It doesnt seem to me to add an awful lot more insight than just a time stop parameter within an ATR adjusted system. If you optimise using the time parameter you will then get similar information. It strikes me that it might just be an addendum to Faith’s other useful evaluation measures (RAR, 5 max DDs), which I find useful.

    Is this roughly correct or am I missing something (hopefully I wont be causing offence with any of these comments, as I see you have worked very hard on the code).

    Thanks in advance

  • Jez

    I still think that the e-ratio is useful as it measures the “potential” of an entry for example without any other consideration.

    In your example of your trading system, you would not really capture what is going on during the life of the trade (ie MAE, MFE) just the trade P&L. I don’t think it’s a revolutionary measure but I see it as a more independent edge measure for a system component.

    And no worries about the questions. They are all welcome!

Leave a Comment