# graph data

# tidies graphs into a neat format and outputs csvs

if (!dir.exists('./graphs')) {
  dir.create('./graphs')
}


time_series <- ungroup(time_series)

# Figure 1: Australian Financial Markets


SSE_annual %>%
  group_by(Year) %>%
  summarise(Market_cap_SSE = sum(Market_cap)) %>%
  rename(Dates = Year) %>%
  merge(external_series_quarterly %>%
          select(c(Dates, ABS_equities_outstanding)), all = T) %>%
  filter(months(Dates) == "December") %>%
  merge(external_series_annual %>%
          select(c(Dates, `Nominal GDP`, Foster_marketcap)), all = T) %>%
  mutate(
    Market_cap_SSE = Market_cap_SSE / `Nominal GDP` * 0.0001,
    #some scaling factors to adjust for the fact that some are in millions, some billions, etc...
    Foster_marketcap = Foster_marketcap / `Nominal GDP` * 100,
    ABS_equities_outstanding = ABS_equities_outstanding / `Nominal GDP` * 100
  ) %>%
  transmute(
    Dates = Dates,
    Equities_percentGDP = ifelse(
      !is.na(ABS_equities_outstanding),
      ABS_equities_outstanding,
      ifelse(!is.na(Foster_marketcap), Foster_marketcap,
             Market_cap_SSE)
    )
  ) %>%  #in order of likely accuracy
  merge(external_series_annual %>%
          select(
            c(
              Dates,
              Black_bondsOS_government_percentGDP,
              Black_bondsOS_corporate_percentGDP
            )
          )) %>%
  rename(`Government bonds` = Black_bondsOS_government_percentGDP, `Corporate bonds` = Black_bondsOS_corporate_percentGDP) %>%
  write.csv('./graphs/1.Australian Financial Markets.csv', row.names = F)


# Figure 2: Australian equity price indices

price_indices <-
  time_series %>% select(c(Dates, Price_index, Broad.sector)) %>%
  spread(Broad.sector, Price_index) %>%
  merge(external_series_quarterly %>%
          select(
            c(
              Dates,
              DS_ASX_index,
              DS_RES_index,
              DS_FIN_index,
              DS_OTHER_index
            )
          ), all = T) %>%
  rename(
    Total_modern = DS_ASX_index,
    Resources_modern = DS_RES_index,
    Financial_modern = DS_FIN_index,
    Other_modern = DS_OTHER_index
  )

#splice modern series
max_date <- which(price_indices$Dates == max(time_series$Dates))

scale_factors <-
  c(
    price_indices[max_date, "Total"] / price_indices[max_date, "Total_modern"],
    price_indices[max_date, "Resources"] / price_indices[max_date, "Resources_modern"],
    price_indices[max_date, "Financial"] / price_indices[max_date, "Financial_modern"],
    price_indices[max_date, "Other"] / price_indices[max_date, "Other_modern"]
  )
price_indices %>%
  mutate(
    Total_modern = ifelse(
      Dates >= max(time_series$Dates),
      Total_modern * scale_factors[1],
      NA
    ),
    Resources_modern = ifelse(
      Dates >= max(time_series$Dates),
      Resources_modern * scale_factors[2],
      NA
    ),
    Financial_modern = ifelse(
      Dates >= max(time_series$Dates),
      Financial_modern * scale_factors[3],
      NA
    ),
    Other_modern = ifelse(
      Dates >= max(time_series$Dates),
      Other_modern * scale_factors[4],
      NA
    )
  ) %>%
  write.csv('./Graphs/2.Equity price indices.csv', row.names = F)

rm(price_indices, scale_factors)


# Figure 3: Listed companies' profits and dividends
profits <- external_series_quarterly %>%
  mutate(
    DS_ASX100_dividend = DS_ASX100_cap * DS_ASX100_dividendyield / 100,
    DS_ASX100_profit = DS_ASX100_cap / DS_ASX100_PE
  ) %>%
  merge(
    transmute(
      external_series_annual,
      Dates = Dates,
      Foster_profit = Foster_marketcap * Foster_earningsyield,
      Foster_dividend = Foster_marketcap * Foster_weighted_dividendyield
    )
  )
# splice the modern series - the Foster data cover the entire market, not just the top 100

splice_date <-
  min(filter(profits,!is.na(Foster_profit),!is.na(DS_ASX100_profit))$Dates)  # the earliest available point where there is data for both series we are trying to splice
