Menu
Sister Sites

Options

Bookmark Thread

First Page First Unread Last Page Last Post

Print Thread

Similar Threads

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

Has anyone made an Excel spreadsheet to analyze your MT4 trades? 0 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!)

  • Post #61
  • Quote
  • Edited 11:47am Oct 23, 2020 11:23am | Edited 11:47am
I spent long time trying to figure two things. One to create the clickable buy/sell buttons but Iam getting errors. Two to create an If/Then statement in VBA for buying or selling based on a condition but still getting errors.

I decided to learn more about VBA from scratch and get the basic stuff in my head first.

BTW, @antariks: can you send me the sample excel sheet that has the VBA clickable buttons?
Quoting antariks
{quote} Thanks a lot. I will also try to play with the code. A question....instead of putting a constant volume there, I will try to put a cell reference number and see what happenes. Can we make it more conditional? For ex, as in Excel, =IF(AND(R520>0, S520<0, S527=1),"Buy","Sell")
You can do a lot if you're familiar with VBA. We do the most with M$ Excel formulas. Only the order execution is coded in VBA and send to MT4 thru the API.

Best of luck and stay green always!

Jibala
PDF & BCC (Patience Discipline Focus & Belief Confidence Consistency)
Quoting mtharwat
I spent long time trying to figure two things. One to create the clickable buy/sell buttons but Iam getting errors. Two to create an If/Then statement in VBA for buying or selling based on a condition but still getting errors.
Please explain a bit more in detail and maybe show the VBA code so that we maybe can have a look at what you're doing wrong.

Best of luck and stay green always!

Jibala
PDF & BCC (Patience Discipline Focus & Belief Confidence Consistency)
Quoting mtharwat
I spent long time trying to figure two things. One to create the clickable buy/sell buttons but Iam getting errors. Two to create an If/Then statement in VBA for buying or selling based on a condition but still getting errors. I decided to learn more about VBA from scratch and get the basic stuff in my head first. BTW, @antariks: can you send me the sample excel sheet that has the VBA clickable buttons?
Sure. Here you go, mate. Thanks.

