Further adventures in superannuation land

Published in

Prosperity Advisers DnA

10 min readMar 28, 2020

Disclaimer: The details in this article do not constitute investment advice. The data pulled from APRA is publicly available information and the work to visualise this data was done by joining various fund-level tables together linked based on Fund details and dates. If you are after financial advice then see our range of services here: http://www.prosperityadvisers.com.au/services/personal-wealth-and-insurance

Introduction

If you’re like me when you’re bored, you tinker. According to Google, the definition of tinker/tinkering goes along the lines of an ‘attempt to repair or improve something in a casual or desultory way.’ For some people, they might tinker with gardens or home improvements. For others it might be working on their cars, boats or bikes. For me, my passion (and how I make a living) is in analytics. So when I tinker, it’s going to include data, visualisations and, in this case, continuing with the superannuation theme.

In the last week of 2019 I published an article and application showing my attempt at tinkering with APRA’s MySuper heatmap datasets. I’d heard about their push towards better data transparency in that space when they were announced earlier in the year and saw it finally released on December 10. The heatmap file (in .XLS format) was useful but I thought it could be made better. You can see that app and accompanying article HERE.

I’m all for transparency and I applaud the efforts of any organisation that puts more information (free or paid) out there for consumption by others because I believe that this can lead us towards smarter and more data-driven decisions. I built that original app on the MySuper dataset because that’s what I saw available but after further digging I found some more interesting superannuation data from APRA so decided to have a go at tinkering some more.

What I saw was that APRA produces annual fund-level superannuation statistics (see HERE) and, interestingly, these were also published the same day (December 10, 2019) as the aforementioned MySuper Heatmap data. Because this is annual data, it’s unfortunate that the information isn’t going to be updated until December 2020 but there are still a lot of insights that can be gained from making this data more easily consumable through analytics.

I focused on the back-series dataset which showed yearly historical data as far back as 2014 and up to June 2019. Some of the tables had more history than others but I stuck to just the last 5 years for comparative purposes. Like the MySuper data, this was in an Excel spreadsheet and for anyone who’s worked with data like this (most of us), they’ll know that whilst these can be filled with insights, it can often be time consuming to get to them. Things are no different here as the data is spread across numerous tables but there’s a lot of interesting insights in here to do with fund returns, rollovers, investment income and demographic info of fund participants (plus many more).

Luckily for us, we have access to reporting tools like Microsoft Power BI which can be used to extract and transform these tables of information and allowed me to produce an interactive dashboard which I’ve now made available for others to also use.

The Annual Superannuation Statistics App

Dashboard

Firstly, you can access the dashboard HERE. There is also an embed code at the end of this article should you wish to share it in your own blog/site.

Unlike the menu option that the user is greeted with in the MySuper app I built, I decided on a different route for this app. Many business users have seen dashboards that their organisation uses. The best of these are often produced in a way that provides a snapshot of what is going on across the various facets of a business (i.e. the main dashboard page) and gives users the ability to drill into further insights about a specific area should they wish to explore it (i.e. via detailed sub-pages). This dashboard is designed keeping this approach in mind.

From the main screen the user is able to pick 3 things. A FUND TYPE, FUND NAME or PERIOD. In the example below, the user is able to select different types of funds and can see how the various snapshot tiles (covering various areas of the data) have their statistics/charts change.

Animation 1: Showing user selecting different Fund Types from the Dashboard menu

From here, the user can explore further by clicking on 1 of the 6 interactive tiles on the screen. These tiles represent different areas of this superannuation dataset that I thought were interesting and there are many more areas that could be explored. For now the app includes the following focus areas.

Returns Statistics
Assets/Accounts
Rollovers
Investment Income
Fund Movement
Demographics

Clicking each of these tiles leads the user to more detailed exploration pages for the chosen area which we’ll go through next.

Animation 2: Showing user drilling into 1 of the 6 dashboard tiles

Detailed Pages

Once a user has selected one of the tiles from the main dashboard page (in this case, the first tile, Return Statistics) they will be greeted with the page below.

At the top of this detailed page are other sub-menus that relate to returns with the current page (in this case Returns Growth — 1 vs 5 year) highlighted in grey. Each of the 6 sections mentioned earlier has their own sub-menu (although some do not have multiple options). In the returns statistics case the sub-menu includes a Returns Growth view on a 1 and 5 year basis and 5 and 10 year basis. I have also included 2 other sub-menus which look at the ranking of returns across those same time periods.

On the left hand side of the page, the user can select different filters including time period and make selections of Fund Type, Fund RSE licensee and Fund name. In this example the user had already selected Industry as the Fund Type from the main dashboard menu and so that selection has held here. I include both RSE Licensee and Fund Name because in some cases there are multiple funds under an RSE Licensee (which is the case here when I select AMP Superannuation Limited as the selection).

