MENU
Minokamo Citizens
  1. Home
  2. education
  3. Python-Driven Network Monitoring: Integrating Excel, CSV, and Web Interfaces

Python-Driven Network Monitoring: Integrating Excel, CSV, and Web Interfaces

education
table of contents

Discovering the Convenience of Python in Excel

Not too long ago, while working on complex data sheets in Excel, I had an eye-opening thought:

“Wait, wouldn’t this be faster if I used Python?”

It’s a question that has likely popped into the minds of many data analysts and office workers. Sure, we can export Excel data into CSV, process it with Python, and then re-import it into Excel, but all that back-and-forth can become tedious.

A Wish Come True for Excel Users

Many of us have dreamed of combining Excel’s user-friendly interface with Python’s powerful scripting capabilities. That’s why it was such exciting news when Microsoft officially announced the “Python in Excel” feature on September 16, 2024. Although a public preview had been available to select users since August 2023, the fully released version feels like a game-changer—especially for anyone who regularly juggles between spreadsheets and Python scripts.

Initial Skepticism Turned into Pleasant Surprise

I’ll admit, at first, I was a bit skeptical. “Is this going to be one of those half-baked Excel add-ins?” I wondered. But after getting some hands-on experience, I’ve discovered it’s impressively solid. One standout aspect is that Anaconda comes bundled with Excel’s Python environment. Whenever I feel like using a specific Python library for data modeling or visualization, there’s no extra installation required. I can simply write Python commands directly in Excel cells and run them on the spot.

Key Point: This built-in environment removes the hurdle of manually setting up Python dependencies, making it easier than ever for spreadsheet enthusiasts to dip their toes into the world of coding.

A Quick Comparison Table

Here’s a brief snapshot of how the new Python in Excel feature compares with the traditional Excel + Python workflow:

FeatureTraditional WorkflowPython in Excel
SetupSeparate Python install + CSV exports/importsPython environment included within Excel (via Anaconda)
Ease of UseRequires switching between Excel and PythonWrite and run Python code directly in Excel
Data VisualizationManually generate charts using Excel or separate librariesIntegrate Python libraries (like matplotlib) within Excel cells
MaintenanceMust periodically update Python packages outside of ExcelPython dependencies auto-managed through Excel’s built-in Anaconda

This table highlights how much simpler it’s becoming to utilize Python for tasks that were historically Excel-centric—like data cleaning, advanced visualization, and even machine learning prototypes.

Important Limitations

  1. Microsoft 365 Subscription: You need an active Microsoft 365 plan to use Python in Excel. Unfortunately, perpetual license versions like Office 2019 or 2021 don’t support this feature.
  2. Insider Program Requirement: At least for now, you must be part of the Office Insider Program to unlock early features.
  3. Continued Development: While Python in Excel is robust, it’s still evolving. Keep an eye out for further updates that might expand its capabilities.

Placeholder for Code Snippets

import socket
import platform
import subprocess
import os
import sys
import time
import threading
from datetime import datetime
import xlwings as xw
import ipaddress
import re
from concurrent.futures import ThreadPoolExecutor, as_completed

def get_shared_folders(ip_address: str) -> str:
    """
    Retrieve a list of shared folders for the specified IP address.
    For Windows, use the 'net view' command.
    """
    if platform.system().lower() == "windows":
        cmd = f'net view \\\\{ip_address}'
        try:
            output = subprocess.check_output(cmd, shell=True, universal_newlines=True, stderr=subprocess.DEVNULL)
            shares = re.findall(r'\\\\\S+\s+(\S+)', output)
            return ", ".join(shares) if shares else ""
        except subprocess.CalledProcessError:
            return ""
        except Exception:
            return ""
    else:
        return ""