https://www.jfdbank.com/documents/Ex...al_Ticket.xlsm
(I was trying to attach but don't know why this type of file is not supported. Hope you can download it from the link)
Quoting antariks
{quote} Sure. Here you go, mate. Thanks. https://www.jfdbank.com/documents/Ex...al_Ticket.xlsm (I was trying to attach but don't know why this type of file is not supported. Hope you can download it from the link)
Thanks
Quoting JibalaPasan
{quote} Just add an ActiveX Controls CommandButton and add the VBA Macro to it. {image} {image} Private Sub CommandButton1_Click() Set cmd = CreateObject("FXBlueLabs.ExcelCommand") strResult = cmd.SendCommand(RTD!A1, "BUY", "s=EURUSD|v=1500", 5) End Sub Private Sub CommandButton2_Click() Set cmd = CreateObject("FXBlueLabs.ExcelCommand") strResult = cmd.SendCommand(RTD!A1, "SELL", "s=EURUSD|v=1500", 5) End Sub Best of luck and stay green always! Jibala
Ok, lets say this is my workstation.

Every time the candle closes (15 min) I want to place a trade automatically. Ideally just 1 or 2 sec before the candle close.

By using IF and THEN functions of excel, the BUY/SELL and the volume cells are being calculated in real time.

How exactly the existing VBA code for that sheet will have to be ammended to achieve this?

Hope your suggestion will be useful for mtharwat as well.

Thanks.
Attached Image (click to enlarge)
Click to Enlarge Name: a59b929fa4dd0a7f42d8d985e65b4056.png Size: 59 KB
I need to ask a question about a Excel currency strength meter synced with Mt4: How to add Ger30 and gold below the last currency pair USDJPY, along with the remainder of the date in the adjacent columns. I cant attach the spread sheet below. Can some one help. Thanks and Regards.
Quoting antariks
{quote} Ok, lets say this is my workstation. Every time the candle closes (15 min) I want to place a trade automatically. Ideally just 1 or 2 sec before the candle close. By using IF and THEN functions of excel, the BUY/SELL and the volume cells are being calculated in real time. How exactly the existing VBA code for that sheet will have to be ammended to achieve this? Hope your suggestion will be useful for mtharwat as well. Thanks. {image}

Jibala has posted the code in a previous post. I am still trying to work on it without getting errors or I will post my steps so Jibala can comment. I am working on it still.
Buying and selling are only achievable through VBA which sends the orders to the API. That is my understanding as it was mentioned in a previous post by Jib ala.
  • Post #69
  • Quote
  • Edited 7:35am Oct 24, 2020 4:15am | Edited 7:35am
Quoting mtharwat
Does any know how to record with a dynamic timestamp in Excel? A VBA code perhaps that can help? I want to record the time when certain conditions are met in a column. I can assign a cell in Excel that will represent the conditions but do not know how to record a dynamic time stamp in a column. The timestamped cell should not be refreshed but rather a new cell will contain the new timestamp when the same conditions are met again.

Good News! I have found a VBA code that will do this and I will attach it here. The second step is I will add timestamps and reattach it here when I am done.

In this screenshot, cell (C2) is the one that gets changed. For example, it references certain conditions you have specified, and hence the cell changes values whenever these conditions are met.

Column D records previous values of cell C2. For example you wrote "Hello" in Cell (C2), and then you changed it to "Good Bye". Column D will show you the previous value/text as "Hello". The more you add values/texts in cell C2, then column D will keep typing them in order.

WoW... That is exactly what I wanted as I wanted to track percentage change values.

Attached Image (click to enlarge)
Click to Enlarge Name: 2020-10-24.png Size: 76 KB


The VBA code source is

Dim xVal As String
'Update by Extendoffice 2018/8/22
Private Sub Worksheet_Change(ByVal Target As Range)
Static xCount As Integer
Application.EnableEvents = False
If Target.Address = Range("C2").Address Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
Else
If xVal <> Range("C2").Value Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
End If
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
xVal = Range("C2").Value
End Sub

--------------------------

I have attached a workbook that has the code embedded in it.
You do not need to copy/paste anything.
Attached File(s)
File Type: zip Record Changing Values.zip   12 KB | 397 downloads


-------------------

I found this code from: https://www.extendoffice.com/documen...s%20below%20D2.
Follow the instructions if you are doing it manually.

---------------

NB: I am going later to share a modified version of calculating percentage change for grouping currencies. The percentage change I shared calculates (close0-close1)/close1 for each pair. But then how to translate this into overall strength or weakness for each whole currency group? Will show this soon.
Quoting goodways100
I need to ask a question about a Excel currency strength meter synced with Mt4: How to add Ger30 and gold below the last currency pair USDJPY, along with the remainder of the date in the adjacent columns. I cant attach the spread sheet below. Can some one help. Thanks and Regards.

Hi goodways100,

Can you please share the spreadsheet you have? I am not sure which one are you referring to. The one I posted here or the sample one from FXBlue Labs?

Normally, you would look at a currency pair that has the syntax. You will find the referencing to this pair in text or by cell referencing. If you change this "Text"or cell referencing to GER 30 or Gold , you should get the values you need.

Attach the sheet and I will help you more.
Quoting mtharwat
{quote} Good News! I have found a VBA code that will do this and I will attach it here. The second step is I will add timestamps and reattach it here when I am done.
Ok, I am updating the sheet with a new column that records the time for every time the dynamic cell changes its value.
If you can not open the attached sheet.
Here is the IF function:

=IF(D6<>"",IF(E6="",NOW(),E6),"")


---------------------

You need to enable iterations in Excel because it is a circular formula. Go to File > Options > Formulas > Enable iterations > close
There is a way around it with a VBA that is not included here. With the VBA, the iterations do not need to be checked/enabled.

----------------

A screenshot:
Attached Image (click to enlarge)
Click to Enlarge Name: 2020-10-24 (1).png Size: 50 KB


-----------

The sheet with the time stamp:
Attached File(s)
File Type: zip Record Changing Values With TimeStamp.zip   17 KB | 274 downloads
Quoting mtharwat
{quote}That is my understanding as it was mentioned in a previous post by Jib ala.
That's absolutely correct!

Best of luck and stay green always!

Jibala
PDF & BCC (Patience Discipline Focus & Belief Confidence Consistency)
Quoting mtharwat
{quote} Ok, I am updating the sheet with a new column that records the time for every time the dynamic cell changes its value. If you can not open the attached sheet. Here is the IF function: =IF(D6<>"",IF(E6="",NOW(),E6),"") --------------------- You need to enable iterations in Excel because it is a circular formula. Go to File > Options > Formulas > Enable iterations > close There is a way around it with a VBA that is not included here. With the VBA, the iterations do not need to be checked/enabled. ---------------- A screenshot: {image} -----------...
Hi mtharwat

Can it be done the other way round? I mean keeping the timestamps at a fixed interval, say 5 min, can the corresponding values be copied from a different sheet and pasted in the adjacent cells. Basically a data recording procedure in a time series format.

If this is feasible then I can present a theory for global risk measurement at any point of time. A Riskometer type value ranging from -100 to +100 gets calculated in a cell, the cell is updated every 5 min and new value come in (up to this part I have done). Then the problem is the new value replaces the old calculated value and the old one disappears. I wish to record each such values, before the cell refreshes, at a fixed interval, in a time series format.

The objective is to plot the values later and compare with global stock indices to see how my Riskometer correlates with the market behaviour.

Could I make it clear?

PS: In parallel, we can also develop the auto clicking on the "place trade" object to develop some kind of RTD-Expert advisor.
Quoting antariks
{quote} Hi mtharwat Can it be done the other way round? I mean keeping the timestamps at a fixed interval, say 5 min, can the corresponding values be copied from a different sheet and pasted in the adjacent cells.
I will explain how you can implement this.

The timestamp I shared is simply recording the time the "Referenced Cell" is changing its value. In other words, if the "Referenced Cell" is changing every X minute, then the time stamp will change every same X minutes. So what you need to do is to make sure the referenced cell is inputting its value every X minute.

Remember in a previous post when I mentioned to you to assign the syntax interval to "Previous (1) Bar and not current (0) Bar?
With regard to your Riskometer, it already updates every 5 minutes and the time stamp will record timestamp every 5 minutes.

And definitely, you can reference any cell from any sheet. If I am in Sheet (Y) , I can reference Cell A1 to any cell in Sheet (X). It is quite simple. just type (=) in the cell and then go to the other cell you want to reference from. Or copy paste the formula.

I feel like I am missing something else?
Quoting antariks
{quote} PS: In parallel, we can also develop the auto clicking on the "place trade" object to develop some kind of RTD-Expert advisor.

I am working on it. I am trying to develop a clickable place trade button and then partial take profits with an if/else in VBA depending on a number of criteria (ATR/Risk: Reward/ trailing stops, breakeven, etc...)

It took me hours in a number of days to get to the time stamp formula I posted above. I searched on youtube and searched online articles. Many times, I keep reading until I find the information is irrelevant to what I am looking for.

Try to search with me and find any partial solutions. Have you tried the VBA coding yet? which online sources have you found most informative?
Quoting mtharwat
{quote} I will explain how you can implement this. The timestamp I shared is simply recording the time the "Referenced Cell" is changing its value. In other words, if the "Referenced Cell" is changing every X minute, then the time stamp will change every same X minutes. So what you need to do is to make sure the referenced cell is inputting its value every X minute. Remember in a previous post when I mentioned to you to assign the syntax interval to "Previous (1) Bar and not current (0) Bar? With regard to your Riskometer, it already updates every...
Awesome. I completely missed your original concept. Because I didn't download the file and played with it. My mistake, sorry.

Now it is smooth.

Let me explain the Riskometer concept.

If one is aware of the "community outlook" statistics published by the Myfxbook, she will surely know that almost 90% of the time the traders' positions listed there are losing money. In other words, in any currency pair if the majority is holding Long and stuck in a losing position (indicated by their average entry price), then the secular trend of that pair is Bearish. So we pick up 7-8 pairs of any major currency (ex eur/usd, eur/gbp, eur/jpy.....) and see what is the total/average/net position of traders in EUR then we can have a fair idea on EUR strength/direction/trend.

Next step is to define certain currency/commodities as favourable in a "risk on" environment and vice versa. For example, commodity currencies such as Crude, NZD and AUD are risk on whereas USD, Gold and JPY may be considered as "risk off" favourites.

Then by observing a net position on them by the traders at any point of time, we can ascertain a net global risk mood.

The stat changes in real time can be downloaded in Excel using a XML code offered by Myfxbook.

If anyone has any other idea around this please share and we can think of some improvements.
Quoting JibalaPasan
{quote} Please explain a bit more in detail and maybe show the VBA code so that we maybe can have a look at what you're doing wrong. Best of luck and stay green always! Jibala

Hi Jibala,

Ok.. I still get an error.

In the first screenshot, I am getting this error message.
Attached Image (click to enlarge)
Click to Enlarge Name: 2020-10-25.png Size: 139 KB


When I click on Debug, I get this highlighted

Attached Image (click to enlarge)
Click to Enlarge Name: 2020-10-25 (1).png Size: 132 KB


My account number is in cell A1

Thanks,
Hello everyone,

I wanted to share one of the ways to calculate currency strength.

The sheet I have previously shared calculates percentage change (close(0) - close(1))/Close(1)*100. And maybe 1000 to make the percentage change more noticeable.

Anyway, what happens next is up to the trader. For example, you can visually inspect the colors and take a bias if the group of pairs per currency is mostly red or green. Or you can take averages of the percentage changes and get a value. I am sure many of you have many ideas.

Here I will show you a "Different" or "New Way" of calculating currency strength from (Stuart Brownie) in MQL5 forum.

Here is a copy/paste of how he applies percentage change:

Beginning of Quote:
I prefer to use "raw" currency strength rather than abstract values that some currency strength indicators use. And it's simple. You simply add up all moves in each currency pair. This is normally done on the 8 major currencies (28 pairs).

So to get say AUD's current strength you would look at each pair AUD is in and see how much it has moved (in percentage terms) from open to current price. Remember that a positive or negative move will depend on if the AUD is the base or quote of a pair. So the (exaggerated) calculation would look something like this:
AUDCAD = 2.2%
AUDCHF = 1.8%
AUDJPY = 2.5%
AUDNZD = 1.2%
AUDUSD = 1.6%
EURAUD = -2.3% (so + 2.3%)
GBPAUD = -1.9% (so +1.9%)
Total = 13.5%
You don't then divide it by 7 to get an average. You want the raw strength and 13.5% is it. That is the current strength of the Aussie compared to the other 7 currencies. You now do the same calculation for each of the other currencies.
If you've done it correctly, when you add up all 8 currencies, the final answer should be......zero! Why? Currency correlation ) End of Quote

----------------------------------------------------------------------------------------------

As you can see the idea here is the last calculation. i.e How to represent the percentage changes you have calculated from all pairs. So I modified my initial Excel Sheet that I have attached here earlier and added the percentage changes to get the value of Zero.
This idea I have not tested fully either for scalping or swing trading. However, I like the idea itself and wanted to share it with you.

However, I made two modifications:

1- Stuart uses the open and current price to calculate percentage change. I am using the previous close and current price. My reasoning is that I need to account for any possible gaps the price (for any particular time frame) has opened with.
2- Stuart only mentioned the daily time frame. I kept all the timeframes as I wanted to test/compare a group of timeframes.
Attached File(s)
File Type: xlsx NEW Price Change formula - Forex Factory.xlsx   70 KB | 372 downloads
Quoting mtharwat
{quote} Hi Jibala, Ok.. I still get an error. In the first screenshot, I am getting this error message. {image} When I click on Debug, I get this highlighted {image} My account number is in cell A1 Thanks,
Ok, sorry it was my mistake. VBA cannot interpret the pointer "RTD!A1", where RTD! is referring to the table and "A1" the cell with the account number. You have to hard code it with the account number instead!

Accordingly to the manual:
Inserted Code
Private Sub CommandButton1_Click()
Set cmd = CreateObject("FXBlueLabs.ExcelCommand")
strResult = cmd.SendCommand(12345678, "BUY", "s=EURUSD|v=1500", 5)
End Sub
Private Sub CommandButton2_Click()
Set cmd = CreateObject("FXBlueLabs.ExcelCommand")
strResult = cmd.SendCommand(12345678, "SELL", "s=EURUSD|v=1500", 5)
End Sub

Best of luck and stay green always!

Jibala
PDF & BCC (Patience Discipline Focus & Belief Confidence Consistency)
Quoting mtharwat
{quote} Good News! I have found a VBA code that will do this and I will attach it here. The VBA code source is

Inserted Code
Dim xVal As String
'Update by Extendoffice 2018/8/22
Private Sub Worksheet_Change(ByVal Target As Range)
Static xCount As Integer
Application.EnableEvents = False
If Target.Address = Range("C2").Address Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
Else
If xVal <> Range("C2").Value Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount + 1
End If
End If
Application.EnableEvents = True
End Sub
Private
...
That implicates that the cell "C2" is changed manually but that never happens since you calculate the cell by reference to another cell. Therefore, you must adjust that piece of code and you have to work with "Private Sub Worksheet_Calculate()" instead.

Best of luck and stay green always!

Jibala
PDF & BCC (Patience Discipline Focus & Belief Confidence Consistency)
0 traders viewing now