So a couple of things before I start

1. I have the flu and went home at lunch time today as it doesn’t feel great. Therefore I feel like my brain has been working at 60% capacity (getting my excuses out there). When I got home, I’ve kept working on this as I wanted to complete the first day of dashboard week
2. A disadvantage of going home is that it’s harder to get help and get a 2nd opinion when I’ve run into problems.
3. I’m writing this at 4:45, so it may be quite brief

But now that I’ve made my excuses, here is what I did

First, I tried to find the links to the 391 data sources. This was quite frustrating as I kept getting more results than the 391 and was struggling to find out why. This took me until midday to figure out after some very appreciated help from Jonathan to understand why (on the surface) it looked like we were doing the same thing but with me not getting the right result.

It turned out I was using the wrong ‘next’ link in the html. But once that was fixed, I went back home feeling sorry for myself and not looking forward to trying to get everything done ill, at home after spending so much time on a small problem.

Next, I created a batch macro to pull out all the csv files

This went quite well and made up some of my lost time (after having a fight with regex)

Now that I had the csv files, I limited my results to csvs for 2017 and 2018, found the trust name from the files, created filepath and file names so I could download the csvs and used a download tool to do this.

After downloading the files, I found out which files were broken and filtered them out. Now I looked at the files, and while they were in different formats and with different data, they seemed to have some common fields (even if they were named differently). These were: a ‘date’ for the payment, ‘Expense Type’, ‘Expense Area’, ‘supplier’ and ‘Amount Paid’

So I created a batch macro to find, select and rename these fields using another batch macro

From this, I could tidy a couple of things up and get a data source I could use. Then it was a matter of playing with tableau and finding something interesting. Unfortunately, it was 4:15 by this point and still needed to write a blog post as well as come up with a dashboard!

There was 5 trusts that I had reasonable coverage of data so I used these and found that Leicestershire Partnership Trust had a huge % increase of payments after Jan and Feb 2017. So I focused on them, using the other 4 for some limited context (but you can’t read much into this without understanding why there has been a change – it’s not my best work at all)