class ImprovedNetworkMonitor:
    def __init__(self, excel_file='improved_lan_monitor.xlsx'):
        self.excel_file = excel_file
        self.should_run = False
        self.monitor_thread = None
        self.refresh_interval = 30  # Regular refresh interval (seconds)
        self.full_scan_interval = 600  # Full scan interval (seconds)
        self.known_devices = {}       # Dictionary of known devices keyed by IP address
        self.active_ips = set()       # Set of active IPs
        self.priority_ips = set()     # IPs to scan with priority
        self.check_shared_folders = False  # Whether to detect shared folders (toggle in settings)
        self.thread_app = None      # Excel app for background thread
        self.monitor_wb = None      # Workbook kept open continuously
        self.close_excel_on_stop = True  # Close Excel on exit (True on exit, False on stop)

        # If the Excel file does not exist, create a template; otherwise, load settings.
        if not os.path.exists(self.excel_file):
            self.create_excel_template()
        else:
            self.load_settings()
    
    def create_excel_template(self):
        """Create an Excel template for monitoring (performed in the main thread)"""
        try:
            app = xw.App(visible=False)
            wb = app.books.add()
            try:
                main_sheet = wb.sheets['Network Monitor']
            except Exception:
                main_sheet = wb.sheets.add(name='Network Monitor')
            try:
                status_sheet = wb.sheets['Status History']
            except Exception:
                status_sheet = wb.sheets.add(name='Status History')
            try:
                config_sheet = wb.sheets['Settings']
            except Exception:
                config_sheet = wb.sheets.add(name='Settings')
            
            main_sheet.range('A1').value = [
                'IP', 'Hostname', 'Device Type', 'Status', 'Response Time (ms)', 'Last Seen', 'Detection Count', 'Shared Folders'
            ]
            main_sheet.range('A1:H1').api.Font.Bold = True
            main_sheet.range('A1:H1').color = (200, 200, 250)
            
            status_sheet.range('A1').value = ['Timestamp', 'Online Devices', 'Offline Devices', 'Network Status', 'Scan Mode']
            status_sheet.range('A1:E1').api.Font.Bold = True
            status_sheet.range('A1:E1').color = (200, 200, 250)
            
            network_info = self.get_network_info()
            config_sheet.range('A1').value = 'Regular Refresh Interval (seconds)'
            config_sheet.range('B1').value = 30
            config_sheet.range('A2').value = 'Full Scan Interval (seconds)'
            config_sheet.range('B2').value = 600
            config_sheet.range('A3').value = 'Network Range'
            config_sheet.range('B3').value = network_info['network_range']
            config_sheet.range('A4').value = 'DHCP Range Start'
            config_sheet.range('B4').value = 100
            config_sheet.range('A5').value = 'DHCP Range End'
            config_sheet.range('B5').value = 200
            config_sheet.range('A6').value = 'Priority IP Addresses (comma-separated)'
            config_sheet.range('B6').value = f"{network_info['gateway']},{network_info['local_ip']}"
            config_sheet.range('A7').value = 'Detailed Display'
            config_sheet.range('B7').value = False
            config_sheet.range('A8').value = 'Shared Folder Detection'
            config_sheet.range('B8').value = False
            config_sheet.range('C8').value = 'Note: Set to True to retrieve shared folder information'
            
            config_sheet.range('C1').value = 'Note: Too short intervals increase load (minimum 10 seconds)'
            config_sheet.range('C2').value = 'Note: Interval for detecting all devices on the network'
            config_sheet.range('C4').value = "Note: Router's DHCP configuration range"
            config_sheet.range('C6').value = 'Note: IP addresses to always scan'
            config_sheet.range('C7').value = 'Note: If True, display debug information in Excel'
            
            main_sheet.autofit()
            status_sheet.autofit()
            config_sheet.autofit()
            
            for sheet in wb.sheets:
                if sheet.name not in ['Network Monitor', 'Status History', 'Settings']:
                    sheet.delete()
            
            wb.save(self.excel_file)
            wb.close()
            app.quit()
            print(f"Template created: {self.excel_file}")
            self.load_settings()
        except Exception as e:
            print(f"Template creation error: {e}")
    
    def load_settings(self):
        """Load settings from the Excel file (performed in the main thread)"""
        try:
            app = xw.App(visible=False)
            wb = app.books.open(self.excel_file)
            config_sheet = wb.sheets['Settings']
            try:
                interval = config_sheet.range('B1').value
                if isinstance(interval, (int, float)) and interval >= 10:
                    self.refresh_interval = int(interval)
            except:
                pass
            try:
                full_interval = config_sheet.range('B2').value
                if isinstance(full_interval, (int, float)) and full_interval >= 60:
                    self.full_scan_interval = int(full_interval)
            except:
                pass
            try:
                priority_ips_str = config_sheet.range('B6').value
                if priority_ips_str and isinstance(priority_ips_str, str):
                    for ip in priority_ips_str.split(','):
                        ip = ip.strip()
                        if self.is_valid_ip(ip):
                            self.priority_ips.add(ip)
            except:
                pass
            network_info = self.get_network_info()
            self.priority_ips.add(network_info['local_ip'])
            if network_info['gateway'] != "Unknown":
                self.priority_ips.add(network_info['gateway'])
            try:
                shared_option = config_sheet.range('B8').value
                self.check_shared_folders = bool(shared_option)
            except:
                pass
            wb.close()
            app.quit()
        except Exception as e:
            print(f"Error reading settings: {e}")
    
    def is_valid_ip(self, ip):
        pattern = re.compile(r'^(\d{1,3}\.){3}\d{1,3}$')
        if not pattern.match(ip):
            return False
        return all(0 <= int(octet) <= 255 for octet in ip.split('.'))
    
    def get_network_info(self):
        # Get local hostname from environment variables (e.g., n4060)
        hostname = os.environ.get("COMPUTERNAME", socket.gethostname())
        try:
            local_ip = socket.gethostbyname(hostname)
        except:
            s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
            try:
                s.connect(('8.8.8.8', 1))
                local_ip = s.getsockname()[0]
            except:
                local_ip = '127.0.0.1'
            finally:
                s.close()
        gateway = "Unknown"
        try:
            if platform.system() == "Windows":
                output = subprocess.check_output("ipconfig", universal_newlines=True)
                for line in output.split('\n'):
                    if "Default Gateway" in line or "デフォルト ゲートウェイ" in line:
                        gw = line.split(":")[-1].strip()
                        if gw:
                            gateway = gw
                            break
            else:
                try:
                    output = subprocess.check_output("ip route | grep default", shell=True, universal_newlines=True)
                    gateway = output.split()[2]
                except:
                    output = subprocess.check_output("netstat -nr | grep default", shell=True, universal_newlines=True)
                    gateway = output.split()[1]
        except:
            pass
        ip_parts = local_ip.split('.')
        network_prefix = '.'.join(ip_parts[:3]) + '.'
        network_range = f"{network_prefix}0/24"
        return {
            "hostname": hostname,
            "local_ip": local_ip,
            "gateway": gateway,
            "network_range": network_range,
            "network_prefix": network_prefix,
            "scan_time": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        }
    
    def ping_device(self, ip_address, count=1):
        try:
            if platform.system().lower() == "windows":
                ping_cmd = f"ping -n {count} -w 500 {ip_address}"
            else:
                ping_cmd = f"ping -c {count} -W 0.5 {ip_address}"
            start_time = time.time()
            result = subprocess.run(ping_cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True, universal_newlines=True)
            response_time_ms = round((time.time() - start_time) * 1000)
            if result.returncode == 0:
                return {"status": "Online", "response_time_ms": response_time_ms}
            else:
                return {"status": "Offline", "response_time_ms": None}
        except Exception as e:
            print(f"Ping error ({ip_address}): {e}")
            return {"status": "Error", "response_time_ms": None}
    
    def get_hostname(self, ip_address):
        try:
            socket.setdefaulttimeout(1)
            hostname = socket.getfqdn(ip_address)
            if hostname == ip_address:
                return "Unknown"
            return hostname
        except:
            return "Unknown"
    
    def get_device_type(self, ip_address, network_info):
        if ip_address == network_info["local_ip"]:
            return "This PC"
        elif ip_address == network_info["gateway"]:
            return "Gateway"
        if ip_address in self.known_devices:
            hostname = self.known_devices[ip_address].get("hostname_base", "").lower()
            if "printer" in hostname or "print" in hostname:
                return "Printer"
            elif "phone" in hostname or "mobile" in hostname or "android" in hostname or "iphone" in hostname:
                return "Mobile"
            elif "tv" in hostname or "television" in hostname or "smart-tv" in hostname:
                return "TV/Media"
        return "Other"
    
    def get_scan_targets(self, network_info, mode="regular"):
        targets = set()
        network_prefix = network_info["network_prefix"]
        if mode == "full":
            try:
                network = ipaddress.IPv4Network(network_info["network_range"])
                for ip in network.hosts():
                    targets.add(str(ip))
            except Exception as e:
                print(f"IP network analysis error: {e}")
                for i in range(1, 255):
                    targets.add(f"{network_prefix}{i}")
        elif mode == "dhcp":
            try:
                config_sheet = self.monitor_wb.sheets['Settings']
                start_range = int(config_sheet.range('B4').value or 100)
                end_range = int(config_sheet.range('B5').value or 200)
                if start_range < 1:
                    start_range = 1
                if end_range > 254:
                    end_range = 254
                for i in range(start_range, end_range + 1):
                    targets.add(f"{network_prefix}{i}")
            except Exception as e:
                for i in range(100, 201):
                    targets.add(f"{network_prefix}{i}")
        else:  # regular
            targets.update(self.active_ips)
            targets.update(self.priority_ips)
        targets.update(self.priority_ips)
        return list(targets)
    
    def scan_network(self, mode="regular"):
        network_info = self.get_network_info()
        targets = self.get_scan_targets(network_info, mode)
        print(f"Scanning network: {mode} mode ({len(targets)} IPs)...")
        devices = []
        new_active_ips = set()
        results = {}
        # Execute ping in parallel using a thread pool
        with ThreadPoolExecutor(max_workers=min(50, len(targets))) as executor:
            future_to_ip = {executor.submit(self.ping_device, ip): ip for ip in targets}
            for future in as_completed(future_to_ip):
                ip = future_to_ip[future]
                try:
                    results[ip] = future.result()
                except Exception as e:
                    results[ip] = {"status": "Error", "response_time_ms": None}
        # Update device information based on results
        for ip in targets:
            ping_result = results.get(ip, {"status": "Error", "response_time_ms": None})
            if ping_result["status"] == "Online":
                new_active_ips.add(ip)
                if ip not in self.known_devices or self.known_devices[ip].get("status") != "Online":
                    hostname_base = self.get_hostname(ip)
                else:
                    hostname_base = self.known_devices[ip].get("hostname_base", "Unknown")
                device_type = self.get_device_type(ip, network_info)
                device = {
                    "ip": ip,
                    "hostname": hostname_base,
                    "hostname_base": hostname_base,
                    "device_type": device_type,
                    "status": ping_result["status"],
                    "response_time_ms": ping_result["response_time_ms"],
                    "last_seen": datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
                    "detection_count": self.known_devices.get(ip, {}).get("detection_count", 0) + 1
                }
                self.known_devices[ip] = device
                devices.append(device)
            elif ip in self.known_devices:
                offline_device = self.known_devices[ip].copy()
                offline_device["status"] = "Offline"
                offline_device["response_time_ms"] = None
                offline_device["last_seen"] = self.known_devices[ip].get("last_seen", "Unknown")
                devices.append(offline_device)
        self.active_ips = new_active_ips
        return devices
    
    def update_excel(self, devices, scan_mode):
        max_retries = 5
        while max_retries > 0:
            try:
                wb = self.monitor_wb
                app = wb.app
                app.screen_updating = False
                try:
                    sheet = wb.sheets['Network Monitor']
                except Exception:
                    sheet = wb.sheets.add(name='Network Monitor')
                sheet.range("A2:H1000").clear()
                unique_devices = {}
                for device in devices:
                    ip = device.get('ip')
                    if ip not in unique_devices or device.get('detection_count', 0) > unique_devices[ip].get('detection_count', 0):
                        unique_devices[ip] = device
                device_data = []
                for device in unique_devices.values():
                    if self.check_shared_folders and device.get('status') == 'Online':
                        shared_folders = get_shared_folders(device.get('ip'))
                    else:
                        shared_folders = ""
                    device_data.append([
                        device.get('ip', 'Unknown'),
                        device.get('hostname', 'Unknown'),
                        device.get('device_type', 'Other'),
                        device.get('status', 'Unknown'),
                        device.get('response_time_ms', '-'),
                        device.get('last_seen', datetime.now().strftime("%Y-%m-%d %H:%M:%S")),
                        device.get('detection_count', 1),
                        shared_folders
                    ])
                if device_data:
                    device_data.sort(key=lambda x: [int(octet) for octet in x[0].split('.')])
                    sheet.range('A2').value = device_data
                    for i, row_data in enumerate(device_data, start=2):
                        status = row_data[3]
                        if status == 'Online':
                            sheet.range(f'D{i}').color = (198, 239, 206)
                        else:
                            sheet.range(f'D{i}').color = (255, 199, 206)
                        device_type = row_data[2]
                        if device_type == 'Gateway':
                            sheet.range(f'C{i}').color = (255, 230, 153)
                        elif device_type == 'This PC':
                            sheet.range(f'C{i}').color = (197, 217, 241)
                        elif device_type == 'Printer':
                            sheet.range(f'C{i}').color = (216, 191, 216)
                        elif device_type == 'Mobile':
                            sheet.range(f'C{i}').color = (255, 204, 153)
                try:
                    status_sheet = wb.sheets['Status History']
                except Exception:
                    try:
                        status_sheet = wb.sheets.add(name='Status History')
                        status_sheet.range('A1').value = ['Timestamp', 'Online Devices', 'Offline Devices', 'Network Status', 'Scan Mode']
                        status_sheet.range('A1:E1').api.Font.Bold = True
                        status_sheet.range('A1:E1').color = (200, 200, 250)
                    except:
                        status_sheet = None
                if status_sheet:
                    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    online_count = sum(1 for d in unique_devices.values() if d['status'] == 'Online')
                    offline_count = sum(1 for d in unique_devices.values() if d['status'] == 'Offline')
                    if online_count == 0:
                        network_status = "No network connection"
                    elif online_count < 2:
                        network_status = "Issues - Too few devices found"
                    else:
                        network_status = "Normal"
                    last_status_row = status_sheet.range('A' + str(status_sheet.cells.last_cell.row)).end('up').row
                    status_sheet.range(f'A{last_status_row+1}').value = [timestamp, online_count, offline_count, network_status, scan_mode]
                    if last_status_row > 30:
                        status_sheet.range(f'A2:E{last_status_row-29}').delete()
                try:
                    config_sheet = wb.sheets['Settings']
                    show_details = bool(config_sheet.range('B7').value)
                except:
                    show_details = False
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                try:
                    info_text = f"Last update: {current_time} ({scan_mode} scan)"
                    if show_details:
                        info_text += f" | Active IPs: {len(self.active_ips)} | Priority IPs: {len(self.priority_ips)}"
                    sheet.range('A1').offset(-1, 0).value = info_text
                except:
                    pass
                wb.save()
                app.screen_updating = True
                print(f"Excel updated: {current_time} ({scan_mode} mode)")
                return True
            except Exception as e:
                if "0x800ac472" in str(e):
                    print("Excel is busy. Retrying.")
                    time.sleep(1)
                    max_retries -= 1
                    continue
                else:
                    print(f"Excel update error: {e}")
                    return False
        print("Failed to update Excel after retrying.")
        return False
    
    def monitor_loop(self):
        import pythoncom
        pythoncom.CoInitialize()
        self.thread_app = xw.App(visible=True)
        try:
            self.monitor_wb = self.thread_app.books.open(self.excel_file)
            last_scan_time = 0
            last_full_scan_time = 0
            while self.should_run:
                try:
                    current_time = time.time()
                    scan_mode = "regular"
                    if current_time - last_full_scan_time >= self.full_scan_interval:
                        scan_mode = "full"
                        last_full_scan_time = current_time
                    elif len(self.active_ips) < 3 and current_time - last_full_scan_time >= 60:
                        scan_mode = "dhcp"
                    if (current_time - last_scan_time >= self.refresh_interval) or scan_mode != "regular":
                        try:
                            devices = self.scan_network(mode=scan_mode)
                            if devices:
                                self.update_excel(devices, scan_mode)
                            last_scan_time = current_time
                        except Exception as e:
                            print(f"Monitoring cycle error: {e}")
                    time.sleep(5)
                except Exception as e:
                    print(f"Monitoring loop error: {e}")
                    time.sleep(10)
        finally:
            if self.close_excel_on_stop:
                if self.monitor_wb:
                    self.monitor_wb.save()
                    self.monitor_wb.close()
                    self.monitor_wb = None
                self.thread_app.quit()
                self.thread_app = None
            else:
                if self.monitor_wb:
                    self.monitor_wb.save()
            pythoncom.CoUninitialize()
    
    def start_monitoring(self):
        if self.monitor_thread and self.monitor_thread.is_alive():
            print("Monitoring is already running")
            return False
        self.load_settings()
        self.close_excel_on_stop = True
        self.should_run = True
        self.monitor_thread = threading.Thread(target=self.monitor_loop)
        self.monitor_thread.start()
        print("Network monitoring started")
        return True
    
    def stop_monitoring(self, close_excel=False):
        self.close_excel_on_stop = close_excel
        self.should_run = False
        if self.monitor_thread:
            try:
                self.monitor_thread.join(timeout=5)
            except:
                pass
        print("Network monitoring stopped")
        return True

