So today marks the first day if dashboard week and the first of the of the blog posts with many yet to come. Of course, with the timing of all of this we thought it appropriate to start with some data about The Melbourne Cup to help with tomorrow’s bets. The first challenge faced for the day was getting the required data. This is because said data was stored online on a table meaning that it was download and regex time.

 

Because of this been a previous lesson from what feels like 20 years about now it was a rocky start trying to remember how to get the specific tables we wanted. Getting the URL and downloading it was the easy part of the challenge, the main issue faced was passing out everything between the two <tbody> nodes. Especially when it became apparent that there were more than one table located in the dataset. After a bit if trial and error I managed to extract all the data that was needed for one year (2012)

Because it was already done once I decided to turn this workflow into a simple iterative macro to run and automatically pull the next few years if data. This was easy enough to do but then we faced the next issue, changing scheme. This meant that because of the change between tables we couldn’t use the same Marco for all years. It worked up until 2019 with only a few fields changing columns but 2020 and 2021 introduced new columns and layouts. To get past this I stopped the iteration when it was 2019 and manual passed out 2020 and 2021. After a bit of data cleaning, it was ready to go!

 

 

For the tableau side of this it was a bit harder to figure out what I wanted to do. Because the data was still pretty limited, I wanted to add some complimentary data on jockey stats, race conditions and a few other bits of information. I then made a small dashboard about each of these points and used navigation buttons to move between the new dashboards.

When finalized I hoped to have more done but to fix it up, I’ll add more functionality and interaction between the pages or try to get one layout on a large dashboard.

 

The true race this time was the race against time!!

Stephen Hughes
Author: Stephen Hughes