Market Simulation (Part 16): Sockets (X)

Introduction

In the previous article, "Market Simulation (Part 15): Sockets (IX)", we explained and showed part of the server code that will be written in Python. However, this code alone is not enough to use the communication system between MetaTrader 5 and Excel. We need more elements in addition to those shown in that article. Many could implement the entire solution directly in this Python code, and they wouldn't be wrong. If we did that, we would limit ourselves to a completely closed system. In other words, we would not be able to adapt to perform certain actions or cover more specific scripts. However, by dividing the system into parts, we can implement it more flexibly. Thus, we can use the best that each language or program has to offer.

You might think this is foolish. But consider that we are merely demonstrating that MetaTrader 5 can be used in completely unexplored ways. Just as we are showing ways to use Excel, we could use any other program. Furthermore, we can create our own method using programs developed exclusively for this purpose, to have a specific analytical and operational model for a certain type of work. In this way, MetaTrader 5 would only transmit orders between what we create and the market, avoiding all the work associated with developing a trading platform.

Therefore, you can consider that all the explanations are much broader than what we are showing here. To do such things, you need to know and study a lot. But if you truly want something like this to become a reality, you can view these articles as a small example of what we can do. However, all of this will not have any real meaning until we see what else needs to be implemented to make the Python server truly useful. So in this article, we will cover the part that needs to be implemented in VBA, which is part of Excel.

Getting started in Excel

The code that needs to be implemented in VBA is not complex. In fact, much of what will be shown is already done by those who write scripts in VBA. But there are a couple of points that may be more or less difficult, at least for some. However, we would like to emphasize that everything here is arranged as simply and educationally as possible. Furthermore, the code provided in the previous article will remain unchanged. All that needs to be done is to create elements in VBA to perform the necessary tasks or settings depending on what you intend to do.

To begin, let's take a look at the Excel interface. We can see it below:

If you know how to use Excel, you should have noticed that in the image above, the buttons are shapes. They allow you to create an interface with rounded corners. However, their operation is not much different from standard controls, as they can be linked to a macro. Therefore, when you click on one of these shapes, Excel interprets it as interacting with a regular button and makes the appropriate call to execute the macro associated with the click event on that shape or button.

In reality, what interests us when looking exclusively at the interface shown in the image is the content of the cells. Notice that cell B2 contains information indicating that the server is offline. Similarly, cell B5 contains some other information, which in this case indicates the symbol for which we want to obtain information from MetaTrader 5. It is at this moment that the idea begins to take shape. Even if we don't yet know what will be programmed in MQL5, we can already get an idea of what we need and what we can do.

Remember that in the explanation of the server code, we mentioned that it would capture information from a cell and send it to MetaTrader 5 in order to obtain some data? It is in this cell, where we have the value WDON23, that we need to act. However, if we are going to manually change the information in this cell, we must first stop the server, change the value in cell B5, and then start the server again. After that, MetaTrader 5 will receive the new parameters to analyze them.

Such changes are quite simple and can be done at any time, with the server never knowing what is happening, since everything will occur between MetaTrader 5 and Excel. But, as mentioned before and I repeat it again, the idea here is to be didactic. However, nothing prevents us from creating our own protocol to obtain more information or access other symbols. All that needs to be done is to place the data in a specific Excel cell. But you might ask yourself: «Why this particular cell? Why not use another one?» The point is that the server code uses an index, and depending on that index, cell B5 is used. However, in this article, we will show how you can use a different cell without needing to modify the server-side code shown in the previous article.

If you understand this, we can move on to the next stage. Let's now look at how everything will be done in VBA.

Let's start working on the VBA code

To make something truly useful happen and to ensure the user doesn't realize what's going on behind the scenes, we'll need to do some work in VBA. Therefore, the first thing to do is to create a module. The purpose of this module is to house all the code that needs to be implemented and that wouldn't fit elsewhere. The first thing to implement as code can be seen just below:

01. Private Const szMSG_START_SERVER As String = "Start Server"
02. Private Const szMSG_STOP_SERVER As String = "Stop Server"
03. Private Const szSERVER_SHUTDOWN As String = """/Force Server ShutDown"""
04. Private Const szMSG_MT5_OFFLINE As String = "MetaTrader 5 is offline."
05. Private Const szMSG_MT5_ONLINE As String = "MetaTrader 5 is online."
06. Private Const szMSG_MT5_BUY As String = """Buy in Market >"""
07. Private Const szMSG_MT5_SELL As String = """Sell in Market >"""
08. Private Const iPort As Integer = 9090
09. Private gl_ServerInExec As Boolean
10. Public Const CELL_MT5 As String = "$B$4"
11. 
12. Public Sub SwapStatusMT5()
13.     Dim c1, c2, c3 As Long
14.     
15.     If Sheets("System").Range(CELL_MT5).Value = szMSG_MT5_ONLINE Then
16.         c1 = RGB(217, 238, 16)
17.         c2 = RGB(51, 153, 102)
18.         c3 = RGB(255, 80, 80)
19.     Else
20.         c1 = RGB(132, 130, 122)
21.         c2 = c1
22.         c3 = c1
23.     End If
24.     With Sheets("System")
25.         .Shapes("Btn_MT5").Fill.ForeColor.RGB = c1
26.         .Shapes("Btn_BUY").Fill.ForeColor.RGB = c2
27.         .Shapes("Btn_SELL").Fill.ForeColor.RGB = c3
28.     End With
29. End Sub
30. 
31. Private Sub SwapMsgBtnServer(text As String)
32.     Dim c As Long
33.     If text = szMSG_START_SERVER Then
34.         c = RGB(213, 87, 99)
35.         gl_ServerInExec = False
36.     Else
37.         c = RGB(84, 130, 53)
38.         gl_ServerInExec = True
39.         SwapStatusMT5
40.     End If
41.     With Sheets("System").Shapes("Btn_Server")
42.         .TextFrame2.TextRange.text = text
43.         .Fill.ForeColor.RGB = c
44.     End With
45. End Sub
46. 
47. Public Sub BtnServer_Click()
48.     If gl_ServerInExec Then
49.         StopServer
50.     Else
51.         InitServer
52.     End If
53. End Sub
54. 
55. Public Sub InitServer()
56.     Dim szScript, szCmd As String
57.     
58.     szScript = Chr$(34) & Application.ActiveWorkbook.Path & "\Server.py" & Chr$(34)
59.     szCmd = " ""127.0.0.1""" & Str(iPort) & " ""System"" ""B$2"""
60.     VBA.CreateObject("WScript.shell").Run """Python""" & szScript & szCmd, vbHide
61.     SwapMsgBtnServer szMSG_STOP_SERVER
62. End Sub
63. 
64. Public Sub StopServer()
65.     szPath = Chr$(34) + Application.ActiveWorkbook.Path + "\MsgFromExcel.py" + Chr$(34)
66.     VBA.CreateObject("WScript.shell").Run """Python""" & szPath & " " & Str(iPort) & " " & szSERVER_SHUTDOWN, vbHide, True
67.     SwapMsgBtnServer szMSG_START_SERVER
68. End Sub
69. 
70. Public Sub MT5_BuyInMarket()
71.     szPath = Chr$(34) + Application.ActiveWorkbook.Path + "\MsgFromExcel.py" + Chr$(34)
72.     VBA.CreateObject("WScript.shell").Run """Python""" & szPath & " " & Str(iPort) & " " & szMSG_MT5_BUY, vbHide, True
73. End Sub
74. 
75. Public Sub MT5_SellInMarket()
76.     szPath = Chr$(34) + Application.ActiveWorkbook.Path + "\MsgFromExcel.py" + Chr$(34)
77.     VBA.CreateObject("WScript.shell").Run """Python""" & szPath & " " & Str(iPort) & " " & szMSG_MT5_SELL, vbHide, True
78. End Sub

The VBA source code

This module contains everything we will need moving forward. As you can see, several constants and several procedures are declared here. The constants help us fix the code more quickly. Each procedure has its own purpose, and all of them are important within the overall code. Pay attention to the details in order to correctly modify this module in VBA.

