MetaTrader Meets Google Sheets with Pythonanywhere: A Guide to Secure Data Flow

Introduction

Accessing and analyzing trading data is very important for traders of MetaTrader to avoid mistakes and to decide better in the future. There are built-in tools and functions for exporting data but they don't have capability to export to cloud services. Automatic exporting charts, history and log data to cloud services can be a significant approach for traders' decision making process.

There are different ways and tools to transfer data from MetaTrader to cloud solutions such as google sheets, some are free but less secure and some are secure but not free or with limited access. Finding an appropriate way for cloud exports which costs less can be a headache for traders focusing on benefitting by analyzing their traded data in their future trades.

Traders often lose important trades and other useful information as MetaTrader lacks an automatic backup system to cloud services. Traders struggle to get benefited in using data visualization and analysis tools that are readily available in the cloud. This article aims to show how data transfer from MetaTrader to Google sheets can be automated without extra costs and with high security and utilize the easily available solutions to have profitable future trades.

This article is an aid to such traders and developers looking for ways to transfer their data to some cloud solutions or storage. In this article I am going to explain how we can utilize freely available and secure tools like google service account keys and a proxy server hosted on a web based secure and free cloud platform called pythonanywhere.

Why google sheet and service accounts key?

Google sheet is the most valuable solution as it is cloud based and the data saved in there can be accessed anytime and from anywhere. So traders can access trading and related data exported to google sheet and do further analysis for future trading anytime and wherever they are at the moment. We can utilize google sheets data analyzing tools and even integrate it with other tools like google data studio or TradingView. And other traders and helpers can collaborate on the sheets to get their analysis and view points.

I have published an Info Exporter MT5 utility application in mql5 market, free to download and use which uses google's apps script facility for the transfer of data from MetaTrader to google sheet.

While apps script is free and easy to use, it is not secure enough as the deployed url is publicly accessible which makes it possible for anyone with the url to read and write to the script for accessing sheets data. There are other numerous approaches such as workload identity federation and google sheets api which are either complex to use or not free or limitedly free.

Service accounts key is more secure, free and requires less knowledge to set up. 

You can checkout Access Google Sheets using service account key in proxy server video for more details on how to set google account service key and use it in your application.

Why proxy server in pythonanywhere.com?

MetaTrader lacks direct integration with google sheets and its apis. So a proxy server is the best option for transferring data to google sheets. Using a cloud based solution like pythonanywhere gives us availability, scalability, security and reliability. Availability, as it is available even when our machines are offline. Scalability, as anyone with the link can use it as long as they give valid spreadsheet id. Security, as it is well tested and managed by talented engineers. Reliability, as we can always rely on its services (even for the free version).

Pythonanywhere.com provides a justifiable free tier package to users to create a simple proxy server for applications of this kind. The free versioned proxy server url is for three months but is continuously extensible if extended before each three months expiry.  

As a free service, we get an accessible url which is used as an API service in the proxy server which is used to access the google sheets and other necessary stuff like reading from sheets, displaying static pages for information and many more. We get access to consoles for applying terminal commands which is mainly used to install required libraries and other file related, network related works such as editing of error files etc.

We also get access to mysql database to be used with our api service to store information or to cache information. We also get scheduling task features where we can create cron jobs to repeat itself at the specified time of the day so that we don't have to run it manually. We get access to file creation, editing and deleting tools, web interface for viewing error log files, for securing the site, for creating virtualenvs and many more other tools.

I have tried covering all the necessary information to create, edit and host a proxy server on pythonanywhere.com video in which I create an identical proxy server used in this article.

Data flow Diagram 

The concept of data flow in this transfer process is simple enough to understand. MetraTrader acts as the data source, where data necessary for careful analysis to get profitable trading decisions reside, to be transferred to a proxy server hosted on the cloud-based pythonanywhere.com platform. The proxy server writes the received data, using google authenticating packages and credentials created from google account service key, to the authorized google sheet. This process is more clear in the following diagram.