def main():
    run_now = False
    excel_file = 'improved_lan_monitor.xlsx'
    if len(sys.argv) > 1:
        for arg in sys.argv[1:]:
            if arg == '--run':
                run_now = True
            elif arg.startswith('--excel='):
                excel_file = arg.split('=')[1]
    monitor = ImprovedNetworkMonitor(excel_file=excel_file)
    if run_now:
        monitor.start_monitoring()
        try:
            while True:
                time.sleep(1)
        except KeyboardInterrupt:
            print("Exiting...")
            monitor.stop_monitoring(close_excel=True)
            sys.exit(0)
    else:
        print(f"Improved LAN Monitoring Tool v1.0")
        print(f"Excel file: {monitor.excel_file}")
        print("Commands: start - start monitoring, stop - stop monitoring (Excel remains open), exit - exit (closes Excel)")
        while True:
            try:
                cmd = input("> ").strip().lower()
                if cmd == "start":
                    monitor.start_monitoring()
                elif cmd == "stop":
                    monitor.stop_monitoring(close_excel=False)
                elif cmd == "exit":
                    if monitor.should_run:
                        monitor.stop_monitoring(close_excel=True)
                    sys.exit(0)
                else:
                    print("Unknown command. Please enter one of start, stop, exit.")
            except EOFError:
                break
            except KeyboardInterrupt:
                print("\nExiting...")
                if monitor.should_run:
                    monitor.stop_monitoring(close_excel=True)
                sys.exit(0)

