Market Simulation (Part 13): Sockets (VII)

Introduction

In the previous article, "Market Simulation (Part 12): Sockets (VI)", we created a Python server capable of responding to multiple clients. It worked through the use of threads. I believe the core idea of how threads function is now clear. But above all, I hope you were able to understand how the server manages, in a sense, to make a small chat work. The purpose of these explanations about sockets is not to cover everything about them. We want you to understand how sockets work. This knowledge will be needed later in the replay/simulation system.

Let's return to the problem in Excel

However, even though the Python server works in such a way that it can be used by multiple clients, it is not designed for use within Excel. That is, if we use xlwings to integrate this server into Excel, you will encounter issues when interacting with Excel. But why, if we are using threads to avoid blocking code execution?

This is a somewhat complex question— at least for those who are not very familiar with how a program uses another program that is not part of it. Perhaps that sentence was a bit confusing, but let's try to understand it. When we use a VBA script inside Excel, we are essentially running a program. If that script calls another application, Excel usually waits for it to finish executing. But this is not always the case. To prevent Excel from having to wait for the application to complete, we can use an application that is not linked to Excel. That is, we would have Excel open alongside another application working with VBA. Both can coexist without competing for the CPU. It is roughly the same as asking Excel to open Word. It doesn't matter if Word freezes—that won't interfere with Excel's operation.

However, this is not the case for what is being proposed here—namely, enabling MetaTrader 5 and Excel to exchange data. While MetaTrader 5 and Excel do not compete for the CPU, the Python script that creates the socket for this data exchange does. This is due to the server models shown up to this point. To make the explanation simpler, let's take the last script discussed in the previous article. It can be seen below:

01. import socket as sock
02. import threading as thread
03. 
04. CONN_LIST = []
05. 
06. def NewClientHandler(conn, addr):
07.     global CONN_LIST
08.     CONN_LIST.append(conn)
09.     print(f"Client [%s:%s] is online..." % addr)
10.     while True:
11.         msg = conn.recv(512).decode().rstrip(None)
12.         if msg:
13.             print(f"Message from [%s:%s]: {msg}" % addr)
14.             for slave in CONN_LIST:
15.                 if slave != conn:
16.                     slave.send(f"[{addr[0]}]: {msg}\n\r".encode())
17.             if msg.lower() == "/see you later":
18.                 break
19.     print(f"Client [%s:%s] is disconnecting..." % addr)
20.     conn.close()
21.     CONN_LIST.remove(conn)
22. 
23. server = sock.socket(sock.AF_INET, sock.SOCK_STREAM)
24. server.bind(('0.0.0.0', 27015))
25. server.listen()
26. print("Waiting connections...")
27. while True:
28.     conn, addr = server.accept()
29.     conn.send("Welcome to Server.\n\r".encode())
30.     thread.Thread(target=NewClientHandler, args=(conn, addr)).start()

Python Code

Although this script cannot be used directly in xlwings to create a server, we can make some small adjustments, and it will become suitable for use. The code shown above will be transformed into the following:

01. import socket as sock
02. import threading as thread
03. 
04. CONN_LIST = []
05. 
06. def NewClientHandler(conn, addr):
07.     global CONN_LIST
08.     CONN_LIST.append(conn)
09.     print(f"Client [%s:%s] is online..." % addr)
10.     while True:
11.         msg = conn.recv(512).decode().rstrip(None)
12.         if msg:
13.             print(f"Message from [%s:%s]: {msg}" % addr)
14.             for slave in CONN_LIST:
15.                 if slave != conn:
16.                     slave.send(f"[{addr[0]}]: {msg}\n\r".encode())
17.             if msg.lower() == "good bye":
18.                 break
19.     print(f"Client [%s:%s] is disconnecting..." % addr)
20.     conn.close()
21.     CONN_LIST.remove(conn)
22. 
23. def InitServer(HOST, PORT):
24.     server = sock.socket(sock.AF_INET, sock.SOCK_STREAM)
25.     server.bind((HOST, PORT))
26.     server.listen()
27.     print("Waiting connections...")
28.     while True:
29.         conn, addr = server.accept()
30.         conn.send("Welcome to Server.\n\r".encode())
31.         thread.Thread(target=NewClientHandler, args=(conn, addr)).start()
32. 
33. if __name__ == '__main__':
34.     InitServer('localhost', 27015)

