Capture Real-Time Forex Data in Excel with Python and WebSockets!

With our step-by-step guide, you can learn to fetch real-time forex data using WebSockets in Python and save it directly to an Excel file!

Hey there! In today's digital age, having real-time market data is a game-changer for financial apps. And guess what? WebSockets are the secret sauce to getting that data fast and efficiently. They make it super easy to share market data with developers and analysts, and they're perfect for creating dynamic and interactive platforms.

So, Why WebSockets?

WebSockets are all the rage right now. Setting up a server and a client to tinker with data using WebSockets is pretty straightforward. But the real fun begins when you tap into a WebSocket that dishes out real-time data.

In this tutorial, we'll guide you through connecting to a WebSocket Server using Python, grabbing real-time forex data, and saving it directly into an Excel file. Sound good? Let's get started!

What You'll Need:

Before we dive in, make sure you've got:

Python installed
A basic grasp of Python.

Quick heads up:

This tutorial uses locally installed Python, which is super easy to follow. However, Google Colab won't play nice with our file-saving antics. Our code is set up to save data directly to an Excel file, which won't work in Google Colab.

Got Your API Key?

Ready to jump in? Sign up for a trial to snag access to Forex data for 14 days. Your API key is waiting for you in your dashboard. Need a hand? We've got you covered with comprehensive documentation and example codes to guide you through the process.

Let's Get Coding, Shall We?

Step 1:

Here, we're importing the necessary modules to make our code work smoothly:

websocket: This module is essential for handling WebSocket connections, enabling us to establish and manage a connection to the WebSocket server.
Time: We use this module to generate timestamps for our messages, helping us keep track of when each piece of data was received.
_thread: This module allows us to run a function in a new thread. In our case, we're using it to send a message to the WebSocket server without blocking the main thread.
Workbook from openpyxl: This module provides the tools we need to work with Excel files. We'll use it to create a new Excel workbook and write our data to it.

import websocket
import time
import _thread
from openpyxl import Workbook

After importing the necessary modules, we move on to setting up our Excel workbook:

We create a new Excel workbook using Workbook().
We select the active worksheet with wb.active.
We set up the headers "Timestamp" and "Message" for the Excel file using ws_excel.append(["Timestamp," "Message"]).
Lastly, we open a log file named "webSocketTester.log" in append mode using f = open("webSocketTester.log," "a"). This log file will store the messages we receive from the WebSocket server.

This sets the stage for the rest of our code, where we'll define functions to handle incoming messages, errors, and the WebSocket connection itself.

# Create a new workbook and select the active worksheet
wb = Workbook()
ws_excel = wb.active

# Set up the headers for the Excel file
ws_excel.append(["Timestamp", "Message"])

# Open the log file for appending
f = open("webSocketTester.log", "a")

Step 2:

In this step, we define a function called on_message. This function gets triggered automatically whenever a message is received from the WebSocket server.

Let's break down what happens inside this function:

Timestamp Generation:

Here, we use the time.strftime method to generate a current timestamp in the format "YYYY-MM-DD HH:MM:SS." This helps us know exactly when each message was received.

current_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

Printing the Message:

We then print the received message along with its timestamp to the console. This helps us keep track of the incoming data in real-time.

print(f"{current_time} - {message}")

Writing to the Log File:

Next, we write the message and its timestamp to our log file "webSocketTester.log". The f.flush() method ensures that the data is immediately written to the file.

f.write(f"{current_time} - {message}\n") f.flush()

Writing to the Excel File:

Finally, we append the timestamp and message to our Excel worksheet using ws_excel.append([current_time, message]). After adding the new data, we save the workbook with wb.save("webSocket1.xlsx").

ws_excel.append([current_time, message]) wb.save("webSocket1.xlsx")

And that wraps up Step 2! This function efficiently handles each incoming message, logging it to a file and saving it to an Excel worksheet for easy tracking and analysis.

Step 3:

In this step, we define a function named on_error. This function is designed to handle any errors that may occur during the WebSocket connection. Let's dive into what's happening inside this function:

Printing the Error Message:

print(error)

Here, we print the error message to the console. This helps us quickly identify and understand any issues that may arise during the WebSocket connection. By defining this on_error function, we ensure that our code can gracefully handle and report any errors that occur, making it easier for us to troubleshoot and fix issues as they arise.

def on_error(ws, error):
    print(error)

Step 4:

In this step, we define a function named on_close. This function is triggered when the WebSocket connection is closed. Let's break down what this function does:

Print a Closing Message:

print("### closed ###")

Here, we simply print "### closed ###" to the console. This message clearly indicates that the WebSocket connection has been closed, allowing us to monitor its status easily. By including this on_close function in our code, we are informed whenever the WebSocket connection closes, helping us to keep track of the connection status and any potential issues.

def on_close(ws):
    print("### closed ###")

Step 5:

We define a function on_open to handle the WebSocket opening event. Inside this function:

We define another function run to send a message with a user key and symbol to the WebSocket server.
We use _thread.start_new_thread to run the run function in a new thread when the WebSocket connection is opened.

def on_open(ws):
    def run(*args):
        ws.send("{\"userKey\":\"wszzeid0Skp6ClO7eo_w\", \"symbol\":\"GBPUSD\"}")
    _thread.start_new_thread(run, ())

Step 6:

Finally, we check if the script is being run as the main program:

We create a WebSocketApp instance with the WebSocket server URL and the callback functions on_message, on_error, and on_close.
We set the on_open callback to our on_open function.
We run the WebSocket connection forever.

And that's it! This code connects to a WebSocket server, fetches real-time forex data for the GBP/USD pair, and saves the received messages with timestamps to both a log file and an Excel file.

if __name__ == "__main__":
    ws = websocket.WebSocketApp("wss://marketdata.tradermade.com/feedadv",
                                on_message=on_message,
                                on_error=on_error,
                                on_close=on_close)
    ws.on_open = on_open
    ws.run_forever()

Let us Execute the code:

The command python websocket_excel.py is used to execute a Python script named websocket_excel.py. Let's break it down step by step:

Python:

This is the command-line interface (CLI) command used to run Python scripts. It instructs the system to interpret and execute the following Python script.

websocket_excel.py:

This is the name of the Python script you want to run. In this context, it suggests that the Python script is likely designed to perform operations related to WebSockets and Excel, such as connecting to a WebSocket server, fetching real-time forex data, and saving it to an Excel file.

So, when you run python websocket_excel.py in the command line:

The system will look for a file named websocket_excel.py.
It will then execute the Python script, performing whatever operations are defined within that script.

It connects to a WebSocket server, fetches real-time forex data for the GBP/USD pair, and saves the received messages, along with timestamps, to both a log file and an Excel file.

Python websocket_excel.py

Conclusion: Capture Real-Time Forex Data in Excel with Python and WebSockets!

Embarking on this journey, you've learned how to harness the power of WebSockets in Python to fetch real-time forex data and save it directly to an Excel file. By following our step-by-step guide, you've set up a robust system that not only connects to a WebSocket server but also processes and logs the received data effectively.

Remember, this approach leverages the immediacy and efficiency of WebSockets, making it a fantastic tool for real-time data retrieval in financial applications. However, you should replace the placeholder userKey with your actual API key to access the Forex data.

Should you encounter any issues or need further clarification, don't hesitate to reach out to us through live chat or email. Happy coding!

Also, go through the following resources published on the TraderMade website:
Real-time OHLC Data with Python WebSocket

Real-time Forex Data in OHLC Bars with PHP

Python SDK

Forex Charts in Excel

Blog