MT4 to Excel through Excel RTD (Finally!) | Page 13 | Forex Factory

Options

Go to
Permalinks
Print Thread

Related Instruments

EUR/USD, EUR/JPY, GBP/USD

Similar Threads

Calculating EMA strength like RTD on break free trading 0 replies

MT4 RTD API 0 replies

MT4 export to Excel? 8 replies

How to copy profiles in MT4? How to use Excel with MT4? 7 replies

Integration between excel and MT4 prices 3 replies

Attachments: MT4 to Excel through Excel RTD (Finally!)
Exit Attachments

MT4 to Excel through Excel RTD (Finally!)

  • #241
  • Jan 17, 2021 6:05am Jan 17, 2021 6:05am
A quick guide to get free live Interactive Brokers quotes into Excel in three steps


Step 1: Install IB API - it's a one time affair. You don't have to touch it later until a new version is available.
Go to:
http://interactivebrokers.github.io
Agree to terms.
Choose the stable version for your operating system (Windows or Mac/Unix).
Download and install.

Step 2: Enable API Access in IB TWS - Trader Workstation - trading platform.
Go to global configuration - API - Setting and check "Enable Activex and Socket Clients"

Step 3: IB Quotes Excel Formulas
The formulas are quite simple. I list a few examples below, but it is best to see official documentation for more examples and full list of parameters:
RTD Server for Excel > Simple Syntax
RTD Server for Excel > TWS RTD Server Samples
There are three types of syntax for these formulas: Simple, Complex, and Mixed. They differ in the way individual inputs (details like symbol, exchange, or currency) are arranged in the formula.

That’s it for the setup. You can now open Excel, write a formula into a cell and if all goes well you will see quotes from IB updated in real time.

Syntax for Simple style:

Forex (and Explanation of General Format)
Let’s get IB bid quote for the EUR/USD exchange rate in Excel.
The formula is:
=RTD("Tws.TwsRtdServerCtrl",,"EUR.USD/CASH", "Bid")

It has four parameters:

  1. "Tws.TwsRtdServerCtrl" is the same for all formulas.
  2. The second is empty (notice there are two commas).
  3. Symbol (in this case "EUR.USD/CASH").
  4. Type of quote, named Topic in the documentation. It can be "Bid", "Ask", "Last", or some other things. If omitted, it defaults to "Last".

Forex: EUR/USD
Once you enter the formula in an Excel cell and hit Enter, you should see the EUR/USD rate updating in real time.
If something goes wrong, you will see an error message instead of the quote.
For example, if you try to get a quote for an invalid symbol like "ABC.USD/CASH", you will see this text in the Excel cell:
TwsRtdServer error: No security definition has been found for the request,

Futures

With futures you also need to specify expiration:
=RTD("Tws.TwsRtdServerCtrl",,"ES@GLOBEX//FUT/202009///USD", "Bid")

Options

With options you also need the strike and call/put:
=RTD("Tws.TwsRtdServerCtrl",,"AAPL@SMART//OPT/20200717/C/360/USD", "Bid")
Quote Types
Besides bid, ask, and last, there are many different kinds of quotes you can request:

  1. “BidSize”
  2. “Bid”
  3. “Ask”
  4. “AskSize”
  5. “Last” (default)
  6. “LastSize”
  7. “High”
  8. “Low”
  9. “Volume”
  10. “Close”
  11. “Open”
  12. “LastTime”

For options also things such as:

  1. “BidImpliedVol”
  2. “BidDelta”
  3. “BidUndPrice”
  4. “AskImpliedVol”

… and much more
https://interactivebrokers.github.io...e_syntax_topic

Practice makes a person perfect
  • #242
  • Jan 18, 2021 1:21am Jan 18, 2021 1:21am
Release 1 of MTF strength. feels a bit buggy and resource-intensive.
@Mtharwat - Please have look and see if you can figure out why some of these values are not getting updated. I have circled them in Blue, which is causing miscalculation.

