For Day 2’s challenge for Dashboard Week we see a familiar old friend (and foe), Excel Spreadsheets! Since joining The Data School Down Under, I’ve rarely found the need to open up a Excel Spreadsheet to organise data. When I was introduced to Alteryx, I never looked back to Excel for my data preparation needs. The best part about Dashboard Week so far is seeing how much I’ve grown and how much more there is for me to grow.
Check out my Day 2 Dashboard here: https://public.tableau.com/app/profile/kier.bituin/viz/WorldBankFMISFinancialActivityinAfrica/WorldBankFMISFinancialActivity
The Data for Day 2 – World Bank Financial Management Information Systems Projects (FMIS) and Financial Activity
We were provided an Excel file with multiple sheets (some cheekily hidden) about the World Bank’s 150 Bank-funded Financial Management Information Systems (FMIS) Projects. It took some time to read and understand the data and I explored the World Bank website to find out more. I looked at one of the aforementioned project’s details on the website to understand what these projects are about and I happen to stumble upon some interesting transaction data about the commitment the bank has made to the project, what they disbursed and what was repaid. Without any luck trying to web scrape each page through Alteryx, I had to do it the old fashioned way, download 47 Excel Spreadsheets for 47 different FMIS projects as I just wanted to focus on the African region for my analysis.
Data Preparation in Alteryx
With 47 Excel Spreadsheets, I wasn’t willing to import each of them one by one so I searched for some Alteryx macros and came across a macro that did the job perfectly where it could read every excel file in a folder. The rest of the data preparation wasn’t too special and I ended up copying and pasting the data I wanted from the original dataset with some tidy up in Alteryx.
Even though I thought of a story for the data quicker than Day 1’s challenge, I actually struggled more to think of how I could flaunt the data. One point that I wanted to show was to compare and show projects where the Disbursed amount exceed the Committed amount because that meant that the World Bank funded more than expected for the project so that and with a map to show the locations for each project made it simple but I felt like I needed to add more value to the dashboard.
The biggest challenge was that I wanted to show 3 different values for all projects without having too much clutter. Firstly, I knew I wanted to hide it and only show up when I filtered values on other charts and then that left a big blank space on my dashboard. As it currently stands, I decided on a breakdown by country with the total amount that the World Bank has Committed to each country.
Insights from the Day 2’s Viz:
It was a very challenging day today and I am proud of my efforts and here is a little about what I found with the data I had on 47 World Bank FMIS projects in Africa.
- 11 Projects in Africa had more Disbursed than Committed
- Nigeria had the most amount Committed to them
- 26 Projects already have been started to be repaid
- The World Bank’s has Disbursed 80.45% of what they had Committed to the projects
Time to recharge the batteries!
Thanks for reading my Day 2 blog for Dashboard week. Bring on Day 3!