Transferring SIMOTION D425 Shift Data to Excel via OPC and TCP

Overview: From SIMOTION Array to Excel Workbook

Production data for a pin-setting machine is generated cycle-by-cycle in a SIMOTION D425 motion controller and accumulated shift-by-shift inside a structured array. The typical requirement is to retain 365 days of shift totals (good pieces, rejects, downtime, OEE inputs) and to expose that array to a PC on the same LAN so that supervisors can open it directly in Microsoft Excel for analysis, charting, and ERP upload.

The D425 (part of the SIMOTION D4xx range, firmware V4.x / V5.x) does not natively create a .xls file - it is a real-time motion controller, not a file server. The data must therefore leave the controller over its Ethernet interface using one of the supported open protocols and be written to a spreadsheet by a PC-side service. The four practical paths are:

1. OPC XML DA server running on the SIMOTION (no PC license, simple polling).
2. SIMATIC NET OPC DA Server on the PC, reading SIMOTION variables over the SOFTNET interface.
3. TCP/IP communication using the SCOUT "Interbranch Communication" library (binary, deterministic, free).
4. FTP file drop of a .csv followed by Excel auto-refresh (requires a small PC-side daemon).

Each method is covered below with prerequisites, programming steps, sample code, and a verification procedure.

Architecture and Network Prerequisites

Method 1 - OPC XML DA Server Running on the D425

The OPC XML DA server is embedded in the SIMOTION firmware from V4.2 onward. It exposes every variable published on the controller as an OPC item, and any OPC XML client - including the OPC.Excel.Connector add-in or a Power Query feed - can subscribe to it.

Step 1: Publish the production array

In SCOUT, open the unit that owns the shift data. Right-click the array tag (e.g. shiftData[1..365]) and select Properties > OPC > Exportable. Set the OPC visibility to Public for the top-level structure; child elements are exposed automatically.

// MCC source snippet - declaring the exportable array
TYPE shiftRecord
    STRUCT
        shiftId       : DINT;       // 1..3
        dateJulian    : DINT;       // days since 2000-01-01
        goodParts     : UDINT;
        rejectParts   : UDINT;
        downtimeSec   : UDINT;
        runtimeSec    : UDINT;
    END_STRUCT;
END_TYPE

VAR_GLOBAL
    shiftHistory : ARRAY[1..365] OF shiftRecord;   // OPC-exported
END_VAR

Step 2: Activate the OPC XML server in the project

1. Open SIMOTION > Commissioning > Web server / OPC settings in SCOUT.
2. Enable OPC XML DA Server and assign HTTP port 80.
3. Set the user authentication to Anonymous read if the array is non-sensitive, or define a user in the SIMOTION user administration.
4. Download the project to the D425 and run RUN.

Step 3: Read the array from Excel with Power Query

1. Open Excel > Data > Get Data > From Other Sources > From OData Feed (OPC XML DA is a compatible OData-like service for Microsoft clients).
2. Enter the URL: http://192.168.0.10:80/soap/opcxml and the device name, e.g. SIMOTION/D425.
3. Select the items shiftHistory.goodParts, shiftHistory.rejectParts, etc.
4. Click Transform, pivot the array to a tabular layout, then Close & Load To > Table.
5. Schedule refresh with Query > Properties > Refresh every 5 minutes.

Method 2 - SIMATIC NET OPC DA Server on the PC

For sites that already own a SIMATIC NET licence, the OPC.SimaticNet server is the most robust option. It supports DA 2.0 / 3.0, has high item density, and is reachable from any OPC client such as OPC.Excel.Connector, WinCC, or a custom C# application.

Step 1: Install SIMATIC NET on the PC

1. Install SIMATIC NET PC Software V16 SP1 (or V18 for TIA V17 projects).
2. During install, select OPC Server and SOFTNET-IE S7 Lean (one S7 connection) or SOFTNET-IE S7 Standard (up to 16 connections).
3. Restart the PC and verify the SIMATIC NET Configuration Console shows the Ethernet adapter bound to the S7 protocol.

Step 2: Configure an S7 connection to the D425