profit_splice <-
  profits[which(profits$Dates == splice_date), "DS_ASX100_profit"] / profits[which(profits$Dates == splice_date), "Foster_profit"]
dividend_splice <-
  profits[which(profits$Dates == splice_date), "DS_ASX100_dividend"] /
  profits[which(profits$Dates == splice_date), "Foster_dividend"]

profits <- profits %>%
  mutate(
    Foster_profit = Foster_profit * profit_splice,
    Foster_dividend = Foster_dividend * dividend_splice
  ) %>%
  transmute(
    Dates = Dates,
    modern_dividend = ifelse(!is.na(DS_ASX100_dividend),DS_ASX100_dividend, Foster_dividend),
    modern_profit = ifelse(!is.na(DS_ASX100_profit), DS_ASX100_profit, Foster_profit)
  ) %>%
  merge(external_series_annual %>% select(c(Dates, `Nominal GDP`))) %>% 
  mutate(`Nominal GDP` = `Nominal GDP` / 1000) %>%
  merge(
    time_series %>% filter(Broad.sector == "Total") %>%
      ungroup() %>% 
      select(c(Dates, Dividend, Profit)) %>% 
      mutate_if(is.numeric, function(x) x/1e6),
    all = T
  ) %>%
  as_tibble() %>%
  filter(months(Dates) == "December") %>%
  write.csv('./graphs/3.Listed companies profits and dividends.csv',
            row.names = F)

rm(splice_date, profit_splice, dividend_splice)



# Figure 4: Dividend payout ratios

transmute(external_series_annual,
          Dates = Dates,
          Payout_ratio_US = Dividend_yield_US * PE_ratio_US) %>%
  merge(
    transmute(
      external_series_quarterly,
      Dates = Dates,
      Payout_ratio_modern = DS_ASX_dividendyield * DS_ASX_PE
    )
  ) %>%
  merge(filter(time_series, Broad.sector == "Total") %>% select(c(Dates, Payout_ratio)),
        all = T) %>%
  filter(months(Dates) == "December",
         !is.na(Payout_ratio_modern) | !is.na(Payout_ratio)) %>%
  write.csv('./graphs/4.Dividend payout ratios.csv', row.names = F)

# Figure 5: Dividend yields

subset(time_series,
       Broad.sector == "Total",
       select = c(Dates, Dividend_yield)) %>%
  merge(select(
    external_series_quarterly,
    c(Dates, Dividend_yield_LambertonASE, DS_ASX_dividendyield)
  ), all = T) %>%
  write.csv('./graphs/5.Dividend yields.csv', row.names = F)


# Figure 6: Equity risk premia
# for this, we calculate it using various combinations of share prices and dividend yields, and also compare to the US

window <- 25 #25 year rolling window for ERP graph

select(
  external_series_annual, #get the Lamberton and Datastream/Refinitiv series
  c(
    Dates,
    Bond_yield_aus,
    Bond_yield_US,
    Share_price_US,
    Dividend_yield_US
  )
) %>% 
  merge(select(
    external_series_quarterly,
    c(
      Dates,
      Dividend_yield_LambertonASE,
      Share_index_LambertonASE,
      DS_ASX_index,
      DS_ASX_dividendyield
    )
  ), all = T) %>% #
  merge(subset( #merge bond yields and other external data with our calculated series
    time_series,
    Broad.sector == "Total",
    select = c(Dates, Dividend_yield, Price_index)
  ), all = T) %>%
  mutate( #splice the indices to create continuous series from the start
    Share_index_LambertonASE = ifelse(
      is.na(Share_index_LambertonASE / lag(Share_index_LambertonASE)),
      DS_ASX_index / lag(DS_ASX_index),
      Share_index_LambertonASE / lag(Share_index_LambertonASE)
    ),
    Share_index_LambertonASE = ifelse(is.na(Share_index_LambertonASE), 1, Share_index_LambertonASE),
    Share_index_LambertonASE = cumprod(Share_index_LambertonASE),
    Price_index = ifelse(
      is.na(Price_index / lag(Price_index)),
      DS_ASX_index / lag(DS_ASX_index),
      Price_index / lag(Price_index)
    ),
    Price_index = ifelse(is.na(Price_index), 1, Price_index),
    Price_index = cumprod(Price_index),
    Dividend_yield = ifelse(is.na(Dividend_yield), DS_ASX_dividendyield, Dividend_yield),
    Dividend_yield_LambertonASE = ifelse(
      is.na(Dividend_yield_LambertonASE),
      DS_ASX_dividendyield,
      Dividend_yield_LambertonASE
    )
  ) %>%
  filter(months(Dates) == "December") %>%
  transmute(
    Dates = Dates,
    Aus_ERP_RBA = udf_ERP(Bond_yield_aus, Price_index, Dividend_yield, window),
    AUS_ERP_Lamberton = udf_ERP(Bond_yield_aus, Share_index_LambertonASE, Dividend_yield_LambertonASE,window),
    AUS_ERP_LambRBA = udf_ERP(Bond_yield_aus, Share_index_LambertonASE,  Dividend_yield, window),
    US_ERP = udf_ERP(Bond_yield_US, Share_price_US, Dividend_yield_US, window)
  ) %>%
  filter(!is.na(Aus_ERP_RBA)) %>%
  write.csv('./graphs/6.Realised equity risk premium.csv', row.names = F)


