In today’s data-driven world, extracting meaningful insights from raw data is crucial for informed decision-making. Microsoft Excel stands as an amazing tool for data analysis, offering powerful features for visualization, manipulation, and interpretation. However, accessing and processing data from external sources, such as BLE devices, can often be challenging and time-consuming.

BleuIO revolutionizes BLE application development with its intuitive AT command interface, eliminating the need for complex coding. With BleuIO, developers can communicate effortlessly with BLE devices, retrieve data, and execute commands with ease. Whether you’re a seasoned developer or just starting out, BleuIO streamlines the development process, allowing you to focus on innovation rather than technical complications. In this tutorial we will see how to read data from an air quality monitoring BLE device and get it on Microsoft excel sheet for further analysis.

What is BleuIO?

BleuIO is a versatile BLE 5.0 USB dongle designed to simplify the development of BLE applications. With its AT command interface, developers can easily communicate with BLE devices without the need for complex coding. Whether you’re a beginner or an experienced developer, BleuIO makes BLE application development faster and more accessible.

Setting Up the Environment

Before we dive into the code, let’s set up our development environment. You’ll need:

• BleuIO USB dongle
• Microsoft Excel
• Python installed on your system
• HibouAir – air quality monitoring device

Communicating with BleuIO

To communicate with BleuIO, we’ll use Python and its serial library. First, ensure that you have the pyserial library installed. Then, connect BleuIO to your computer and identify the serial port it’s connected to. Next, we’ll send AT commands to BleuIO and retrieve the responses.

Here is the complete python code

import serial
import re
import json

# Define the serial port and baudrate
serial_port = "COM8"
baudrate = 57600


def read_response(ser):
    """
    Read response from serial port until a newline character is encountered.
    """
    response = b""
    while True:
        char = ser.read(1)
        if char == b"":
            break  # No more data to read
        response += char
        if char == b"\n\n":
            break  # Reached end of response
    return response.decode()


def hex_to_decimal(hex_str):
    """
    Convert hexadecimal string to decimal integer.
    """
    return round(int(hex_str, 16) / 10.0, 1)


def find_pattern(response):
    """
    Find and extract patterns matching the specified format.
    """
    pattern = r"\{T:\"(\w+)\",H:\"(\w+)\",PM1:\"(\w+)\"PM2\.5:\"(\w+)\"PM10:\"(\w+)\",IAQ:\"(\w+)\",PPM:\"(\w+)\"\}"
    matches = re.findall(pattern, response)
    return [
        {
            "T": hex_to_decimal(m[0]),
            "H": hex_to_decimal(m[1]),
            "PM1": hex_to_decimal(m[2]),
            "PM2.5": hex_to_decimal(m[3]),
            "PM10": hex_to_decimal(m[4]),
            # "IAQ": hex_to_decimal(m[5]),
            # "PPM": hex_to_decimal(m[6]),
        }
        for m in matches
    ]


def main():
    # Connect to the serial port
    ser = serial.Serial(serial_port, baudrate, timeout=1)

    # List to store responses
    responses = []

    # Send the command 'AT+CENTRAL' to the device
    ser.write(b"AT+CENTRAL\r")
    response = read_response(ser)

    # Connect to the device
    ser.write(b"AT+GAPCONNECT=[1]D1:53:C9:A9:8C:D2\r")
    response = read_response(ser)

    # Set notification
    ser.write(b"AT+SETNOTI=0021\r")
    response = read_response(ser)

    # Get all data
    ser.write(b"AT+GATTCWRITEWR=0021 GET DATA=ALL\r")
    while True:
        response = read_response(ser)
        responses.append(response.strip())
        if "DATA:END" in response:
            break  # End of response

    # Find and collect patterns matching the specified format
    collected_patterns = []
    for r in responses:
        pattern_matches = find_pattern(r)
        if pattern_matches:
            collected_patterns.extend(pattern_matches)

    # Convert to JSON
    json_data = json.dumps(collected_patterns, indent=2)
    print(json_data)

    # Close the serial port
    ser.close()


if __name__ == "__main__":
    main()

In this code we have

• Establishes a connection to the serial port, sends commands to the BLE device, and retrieves responses of 7 days air quality data history stored in the device.
• Parses the responses to extract relevant patterns using regular expressions.
• Converts the extracted patterns into JSON format for easy handling and printing.
• Finally, closes the serial port.

Integrating with Excel

Now, let’s integrate BleuIO with Excel to visualize and analyze the air quality data. By executing a Python script within Excel’s VBA environment, we can populate the data directly into Excel for further analysis.

Here is the complete code