1. Launch Station Configuration Editor and add a PC station with index 1 (matches the OPC default).
2. Insert an OPC Server slot and an IE General module on the same station.
3. Open SIMATIC NET Configuration > Connections and create a new S7 connection to 192.168.0.10 (D425), rack 0, slot 2 (D4xx CPU slot), connection resource DB 1.
4. Download the PC station configuration.

Step 3: Expose the array in the OPC namespace

The SIMATIC NET OPC server reads item names in the form S7:[S7 connection_1]DBx,BYTE y. For symbolic access (preferred), enable Symbolic access to S7-1500 / SIMOTION in the SIMATIC NET configuration and use the symbolic path:

S7:[D425_OPC]shiftHistory[1].goodParts
S7:[D425_OPC]shiftHistory[1..365].runtimeSec

Step 4: Read from Excel with OPC.Excel.Connector

1. Install the OPC Excel Connector add-in (Microsoft, free, retired but still redistributable) or use a modern replacement such as OPC-DA-to-Excel bridge in C#.
2. Add a new Server pointing to OPC.SimaticNet.
3. Bind cells A1:A365 to shiftHistory[i].goodParts using the =OPCItem formula.
4. Press Refresh All; the array is filled within ~600 ms for 365 records.

Method 3 - TCP/IP via the SCOUT Communication Library

When licensing cost must be zero and the dataset is large or proprietary, the SIMOTION Interbranch Communication (IBS) library on the SCOUT Utilities & Applications DVD provides a deterministic TCP/UDP stack callable from MCC/ST/LAD. The library is part of the standard SCOUT install (Start > Programs > Siemens Automation > SCOUT > Utilities & Applications > Interbranch Communication).

Step 1: Install the library

1. Copy the library archive to a working folder and unpack it.
2. In SCOUT, open Libraries > Open Library and select the unpacked .zip / .slb.
3. Drag the TCP_IP_basic and TCP_IP_stream function blocks into your project.
4. Compile and download.

Step 2: SIMOTION-side TCP server

Create a unit ShiftDataExport with the following ST code. The SIMOTION listens on port 2000, accepts a GetShift command, and returns a binary record. The PC acts as a client and writes the bytes into Excel as a tab-separated file.

PROGRAM ShiftServer
VAR
    fbListen    : TCPIP_Listen;        // from library
    fbAccept    : TCPIP_Accept;
    fbRecv      : TCPIP_Receive;
    fbSend      : TCPIP_Send;
    hSocket     : DINT := -1;
    rxBuf       : ARRAY[0..255] OF BYTE;
    txBuf       : ARRAY[0..8191] OF BYTE;
    cmd         : STRING[32];
    i           : DINT;
    pRecord     : POINTER TO shiftRecord;
END_VAR

// Trigger: Rising edge of _TCP.startServer
IF _TCP.startServer THEN
    fbListen(port := 2000, hSocket := hSocket);
END_IF;

// Accept a pending connection (polled each cycle)
fbAccept(hSocket := hSocket, subSocket := hSocket);

// Receive a 32-byte command
fbRecv(socket := hSocket, pData := ADR(rxBuf), dataLen := 32, recLen := i);
IF i > 0 THEN
    cmd := STRING_FROM_BUFF(rxBuf, 0, 32);
    IF cmd = 'GETSHIFT' THEN
        // Build a 365 x 28 byte payload = 10220 bytes
        FOR i := 1 TO 365 DO
            pRecord := ADR(shiftHistory[i]);
            memcpy(ADR(txBuf) + (i-1)*SIZEOF(shiftRecord),
                   pRecord, SIZEOF(shiftRecord));
        END_FOR;
        fbSend(socket := hSocket, pData := ADR(txBuf),
               dataLen := 365 * SIZEOF(shiftRecord), sentLen := i);
    END_IF;
END_IF;

Step 3: PC-side client (Python example)

Python's socket module plus pandas to push the data to .xlsx - the cheapest, fastest and most portable approach.

import socket, struct, pandas as pd

HOST, PORT = '192.168.0.10', 2000
RECORD_FMT = '<i i I I I I'      # 28 bytes matching shiftRecord
RECORD_LEN = struct.calcsize(RECORD_FMT)

