Baran Ratio: Chile and Türkiye

Aim

In this assignment I make a panel data set for two countries: Chile (my matched country) and Türkiye. I use the OECD income and expenditure files. Then I calculate the Baran ratio and plot it.

\[ \text{Baran ratio}_{it}=\frac{\text{Gross fixed capital formation}_{it}}{\text{Gross operating surplus and mixed income}_{it}} \]

All values are in national currency, current prices, millions.

Setup

Packages I use (install once): install.packages(c("readxl", "dplyr", "ggplot2", "openxlsx"))

library(readxl)
library(dplyr)
library(ggplot2)
library(openxlsx)

matched   <- "Chile"
countries <- c(matched, "Türkiye")

# find the two OECD files in the folder of this .qmd (sub-folders too)
find_file <- function(key) {
  f <- list.files(".", pattern = paste0(key, ".*\\.xlsx$"), full.names = TRUE,
                  recursive = TRUE, ignore.case = TRUE)
  f <- f[!grepl("baran_panel", f)]            # skip my own output file
  if (length(f) == 0)
    stop("No '", key, "' .xlsx file found under ", getwd(),
         ". Put the two OECD files in a 'data' folder next to baran_ratio.qmd.")
  f[1]
}
f_income <- find_file("INCOME")
f_expend <- find_file("EXPENDITURE")

# read all cells as text without a header, because the OECD sheet is not a normal table
read_raw <- function(path) {
  as.matrix(read_excel(path, col_names = FALSE, col_types = "text", .name_repair = "minimal"))
}
inc_raw <- read_raw(f_income)
exp_raw <- read_raw(f_expend)

# the header row is the row with the years (1995, 1996, ...)
year_pattern <- "^[0-9]{4}$"
find_header <- function(m) which(apply(m, 1, function(r) sum(grepl(year_pattern, r)) > 5))[1]

Step 1: Gross operating surplus and mixed income (income file)

This variable is in the income file. In this file every country has its own block, and each block has a national currency part and a US dollar part. I keep only the national currency rows of “Operating surplus and mixed income, gross”.

hdr        <- find_header(inc_raw)
year_cols  <- which(grepl(year_pattern, inc_raw[hdr, ]))
years_inc  <- as.integer(inc_raw[hdr, year_cols])
label_col  <- which(apply(inc_raw, 2, function(x) any(grepl("^Reference area:", x))))[1]

out <- list(); area <- NA; unit <- NA
for (i in (hdr + 1):nrow(inc_raw)) {
  a <- inc_raw[i, label_col]
  if (is.na(a)) next
  if (grepl("^Reference area:", a)) { area <- trimws(sub("^Reference area:", "", a)); next }
  if (grepl("^Unit of measure:", a)) { unit <- a; next }
  if (grepl("Operating surplus and mixed income", a, fixed = TRUE) &&
      !is.na(unit) && grepl("National currency", unit, fixed = TRUE)) {
    out[[length(out) + 1]] <- data.frame(
      country = area, year = years_inc,
      gos_mi  = suppressWarnings(as.numeric(inc_raw[i, year_cols])))
  }
}
gos <- bind_rows(out) |> filter(country %in% countries, !is.na(gos_mi))

gos |> group_by(country) |>
  summarise(first_year = min(year), last_year = max(year), n = n())

# A tibble: 2 × 4
  country first_year last_year     n
  <chr>        <int>     <int> <int>
1 Chile         2018      2024     7
2 Türkiye       1995      2024    30

Step 2: Gross fixed capital formation (expenditure file)

This variable is in the expenditure file. Here every country has one row, so it is easier. I keep the national currency rows of Chile and Türkiye.

hdr        <- find_header(exp_raw)
year_cols  <- which(grepl(year_pattern, exp_raw[hdr, ]))
years_exp  <- as.integer(exp_raw[hdr, year_cols])
country_col <- which(apply(exp_raw, 2, function(x) any(x == "Türkiye", na.rm = TRUE)))[1]
unit_col    <- country_col + 1

out <- list()
for (i in (hdr + 1):nrow(exp_raw)) {
  ctry <- exp_raw[i, country_col]; u <- exp_raw[i, unit_col]
  if (!is.na(ctry) && ctry %in% countries && !is.na(u) && startsWith(u, "National currency")) {
    out[[length(out) + 1]] <- data.frame(
      country = ctry, year = years_exp,
      gfcf    = suppressWarnings(as.numeric(exp_raw[i, year_cols])))
  }
}
gfcf <- bind_rows(out) |> filter(!is.na(gfcf))

gfcf |> group_by(country) |>
  summarise(first_year = min(year), last_year = max(year), n = n())

# A tibble: 2 × 4
  country first_year last_year     n
  <chr>        <int>     <int> <int>
1 Chile         1995      2024    30
2 Türkiye       1995      2024    30

Step 3: Panel data and Baran ratio

Now I join the two series. The panel is in long format, so every row is one country and one year. I divide GFCF by operating surplus to get the Baran ratio. The ratio can be calculated only if both numbers exist.