This is one of the free and secure methods of transferring data from MetaTrader to google sheets. Only the authorized sheets are accessed from a proxy server, either locally hosted or  hosted on a tested and secure cloud platform like pythonanywhere. 

Process to create service accounts key 

Google cloud console has numerous rich features which are free and very useful. Google Service Accounts is one of them. Service Accounts is located in IAM & Admin section as shown below:

Before going to manage service accounts make sure you create a project or choose one of the existing projects. The service accounts page looks as below where the Create service account button is located at the top row.

Fill up the necessary steps in the create service account section and you have a service account email created which is used as the email in the sharing sheets step below. Key ID field would be empty which can be created by going to the created service account detail page which is shown below:

The Add key button will give you the option to create a key and download the credentials in json or p12 format. Choose json format and the file is downloaded automatically which is used in the proxy server for authentication.

Next step is to enable sheet api for the project which is located in APIs & Services as shown below where we would need to locate google sheets api and enable it.

The last step is to add the Service Accounts email created above in the shared list of the sheet that is to be used in the proxy server for transferring data. 

I have linked a detailed video on this process in the why google sheet and service accounts key section above.

Proxy server program 

If you have viewed the videos I shared in the above section, why proxy server in pythonanywhere.com, then you should have a better understanding about the codes I am going to explain below. We are going to write a simple flask application that will update the google sheet using google auth package functions and service account key credentials.

First of all we are going to import the necessary library functions in our program

from flask import Flask, request, jsonify
from google.oauth2 import service_account
from googleapiclient.discovery import build

Above libraries need installation using pip if they are not in your system or in your virtual environment. I have explained them in my videos too.

Next we need to objectify the Flask framework so that we can create api endpoints (GET, POST, PUT, DELETE), routers and many more later in our programs. The following line does that.

app = Flask(__name__)

Next step is to specify the location of the credentials json file which was downloaded when the service accounts key was created. We can define scope as well so that it is only limited to specified items i.e. sheets. Then using the credentials and scope credentials object is created which will be used when initiating google api client.

SERVICE_ACCOUNT_FILE = 'service_account.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES
)

The next step is to define a POST api called "update-sheet".

@app.route('/update-sheet', methods=['POST'])
def update_sheet():

We usually use post api to send data to the server. And we also pass headers which include information about the data so that the server can extract data easily.

data = request.json
sheet_id = data['sheet_id']
data_range = data['range']
values = data['values'] 

In the above code, request is a flask object which contains information passed from the client in their post request which includes json data as well. Above code is extracting spreadsheet id, sheet range and values to be written to sheet.

Next we are going to initiate a google client api package passing the credentials object created above.

service = build('sheets', 'v4', credentials=credentials)

This service object is used to update the spreadsheet identified by the extracted sheet_id above.

result = service.spreadsheets().values().update(spreadsheetId=sheet_id, range=data_range, valueInputOption='RAW', body={'values': values}).execute()

Above code tells the google api client service to execute the update the spreadsheet with the values passed on the range passed. Complete code with append api is attached for further investigation where you can find additional append api and code to check for sheet name and if it doesn't exist then create. You can add more api endpoints if you prefer, like delete and more.

MetaTrader program 

The actual mq5 program contains export of terminal information, account information, history deals and history orders, and input section where users can choose which information to transfer. I am going to explain the codes for account information only. Following is the function where we use the AccountInfo.mqh header file to extract account data and format the data into string array for use in StringToCharArray function to create char array as required by WebRequest network function. 