Cheers
Garfield75
Attached Image (click to enlarge)
Click to Enlarge Name: 2bf67f64b7e429e7454f0973c752e1f5.png Size: 246 KB
Attached File(s)
File Type: xlsx MTF-Strength-V1.xlsx   47 KB | 533 downloads
Trading is SIMPLE not EASY.
  • #243
  • Jan 18, 2021 3:24am Jan 18, 2021 3:24am
Quoting garfield75
Release 1 of MTF strength. feels a bit buggy and resource-intensive. @Mtharwat - Please have look and see if you can figure out why some of these values are not getting updated. I have circled them in Blue, which is causing miscalculation. Cheers Garfield75 {image} {file}
I will start on it now..
  • #244
  • Jan 18, 2021 4:43am Jan 18, 2021 4:43am
Quoting garfield75
Release 1 of MTF strength. feels a bit buggy and resource-intensive. @Mtharwat - Please have look and see if you can figure out why some of these values are not getting updated. I have circled them in Blue, which is causing miscalculation. Cheers Garfield75 {image} {file}
There is nothing wrong with any of the formulas...

with the circled pairs, I did not have an issue with the updating of values for Highs and Lows in M5.

However, the data did not get updated promptly and I got the #NAs.

What I noticed is when I only have up to 4 time frames (attached photo) , it works fine without issues. To avoid miscalculations, I removed the EA and re-applied it again. Three time frames gave the best performance.
Attached Image (click to enlarge)
Click to Enlarge Name: 2021-01-18.png Size: 112 KB
  • #245
  • Jan 18, 2021 4:46am Jan 18, 2021 4:46am
Quoting mtharwat
{quote} There is nothing wrong with any of the formulas... with the circled pairs, I did not have an issue with the updating of values for Highs and Lows in M5. However, the data did not get updated promptly and I got the #NAs. What I noticed is when I only have up to 4 time frames (attached photo) , it works fine without issues. To avoid miscalculations, I removed the EA and re-applied it again. {image}
Thanks for testing. 4 TF is good, everyone can pick their top 4 favourite TF.

Cheers
Garfield75
Trading is SIMPLE not EASY.
  • #246
  • Edited 9:21pm Jan 18, 2021 4:57am | Edited 9:21pm
Multi Time Frame Candlestick Direction (M5 to weekly)

This is a dashboard that calculates (current price - open / open * 100) for M5,M15,M30, H1,H4,D,W).

@Garfield

It seems that when retrieving highs and lows for many pairs in multiple time frames, we get some delay in updating the data. In this dashboard I am attaching, I am only retrieving open price for each pair across many time frames and current price (only once) as it will be the same across all time frames.

Edit: attached template..
Attached Image (click to enlarge)
Click to Enlarge Name: 2021-01-18 (1).png Size: 81 KB
Attached File(s)
File Type: xlsx Candlestick Direction (M5-Weekly).xlsx   52 KB | 565 downloads
  • #247
  • Jan 18, 2021 5:02am Jan 18, 2021 5:02am
Quoting garfield75
{quote} Thanks for testing. 4 TF is good, everyone can pick their top 4 favourite TF. Cheers Garfield75
You're welcome..
  • #248
  • Jan 18, 2021 2:16pm Jan 18, 2021 2:16pm
Congratulations on the thread ... for what it is worth I am in and will do what I can, however very much a novice with Excel.
Thought you may be interested in comparison DDE (Trading The Gap with Excel) v RTD (your download), same MT4 account and screenshot taken at the same time.
Attached Image (click to enlarge)
Click to Enlarge Name: DDE v RTD.png Size: 145 KB
When you are successful perhaps give a thought to others ... Kiva dot org
  • #249
  • Jan 18, 2021 2:23pm Jan 18, 2021 2:23pm
Quoting PayItForward
Congratulations on the thread ... for what it is worth I am in and will do what I can, however very much a novice with Excel. Thought you may be interested in comparison DDE (Speedometer) v RTD (your download), same MT4 account and screenshot taken at the same time. {image}
PayItForward,