Python Code

Alright. As you can see, we've added very little code, but this allows us to use the script directly in Python or run it from VBA in Excel via xlwings. In VBA, the following command can be used to activate the server from Excel:

Public Sub ExecuteServer()
    RunPython ("import Server_Thread; Server_Thread.InitServer('127.0.0.1', 27014)")
End Sub

VBA code

Let's see what will happen. This is for those who haven't followed this sequence or don't know how to work with VBA. his small VBA script should be called by some control that we need to add to Excel. It's actually a very simple task. We add a control, such as a button, and in the button's properties, we specify that it should execute the script shown above.

But what does this VBA script actually do? It instructs xlwings to run the Python script we saw earlier. Notice that in the Python script, you can specify the port and host on which the server will run. The arguments we see in Server_Thread.InitServer indicate precisely the host—more specifically, the address where the server expects connections—as well as the port. Note that we are defining a port different from the one used in the Python script; this is done to make it clear that the server will be defined and called using Excel.

If you've followed all the steps correctly, you'll notice that Excel opens Python and the server becomes available. Thus, it will work the same as if we were running it directly in Python. But there is an important point: Excel will behave strangely, and performing actions within it will be difficult. This is because the Python server will compete with Excel for CPU usage. «But why does this happen? Doesn't our server use threads?» Yes, the server uses threads, but not at the overall server level—there is a part of the code that blocks. This part is found in line 29 or 28 of the original code. Looking at it, you can see that this line contains a call to the accept function. It is precisely this function that blocks and competes with Excel for CPU usage.

Is there a way to solve this problem? Yes, there is. The key point is how much effort we are willing to invest in the solution. I say this because you could create a thread within VBA so that the Python script runs directly in a thread. Or you could look for another solution to this same problem.

Either way, something needs to be done to prevent the accept function from continuing to compete with Excel for CPU usage.

Let's start using the SELECT function

One of the simplest solutions, which doesn't require complex programming maneuvers, is to use the select function in the server code. This function has already appeared in other code within this socket series. You can verify this by looking at line 69 of the mini-chat server code written in C++. This code can be found in the article: Market Simulation (Part 09): Sockets (III)

As you can see, it's not such a complicated solution. However, if you want to do something similar in Python, the code will be somewhat different from the C++ version. If you use code identical to the C++ version, the program will crash when calling select instead of accept. To make it clearer, let's take a look at how the server code is implemented in Python. This can be seen in the script below:

01. import socket as sock
02. import threading as thread
03. import select
04. import xlwings as xl
05. import time
06. 
07. CONN_LIST = []
08. INPUTS = []
09. WB = None
10. LINE = 1
11. 
12. def WriteMessageInExcel(msg):
13.     global WB
14.     global LINE
15.     if WB == None:
16.         WB = xl.Book.caller()
17.     WB.sheets[0].cells(LINE, 1).value = msg
18.     LINE = LINE + 1
19. 
20. def NewClientHandler(conn, addr):
21.     global CONN_LIST
22.     CONN_LIST.append(conn)
23.     print(f"Client [%s:%s] is online..." % addr)
24.     while True:
25.         msg = conn.recv(512).decode().rstrip(None)
26.         if msg:
27.             print(f"Message from [%s:%s]: {msg}" % addr)
28.             for slave in CONN_LIST:
29.                 if slave != conn:
30.                     slave.send(f"[{addr[0]}]: {msg}\n\r".encode())
31.             if msg.lower() == "good bye":
32.                 break
33.     print(f"Client [%s:%s] is disconnecting..." % addr)
34.     conn.close()
35.     CONN_LIST.remove(conn)
36. 
37. def InitServer(HOST, PORT):
38.     global INPUTS
39.     server = sock.socket(sock.AF_INET, sock.SOCK_STREAM)
40.     server.bind((HOST, PORT))
41.     server.listen()
42.     INPUTS.append(server)
43.     WriteMessageInExcel("Waiting connections...")
44.     while True:
45.         read, write, err = select.select(INPUTS, [], [], 1)
46.         for slave in read:
47.             if slave is server:
48.                 conn, addr = slave.accept()
49.                 conn.send("Welcome to Server in Python.\n\r".encode())
50.                 thread.Thread(target=NewClientHandler, args=(conn, addr)).start()
51.                 WriteMessageInExcel(f"Client [%s:%s] is online..." % addr)
52.         WriteMessageInExcel("Ping...")
53.         time.sleep(1)        
54. 
55. if __name__ == '__main__':
56.     InitServer('localhost', 27015)