Sub SerialCommunication()
    Dim response As String
    Dim pythonScriptPath As String
    Dim wsh As Object, exec As Object, output As String
    Dim jsonData As Object
    Dim obj As Object
    Dim i As Integer, j As Integer

    ' Set the path to the Python script
    pythonScriptPath = "C:\Users\PC\Desktop\excel bleuio\serial_communication.py" ' Update with the correct path

    ' Create Windows Script Host object
    Set wsh = CreateObject("WScript.Shell")

    ' Execute the Python script and capture its output
    Set exec = wsh.exec("python """ & pythonScriptPath & """")

    ' Read the output of the script
    output = exec.StdOut.ReadAll

    ' Parse JSON data
    Set jsonData = JsonConverter.ParseJson(output)

    ' Write headers in the first row
    i = 1 ' Starting row
    j = 1 ' Starting column
    For Each key In jsonData(1).Keys
        Sheet1.Cells(i, j).value = key
        j = j + 1
    Next key

    ' Write data into separate columns
    i = i + 1 ' Move to the next row
    For Each obj In jsonData
        j = 1 ' Starting column
        For Each key In obj.Keys
            Sheet1.Cells(i, j).value = obj(key)
            j = j + 1
        Next key
        i = i + 1 ' Move to the next row
    Next obj
End Sub

In this code we have,

• Called the python script.
• The response we got from python script we then passed it using JsonConverter.
• Finally we loop through the object and presented it on the Excel sheet on their respective cells.

Set up JsonConverter

If you get error like JsonConverter object is not recognized, follow the steps:

1. Download JSONConverter.bas: You can download the JSONConverter.bas file from various sources online. Here is a good github link to download from. https://github.com/VBA-tools/VBA-JSON It’s a common utility module for VBA that provides JSON parsing capability.
2. Import JSONConverter.bas into your project: Open your Excel workbook, then go to the Visual Basic Editor (Alt + F11). From the menu, select File > Import File and choose the JSONConverter.bas file you downloaded. This will add the JSONConverter module to your project.
3. Ensure Microsoft Scripting Runtime Reference: Go to Tools > References in the VBA editor and ensure that “Microsoft Scripting Runtime” is checked. This is needed for dictionary objects used in JSON parsing.

Run the script

1. Insert a Button:
   • Go to the “Developer” tab in Excel. If you don’t see the “Developer” tab, you may need to enable it in Excel options.
   • Click on the “Insert” drop-down menu in the “Controls” group.
   • Choose the “Button” (Form Control) option.
   • Click and drag to draw the button on your worksheet.
2. Assign the Macro:
   • Right-click on the button you just inserted and select “Assign Macro”.
   • In the “Assign Macro” dialog box, you should see a list of available macros. Since you just created a new macro, it should be listed. In this case, it should be “SerialCommunication”.
   • Select the “SerialCommunication” macro and click “OK”.
3. Edit the Macro (if needed):
   • If you want to edit the macro, you can click on the “Edit” button in the “Assign Macro” dialog box. This will open the VBA editor where you can make changes to the macro.
4. Test the Button:
   • Click on the button you inserted in your worksheet. This should trigger the “SerialCommunication” macro, which will execute the VBA code to communicate with the serial port and display the response in Excel.
5. Ensure Correct Port and Settings:
   • Before testing, ensure that the COM port (COM8) and other settings in the VBA code match your requirements and device specifications.

Output

Use Cases: Transforming Data into Actionable Insights

1. Indoor Air Quality Monitoring: Deploy BLE-enabled sensors in indoor environments to monitor air quality parameters such as temperature, humidity, and particulate matter. Excel’s data analysis capabilities enable users to identify trends, anomalies, and potential air quality issues, facilitating proactive measures for improving indoor air quality.
2. Environmental Studies and Research: Conduct environmental studies and research projects using BleuIO to collect air quality data in various outdoor settings. Excel serves as a powerful tool for data aggregation, statistical analysis, and visualization, enabling researchers to gain valuable insights into environmental patterns and trends.
3. Health and Safety Compliance: Ensure compliance with health and safety regulations by monitoring air quality in workplaces, public spaces, and industrial facilities. BleuIO, coupled with Excel, enables continuous monitoring of air quality parameters, facilitating compliance reporting and risk assessment processes.

By leveraging BleuIO’s seamless BLE communication capabilities and Excel’s robust data analysis features, developers and analysts can unlock the full potential of BLE application development and data analysis. Whether you’re monitoring air quality in indoor environments, conducting environmental research, or ensuring regulatory compliance, BleuIO and Excel provide a powerful combination for transforming raw data into actionable insights.