#include <Trade\AccountInfo.mqh>
string GetAccountInfo() {
   CAccountInfo accountInfo;
   string account_info = "[\"symbol\", \"name\", \"currency\", \"company\", \"balance\", \"credit\", " +
      "\"profit\", \"equity\", \"margin\", \"login\", \"trade_mode\", \"leverage\", \"limit_orders\", \"margin_mode\"]";
   StringAdd(account_info, StringFormat(
      ", [\"%s\", \"%s\", \"%s\", \"%s\", \"%f\", \"%f\", \"%f\", \"%f\", \"%f\", \"%d\", \"%d\", \"%d\", \"%d\", \"%d\"]",
      Symbol(),
      accountInfo.Name(), accountInfo.Currency(), accountInfo.Company(),
      accountInfo.Balance(), accountInfo.Credit(), accountInfo.Profit(), accountInfo.Equity(), accountInfo.Margin(),
      accountInfo.Login(), accountInfo.TradeMode(), accountInfo.Leverage(), accountInfo.LimitOrders(),
      accountInfo.MarginMode()
   ));
   return account_info;
}

The above given function prepares data for account information only. ProxyExport.mq5 attached complete code contains three more functions for terminal information, history deals and history orders data preparation. After preparing data we send them to the following WriteToGoogleSheet function where additional string manipulations are carried and SheetExporter is called.

void WriteToGoogleSheet(string terminalInfos, string historicalDeals, string historicalOrders, string accountInfos) {
   if(terminalInfos != NULL) {
      StringReplace(terminalInfos, "\\", "/");
      SheetExporter(terminalInfoName, terminalInfos);
   }
   if(historicalDeals != NULL) {
      SheetExporter(historyDealsName, historicalDeals);
   }
   if(historicalOrders != NULL) {
      SheetExporter(historyOrdersName, historicalOrders);
   }
   if(accountInfos != NULL) {
      SheetExporter(accountInfoName, accountInfos);
   } 
}

void SheetExporter(string sheetName, string data) {
   string headers = "Content-Type: application/json\r\n";
   char postData[], result[];
   string responseHeaders;
   string jsonDataWithSheetName = StringFormat("{\"spreadSheetId\": \"%s\", \"sheetName\": \"%s\", \"data\": [%s]}", spreadSheetId, sheetName, data);
  
   // Convert to char array
   StringToCharArray(jsonDataWithSheetName, postData, 0, StringLen(jsonDataWithSheetName), CP_UTF8);
   
   // Send POST request
   int res = WebRequest("POST", proxyServerUrl, headers, 5000, postData, result, responseHeaders);
   
   // Check response
   if(res != 200) {
     Alert("Account Infos not exported to Google Sheets returned error ", res, " and get last error is ", GetLastError());
   }
}

SheetExporter function adds spreadSheetId and sheetName in addition to data in json string to be sent to the proxy server. Things to remember is that we need to put the proxy server url in the "allow webrequest for listed URL:" section which under tools -> options menu. The same proxy server url needs to be pasted in the following input area.

 

Demo of data flow 

Follow the process I explained in "process to create service accounts key" section above to create your service accounts key and download the json which is needed in authorizing the proxy server to the spreadsheet you want the MetaTrader data to be exported. Then you can either server your own proxy server or use pythonanywhere.com or any other platform you are comfortable with to execute the service_accounts_proxy.py code and get the update api hosted. Remember you can modify the code according to your needs.

The last step would be to compile the ProxyExport.mq5 code and run the ex5 in your chart. You get options to choose which data to export and input sections for your api and spreadsheet id without which the export won't work.

I have tried to capture all the stuff of creating service accounts key credentials, enabling google sheets for the service account and sharing google sheet with the service accounts email.

I have tried to capture all the necessary stuff in pythonanywhere.com that we need to follow in the following gif.

And I have tried capturing all the steps needed to be done in MetaEditor and MetaTrader for the export to work. And I have captured the automatic transfer of MetaTrader information to google sheet live in the following gif.

Trading insights using google sheet

I have the following trading historical data exported to google sheet. I have included only 9 rows.

Where Pips value is calculated as (Exit Price - Entry Price) * multiplier where multiplier is 100 for JPY Pair else 10000, Profit value is calculated as Pips * Lot Size * 10 and Cumulative Profit is calculated as (previous Cumulative Profit + current Profit)

The name of the above sheet is History_Deals according to the way I have coded. You can change according to your needs. Then we can create another sheet and name it as matrices and create labels and formulas as below.

