Market Simulation (Part 15): Sockets (IX)

Introduction

In the previous article, "Market Simulation (Part 14): Sockets (VIII)", we explored how certain things can be done using Python without relying on third-party packages or tools. I don't want to discourage you from using packages that help accomplish tasks in Python, but the purpose of these demonstrations is to encourage you to study concepts with greater interest. There's no point in relying on external packages or solutions if we can do everything based on what our favorite language allows.

I believe that in that article, I managed to spark your curiosity about certain matters whose inner workings many people don't understand. I think that most people, if they've taken any course or studied standard materials on this subject, have never heard of some technologies. One example is COM technology, which we briefly mentioned in the previous article. However, knowing how to use these methods will help you at all stages of programming.

In this article, we will explain one of the possible solutions to what we have been trying to demonstrate—namely, how to allow an Excel user to perform an action in MetaTrader 5 without sending orders, or opening or closing positions. The idea is that the user employs Excel to conduct fundamental analysis of a particular symbol. And by using only Excel, they can instruct an expert advisor running in MetaTrader 5 to open or close a specific position.

So far, everything shown and demonstrated has been aimed exclusively at data transmission, as if we were using a chat. However, I think that for those of you less experienced with sockets, it was easier to understand the core concepts through these methods. Remember, we have only scratched the surface of a large iceberg called «sockets». I advise you to study this technology further, because once you understand how it works, you'll come to love it and be amazed by the possibilities it opens up.

But let's move straight to today's topic. Since most of the things that need to be implemented are relatively complex, while others are quite simple, we'll go through them at a leisurely pace. This way, even if we don't show everything, you'll understand how it works. If this goal is achieved, you'll be able to adapt what is shown and create your own solution—one that will likely far surpass what is presented here today. Our main objective is educational, so let's begin.

Creating the server in Python

As explained in previous articles, it is not possible to create and run a server directly within Excel. We could do it, but we would have to work hard to ensure that the server does not interfere with our use of Excel. One way to achieve this is to create the server in a thread. However, creating and running a server on an Excel thread is a complex task. Of course, it's not the most difficult task, but it is certainly much more complex than the solution we will present here.

The first thing to do is to create a standalone server in Python. This server will actually access the Excel spreadsheet. However, there are some points to consider. Again, the idea is purely educational. What we present can be significantly improved. Below, we see the main Python server code. I call it the "main" code because it can be refined and adapted to our needs, requiring adjustments only in the necessary places. But before we do that, let's understand how it works. This will help us understand what changes can and should be made.