To begin, let's look at line 55. We won't actually follow the process in the usual order. We'll examine the functions and procedures based on their importance for enabling Excel to manage the server in Python. In line 55, there is a procedure that starts the server. Notice that the Python script must be in the same folder as the Excel file. This is because, in line 58, we use VBA to capture the directory where the Excel file is located. This way, we build the full path along with the name of the Python script.

Now, in line 59, we declare the parameters that will be passed to the script. To properly understand this line, go back to the previous article and review the correct order of parameters that the script should receive. Line 60 is where the magic happens, as this line calls and runs the Python script. But then the main problem arises: this line does not wait for the code to return—that is, VBA calls the server script and executes it as if it were a separate thread. Please note that this is not a literal statement, but an observation. If you look in the Task Manager, you'll see that the Python script will appear in the same block as the Excel application. This can be seen in the following image:

This behavior indicates that when Excel is closed, the Python script will also be closed. If we observe in the Task Manager that the script is outside the Excel application block, then when Excel is closed, the script will continue to run and would need to be closed manually. At the end of the procedure in line 61, we call the procedure from line 31. In this procedure, we make changes to the object representing the button that starts and stops the server. Since this procedure is quite simple and directly related to the shape, there is nothing more to comment on.

However, note the following: neither the procedure in line 31 nor the procedure in line 55 is directly linked to the shape that starts or stops the server. In fact, that task is performed by another procedure in line 47. Therefore, the server start/stop button will execute the BtnServer_Click procedure. This way, we have simple and effective control over what will be displayed in Excel. However, this explanation was only the beginning; there are still four procedures within this module to understand.

Let's now continue by understanding what the server control button shape is linked to. Notice that in the BtnServer_Click procedure, besides calling the server start, there is also a call to stop it. This is found in line 49 and references line 64. In this StopServer procedure, we act a bit differently. Why is that? The reason is that if we examine the interaction between Excel and Python, we see that there are ways to read, execute, or write Python procedures or variables declared in VBA. We could then simply write to a VBA variable indicating that the server should be terminated. Thus, when the Python script accesses this variable, it would know that the server needs to be stopped.

However, just as we can place the server on the same host as Excel, we can also do this by placing both servers on different machines. Communication would then occur through sockets. Therefore, we use a slightly different procedure. In line 65, we specify where the Python script that will send a message to the server is located. This message is actually a command. In line 66, we have the information about the command to be sent to the server. For now, don't worry about this Python script; we'll look at it later, closer to the end of the article.

The next procedure, located in line 70, is intended to send a request to MetaTrader 5, as is the procedure in line 75. Both of these should be linked to the corresponding buttons so that the user can interact with them directly from Excel. But if you've noticed, both the StopServer procedure and the MT5_BuyInMarket and MT5_SellInMarket procedures use the same Python script. However, unlike StopServer, which is already a fully functional procedure, the MT5_BuyInMarket and MT5_SellInMarket procedures are actually not yet finalized. The reason is that these procedures are conducted exclusively for educational purposes—that is, they do absolutely nothing except display a message in the MetaTrader 5 terminal, but we will discuss this in the next article.

For these two procedures to work, it is necessary to implement a communication protocol that informs MetaTrader 5 about the symbol and defines aspects such as the stop-loss and take-profit of the position. I think that for most enthusiasts, this will be a quite interesting task and will even become a personal challenge. But implementing it is not difficult at all. One would only need to instruct VBA on how to create a message that MetaTrader 5 can interpret, so that trades can be placed directly from this platform. In the next article, we will discuss this process in more detail. For now, don't worry about it.

To complete this module, there is another procedure in line 12. The purpose of this procedure is to ensure that the Excel interaction and control buttons correspond to the current state of MetaTrader 5. In other words, when MetaTrader 5 is offline, the interaction buttons will be grayed out, and when it is online, they will be colored. This way, the user will know when they can send commands to MetaTrader 5.

Thus, we have finished studying the VBA part of the module. But this does not mean we are done programming the main elements in Excel. We still need to do some work to improve support and interaction. The next action that needs to be performed is shown below:

And to eliminate any doubts about which code needs to be used, the same code as in the image is provided just below:

1. Private Sub Worksheet_Change(ByVal Target As Range)
2.     If Target.Address = CELL_MT5 Then
3.         SwapStatusMT5
4.     End If
5. End Sub

The VBA source code

But what exactly does the code do? If you're not yet familiar with VBA programming, and especially if you're not used to creating code for Excel, this might be somewhat confusing. However, this code simply configures the state of the buttons that send commands to MetaTrader 5. To do this, the value of the cell determined by the server is constantly checked. When the value of this cell changes for any reason, the third line is triggered. As a result, the colors of the buttons will automatically change depending on the state of MetaTrader 5 in relation to the server.

Thus, neither you nor the end user will have to read the content of a specific cell; you can simply look at the color of the buttons to understand whether MetaTrader 5 is online or offline. An important point, especially for those who don't know how to perform such tasks in Excel: look at the image above to understand on which sheet the code needs to be placed for it to do its job. If you place this same code on the wrong sheet, we will not get the expected result. So pay attention to this detail.

As we have done before, there is another task that also needs to be addressed. Just as we needed to be careful about where to place the code, here it also needs to be placed in a specific location. Therefore, you need to insert the following code, as shown below:

If you are unsure about exactly what needs to be placed there, look at the same code as in the image above.

1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
2.     ThisWorkbook.Save
3.     StopServer
4. End Sub
5. 
6. Private Sub Workbook_Open()
7.     InitServer
8. End Sub

The VBA source code

This option is even simpler, as we have only two fairly straightforward event handlers. The first, Workbook_BeforeClose, will be called when the user requests to close the Excel workbook. When this happens, two actions will occur. The first can be seen in line 02, where Excel is instructed to save the workbook. Thus, even if we have made changes to the workbook, Excel will not ask us for confirmation to save it, as it will be saved automatically when the program closes. The second action is shown in line 03, where we call the procedure shown in the module. Our goal here is to shut down the server.

Alright, in line 06 we have something even simpler. In this line, where we have the Workbook_Open event handler, we simply make a call in line 07 to the module procedure that starts the server. This Workbook_Open call will be executed when the workbook is opened in Excel. Thus, when you or the end user request to open it, it will automatically start the server, which will wait to begin receiving data from MetaTrader 5.

All of this works perfectly. You can create a Python server with exactly the same code as shown in the previous article, create a simple spreadsheet, add a few buttons or shapes for interaction, and, of course, ensure that the names of the shapes to be used in the VBA code are exactly the same. After that, you can test the system. You will see that you can initialize the server without any problems. But something is still missing: the code for Excel to interact with the Python server.

If you've followed the entire explanation, you'll have noticed that we need another piece of Python code. This file should be named MsgFromExcel.py. To finish this part of the Excel and VBA course, we must look at the final script. Don't despair and think that you don't understand anything; let's stop and think for a moment.

When Excel starts, the Workbook_Open procedure is called. This will call the InitServer procedure, which is present in the VBA module. This procedure will look for the Server.py file in the same folder as the workbook. This is the Python server script that can be seen in the previous article. Once this part is complete, Excel will respond to user commands and actions in the usual way.

If we try to start the system and the server does not start, let's go back and repeat the steps we just explained. This is because the server should start automatically. If it doesn't, you need to find the error in your code before continuing.

The next stage occurs when we shut down the server. This happens when we request to close the Excel application or perform an action that calls the StopServer procedure. When either of these actions is performed, line 65 from the module is executed. So far, you haven't seen the code that runs at this moment. To keep your interest and maintain order, we will look at this in another topic.

Client for Excel

Yes, that's exactly what you read. The MsgFromExcel.py script is actually a client, and it will interact with the server that was also opened and will remain active in the Excel session. For simplicity and understanding, the code of the file is provided below:

01. import socket as sock
02. import sys
03. 
04. if sys.argv[2]:
05.     conn = sock.socket(sock.AF_INET, sock.SOCK_STREAM)
06.     try:
07.         msg = '<mt5 with excel>:' + sys.argv[2]
08.         conn.connect(('localhost', int(sys.argv[1])))
09.         conn.send(msg.encode())
10.         conn.close()
11.     except:
12.         sys.exit(1)

