Backtesting in Excel vs MQL4

Does anyone do backtesting in Excel, or know members who do? I would like to discuss methodology and models with anyone who uses Excel. Does anyone have any simple (or complex) models that they would be willing to share for basic indicators or systems?

Or... should I devote some time to learning MQL4? I have a lot of experience modelling in Excel, but I do not have any experience with computer programming. I am reluctant to spend time learning MQL4 as I will be starting from nothing, but maybe this would be easier. Are there any other non-programmers out there who have become proficient in MQL4?

Excel is a powerful tool. While it is designed to work as a spread sheet and modelling, etc, people have used it to do all kinds of amazing things, including AI, data bases, etc even though there specialist tools designed specifically for those tasks.

MQL4 is a pretty crude language but it is designed specifically for trading and so it has many things specific to that task.

While there's an ongoing debate about the effectiveness of the strategy tester as a back testing tool, I'm sure that you will be back testing ten times quicker with MQL4 even if you have to learn the language from scratch. You're likely already familiar with a lot of the fundamental programming concepts like loops and conditional statements.

For the Excel route, you might want to look for tools that are already available, I'd be surprised if someone hasn't already done this. If you can't find something ready made, you will have to first of all design a trade simulator, handle the reporting, process your historic data, and then have a reasonable UI. All this comes for free with MT4.


Have fun.

Anything involving calculations I do in Excel, have done for years. However, I'm not sure you'd get anything out of my models as they're specific to what I'm doing.

Excel is way more flexible, and transparent, so you can interrogate and check the data properly. For the non-programmer it's golden.

Just as an example, how long would it take you knock up an EA that shows the average volatility of any given hour for the past 14 days...? I'm not saying it's impossible - I have no idea - but in Excel, a pivot table and 5 minutes later and you're done.

Where Excel falls down is in live trading - it doesn't play nicely hooking into other trading platforms (FXCM/IB/Currenex) but for backtesting, that doesn't matter.

When I started doing my own analysis I began with Excel as I had no programming experience and found VBA easier to learn than MQL4. Now I use a combination of both. In my limited experience, MQL4 is faster at performing calculations than Excel, in particular if your Excel sheet makes use of lots of user defined functions.

One of my ongoing projects is to build a spreadsheet to analyse 70'ish different instruments on weekly and daily timeframes. At first I thought that I would use MQL4 to write .csv files of OHLC info for each instrument and timeframe, then crunch the numbers in Excel. Downside - taking a couple of minutes to recalculate! So, now I perform all of the calcs in MT4 and then write just two files. Excel is then the UI and there is no waiting on calcs.

I suppose what I am getting at, is that if you can use both, then you are giving yourself the ability to use whichever is best suited to the task that you have set yourself.

Just my 2 pence...

I've tried these methods over the years:

MT4 Strategy Tester

Custom Python programs

OpenOffice Calc (Excel compatible)

MT4 Indicators/Scripts

Each EA has its own characteristics but, generally I've had the best results with MT4 Indicators/Scripts. If you can create an indicator that duplicates the actions of a given EA it's possible to turn that indicator into an analysis tool.

All EAs do not lend themselves to this approach but if you have one that does, it will provide near-instant results (not accurate to the pip, but close enough) and save having to tinker with csv files or other more complex interfacing techniques.

IMHO, let the nature of the EA you are testing dictate the best method of testing...

I'm not sure if this is the right place, but I have a Google Sheet that I'm playing with for back testing, but I need some help with the calculations. I don't think it's calculating my returns correctly. Would anyone be able to help me with the formulas required so that I can accurately test my systems?

Some questions

1. I'm still a little wooly in understanding position sizes. I understand the theory behind them, but I can't quite figure out if a position size relates to the currency amount that's placed, or if it's an extrapolation of risk, or some other value like lot size? For disclosure, I use a Plus500 demo account, and BabyPips' Position Size Calculator. I can't seem to reconcile the amounts that the position size calculator tells me to place with the values in Plus500.
2. I can't seem to create a formula that calculates pip value.
3. Do I need to be bringing the platform leverage 1:300 into the calculations somewhere?

In the Google Sheet, I think I've got the formula right with Trading Journal - Backtest - BabySteps, but the returns just seemed a little too good to be true. So I messed around with the calculations and tried to uncover what the inherent pip value was for each trade, and now I've broken my brain.

Thanks so much

Ciaran