s = socket.socket(); s.connect((HOST, PORT))
s.sendall(b'GETSHIFT\x00' + b'\x00'*24)

payload = b''
while len(payload) < 365 * RECORD_LEN:
    chunk = s.recv(4096)
    if not chunk: break
    payload += chunk

rows = [struct.unpack(RECORD_FMT,
        payload[i*RECORD_LEN:(i+1)*RECORD_LEN]) for i in range(365)]
df = pd.DataFrame(rows, columns=['shiftId','dateJulian',
        'goodParts','rejectParts','downtimeSec','runtimeSec'])
df.to_excel(r'C:\ShiftReport\history.xlsx', index=False)
s.close()

Step 4: Schedule the export

1. Save the script as C:\ShiftReport\pull.py.
2. Open Task Scheduler and create a task that runs at every shift change (e.g. 06:00, 14:00, 22:00) with action python.exe C:\ShiftReport\pull.py.
3. Add an error event to the Windows Event Log so MES can pick it up.

Method 4 - FTP File Drop of a CSV

When the PC application (MES, custom .NET) is already polling a shared folder, the simplest method is to have the D425 write a CSV block to a shared network share. SIMOTION does not include a CIFS client, so the pattern is reversed: a PC daemon requests a chunk of data over OPC or TCP, appends a line to history.csv, and Excel opens it directly with Data > From Text/CSV or a refreshable query.

1. Create a network share \\\\MES-PC\\ShiftData with write access for the PC service account.
2. Extend the TCP server to support the command GETCSV; the response is a single line of comma-separated values for one shift.
3. The PC appends the line to the CSV and renames history.csv with a date stamp once per day.
4. Excel Power Query refreshes on open - the file path is in the query definition.

Designing the Array for Smooth Export

Whatever method you pick, the array geometry on the SIMOTION must match what Excel expects. A common pitfall is a sparsely populated array - shift 365 is empty on day 1 of commissioning, so Power Query reads 0 instead of NULL. Use the following rules of thumb:

Keep the array fixed-size (365) to avoid OPC XML dynamic-array re-browsing. A ring buffer or pointer indirection only confuses the spreadsheet side.

Verification Procedure

1. Reachability: From the PC, open a command prompt and run ping 192.168.0.10. Latency must be below 5 ms on a healthy LAN.
2. OPC XML discovery: In a browser, open http://192.168.0.10/soap/opcxml?wsdl. You should see a WSDL document; if you get 401 Unauthorized, check the user list on the D425.
3. Item browse: In SIMATIC NET OPC Scout V10, connect to OPC.SimaticNet, navigate to S7:[D425_OPC]shiftHistory and confirm the array length is 365.
4. Read test: Add a Read DA item group of three elements, click Read. Update cycle should report < 100 ms for 365 records.
5. Excel round-trip: Refresh the workbook, then deliberately corrupt one cell. The next refresh should overwrite it back to the SIMOTION value - if it doesn't, you are looking at a static copy rather than a live query.
6. Long-run: Leave the query refreshing overnight and verify the array does not stall; a 24-hour stability test catches TCP socket leaks in custom code.

Troubleshooting Matrix

Method Selection Cheat Sheet

For a single-machine commissioning such as the pin setter, OPC XML on port 80 is the lowest-effort and most maintainable path. Reserve the TCP/UDP library for OEM-style machines that are shipped to many sites with restricted outbound connectivity.

Field-Proven Commissioning Tips

• Always export the symbolic name, not the absolute address - it survives firmware upgrades and project refactoring.
• On the D425, mark the data block as retain in the unit properties; otherwise the array is cleared on every STOP/RUN transition.
• Set the OPC XML update rate to at least 500 ms. A faster rate wastes CPU on the controller and may stall the servo.
• Use a dedicated VLAN for OPC traffic if the line is shared with PROFINET IO motion devices; broadcast storms from office PCs will otherwise jitter the bus.
• Tag every export job with a version number stored in the array header, so spreadsheet formulas referencing "production v3" do not break when the schema changes.
• Validate the array on day 1: write a known pattern (1..365) into the array in Startup task and confirm Excel shows the same numbers. Then clear the array and let the real counters fill it.

FAQ