001. import socket as sock
002. import select
003. import sys
004. from win32com import client as win32
005. 
006. class LinkMT5Excel:
007.     def __init__(self, host, port, sheet, cell) -> None:
008.         try:
009.             self.__CMD = ''
010.             self.__MT5 = None
011.             self.__server = sock.socket(sock.AF_INET, sock.SOCK_STREAM)
012.             self.__server.setblocking(False)
013.             self.__server.bind((host, int(port)))
014.             self.__server.listen()
015.             self.__CONN_LIST = [self.__server]
016.             EXCEL = win32.GetActiveObject('Excel.Application')
017.             self.__SHEET = EXCEL.Worksheets(sheet)
018.             self.__COLUNN, L = cell.split('$')
019.             self.__LINE = int(L)
020.         except:
021.             self.__CONN_LIST.clear()
022. 
023.     def __WriteInExcel(self, index, msg) -> None:
024.         try:
025.             if self.__SHEET:
026.                 self.__SHEET.Range(self.__COLUNN + str(self.__LINE + index)).Value = msg
027.         except:
028.             self.__SHEET = None
029.             self.__ShutdownServer()
030. 
031.     def __ReadInExcel(self, index) -> str:
032.         try:
033.             if self.__SHEET:
034.                 return self.__SHEET.Range(self.__COLUNN + str(self.__LINE + index)).Value
035.         except:
036.             self.__SHEET = None
037.             self.__ShutdownServer()    
038.         return ''
039. 
040.     def __MT5_Disconnect(self, conn) -> None:
041.         if conn == self.__MT5:
042.             self.__WriteInExcel(2, 'MetaTrader 5 is offline.')
043.             self.__MT5 = None
044.         if conn:
045.             conn.close()
046.             if self.__CONN_LIST:
047.                 self.__CONN_LIST.remove(conn)
048. 
049.     def __ShutdownServer(self) -> None:
050.         for conn in self.__CONN_LIST:
051.             conn.close()
052.         self.__CONN_LIST.clear()
053. 
054.     def __Command(self, cmd, conn) -> bool:
055.         if cmd.lower() == '/force server shutdown':
056.             self.__ShutdownServer()
057.             return True
058.         elif cmd.lower() == '/shutdown':
059.             self.__MT5_Disconnect(conn)
060.             return True
061.         self.__CMD = '' if cmd.lower() == 'n/d' else cmd
062.         return False
063.     
064.     def __Refused(self, conn) -> bool:
065.         conn.send('Connection Refused...\n\r'.encode())
066.         conn.close()
067.         return False
068. 
069.     def __Checking(self, conn) -> bool:
070.         try:
071.             info, cmd = conn.recv(512).decode().rstrip(None).split(':')
072.             if info.lower() != '<mt5 with excel>':
073.                 return self.__Refused(conn)
074.             if self.__Command(cmd, conn):
075.                 return True
076.             if (self.__MT5) or (cmd.lower() != 'mt5'):
077.                 return self.__Refused(conn)
078.             self.__MT5 = conn
079.             self.__WriteInExcel(2, 'MetaTrader 5 is online.')
080.         except:
081.             return self.__Refused(conn)
082.         return True
083.     
084.     def __SwapMsg(self, rec, conn) -> None:
085.         try:
086.             if rec:
087.                 data = conn.recv(1024).decode().rstrip(None)
088.                 if data:
089.                     if '/' in data:
090.                         if self.__Command(data, conn):
091.                             return
092.                     else:
093.                         self.__WriteInExcel(4, data)
094.                 else:
095.                     self.__MT5_Disconnect(conn)
096.                     return
097.             conn.send((self.__ReadInExcel(3) + f'[{self.__CMD}]').encode())
098.         except:
099.             self.__MT5_Disconnect(conn)
100. 
101.     def Run(self) -> None:
102.         self.__WriteInExcel(0, 'Server online.')
103.         self.__MT5_Disconnect(None)
104.         while self.__CONN_LIST:
105.             read, write, err = select.select(self.__CONN_LIST, [], [], 0.5)
106.             for slave in read:
107.                 if slave is self.__server:
108.                     conn, addr = slave.accept()
109.                     conn.setblocking(False)
110.                     if not self.__Checking(conn):
111.                         continue
112.                     self.__CONN_LIST.append(conn)
113.                     self.__SwapMsg(False, conn)
114.                 else:
115.                     self.__SwapMsg(True, slave)
116. 
117.     def __del__(self) -> None:
118.         for n in range(3):
119.             self.__WriteInExcel(n, '')
120.         self.__WriteInExcel(0, 'Server offline...')
121. 
122. if __name__ == '__main__':
123.     if len(sys.argv) == 5:
124.         Mt5_Excel = LinkMT5Excel(sys.argv[1], sys.argv[2], sys.argv[3], sys.argv[4])
125.         Mt5_Excel.Run()
126.         del Mt5_Excel
127.     else:
128.         print(f'Usage: {sys.argv[0]} <HOST> <PORT> <SHEET> <CELL>')

Python Code

If you have been following this stage of the replay/simulation service, where we focused on explaining sockets, nothing in this code will seem strange to you. And if you've been studying Python for a long time, this code will be even easier to understand. However, if that's not your case, don't worry—we'll now go through what's happening here. So, for those following along in sequence, let's be patient with others, especially those with less experience in this area, because there are things in this code that need to be well understood.

