Tag Archives: Finance

Making use of Open Data

Darllenwch y flogbost yn Gymraeg

The Wales Audit Office recently released our first Open Dataset. What happened next? Ben Proctor of Open Data Institute Cardiff talks us through how he made use of the data.

A screenshot of a dynamic map created by Ben Proctor to show levels of Council Tax per head of population in Wales

A screenshot of a dynamic map created by Ben Proctor to show levels of Council Tax per head of population in Wales

Oooo! new data

I was excited to see that the Wales Audit Office had released a set of data as open data. Open data is data that anyone can find access and use and it is the most useful sort of data.

Dyfrig Williams wrote about the process they’d gone through to release this data set (a summary of the audit data from each local authority in Wales for each year). The data is a simple table and you can download it as a CSV file (essentially a file that will work in any spreadsheet programme) here.

But there are problems

I downloaded the file and quickly spotted some problems. These are not errors exactly but just things that are missing or inconsistent and will make some uses of the data a bit harder. But this is not a complaint, because one of the attractive features of open data is that I could resolve these problems. I can do this because the Wales Audit Office have released the data under the Open Government Licence. This tells me I don’t need their permission to do anything with the data and there are no limits to what I can do with it (apart from I have to make it clear where it came from).

I can fix the problems

These are the things I did to my copy of the data.

I changed the format of the “financial year column” because in the Wales Audit Office file some of these are numbers and some are text.

I added a column of GSS codes. GSS codes are codes that are used to identify local authorities (and other boundaries). Having the GSS code means you don’t have to worry about whether the data says Anglesey Council, or Isle of Anglesey Council or Ynys Môn. And with the GSS code I could add “polygons” for each council. Polygons are basically instructions on how to draw the outline of each council and information about where to put the drawing on a map.

With these changes I was able to draw a series of maps showing the level of council tax per head in each local authority and how this has changed over time.

And given the Wales Audit Office an improved file

And I’ve been able to hand back to the Wales Audit Office a KML file. This is a file suitable for use in mapping software. Anyone who wants to visualise the Wales Audit Office data on a map can just open the KML file and get going.

You can download this mapping file yourself.

Why did I do this?

I’m part of the core team at ODI-Cardiff so I get excited about open data.
It took me a very few minutes.
I’m trying to get better at using a Google service called Fusion Tables and this is a good opportunity to experiment.
I’m actually quite interested in what this data might tell us.

Releasing Open Data

Darllenwch y flogbost yn Gymraeg

How can organisations release Open Data? Dyfrig Williams looks at the process of making the data behind the Wales Audit Office’s Local Government Financial Statements report freely available, and shares the dataset at the bottom of this post.

I’ve previously blogged on how the Wales Audit Office is looking to challenge our existing use of data and technology as part of the Cutting Edge Audit project. My role on the project has been to look at how we acquire data.

How does Open Data fit into acquiring data?

The below diagram shows the rationale behind my work. For me, we need to share the data that we have in order to develop our relationships with our client bodies so that we can gather data effectively. Part of this is about “being the change that you want to see.” Auditors are sometimes seen as being risk averse, but in the Good Practice Exchange we’ve seen that when we work differently, we enable others to do the same. A number of local authorities have reported back to us about how they’ve been able to challenge the limitations of the websites that they’re able to visit and the social media that they can access because of how we share knowledge. By making data openly available, we can demonstrate that there is little risk, as long as the process is well managed.

A cycle illustrating that by open data results from how we share data and how it feeds into acquiring data

As I mentioned in my original post, finding an appropriate dataset was more challenging than I thought that it might be. As we often don’t have the right to share the data that we collect from clients during our audits. However after a bit of research, we found the data behind the Local Government Financial Statements report, which is a report on local government bodies’ accounts. This was safe data to release because it’s already available on each council’s website as part of their accounts, but we are the only organisation that collates this data. The data within the report is analysed on a national basis, but by releasing the dataset we can enable councils and other interested stakeholders to look at the data on a county by county basis and to compare and contrast their accounts against others. The data is used by the Wales Audit Office to support local audit work and for general benchmarking. The report itself looks at the quality of accounts, and is based on the data that’s released before amendments – we don’t keep track of prior-period adjustments.

How did we go about making this data open?

