# Working with Excel (tools, indicators, conversion ...) - page 22

237

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:

1754

you are totally talking about Datamining

or regression analysis

I doubt your Excel (2003 2007 2010) could do something that advanced

I learnt datamining in my master course, if I pay handsomely by you , I might able to do it for you -- I have not yet look into your data in details -- to see whether it is within my ability

237

Are you certain that it is as difficult as you say? The main part for me is not generating the function; that is secondary. For now I just want to graph the scatter chart properly.

1754

I am not sure how to do it in excel

you could use some automatic estimate function, but it is very primitive

===

I am quite certain that automatic programming -- datamining -- we seldom need to do real programming in those datamining software

it will find the weighted and variable to fit the best 91% of your best outcome (in this case, you find the few best EA that are able to get you highest profit and reliable consistency)

so I know a lot about datamining (my excel knowledge limit to pivot table , VBA etc) to get the best fitted curve or derive the formula for you

I think I could be in the consultant level indatamining to gave you my previous REPLY

but if I do it, it will come with a price tag

237

Oops - the variable is Expected Payoff, not Expected Profit. I got it straight from a strategy tester optimization log. Perhaps that mislead you? Or do you still believe that its a serious 'data-mining' project?

237

I understand what datamining is, and I am pretty certain that what I want is not datamining at all. From what I understand, datamining is applying various mathamatical/statistical functions to an (often large) set of variables and using these variables in combination to draw a conclusion out of the 'cloud' of data.

My situation is totally different. I have only 1 variable and nothing to 'mine.' 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 Payoff by the sum of all trade-amounts from all cycles.... or something like that.

1754

Excel sheet analysis - 30M chart -- Have you done that before

During every weekend

I find that the most consistent and worthwhile method is to use EXCEL spreadsheet to do simple analysis on past week 30M chart

should we post our Finding, with attachment of spreadsheet here

and use spreadsheet to create some Proven ENTRY price level or Limit Order strategy -- with attachment of spreadsheet here

Just an idea to present here, and see does anyone like this idea -- usually we could avoid betting on wrong direction and enter it when there is a possible wedging triangle of consolidation formed

24

hi

thanks

I've seen a strategy called Stochastic Calculus appear in quantitative analysis for the stock market, but it may be worth investigating advanced mathematics for FOREX as well. The way the trends in the market appear, using differentiation and integration to figure out the general "acceleration" of the market and in which direction it's occurring could work.

It would require something like Microsoft Excel, for you to plot a best fit curve. Using that, you could take the derivative of the given function to find the general direction of the market at specific points on the function. Following that, you could take the second derivative to find how volatile the market is at the time.

This doesn't take into account oddities in the news and such, however if compensated for, advanced math could easily be applied to FOREX.

3

Capturing indicator data in csv file

When I do a "Save" to a csv file, I get the date, time, open, high, low, close, and volume. I would like to also save the data from the indicator which is on the screen, which in my case is the Commodity Channel Index (set to 20). Is there any way to do that other than writing a script? If not, does anyone have a script I could use?