<?php
/* MT4Analysis - Main Documentation Page
   Copyright (C) 2009-2010, Future Lab.

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, get a copy via the Internet at
   http://gnu.org/copyleft/gpl.html or write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston,
   MA 02111-1307 USA

   You can contact the author via email at rsmereka@future-lab.com */

// initialize session
session_start();

require '../lib/constants.php';
require '../lib/mt4analysis.php';
require '../lib/parse.php';

$_SESSION['rpath_2_root'] = '..';
?>
<HTML>
<HEAD>
<TITLE>MT4Analysis Project Documentation</TITLE>
</HEAD>
<BODY>
<A NAME="top"></A>
<CENTER><H1>MT4Analysis Project Documentation</H1></CENTER>
<A NAME="introduction"></A>
<CENTER><H2>Introduction</H2></CENTER>
<P><B>MT4Analysis</B> is a collection of programs built into a system or
project that will (eventually) deliver end-to-end analysis of 
<A HREF="http://www.metaquotes.net/">MetaTrader</A> V4 
trading accounts.</P>
<P>The backend consists of a program to parse and load 
<I>MetaTrader</I> V4 statement files into a 
<A HREF="dbtermon.html">database</A>.
The frontend will consist of a series of 
programs to analyze and present various trading performance statistics.</P>
<P>I have chosen to write this project using the 
<A HREF="http://en.wikipedia.org/wiki/PHP">Php</A>,
<A HREF="http://en.wikipedia.org/wiki/JavaScript">JavaScript</A> and
<A HREF="http://en.wikipedia.org/wiki/Html">HTML</A> languages with
<A HREF="http://en.wikipedia.org/wiki/SQLite">SQLite</A> version 3 as the 
<I>database</I>.</P>
<P>Note that <I>Php</I> is a 
<A HREF="http://en.wikipedia.org/wiki/Server-side_scripting">server-side 
scripting</A> language which means that the bulk of the processing takes
place on a 
<A HREF="http://en.wikipedia.org/wiki/Web_server">web server</A>, not on 
your 
<A HREF="http://en.wikipedia.org/wiki/Web_browser">web browser</A>. 
This also means that in order
to use <B>MT4Analysis</B>, you will need a 
<I>web server</I>.</P>
<H3>Document Conventions</H3>
<P>Note that this document will not repeat an external
<A HREF="http://en.wikipedia.org/wiki/Hyperlink">hyperlink</A> (abbreviated
to <B>link</B>). An <B>external</B> link is a link that refers to another
website. The very first time the word or phrase is used, it will
appear as a <I>link</I> and each subsequent reference will appear in
<I>italics</I>. A complete alphabetical list of all external <I>links</I> this 
document referrs to is contained at the bottom of this document.</P>
<A NAME="whyme"></A>
<CENTER><H2>Why Use MT4Analysis</H2></CENTER>
<P>Since there are a number of free Internet-based 
<I>MetaTrader</I> statement
hosting services already available, why should you use <B>MT4Analysis</B>?</P>
<P><B>MT4Analysis</B> has several important abilities and advantages that 
none of the current Internet-based
<I>MetaTrader</I> statement hosting services have:</P>
<H3>Permanent record of all closed trades</H3>
<P>All the current Internet-based
<I>MetaTrader</I> statement hosting services are geared towards your <B>current</B>
<I>MetaTrader</I> statement file. When your broker decides to wipe your 
trade record, all of your hosting service trades will be lost.</P>
<P><B>MT4Analysis</B> loads all of your closed trades into a 
<I>database</I> and this record will survive any broker statement rollover.</P>
<H3><A HREF="http://www.metaquotes.net/en/metatrader4/automated_trading">EA</A> Analysis</H3>
<P>Not one of the current Internet-based
<I>MetaTrader</I> statement hosting services is currently capable of 
presenting trade analysis on an <I>expert advisor (EA)</I> basis. Some 
services will present trade analysis on a single currency pair but that 
is it.</P>
<P><B>MT4Analysis</B> can provide trade analysis based on an entire
account, an <I>EA</I> trading single or multiple currency pairs or a single
currency pair.</P>
<H3><A HREF="#time_shifting">Time shifting</A></H3>
<P>All of the current Internet-based
<I>MetaTrader</I> statement hosting services present trade dates and 
times only as they are
recorded in the <I>MetaTrader</I> statement
file.</P>
<P>Trade dates and times in <B>MT4Analysis</B> can be displayed in any time
zone.</P>
<A NAME="status"></A>
<CENTER><H2>Current Project Status</H2></CENTER>
<P><B>Version</B>: <?php echo(MT4A_VERSION); ?></P>
<P>This project is currently in an alpha stage. Some parts are working
well and other parts are buggy. Here is the current breakdown:</P>
<UL>
<LI><A HREF="#mt4_sqlite_loader">mt4_sqlite_loader.php</A> parse and 
statement load program, working well
<LI>configuration management screens - working well
<LI>project command line functionality - working well
<LI><I>database</I> - fully implemented, 
<A HREF="http://en.wikipedia.org/wiki/Database_schema">schema</A> stable
<LI><I>database</I> calculated <A HREF="dbtermon.html#field">fields</A> 
(<I>SQLite</I> calls them 
<A HREF="http://etutorials.org/Server+Administration/upgrading+php+5/Chapter+4.+SQLite/4.6+User-Defined+Functions/">
UDF's</A> [user defined functions]) - working well except <B>tradetime</B>
which is currently under development and is buggy
<LI>documentation - incomplete
<LI>text reports - not implemented yet
<LI>graphical reports - not implemented yet
</UL>
<A NAME="requirements"></A>
<CENTER><H2>Requirements</H2></CENTER>
<P>Here is a list of the software requirements:</P>
<UL>
<LI><I>web server</I> with <I>Php</I> V5 module
<LI><A HREF="http://www.php-cli.com/">Php CLI</A> 
capabilities
<LI><A HREF="http://php.net/manual/en/book.pdo.php">Php PDO</A> extension
(built into <I>Php</I> core in recent versions of <I>Php</I>)
<LI><A HREF="http://ca.php.net/pdo-sqlite">Php PDO SQLite V3</A> extension
<LI><A HREF="http://www.sqlite.org/sqlite.html">SQLite CLI</A>
(optional but highly recommended)
</UL>
<P>Since all the above components are open source and multi-platform,
you may run <B>MT4Analysis</B> on any platform that supports all the
software components including:</P>
<UL>
<LI><A HREF="http://en.wikipedia.org/wiki/Linux">Linux</A> (I use and 
recommend <A HREF="http://www.ubuntu.com">Ubuntu</A>)
<LI><A HREF="http://www.freebsd.org/">FreeBSD</A>
<LI><A HREF="http://www.apple.com/macosx">Mac OS X</A>
<LI><A HREF="http://www.microsoft.com/windows">Microsoft Windows</A>
</UL>
<P>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
<I>Metatrader</I> runs on, which
is <I>Microsoft Windows</I>.</P>
<P>The really good news is that there is a <I>Windows</I> distribution of 
<A HREF="http://en.wikipedia.org/wiki/LAMP_(software_bundle)">LAMP</A>
called 
<A HREF="http://www.wampserver.com/en/">WampServer</A> that works right 
out of the box without any configuration.</P>
<A NAME="quickstart"></A>
<CENTER><H2>Quick Start</H2></CENTER>
<P>This quick start assumes that you will be using 
<I>Microsoft Windows</I> along with 
<I>WampServer</I> as your <B>MT4Analysis</B> platform. Please read the 
important notes below the
instructions before you start.</P>
<P>To get up and running as fast as possible, just follow these steps:</P>
<UL>
<LI>download and install 
<I>WampServer</I> using all the default settings
<LI><A HREF="http://www.sqlite.org/download.html">download</A> and extract
the <I>SQLite CLI</I>
(optional but <B>highly</B> recommended)
<LI><A HREF="#phptimezoneset">Set the <I>Php</I> timezone</A>
<LI><A HREF="#phptest">Test Php</A> (optional but <B>highly recommended</B>)
<LI>download and de-compress/<A HREF="#package_install">install</A> the latest
<A HREF="http://www.future-lab.com/download">MT4Analysis package</A> into the
directory <B>c:\wamp\www</B>
<LI>create a sub-directory to hold your account 
<I>database(s)</I>
and name it <B>c:\wamp\www\db\my</B>
<LI><A HREF="#copydb">Copy and rename the skeleton</A> 
<I>database</I>
<B>c:\wamp\www\db\skeleton.db</B>
to <B>c:\wamp\www\db\my\live.db</B> to represent your live account or
<B>c:\wamp\www\db\my\demo.db</B> to represent your demo account
<LI><A HREF="#registertradepath">register your database trade path with
MT4Analysis</A>
<LI>verify the <B>MT4Analysis</B> 
<A HREF="#config">configuration</A> by making sure your account,
broker, currency pair set name, currency pairs and <I>EA's</I> are defined
<LI><A HREF="#statementfile">Make your statement file available to
Mt4Analysis</A>
<LI><A HREF="#mt4_sqlite_loader.php">run the statement loading program</A>
<LI>Use the <B>Mt4Analysis</B> <A HREF="../misc/command_line.php">command
line interface</A> to query the 
<I>database</I> using 
<A HREF="http://en.wikipedia.org/wiki/SQL">SQL</A>
<LI>Enjoy!
</UL>
<H3>Important Notes</H3>
<P>If you are already using 
<I>WampServer</I> and already
have it installed or you want to isolate the <B>MT4Analysis</B> files
in the <B>www</B> directory, just create the sub-directory
<B>c:\wamp\www\mt4analysis</B> before de-compressing the <B>MT4Analysis</B>
files and add the <B>mt4analysis</B> directory to each of the paths
described above (eg: <B>c:\wamp\www\mt4analysis\db</B>).</P>
<P>If you are at all uncomfortable with the above steps, please read the
entire documentation before proceeding.</P>
<P>Warning: Do not put this <B>MT4Analysis</B> setup on the Internet. The
above setup is designed for ease, not security. If you plan to offer your
<B>Mt4Analysis</B> data on the Internet, there are a number of steps
that must be done in order to secure your data and installation.</P>
<A NAME="meeting_requirements"></A>
<CENTER><H2>Meeting the Software Requirements</H2></CENTER>
<P>The first step in running <B>MT4Analysis</B>, is making sure you meet
the 
<A HREF="#requirements">software requirements</A>.
If you believe that you already meet the software requirements,
you can skip to <A HREF="#phptest">testing Php</A>.</P>
<A NAME="install_req_windows"></A>
<H3>Windows Installation</H3>
<P>As mentioned in the <A HREF="#quickstart">quick start</A>, the fastest
and easiest way to install and configure a
<I>web server</I> is to use <I>Microsoft Windows</I> to
run your <I>web server</I>. Just download and install
<I>WampServer</I> and during the install, accept all the default settings.</P>
<P>This will install the 
<A HREF="http://en.wikipedia.org/wiki/Apache_HTTP_Server">Apache2</A>
<I>web server</I>, the
<A HREF="http://en.wikipedia.org/wiki/MySQL">MySQL</A> <I>database</I> server
(not needed for this project at the moment but handy to have around),
the <I>Php</I> language, the <I>Php CLI</I>, the
<I>Php PDO extension</I> and the <I>Php PDO extension for SQLite</I>.</P>
<P>You can then download and extract the
<I>SQLite CLI</I>.</P>
<P>If the speed of the 
<I>web server</I>
and security are more important than the ease and speed of the installation,
I highly recommend that you install your
<I>web server</I> under the <I>Linux</I>
operating system. This is especially important in terms of security if
you plan on putting your <B>MT4Analysis</B> server live on the
<B>Internet</B>. The specific flavour of
<I>Linux</I> I am partial to and
recommend is <I>Ubuntu</I>.</P>
<A NAME="install_req_linux"></A>
<H3><I>Linux</I> Installation</H3>
<P>This section will assume that you are installing the required software
from <I>Ubuntu</I>
<I>Linux</I>.</P>
<P>Start a <B>terminal</B>/shell/command prompt and type:</P>
<PRE>   sudo apt-get -y install mysql-server apache2 libapache2-mod-php5
                                php5 php5-mysql php5-cli php5-sqlite
                                sqlite3</PRE>
<P>This will download and install all the required software. In my testing
of the <I>Ubuntu</I> install, I found that the <I>Apache2</I> <I>web
server</I> would not process <I>Php</I> files until it was restarted with:</P>
<PRE>   sudo /etc/init.d/apache2 restart</PRE>
<A NAME="phptest"></A>
<H3>Test Php</H3>
<P>At this point, it is a good idea to test your
<I>web server</I> and <I>Php</I> installation.
I am assuming that you will be testing the installation from the
server itself.</P>
<P>Use your favorite editor and compose the following file:</P>
<PRE>   &lt;HTML&gt
   &lt;HEAD&gt
   &lt;TITLE&gt;Php Info&lt/TITLE&gt
   &lt;/HEAD&gt
   &lt;BODY&gt
   &lt?php phpinfo(); ?&gt
   &lt/BODY&gt
   &lt/HTML&gt</PRE>
<P>Save the file with the name <B>info.php</B> and place this file
into your <I>web server</I>s' <B>www</B> directory (if using
<I>Wamp</I> this is C:\WAMP\WWW, if using <I>Ubuntu Linix</I> this is
/var/www).</P>
<P>Now launch your favorite browser and type this into the address
line:</P>
<PRE>   http://localhost/info.php</PRE>
<P>You should see a page in your browser similiar to
<A HREF="phpinfo.html">this</A>.</P>This test page was taken from a
<A HREF="http://en.wikipedia.org/wiki/Microsoft_Windows">Windows</A>
server running <I>WampServer</I>.
If the page appears properly, the
<I>web server</I> and <I>Php</I> are functioning.
Scan the output page and locate the section entitled <B>PDO</B> (the
section title will be centered on the screen). This section will list
all of the <I>PDO drivers</I> installed. Make sure that the 
<I>SQLite PDO driver</I> is listed.</P>
<A NAME="package_install"></A>
<CENTER><H2>MT4Analysis Installation</H2></CENTER>
<P>This section assumes that you already have a <I>web server</I> installed and 
running all of the <A HREF="#requirements">requirements</A>. If this is not 
the case, go back and read the previous two sections.</P>
<P>To install <B>MT4Analysis</B>, just de-compress the <B>MT4Analysis</B> 
package into the directory of your choice.</P>
<P>If <B>MT4Analysis</B> will be the only application served-up
by your <I>web server</I>, just de-compress the <B>MT4Analysis</B> package 
into the main <I>web server</I> document
directory (eg: <B>c:\wamp\www</B> if using
<I>WampServer</I>, <B>/var/www</B> if using <I>Apache</I> <I>web server</I> 
under <I>Ubuntu Linux</I>).</P>
<P>If <B>MT4Analysis</B> will have to co-exist with other web applications,
it is best to create a directory specifically for <B>MT4Analysis</B> right
inside the main web directory (eg: <B>c:\wamp\www\mt4analysis</B> if using
<I>WampServer</I>, <B>/var/www/mt4analysis</B> if
using <I>Apache</I> <I>web server</I> under 
<I>Ubuntu Linux</I>).</P>
<P>Once the base <B>MT4Analysis</B> software is in place, you will need to
create a directory especially for your trading <I>databases</I>. If you 
are using <I>WampServer</I>, I 
suggest that you create the directory <B>c:\wamp\www\db\my</B>. If you 
are using <I>Ubuntu Linux</I>, I suggest that you
create the directory <B>/var/www/db/my</B>.</P>
<P>Once a <I>database</I> directory has been created, copy the 
<B>skeleton.db</B> datbase file from the install package <B>db</B> directory 
into your new database directory and name this file in accordance with your 
trading account (eg: <B>demo.db</B>, <B>live.db</B>). It is not mandatory 
that you use a <B>.db</B> file extension, but I recommend it for clarity.</P>
<P>Make sure you remember your trading <I>database</I> path or write it down.
You will need it during the configuration phase.</P>
<A NAME="phptimezoneset">
<H3>Setting the <I>Php</I> Timezone</H3>
<P>In order for <A HREF="#time_shifting">time shifting</A> to accurately take
place, <I>Php</I> needs to know the current time zone.</P>
<P>The main <I>Php</I> configuration file called <B>php.ini</B> needs to be
edited. If you have installed <I>WampServer</I> you can edit the <B>php.ini</B>
file by clicking (left click) on the <I>WampServer</I> icon in the system task 
tray (bottom-right of the screen) and choose the option <I>Php</I> and then
choose <B>php.ini</B> which will load the <B>php.ini</B> file into the
<B>Notepad</B> editor.</P>
<P>Search for <B>[Date]</B> and change the <B>date.timezone</B>. The possible
combination of time zones are listed 
<A HREF="http://php.net/manual/en/timezones.php">here</A>.</P>
<A NAME="config"></A>
<CENTER><H2>MT4Analysis Configuration and Management</H2></CENTER>
<P>Right after the <B>MT4Analysis</B> package is installed, you will
need to register your <I>database</I> trade path with <B>MT4Analysis</B>.</P>
<A NAME="registertradepath"</A>
<H3>Register Your <I>Database</I> Trade Path</H3>
<P><B>MT4Analysis</B> needs to know where the trading <I>databases</I> are. This
information is stored in the file <B>constants.php</B> in the 
<B>MT4Analysis</B> <B>lib</B> directory. Using your favorite editor, edit
this file and look near the end of the file for this line:</P>
<PRE>   define('TRADE_PATH_LOCAL', '/fl/mt4analysis/db/my');</PRE>
<P>Change the second part of the <B>define</B>, the part that says
<B>/fl/mt4analysis/db/my</B> (in the above example) to your <I>database</I> 
trade path. Specify all path
separators with the forward slash (<B>/</B>) even when using 
<I>WampServer</I>. For example:</P>
<PRE>   define('TRADE_PATH_LOCAL', '/wamp/www/db/my');</PRE>
<P>The above example would be used with a default 
<I>Microsoft Windows</I>/ <I>WampServer</I> installation and
this:</P>
<PRE>   define('TRADE_PATH_LOCAL', '/var/www/db/my');</PRE>
<P>Would be used with a default
<I>Ubuntu Linux</I> installation.</P>
<A NAME="copydb"></A>
<H3>Starting With a New <I>Database</I> - Copying From the Skeleton</H3>
<P>Each time you want to create a new 
<I>database</I>, you should copy the <B>skeleton</B> <I>database</I> 
(skeleton.db) in the <B>db</B> directory to the directory where your trade 
<I>databases</I> are located. This ensures that you
are starting with a <I>database</I> that has all the
proper tables in place.</P>
<A NAME="config_intro"></A>
<H3>Configuration Introduction</H3>
<P><B>MT4Analysis</B> stores the configuration for the following:</P>
<UL>
<LI>Accounts
<LI>Brokers
<LI>Currency Pair Sets
<LI>Currency Pairs
<LI><I>EAs</I>
</UL>
<P>Each <I>database</I> may contain many accounts, brokers etc. Each part of
the <B>MT4Analysis</B> configuration contains a management screen or page
where you may add/change or delete configuration items. The main configuration/
management screen is <A HREF="../manage/index.php">here</A>. From this main
management screen, you can change any part of the configuration.</P>
<P>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:</P>
<UL>
<LI>currency pair set
<LI>broker
<LI>account
<LI>currency pairs
<LI><I>eas</I>
</UL>
<P>Before discussing each of the configuration/management screens in the
order presented above, the main configuration/management screen needs to be 
presented.</P>
<A NAME="config_main">
<H3>Configuration/Management Main Screen</H3>
<P>This screen has a number of functions:</P>
<UL>
<LI>select/change the current database
<LI>add/change/delete accounts, brokers, currency pair set names, 
currency pairs or <I>expert advisors (eas)</I>
<LI>establish and save the relationship between the account name and all the
other configuration items
</UL>
<P>The <B><I>database</I> file name</B> drop down should contain a list of all
your available trading <I>databases</I>. If it does not, the <I>database</I> 
trade path has not been properly <A HREF="#registertradepath">registered</A> 
or you have not <A HREF="#copydb">copied your first <I>database</I></A>.</P>
<P>Once the <I>database</I> file name has been selected, you will see that
you may now add new accounts, brokers, currency pair sets and expert 
advisors.</P>
<P>To permanently save a relationship between an account name and the broker,
currency pair set just select the <B><I>database</I> file name</B>, the
account name, the broker name, the currency pair set name and then press
the <B>save configuration</B> button.</P>
<P>You will notice that as you select an existing item from a drop-down,
the <B>change</B> and <B>delete</B> action items will appear.</P>
<A NAME="config_currency_pair_set">
<H3>Currency Pair Set Screen</H3>
<P>A <B>currency pair set</B> is the name of a collection of currency 
pair names that apply to one or more brokers.</P>
<P>For example, in the skeleton 
<I>database</I>, I created two currency pair sets. The first one called 
<B>standard lot</B>
which contains a list of currency pairs with the usual/normal names
(for example: EURUSD, USDCAD etc) and the second one called <B>ibfx mini</B>
which contains a list of currency pair names that the
<A HREF="http://www.ibfx.com">IBFX</A> broker uses with it's mini account
(for example: EURUSDM, USDCADM). Notice the <B>m</B> at the end of
every currency pair name in the <B>ibfx mini</B> set example.</P>
<A NAME="config_broker"></A>
<H3>Broker Screen</H3>
<P>The broker screen stores all configuration information specific to a
single broker. There are two acceptable broker configuration profiles.
With or without 
<A HREF="http://en.wikipedia.org/wiki/Daylight_saving_time">daylight
saving time (DST)</A> information.</P>
<P>
<P>The only mandatory screen items are the description, the currency pair set name
and the normal time (non-DST) offset. Accurate 
<A HREF="#time_shifting">time shifting</A> 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 
<I>Metatrader</I> when using the broker account.</P>
<P>The broker screen starts with the broker name/description followed by
the <A HREF="#config_currency_pair_set">currency pair set</A> name (which
must already exist).</P>
<P>The next series of screen items involve the broker time zone and
<I>DST</I>. The <B>normal time offset</B> is the number of hours the broker
clock differs from <A HREF="http://en.wikipedia.org/wiki/GMT_Time">UTC/GMT</A>
time during the time of year when
<I>daylight saving time</I> is not in effect. If your broker does not use
<I>daylight saving time</I>, you can skip the rest of the time items on the
screen and go straight to <B>five decimal prices</B>.</P>
<P>If your broker does use <I>daylight saving time</I>, 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 <I>UTC/GMT</I>) when <I>DST</I> 
is in effect, the start date that <I>DST</I> comes into effect and also the start 
<I>DST</I> time. All of this time and time zone information can be obtained
from <A HREF="http://www.timeanddate.com">time and date.com</A>.</P>
<P>The last item on the broker screen is <B>five decimal prices</B> which is an
indicator of whether the broker uses five decimal prices in its price quotes and
statement data.</P>
<A NAME="config_account"></A>
<H3>Account Screen</H3>
<P>The account screen allows you to add, change or delete <B>MT4Analysis</B> 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.</P>
<P>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.</P>
<A NAME="config_currency_pair"></A>
<H3>Currency Pair Screen</H3>
<P>This screen stores all currency pair names that belong to a specific
<A HREF="#config_currency_pair_set">currency pair set</A>. The
<A HREF="#config_currency_pair_set">currency pair set</A> must already exist before
you can add currency pairs to it.</P>
<A NAME="config_ea"></A>
<H3><I>Expert Advisor</I> Screen</H3>
<P>This screen stores all <I>expert advisors</I> in use by the <B>MT4Analysis</B>
<A HREF="#config_account">account</A>. The <I>EA</I> description is supplied as
well as a fragment of an <I>SQL</I> statement that will identify all of the closed
trades that belong to the <I>EA</I>. Here are some examples of the magic <I>SQL</I>
statement:
<PRE>   magic = 80</PRE>
<P>Match the magic number 80 exactly.</P>
<PRE>   magic LIKE '8%'</PRE>
<P>Match any magic number starting with 8.</P>
<PRE>   (magic LIKE '51%' OR magic LIKE '61%')</PRE>
<P>Match any magic number that starts with 51 or starts with 61. Note that the
brackets are required becaused of the <B>or</B>.</P>
<A NAME="change_magic"></A>
<H3>Change Closed Trade Magic/Comment Screen</H3>
<P>This screen allows you to change the magic number and/or the
comment of any closed trade.</P>
<P>First, set the <B>database file name</B> to the desired database. Then 
set the <B>account name</B> within the database followed by the <B>currency 
pair</B> and the trade <B>ticket</B>. At each step, the drop-down list will 
be qualified by the selections of the previous drop-downs.</P>
<P>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 <B>change</B> button.</P>
<A NAME="mt4_sqlite_loader"></A>
<CENTER><H2>MetaTrader Statement Parse and Load Program</H2></CENTER>
<P>The <I>Php CLI</I> program that reads the <I>MetaTrader</I> statement
files (normal or detailed) and loads the closed trade data into the
database has the file name <B>mt4_sqlite_loader.php</B>.
The program should normally be run from a scheduled task
(<A HREF="http://en.wikipedia.org/wiki/Cron">cron</A> job in <I>Unix</I>,
<I>Linux</I> or <I>Mac OS X</I>). Here is the program syntax:</P>
<PRE>php mt4_sqlite_loader.php db account statement</PRE>
<P>Where <B>db</B> is the database path and file name, <B>account</B> is
the name of a valid account in the database and <B>statement</B> is the
path and file name of the <I>MetaTrader</I> statement file.</P>
<P>Note that the path and file name of the <I>MetaTrader</I> statement file
may be something other than a disk file. I am using <I>Php</I>'s <B>fopen</B>
function to open the statement file which supports all kinds of what
<I>Php</I> calls <B>wrappers</B> including <B>http</B>, <B>ftp</B>,
compressed data streams and others (please refer to the 
<A HREF="http://php.net/manual/en/function.fopen.php">fopen 
reference</A>). Here is an example:</P>
<PRE>\wamp\bin\php\php5.3.0\php mt4_sqlite_loader.php db/my/live.db Live http://mt4.live.future-lab.com</PRE>
<P>The above command is what I run on a <I>Windows</I> PC that is using
<I>WampServer</I>. Note that the <I>MetaTrader</I> statement file is
obtained from a <I>web server</I> instead of a disk file.</P>
<P>Note that the input <I>MetaTrader</I> statement file must be the
original one generated by <I>MetaTrader</I>, not a modified version
presented by some statement hosting services. In addition, some brokers
have modified the <I>MetaTrader</I> 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 
<A HREF="http://mt4.live.future-lab.com">here</A>. Compare this statement
to yours. The column names, the number of columns and the column order must 
be identical.</P>
<A NAME="cli"></A>
<CENTER><H2><I>SQL Command Line Interface (CLI)</I></H2></CENTER>
<P>The <I>SQL CLI</I> allows you to interact with <B>Mt4Analysis</B> 
database(s) on the command line level. Any valid <I>SQLite SQL</I> command 
can be issued. A good <I>SQL</I> tutorial is
<A HREF="http://www.w3schools.com/sql/default.asp">here</A>.</P>
<P>Before you can start issuing <I>SQL</I> commands, the <B>database file
name</B> and the <B>account name</B> need to be selected from the
drop-down lists.</P>
<P>Just type the <I>SQL</I> command and press the <B>run command</B>
button.</P>
<P>All dates and times are placed into the <I>SQLite</I> database in the
<I>UTC/GMT</I> timezone to easily facilitate timezone shifting. To shift
open/close dates/times in the server timezone use:</P>
<UL>
<LI>Both date and time
<UL>
<LI>datetime(opendatime, 'localtime')
<LI>datetime(closedatime, 'localtime')
</UL>
<LI>Date only
<UL>
<LI>date(opendatime, 'localtime')
<LI>date(closedatime, 'localtime')
</UL>
<LI>Time only
<UL>
<LI>time(opendatime, 'localtime')
<LI>time(closedatime, 'localtime')
</UL>
</UL>
<P>Also refer to <A HREF="http://www.sqlite.org/lang_datefunc.html">
SQL As Understood by SQLite Date and Time Functions</A></P>
<A NAME="udf"></A>
<CENTER><H2><I>UDF's</I> (User Defined Functions)</H2></CENTER>
<P><B>Mt4analysis</B> contains a number of what <I>SQLite</I> calls 
<I>UDF's</I> or user defined functions. I prefer to refer to them as
calculated or symbolic <I>fields</I>.</P>
<P>Calculated <I>fields</I> are functions of <I>Php</I> code that
provide extra <I>fields</I> in the database. Unlike real database
<I>fields</I>, calculated database <I>fields</I> do not store
anything in the database. Typically, calculated <I>fields</I>
obtain one or more database <I>fields</I> and calculate a new
value.</P>
<P>UDF's that you can use (<A HREF="#table_closedtrade">closedtrade</A>
table only):</P>
<UL>
<LI>eaid(ticket) - ea id number of the trade
<LI>eadesc(ticket) - ea description/name of the trade
<LI>pips(ticket) - pips won/lost of the trade
<LI>balance(ticket) - account balance as at and including this trade
</UL>
<A NAME="database"></A>
<CENTER><H2><I>Database</I> Design</H2></CENTER>
<P><B>Mt4Analysis</B> stores all of it's data including configuration
and trades in an <I>SQLite</I> <I>database</I>.
The <B>Mt4Analysis</B> 
<I>database</I> consists of a single
<I>SQLite</I> version 3 file. There are a number of <I>database</I>
<I>tables</I> 
contained inside the file. Here is the current
<I>database</I> layout or 
<I>schema</I> in alphabetical order by <I>table</I> 
name:</P>
<A NAME="schema"></A>
<UL>
<LI><A HREF="#table_account">account</A> - account information
<UL>
<LI>id integer primary key
<LI>description text
<LI>broker integer
</UL>
<LI><A HREF="#table_broker">broker</A> - broker information and configuration
<UL>
<LI>id integer primary key
<LI>description text
<LI>currencyPairSet integer
<LI>normTimeOffset integer
<LI>normTimeStartDate text
<LI>normTimeStarTime text
<LI>DSTimeOffset integer
<LI>DSTimeStartDate text
<LI>DSTimeStarTime text
<LI>fiveDecimals integer
</UL>
<LI><A HREF="#table_closedtrade">closedtrade</A> - where all the closed 
trade data is stored
<UL>
<LI>ticket integer primary key
<LI>opendatime text
<LI>closedatime text
<LI>transactiontype integer
<LI>lot real
<LI>currencyPair integer
<LI>currentPairSet integer
<LI>openprice real
<LI>sl real
<LI>tp real
<LI>closeprice real
<LI>rollover real
<LI>pl real
<LI>comment text
<LI>magic text
<LI>broker integer
<LI>account integer
</UL>
<LI><A HREF="#table_currencypair">currencyPair</A> - list or currency pairs 
and which set they belong to
<UL>
<LI>id integer primary key
<LI>description text
<LI>setID integer
</UL>
<LI><A HREF="#table_currencypairset">currencyPairSet</A> - currency pair set 
names
<UL>
<LI>id integer primary key
<LI>description text
</UL>
<LI><A HREF="#table_ea">ea</A> - list of <I>EA's</I> and what their magic 
numbers are
<UL>
<LI>id integer primary key
<LI>description text
<LI>magic text
</UL>
<LI><A HREF="#table_transactiontype">transactionType</A> - trade 
order/transaction names and type numbers
<UL>
<LI>id integer primary key
<LI>description text
</UL>
</UL>
<P>Each <I>table</I> in the <I>database</I> stores part of the project's 
information, either
configuration or trade data.</P>
<P><I>Database</I> 
<I>tables</I> contain rows of data which are 
called <A HREF="dbtermon.html#row">rows or records</A>. 
Each <I>record</I> contains a series of items called
<I>fields</I>.</P>
<P>It is a requirement of <I>SQL</I> that every <I>record</I> in each 
<I>table</I> has a unique identifier. This
is referred to as the <B>key</B> 
<I>field</I>. This is why in the above
<I>table</I> and 
<I>field</I> list, the first 
<I>field</I> in each
<I>table</I> is the <B>key</B> 
<I>field</I>. In this project the <B>key</B>
<I>field</I> is typically a 
is a numeric (integer) sequential counter which I refer to as the 
<B>ID</B>.</P>
<P>Here is a detailed discussion of each of the
<I>database</I> 
<I>tables</I>:</P>
<A NAME="table_account"></A>
<H3>account</H3>
<P>This <I>table</I> stores a list of all
defined accounts including the account <B>ID</B>, the account name or
description and the <B>ID</B> of the <A HREF="#table_broker">broker</A>.</P>
<A NAME="table_broker"></A>
<H3>broker</H3>
<P>The <B>broker</B> <I>table</I> contains
all the configuration details specific to a single <B>broker</B>.</P>
<P>Most of the <I>fields</I> deal with the
<B>broker</B> time zone configuration which is the time zone of the
<B>broker</B>. All time offsets are expressed relative to 
<I>UTC/GMT</I> time.
In addition to the time offset, the start date and end date of
<I>daylight saving time (<B>DST</B>)</I> is stored.</P>
<P><I>Fields</I> in the <B>broker</B>
<I>table</I> include the <B>broker</B> <B>ID</B>, the <B>broker</B> name or 
description, the
<A HREF="table_cpset">currency pair set</A> <B>ID</B>, the normal time
(non-DST) offset, the normal time start date (in yyyymm format), the
normal time start time (in hhmm format), the <B>DST</B> time offset,
the <B>DST</B> time start date (in yyyymm format), the <B>DST</B> time
start time (in hhmm format) and a
<A HREF="http://en.wikipedia.org/wiki/Boolean_datatype">boolean</A> flag
indicating whether the <B>broker</B>'s prices are in five decimal format.</P>
<P>The <A HREF="#config_broker"><B>broker</B> configuration</A> section fully
describes the <B>broker</B> management/configuration screen and the
<I>fields</I> that need to be filled in.</P>
<A NAME="table_closedtrade"></A>
<H3>closedtrade</H3>
<P>This <I>table</I> stores all of the
closed trade detail. Each <I>record</I> represents
one closed trade. This <I>table</I> is
different than most in the sense that the <B>key</B>
<I>field</I> is not an <B>ID</B>. The <B>key</B>
<I>field</I> is the trade <B>ticket</B> number
which is a unique number (integer) assigned by the broker when the trade
is opened.</P>
<P>The first <I>field</I> is the <B>ticket</B>
followed by the open and close date/time combination.
The trade date and time are stored together in a single
<I>field</I> as per the
<A HREF="http://en.wikipedia.org/wiki/ISO_8601">ISO 8601</A> international
standard. The full format of the trade date/time combination is:</P>
<PRE>   yyyy-mm-dd hh:mm:ss</PRE>
<P>Note that there is an intentional space between the date and time. Also
note that the seconds are always zero in this project as
<I>Metatrader</I> does not publish the
seconds in the trading statement.</P>
<P>Both the open and close date/time are adjusted to the 
<I>UTC/GMT</I> time
zone by the <A HREF="#mt4_sqlite_loader">statement loader program</A>. This
conversion provides a common date/time base which makes 
<A HREF="#time_shifting">time shifting</A> possible. In order for
<A HREF="#time_shifting">time shifting</A> to properly function, the
<A HREF="#config_broker">broker configuration</A> must be as complete
and accurate as possible.</P>
<P>After the open and close date/time <I>fields</I>,
the <B>closedtrade</B> <I>table</I> contains the transaction type which is an
<B>ID</B> from the <A HREF="#table_transactiontype">transactionType</A>
<I>table</I> followed by the trade lot size,
the currency pair which is an <B>ID</B> from the
<A HREF="#table_currencypair">currencyPair</A>
<I>table</I>, the 
<A HREF="#config_currencypairset">currency pair set</A> which is an
<B>ID</B> from the <A HREF="#table_currencypairset">currencyPairSet</A>
<I>table</I>, 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
<I>EA</I> comment, the <I>EA</I> <B>magic</B> number, the <B>broker</B>
which is an <B>ID</B> from the <A HREF="#table_broker">broker</A>
<I>table</I> and the <B>account</B> which is
an <B>ID</B> from the <A HREF="#table_account">account</A>
<I>table</I>.</P>
<A NAME="table_currencypair"></A>
<H3>currencyPair</H3>
<P>This <I>table</I> stores a list of currency pair name and <B>ID</B>'s. 
The first <I>field</I> is the <B>ID</B> of the pair
followed by the name/description of the pair and finally which
<A HREF="table_currencypairset">currency pair set</A> (as an <B>ID</B>)
this pair belongs to.</P>
<A NAME="table_currencypairset"></A>
<H3>currencyPairSet</H3>
<P>This <I>table</I> 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 <B>broker</B>.</P>
<P>This <I>table</I> consists of the
<I>fields</I> <B>ID</B> and description.</P>
<A NAME="table_ea"></A>
<H3><I>ea</I></H3>
<P>This <I>table</I> contains a list of all
<I>EA</I>'s in use. The first <I>field</I>
is the <B>ID</B> followed by its description and finally an
<I>SQL</I> statement designed to locate the trades of this <I>EA</I>.</P>
<A NAME="table_transactiontype"></A>
<H3>transactionType</H3>
<P>This <I>table</I> stores a list of all 
acceptable transaction types (eg: buy, sell). The first
<I>field</I> is the <B>ID</B> followed by
the description.</P>
<H1> External References</H1>
<UL>
<LI><A HREF="http://en.wikipedia.org/wiki/Apache_HTTP_Server">Apache2</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Boolean_datatype">boolean</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Command-line_interface">Command-line interface (CLI)</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Cron">Cron</A>
<LI><A HREF="dbtermon.html">database</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Daylight_saving_time">daylight
saving time (DST)</A>
<LI><A HREF="http://www.metaquotes.net/en/metatrader4/automated_trading">
expert advisor (EA)</A>
<LI><A HREF="dbtermon.html#field">field</A>
<LI><A HREF="http://www.freebsd.org/">FreeBSD</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Html">HTML</A>
<LI><A HREF="http://en.wikipedia.org/wiki/ISO_8601">ISO 8601</A>
<LI><A HREF="http://en.wikipedia.org/wiki/JavaScript">JavaScript</A>
<LI><A HREF="http://en.wikipedia.org/wiki/LAMP_(software_bundle)">LAMP</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Linux">Linux</A>
<LI><A HREF="http://www.apple.com/macosx">Mac OS X</A>
<LI><A HREF="http://www.metaquotes.net">MetaTrader</A>
<LI><A HREF="http://www.microsoft.com/windows">Microsoft Windows</A>
<LI><A HREF="http://en.wikipedia.org/wiki/PHP">Php</A>
<LI><A HREF="http://www.php-cli.com/">Php CLI</A>
<LI><A HREF="http://ca2.php.net/pdo">Php PDO</A>
<LI><A HREF="http://ca.php.net/pdo-sqlite">Php PDO SQLite</A>
<LI><A HREF="http://php.net/manual/en/timezones.php">Php Time Zones</A>
<LI><A HREF="dbtermon.html#record">record</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Database_schema">schema</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Server-side_scripting">server-side 
scripting</A>
<LI><A HREF="http://en.wikipedia.org/wiki/SQL">SQL</A>
<LI><A HREF="http://en.wikipedia.org/wiki/SQLite">SQLite</A>
<LI><A HREF="http://www.sqlite.org/sqlite.html">SQLite CLI</A>
<LI><A HREF="http://www.w3schools.com/sql/default.asp">SQL Tutorial</A>
<LI><A HREF="dbtermon.html#table">table</A>
<LI><A HREF="http://www.timeanddate.com">time and date.com</A>
<LI><A HREF="http://www.ubuntu.com">Ubuntu</A>
<LI><A HREF="http://etutorials.org/Server+Administration/upgrading+php+5/Chapter+4.+SQLite/4.6+User-Defined+Functions/">UDF</A>
<LI><A HREF="http://en.wikipedia.org/wiki/GMT_Time">UTC/GMT</A>
<LI><A HREF="http://www.wampserver.com/en/">WampServer</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Web_browser">web browser</A>
<LI><A HREF="http://en.wikipedia.org/wiki/Web_server">web server</A>
</UL>
<?php require '../misc/footer.php'; ?>
</BODY>
</HTML>