if __name__ == "__main__":
    main()

My Personal Take

As someone who has been leaning heavily on Python for data analysis, I see Python in Excel as a fantastic entry point for Excel-first users. It lowers the barrier to learning Python and lets you test scripts within a familiar environment. If you’re already comfortable with Excel, this integration allows you to gradually pick up Python concepts without flipping constantly between two separate tools.

Exploring Python-Controlled Excel: Insights from Developing a Network Monitoring Tool

Even though Microsoft’s new Python in Excel feature is a welcome innovation, many of us still rely on the tried-and-true method of writing Python scripts in a separate environment—often due to workflow preferences or existing project requirements. Personally, I’ve been doing it this way for years, particularly while building a real-time network monitoring tool that displays up-to-the-minute data in Excel.

When a Mysterious Blank Excel Window Appears…

During development, I ran into a curious glitch: every time I launched my Python script, a blank Excel window would flash for a brief moment before disappearing. Initially, I was alarmed, thinking, “Is my code broken?” But after some investigation, I discovered there was a perfectly logical reason behind it.

Why the Tool Might Need Two Separate Excel Windows

In my particular program, there are two main tasks:

  1. Loading Configuration and Generating a Template
  2. Updating the Network Status in Real Time

If you try to handle both processes in one Excel instance, you can encounter major issues related to Excel’s COM (Component Object Model). When different threads attempt to manipulate the same open workbook, unpredictable errors—or even crashes—can occur. The underlying cause is that the COM architecture isn’t always thread-safe, so multiple concurrent operations on a single workbook can lead to race conditions.

