The Guardian’s Randeep Ramesh reports today on the use of bed and breakfasts to house families beyond the legal time limit of six weeks.

The national picture is that half of the 242 authorities who responded had placed homeless families in private accommodation for more than 6 weeks since April 2010. But what’s your local picture?

A good first stop is your local authority’s expenditure above £500. To find this, try a search like ‘expenditure 500 site:bolton.gov.uk‘ – but replace the last bit with your own local authority’s website (excluding the www.).

Download it and open in Excel or Google Docs (if you need to convert it from PDF try pdftoexcelonline.com). Now it’s time to filter…

Filtering data to focus

Different authorities publish different details on their expenditure: how much is paid to who is a basic requirement; vendor ID is published by some, but not others (for example, Bristol). Date and invoice reference are useful. Directorates (different parts of the council are very helpful). Cost centres and codes even more so, as I’ll explain later.

I’ll assume you’ve only got vendor name and amount for now, and just the latest month’s data.

Open that spreadsheet up. We’re going to use the Advanced Filter feature to focus just on guesthouses.

The Advanced Filter uses a small table of filter criteria, which we’ll need to create first. To do that:

1. Copy the heading of the column you want to filter on – it’s likely to be ‘Vendor name’ or something similar.
2. At the top of your sheet, find the last column of your data, leave the first empty column after it alone, then paste that copied heading into a cell at the top of the second empty column.
3. Underneath that heading (i.e. the second cell in that column) type Guest house (including the asterisks)
4. Underneath that, type hotel (again, including asterisks)
5. Underneath that, type B&B Underneath that, type * breakfast* (including the space)
6. Underneath that, type * house* (including the space)
7. Once you’ve finished this mini-table of filters, you need to go back to the columns containing your original data. Click on any cell in those columns (this is so that Excel know which range of data you want to filter).
8. At the top of your screen, where the menu options are, click on Data > Advanced filter…
9. A window should appear with options for your filter. The first box should contain a cell range for the data you’re filtering. Leave that. The second box, however, asks for ‘criteria range’. Click in this box, and then on your spreadsheet click and drag to select the range of cells containing the table of criteria you just created. There are other options for filtering in a new sheet, and so on, but we don’t need them. Just click OK.

Your main table of data will now become filtered – it may not look very different but if you look at the row numbers you will notice that they don’t run in sequence – some are missing.

Likewise, the mini table of criteria might have ‘disappeared’. Don’t worry – it’s just been hidden for now.

But because the data is just hidden, not completely filtered out, we can’t do any calculations – yet. We need to copy this filtered data into a new sheet.

Copying into a new sheet

With your data still filtered, select all of it by holding down CTRL + A (on a PC) or CMD + A (on a Mac). Then copy it with CTRL + C (or CMD + C).

Create a new sheet by clicking on Insert > sheet > blank sheet (or similar).

Now paste your data onto the sheet with CTRL + V or select Edit > Paste.

With this data safely copied across, you can now remove the filter on your original data. To do that, go back to that sheet and select Data > clear filters.

Using pivot tables to get an overview of filtered data

Now, go to the new sheet containing only the data that met your filter criteria. We need to turn this into a simple list of companies, and then how much each of those received. We might also want to only look at those which were paid out of the housing budget, or a particular cost centre.

Pivot tables can do all of this. Here’s how:

1. Make sure you have selected a single cell somewhere in the data. This helps Excel work out which range of cells you want to get an overview for.
2. At the top of your screen, where the menu options are, click on Data > Pivot table (or pivot table report in some versions)
3. Depending on your version of Excel, either a new sheet will be automatically generated, or you’ll be presented with a new window containing options to fill in. If the former, ignore this step. If the latter, just click ‘OK‘ or ‘Finish‘ until you have a new sheet.
4. On the right of the screen will be a list of fields in your data, with tick boxes next to them; and below that, 4 boxes: filters, columns, rows, and values. If any of those boxes have anything in them already, click and drag them outside the boxes so they disappear, until your pivot table on the spreadsheet is empty.
5. Now to populate it. From the list of fields in the upper right area of the screen, click and drag your ‘Vendor name’ field into the ‘Rows’ box. Your pivot table should now change so that it shows a list of vendors – one row for each. Great: now you know how many companies there are that met the criteria – and there’ll probably be some that aren’t relevant. We’ll solve that soon.
6. From the list of fields in the upper right area of the screen, click and drag your ‘amount’ field into the ‘Values’ box. This box will now say ‘Sum of amount’ or ‘Count of amount’. If it’s ‘Count of‘ try to right-click on it (or click on the ‘i’ on Excel for Mac) and change the settings so it is using ‘Sum’ (‘Sum’ adds up all the values; ‘count’ just counts how many payments there were).
7. Now you should be able to see how much was paid to each company in total.

I said we’d sort out the companies we didn’t want here. There are a few ways to do this.

If your data contains information on the directorate or section of the authority which paid the money, then you can say you only want to see payments from the relevant one (e.g. housing).

To do this, from the list of fields in the upper right area of the screen, click and drag your ‘Directorate’ field into the ‘Filters’ box.

In the first row of your sheet you should now see the name of that new filter, and next to it a drop-down menu. Click on this, deselect all, then select the departments or directorates you want to see (i.e. housing).

If your data has cost centres or cost codes, and you know which ones relate to what you’re looking for, you can also use those in the same way (if you don’t know, try sending an FOI request asking for a list of those cost centre codes and what they refer to).

Once you do know cost centres and codes, of course, you don’t need the advanced filter – you can run a pivot table on the original data to find out what companies were paid under that cost code.

You can also filter the companies manually: on the pivot table itself, at the top of your row headings (where it says ‘Row Labels’), should be a drop-down menu where you can deselect companies that aren’t relevant. You’ll probably have to google them to find out what they do.

What’s interesting?

This data might tell you a range of things: perhaps a surprisingly large amount of money is spent overall, or on a particular company, or a small number of them. Perhaps you want to know how this has changed over time: has the bill for housing homeless people in B&Bs risen over time? Or had a particular peak, or trough?

For answers you may need to find out more about the companies: what hotels do they run? How many rooms do they have at what rates? How much would it cost to book them all out? Are there reviews or images on TripAdvisor or Google Streetview? Who are the directors, and what other companies do they hold positions at? How new are the companies?

You may need to merge more than one month’s data: one way of doing this is to use Google Fusion Tables’s ‘Merge’ option.

You can also search your local authority’s documents for any mention of those organisations, or the particular area of spending. (try doing a search with filetype:PDF site:wigan.gov.uk – your own authority URL instead of course – at the end to narrow the scope).

Knowing the term used to refer to the area of spending helps. This search, for example, brings up a link to the latest Homelessness Review Strategy document, which includes context like the aim to:

“Continue to make best use of temporary accommodation (TA), only using B&B accommodation in exceptional circumstances.

“… Reducing the use of expensive and inappropriate bed and breakfast accommodation through additional procurement from the private rented sector and the use of dispersed local authority properties – generally properties that are no longer being let on secure tenancies as a result of planned demolition.”

…and, on page 48 the ‘key performance indicators’ (KPIs) for that section including average stay in B&B for households with children.

A search on the phrase used there will lead you to the results of the Welfare Reform Inquiry, which notes “An increase in the use of bed and breakfast and temporary accommodation” as one of the concerns raised about the introduction of the benefit cap in April 2013.

You can also try to find the original FOI requests that sparked off the story. A search like this will get you examples like this, which you can always adapt.

And of course, you’ll want to seek context to the data: can the council explain the spending? If not, why not? Include their replies (or the fact that they wouldn’t). Would local homeless organisations or welfare charities be able to shed any light?

Even if you don’t take it any further, you’ll have gained some useful skills in filtering and pivoting data – let me know how you get on, I’m always happy to publish links and cross-posts on Help Me Investigate Welfare.