A niche topic this one as I look at how it’s possible to source large amounts of data relating to the UK power market and system from National Grid using the Elexon API. I also look at how it’s possible to take an XML feed and create a dynamic Pandas Dataframe as I couldn’t find anything helpful online as to how to do this. I’ve created some custom code and share this below too.

The video above explains the API, the structure of the data objects and walks us through the Python code.

Transcript

The balancing mechanism and reporting service – BMRS – is the primary mechanism by which National Grid informs utilities, trading entities and interested parties about the UK power system. It’s used extensively by the market to make trading decisions, to understand system dynamics and also as a reporting platform.

Historically, the data was available through a high-grade data feed which provided near-real time updates, but was not simple to set up and expensive, or through a free service on www.bmreports.com which was delayed data but easier to access.

Moves to open up the market and to be more transparent lead to the release in December 2014 of a set of Application Programming Interfaces (API) enabled market participants to receive BMRS data easily and securely.

Initially only REMIT data was available but over the last year – and progressing through 2016 – a full set of data is being made available. Thus, it is possible to find out physical performance of power stations, demand response, the prices and volumes traded be national grid, BOA actions, along with data relating the wider market such as wind, weather and inter-connector flows. In short, everything you’d need to start trading the UK market or analyse its behavior.

To access this data we need to set up a free account with Elexon which will generate an API key. Using this key we can start writing some code to access UK power market data. The general form of the API is as follows:

APIKey

 

We generate a URL that contains our API key and any other parameters – BMU, date, period etc – that the database requires, send the URL and process the response. The response can be in an XML or CSV format.

As we can see from the PDF guide, there is a vast array of data that we can source. In fact, the user guide is well over 250 pages long and along with the individual guides does contain example code for a number of programming languages. We’re going to look at sourcing UK Temperature data as a short example.

First page of the Elexon API guide
First page of the Elexon API guide

XML

The API returns an XML object with the data bound by the ITEM tags; this child data varies per report – Temperature, Price, Wind etc – but the overall hierarchy remains the same. Using this structure we use XML XPATH to navigate to this tag, run one extract to build the tags and another to extract out the values of the tags.

This allows us to have one single code block/function but to be able to extract almost all the API data. We simply pass in different parameters

Elexon API - XML object
Elexon API – XML object

Code

[code language=”python”]

import urllib2
import pandas as pd
from lxml import objectify

from collections import OrderedDict


def BMRS_GetXML(**kwargs):
    '''BMRS_XMLGet(api=**YOUR-API-KEY-HERE**, report='PHYBMDATA', sd='2016-01-26', sp=3,
    bmu='T_COTPS-1',bmutype='T', leadpartyname = 'AES New Energy Limited',ngcbmuname='EAS-ASP01')'''

    url = 'https://api.bmreports.com/BMRS/{report}/v1?APIKey=
api=**YOUR-API-KEY-HERE**
&ServiceType=xml'.format(**kwargs)

 for key, value in kwargs.iteritems():
 if key not in ['report']:
 a = "&%s=%s" % (key, value)
 url = url + a
 xml = objectify.parse(urllib2.urlopen(url))
 return xml


def BMRS_Dataframe(**kwargs):
 '''Takes the sourced XML file produces a dataframe from all the children of the ITEM tag'''
 tags = []
 output = []

 for root in BMRS_GetXML(**kwargs).findall("./responseBody/responseList/item/"):
 tags.append(root.tag)

 tag = OrderedDict((x, 1) for x in tags).keys()
 df = pd.DataFrame(columns=tag)

 for root in BMRS_GetXML(**kwargs).findall("./responseBody/responseList/item"):
 data = root.getchildren()
 output.append(data)
 df = pd.DataFrame(output, columns=tag)
 return df

BMRS_Dataframe(report='TEMP', FromDate='2016-01-01', ToDate='2016-01-20').to_clipboard()


[/code]

 

7 COMMENTS

  1. Hi Patrick

    Enjoying your blog – really interesting articles and content.
    On the above python script: I am now learning python based on the above prompt and have the elexon account setup. Please could you send the full .py file with the above python script? I am struggling to run the script based on copy and paste from the above.

    Thanks

    David

    • Hi David – I’ve been rather busy the last couple of weeks but will certainly look to send this through in the next few days. Feel free to chase me up if I haven’t!

  2. Hi Patrick
    have debugged the code and it is a version difference – I am on 3.5 and there were a couple of code changes to move from your version to 3.5 – code now works fine

    Thanks

    David

  3. Hi Patrick – do you have a script for FPN\MEL? I have looked at the API guide and your code but cannot get a solution – I can see the reference to PHYBMDATA in your code but that is commented out. I am trying to get one years data and figure that a function that takes a settlement date and then outputs the data for each sd will work. Thank you for any help David

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