# Figure 7: Equity Total Return by Sector

total_returns_extended %>%
  select(c(Dates, Broad.sector, Accumulation_index)) %>%
  group_by(Broad.sector) %>%
  mutate_if(is.numeric, function(x)
    (x / lag(x, 40)) ^ (1 / 10) * 100 - 100) %>%  #10 year (40 quarter) trailing annualised returns
  filter(!is.na(Accumulation_index)) %>%
  spread(Broad.sector, Accumulation_index) %>%
  write.csv('./graphs/7.Equity total return by sector.csv', row.names = F)


# Figure 8: Return volatility by sector

total_returns_extended %>%
  select(c(Dates, Broad.sector, Accumulation_index)) %>%
  mutate_if(is.numeric, udf_growthrate) %>%
  spread(Broad.sector, Accumulation_index) %>%
  mutate_if(is.numeric, function(x)
    sqrt(rollapplyr(
      x,
      width = 40,
      FUN = var,
      fill = NA
    ))) %>%  #10 year moving standard deviation
  filter(!is.na(Total)) %>%
  write.csv('./graphs/8.return volatility by sector.csv', row.names = F)

# Figure 9: Share price correlations
window <- 20

rbind(
  udf_pricecorrelations(SSE_data %>% select(c(
    Dates, Security, Price
  )), window),
  udf_pricecorrelations(
    external_companies %>% select(c(Dates, ASX_code, Price)) %>% rename(Security = ASX_code),
    window
  )
) %>%
  merge(
    total_returns_extended %>%
      filter(Broad.sector == "Total") %>%
      ungroup() %>%
      transmute(
        Dates = Dates,
        Volatility = rollmeanr(abs(udf_growthrate(Price_index)), window, na.pad = T)
      ),
    all = T
  ) %>%
  write.csv('./graphs/9.Share price correlations.csv', row.names = F)


# Figure 10: Listed company shares
SSE_data %>%
  select(c(Dates, Price, Shares)) %>%
  mutate(Shares = Shares / 1e6) %>%
  merge(external_companies %>% filter(Market.Capitalisation.Rank <= 100) %>% select(c(Dates, Price, Shares)),
        all = T) %>%
  group_by(Dates) %>%
  summarise(
    mean_share_price = mean(Price, trim = 0.1, na.rm = T),
    mean_shares_outstanding = mean(Shares, trim = 0.1, na.rm = T)
  ) %>%
  write.csv('./graphs/10.Listed company shares.csv', row.names = F)


# Figure 11: Market Capitalisation by sector

time_series %>%
  ungroup() %>%
  select(c(Market.Capitalisation, Dates, Broad.sector)) %>%
  filter(Broad.sector != "Total") %>%
  mutate(Series_type = "Historical") %>%
  merge(
    external_series_quarterly %>%
      select(c(
        Dates, DS_FIN_cap, DS_RES_cap, DS_OTHER_cap
      )) %>%
      rename(
        Financial_modern = DS_FIN_cap,
        Resources_modern = DS_RES_cap,
        Other_modern = DS_OTHER_cap
      ) %>%
      gather(key = Broad.sector, value = Market.Capitalisation,-Dates) %>%
      mutate(Series_type = "Modern"),
    all = T
  ) %>%
  group_by(Dates, Series_type) %>%
  mutate(Share = Market.Capitalisation / sum(Market.Capitalisation) * 100) %>%
  ungroup() %>%
  select(-c(Market.Capitalisation, Series_type)) %>%
  spread(Broad.sector, Share) %>%
  write.csv('./graphs/11.Market capitalisation by sector.csv', row.names = F)