Labels	Formulas
Total Trades	=COUNTA(History_Deals!A2:A)
Winning Trades	=COUNTIF(History_Deals!K2:K,">0")
Losing Trades	=COUNTIF(History_Deals!K2:K,"<0")
Win Rate %	=FIXED(B2/A2 * 100, 2)
Loss Rate %	=FIXED(C2/A2 * 100, 2)
Average Win	=FIXED(AVERAGEIF(History_Deals!K2:K,">0"), 2)
Average Loss	=FIXED(AVERAGEIF(History_Deals!K2:K,"<0"), 2)
Risk-Reward Ratio	=FIXED(F2/ABS(G2), 2)
Expectancy	=FIXED((D2/100*F2)-(E2/100*G2), 2)
Total Profit	=SUM(History_Deals!K2:K)
Profit Factor	=SUMIF(History_Deals!K2:K,">0")/ABS(SUMIF(History_Deals!K2:K,"<0"))
Max Drawdown	=MIN(History_Deals!L2:L)-MAX(FILTER(History_Deals!L2:L,History_Deals!L2:L<MIN(History_Deals!L2:L)))

Let me briefly explain the matrices used in the above table.

• Total Trades is the total number of trades successfully completed. Larger Total Trades means reliable and accurate statistics.
• Winning Trades is the number of trades that closed in profit. This number denotes the number of your correct decisions.
• Losing Trades is the number of trades that closed in loss. This number tells the number of your incorrect decisions.
• Win Rate % is the profitability rate and Loss Rate % is the rate at which your trades closed in losses. An important thing to remember is that a higher Win Rate doesn't always mean profitable. Traders can be profitable with correct risk-reward even with a win rate of 30 - 40 %.
• Average Win and Average Loss are the numbers which show how much you win when right and how much you lose when wrong. We have made a strategy so that average loss always remains low.
• Risk-Reward Ratio is the ratio of average win over average loss. Traders' motto should be to make it always greater than 1. The golden rule is to have it between 1.5 to 2:1.
• Expectancy is the average profit per trade. For example, if this value is 20, then for 100 trades you will be making 2000.
• Total Profit is the sum of all profits and losses. 
• Profit Factor is the number that shows the overall profitability. Traders should always try to make it higher than 1.5.
• Max Drawdown the largest drop in cumulative profit from its peak.Traders should always try to make it as small as possible.

After applying all the formulas, we get the following table.

Total Trades	Winning Trades	Losing Trades	Win Rate %	Loss Rate %	Average Win	Average Loss	Risk-Reward Ratio	Expectancy	Total Profit	Profit Factor	Max Drawdown
9	7	2	77.78	22.22	285.71	-400.00	0.71	311.11	1200	2.5	200

We can know health and consistency in our trading by plotting the cumulative profit in line chart as below by going to Insert → Chart → Line Chart and choosing cumulative profit on vertical axis and date closed on horizontal axis.

We can also compare winning trades vs losing trades in a pie chart as below

We can also see pair-wise performance using the column chart as below.

We can do a lot of such things in google sheets.

Conclusion

Traders can use this solution to effortlessly and securely transport their MetaTrader data to google sheet and get benefitted by using google sheets visualization tools. This is a solution to export our MetaTrader data for journal keeping or further analysis to Google sheets by utilizing freely available solutions. This article has shown how to serve your own proxy server as well as on cloud based solutions like pythonanywhere.com. Cloud based solutions are always secure and scalable. It has tried to modularize the MetaTrader program for flexibility in choice and even for easiness to add new features like log data for the strategy tester for further analysis and many more.

To sum up, this article is one more tool that traders and developers can utilize to transfer their MetaTrader data for further analysis to make better trading decisions.

I have included the source code for the proxy server and the MetaTrader script.

File Name	Description
service_accounts_proxy.py	This is a simple flask application with update and append api.
ProxyExport.mq5	This is a MetaTrader application with choices in data to be exported.