Export, Verify and Consolidate Multi-Sensor Audit Data in Excel
Export raw Audit Report data from multiple sensors into one Excel workbook, protect or verify the original file, and optionally create a consolidated temperature table and statistical summary with supplied Excel macros.
Create a Controlled Multi-Sensor Excel Record
Telemetry2U Audit Reports can export raw records from several selected sensors into a single Excel workbook. Each selected sensor input is placed on its own worksheet, while optional workbook protection and SHA-512 document verification provide separate controls for editing and file-integrity checking.
The optional macros in this bulletin are intended for users who also need to place temperature readings from multiple sensor sheets into one timestamp-aligned worksheet or generate a statistical summary. These macros modify the workbook and therefore should be used on a working copy when the original generated file must remain independently verifiable.
An Audit Admin configures the report and selected nodes. Authorised users can then download available Audit Reports.
The optional BAS modules use VBA and require a desktop Excel installation that permits macros. Excel for the web does not run this workflow.
Keep the original file when verification matters. Telemetry2U document verification confirms whether a supplied file is identical to the originally generated document. Editing, re-saving or running either macro changes the workbook and can produce a different SHA-512 hash.
1. Configure the Audit Report and Sensor Inputs
Open the required report under Audit Report Maintenance. The entries under Nodes to include determine which node and sensor inputs are included when the report is generated.
- Add each required node and select the sensor input to include.
- Enable Protect Excel sheet when the downloaded Excel workbook should be protected against normal worksheet editing and workbook-structure changes.
- Enable Include document verification when generated PDF, CSV and Excel files should include a Document ID and verification link.
- Select Save after completing the report configuration.
Using the optional macros? Leave Protect Excel sheet cleared. Both supplied macros need to create or replace worksheets, which workbook-structure protection prevents. Document verification can still be enabled for the original download.
Scheduled-report extracts: Include CSV extract and Include Excel extract control whether those raw-data files are attached when a scheduled Audit Report PDF is emailed. They are not required when a user manually selects CSV or Excel as the output format on the Download Audit Report page.
For the complete Audit Report configuration and scheduling workflow, see How to Create and Schedule an Audit Report and the Audit Reports help documentation.
2. Download the Audit Report in Excel Format
Open Download Audit Report from the platform menu, then select the saved report definition and the period to export.
- Select the required Report name.
- Set the Start date and End date.
- Select Excel as the Output Format.
- Select Load to generate and download the workbook to the local computer.
3. Review the Multi-Sensor Workbook
The downloaded Excel file contains one worksheet for each selected sensor input. Each sheet retains that sensor's own timestamped records, making the original measurements available independently inside the same workbook.
When Include document verification is enabled, generated files also contain a unique Document ID and verification link. Excel protection and document verification are separate controls: workbook protection restricts normal editing, while SHA-512 verification determines whether a file remains identical to the original generated file.
4. Verify the Original File with SHA-512
Follow the Document verification link embedded in a verification-enabled PDF, CSV or Excel file. The link opens Telemetry2U Document Verification and supplies the Document ID automatically. A Document ID can also be entered manually and located with Find document.
Telemetry2U displays the Document ID, the UTC generation time and the original SHA-512 hash stored when the file was created. To check a copy of the file:
- Select Choose file and upload the PDF, CSV or complete Excel workbook.
- Select Calculate and compare SHA512.
- Telemetry2U calculates the uploaded file's hash and compares it with the stored original.
A matching result confirms that the supplied file is byte-for-byte identical to the original generated document. Editing, re-saving or digitally signing a file can change its hash and cause the comparison to fail.
5. Optional: Install the Excel Macros Permanently
Telemetry2U provides two optional VBA modules for desktop Excel. Installing them in
PERSONAL.XLSB makes the tools available whenever Excel is running without
embedding the macros inside each Audit Report workbook.
Combine Temperature Sensors
Creates a new Combined Data worksheet containing exact timestamps that occur on every detected temperature sensor sheet.
Generate Temperature Statistics
Creates a new Statistics worksheet with minimum, maximum, average, median and population standard deviation for each temperature sensor sheet.
Only enable and run VBA macros from a source approved by your organisation. Organisational Excel security policies may prevent VBA macros from running.
Create the Personal Macro Workbook if Required
If PERSONAL.XLSB does not already exist, create it once:
- Open Excel and select View > Macros > Record Macro.
- Under Store macro in, select Personal Macro Workbook.
- Select OK, then immediately select Stop Recording.
Import the BAS Files
- Press Alt + F11 to open the Visual Basic Editor.
- In the Project panel, select VBAProject (PERSONAL.XLSB).
- Select File > Import File....
- Import
CombineTemperatureSensors.basand repeat the process forCreateTemperatureStatistics.bas. - Close Excel and select Save when prompted to save changes to the Personal Macro Workbook.
Add the Macros to the Quick Access Toolbar
- Open File > Options > Quick Access Toolbar.
- Change Choose commands from to Macros.
- Add
PERSONAL.XLSB!BuildCombinedTemperatureSheet. - Add
PERSONAL.XLSB!BuildTemperatureStatisticsSheet. - Use Modify to select an icon and assign short labels such as Combine Sensors and Generate Sensor Statistics.
6. Combine Temperature Sensors onto One Worksheet
Open an unprotected Audit Report workbook and run
BuildCombinedTemperatureSheet from the Quick Access Toolbar or the
Excel macro list. The macro works on the currently active workbook, not on
PERSONAL.XLSB itself.
The macro:
- requires at least two qualifying worksheets and finds sheets containing both Local Time and Temperature headings, regardless of which columns contain those headings;
- uses the worksheet name as the sensor name;
- keeps timestamps that occur on every detected sensor sheet;
- uses exact timestamp matches only and does not interpolate missing readings;
- creates or rebuilds a Combined Data worksheet;
- places Local Time in the first column and one temperature column per sensor; and
- leaves the original sensor worksheets in the workbook.
The combined sheet is a derived working record. Running the macro changes the workbook, so the modified file is not covered by the SHA-512 verification record for the original Telemetry2U-generated workbook.
7. Generate Temperature Statistics
Run BuildTemperatureStatisticsSheet to create a separate statistical
summary directly from the original temperature sensor worksheets. The macro can be
used independently of the Combined Data macro.
For every worksheet containing a Temperature heading, the macro calculates:
- Minimum
- Maximum
- Average
- Median
- Population standard deviation
The results are written to a new Statistics worksheet using the original worksheet names as the sensor identifiers. Existing Combined Data and Statistics sheets are excluded from the source calculations.
Recommended Record-Keeping Workflow
When both document verification and Excel analysis are required, retain the original Telemetry2U download unchanged and create a separate working copy before running either macro. The original file can then continue to be checked against its stored SHA-512 hash, while the working copy contains the Combined Data or Statistics worksheets required for analysis.
Keep unchanged for Document ID and SHA-512 verification against the Telemetry2U record.
Use this copy for consolidation, statistics and any other spreadsheet changes required by the review process.