# Figure 12: Listed banking sector concentration

banking_concentration <- SSE_data_ordinaries %>%
  filter(Narrow.sector.modern == "Banks") %>%
  filter(!is.na(Market.Capitalisation)) %>%
  group_by(Dates) %>%
  summarise(hhi = sum((
    Market.Capitalisation / sum(Market.Capitalisation)
  ) ^ 2),
  no = n())

fin_nonfin <- SSE_data_ordinaries %>%
  filter(Broad.sector == "Financial") %>%
  mutate(Narrow.sector.modern =
           ifelse(
             Narrow.sector.modern %in% c("Trustee", "Hire purchase", "Investment"),
             "Other",
             Narrow.sector.modern
           )) %>%
  group_by(Narrow.sector.modern, Dates) %>%
  summarise(cap = sum(Market.Capitalisation, na.rm = T)) %>%
  group_by(Dates) %>%
  mutate(cap = cap / sum(cap))

# modern data

banking_concentration_modern <- external_companies %>%
  group_by(Dates) %>%
  filter(Market.Capitalisation.Rank <= 100, GICS_code == '4010') %>%
  summarise(hhi = sum((
    Market.Capitalisation / sum(Market.Capitalisation)
  ) ^ 2),
  no = n())

rbind(banking_concentration, banking_concentration_modern) %>%
  write.csv('./graphs/12.Listed banking sector concentation.csv',
            row.names = F)

# Table 1: Total returns

table1 <- total_returns_extended %>%
  select(c(Dates, Broad.sector, Accumulation_index)) %>%
  spread(Broad.sector, Accumulation_index) %>%
  merge(external_series_annual %>% select(c(Dates, Bond_yield_aus, CPI))) %>%
  mutate(Bond_yield_aus = udf_bondTR(Bond_yield_aus)) %>% # turn the bond yield into a total return index
  gather(Series, index,-Dates) %>%
  group_by(Series) %>%
  mutate(growth = udf_growthrate(index))

# have to start the pipe again since we are going to want to refer back to the table
table1 <- table1 %>%
  group_by(Series) %>%
  filter(Dates %in% c(max(Dates), min(Dates))) %>%
  arrange(Dates) %>%
  mutate(geometric_mean = (index / lag(index)) ^ (1 / (year(max(table1$Dates)) - year(min(table1$Dates)) - 1)) * 100 - 100) %>%
  ungroup() %>%
  filter(Dates == max(Dates)) %>%
  select(c(Series, geometric_mean)) %>%
  merge(
    table1 %>%
      group_by(Series) %>%
      summarise(
        arithmetic_mean = mean(growth, na.rm = T),
        standard_deviation = sd(growth, na.rm = T)
      )
  )

write.csv(table1, './graphs/Table Total returns.csv', row.names = F)


# Figure 13: Bank/non-bank split
SSE_data_ordinaries %>%
  filter(Broad.sector == "Financial") %>%
  mutate(Sector = ifelse(
    Narrow.sector.modern %in% c("Banks", "Insurance"),
    Narrow.sector.modern,
    "Other"
  )) %>%
  group_by(Sector, Dates) %>%
  summarise(cap = sum(Market.Capitalisation, na.rm = T)) %>%
  group_by(Dates) %>%
  mutate_if(is.numeric, function(x)
    x / sum(x, na.rm = T) * 100) %>%
  spread(Sector, cap) %>%
  merge(
    external_series_quarterly %>%
      select(c(Dates, DS_FIN_cap, DS_banks_cap, DS_insurance_cap)) %>%
      transmute(
        Dates = Dates,
        Banks_modern = DS_banks_cap / DS_FIN_cap * 100,
        Insurance_modern = DS_insurance_cap / DS_FIN_cap * 100,
        Other_modern = (DS_FIN_cap - DS_banks_cap - DS_insurance_cap) / DS_FIN_cap * 100),
    all = T
  ) %>% 
  filter(months(Dates) == "December") %>% 
  write.csv('./graphs/13.Listed financial sector.csv',  row.names = F)

# Figure 14: Breakdown of the 'other' sector

