library(httr)
library(jsonlite)
library(tidyverse)
library(janitor)
library(readxl)
library(lubridate)
library(purrr)AustralianSuper PHD
First, let us get the APRA-data
Load those packages
Read those APRA-files
annual_fund_url <- "https://www.apra.gov.au/sites/default/files/2023-12/Annual%20fund-level%20superannuation%20statistics%20back%20series%20June%202004%20to%20June%202023.xlsx"
destfile <- "annual_fund_data.xlsx"
response <- GET(annual_fund_url)
writeBin(content(response, "raw"), destfile)
annual_fund_data <- read_excel(destfile, sheet = "Table 9", trim_ws = T, skip = 5)
annual_fund_clean_data <- annual_fund_data %>%
clean_names() %>%
filter(!is.na(period)) %>%
mutate(period = as.Date(period)) %>%
mutate(total_assets = as.numeric(total_assets))Explore that data
#Industry funds: top ten as of end Jun 23
top_industry_funds <- annual_fund_clean_data %>%
filter(period == "2023-06-30", fund_type =="Industry") %>%
slice_max(total_assets, n = 10)
#PublicSector funds: top ten as of end Jun 23
top_public_sector_funds <- annual_fund_clean_data %>%
filter(period == "2023-06-30", fund_type =="Public Sector") %>%
slice_max(total_assets, n = 10)
#All funds: top ten as of end Jun 23
top_funds <- annual_fund_clean_data %>%
filter(period == "2023-06-30", rse_regulatory_classification == "Public offer") %>%
slice_max(total_assets, n = 10)Set those names
top_funds$fund_name
top_funds$fund_code = c("aussuper",
"art",
"aware",
"unisuper",
"hostplus",
"cfs",
"cbus",
"mlc",
"hesta",
"rest")
#check code names
top_funds %>% select(fund_name, fund_code)
sum(top_funds$total_assets)Second, let us get the fund-specific data
AustralianSuper
These are the links to PHD
superannuation_url <- "https://www.australiansuper.com/-/media/australian-super/files/investments/phd/superannuation/"
pension_url <- "https://www.australiansuper.com/-/media/australian-super/files/investments/phd/pension/"
superannuation_files <- c("balanced-phd.csv", "cash-phd.csv", "conservative-phd.csv",
"diversified-fixed-interest-phd.csv", "high-growth-phd.csv",
"indexed-diversified-phd.csv", "international-shares-phd.csv",
"socially-aware-phd.csv", "stable-phd.csv")
pension_files <- c("balanced-pension-phd.csv", "cash-pension-phd.csv", "conservative-pension-phd.csv",
"diversified-fixed-interest-pension-phd.csv", "high-growth-pension-phd.csv",
"indexed-diversified-pension-phd.csv", "international-shares-pension-phd.csv",
"socially-aware-pension-phd.csv", "stable-pension-phd.csv")We download. Yes.
download_and_read <- function(base_url, file_name) {
file_url <- paste0(base_url, file_name)
response <- GET(file_url)
if (status_code(response) == 200) {
content <- content(response, "text", encoding = "UTF-8")
data <- read_csv(content) %>% clean_names()
return(data)
} else {
warning(paste("Failed to download:", file_url))
return(NULL)
}
}
aussuper_superannuation_data <- map_df(superannuation_files, ~download_and_read(superannuation_url, .))
aussuper_pension_data <- map_df(pension_files, ~download_and_read(pension_url, .))
aussuper_data_raw <- bind_rows(aussuper_superannuation_data, aussuper_pension_data)
# Adding as at date to aussuper
aussuper_data <- aussuper_data_raw %>%
mutate(as_at_date = as.Date("31-12-2023", format = "%d-%m-%Y")) %>%
mutate(tax_treatment = case_when(
grepl("(Pension)", option_name) ~ "Pension",
TRUE ~ "Accumulation"
)) We see as asset class.
# Transform to asset class view
aussuper_by_asset_class <- aussuper_data %>%
# Selecting rows where 'name' column is "Total"
filter(name == "Total") %>%
# Filtering out Derivatives by 'filter' unless it is specified as "By Kind"
filter((asset_class == "Derivatives" & filter == "By Kind") | asset_class != "Derivatives") %>%
# Keeping only relevant columns for further analysis
select(as_at_date, asset_class, filter, sub_filter, value) %>%
# Grouping data by asset class, filter type, and sub-filter for aggregation
group_by(as_at_date, asset_class, filter, sub_filter) %>%
# Summing up the values to get total dollar value for each group
reframe(value = sum(value))
aussuper_asset_class %>% adorn_totals()We see as listed equity.
aussuper_equity_listed_holdings <- aussuper_data %>%
filter(option_name == "Balanced", tax_treatment == "Accumulation",
asset_class == "Equity", filter == "Listed", name_type != "Total") %>%
select(as_at_date, option_name, tax_treatment, asset_class, filter, name, value, currency,
security_identifier, weighting_percent) %>%
mutate(fund_name = "aussuper")We see as PE.
# Extracting and transforming data for private equity managers in Australia
pe_managers_aus <- aussuper_data_date %>%
# Filtering rows specific to Equity, Unlisted, Externally Managed, and not totals
filter(asset_class == "Equity",
filter == "Unlisted",
sub_filter == "Externally Managed",
name_type != "Total") %>%
# Selecting relevant columns for the analysis
select(name, value) %>%
# Grouping by the manager's name to aggregate data
group_by(name) %>%
# Summing up investment values for each manager
reframe(value = sum(value, na.rm = TRUE)) %>%
# Adding a new column to identify the fund code
mutate(fund_code = "aussuper")ART
This is WIP and may not run right.
# Base parts of the URL
base_url <- "https://files.australianretirementtrust.com.au/phd/"
versions <- "20240327"
types <- c("super", "retire")
categories <- c("lifecycle", "multi", "single")
options <- list(
lifecycle = c("Lifecycle_Balanced", "Lifecycle_Retirement", "Lifecycle_Cash"),
multi = c("Growth", "Balanced", "Balanced_Index", "Socially_Balanced",
"Alternatives", "Retirement", "Conservative"),
single = c("Shares", "Aust_Shares", "Aust_Shares_Index",
"Intl_Shares_Hedged", "Intl_Shares_Unhedged", "Emerging",
"Property", "Aust_Property", "Bonds", "Bonds_Index", "Cash")
)
urls_grid <- expand_grid(art_type = types, art_category = names(options),
option_name = options) %>%
unnest(cols = option_name) %>%
mutate(
link = paste0(base_url, art_type, "/", art_category, "/", option_name,
".csv?v=", versions),
# Create tax_treatment based on the type
tax_treatment = if_else(art_type == "super", "Accumulation", "Pension")
)
# Function to download CSV from a given URL and add tax_treatment
download_csv <- function(link, tax_treatment) {
response <- GET(link)
if (status_code(response) == 200) {
# Read the content of the CSV into a dataframe and clean names
df <- read_csv(content(response, "text"), col_types = cols()) %>%
clean_names() %>%
mutate(tax_treatment = tax_treatment) # Add tax_treatment column
return(df)
} else {
warning("Failed to download the file from: ", link)
return(tibble()) # Return an empty tibble if download fails
}
}
# Using purrr's pmap_dfr to download and combine all CSV files into one dataframe
art_data_raw <- pmap_dfr(urls_grid, ~ download_csv(..4, ..5))
art_data <- art_data_raw %>%
mutate(as_at_date = as.Date("31-12-2023", format = "%d-%m-%Y")) %>%
mutate(asset_class = case_when(
type == "Cash" ~ "Cash",
grepl("Fixed Income", type) ~ "Fixed Income",
grepl("Equity", type) ~ "Equity",
grepl("Property", type) ~ "Property",
grepl("Infrastructure", type) ~ "Infrastructure",
grepl("Alternatives", type) ~ "Alternatives",
grepl("Derivatives", type) ~ "Derivatives",
TRUE ~ NA
)) %>%
mutate(filter = case_when(
grepl("By Asset Class", type) ~ "By Asset Class",
grepl("By Currency", type) ~ "By Currency",
grepl("By Kind", type) ~ "By Kind",
grepl("Listed", type) ~ "Listed",
grepl("Unlisted", type) ~ "Unlisted",
TRUE ~ NA # For any Type not matching the above conditions
)) %>%
mutate(sub_filter = case_when(
grepl("Externally Managed", type) ~ "Externally Managed",
grepl("Internally Managed", type) ~ "Internally Managed",
TRUE ~ NA # For any Type not matching the above conditions
)) %>%
mutate(
value = as.numeric(str_replace_all(value, "[$,]", "")),
weighting_percent = as.numeric(str_replace_all(weighting, "[%,]", ""))
)
art_equity_listed_holdings <- art_data %>%
filter(option_name == "Lifecycle Balanced Pool", tax_treatment == "Accumulation",
asset_class == "Equity", filter == "Listed") %>%
select(as_at_date, option_name, tax_treatment, asset_class, filter, name, value, currency,
security_identifier, weighting_percent) %>%
mutate(fund_name = "art")Hostplus
This is WIP and may not run right.
# URL of the CSV file to download
url <- "https://hostplus.com.au/content/dam/hostplus-program/site/resources/investments/investment-holdings/accumulation-investment-holdings/Balanced.csv"
# Read the CSV directly from the URL
hostplus_data_raw <- read_csv(url, col_types = cols(.default = "c"))
# Function to extract the "Listed Equity" section using tidyverse
extract_listed_equity <- function(data) {
# Identify where "Listed Equity" starts
start_row <- which(data[[1]] == "Listed Equity") + 2
# Find the first "Total" that appears after "Listed Equity"
total_rows <- which(data[[1]] == "Total")
end_row <- total_rows[total_rows > start_row][1] - 1 # Find the first "Total" after start_row
# Extract the relevant rows and assign meaningful column names
listed_equity <- data %>%
slice(start_row:end_row) %>%
set_names(c("name",
"security_identifier",
"units_held",
"value",
"weighting_percent"))
return(listed_equity)
}
hostplus_data_raw %>% view()
# Extract the "Listed Equity" section
hostplus_equity_listed_holdings <- extract_listed_equity(hostplus_data_raw) %>%
mutate(
value = as.numeric(str_replace_all(value, "[$,]", "")),
weighting_percent = as.numeric(str_replace_all(weighting, "[%,]", ""))
)
hostplus_equity_listed_holdings
# Perform any necessary transformations (example: clean up column names)
investment_data <- investment_data %>%
janitor::clean_names() %>% # If you have janitor installed for column name cleaning
mutate(
asset_class = "Equity", # Example of adding an additional column
filter = "Balanced"
)