Our starting point was a spreadsheet that we use internally that contains the datasets dating back to 2008-09. We were a bit disappointed to learn that the requirements for local authorities to provide this data in this structure has now changed, so there won’t be comparable data available next year. However, this dataset served as a good test for a future approach. In the longer term it would be worth us looking at how we could make continuous data available in order to reduce the burden of reporting requirements. Lucy Knight from Devon County Council has a really useful example that we can draw on in her lunchtime lecture for the Open Data Institute on Making open data happen in local government

We used Hendrik Grothuis’s post on making data open and the Open Data Institute’s Consumers Checklist as rough guides for the process. Our first step in cleaning up the data was to look at which data was ours to share, and which data was already available from other sources. We decided to remove the data that was already made available through StatsWales to avoid duplication, but should you want to think about using this dataset with some of the ones that we used internally, these may provide a good starting point:

We then used CSV Lint to check whether the file was readable. We were pleased to discover that we had a valid file, but we also found ways that we could improve it. We turned the dataset around so that the data items go horizontally and the years go vertically. We also created a null value to indicate where the data was unavailable. A quick Google search was enough for us to discover how to note empty cells.

As a Welsh public sector organisation, we are required to make the data available bilingually, so we sent it to the translators to make sure that we got each technical term exactly right.

Publishing the data

When it came to publishing the data, we decided to publish it as part of this post on the Good Practice Exchange blog. It would be a very lonely looking dataset on an Open Data platform at the moment, but the hope is that we can identify other datasets that we can release going forward. We looked at potential platforms that could be used, including open source options like CKAN and DKAN (both of which would integrate with our Drupal Content Management System), as well as cloud based platforms like Socrata. As an organisation we’re moving to the cloud when it makes sense, but there may be things that we could learn from Audit Scotland’s Innovation Zone, which has been set up to allow their staff to test new software and platforms in a lightly regulated space. This gives staff the opportunity to test new ways of working.

As per our recent webinar on Open Standards, we’ve chosen to publish the data in CSV instead of a proprietary format like Excel. This means that it can be used by a wide variety of software, and hopefully as wide a variety of people as possible.

It’s now up to us to ensure that this data is discoverable by tagging it effectively, and we will also publicise the dataset through the networks that we’ve built through our prior work on Open Data. Our next challenge is to track how the data is used, so if you do use the dataset, we’d love to have your feedback about the format and what you used it for.

Learning from the Welsh Government

The Welsh Government were a great help throughout my work on the Cutting Edge Audit project. They shared learning from their approaches, and we also attended meetings together to learn more about Cardiff and Monmouthshire Councils’ approaches. It was fascinating to hear that the Welsh Government’s own staff use StatsWales to share and gather data as it’s open and transparent. This is something for us to think about in our own journey forward – how we can make data more accessible for both internal and external stakeholders.

We ended up using the Welsh Government’s approach to Metadata as a template for our own work. Metadata is a set of data that describes and gives information about other data, and it’s really important because it gives context around the data that is being shared. You can find the metadata at the bottom of this post alongside a link to the data itself.


Your feedback on our approach here is really important. As this is an initial test of how we might make data open and shareable, your feedback will be used to shape how this might progress. As an organisation, we’re very keen to look at how we can make better use of data to help public services improve, and also to walk the talk in terms of our own digital practice. The Auditor General for Wales talks about enabling innovation through well managed risks before every one of our shared learning events. We’re looking to share our own learning so that people can learn from our experiences, be they good or bad. We always say that there’s no point reinventing the wheel. By working openly and transparently, we hope that organisations can build on what we’re doing so that they can share data as effectively as possible in order to improve the services that they provide.

Dataset: Local Government Financial Statements


Title Data from Local Government Financial Statements 2008-09 to 2015-16
Last update 01/04/2017
Next update No longer updated.
Publishing organisation Wales Audit Office
Source Local Government Financial Statements 2008-09 to 2015-16
Contact email good.practice@audit.wales
Lowest level of geographical disaggregation Local authorities
Geographical coverage Local authorities
Languages covered English and Welsh
Data licensing You may use and re-use this data free of charge in any format or medium, under the terms of the Open Government License – see http://www.nationalarchives.gov.uk/doc/open-government-licence


Accounting against the Clock

Faster Closure of accounts in Wales – are we all up for the challenge?


The Good Practice Exchange will shortly be holding two Faster Closing seminars in Cardiff and North Wales. John Herniman from our Senior Leadership Team shares his views on Faster Closing and what it means for Wales.