SSE_data_ordinaries %>%
  filter(Broad.sector == "Other", months(Dates) == "December", Broad.Company.Name != "National Dairy Products") %>%
  # exclude NDP, which is as far as I can tell at this point is a large American co. with a very small number 
  #of irregularly-traded shares on the SSE (but the data here represent their entire market cap) 
  # causes a some volatility in this series which is (i think) ultimately meaningless (but not material enough to affect any other series in this paper except this split)
  mutate(
    Narrow.sector.modern = ifelse(
      Narrow.sector.modern %in% c(
        "Manufacturing",
        "Consumer.staples",
        "Consumer.discretionary",
        "Agriculture.forestry"
      ),
      Narrow.sector.modern,
      "Other"
    ),
    Narrow.sector.modern = ifelse(
      Narrow.sector.modern %in% c("Consumer.staples", "Consumer.discretionary"),
      "Consumer",
      Narrow.sector.modern
    )
  ) %>%
  group_by(Dates, Narrow.sector.modern) %>%
  summarise(Market.Capitalisation = sum(Market.Capitalisation, na.rm = T)) %>%
  spread(Narrow.sector.modern, Market.Capitalisation) %>%
  merge(
    filter(time_series, Broad.sector == "Total") %>% ungroup() %>% select(Dates, Market.Capitalisation)
  ) %>%
  transmute(
    Dates = Dates,
    Agriculture.forestry = Agriculture.forestry / Market.Capitalisation * 100,
    Manufacturing = Manufacturing / Market.Capitalisation * 100,
    Consumer = Consumer / Market.Capitalisation * 100,
    Other = Other / Market.Capitalisation * 100
  ) %>%
  write.csv('./graphs/14.Other sector.csv', row.names = F)


# Figure 15: Equity market concentration

rbind(udf_hhi(SSE_data_ordinaries),
      udf_hhi(filter(
        external_companies, Market.Capitalisation.Rank <= 100
      ))) %>%
  spread(Broad.sector, hhi) %>%
  rename(
    Financial_hhi = Financial,
    Resources_hhi = Resources,
    Other_hhi = Other,
    Total_hhi = Total
  ) %>%
  merge(rbind(udf_topn(SSE_data_ordinaries), udf_topn(
    filter(external_companies, Market.Capitalisation.Rank <= 100)
  ))) %>%
  write.csv('./graphs/15.Concentration.csv', row.names = F)

# Figure 16 is done in a separate script (company age.R), called from the Master.R script

# Figure 17: additions and deletions

SSE_data_ordinaries %>%
  filter(months(Dates) == "March") %>% # the index members change in March
  group_by(Broad.Company.Name) %>%
  filter(!is.na(Shares)) %>% # ie filter times when the company was not in the index and therefore no data were entered
  arrange(Security, Dates) %>%
  mutate(gap = as.numeric(Dates - lag(Dates))) %>%
  filter((gap > 366) |
           (lead(gap) > 366) |
           (Dates == max(Dates)) |
           (Dates == min(Dates))) %>% #either its the first or last time it appears, or there is a gap of longer than a year between data points
  mutate(
    first_entry = ifelse(Dates == min(Dates), 1, 0),
    last_exit = ifelse(Dates == max(Dates), 1, 0),
    re_entry = ifelse(gap > 366, 1, 0),
    temp_exit = ifelse(lead(gap) > 366, 1, 0)
  ) %>%
  group_by(Dates) %>%
  summarise(
    first_entry = sum(first_entry, na.rm = T),
    last_exit = sum(last_exit, na.rm = T),
    re_entry = sum(re_entry, na.rm = T),
    temp_exit = sum(temp_exit, na.rm = T)
  ) %>%
  filter(Dates > min(Dates), Dates < max(Dates)) %>%  #cut off the end points since everything joins at the start and leaves at the end, which is not informative
  write.csv('./graphs/17.Index churn.csv', row.names = F)

# Figures 18 and 19: Price-earnings ratios

udf_meanna <- function(x){mean(x, na.rm = T)} #for below