You post timing was uncanny. I am comparing exactly the same thing right now and wondering why the difference.
One of the reason could that in RTD we are check H&L for a specific TF, In DDE that is not the case.

Cheers
Garfield75
Trading is SIMPLE not EASY.
  • #250
  • Jan 18, 2021 7:29pm Jan 18, 2021 7:29pm
Quoting PayItForward
Congratulations on the thread ... for what it is worth I am in and will do what I can, however very much a novice with Excel. Thought you may be interested in comparison DDE (Trading The Gap with Excel) v RTD (your download), same MT4 account and screenshot taken at the same time. {image}

Hello PayItForward

Thank you... You are welcome. This thread is for novices to unlock the many potentials of Excel.. Ask any thing you wish..

Now, with regard to your question,
The DDE version only retrieves the high and the low of the day which is determined by your platform. For example at 1:00 am (your mt4 server time), the high and the low is of this hour only from 12:00 to 1:00.
Further on at 13:00 your mt4 server time, the highest value will the one achieved from 12:00 till 13:00.

The Excel RTD version is more superior as you determine the high and low of a specified time frame (H4, H12, W, etc..)
  • #251
  • Jan 18, 2021 7:32pm Jan 18, 2021 7:32pm
Quoting garfield75
{quote} PayItForward, You post timing was uncanny. I am comparing exactly the same thing right now and wondering why the difference. One of the reason could that in RTD we are check H&L for a specific TF, In DDE that is not the case. Cheers Garfield75
yes , this is correct..
In RTD, we check H&L for specific TF.
To have a real comparison, we can compare the DDE version with that of the Daily time frame in RTD. If both have same starting time for the day, they should give similar results.
  • #252
  • Jan 18, 2021 7:49pm Jan 18, 2021 7:49pm
Quoting mtharwat
{quote} Hello PayItForward Thank you... You are welcome. This thread is for novices to unlock the many potentials of Excel.. Ask any thing you wish.. Now, with regard to your question, The DDE version only retrieves the high and the low of the day which is determined by your platform. For example at 1:00 am (your mt4 server time), the high and the low is of this hour only from 12:00 to 1:00. Further on at 13:00 your mt4 server time, the highest value will the one achieved from 12:00 till 13:00. The Excel RTD version is more superior as you determine...
Thanks for that info ... appreciated
As per below it appears that at time of screen shots H4 is common across DDE & RTD ... there appears to be an error in USDJPY as BR 8000% does not seem correct.
Attached Image (click to enlarge)
Click to Enlarge Name: DDE v RTD_H4.png Size: 107 KB
When you are successful perhaps give a thought to others ... Kiva dot org
  • #253
  • Jan 18, 2021 7:54pm Jan 18, 2021 7:54pm
Quoting PayItForward
{quote} Thanks for that info ... appreciated As per below it appears that at time of screen shots H4 is common across DDE & RTD ... there appears to be an error in USDJPY as BR 8000% does not seem correct. {image}
Sometimes, these errors happen. Remove the EA and reattach it again ti the chart. That should fix the error.
  • #254
  • Jan 18, 2021 8:31pm Jan 18, 2021 8:31pm
Quoting mtharwat
{quote} Sometimes, these errors happen. Remove the EA and reattach it again ti the chart. That should fix the error.
Tried that and still the same so looked at formula, where the price of a pair has 2 or more whole numbers the multiple factor is 100 and in the case of that pair USDJPY e.g price 103.82900 and multiple is 1000,
=($C84:$AD84-$C85:$AD85)/$C88:$AD88*10000
CHFJPY e.g. price 116.59400
=($C68:$AD68-$C69:$AD69)/$C72:$AD72*100
I am just guessing
When you are successful perhaps give a thought to others ... Kiva dot org
  • #255
  • Jan 18, 2021 8:56pm Jan 18, 2021 8:56pm