We’ve heard a lot in recent months about the faster closing of accounts. In England, both Oldham and Westminster Councils beat their previous records for closing their accounts, doing so by the end of May. Closer to home, Torfaen County Borough Council closed its 2014/15 accounts in record time, 10 weeks ahead of the deadline. There is also the continued pressure to speed up the publication of the annual Whole of Government Accounts into which Local Government bodies accounts are consolidated.

As we all know, the current deadlines for the production and audit of Local Government bodies’ accounts are 30 June and 30 September respectively. The Welsh Government has recently consulted on bringing these dates forward to 31 May and 31 July over the next few years. Whilst the earlier deadlines may seem like a distant challenge not to be concerned about just yet, the scale of the changes required for practitioners and auditors alike means that planning needs to start now.

What are the benefits?

As daunting as it might seem, we know that there are many benefits of faster closure. These include, but are not limited to, improving the timeliness of reporting to stakeholders and having earlier assurance over the previous year’s position before embarking on major financial decisions for the future. The private sector and other parts of the public sector all close their accounts earlier so the question for me is ‘Where do we start?’

Faster Closure in Wales

Our Good Practice Exchange seminars in October and November will give us insight into how particular organisations started their journey. These seminars are not going to be focused on detailed processes but rather the organisational and cultural changes needed to start the journey. Further seminars and workshops will be held over the coming years exploring the more detailed aspects of faster closing.

The organisations involved will share with us their approaches and most importantly, the lessons learnt from achieving earlier closure of accounts. For me personally, hearing about the challenges that they faced at the early stages and how they overcame them will be of particular interest

A new challenge

There is no doubt that the faster closing agenda will bring major challenges for both practitioners and auditors as we work together to develop and learn from new ways of working. I remember the last time the closure dates were brought forward, from the end of December to September. Back then that sounded impossible but it quickly became the norm with the tighter deadlines being achieved.

Faster closure is a learning curve for all of us; insights from Torfaen, Oldham, Kent and Westminster will provide us all with a chance to be working from the same page so that we have a good starting point as we embark on their faster closing journey.

From Financial Audit to Good Practice Exchange

Michelle Davies has recently been working with us at the Good Practice Exchange. It’s been great to have her on board, and we’ve been learning from her about her work and vice versa. In this blog Michelle tells us about her experiences of working with us.

Michelle DaviesI’ve recently completed a Good Practice project where I helped deliver the Facing Financial Challenges Webinar (which you can watch in full on Vimeo). For those of you who don’t know me, I’m normally found working in the Mid and West Wales Financial Audit Cluster working on the exciting stuff – Local Government and Health Accounts!

So, Why I did I want to get involved?

I was both curious and ignorant as to what went on with the Good Practice Exchange Team, what do they do and how do they do it, what circles do they move in, what are their goals in our organisation? After working in Financial Audit in the West for over 10 years, I was desperate for a change of scenery. Getting out and about more, even working in the Cardiff office was a change of scenery.

I learnt loads of new skills, from different approaches to researching topics which I had little or no knowledge of at all. And as for social media, well it would be safe to say I am a little rusty around the edges; I needed to move with the times and the promise of being taught how to ‘tweet’ was appealing. Don’t you just love it when people say “it’s easy?”

I guess to work differently; sometimes you have to work with different people. Although a little daunting at first, you soon learn personalities and expectations. You can learn so much from other people, a kind of inner confidence starts to grow as you bounce ideas off colleagues and share their experiences and excitement.

What I learnt

Firstly I had to research the subject area, surfing the web, trawling through the current relevant reports, any conferences, seminars, identify potential speakers and produce a scoping document to present with my findings and identify the key themes for discussion.

Armed with my research material, I sat and discussed my findings with Ena and Anthony Barrett to agree on a punchier title and to identify speakers that were able to discuss current, relevant good practice identified within the public sector. I learnt the importance of does the title say what it does on the tin?

I held my first speakers briefing with Guy Clifton from Grant Thornton over the telephone, explaining the webinar structure, format and timings and taking away additional housekeeping issues he had for the Good Practice Exchange Team to confirm.

Social Media, i.e. twitter schedules and live tweeting
I had a lesson in tweeting and was asked to tweet live at a few seminars, helping the Good Practice Exchange Team when they were short staffed or at a larger event where there were several workshops running at the same time. A lesson in tweeting, was as I discovered, not always going to ensure it goes smoothly on the day!