Hence, the tool I wrote briefly opens a temporary Excel window for settings retrieval and template generation, closes it immediately, and then launches a main Excel window for ongoing network monitoring. Users only see that second window for the duration of the program, but the fleeting blank one is part of a necessary workaround for stable performance.

Key Advantages of Excel for Network Monitoring

You might wonder, “Why use Excel for network monitoring?” There are actually several compelling reasons:

ReasonBenefits
Highly Visual LayoutTabular data is easy to scan, and conditional color formatting offers intuitive status updates.
Easy Data ReuseSince the results are saved in standard spreadsheet format, you can reuse the data for reports or logs.
Familiar UI for Non-DevelopersColleagues who aren’t coders can still open Excel and understand the monitoring results immediately.

Unlike specialized monitoring software that can be overwhelming, Excel offers a comforting familiarity and built-in charting tools.

Environment Requirements

Here are the basics you’ll need to run a Python-based Excel tool on Windows:

  1. Python 3.8 or Later
  2. pip (Package Manager)
  3. Microsoft Excel 2019 or Above
  4. Administrator Privileges (on Windows)

And here is a sample snippet showing how to install the necessary libraries:

pip install pywin32   # Provides essential Windows COM interaction capabilities
pip install xlwings   # Enables direct Excel integration

Highlights of the Network Monitoring Script

  1. Thread-Safe Excel Usage
    By carefully separating tasks into different Excel windows, it avoids COM errors and provides a stable user experience.
  2. Real-Time Status
    The script continuously pings devices, updating Excel in near real time.
  3. Visual Alerts
    Offline devices show up in a clearly colored cell, while active ones appear in a different color, reducing the risk of oversight.
  4. Optional Shared Folder Detection
    If the script is configured to do so, it can also query Windows network shares and list them directly in Excel.

Taking Network Monitoring to the Web Browser

While an Excel-based monitoring tool can be incredibly handy, one question inevitably arises:

“Wouldn’t it be great if we could view this dashboard from other computers as well?”

That thought led me to upgrade the monitoring system into a browser-based tool. With a web interface, you can keep tabs on your network from any device on the same network—no more being chained to a single Excel file.

But First, Let’s Organize the Development Environment

In the process of writing the earlier Excel-plus-Python scripts, I realized my local machine was becoming a bit messy. I had installed numerous Python packages over time and found myself wondering, “Which project is using which library again?” That’s never a good sign. So, this time around, I decided to do it the right way by creating a dedicated virtual environment for my project.

Why Use a Virtual Environment?

A virtual environment (venv) helps keep your dependencies isolated. In other words, any library you install won’t conflict with packages from other projects or clutter your system-wide Python installation. It’s considered a best practice for professional developers and data scientists alike.