Python Code

Please note that only minor changes have been made. We are doing this step by step so that you can understand exactly what is being modified. Notice that we now have a few additional elements between lines 08 and 09. They serve as support for both select and Excel via xlwings.

To make code writing, debugging, and information display easier, we placed a small procedure in line 12. Its purpose is to input data into Excel. Notice that in line 15, we check whether the variable has been initialized; since it will not be initialized on the first call, line 16 will execute—but only once. Then, in line 17, a message is written to the current worksheet row, and in line 18, the row counter is incremented so that the next call is displayed on the following row.

I know this code is not very visually appealing, but aesthetics are not the priority here—functionality is. The most important thing is that it works, not that it looks beautiful or is pleasant to use from VBA.

Now, I want to draw your attention to line 45 of this script. Notice that here we make a call to select. Unlike the C++ code mentioned earlier, here we have an additional parameter. This parameter has an exact value of one. It specifies how long select will wait for something to happen. This time is expressed in seconds, so select will wait one second before proceeding. You can use a smaller value and set it as a floating-point number. But if you're unsure how to do that, don't worry—we'll explain it later. However, it's important to note the following: if we do not specify this value, select will block until some event occurs on the server socket.

To better understand this, note that in line 52, we print a message to Excel, and in line 53, we wait another second, thus creating a two-second delay in the loop. For our purposes, this is more than sufficient. Here's what will happen: if line 45 blocks execution while waiting for an event on the socket, the ping will not be sent every two seconds; if there is no such blocking, the ping will be sent. When running this script via VBA, we will obtain the following:

Okay, but why do we need the for loop in line 46? It is needed to check which event occurred on the socket, given that we haven't yet entered the thread. Thus, if a user tries to connect, this loop will be used to determine what happened. That way, everything else remains the same. One important point: without this for loop, the server would hang waiting for something to happen in the accept function, which is on line 48. But with the check in line 47 inside the loop, we can detect that a client is trying to connect. Although this server still doesn't allow Excel to run without competing with Python, we have already significantly improved the system.

However, the problem arises in line 44. The fact that at this line we enter an infinite loop in the Python script causes the script to compete with Excel for CPU usage. But we cannot remove this loop, because if we did—given how the script is implemented—the server would shut down. Therefore, we need to make some changes to avoid this scenario. That is, we need to be able to enter and exit the script without the server closing its connections. But before that, let's look at another piece of intermediate code that we need to create. To lighten the mood a bit, let's move on to a new topic.

A mini-chat server in a class

Although many people don't like developing or implementing object-oriented code, it has clear advantages. First and foremost, it allows us to make the server implemented in Python easier to operate, at least from a programming perspective. This is because we can very easily remove the thread from the version shown in the previous topic. In doing so, we will achieve functionality that is very similar to the C++ implementation. To accomplish this, we will create new code, but it will be fully compatible with what we have seen up to this point. Of course, at this early stage, we won't be running it from Excel. We'll be using Python in its pure form. The new script, which uses OOP, is shown below:

01. import socket as sock
02. import select
03. import time
04. 
05. class MiniChat:
06.     def __init__(self, HOST : str = 'localhost', PORT : int = 27015) -> None:
07.         self.server = sock.socket(sock.AF_INET, sock.SOCK_STREAM)
08.         self.server.setblocking(0)
09.         self.server.bind((HOST, PORT))
10.         self.server.listen()
11.         self.CONN_LIST = [self.server]
12.         print(f"Waiting connections in {PORT}...")
13. 
14.     def BroadCastData(self, conn, info) -> None:
15.         for sock in self.CONN_LIST:
16.             if sock != self.server and sock != conn:
17.                 sock.send((info + "\n\r").encode())
18. 
19.     def ClientDisconnect(self, conn) -> None:
20.         msg = "Client " + str(conn.getpeername()) + " is disconnected..."
21.         self.BroadCastData(conn, msg)
22.         print(msg)
23.         conn.close()
24.         self.CONN_LIST.remove(conn)
25. 
26.     def Shutdown(self) -> None:
27.         while self.CONN_LIST:
28.             for conn in self.CONN_LIST:
29.                 self.CONN_LIST.remove(conn)
30. 
31.     def LoopMessage(self) -> None:
32.         while self.CONN_LIST:
33.             read, write, err = select.select(self.CONN_LIST, [], [], 1)
34.             for sock in read:
35.                 if sock is self.server:
36.                     conn, addr = sock.accept()
37.                     conn.setblocking(0)
38.                     self.CONN_LIST.append(conn)
39.                     conn.send("Welcome to Server Chat in Python.\n\r".encode())
40.                     self.BroadCastData(conn, "[%s:%s] entered room." % addr)
41.                     print("Client [%s:%s] connecting..." % addr)
42.                 else:
43.                     try:
44.                         data = sock.recv(512).decode().rstrip(None)
45.                         if data:
46.                             if data.lower() == '/shutdown':
47.                                 self.Shutdown()
48.                             self.BroadCastData(sock, str(sock.getpeername()) + f":{data}")
49.                         else:
50.                             self.ClientDisconnect(sock)
51.                     except:
52.                         self.ClientDisconnect(sock)
53.             time.sleep(0.5)
54. 
55. if __name__ == '__main__':
56.     MyChat = MiniChat()
57.     MyChat.LoopMessage()
58.     print("Server Shutdown...")

Python Code

If you have questions about this code, you can refer to the Python documentation. But I believe you won't have much difficulty understanding the script. Although it may seem strange at first glance, it's all about how Python is implemented. Refer to the documentation to understand how everything works. But let's do a brief overview for greater clarity.

The class starts at line 05 and ends at line 53. In line 56, we use the class constructor—that is, the __init__ procedure located in line 06. This procedure effectively creates the server, although it will not yet be listening for connections. However, when init completes, the call happens in line 57. It will direct execution to line 31. Notice that in this procedure, at line 31, we have removed the thread. That is, the server will now use the same type of control as in the C++ server. Note that we are still in a loop at line 32. However, I want you to understand the following: this loop can be either inside the class or outside it, and the server will still be able to perform its job.

Why is this so important to us? The reason is that if we place the loop outside the class, we will need a way to check whether the server can be active. With the loop inside the class, to create the server we only need lines 56 and 57. This allows us to package this server for import into other Python scripts.

Now, here's the interesting part: If we ignore the fact that there is a loop in line 32, this same server can be packaged and included in Excel so that xlwings can use it—not in the way shown up to now. However, this implementation was done intentionally. This way, we can easily understand the changes between versions of the Python server. Thus, we can easily see how we can stop the Python server from competing with Excel for CPU usage. Now, assuming you truly understand the code, we will modify it, so that it can be easily packaged and used in any script.

