How to run a SAP 10 calculation in Excel

This article looks at using an Excel workbook to run SAP 10.2 energy calculations for new and existing dwellings.

What is SAP 10?

The Standard Assessment Procedure is a method to calculate the energy consumption of UK dwellings. SAP version 10 is published by the Building Research Establishment (see here). SAP 10.2 is the latest version and is used by the UK government in the building regulations for the design of new dwellings. SAP is also the calculation engine for domestic energy performance certificates.

How can I get the Excel workbook?

It can be downloaded here: https://www.stevenfirth.com/downloads/

What does the Excel workbook do?

The workbook acts as a wrapper for the online SAP10 API here. It does the following:

• It takes the data from the input worksheet
• Converts it to a SAP 10 input file (an XML file based on the schema here)
• Sends the input file to the online SAP10 API
• Gets back the results of the calculation
• Places the calculation outputs in a results worksheet

What inputs are required?

A screenshot of the SAP10 inputs is shown below.

The "SAP10_Inputs" worksheet contains all the information needed to run a SAP10.2 calculation. The values in the "Input Data" column can be updated to run different types of dwellings with different characteristics.

It is also possible to:

• load an existing SAP10 XML file into the worksheet
• save the data from the worksheet to a SAP10 XML file
• add or remove input variables, such as adding another window or including a solar PV system

These options are available by right-clicking on a row in the worksheet and viewing the options at the bottom of the menu.

How to run a SAP10 calculation?

A SAP10 calculation is run by calling the RunSAP10Calculation macro.

This done by either:

• right-clicking on any cell, and choosing the "Run SAP 10 calcuation" menu option
• using the keyboard shortcut ctrl + m

What are the calculation outputs?

A screenshot of the calculation outputs is shown below.

The API returns hundreds of intermediate calculations, and a selection of these are automatically placed in the "SAP10_Results" worksheet. This is formatted in the exact same style as the SAP Worksheet in the official SAP10.2 PDF document.

What else do I need to know?

The main thing is that you may encounter error messages if you start to change the choice of input variables, such as changing the type of heating system or adding a hot water cylinder.

This is because these changes will mean that other input variables are now required. For example, if you add a hot water cylinder then you also need to include the type and thickness of the hot water cylinder insulation. This is done by using the "Add XML child element" menu option in the right-click menu.

In most cases, the error messages you receive should tell you what data is missing.

But in some instances you might receive an internal error message instead. This means that the particular combination of inputs variables you are using doesn't have a bespoke error message yet, or possibly hasn't yet been implemented in the API.

If this is the case and you can't see how to solve the problem, please get in touch.

Summary

• An Excel workbook to run SAP10.2 energy calculation is available here.
• This makes use of an existing online SAP10 calcuation API (see here).
• This can be used to run a SAP10.2 calculation for a single dwelling.
• Changes can then be made to the dwelling, and new calculations can be run to see the effect of those changes.