Spread Calculator

This EA puts all ticks inside a MYSQL Database where you can do any calculations you want on the ticks.



Executive Summary

Boston Technologies has made available to everybody a FREE Expert Advisor (EA) that will run on a chart, and will store all the ticks ( time, bid, ask and symbolname) in a database. One can then calculate different statistics on the tick data.
One can use this EA to calculate for example

  1. The average spread of a feed
  2. The largest spread
  3. The smallest spread
  4. The average spread during a daily time period
  5. And any other statistics one wants to calculate
One can also use this EA to store ticks so that you can do proper back testing.



What does it mean for you?

This means that you can do your own calculations instead of trusting what other information people are providing to you. For example if somebody says that Broker A’s EURUSD feed is 0.5 pip spread on average. You can store ALL the ticks from Broker A’s EURUSD feed and calculate the average yourself to check.



How to install?

The installation takes 10-15 minutes if you click on next everywhere and read only the information in red.
To use this EA you need to do 4 steps

  1. You need to install MYSQL on your computer and start it
  2. You need to install MYSQL Query Browser so that you can work on the date in the MYSQL database and do queries and calculations on the data.
  3. You need to install the EA and launch it on one or more charts of interest to you.

  1. A.) Download MYSQL from here (pick the closest location to you) : http://dev.mysql.com/downloads/mirror.php?id=382190#mirrors
    1. You should receive a file called mysql-essential-win32.msi. The version number might increase as new versions are releases. Save and double click on this file to launch the installer.
    2. Chose the following options:
      1. Typical and click next
      2. Install wait for it to install
    3. Now we will configure it:
      1. Click on next.
      2. Next again.
      3. Leave Configure MYSQL server now ( you can disable the register the MYSQL server now, it will save you time).
      4. Click on Finish
      5. Click on Next
      6. Select Standard Configuration. Click on next.
      7. Leave Install as Windows Service checks only. Leave Launch the MYSQL server automatically checked. No need to check the include Bin option. Leave service name MYSQL (good practice).
      8. Leave Modify Security Settings. In New root password type : test747
      9. In confirm also type : test747
      10. If you type another password (for advanced users) make sure you type the right password below in point C.g.
      11. Do not check anything else.
      12. Click Execute
      13. Click Finish
      14. The MYSQL server is installed.
  2. Now download the MYSQL Browser from here ( this is a graphical user interface that will allow you to actually work with MYSQL without needing to know very much MYSQL at all)
    1. Download it from here: http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-gui-tools-5.0-r17-win32.msi/from/pick#mirrors
    2. Pick a close location. Save the file mysql-gui-tools-5.0-r17-win32.msi (version name might change).
    3. Execute the file.
    4. Select Run.
    5. Select Next.
    6. Say Next to everything.
    7. Check Accept. Click on Next.
    8. Next
    9. Next (leave default option checked)
    10. Install
    11. This is REQUIRED to configure the MYSQL DB you need to start the MYSQL Browser:
    12. Start MYSQL QUERY BROWSER from the Start menu.
    13. Login using:
      1. Stored connection: leave empty (do not put anything)
      2. Server host: localhost
      3. Username: root
      4. Password: test747
      5. Default Schema: bt
      6. Port: 3306
      If you change the Schema you need to type it in in point C.g.
    14. It will ask you Do you want to create the schema now? And YES.
  3. EA installation , this is the same installation as any other EA.
    1. Download it from here: www.bostontechnologies.com/downloads/EA/TicksInMySQL.ex4
    2. Save this file in the Expert subdirectory of your Client Terminal.
      If you use our Client Terminal from here ( www.bostontechnologies.com/downloads/lc4setup.exe ) then this is in: C:\Program Files\Liquidity Connection\experts
      1. For Citi Bank: C:\Program Files\Citi FX MT4 powered by BT\experts
      2. And so on.
    3. Download the following DLL library in your library folder: www.bostontechnologies.com/downloads/EA/libmysql.dll
    4. Put it in the libraries subdirectory of the expert directory.
      1. For example in : C:\Program Files\Liquidity Connection\experts\libraries
      2. For Citi Bank: C:\Program Files\Citi FX MT4 powered by BT\experts\libraries
      3. And so on.
    5. Then restart the client terminal.
    6. When it opens you should see a new EA installed called: TicksInMySQL
    7. Make sure that EAs are enabled.
    8. Drag drop this EA on the chart of your choice.
    9. Click ok
    10. Ticks should be appearing in MYSQL Browser



How do I do math on the ticks after the EA is storing them in MYSQL?

  1. Open MYSQL browser
  2. Login using:
    1. Stored connection: leave empty (do not put anything)
    2. Server host: localhost
    3. Username: root
    4. Password: test747
    5. Default Schema: bt
    6. Port: 3306
  3. In the MYSQL browser text window type for example:
    1. To calculate average spread on EURUSD on all stored ticks:
      SELECT avg(ask-bid)*10000 FROM eurusd e;
    2. To calculate average spread on GBPUSD on all stored ticks:
      SELECT avg(ask-bid)*10000 FROM gbpusd e;

It will look like this:

Then click on the green flash, the red arrow points to it, to execute each line , each request.



Example of useful MYSQL:

SELECT * FROM eurusd;
SELECT avg(ask-bid)*10000 FROM gbpusd e;
SELECT avg(ask-bid)*10000 FROM gbpusd e WHERE
SELECT avg(ask-bid)*10000 FROM gbpusd e WHERE date>'2010-02-04 00:00' AND date<'2010-02-05 00:00';
SELECT avg(ask) FROM eurusd WHERE date>'2010-02-04 00:00' AND date<'2010-02-05 00:00';
Feel free to email us your useful MYSQL command and we will post them here.

F.A.Q.