Creating a Virtual Environment: Step by Step

  1. Open the Command Prompt
    On Windows, you can simply search for “Command Prompt” or use PowerShell.
  2. Run the venv Module
    python -m venv venv
    This command creates a new folder called venv that holds all the files for the isolated Python environment.
  3. Activate the Environment
    On Windows, type:
    venv\scripts\activate
    If you see (venv) appear before your command prompt, you’re good to go.

Pro Tip: Make sure you’re in the correct project directory when creating and activating the virtual environment. This keeps everything neatly contained in one place.

Installing the Necessary Packages

When I reopened the code in VSCode, a few warnings indicated missing dependencies. Specifically, I wanted to manage network interfaces, generate Excel files, and build a browser interface. Here’s an example of the packages I installed:

pip install openpyxl netifaces
pip install streamlit pandas streamlit_autorefresh

Note: Depending on your project, you might need additional libraries.

Why Choose Streamlit for the Web Version?

Several Python frameworks (like Flask or Django) let you build web apps. However, for data visualization and real-time monitoring, Streamlit offers a more direct path:

  1. Code Simplicity: You mainly write Python code without worrying about HTML or JavaScript.
  2. Real-Time Refreshes: It’s easy to configure periodic updates, so your network data stays current.
  3. Instant UI: Streamlit automatically creates widgets (buttons, sliders, etc.) and organizes them into a visually pleasing layout.

This can save hours of front-end work compared to building a traditional web app from scratch.

Preview of the Setup

Once everything is installed, the overall workflow looks like this:

  1. Run the Python Monitoring Script
    • This script continuously gathers network data (e.g., ping times, device statuses).
  2. Launch Streamlit
    • It spins up a local web server and displays the results in your browser.
  3. Auto-Refresh
    • The dashboard updates itself, allowing you to quickly spot any network issues.

Simultaneously Running Your Monitoring Script and Browser App

With your environment now set up, you’ll need to run two separate programs to make the browser-based monitoring tool work smoothly:

  1. realtime_lan_monitor.py
    • Handles the core functionality of network scanning and data logging.
  2. app.py
    • Powers the Streamlit-based web interface, allowing you (and others) to view the results in a browser.

Most of the time, you can run a single Python script from within VSCode. But since we require two concurrent processes, a simple approach is to run one program in VSCode and the other in a separate PowerShell (or Command Prompt) window.

Why Separate Windows?

While you can theoretically manage both scripts from within VSCode using multiple integrated terminals or advanced workspace setups, it’s often less confusing to just open a second window for the second process. If something goes wrong in one program, it won’t interfere with the other. This separation also makes debugging simpler, because each console logs only the messages relevant to its script.

Note: If you’re more comfortable with advanced configurations, you can absolutely keep both processes within VSCode. But for many people, having a dedicated PowerShell window is more intuitive.

Nextcloud
excellan.zip Nextcloud – a safe home for all your data

Step-by-Step Execution

  1. Run the Main Monitoring Script (VSCode)
    • Open realtime_lan_monitor.py in VSCode.
    • Make sure your virtual environment is activated.
    • Enter:
      python realtime_lan_monitor.py
    • This starts your network scanning in the background and typically outputs logs (e.g., ping results) to the console.
  2. Launch the Web Interface (PowerShell)
    • Open a fresh PowerShell window.
    • Navigate to the same project folder:
      cd C:\lan
    • Reactivate the same virtual environment:
      venv\scripts\activate
    • Finally, launch Streamlit:
      streamlit run app.py
  3. Confirm the Dashboard
    • After a short delay, your default browser should open, displaying the Streamlit dashboard where network statuses automatically update.

