Liveo Research: process TIN data

1 Purpose

This report processes raw TIN prices, visualizes trend behavior, computes a 3-month backward-looking trailing average, and summarizes key status indicators.

library(readr)
library(readxl)
library(dplyr)
library(lubridate)
library(ggplot2)
library(writexl)

data_dir <- "output/raw"
output_dir <- "output/processed"

dir.create(output_dir, recursive = TRUE, showWarnings = FALSE)

csv_files <- list.files(data_dir, pattern = "\\.csv$", full.names = TRUE)
xlsx_files <- list.files(data_dir, pattern = "\\.xlsx$", full.names = TRUE)

if (length(csv_files) > 0) {
  input_file <- csv_files[which.max(file.info(csv_files)$mtime)]
  df <- read_csv(input_file, show_col_types = FALSE)
} else if (length(xlsx_files) > 0) {
  input_file <- xlsx_files[which.max(file.info(xlsx_files)$mtime)]
  df <- as.data.frame(read_excel(input_file))
} else {
  stop("No CSV or XLSX input file found in: ", data_dir)
}

required_cols <- c("date", "price", "open", "high", "low", "vol", "perc_chg")
missing_cols <- setdiff(required_cols, names(df))

if (length(missing_cols) > 0) {
  stop("Missing required columns: ", paste(missing_cols, collapse = ", "))
}

df <- df %>%
  mutate(
    date = as.Date(date),
    price = as.numeric(price),
    open = as.numeric(open),
    high = as.numeric(high),
    low = as.numeric(low),
    vol = as.numeric(vol),
    perc_chg = as.numeric(perc_chg)
  ) %>%
  arrange(date)

2 Visualization: raw daily trend

ggplot(df, aes(x = date, y = price)) +
  geom_line(color = "#1f4e5f", linewidth = 0.7) +
    scale_x_date(date_labels = "%b %y", date_breaks = "1 month") +
  labs(
    title = "TIN raw daily price trend",
    x = "Date",
    y = "Price"
  ) +
  theme_minimal(base_size = 11)

3 Trailing average: 3-month backward-looking

monthly <- df %>%
  mutate(month = floor_date(date, unit = "month")) %>%
  group_by(month) %>%
  summarise(
    monthly_mean_price = mean(price, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  arrange(month)

monthly$ma_3m <- as.numeric(
  stats::filter(monthly$monthly_mean_price, rep(1 / 3, 3), sides = 1)
)

monthly

# A tibble: 10 × 3
   month      monthly_mean_price  ma_3m
   <date>                  <dbl>  <dbl>
 1 2025-07-01             33625.    NA 
 2 2025-08-01             33811.    NA 
 3 2025-09-01             34523. 33986.
 4 2025-10-01             36010. 34782.
 5 2025-11-01             36953. 35829.
 6 2025-12-01             41117. 38027.
 7 2026-01-01             49558. 42543.
 8 2026-02-01             49451. 46709.
 9 2026-03-01             47368. 48792.
10 2026-04-01             48645. 48488.

ggplot(monthly, aes(x = month)) +
  geom_line(aes(y = monthly_mean_price, color = "Monthly mean"), linewidth = 0.8) +
  geom_line(aes(y = ma_3m, color = "3-month trailing average"), linewidth = 1) +
  scale_x_date(date_labels = "%b %y", date_breaks = "1 month") +
  scale_color_manual(
    values = c(
      "Monthly mean" = "#8c3b2a",
      "3-month trailing average" = "#1f4e5f"
    )
  ) +
  labs(
    title = "TIN monthly mean and 3-month trailing average",
    x = "Month",
    y = "Price",
    color = NULL
  ) +
  theme_minimal(base_size = 11)

4 Reporting: KPI summary

latest_daily <- df[nrow(df), ]
latest_month <- monthly[nrow(monthly), ]

if (nrow(monthly) > 1) {
  previous_month <- monthly[nrow(monthly) - 1, ]
  mom_change <- (latest_month$monthly_mean_price - previous_month$monthly_mean_price) /
    previous_month$monthly_mean_price * 100
} else {
  mom_change <- NA_real_
}

kpi <- data.frame(
  indicator = c(
    "Latest date",
    "Latest daily price",
    "Latest monthly mean",
    "Latest 3-month trailing average",
    "Month-over-month change (%)",
    "Period start",
    "Period end",
    "Number of daily observations"
  ),
  value = c(
    as.character(latest_daily$date),
    round(latest_daily$price, 2),
    round(latest_month$monthly_mean_price, 2),
    round(latest_month$ma_3m, 2),
    round(mom_change, 2),
    as.character(min(df$date)),
    as.character(max(df$date)),
    nrow(df)
  )
)

knitr::kable(kpi)

indicator	value
Latest date	2026-04-20
Latest daily price	50623
Latest monthly mean	48644.64
Latest 3-month trailing average	48488.06
Month-over-month change (%)	2.69
Period start	2025-07-01
Period end	2026-04-20
Number of daily observations	196

5 Export processed data to Excel

export_file <- file.path(
  output_dir,
  paste0("TIN_processed_", format(Sys.Date(), "%Y%m%d"), ".xlsx")
)

write_xlsx(
  x = list(
    raw_data = df,
    monthly_with_ma3 = monthly
  ),
  path = export_file
)

export_file

[1] "output/processed/TIN_processed_20260422.xlsx"