Skip to content

Energy simulation in Excel or in Wattix?

Last updated

Excel is good enough for energy simulation as long as you calculate one asset and one scenario. Once a battery, solar and chargers must share one grid connection, every quarter-hour depends on the last, and you want to compare scenarios. That is where a spreadsheet breaks down. Wattix simulates every quarter-hour, optimises the battery and compares scenarios side by side.

What is Excel good at?

Almost every installer and consultant calculates in Excel, and for good reason. You set your own assumptions, you can see every formula, and you already have it. For a first estimate with one asset, or a yearly total of consumption and generation, a spreadsheet is fast and good enough.

What has changed are the questions. Grid congestion means a battery, a charging hub or a new building has to fit within the same connection, and with dynamic prices every quarter-hour counts. A yearly total does not answer those questions.

How do Excel and Wattix differ?

The table lists the main differences. The rest of the article explains them.

ExcelWattix
No metering dataBuild a profile yourselfPut together a profile in the Data Studio
Battery controlA rule per quarter-hour, in formulas you build yourselfOptimised within the limits of the connection
Battery sizingTrying sizes one by onePower and capacity calculated automatically
Assets per scenarioEvery asset is an extra column with extra conditionsBattery, solar, chargers, loads and generators together
Contracts and pricesModel them yourselfOptions for fixed, time-of-use or dynamic. Flex contracts are easy to set up too
Comparing scenariosA copy of the tab or file per variantScenarios side by side in one project
Report for the clientCharts and screenshots by handReport from a template, as a PDF
Knowledge of the modelIn the file, and often with the colleague who built itThe same method for everyone, every assumption visible in the scenario

Why does a spreadsheet break down on quarter-hour data?

A year has 35,040 quarter-hours: 365 days of 96 quarter-hours. That number of rows is no problem for Excel, since a worksheet holds over a million1. The problem is how the rows depend on each other.

Take a business with a contracted capacity of 500 kW, solar panels on the roof and a battery for peak shaving. What the battery can do in a quarter-hour depends on how full it is. And how full it is depends on every quarter-hour before. In Excel that becomes a chain of 35,040 formulas in which every row refers to the previous one. A rule like "discharge above 450 kW, charge from surplus solar" has to hold in every row, within the limits of power, capacity and efficiency.

You have to tune a rule like that yourself. Set the threshold too low and the battery is empty before the big peak arrives. Set it too high and the peak still gets through. Whether the battery is too small or the rule is wrong is then hard to tell.

Add chargers, a second roof of panels or a contract with different limits per time block, and every row gets more conditions. Every change affects the whole chain.

How does Wattix calculate a battery?

Wattix works with the same quarter-hour data, but you do not write a rule. Wattix optimises the battery's control and finds the dispatch with the lowest cost within the limits of the connection. So you see what the battery can do, regardless of how well a rule is tuned.

If you do not know yet how big the battery should be, Wattix calculates the required power and capacity in one go, instead of you trying sizes one by one. If no battery leaves enough room on the connection to absorb every peak, you see that too.

The result is the best achievable control for this data. An EMS in practice will not always match it fully, so leave some margin in your advice.

More on battery sizing with Wattix →

“
We used to combine Excel with a simulation tool for solar systems. It took a lot of time and we couldn't always figure it out, especially when energy storage also had to be factored in. With Wattix we can calculate generation and storage together, run multiple simulations, compare the outcomes, and have a well-founded recommendation in no time.
Jesse Moms · Kremer Installatietechniek
Kremer

How do you compare scenarios?

Advice is rarely one calculation. Your client wants to know what a battery delivers, what happens with extra chargers and whether a different contract is enough. In Excel every variant becomes a copy of the tab or the file. Change one assumption and you change it again in every copy.

In Wattix you set up the variants as scenarios in one project, on the same data, and compare the outcomes side by side. A scenario takes a few minutes on average, so you can add another variant at the client's table.

From metering data to report

You upload your client's quarter-hour data as an Excel or CSV file. If there is no metering data, for example for a new building or an expansion, you build a profile in the Data Studio.

At the end you turn the scenarios into a report for your client, from a template, and save it as a PDF. The charts come from the same calculation, so there is no copying and pasting in between. Read more about reporting in Wattix.

Who knows the model?

A good Excel model has often grown over years. It works because the colleague who built it knows exactly what each cell does. That is also the risk: when that colleague is away, the model is hard to hand over and an error is hard to find.

In Wattix you do not build a model, you set up a scenario. The calculation method is the same for everyone and every assumption is a setting in the scenario. So you can read a colleague's scenario without first taking formulas apart. And because projects belong to your organisation, you can pick up the work without anyone handing it over.

When should you move from Excel to Wattix?

If you calculate a simple case a few times a year, with one asset and a yearly total, Excel is fine and you do not need Wattix.

Wattix pays off when you regularly:

  • calculate several assets together within one connection, such as a battery with solar panels and chargers;
  • size a battery for peak shaving or grid congestion;
  • show a client scenarios or contracts side by side;
  • share the calculation work with colleagues.

Sources

  1. Microsoft, “Excel specifications and limits” ↩

Checked on

Frequently asked questions about energy simulation in Excel

Can you do an energy simulation in Excel?

Yes, for one asset and one scenario, or a yearly total of consumption and generation. Once a battery is added, every quarter-hour depends on how full the battery was in the one before. That becomes a chain of 35,040 formulas per year, and every extra asset or variant makes the chain bigger.

Can Excel handle a year of quarter-hour data?

Yes. A year is 35,040 quarter-hours, and an Excel worksheet holds over a million rows. The number of rows is not the problem. The problem is that the rows depend on each other once a battery is involved, and that you rebuild every variant by hand.

Can I use my metering data from Excel in Wattix?

Yes. You upload your client's quarter-hour data as an Excel or CSV file. Every row has to be one quarter-hour after the previous one, with no missing values. If there is no metering data, for example for a new building, you build a profile in the Data Studio.

Will an EMS in practice match the Wattix result?

Not always fully. Wattix calculates with the data of the whole period and returns the best achievable control for that data. An EMS in practice steers on forecasts, so leave some margin in your advice.

The grid is full.
Show what still fits.

In a demo you see how to get from metering data to a recommendation you can back up. Bring your own data and we look at your case right away.