01. import socket as sock
02. import select as sel
03. 
04. class MiniChat:
05.     def __init__(self, HOST : str = 'localhost', PORT : int = 27015) -> None:
06.         self.server = sock.socket(sock.AF_INET, sock.SOCK_STREAM)
07.         self.server.bind((HOST, PORT))
08.         self.server.listen()
09.         self.CONN_LIST = [self.server]
10.         print(f"Waiting connections in {PORT}...")
11. 
12.     def __BroadCastData(self, conn, info) -> None:
13.         for sock in self.CONN_LIST:
14.             if sock != self.server and sock != conn:
15.                 sock.send((info + "\n\r").encode())
16. 
17.     def __ClientDisconnect(self, conn) -> None:
18.         msg = "Client " + str(conn.getpeername()) + " is disconnected..."
19.         self.BroadCastData(conn, msg)
20.         print(msg)
21.         conn.close()
22.         self.CONN_LIST.remove(conn)
23. 
24.     def ExistConnection(self) -> bool:
25.         if self.CONN_LIST:
26.             return True
27.         return False
28.     
29.     def CheckMessage(self, sleep) -> str:
30.         read, write, err = sel.select(self.CONN_LIST, [], [], sleep)
31.         for sock in read:
32.             if sock is self.server:
33.                 conn, addr = sock.accept()
34.                 self.CONN_LIST.append(conn)
35.                 conn.send("Welcome to Server Chat in Python.\n\r".encode())
36.                 self.__BroadCastData(conn, "[%s:%s] entered room." % addr)
37.                 print("Client [%s:%s] connecting..." % addr)
38.             else:
39.                 try:
40.                     data = sock.recv(512).decode().rstrip(None)
41.                     if data:
42.                         if data.lower() == '/shutdown':
43.                             self.CONN_LIST.clear()
44.                         self.__BroadCastData(sock, str(sock.getpeername()) + f":{data}")
45.                         return str(sock.getpeername()) + f"> {data}"
46.                     else:
47.                         self.__ClientDisconnect(sock)
48.                 except:
49.                     self.__ClientDisconnect(sock)
50.         return ""
51. 
52. if __name__ == '__main__':
53.     MyChat = MiniChat()
54.     while MyChat.ExistConnection(0.2):
55.         info = MyChat.CheckMessage()
56.         if info:
57.             print(f"{info}")
58.     print("Server Shutdown...")

Python Code

Alright. Alright. In the script shown above, we've made some improvements to the code so that it can do what was previously discussed—that is, it can be packaged and used in other applications. But let's see what happens now, since this code will no longer be waiting for network events.

First, the BroadCastData and ClientDisconnect procedures are private within the class, meaning they cannot be accessed directly from outside the class body. Although there are ways to access these private procedures, I won't explain how to do this, so you won't be tempted to do so in other cases. Notice that in Python, you can define private elements and should avoid trying to access procedures or functions that are considered private. If we try to do so, Python will report an error. Again, there is a way in Python to access private procedures, but I'll leave that to you. However, I strongly advise against using this practice, as it violates one of the premises of code encapsulation. If a procedure or function is private, there is a reason for it.

There are only two public procedures left: ExistConnection and CheckMessage. ExistConnection returns a boolean value, as seen in line 24. This return value is intended so that, outside the class body, we can check whether the server is active or not—i.e., whether there is an active connection.

The CheckMessage procedure, located at line 29, returns the message sent by some client. But this is not always the case. Notice that in line 43, the server shuts down at the client's request. This may seem a bit radical, as in principle, any client should not be able to do something like this. But since this code is for educational purposes only, I see no problem in implementing it this way.

In any case, in line 45, we will return what the client wrote. We'll see that we use both the client's name and the typed message as the function result. This is important for analysis and experimentation.

But what really interests us—and here begins the most interesting part of this code—is the content of lines 53 to 58. Note that the loop inside the class in the previous code has been removed and moved to this point. More precisely, it is now at line 54. In line 55, we intercept whatever the client sent to the socket, and if the value is relevant—that is, it contains any data—we output the received information to the console using Python; this is done in line 57. Thus, everything any client sends will be visible to the others and, in addition, can be monitored directly on the terminal where the server is running.

Now, one point that might interest you: how long will this server remain idle if nothing happens on the socket it's monitoring? Remember, in the previous code we mentioned that the select function would wait for one second? That was due to a specific value.