I have also included some KPI’s on the right-hand side. The first 2 show the current average of returns on a 1 year and 5 year basis. This is based on the period of time you have filtered (or filtered out). In this case, as I’ve made no selections on period, it shows all yearly data from 2014 to 2019 so 6.29% is the average 1 year return for Industry type super funds in fiscal year 2019 (i.e. year ending 30 June 2019).

The latter 2 KPI’s are the average of those 1 and 5-year returns over the period being filtered. In this case, whilst Industry style super fund returned 6.29% on average in FY2019, their 1 year returns have been 8.34% on average from 2014 until today.

The main part of the page shows the key charts pertaining to this analysis. Each detailed page has its own info but for this page it shows what the average 1-year and 5-year returns have been over time based on what’s been filtered (in this case focus is on Industry super funds) and the bottom chart is a scatter plot showcasing the 1-year and 5-year returns. The upward right facing angle of the average line (dotted in black) shows that there is an indicative relationship/correlation between performances over this timeframe.

Navigation

If the user wants to navigate back to the dashboard then they can click on the APRA log on on the top-left hand side of every page of the app. Along with the sub-menu navigation buttons, this gives the user the ability to navigate to different parts of the application.

The other 6 tiles showcase different sets of information that are visual representations of the APRA data so users can explore those pages for further insights.

Resetting selections

Users can change what’s been selected by using the filters available on each page. However, should they wish to reset everything, they can do so by clicking on the reset button on the top right-hand side of each page. This will bring reset the filters (except for the period/time selecitons) back to their default.

Embed code

For users who want to embed this app in their own website/blog or other publication then they can do so with the following code:

Insights

As I did with the MySuper heatmap dataset, I also looked at a few insights that this app can provide. For example:

Industry super funds had the best 10-year returns (8.07%) compared to other fund types. However, this performance has been in decline as 5-year and 1-year returns indicate.

Within the Industry funds, Australian Super had the largest average total assets (120m+) and member accounts (2.16m) over the 5 years of data in the app (from 2014 to 2019). However, whilst assets have grown, membership took a hit in 2019 and declined from 2.23m to 2.16m.

Demographic data shows that the only increasing age brackets for Retail super funds on average is in the 70+ age brackets (1st image below). The picture is different for Industry super funds where age brackets 55+ and above are all seeing increases in membership whilst the younger brackets are in decline (2nd image below).

Caveats

I should mention, at this point at least, that this is in no way meant to be investment advice. This is merely an attempt to tinker with the fund-level superannuation data made available by APRA and make it easier to use and explore.

In case you were wondering I am not an expert in superannuation data. However, I’ve decided to build in this area for 3 main reasons:

The data is just sitting there (asking to be transformed into something easy to use/insightful/beautiful).
I’ve worked in the financial markets for the last 5 years and analytics for the last 10+ so doing this type of thing with data/analytics is something I like to do.
Australia has the 4th largest pool of pension (i.e. superannuation) money in the world so this is data that deserves to be looked at and shared.

This application merely scratches the surface of the information that is currently available in the back catalog of statistics provided by APRA. When I have time, I’ll explore some more and find ways to add to this app.

I have used Power BI as the analytics tool here but there are many other data exploration apps that could have been used. Publishing data publicly and for free is just something that Microsoft makes easy. I could have built this app using Qlik Sense or even QlikView as well but I’ll find another use for those later on. Some of you may wish to try building upon this data with other tools like Tableau, Spotfire or even via open source tools (R / Python).

There is more information available (including a glossary) from APRA so make sure to check out their page if you’re interested in the data. Using my application, perhaps you can come up with some other insights from this data so go ahead and try it out.

Conclusion

Well I hope you enjoyed reading this article and accessing the app. If you couldn’t already tell, I’ve a passion for data analytics and data-driven decision making. Turning public data into something more usable is one way in which I get to share this passion with others.

You might also be wondering why I’ve decided to put this out today when it’s only been a week since the MySuper app was published. I’m not trying to set a precedent but I’m on holidays soon (January 12 to February 1) so I’m giving this out a few days before then so I can answer any questions that come up prior to my departure.

Whilst away, I will mostly be off the grid and in need of this time off to recharge. I’ll need my analytics juices flowing when I start my new role at Prosperity Advisers next month (which you can read more about HERE) so I’ll be intermittently checking on things during that time.

If anyone wants to know more about how this file was built or wants help on how to use it then feel free to reach out to me.

Anyway, I hope you enjoy playing around with this new app and I welcome any feedback, advice or criticisms or questions. Any line of conversation that is data driven will always get my attention.