library(readxl)
library(dplyr)
library(purrr)
library(tidyr)
library(stringr)
library(lubridate)
library(ggplot2)
library(readr)
input_dir <- "03-other-inputs"
worksheets <- c("NSW","VIC","SA","QLD")
files <- paste0("Solar market offers - ", worksheets, ".xlsx")
pv_size <- 6.6# capacity factors
capacity_factors <- read_excel(
file.path(input_dir, "Rooftop_solar_calc_data.xlsx"),
sheet = "capacity_factor",
skip = 2
) %>%
transmute(State, CF = CSIRO)
# export ratios
exp_raw <- read_excel(file.path(input_dir,"Rooftop_solar_calc_data.xlsx"),
sheet="Export", skip=2) %>%
slice(1:9) %>%
transmute(size = as.numeric(`System size`),
ratio = as.numeric(`SunWiz Export Ratios`))
exp_p <- read_excel(file.path(input_dir,"Rooftop_solar_calc_data.xlsx"),
sheet="Export", range="B36:C36",
col_names=c("a","k"))
A <- as.numeric(exp_p$a)
K <- as.numeric(exp_p$k)
calc_export <- function(s) {
x <- filter(exp_raw, size==s)
if (nrow(x)) x$ratio else A*(1-exp(-K*s))
}
calc_fit <- function(bonus, b1, r1, r2, qexp) {
# Bonus‐months special → return 2nd FIT
if (!is.na(bonus)) {
return(r2)
}
# No second‐tier FIT → base FIT
if (is.na(r2)) {
return(r1)
}
# Missing block breakpoint → treat as flat FIT
if (is.na(b1)) {
return(r1)
}
# Two‐tier weighted average
f <- min(qexp, b1)
rem <- qexp - f
(f * r1 + rem * r2) / qexp
}
parse_date <- function(nm) {
yr <- str_extract(nm,"\\d{4}")
mo <- str_extract(nm,"Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec")
if (is.na(mo)) mo <- "Jul"
as.Date(paste("1",mo,yr),"%d %b %Y")
}
std_cols <- function(df) {
map <- list(
FiT_c_kWh = c("FIT (c/kWh)","FIT (c/kwh)"),
First_kWh_per_quarter = c("First kWh per quarter","1st step (kWh/quarter)"),
Bonus_months = c("First # months","1st step (months)"),
Second_FiT_c_kWh = c("2nd FIT","2nd FIT (c/kWh)")
)
for(n in names(map)) {
for(x in map[[n]]) if(x %in% names(df)) {
df <- rename(df, !!n := all_of(x)); break
}
if (!n %in% names(df)) df[[n]] <- NA
}
df
}
type_cols <- function(df) {
types <- c(
State="character", DB="character", `Meter type`="character",
`Solar meter fee (c/day)`="numeric", Retailer="character", Name="character",
FiT_c_kWh="numeric", First_kWh_per_quarter="numeric",
Bonus_months="numeric", Second_FiT_c_kWh="numeric"
)
for(col in intersect(names(types), names(df))) {
df[[col]] <- switch(types[col],
numeric = as.numeric(df[[col]]),
character = as.character(df[[col]]),
df[[col]])
}
df
}
parse_period <- function(sheet) {
m <- str_extract(
sheet,
"(?i)(Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?)[[:space:]]*\\d{4}"
)
if (is.na(m)) return(NA_Date_)
# normalise to “Month YYYY”
m <- str_to_title(str_replace(m, "([A-Za-z]+)\\s*(\\d{4})", "\\1 \\2"))
# parse to first of month, then roll forward to month end
first_day <- parse_date_time(m, orders = c("b Y", "B Y"))
as.Date(ceiling_date(first_day, "month") - days(1))
}
extract <- function(sheet, file) {
read_excel(file, sheet = sheet) %>%
std_cols() %>% type_cols() %>%
select(any_of(c(
"State","DB","Meter type","Solar meter fee (c/day)",
"Retailer","Name","FiT_c_kWh","First_kWh_per_quarter",
"Bonus_months","Second_FiT_c_kWh"
))) %>%
mutate(Period = parse_period(sheet))
}
# ingest
raw <- map_dfr(files, ~ map_dfr(excel_sheets(file.path(input_dir, .x)),
extract, file=file.path(input_dir, .x)))
df <- raw %>%
filter(State %in% worksheets, `Meter type` %in% c("Single","TOU"))
# quarterly export volumes
qtr <- df %>%
distinct(State) %>%
left_join(capacity_factors, by = "State") %>%
mutate(
annual_output = CF * pv_size * 8760, # kW × hours/year
qtr_output = annual_output / 4,
ratio = calc_export(pv_size),
qexp = qtr_output * ratio
)
print(qtr)# A tibble: 4 × 6
State CF annual_output qtr_output ratio qexp
<chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 NSW 0.146 8441. 2110. 0.774 1633.
2 VIC 0.134 7747. 1937. 0.774 1499.
3 SA 0.148 8557. 2139. 0.774 1655.
4 QLD 0.152 8788. 2197. 0.774 1700.# effective FiT
df <- df %>%
left_join(qtr, by = "State") %>%
rowwise() %>%
mutate(effective_FiT = calc_fit(
Bonus_months, First_kWh_per_quarter,
FiT_c_kWh, Second_FiT_c_kWh,
qexp
)) %>%
ungroup()
# final table
final <- df %>%
select(State, DB, `Meter type`, Period,
Retailer, Name, `Solar meter fee (c/day)`,
FiT_c_kWh, First_kWh_per_quarter,
Second_FiT_c_kWh, effective_FiT)
print(final)# A tibble: 4,364 × 11
State DB `Meter type` Period Retailer Name Solar meter fee (c/d…¹
<chr> <chr> <chr> <date> <chr> <chr> <dbl>
1 NSW Essentia… Single 2024-06-30 Energy … Onli… 1
2 NSW Essentia… Single 2024-06-30 AGL Sola… 2
3 NSW Essentia… Single 2024-06-30 Alinta … Prio… 3
4 NSW Essentia… Single 2024-06-30 Diamond… Rene… 4
5 NSW Essentia… Single 2024-06-30 Dodo Po… Mark… 5
6 NSW Essentia… Single 2024-06-30 EnergyA… Sola… 6
7 NSW Essentia… Single 2024-06-30 Origin … Sola… 7
8 NSW Essentia… Single 2024-06-30 Powersh… Powe… 8
9 NSW Essentia… Single 2024-06-30 Red Ene… Sola… 9
10 NSW Essentia… Single 2024-06-30 Engie Solar 10
# ℹ 4,354 more rows
# ℹ abbreviated name: ¹`Solar meter fee (c/day)`
# ℹ 4 more variables: FiT_c_kWh <dbl>, First_kWh_per_quarter <dbl>,
# Second_FiT_c_kWh <dbl>, effective_FiT <dbl>violations <- final %>%
filter(
# Case A: two‐tier plans where effective_FiT should lie between FiT_c_kWh and Second_FiT_c_kWh
!is.na(FiT_c_kWh) & !is.na(Second_FiT_c_kWh) &
(effective_FiT < pmin(FiT_c_kWh, Second_FiT_c_kWh) |
effective_FiT > pmax(FiT_c_kWh, Second_FiT_c_kWh))
|
# Case B: single‐tier plans (no second FIT) where effective_FiT should exactly match FiT_c_kWh
!is.na(FiT_c_kWh) & is.na(Second_FiT_c_kWh) &
effective_FiT != FiT_c_kWh
)
# Report
if (nrow(violations) == 0) {
message("All effective_FiT values are within expected bounds.")
} else {
message("Found ", nrow(violations), " rows violating the effective_FiT logic:")
print(violations %>% select(State, DB, `Meter type`, Period, Retailer, Name,
FiT_c_kWh, Second_FiT_c_kWh, effective_FiT))
}final <- final %>%
mutate(
Period = as.Date(Period),
effective_FiT = as.numeric(effective_FiT)
)
states <- unique(final$State)
for (st in states) {
df_st <- final %>%
filter(State == st, !is.na(effective_FiT), `Meter type` == "Single") %>%
group_by(Period, State, Retailer) %>%
summarise(
effective_FiT = mean(effective_FiT, na.rm = TRUE)
)
p <- ggplot(df_st, aes(x = Period, y = effective_FiT, colour = Retailer, group = Retailer)) +
geom_line() +
scale_x_date(date_labels = "%b %Y", date_breaks = "6 months") +
labs(
title = paste("Feed-in tariff rates over time - ", st),
x = NULL,
y = "Effective FiT (c/kWh)",
colour = "Retailer"
) +
theme_minimal() +
theme(
axis.text.x = element_text(angle = 45, hjust = 1),
plot.title = element_text(size = 14, face = "bold"),
legend.position = "none"
)
print(p)
}
# Define the retailers of interest
core_retailers <- c("AGL", "Origin Energy", "EnergyAustralia")
other_label <- "Others"
# Loop over each state
for (st in unique(final$State)) {
df_plot <- final %>%
filter(State == st, !is.na(effective_FiT), `Meter type` == "Single") %>%
mutate(
RetailerGroup = if_else(Retailer %in% core_retailers, Retailer, other_label)
) %>%
group_by(Period, RetailerGroup) %>%
summarise(effective_FiT = mean(effective_FiT, na.rm = TRUE), .groups = "drop")
p <- ggplot(df_plot, aes(x = Period, y = effective_FiT, colour = RetailerGroup, group = RetailerGroup)) +
geom_line() +
geom_point() +
scale_x_date(date_labels = "%b %Y", date_breaks = "6 months") +
labs(
title = paste("Effective FiT Over Time -", st),
x = NULL,
y = "Effective FiT (c/kWh)",
colour = "Retailer"
) +
theme_minimal(base_size = 12) +
theme(
axis.text.x = element_text(angle = 45, hjust = 1),
plot.title = element_text(size = 14, face = "bold")
)
print(p)
}
core <- c("AGL", "Origin Energy", "EnergyAustralia")
other_lbl <- "Others"
mkt_share <- readr::read_csv(file.path(input_dir, "retailer-market-share.csv")) %>%
filter(Jurisdiction != "National", Metric == "Market share") %>%
# force all FY cols to character so pivot_longer can combine them
mutate(across(matches("^\\d{4}-\\d{2}$"), as.character)) %>%
pivot_longer(
cols = matches("^\\d{4}-\\d{2}$"),
names_to = "FY",
values_to = "share"
) %>%
mutate(
share = as.numeric(share),
RetailerGroup = if_else(Retailer %in% core, Retailer, other_lbl)
) %>%
group_by(Jurisdiction, FY, RetailerGroup) %>%
summarise(share = sum(share, na.rm = TRUE), .groups = "drop")
fit_with_fy <- final %>%
mutate(
Period = as.Date(Period),
FY = if_else(
month(Period) >= 7,
paste0(year(Period), "-", str_sub(year(Period) + 1, 3, 4)),
paste0(year(Period) - 1, "-", str_sub(year(Period), 3, 4))
)
) %>%
filter(`Meter type` == "Single")
group_fit <- fit_with_fy %>%
mutate(RetailerGroup = if_else(Retailer %in% core, Retailer, other_lbl)) %>%
group_by(State, FY, RetailerGroup) %>%
summarise(avg_fit = mean(effective_FiT, na.rm = TRUE), .groups = "drop")
weighted_fit <- group_fit %>%
left_join(
mkt_share,
by = c("State" = "Jurisdiction", "FY", "RetailerGroup")
) %>%
group_by(State, FY) %>%
summarise(
weighted_FiT = sum(avg_fit * share, na.rm = TRUE) / sum(share),
.groups = "drop"
)
final_ts <- fit_with_fy %>%
distinct(State, Period, FY) %>%
left_join(weighted_fit, by = c("State", "FY"))
weighted_fit %>% arrange(State, FY)# A tibble: 37 × 3
State FY weighted_FiT
<chr> <chr> <dbl>
1 NSW 2015-16 5.77
2 NSW 2016-17 8.96
3 NSW 2017-18 15.5
4 NSW 2018-19 15.5
5 NSW 2019-20 11.5
6 NSW 2020-21 9.65
7 NSW 2021-22 9.08
8 NSW 2022-23 10.7
9 NSW 2023-24 8.19
10 QLD 2015-16 6.33
# ℹ 27 more rowsweighted_fit <- weighted_fit %>%
mutate(FY = factor(FY, levels = sort(unique(FY))))
ggplot(weighted_fit, aes(x = FY, y = weighted_FiT, colour = State, group = State)) +
geom_line(size = 1) +
geom_point(size = 2) +
labs(
title = "Weighted Effective FiT by State",
x = "Financial Year",
y = "Weighted Effective FiT (c/kWh)",
colour = "State"
) +
theme_minimal(base_size = 12) +
theme(
axis.text.x = element_text(angle = 45, hjust = 1),
plot.title = element_text(face = "bold", size = 14)
)