Quoting PayItForward
{quote} Tried that and still the same so looked at formula, where the price of a pair has 2 or more whole numbers the multiple factor is 100 and in the case of that pair USDJPY e.g price 103.82900 and multiple is 1000, =($C84:$AD84-$C85:$AD85)/$C88:$AD88*10000 CHFJPY e.g. price 116.59400 =($C68:$AD68-$C69:$AD69)/$C72:$AD72*100 I am just guessing
I will have a look once I go home and have my PC. Your guess might be correct.

@Garfield has posted this template.

He might be quicker than me in telling us if the multiple factor should change..
  • #256
  • Jan 18, 2021 9:14pm Jan 18, 2021 9:14pm
Quoting garfield75
{quote} PayItForward, You post timing was uncanny. I am comparing exactly the same thing right now and wondering why the difference. One of the reason could that in RTD we are check H&L for a specific TF, In DDE that is not the case. Cheers Garfield75
Hey
Trying to wrap my brain around all this new stuff ... when you get a chance can you take a look at Post 252 & 254 ... I'm confused ... no hurry anytime ... mtharwat suggested contact you
and are you guys just doing the 28 pairs or do you have intentions to add others like XAUUSD, US30, Ger30 etc ...
Appreciated ... Cheers
When you are successful perhaps give a thought to others ... Kiva dot org
  • #257
  • Jan 19, 2021 12:51am Jan 19, 2021 12:51am
Quoting PayItForward
{quote} Tried that and still the same so looked at formula, where the price of a pair has 2 or more whole numbers the multiple factor is 100 and in the case of that pair USDJPY e.g price 103.82900 and multiple is 1000, =($C84:$AD84-$C85:$AD85)/$C88:$AD88*10000 CHFJPY e.g. price 116.59400 =($C68:$AD68-$C69:$AD69)/$C72:$AD72*100 I am just guessing

Good pick up the excel formula for USDJPY was wrong.
updated file attached.
Attached File(s)
File Type: xlsx MTF-Strength-V1.xlsx   47 KB | 582 downloads
Trading is SIMPLE not EASY.
  • #258
  • Jan 19, 2021 12:58am Jan 19, 2021 12:58am
Quoting PayItForward
{quote} Hey Trying to wrap my brain around all this new stuff ... when you get a chance can you take a look at Post 252 & 254 ... I'm confused ... no hurry anytime ... mtharwat suggested contact you and are you guys just doing the 28 pairs or do you have intentions to add others like XAUUSD, US30, Ger30 etc ... Appreciated ... Cheers
Definitely interested to add more instruments.

  1. Gold & Sliver
  2. ASX200, US30, VIX, US500
  3. BTC, ETH & XRP

Trading is SIMPLE not EASY.
  • #259
  • Jan 23, 2021 2:11am Jan 23, 2021 2:11am
Backtesting:

I am working on a templatge to backtest any strategy.
It is still a work in progress..

The strategy is a simple one:
1- Long when Close is above SMA 10 and close is above SMA 20.
2- SMA10 is bigger than SMA 20 and SMA10 a bar ago is smaller than SMA 20 a bar ago ( in order to get the cross).

The opposite is true for shorts...

Targets/stop losses are set in changeable percentages.


The pair is AUDUSD M15 exported from MT4.

Attached Image (click to enlarge)
Click to Enlarge Name: 2021-01-23.png Size: 124 KB
Attached File(s)
File Type: xlsx Backtesting Ver1.xlsx   984 KB | 473 downloads
  • #260
  • Jan 23, 2021 2:28am Jan 23, 2021 2:28am
I do have an Indi for MT4 that saves your chart data (any tf,)to your hard drive, updates auto as a new tick shows, will go back as far as the chart you have opened does.It saves csv or any other common format. I use it for my Dynamic Trader software, had a russian nerd program that for me a decade ago, payed for it.
I can share for free.
0 traders viewing now