Overview
A Bubble Pie chart overlays mini pie charts on top of a bubble chart, showing both the size of each market and its composition across sectors (Agriculture, Industrial, Services) in a single visual. This technique, originally developed by Andy Pope, is implemented here with VBA macros and Excel's Python integration for chart rendering.
Download the Excel file (.xlsm)
How It Works
- Data Structure: 7 markets (A–G) with X/Y coordinates, market size, and three sector breakdowns (Agriculture, Industrial, Services).
- Bubble Positioning: X and Y values position each market on the chart; the Market Size value controls bubble diameter.
- Pie Segments: Each bubble is replaced with a proportional pie chart showing the sector mix. Sector percentages are calculated as a share of each market's total.
- 9 Embedded Charts: The workbook contains 9 chart objects that compose the final Bubble Pies visualisation.
- Python Integration: Uses Excel's
PY()function for library loading and image generation.
Sample Data
| Market | X | Y | Size | Agriculture | Industrial | Services |
|---|---|---|---|---|---|---|
| A | 1 | 5 | 5 | 484 | 448 | 286 |
| B | 3 | 10 | 3 | 437 | 177 | 107 |
| C | 8 | 5 | 5 | 231 | 363 | 111 |
| D | 6 | 6 | 3 | 108 | 293 | 308 |
| E | 2 | 3 | 1 | 449 | 409 | 491 |
| F | 3 | 2 | 1 | 382 | 458 | 480 |
| G | 4 | 4 | 5 | 425 | 150 | 379 |
When to Use This
Bubble Pies are ideal for executive presentations where you need to show market positioning (X/Y), relative scale (bubble size), and composition (pie segments) simultaneously. Common use cases include market analysis, portfolio comparison, and competitive landscape visualisation.