Graphing "Expected Profit" in Excel

Hi guys,
I need to construct a scatter graph in Excel of the “Expected Profit” (as outputted by the MT4 Strategy Tester) versus a variable that I ran an optimization on called “Cutoff.” My aim is to use Excel to generate a best-fit curve for the resulting graph and incorporate the function in one of my EAs.

When I tried doing this I ran into a problem: Some optimization cycles yield very few trades (ex. 2), while others yield magnitudes more (ex. 450). It makes no sense to plot the “Expected Profits” from the 2-trade-optimization on the same graph as the 450-trade one; but I can’t just toss those cycles out either.

I need to somehow apply a “weight” to the various “Expected Profits” variables based on the number of trades. This is what I cannot figure out.

I attempted to make a “Weighted Expected Profits” column and simply use that in my graph in place of “Expected Profits,” but the results were not what I intended; I must have done it incorrectly.

Here is an example:

Just wondering, is that small curve a measure of probability or frequency?

That curve is Excel trying to make a best-fit curve to the scatter plot using a logarithmic function

What is the formula you used for the regular Expected Profit column?

Oops- I meant to call it “Expected Payoff”; I got it straight from a MT4 Strategy Tester optimization log.

All I need is a coefficient to multiply the “Expected Payoff” by in a way that would compensate for the chart-skewing effect caused by throwing a 2-trade optimization run into the mix with 250-trade runs.

I am certain that there is a very simply solution… such as dividing the Expected Profit by the sum of all trade-amounts from all cycles… or something like that.

Expected Profit as calculated by Metatrader is Net Profit/N. In other words, what you’re doing there is NetProfit/N/N*n. I don’t think that SUM(A:A) should be there at all.

A suggestion for weighting would be to multiply each sample by the proportional value (i.e. n/N). That might work, I don’t know.

When you say “proportional value,” do you mean Expected Payout[B]/[/B]∑(All Expected Payouts) or do you mean “#Trades/∑(All #Trades)”? Or both?

On second thought, it comes out to the same number. I’ll see if I can think up something else.

I appreciate you helping, thanks a lot.

I’m not sure if this will work, but maybe you can use confidence intervals to weigh it by the size of its standard error, somehow. Pretty much, this would produce a ± % value (like what you would see in polls). The higher that value in comparison to the base value, the more likely that the base value is erroneous. I’m not too sure how you could fit it in but it’s something to fool around with.

  1. First calculate Expected Profit * Trades into a new column (I’ll assume Column F).

  2. Next calculate F1/SUM(F:F) into a new column (I’ll assume Column G).

  3. In another blank column (H), calculate SQRT((G1*(1-G1))/A1)

If you compare cells in column G and H, you can see the margin of error in H. If G1 says 0.052 and H1 says 0.0070, that means that the true value of G1 is likely to be within 0.007 of 0.052 (i.e 45 - 59).

In other words, the higher the G1 in comparison to the H1, the more “probable” it is that the statistic is accurate.