Here, we will wait a shorter amount of time. Since we don't want to change the class code, we'll pass the value directly into the class. This is done in line 54. Notice that here we define a value less than one. In this case, the value set is 0,2. This is the time in seconds it will wait—that is, less than one second—before the select function breaks and continues code execution.

Depending on the work that needs to be done with the information sent to the socket and the processing time, this value can be reduced even further. Remember that, unlike the model where we used threads, here the server will only monitor what each connection does. In the model where we used threads, each connection was independent. This makes processing in this model slightly slower than when using threads for reading and writing to the socket.

Now, pay attention to the following details. This server, which can be seen in the code above, works perfectly when run in the prompt. Even with the limitation that with many clients, it will read connections sequentially.

But for bidirectional data exchange between Excel and MetaTrader 5, this Python script is not yet fully suitable. Although it is now at a much more advanced level, everything we've seen so far works, but not in a way that allows the Python script to run without competing with Excel for CPU usage.

Even this last script does not allow us to achieve that, although it gives a good idea of how to do it. If you're unsure, don't worry—it's actually not that easy to notice; you need some experience in parallel programming.

You may have noticed that in most of the recent scripts, we haven't used xlwings. Why? Was it not suitable for this? In reality, anything you use outside the basics of Python will not provide a good experience when using Excel in conjunction with Python. Let me clarify this to avoid misinterpretation.

When we develop something in xlwings or any other package that allows reading and writing directly to Excel, we must note that all programs, functions, or procedures execute and then complete their task. They do not remain inside a loop, and no matter how hard you try to do things differently, such solutions will never achieve what we really need. The problem is that the server script must always remain in a loop, regardless of whether it's waiting for something on the socket or not.

The point is that the script will run in a loop. In the case of a client, the approach may be somewhat different. The client connects to the server, and as soon as that happens, the server will send the information that the client should receive. When the client receives it, it can close the connection, which terminates the script and makes the data available for proper processing in Excel. This processing can be done either from Python or from VBA.

But that's actually not the most important point. I'd like to note that if we were using a client instead of a server, the code would be completely different. This is because we could use a timer created in VBA to run the client in Python without any issues regarding CPU competition with Excel. Therefore, I argue that the packages many people use regularly will not be sufficient. We need a different solution.

Concluding thoughts

In today's article, we explored how to create a Python server that does not block while waiting for socket events. However, none of the code presented fully achieves the intended goal. As programmers, we must step out of our comfort zones. Programming is about finding solutions to problems, which is very different from what many imagine—it's not simply about copy-pasting. Programming is something more artistic, requiring us to go beyond, to study, and to seek solutions to problems.

As mentioned at the end of the previous topic, using a client developed in Python alongside Excel ensures that Excel does not behave strangely to the point of becoming unusable. However, I want to show you how to make a Python server work inside Excel while still allowing Excel to be used normally. Many might think: «But why keep Excel open? Perform the calculations, update everything with Pandas, xlwings, or any other package that works with Excel files, and that's it».

That would be the simplest solution. But I want to demonstrate that it is possible to achieve what we originally envisioned. To understand this motivation, imagine for a moment an average user. No matter how beautiful and simple the interface created in Python might be—so that the user doesn't need Excel for data analysis—many will still doubt our solution. They will rightly look for a programmer who can solve the problem in a way that adapts to their workflow.

The point is that the same user will want to use Excel to control what will be executed in MetaTrader 5. And you, as a programmer and aspiring professional, must understand that if a solution doesn't yet exist, you will have to create it. Therefore, the habit of copying and pasting won't suffice.

In the next article, we will explore how to do this. So study all this material carefully—it will be necessary for understanding what will be done in the next part.

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	Enables interaction between graphical controls and the user (required for both replay and real market trading).
Services/MarketReplay.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 of the connection with an external socket.
Indicators\Mini Chat.mq5	Implements a mini-chat using an indicator (requires the server to function).
Experts\Mini Chat.mq5	Implements a mini-chat via an expert advisor (requires the server to function).