panel <- expand.grid(country = countries, year = min(gfcf$year):max(gfcf$year),
                     stringsAsFactors = FALSE) |>
  left_join(gfcf, by = c("country", "year")) |>
  left_join(gos,  by = c("country", "year")) |>
  mutate(baran_ratio = gfcf / gos_mi) |>
  arrange(country, year)

panel |> filter(!is.na(baran_ratio)) |> group_by(country) |>
  summarise(first_ratio_year = min(year), last_ratio_year = max(year), n_ratio = n())

# A tibble: 2 × 4
  country first_ratio_year last_ratio_year n_ratio
  <chr>              <int>           <int>   <int>
1 Chile               2018            2024       7
2 Türkiye             1995            2024      30

Note

Missing data. For Chile, the operating surplus data in national currency exists only for 2018-2024. So the Baran ratio of Chile has 7 years, and Türkiye has 30 years (1995-2024). The panel is unbalanced. I left the missing years empty and did not fill them.

panel |> filter(year >= 2016) |> mutate(baran_ratio = round(baran_ratio, 3)) |>
  knitr::kable(format.args = list(big.mark = ",", scientific = FALSE))

country	year	gfcf	gos_mi	baran_ratio
Chile	2,016	40,721,565.1	NA	NA
Chile	2,017	39,911,311.3	NA	NA
Chile	2,018	43,664,344.1	92,085,107	0.474
Chile	2,019	48,002,424.1	93,364,012	0.514
Chile	2,020	45,415,839.0	100,622,261	0.451
Chile	2,021	55,845,770.4	121,620,952	0.459
Chile	2,022	67,458,425.2	129,095,874	0.523
Chile	2,023	68,824,618.2	134,717,942	0.511
Chile	2,024	73,498,780.8	152,710,857	0.481
Türkiye	2,016	790,135.4	1,492,628	0.529
Türkiye	2,017	953,649.0	1,869,633	0.510
Türkiye	2,018	1,153,188.2	2,325,896	0.496
Türkiye	2,019	1,164,911.5	2,657,147	0.438
Türkiye	2,020	1,437,139.3	3,156,405	0.455
Türkiye	2,021	2,110,611.6	4,787,201	0.441
Türkiye	2,022	4,681,281.4	10,253,084	0.457
Türkiye	2,023	8,906,058.3	16,659,365	0.535
Türkiye	2,024	13,972,883.7	25,760,334	0.542

Step 4: Graph

The graph shows the Baran ratio of both countries. Chile starts in 2018 because of the missing data.

plot_data <- panel |> filter(!is.na(baran_ratio))

p <- ggplot(plot_data, aes(year, baran_ratio, colour = country)) +
  geom_line(linewidth = 0.9) + geom_point(size = 2) +
  scale_colour_manual(values = c("Türkiye" = "#C00000", "Chile" = "#1F3864")) +
  labs(title = paste("Baran ratio:", matched, "and Türkiye"),
       x = "Year", y = "GFCF / Gross operating surplus and mixed income",
       colour = NULL, caption = "Source: OECD. National currency, current prices.") +
  theme_minimal(base_size = 12) + theme(legend.position = "bottom")
p

ggsave("baran_ratio_plot.png", p, width = 10, height = 5.5, dpi = 160)

Step 5: Excel output

I save the panel to Excel. The Baran ratio column is an Excel formula, so it can be checked in the file.

wb <- createWorkbook()
addWorksheet(wb, "Panel")
heads <- c("Country", "Year", "GFCF (mn, national currency)",
           "Gross operating surplus & mixed income (mn, national currency)", "Baran ratio")
writeData(wb, "Panel", as.data.frame(t(heads)), startRow = 1, colNames = FALSE)
writeData(wb, "Panel", panel[, c("country", "year", "gfcf", "gos_mi")],
          startRow = 2, colNames = FALSE)

n <- nrow(panel); r <- 2:(n + 1)
writeFormula(wb, "Panel", startRow = 2, startCol = 5,
             x = sprintf('IF(AND(ISNUMBER(C%d),ISNUMBER(D%d)),C%d/D%d,"")', r, r, r, r))

addStyle(wb, "Panel", createStyle(textDecoration = "bold", fontColour = "white",
         fgFill = "#1F3864", wrapText = TRUE, halign = "center"), rows = 1, cols = 1:5)
addStyle(wb, "Panel", createStyle(numFmt = "#,##0.0"),  rows = r, cols = 3:4, gridExpand = TRUE)
addStyle(wb, "Panel", createStyle(numFmt = "0.000"),    rows = r, cols = 5,   gridExpand = TRUE)
setColWidths(wb, "Panel", cols = 1:5, widths = c(12, 8, 26, 34, 14))
setRowHeights(wb, "Panel", rows = 1, heights = 48)
freezePane(wb, "Panel", firstRow = TRUE)

addWorksheet(wb, "Graph")
insertImage(wb, "Graph", "baran_ratio_plot.png", width = 10, height = 5.5, units = "in")

saveWorkbook(wb, "baran_panel.xlsx", overwrite = TRUE)