Now, between lines 01 and 04, we import some necessary packages. Notice that we are not using any packages beyond the standard Python installation, and we are not doing anything too complex at this stage. You could use packages like pandas, xlwings, openpyxl, or any other package to perform some of the steps shown in this article. But to avoid creating an impression of discrimination toward certain packages, we will only use the standard Python installation. This way, it will be easier for us to choose whichever package we prefer and use the option we find most suitable. Our goal is to achieve maximum simplicity without tipping the balance in any particular direction.

After the mentioned imports are done, we start creating a class in line 06. We could do without classes, but if we use them, we can gain other advantages. So, in line 07, we begin by defining the class initializer. Here, we receive several arguments that tell us about important elements necessary for the server to function properly. Look at where these parameters are used to understand what is expected and how to improve this same code. 

In lines 09 and 10, we declare and initialize two variables that will be important later. In line 13, we start using the passed arguments. Notice that we tell the server the host—that is, the location from which we expect to receive a connection. If the host value is 0.0.0.0,it means we will accept any client address, but it could also be a specific value. That remains at your discretion. An important point in this line is that, since the server initialization will be done from Excel, the passed arguments will be strings. Therefore, it is necessary to convert the string value into an appropriate value, which in this case is an integer, to specify the port to be used. After that, Python will know how to correctly interpret the value being used. The remaining lines (i.e., from 11 to 14) are responsible for initializing the socket so that the server can monitor the port and wait for connections. If you have doubts about this, refer to the previous articles.

Now let's go back to line 08, because although the previous steps should not generate failures without good reason, in line 08 we open a point for analyzing possible errors in the code. Such failures occur during program execution. Therefore, so that the user does not see strange things when the script runs, we ensure that all exceptions are handled by the code itself.

In line 16, we try to capture the active Excel session. Since the server initializes itself, we will successfully accomplish this task. However, in line 17, there is something that could cause failures. At this point, we try to access an Excel worksheet. This can happen at any time, simply because the sheet does not exist in the workbook, and we need it to exist. If successful, in line 18, we capture a specific cell that will be used by the server. Notice that we split this process into two steps. In line 18, we enter the column and a numerical value. This numerical value is converted to an integer in line 19 so that some simple calculations can be performed on the server. But don't worry—this will become clear later.

Now you need to understand that if a failure occurs during this initialization, line 21 will shut down the server and prevent any attempt to send data to Excel. It's important to do this because access to something was not obtained or it was not configured correctly, and we don't want the user to see messages they don't understand.

Now we tackle the difficult task of making everything work. We start with line 23, where we have a procedure that will attempt to write something to Excel. But we won't write just anywhere; we will do it in a fairly controlled manner. Therefore, we need an index to move relative to the column and row specified during initialization. Since the goal is purely educational, we won't use the column, only the row. Thus, based on the value of the first row, we will try to write to the sheet specified during initialization. However, since this process may fail, in line 24, we tell Python to wait for an error.

In line 25, we check whether the variable contains something valid. This is important, as it could happen that during shutdown, the sheet does not exist, and we don't want to enter a loop without reason where the server tries to close, an exception is generated, which triggers another exit attempt, the procedure is called again, generating another exception, and so on. To avoid this, we perform the check in line 25. Then, in line 26, we try to write data to the spreadsheet. At this moment, an error may occur because the user might have accidentally deleted the sheet the server needs from the workbook. In this case, line 27 and the corresponding code will be executed, and the server will shut down.

We move to line 31. This line captures the content of a specific cell in the spreadsheet. This cell has as its reference the cell specified during initialization. This is why it's so important not to rush and to understand everything that's happening. Just as attempting to write to a cell can cause the process to fail, the same can happen here. But if the reading happens as expected, line 34 will return the cell's value for the server to use.

You might have thought: if this function in line 31 can read the Excel spreadsheet, why not simply specify during initialization which sheet and cell it is, so that the server can look for the rest of the information directly in Excel? If you thought of this, I sincerely congratulate you—it means you've already noticed a possible improvement that can be made here in the Python script. But let's move on, as there is still more to see.