Confidence in getting it done
Ummmm, what do I do now, what do I do next, am I doing this right, all my worries and part of the learning curve. Ena would smile at me and say, what do you think, tell me what you feel, what is your gut instinct? Now go and do it!

The Good Practice Exchange Team use a different way of working, they need to be ambidextrous within the team. You learn to work backwards. The team know what they want to achieve, so how do they get there, their focus is on impact.

The good, the bad and the ugly

I’ll do the ugly first – helping on an external seminar specifically to tweet live, I connected to the wifi, ok, sitting at the front of the audience listening to Huw Vaughan Thomas deliver his opening speech – I’m connected but hey – no internet! No matter what I tried it just wouldn’t work! At the end of the opening speech I managed to get Dyfrig’s attention and he miraculously connected my machine, phew! To play catch up I started copying and pasting from a twitter schedule – it wouldn’t work! It took about 15 attempts and a rising temperature before I realised that there were too many characters! Ok – no stopping me now…. except every photo I took of the speakers to upload with their key messages would appear upside down! No photos then!

The bad – well actually I don’t have any bad experiences, so far they have all been good or ugly, and even the ugly one is funny now. You learn from your experiences and I have, tweeting live at the webinar last week went well – phew!

The Good – I have thoroughly enjoyed my experiences, I have learnt a new set of skills and gained an inner confidence and passion that was missing. I have been fortunate in meeting many people from all walks of life, with a range of skills and personalities, some impressive, some humbling, some inspiring, and some eager and switched on.

What I would say to anyone else who wants to get involved

Do it! – You won’t regret it and if you have any concerns and worries I can help you overcome them. The sharing of information this way is important to the future of both our organisation and public services.

Prevention: What’s the Reality of Shifting your Resources?

Bromford's preventative approach

Bromford’s preventative approach

The term ‘prevention’ is used increasingly freely in public services at the moment. But what is the reality of shifting the focus of services away from reaction and towards prevention? We worked with the Welsh Government (the Public Service Leadership Group’s Effective Services for Vulnerable Groups programme) to bring together some practical examples of how organisations have approached this ‘prevention’ agenda in the form of a seminar. We also wanted get the people working to deliver public services into a room and ask them what their own experiences were.

I’d like to share some of the delegate’s positive experiences of striving towards prevention and also what they’re struggling with on a day-to-day basis.

Firstly, there are some good examples of public services looking beyond their own specific areas of expertise. The Fire and Rescue Services are a clear example, when they have taken a holistic view of people’s wellbeing within their homes. We also heard from Cardiff Alcohol Treatment Centre, where diverse sectors (such as the Students’ Union, the Council licensing department and health bodies) are all benefiting from one project. Public Health Wales also discussed their work in connecting General Practices to community assets for health and wellbeing, for instance by ensuring a community health worker is based in GP surgeries.

There are also some excellent opportunities within the role of voluntary sector. Delegates emphasised the role they can play in taking over some responsibility from public sector for prevention, and the additional resources they can provide for specialist preventative work. A WCVA representative discussed the early intervention work that the sector is already doing in communities. However, they felt that many people don’t know how much they can actually offer and that there is much more scope to involve the voluntary sector not as a ‘third’ sector but as an integral part of community preventative services.

Finally, there are examples of organisations willing to take well-managed risks and adapt new approaches as they go. Isle of Anglesey Council discussed their work on transforming children services, moving from safe certainty to safe uncertainty. As Bromford have done, Cartrefi Conwy discussed work on tenant profiling, to move away from one-size-fits-all approach and adapt to each client.  We heard examples of how very small shifts in resources such as these, combined with a willingness to launch imperfect services and adapt as necessary, can have big effect on developing preventative services.

However, we also discussed a range of challenges that delegates experiences in their day-to-day roles. The first and perhaps most expected of these was funding. In particular, how to strike a balance between funding statutory services and funding innovative prevention approaches. Moreover, if prevention activities are successful, there is often an expectation that expenditure will lessen over time. But these resources are still needed to maintain prevention work.

Secondly, how do you demonstrate the benefits of preventative work? How do you demonstrate something that you stopped from happening? This was a particular challenge for delegates when the financial or other outcomes of their prevention activities are by others.

You can see all the presentations on our website, as well as some more of the key ideas that were raised during the day.