Background

In an earlier post (link here) I’d talked about some of the more confusing elements of the UK Power day and then talked about some of the terminology surrounding the EFA day (video). Having a few moments spare – and always a fan of throwing a function or two together – I thought I’d write some example code of how to calculate the current EFA period for any point during the day.

The lowest granularity that electricity is traded in the UK is half an hour with products evolving outwards and upwards to annual tenors. Calculating the start and end periods of intraday products, whilst not too cerebrally challenging, is a fine candidate for automation and allows for a nice introduction to writing function in VBA and Python. However, let’s kick things off with Excel.

Doing it in an Excel formula

If you’ve seen the earlier posts you’ll know that, with the exception of clock change days, the UK power day is split into 48 periods of thirty minutes each. This regularity makes it a pretty easy calculation to do in Excel. To be honest, as with almost all things Excel, the problem is often not how to do something but the utter myriad of ways you can do something.

First off, if we were doing this calculation mentally, we take the current hour and double it, then check to see if we were past the half hour. If we were then we would add on an extra 1 to account for that period. Starting things off, this can be represented as an Excel formula as follows and we’ll assume that cell A1 contains a date and time.

[sourcecode language=”vb”]
=(HOUR(A1)*2) + IF(MINUTE(A1)>=30,1,0)
[/sourcecode]

Personally, I prefer to wrap this expression up into an IF statement as I believe it’s a little more robust. In that case, the formula we’d use would resemble the following.

[sourcecode language=”vb”]
=IF(MINUTE(A1)>30,(HOUR(A1)+0.5)*2,HOUR(A1)*2)
[/sourcecode]

Doing it in an Excel function

Excel formula are fantastically flexible, easy to construct and usually simple to understand. For small tasks they can be ideal, however, they can also dramatically slow a workbook down and may add a lot of bloat. We need not delve into add-ins, library’s and function optimisation at this stage but we can replicate the formulaic approach with our own function. Why would we want to do this? The main benefits I find for functions in Excel come from the options they give to solving problems and their ability to speed up calculations.

Imagine a situation where we didn’t know if a cell would have a date/time or if it would be blank or not. If i contained a date/time then we could use one of the examples about to return the period and if it didn’t then we’d want to calculate the current period. We could write this in a formula but it would be rather an ungainly fellow:

[sourcecode language=”vb” wraplines=”true”]
=IF(ISBLANK(A1),IF(MINUTE(NOW())>30,(HOUR(NOW())+0.5)*2,HOUR(NOW())*2),IF(MINUTE(A1)>30,(HOUR(A1)+0.5)*2,HOUR(A1)*2))
[/sourcecode]

The function I present below has two benefits therefore; it’s quicker to run and much easier to understand/debug at a later date. To use the function, simply copy the text into a new VBA module and call it as =HHPeriod() in Excel. If you select a cell containing a date/time then the corresponding half hour period will be return. If you don’t pass such an argument to the function, it will simply calculate the current period.

[sourcecode language=”vb”]
Public Function HHPeriod(Optional Date1 As Variant) As Integer

If IsMissing(Date1) Then
HHPeriod = Round(Time() * 48, 0)
Else
HHPeriod = Round(TimeValue(Date1) * 48, 0)
End If

End Function
[/sourcecode]

Wrapping it all up

If you arrived here searching for an answer about how to calculate the current half hour period then I’ve answered your question. Excel’s formulae make construction and evaluation of potentially complex calculations relatively simple. However, they also add a lot of fat to the spreadsheet. On the flip side, reconstructing the same set of arguments within your own function can clean up this process considerably.

Caution should be take before rushing to convert everything into functions; their results are usually much hard to decompile since they typically return a single value. A formula has the benefit that its wears are all on display and thus easy to audit

How to Video

I'd love to hear what your thoughts are...please feel free to leave a reply