In line 40, there is a procedure that helps the Excel user know when MetaTrader 5 becomes unavailable for some reason. This happens when the device is disconnected. This procedure is simple and requires no further explanation. In line 49, there is another equally simple procedure for shutting down the server for any reason. Many of these reasons are related to failures during the execution of the server script, but there are others that we will discuss later. For now, don't worry about it. Additionally, in line 54, we have a very interesting function. From here, things get truly interesting. But first, let's look at what happens in the function at line 54.

It is quite simple and is designed to parse any command that might have been sent to the socket. This command must be executed by the server. At the moment, we have only two commands. The first is in line 55 and is intended to forcibly shut down the server—simple and straightforward. The second command is in line 58 and forcibly disconnects the client, which in this case is MetaTrader 5. If no command execution request has been received by the server, line 62 will return false. In other words, what was sent was not a command.

But why do we return false value? The reason lies precisely in the function at line 69. Its main task is to check whether a client attempting to connect to the server can be accepted. This is where it gets interesting, because in principle, anyone could connect to the server. However, this function is designed to prevent that. How does it do that? It simply tests what the client sends to the socket as soon as the server agrees to check the connection. In line 72, we now check the initial connection header. This header should only be present during the connection attempt; at other times, it is not needed. Later, you will understand how all this actually works.

It should be clear to you now that in line 71, a separation is made between the header and any transmitted command. The reason is simple, and you will understand it in the next article, where we will cover the corresponding part of the VBA code from Excel. If the header provided by the client does not match what the server expects, the function in line 64 will execute. This function informs the client that the server is disconnecting it because it was not recognized.

If the header matches what is expected, in line 74, we try to execute the command sent by the client. If it's not a command, line 76 will check whether there is already a connection with MetaTrader 5. If there is no connection or if the command differs from what is expected (indicating that the client is not MetaTrader 5), the connection will be rejected. In the latter case, in lines 78 and 79, we will get information that MetaTrader 5 is online. If at any point an exception occurs, the connection will be rejected due to the execution of line 80.

Now we move on to the crucial procedure, which is in line 84. And what does it do? It manages the exchange of messages between MetaTrader 5 and Excel. Pay close attention, because although this procedure is relatively simple, without understanding it, you won't be able to adapt the code to what you want to do. Admittedly, after going through the next few articles, all of this will make sense. But for now, let's try to understand what's happening in the Python script.

Since errors may occur, we run the code, telling Python that we expect them to appear—this is done in line 85. If the caller indicates that this procedure should read the socket, line 86 will complete successfully, and we move on to reading part of the socket. This is done in line 87.

Now read carefully, because the following explanation will be extremely important later on. In line 88, we check whether we received anything on the socket. If not, we disconnect MetaTrader5. If there is something on the socket, in line 89, we check for the presence of a specified character in the received message. If such a character exists in the message, it means it is a command from MetaTrader 5, which will lead to the execution of line 90. But if this execution fails, nothing further will be published. Looking at the command code, you will be able to understand what data is expected from MetaTrader 5. When we discuss the MQL5 code, we will return to this point. For now, it's enough to know that MetaTrader 5 can send commands to the server for execution.

Okay, but what if it wasn't a command? This happens because the check in line 89 failed. So, what happens then? In that case, line 93 will execute. It will write to a specific cell the information sent by MetaTrader 5 in response to Excel's request. But what is this request? Now we come to the truly interesting part: line 97. Look at what we are doing in this line. Here, we use the server to send a request to MetaTrader 5 through the socket. Such a statement can be presented in two ways:

• The first is in the form of content read from a specific cell or range of cells in Excel. Although for simplicity, we are not using a range, but the content of a specific cell.
• The second is that, inside the parentheses, we will send, according to Excel's instruction, some command for MetaTrader 5 to execute.

Later, we will discuss how such commands or requests in Excel will be executed in MetaTrader 5. For now, note that we have a fairly simple protocol for interaction. That is, essentially, everything that is in the cell or as a command sent by Excel will be passed to MetaTrader 5. The server will not actively participate in this stage of message exchange but will only log messages at the points specified in this procedure so that VBA and MQL5 can do their work.

