We’ve made a few changes to the way we process your data recently (skip over the detail – it’s all about “back ends” and stuff), which has meant we’ve changed a few things about the spreadsheet we send back out to you.  The spreadsheet now has lots of tabs on it – this is your cribsheet to understand what they all are and (importantly) whether or not you need to spend time on them.

places

This is a sheet that sets out who currently has data in the PQF. You can use it to pick your comparators. You want to choose people with recent submissions (column ‘D’) and high success rates (column ‘F’). You can also find contact details if you want to ask your peers something. We don’t need to mediate all these conversations.

applications

This is the sheet you sent in, plain and simple.

date_errors

This sheet should be empty. It contains any records with date errors – eg applications determined before they were received. Any records on this sheet should be fixed on your system (often we see problems with mis-typed years) so that they are fixed forever. If you just fix them in your applications sheet, the next time you do a download the problem reappears.

app_duplicates

This sheet should be empty. It contains any records that have the same application reference. It’s not clear to us why it happens, but some people are running reports that pick up refunds (or partial refunds) as two separate records.

lookups

These are the lookups that are needed when we look at your data. You should be able to recreate the lookup table by using a pivot table on the appropriate column from your applications data if you want to check our workings.

A lookup is matched and happy if there is a value in column ‘D’ (system_value). You’ll see some extra values in columns F to I that we use to generate some of the graphs and things – you can safely ignore them.

You’ll need to populate the system_value from the master_lookups tab. I find it easiest to pick one set at a time.

lookups_wrong

This sheet should be blank. If you provide lookups that were not recognised by the system they are dumped into this sheet. Sometimes we change how we deal with some issue (eg we changed how we think about blank values recently) and so you’ll see a bunch of lookups appear. Sorry. If you understand why they are there you can just ignore them.

lookups_spare

This is a sheet that will show you lookups that you presented that were not matched in your applications data. Often this happens because people know that there are potential matches but some of the more unusual combinations of development code / application type are not present in your application data.

However, more usefully, it can be useful as a diagnostic. If you submit a bunch of lookups but your data comes back as being largely unmatched you might find all of your lookups, pain-stakingly assembled, dumped in lookups_spare. This is when you have to carefully work out why there are differences – the lookup must be an *exact* match, including spaces, capitals – the whole lot.

master_lookups

This is the master set of the lookups that we recognise. It is much easier to use if you filter by column A (‘list’) and resize the columns.

everyones_lookups

Actually, many people are using very similar coding systems. If you want to copy or crib you can use this sheet. It is surprisingly effective at spotting problems or common patterns (although it may be because we’ve spent too long looking at this sort of stuff).