What to Expect Once Running

  • Local Website: You’ll typically see a local URL (e.g., http://localhost:8501) in your console. When you navigate there, you’ll find a live updating table or chart reflecting your network’s state.
  • Log Files: You may also notice log files (like lan_monitor.log) appearing in the project folder, capturing key events and errors.
  • Temporary Excel File (Optional): If your code still uses Excel integration for certain tasks, you might see an Excel file generated. This is normal and may be reused for additional data checks or visual formatting.

Troubleshooting Tips

  • Port Conflicts: If 8501 is already in use, Streamlit might automatically choose another port. Check the console output for the correct URL.
  • Missing Packages: A “module not found” error usually means you need to install a library. Double-check that you’ve activated your virtual environment before installing anything.
  • Permission Errors: If you’re using advanced system functions (like raw sockets or certain admin commands), you may need to run PowerShell or VSCode as Administrator.

After the Setup: Verifying Everything Works

Once both your Python monitoring script and the Streamlit app are running, you should see a few telltale signs that things are set up correctly:

  1. Streamlit Browser Window
    Your default browser will open a local address (like http://localhost:8501) featuring a dynamic interface. Device statuses, IP address pings, or any other monitored metrics should automatically refresh based on the interval you specified in your script.
  2. Console Output
    Both your VSCode terminal (running realtime_lan_monitor.py) and your PowerShell window (running streamlit run app.py) will display logs. Keep an eye out for:
    • Ping Results (to confirm devices are responding)
    • Errors/Warnings (e.g., permission issues or missing libraries)
    • Refresh Notices indicating the Streamlit page has been reloaded
  3. Log Files or CSV Outputs (If Configured)
    Depending on your script’s design, it may generate one or more log files—like lan_monitor.log for events or snapshot_log.csv for capturing device statuses at fixed intervals. Checking these files can be especially helpful for historical analysis or troubleshooting.

Common Problems and How to Solve Them

Despite careful planning, issues can pop up. Below are some frequent roadblocks and potential fixes:

IssuePossible CauseSuggested Fix
Streamlit page fails to loadAnother application is using the same port (8501)Streamlit automatically tries a new port if 8501 is busy. Check your PowerShell output for the new URL.
Modules not foundInstalled packages in the wrong environment or forgot to activate venvDouble-check you have venv\scripts\activate in your console before installing requirements.
Access denied (Permission error)Certain network scans or processes require Admin privilegesRun PowerShell or VSCode as an Administrator, or adjust your user permissions on Windows.
Excel or CSV file locks up / refuses to saveFile is still open in another app, or concurrency conflictClose the file in Excel or turn off any external process locking the file, then rerun the script.
Network devices not showing upDevice may be blocking pings or offlineConfirm that ICMP ping is allowed, or manually add the device to a priority list if needed.

Hint: Keeping logs and enabling verbose output can help pinpoint which part of the process is failing. If you suspect firewall interference, temporarily disable it (with caution) to see if pings start succeeding.

Personalizing the Settings

Every network environment has unique demands. Consider adjusting your script’s default parameters to suit your needs:

  • Ping Interval
    If you want more frequent updates, shorten the interval. But be mindful of the potential extra load on your network.
  • Full Scan Frequency
    If you have a large subnet, you may not want to do a full 255-IP sweep every few minutes. Extend this interval to reduce overhead.
  • Log Format
    Some organizations prefer JSON logs instead of CSV for easier integration with third-party tools. Adapt the script’s logging function accordingly.
  • Alert Thresholds
    If you’re monitoring a critical server, set up an immediate notification (like an email or Slack message) whenever it goes offline.

A Closer Look at the Network Monitoring Code

Previously, we focused on the environment setup and the execution process. Now let’s delve into the actual Python code that runs the monitoring logic—especially how it displays live network status in a browser environment. We’ll cover two core scripts:

  1. app.py: The shorter file responsible for the Streamlit interface.
  2. realtime_lan_monitor.py: The “brains” of the operation, where most of the network scanning, data handling, and logging occur.

How the Streamlit App (app.py) Ties Everything Together

Let’s start with the simpler script, app.py. Even though it’s fairly short, it packs quite a punch:

  1. Establishes a Web Interface
    By importing Streamlit and defining a few layout components (buttons, status displays, etc.), app.py becomes your real-time dashboard.
  2. Refreshes Automatically
    Using packages like streamlit_autorefresh, the page updates itself without any manual intervention. This means you can monitor your network without constantly hitting the “refresh” button.
  3. Communicates with the Monitor
    Under the hood, app.py references shared data structures from the main monitoring script, ensuring that live data (such as device IPs and statuses) is always at your fingertips.

Diving into the Main Script (realtime_lan_monitor.py)

While app.py handles the “frontend,” realtime_lan_monitor.py does most of the heavy lifting:

  1. Network Discovery
    • Uses socket, ping, or other system calls (like subprocess) to detect which IPs are up and running.
    • In many setups, it may also detect shared folders (e.g., via net view on Windows).
  2. Data Structures for Device Status
    • Maintains dictionaries or lists that store information about each discovered device, such as IP, hostname, last ping time, and detection count.
  3. Concurrency Management
    • Often uses ThreadPoolExecutor from Python’s concurrent.futures module to speed up network scans.
    • If done carefully, multi-threading can significantly reduce scan times—especially on larger networks.
  4. Logging and Persistence
    • Logs ping outcomes, errors, and changes in device status.
    • May optionally export data to CSV or Excel for historical analysis. This way, you can spot patterns like frequent disconnections or unusual traffic spikes.

Thread Safety Considerations

When you’re manipulating shared data structures in multiple threads, you need to handle them carefully—especially if you’re updating the same dictionary or list from different threads. Many developers use a “lock” (from the threading module) to prevent race conditions. This is crucial for avoiding data corruption, which could cause inaccurate results or random crashes.

Example Flow: From Scan to Display

Here’s a short outline of how your system might work once it’s all put together:

  1. The Monitor Script (realtime_lan_monitor.py)
    • Pings devices or performs other network checks.
    • Updates a shared data structure (like monitor.last_devices).
  2. The Streamlit App (app.py)
    • Periodically reads monitor.last_devices.
    • Renders the information in a pandas DataFrame or other Streamlit components.
    • Automatically refreshes every few seconds (thanks to streamlit_autorefresh).
  3. User Watches
    • Opens http://localhost:8501 (or the port shown in the console).
    • Observes color-coded statuses or real-time charts for each detected device.

Where to Insert Code Blocks

If you plan to share code snippets or additional notes, consider placing them in collapsible sections or using a table to illustrate differences between various configurations or device statuses. For instance:

ConfigurationDescription
refresh_intervalHow often the script performs a basic scan (in seconds).
full_scan_intervalHow often it performs a thorough, whole-subnet sweep.
check_shared_foldersBoolean toggle to enable Windows shared folder detection.
dhcp_range_start/endIP range to check if the active device list is suspiciously small.

An Even More Evolved Monitoring Tool: The CSV Version

While the Excel-based approach offers a familiar interface for many users, there’s a lighter-weight alternative that might be better suited for long-term data collection or advanced analytics: saving logs in CSV format. By moving away from Excel as the primary data store, this method tends to be more flexible and less error-prone—especially if you’re running the monitoring tool on a server or headless machine.

Why Consider a CSV Approach?

  1. Reduced Overhead
    Excel’s file structure can be complex, potentially leading to file lock issues when multiple processes try to access it. CSV files are just plain text, so they’re simpler to manage and less likely to trigger concurrency conflicts.
  2. Easy Integration
    CSV is highly portable. Third-party tools and custom scripts can process CSV data without needing a specific Excel library. This makes it straightforward to integrate your monitoring logs with other systems—like a database or a dashboard framework.
  3. Lower Risk of Corruption
    Spreadsheet files can become corrupted if something interrupts their save process (like a crash or forced reboot). CSV files, being mere lines of text, are more resilient in such situations.

Key Differences from the Excel-Based Version

AspectExcel VersionCSV Version
Primary StorageExcel file (.xlsx)Plain-text CSV files (.csv)
Logging ApproachSome logs may be embedded into the spreadsheet via macros or COMSeparate CSV files for events, snapshots, or any other data categories
Analysis ToolsExcel’s built-in charts & formulasCSV data can be opened by any program, or ingested into databases or BI tools
Potential DrawbacksCan get locked up by concurrency, heavier overheadRequires external apps (e.g., Python, pandas) for advanced visualization

This table summarizes how each approach suits a different use case. If you’re comfortable with Excel’s environment and appreciate its design features, the Excel-based version might be fine. But if scalability and cross-platform integration are top priorities, CSV is usually a better bet.

CSV Logging Essentials

  1. Event Logging
    • This captures real-time changes, such as when a device goes offline or when a new IP is discovered.
    • Typically stored in something like event_log.csv, where each row details a timestamp, the IP, and old/new status.
  2. Snapshot Logging
    • Records the entire network state at regular intervals (e.g., every 10 minutes).
    • Might go in a file like snapshot_log.csv, with rows containing the total online/offline count, plus a list of the currently active IPs.
  3. Unified Encoding
    • Make sure you use a consistent encoding (e.g., UTF-8 with BOM) to avoid garbled text when reading logs in Excel or other applications.

Practical Tips for CSV-Based Tools

  • Automated Rotation
    If you run the monitoring tool continuously, your CSV files can grow large. Consider implementing a log rotation strategy—perhaps creating a new file daily or weekly to prevent bloated files.
  • Historic Analysis
    Once you have a series of snapshot logs, you can easily chart how many devices are online over time, or see patterns in when certain IPs drop off. This can be done in Excel, Python (with pandas/matplotlib), or any BI platform.
  • Combining the Best of Both Worlds
    Some developers keep their main logs in CSV and only load them into Excel occasionally for formatting or higher-level analysis. You can even create macros in Excel to regularly import CSV data, striking a balance between the CSV approach’s simplicity and Excel’s powerful UI.

Wrapping Up: Choosing the Right Approach for Your Needs

By now, we’ve explored multiple ways to monitor your network using Python and Excel—or even a full-fledged browser interface with tools like Streamlit. Each approach comes with its own pros and cons, and the best one for you depends on the specifics of your environment.

When Excel is the Star

If your workflow revolves around Excel, and you value its ease of use and built-in formatting tools:

  • Python in Excel (Official Integration): Perfect if you have a Microsoft 365 subscription and want a quick way to run Python code directly in spreadsheets.
  • Excel COM Automation: A traditional approach that leverages libraries like pywin32 or xlwings. Great if you’re building custom dashboards and want tight Excel integration.

Opting for a Browser-Based Tool

For those who prefer a platform-agnostic setup or need remote access from different machines, the Streamlit-based app offers:

  • Instant Web Dashboards: No need for desktop Excel installations or specialized software.
  • Flexible Deployment: You can host the script on a server, allowing multiple users to view the live monitoring dashboard simultaneously.

CSV Logging for Scalability

If your goal is long-term data collection or you plan to integrate with other analytics platforms:

  • Lightweight & Resilient: CSV files are easy to process, merge, or archive.
  • Broad Compatibility: Practically every data tool (Excel, BI dashboards, Python, R, etc.) can consume CSV data.

Final Thoughts and Next Steps

  1. Customize to Fit
    No single method is a one-size-fits-all solution. Start by identifying your primary needs (e.g., real-time visibility, remote access, long-term analytics), then tailor your scripts, logging strategy, and data storage accordingly.
  2. Explore Alerts & Automation
    Basic pings and status checks are only the beginning. Consider adding email alerts, Slack notifications, or even SMS messages for critical devices. This extra layer of automation helps ensure you don’t miss crucial events—even if you’re away from your dashboard.
  3. Stay Updated
    Both Excel and Python ecosystems evolve quickly. Microsoft frequently rolls out new features, and Python libraries gain enhancements at a rapid pace. Make sure to keep an eye on release notes and community resources for best practices.
  4. Collaboration & Sharing
    If you’re working in a team environment, consider setting up version control (e.g., Git) to track code changes. For Streamlit apps, you might even explore deploying to a shared cloud service so everyone can access the same real-time data.

Where to Go from Here

If you’re interested in delving deeper, consider:

  • Extended Data Visualization: Using libraries like matplotlib or plotly to create interactive charts of historical downtime or trending ping times.
  • Database Integration: Storing your CSV or Excel data into a SQL or NoSQL database for more robust querying.
  • Machine Learning for Anomaly Detection: As your dataset grows, you could apply ML models to predict or detect unusual network patterns.

Thank you for following along with this guide. Hopefully, you now have a solid understanding of how to build, adapt, and scale a Python-driven network monitoring solution—whether that means sticking with Excel, going the CSV route, or embracing a modern browser-based interface.

education

If you like this article, please
Follow !

Please share if you like it!
table of contents