However, if we want to increase or improve the quantity and quality of information exchanged between Excel and MetaTrader 5, we can use Python to simplify the work that would otherwise have to be done in VBA or MQL5. But since we want to be as didactic as possible, we will use a very simple message protocol, with just one cell in Excel. If you understand how this is done, you'll be able to do anything you want.

Notice that all cell positions used here start from the first cell specified during server initialization. It's important that you understand these points, since everything else will be done directly in Excel VBA. This will be quite simple and easy to do and execute.

Okay, we've reached the end. This brings us to line 101. Almost everything regarding this procedure has already been covered in previous articles about sockets, but there are a few points worth noting. For example, in line 113, we find something that might seem strange at first glance, although it's not, if you think about it and follow the sequence. If the check in line 110 is successful, a message will be sent to the client that has just connected. Usually, this a greeting.

But here, we will do something completely different. Since Excel will not stay online for long, it understands that the information to be sent is directed to MetaTrader 5, so it's important to understand the procedure at line 84, which we described a while ago. Thus, in the first call, we will not read the socket, but only write to it. When the server receives a response from MetaTrader 5, line 115 will execute, and this time, we will read the socket and write to it. This way, we will remain in the loop, constantly receiving information from MetaTrader 5 and passing that information to Excel.

And finally, we have a procedure that many will find quite strange. This is because it is the class destructor. Just as we had a constructor called __init__, we also have a destructor, which in this case is called __del__. Usually, this destructor is not included in Python script code, since Python has a garbage collector that, when the script finishes, deletes objects and frees the memory they used. However, the function of this destructor is somewhat more noble. Essentially, it forces Excel to display a message in the place where it is normally indicated that the server is online, reporting that the server is offline. You can view it directly in Excel, without needing to use other media or resources. The rest of the code is so simple that it requires no further attention from us.

Concluding thoughts

With the explanations provided here, we have concluded the part dedicated to the Python server. The purpose of this server is to be as didactic and simple as possible to work with in Python, using only the packages included in the standard language installation. However, the reader themselves can create something far more complex and practical by using other packages and components for working with Excel files. It is even possible to generate the entire Excel spreadsheet, as well as many other functions, directly within this Python code.

But I do not want to complicate the situation by favoring any specific Python package. I know there are many excellent packages that allow you to perform a wide variety of tasks and even completely replace the need to create something using Excel. However, as mentioned elsewhere in this series, the idea here is to satisfy a client or user who refuses to use any tool other than Excel. The goal is to create a spreadsheet for fundamental analysis of a given symbol, so that we have the ability to buy or sell within certain price ranges. Some of you might ask: «Why not place the order directly on the symbol's chart at the desired positions?» That way, you wouldn't have to constantly work in Excel, and you could do it directly in MetaTrader 5».

I fully understand those who think this way. But there are also people who lack the patience or desire to look at a chart, placing and removing orders, opening and closing positions. They simply prefer to organize everything using a few programs—usually those they have mastered and on which they have developed their analysis methods—and they do not want to give that up. Therefore, these people want you, as programmers, to create some method or means to send orders, close or open positions within a certain price range using that specific program, which in this case is Excel.

But at the same time, they do not want to worry about understanding how it will be done. You are simply hired and paid to do everything possible. This is precisely what I am showing: how to implement it, at least partially. Since you, as programmers, may want to do it differently or with slight variations, personally, that does not concern me much. What truly brings me satisfaction is knowing that by reading these articles, you will be able to create some things you might have thought were impossible. You can achieve anything, as long as you study and dedicate time to it. In the next article, we will cover the VBA part of Excel, where we will analyze how to make this server work together with Excel. See you later in the next article from this series.

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/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).
Code in Python\Server.py	Creates and maintains a Python socket for communication between MetaTrader 5 and Excel.
Scripts\CheckSocket.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).