MT4Analysis is a collection of programs built into a system or project that will (eventually) deliver end-to-end analysis of MetaTrader V4 trading accounts.
The backend consists of a program to parse and load MetaTrader V4 statement files into a database. The frontend will consist of a series of programs to analyze and present various trading performance statistics.
I have chosen to write this project using the Php, JavaScript and HTML languages with SQLite version 3 as the database.
Note that Php is a server-side scripting language which means that the bulk of the processing takes place on a web server, not on your web browser. This also means that in order to use MT4Analysis, you will need a web server.
Note that this document will not repeat an external hyperlink (abbreviated to link). An external link is a link that refers to another website. The very first time the word or phrase is used, it will appear as a link and each subsequent reference will appear in italics. A complete alphabetical list of all external links this document referrs to is contained at the bottom of this document.
Since there are a number of free Internet-based MetaTrader statement hosting services already available, why should you use MT4Analysis?
MT4Analysis has several important abilities and advantages that none of the current Internet-based MetaTrader statement hosting services have:
All the current Internet-based MetaTrader statement hosting services are geared towards your current MetaTrader statement file. When your broker decides to wipe your trade record, all of your hosting service trades will be lost.
MT4Analysis loads all of your closed trades into a database and this record will survive any broker statement rollover.
Not one of the current Internet-based MetaTrader statement hosting services is currently capable of presenting trade analysis on an expert advisor (EA) basis. Some services will present trade analysis on a single currency pair but that is it.
MT4Analysis can provide trade analysis based on an entire account, an EA trading single or multiple currency pairs or a single currency pair.
All of the current Internet-based MetaTrader statement hosting services present trade dates and times only as they are recorded in the MetaTrader statement file.
Trade dates and times in MT4Analysis can be displayed in any time zone.
Version: .50.05-2011.08.09
This project is currently in an alpha stage. Some parts are working well and other parts are buggy. Here is the current breakdown:
Here is a list of the software requirements:
Since all the above components are open source and multi-platform, you may run MT4Analysis on any platform that supports all the software components including:
This may sound complex but it does not have to be, especially if you decide to run all the software components on the same platform that Metatrader runs on, which is Microsoft Windows.
The really good news is that there is a Windows distribution of LAMP called WampServer that works right out of the box without any configuration.
This quick start assumes that you will be using Microsoft Windows along with WampServer as your MT4Analysis platform. Please read the important notes below the instructions before you start.
To get up and running as fast as possible, just follow these steps:
If you are already using WampServer and already have it installed or you want to isolate the MT4Analysis files in the www directory, just create the sub-directory c:\wamp\www\mt4analysis before de-compressing the MT4Analysis files and add the mt4analysis directory to each of the paths described above (eg: c:\wamp\www\mt4analysis\db).
If you are at all uncomfortable with the above steps, please read the entire documentation before proceeding.
Warning: Do not put this MT4Analysis setup on the Internet. The above setup is designed for ease, not security. If you plan to offer your Mt4Analysis data on the Internet, there are a number of steps that must be done in order to secure your data and installation.
The first step in running MT4Analysis, is making sure you meet the software requirements. If you believe that you already meet the software requirements, you can skip to testing Php.
As mentioned in the quick start, the fastest and easiest way to install and configure a web server is to use Microsoft Windows to run your web server. Just download and install WampServer and during the install, accept all the default settings.
This will install the Apache2 web server, the MySQL database server (not needed for this project at the moment but handy to have around), the Php language, the Php CLI, the Php PDO extension and the Php PDO extension for SQLite.
You can then download and extract the SQLite CLI.
If the speed of the web server and security are more important than the ease and speed of the installation, I highly recommend that you install your web server under the Linux operating system. This is especially important in terms of security if you plan on putting your MT4Analysis server live on the Internet. The specific flavour of Linux I am partial to and recommend is Ubuntu.
This section will assume that you are installing the required software from Ubuntu Linux.
Start a terminal/shell/command prompt and type:
sudo apt-get -y install mysql-server apache2 libapache2-mod-php5
php5 php5-mysql php5-cli php5-sqlite
sqlite3
This will download and install all the required software. In my testing of the Ubuntu install, I found that the Apache2 web server would not process Php files until it was restarted with:
sudo /etc/init.d/apache2 restart
At this point, it is a good idea to test your web server and Php installation. I am assuming that you will be testing the installation from the server itself.
Use your favorite editor and compose the following file:
<HTML> <HEAD> <TITLE>Php Info</TITLE> </HEAD> <BODY> <?php phpinfo(); ?> </BODY> </HTML>
Save the file with the name info.php and place this file into your web servers' www directory (if using Wamp this is C:\WAMP\WWW, if using Ubuntu Linix this is /var/www).
Now launch your favorite browser and type this into the address line:
http://localhost/info.php
You should see a page in your browser similiar to this.
This test page was taken from a Windows server running WampServer. If the page appears properly, the web server and Php are functioning. Scan the output page and locate the section entitled PDO (the section title will be centered on the screen). This section will list all of the PDO drivers installed. Make sure that the SQLite PDO driver is listed.This section assumes that you already have a web server installed and running all of the requirements. If this is not the case, go back and read the previous two sections.
To install MT4Analysis, just de-compress the MT4Analysis package into the directory of your choice.
If MT4Analysis will be the only application served-up by your web server, just de-compress the MT4Analysis package into the main web server document directory (eg: c:\wamp\www if using WampServer, /var/www if using Apache web server under Ubuntu Linux).
If MT4Analysis will have to co-exist with other web applications, it is best to create a directory specifically for MT4Analysis right inside the main web directory (eg: c:\wamp\www\mt4analysis if using WampServer, /var/www/mt4analysis if using Apache web server under Ubuntu Linux).
Once the base MT4Analysis software is in place, you will need to create a directory especially for your trading databases. If you are using WampServer, I suggest that you create the directory c:\wamp\www\db\my. If you are using Ubuntu Linux, I suggest that you create the directory /var/www/db/my.
Once a database directory has been created, copy the skeleton.db datbase file from the install package db directory into your new database directory and name this file in accordance with your trading account (eg: demo.db, live.db). It is not mandatory that you use a .db file extension, but I recommend it for clarity.
Make sure you remember your trading database path or write it down. You will need it during the configuration phase.
In order for time shifting to accurately take place, Php needs to know the current time zone.
The main Php configuration file called php.ini needs to be edited. If you have installed WampServer you can edit the php.ini file by clicking (left click) on the WampServer icon in the system task tray (bottom-right of the screen) and choose the option Php and then choose php.ini which will load the php.ini file into the Notepad editor.
Search for [Date] and change the date.timezone. The possible combination of time zones are listed here.
Right after the MT4Analysis package is installed, you will need to register your database trade path with MT4Analysis.
MT4Analysis needs to know where the trading databases are. This information is stored in the file constants.php in the MT4Analysis lib directory. Using your favorite editor, edit this file and look near the end of the file for this line:
define('TRADE_PATH_LOCAL', '/fl/mt4analysis/db/my');
Change the second part of the define, the part that says /fl/mt4analysis/db/my (in the above example) to your database trade path. Specify all path separators with the forward slash (/) even when using WampServer. For example:
define('TRADE_PATH_LOCAL', '/wamp/www/db/my');
The above example would be used with a default Microsoft Windows/ WampServer installation and this:
define('TRADE_PATH_LOCAL', '/var/www/db/my');
Would be used with a default Ubuntu Linux installation.
Each time you want to create a new database, you should copy the skeleton database (skeleton.db) in the db directory to the directory where your trade databases are located. This ensures that you are starting with a database that has all the proper tables in place.
MT4Analysis stores the configuration for the following:
Each database may contain many accounts, brokers etc. Each part of the MT4Analysis configuration contains a management screen or page where you may add/change or delete configuration items. The main configuration/ management screen is here. From this main management screen, you can change any part of the configuration.
Since some parts of the configuration depend and refer to other parts, the order of configuration is important. When starting from the beginning, the proper configuration order is:
Before discussing each of the configuration/management screens in the order presented above, the main configuration/management screen needs to be presented.
This screen has a number of functions:
The database file name drop down should contain a list of all your available trading databases. If it does not, the database trade path has not been properly registered or you have not copied your first database.
Once the database file name has been selected, you will see that you may now add new accounts, brokers, currency pair sets and expert advisors.
To permanently save a relationship between an account name and the broker, currency pair set just select the database file name, the account name, the broker name, the currency pair set name and then press the save configuration button.
You will notice that as you select an existing item from a drop-down, the change and delete action items will appear.
A currency pair set is the name of a collection of currency pair names that apply to one or more brokers.
For example, in the skeleton database, I created two currency pair sets. The first one called standard lot which contains a list of currency pairs with the usual/normal names (for example: EURUSD, USDCAD etc) and the second one called ibfx mini which contains a list of currency pair names that the IBFX broker uses with it's mini account (for example: EURUSDM, USDCADM). Notice the m at the end of every currency pair name in the ibfx mini set example.
The broker screen stores all configuration information specific to a single broker. There are two acceptable broker configuration profiles. With or without daylight saving time (DST) information.
The only mandatory screen items are the description, the currency pair set name and the normal time (non-DST) offset. Accurate time shifting depends on the correctness of the broker profile. Note that the broker time and time zone is not referring to the broker's office but the time/time zone used by Metatrader when using the broker account.
The broker screen starts with the broker name/description followed by the currency pair set name (which must already exist).
The next series of screen items involve the broker time zone and DST. The normal time offset is the number of hours the broker clock differs from UTC/GMT time during the time of year when daylight saving time is not in effect. If your broker does not use daylight saving time, you can skip the rest of the time items on the screen and go straight to five decimal prices.
If your broker does use daylight saving time, you will need to fill in the start date (month and day) of normal time (non-DST), the start time of normal time (non-DST), the clock offset (relative to UTC/GMT) when DST is in effect, the start date that DST comes into effect and also the start DST time. All of this time and time zone information can be obtained from time and date.com.
The last item on the broker screen is five decimal prices which is an indicator of whether the broker uses five decimal prices in its price quotes and statement data.
The account screen allows you to add, change or delete MT4Analysis accounts. Every closed trade in the database will belong to a specific account. You can have one account per database (which is the way I prefer to organize my trade data) or you can have a single database with many accounts.
The account screen starts with the account description and then the broker that this account uses. This implies that the broker must already exist before you create the account.
This screen stores all currency pair names that belong to a specific currency pair set. The currency pair set must already exist before you can add currency pairs to it.
This screen stores all expert advisors in use by the MT4Analysis account. The EA description is supplied as well as a fragment of an SQL statement that will identify all of the closed trades that belong to the EA. Here are some examples of the magic SQL statement:
magic = 80
Match the magic number 80 exactly.
magic LIKE '8%'
Match any magic number starting with 8.
(magic LIKE '51%' OR magic LIKE '61%')
Match any magic number that starts with 51 or starts with 61. Note that the brackets are required becaused of the or.
This screen allows you to change the magic number and/or the comment of any closed trade.
First, set the database file name to the desired database. Then set the account name within the database followed by the currency pair and the trade ticket. At each step, the drop-down list will be qualified by the selections of the previous drop-downs.
Once you locate the closed trade in question, you can then change the trade magic number and/or the trade comment. Once you are done making the necessary changes, just press the change button.
The Php CLI program that reads the MetaTrader statement files (normal or detailed) and loads the closed trade data into the database has the file name mt4_sqlite_loader.php. The program should normally be run from a scheduled task (cron job in Unix, Linux or Mac OS X). Here is the program syntax:
php mt4_sqlite_loader.php db account statement
Where db is the database path and file name, account is the name of a valid account in the database and statement is the path and file name of the MetaTrader statement file.
Note that the path and file name of the MetaTrader statement file may be something other than a disk file. I am using Php's fopen function to open the statement file which supports all kinds of what Php calls wrappers including http, ftp, compressed data streams and others (please refer to the fopen reference). Here is an example:
\wamp\bin\php\php5.3.0\php mt4_sqlite_loader.php db/my/live.db Live http://mt4.live.future-lab.com
The above command is what I run on a Windows PC that is using WampServer. Note that the MetaTrader statement file is obtained from a web server instead of a disk file.
Note that the input MetaTrader statement file must be the original one generated by MetaTrader, not a modified version presented by some statement hosting services. In addition, some brokers have modified the MetaTrader statement by adding a pip column or a column with the magic number. These modified versions are not compatible with this program (unless the program were to be modified). If you are in doubt, my statement file is here. Compare this statement to yours. The column names, the number of columns and the column order must be identical.
The SQL CLI allows you to interact with Mt4Analysis database(s) on the command line level. Any valid SQLite SQL command can be issued. A good SQL tutorial is here.
Before you can start issuing SQL commands, the database file name and the account name need to be selected from the drop-down lists.
Just type the SQL command and press the run command button.
All dates and times are placed into the SQLite database in the UTC/GMT timezone to easily facilitate timezone shifting. To shift open/close dates/times in the server timezone use:
Also refer to SQL As Understood by SQLite Date and Time Functions
Mt4analysis contains a number of what SQLite calls UDF's or user defined functions. I prefer to refer to them as calculated or symbolic fields.
Calculated fields are functions of Php code that provide extra fields in the database. Unlike real database fields, calculated database fields do not store anything in the database. Typically, calculated fields obtain one or more database fields and calculate a new value.
UDF's that you can use (closedtrade table only):
Mt4Analysis stores all of it's data including configuration and trades in an SQLite database. The Mt4Analysis database consists of a single SQLite version 3 file. There are a number of database tables contained inside the file. Here is the current database layout or schema in alphabetical order by table name:
Each table in the database stores part of the project's information, either configuration or trade data.
Database tables contain rows of data which are called rows or records. Each record contains a series of items called fields.
It is a requirement of SQL that every record in each table has a unique identifier. This is referred to as the key field. This is why in the above table and field list, the first field in each table is the key field. In this project the key field is typically a is a numeric (integer) sequential counter which I refer to as the ID.
Here is a detailed discussion of each of the database tables:
This table stores a list of all defined accounts including the account ID, the account name or description and the ID of the broker.
The broker table contains all the configuration details specific to a single broker.
Most of the fields deal with the broker time zone configuration which is the time zone of the broker. All time offsets are expressed relative to UTC/GMT time. In addition to the time offset, the start date and end date of daylight saving time (DST) is stored.
Fields in the broker table include the broker ID, the broker name or description, the currency pair set ID, the normal time (non-DST) offset, the normal time start date (in yyyymm format), the normal time start time (in hhmm format), the DST time offset, the DST time start date (in yyyymm format), the DST time start time (in hhmm format) and a boolean flag indicating whether the broker's prices are in five decimal format.
The broker configuration section fully describes the broker management/configuration screen and the fields that need to be filled in.
This table stores all of the closed trade detail. Each record represents one closed trade. This table is different than most in the sense that the key field is not an ID. The key field is the trade ticket number which is a unique number (integer) assigned by the broker when the trade is opened.
The first field is the ticket followed by the open and close date/time combination. The trade date and time are stored together in a single field as per the ISO 8601 international standard. The full format of the trade date/time combination is:
yyyy-mm-dd hh:mm:ss
Note that there is an intentional space between the date and time. Also note that the seconds are always zero in this project as Metatrader does not publish the seconds in the trading statement.
Both the open and close date/time are adjusted to the UTC/GMT time zone by the statement loader program. This conversion provides a common date/time base which makes time shifting possible. In order for time shifting to properly function, the broker configuration must be as complete and accurate as possible.
After the open and close date/time fields, the closedtrade table contains the transaction type which is an ID from the transactionType table followed by the trade lot size, the currency pair which is an ID from the currencyPair table, the currency pair set which is an ID from the currencyPairSet table, the trade open price, the stop loss price, the take profit price, the close price, the amount of any rollover/swap, the gross profit/loss (before the rollover/swap), the EA comment, the EA magic number, the broker which is an ID from the broker table and the account which is an ID from the account table.
This table stores a list of currency pair name and ID's. The first field is the ID of the pair followed by the name/description of the pair and finally which currency pair set (as an ID) this pair belongs to.
This table stores a list of all currency pair sets. A currency pair set is a collection of currency pairs. The set may be associated with a particular broker.
This table consists of the fields ID and description.
This table contains a list of all EA's in use. The first field is the ID followed by its description and finally an SQL statement designed to locate the trades of this EA.
This table stores a list of all acceptable transaction types (eg: buy, sell). The first field is the ID followed by the description.