Python source code

If you have some experience with Python, then by analyzing this code, you've probably already noticed something and thought the following: «But this is a client that opens, sends something, and then closes the connection. What's the point of that?». Indeed, the code above is a TCP client in Python that does exactly that. It opens, sends some information to the server, and then closes the connection, ready to be called again.

The reason is that this client doesn't need to stay connected to the server all the time. Unlike the client in MQL5, which runs on MetaTrader 5, it is only intended to send some command to the server, either to execute something or to send information. Thus, you can see how the same code is called from the StopServer, MT5_BuyInMarket, and MT5_SellInMarket procedures. The idea is for it to perform an action that we wouldn't be able to perform if the server and Excel were on different machines.

Therefore, this script accepts two parameters. The first specifies the port to be used, and the second is the command or message that the server will execute or pass on to MetaTrader 5. Once this is done, the connection that this client just opened can be closed.

At this point, I would like to comment on a few things that might be important and even raise doubts. If you are just starting to program in Python or VBA and found these materials interesting, I want you to know that this is just a didactic approach I found. Much of what has been done here can be improved, and significantly so. Mainly because we didn't include many checks in the code. But I want to emphasize that this particular client, created in Python, could easily be converted into VBA code.

Therefore, the only module that will be in Python will be the one that creates the server. However, since the Python code is significantly simpler and clearer than implementing the same client in VBA, we decided to keep the code in Python. But if you want to try to do the same thing using VBA, be careful, because the process of doing the same thing as here, but via VBA, turns out to be more complex.

Concluding thoughts

Although in this article we haven't yet finished explaining how to establish communication between Excel and MetaTrader 5, we are close to completing this challenge. However, before we begin, I want you to try to understand these two articles—this one and the previous one. That way, you will truly understand the next article, in which I will cover exclusively the part related to MQL5 programming. But I will also try to make it understandable. If you do not understand these last two articles, it will be difficult for you to understand the next one, because the material accumulates. The more things there are to do, the more you need to create and understand in order to achieve the goal.

I hope that by the time you try to read and put into practice what will be discussed in the next article, you will already understand and be able to apply what you have seen up to this point. If you have doubts, I recommend you study the previous articles, because, as we said at the beginning, the topic of sockets is quite dense and has many details. Some of them are simpler, and some are quite complex. Nevertheless, it is very important to understand this subject so that you know what may arise in the future.

And although you might think that the topic of sockets is not related to the replay/simulation system, I want you to reconsider that idea. If you ever want to create a replay/simulation system that does not depend on something running inside MetaTrader 5, as has been done up to now, you will need to understand how sockets work. This way, you will be able to simulate everything you need—not as I have shown, but by simulating a trading server and simply telling MetaTrader 5 to connect to that server that you yourself will emulate.

But since our goal is different, we will discuss these matters another time. See you in the next article!

File	Description
Experts\Expert Advisor.mq5	Demonstrates interaction between Chart Trade and the expert advisor (requires Mouse Study for interaction).
Indicators\Chart Trade.mq5	Creates a window for configuring the order to be sent (requires Mouse Study for interaction).
Indicators\Market Replay.mq5	Creates controls for interacting with the replay/simulation service (requires Mouse Study for interaction).
Indicators\Mouse Study.mq5	Provides interaction between graphical controls and the user (required for both the replay system and real market operation).
Services\Market Replay.mq5	Creates and maintains the market replay/simulation service (main file of the entire system).
VS Code C++ Server.cpp	Creates and maintains a socket server developed in C++ (mini-chat version).
Python Code Server.py	Creates and maintains a Python socket for communication between MetaTrader 5 and Excel.
ScriptsCheckSocket.mq5	Allows testing the connection with an external socket.
Indicators\Mini Chat.mq5	Implements a mini-chat via an indicator (requires the server to function).
Experts\Mini Chat.mq5	Implements a mini-chat via an expert advisor (requires the server to function).