time_series %>%
  filter(!is.na(Price_earnings)) %>%
  select(c(Dates, Broad.sector, Price_earnings)) %>%
  spread(Broad.sector, Price_earnings) %>%
  merge(external_series_quarterly %>%
          select(c(
            Dates, DS_ASX_PE, DS_RES_PE, DS_FIN_PE, DS_OTHER_PE
          )), all = T) %>%
  rename(
    Total_historical = Total,
    Resources_historical = Resources,
    Financial_historical = Financial,
    Other_historical = Other,
    Total_modern = DS_ASX_PE,
    Resources_modern = DS_RES_PE,
    Financial_modern = DS_FIN_PE,
    Other_modern = DS_OTHER_PE
  ) %>%
  filter(!is.na(Total_historical) | !is.na(Total_modern)) %>% 
  mutate_if(is.numeric, list(mean = udf_meanna, ratio = identity)) %>% 
  write.csv('./graphs/18,19.Price to earnings ratio.csv', row.names = F)

# Figure 20: Resources company earnings and valuations

SSE_data %>% 
  select(c(Dates, Broad.Company.Name, Price, Profit, Market.Capitalisation)) %>% 
  filter(Broad.Company.Name %in% c("Broken Hill Proprietary", "M.I.M. Holdings","Conzinc Riotinto"),
         Dates > as.Date('1960-01-01'),
         Dates <= as.Date('1975-12-31')) %>%
  mutate(PE_ratio = Market.Capitalisation/Profit,
         `Profit_$m` = Profit/1e6,
         Market.Capitalisation = Market.Capitalisation / 1e6) %>% 
  select(-Profit, -Market.Capitalisation) %>% 
  gather(key = "key", value = "value", -c(Dates, Broad.Company.Name)) %>% 
  mutate(column_name = paste(Broad.Company.Name, key, sep = "_")) %>% 
  select(-key, -Broad.Company.Name) %>% 
  spread(column_name, value) %>% 
  merge(time_series %>% 
          filter(Broad.sector == "Total") %>% 
          ungroup() %>% 
          select(c(Dates, Price_earnings)) %>% 
          rename(ASX100_PE_ratio = Price_earnings), by = "Dates") %>% 
  write.csv('./graphs/20.Resources company earnings and valuations.csv', row.names = F)

# Figure 21: Equity ratios

time_series %>%
  filter(!is.na(Price_book), Broad.sector == "Total") %>%
  select(c(Dates, Price_book)) %>%
  merge(external_series_quarterly %>%
          select(c(Dates, DS_ASX_PB, BBG_MSCI_PNTA)), all = T) %>%
  rename(
    Price_NTA_historical = Price_book,
    Price_book_modern = DS_ASX_PB,
    Price_NTA_modern_MSCI = BBG_MSCI_PNTA
  ) %>%
  write.csv('./graphs/21.Equity ratios.csv', row.names = F)

# APPENDIX GRAPHS

# A1&2: coverage by variable and ratio

SSE_data %>% 
  mutate(Dividend_yield = Dividend / Market.Capitalisation, Payout_ratio = Dividend/Profit, PE_ratio = Market.Capitalisation/Profit, PB_ratio = Market.Capitalisation/Book) %>% 
  select(c(Dates, Price, Shares, Market.Capitalisation, Dividend, Profit, Book, Dividend_yield, Payout_ratio, PE_ratio, PB_ratio)) %>% 
  group_by(Dates) %>% 
  summarise_all(function(x) sum(!is.na(x))) %>% 
  write.csv('./graphs/A1&2. Data coverage by variable.csv', row.names = F)

# A3: Companies in share price index

SSE_data_ordinaries %>%
  group_by(Security) %>%
  arrange(Dates, Security) %>% 
  mutate(p = Market.Capitalisation / lag(Market.Capitalisation)) %>% #ie if a market cap growth rate can be calculated
  group_by(Dates) %>% 
  filter(Dates > as.Date('1917-03-31')) %>%  #no growth rate for the first period
  summarise(number = sum(!is.na(p))) %>% 
  write.csv('./graphs/A3. Equity index coverage.csv', row.names = F)

#A4: Share price indices

time_series %>%
  ungroup() %>% 
  filter(Broad.sector == "Total") %>% 
  select(c(Dates, Price_index)) %>% 
  merge(external_series_quarterly %>% 
          select(c(Dates, Share_index_LambertonASE))) %>% 
  transmute(Dates = Dates, RBA_index = Price_index / mean(Price_index) * 100,
            LambertonASE_index = Share_index_LambertonASE / mean(Share_index_LambertonASE) * 100) %>% #rebase them to their own means to make them easier to compare
  write.csv('./graphs/A4. Share price indices.csv', row.names = F)
  
