# Load required libraries
library(httr)
library(jsonlite)
library(dplyr)
library(tidyr)
library(openxlsx)
library(purrr)
library(stringr)
library(writexl)

# Define the request URL
url <- "https://app.bde.es/bierest/resources/srdatosapp/listaSeries?idioma=en&series=DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL,DEEQ.N.ES.W1.S1.S1.T.B.SD._Z._Z._Z.EUR._T._X.N.ALL,DEEQ.N.ES.W1.S1.S1.T.B.OS._Z._Z._Z.EUR._T._X.N.ALL&rango=MAX"

# Make the request
response <- GET(url)

# Check whether the request was successful
if (status_code(response) == 200) {
  data <- content(response, as = "parsed", type = "application/json")
  print("Data retrieved successfully")
} else {
  stop(paste("API call error:", status_code(response)))
}

## [1] "Data retrieved successfully"

# View the JSON returned by the API
# (only the structure of the first series)
# In R, the JSON response is converted into a list,
# so its structure is displayed with str()
# instead of showing the complete JSON.
str(data[[1]], max.level = 1)

## List of 11
##  $ serie           : chr "DEEQ.N.ES.W1.S1.S1.T.B.G._Z._Z._Z.EUR._T._X.N.ALL"
##  $ descripcion     : chr "BP. Goods. Balance"
##  $ descripcionCorta: chr "BP. Goods"
##  $ codFrecuencia   : chr "Q"
##  $ decimales       : int 0
##  $ simbolo         : chr "M€"
##  $ informacion     :List of 12
##  $ fechaInicio     : chr "1993-01-01T09:15:00Z"
##  $ fechaFin        : chr "2026-01-01T09:15:00Z"
##  $ fechas          :List of 133
##  $ valores         :List of 133

# Extract the unit of measurement for each series
get_unidad <- function(info) {
  unidad <- info %>%
    keep(~ identical(.x$titulo, "Units")) %>%
    map_chr(~ .x$descripcion, .default = NA_character_)

  if (length(unidad) == 0) NA_character_ else unidad[1]
}

# Convert the JSON response into a data frame
df <- map_df(data, ~ tibble(
  series       = .x$serie,
  description = .x$descripcion,
  frequency  = .x$codFrecuencia,
  unit      = get_unidad(.x$informacion),
  decimals   = as.integer(.x$decimales),
  date       = as.Date(substr(unlist(.x$fechas), 1, 10)),
  value       = as.numeric(unlist(.x$valores))
))

# Display the first rows
head(df)

## # A tibble: 6 × 7
##   series                  description frequency unit  decimals date        value
##   <chr>                   <chr>       <chr>     <chr>    <int> <date>      <dbl>
## 1 DEEQ.N.ES.W1.S1.S1.T.B… BP. Goods.… Q         Mill…        0 2026-01-01 -11867
## 2 DEEQ.N.ES.W1.S1.S1.T.B… BP. Goods.… Q         Mill…        0 2025-10-01 -12125
## 3 DEEQ.N.ES.W1.S1.S1.T.B… BP. Goods.… Q         Mill…        0 2025-07-01 -15050
## 4 DEEQ.N.ES.W1.S1.S1.T.B… BP. Goods.… Q         Mill…        0 2025-04-01  -9343
## 5 DEEQ.N.ES.W1.S1.S1.T.B… BP. Goods.… Q         Mill…        0 2025-01-01 -12448
## 6 DEEQ.N.ES.W1.S1.S1.T.B… BP. Goods.… Q         Mill…        0 2024-10-01 -10713

# Export data frame to Excel
write_xlsx(df, "df_api_bde_example_r.xlsx")