A data wrangling case with spreadsheets using R

PUBLISHED ON JAN 16, 2018 — R, TIDYVERSE

A typical real world situation is the problem of collecting data from a set of files and structured into one dataset. “Why data is sparse?” you can think, well this could happen for multiple reasons. For example, some phenomenon are measured along time units and you can only access their data by a period, others are a collection of business units and the origin’s data was not centralized. So you end with one file per (time or business) unit in your hands.

Another typical situation is that data is shared or is avaiable in excel files. That is because excel is the main used analytical tool in the world (I guess!) and many of us didn’t have a class of best practices on how to share and document data. The problem with excel files are the “spreadsheet intrinsic issues”. What is that?…is just a fancy way to call the messy format problems that are common to find in spreadsheet files like multiple header levels, more than one table per sheet, different data types values in same column, and the list goes on.

You can look by yourself what I am trying to say in the following magistral example, but first some context. Below of this paragraph you can find a series of tweets that belong to very well known data scientists. The discussion is about a challenge to turn this spreadsheet (a xlsx file) into a tidy data and David Robinson (the challenged) deal with the problem in a very simple and fluently way.

The rest of the post is dedicated to highlight some steps of David Robinson’s answer to the tweet challenge, and then apply the insights on a case of data contained in multiple “xlsx” files with many sheets.

Step 1: The coordinate-view

Consider the next innocent table as an example.

It’s easy to import the above spreadsheet with the readxl::read_excel function and obtain a rectangular table in R, filled with missing values (NA) instead of blank cells. You can take a look of the first six rows.

library(readxl)
df <- readxl::read_excel("./innocent_table.xlsx")
df
## # A tibble: 6 x 6
##   X1                   X2    X3    X4    X5    X6
##   <chr>             <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Expanded Homicid~    NA    NA    NA    NA    NA
## 2 Murder Victims       NA    NA    NA    NA    NA
## 3 by Weapon, 2004-~    NA    NA    NA    NA    NA
## 4 Weapons            2004  2005  2006  2007  2008
## 5 Total             14210 14965 15087 14916 14180
## 6 Total firearms:    9385 10158 10225 10129  9484

The important thing here is to describe explicitly the position of each cell-value that belong to the spreadsheet into the R’s data frame structure–in row \(i\) and column \(j\) you find the cell-value \(x_{i,j}\)–and the following function, written and used by David Robinson in his answer, reorganize data accordingly.

library(dplyr)
library(tidyr)
tidy_excel <- function(x) {
  # x is a data imported from an excel file with readxl::read_excel
  #   function.
  # Return a data frame with the coordinate view representation.
  x %>% 
    setNames(seq_len(ncol(x))) %>% 
    mutate(row = row_number()) %>% 
    tidyr::gather(column, value, -row) %>% 
    mutate(column = as.integer(column)) %>% 
    group_by(row) %>% 
    filter(!all(is.na(value))) %>% 
    group_by(column) %>% 
    filter(!all(is.na(value))) %>% 
    ungroup() %>% 
    arrange(column, row)
}

tidy_excel(df)
## # A tibble: 138 x 3
##      row column value                         
##    <int>  <int> <chr>                         
##  1     1      1 Expanded Homicide Data Table 8
##  2     2      1 Murder Victims                
##  3     3      1 by Weapon, 2004-2008          
##  4     4      1 Weapons                       
##  5     5      1 Total                         
##  6     6      1 Total firearms:               
##  7     7      1 Handguns                      
##  8     8      1 Rifles                        
##  9     9      1 Shotguns                      
## 10    10      1 Other guns                    
## # ... with 128 more rows

As you can observe, the cells of the spreadsheet are melted into one column (value) and two new index-variables are created, one by each dimension (row and column), to mapping a cell coordinate of the spreadsheet with his content. This is the reason of the section title part “coordinate-view”.

A legitimate question now is why can be useful a coordinate-view? What is the advantage with the original form?

A: It’s possible to take an advantage to manipulate data based on his template structure and find regions with relevant data in the spreadsheet using filter operations over rows and columns.

In fact, tidy_excel apart of reshape data into the coordinate-view is also an example of this. Pay attention to the following code snippet from the function definition of tidy_excel.

# ...from the definition of tidy_excel
  group_by(row) %>%  # group by row index
  filter(!all(is.na(value))) %>%  # discard empty rows (row-groups that contain only NA values)
  group_by(column) %>%  # group by col index
  filter(!all(is.na(value))) # discard empty cols (col-groups that contain only NA values)

We can give more life to the above answer with an example. Imagine we want to know the index of the columns that their name contains the word “Weapon” and years between “2004” and “2008” from the previous table. In this example, the data is very small and you can know the answer just by looking. But suppose that we have multiple spreadsheets like this containing different period of years. In that case it would be useful a way to identify the row-index that contains the column names and deal with the particularities of each spreadsheet.

# the column names are in the 4 row-index
df
## # A tibble: 23 x 6
##    X1                  X2    X3    X4    X5    X6
##    <chr>            <dbl> <dbl> <dbl> <dbl> <dbl>
##  1 Expanded Homici~    NA    NA    NA    NA    NA
##  2 Murder Victims      NA    NA    NA    NA    NA
##  3 by Weapon, 2004~    NA    NA    NA    NA    NA
##  4 Weapons           2004  2005  2006  2007  2008
##  5 Total            14210 14965 15087 14916 14180
##  6 Total firearms:   9385 10158 10225 10129  9484
##  7 Handguns          7286  7565  7836  7398  6755
##  8 Rifles             403   445   438   453   375
##  9 Shotguns           507   522   490   457   444
## 10 Other guns         117   138   107   116    79
## # ... with 13 more rows

# identify keywords in the column names
column_names_pattern <- "Weapons|2004|2005|2006|2007|2008"

# filter the coordinate view of data based on the previous key-words
df %>% 
  tidy_excel %>%  # apply the coordinate-view
  filter(stringr::str_detect(value, column_names_pattern))  # use regex to detect cell that satisfy a pattern
## # A tibble: 7 x 3
##     row column value               
##   <int>  <int> <chr>               
## 1     3      1 by Weapon, 2004-2008
## 2     4      1 Weapons             
## 3     4      2 2004                
## 4     4      3 2005                
## 5     4      4 2006                
## 6     4      5 2007                
## 7     4      6 2008

The above code is just a logical filter operation over data. In other words, we give to the dplyr::filter function a logical vector (TRUE / FALSE) of the same length as number of rows and return only rows in which the index is TRUE. The coordinate view allow us to know directly in which row and column the relevant values are located in the spreadsheet. If we pay atention to the row variable you can see that the header content is located in the fourth row.

How we generate the logical vector with stringr::str_detect is the following step.

Step 2: Make your shot with regex

“A regular expression (or just regex) is a sequence of character that define a search pattern.” (Wikipedia)

A data wrangling case with excel files

This is a short case that deal with the problem described previously, we have many spreadsheet files and we want to put all together into one dataset. The data has information of all corporate bonds issues and current debt status of bonds on the chilean local fixed-income market. This information is public on the website of the “Comisión para el mercado financiero (CMF)” to download in xlsx files here.

The goal is to use the insights that were highlighted from David Robinson answer to make a spreadsheet into a tidy data and to generalize for when we have more than one file. An important feature of this set of files is that it contains information of the same phenomenon (bond issued) on different periods of time.

We have three xlsx files corresponding to information of the years 2013, 2014 and 2015. Each of these files contain more than one sheet because data is reported in a monthly frequency. So we use the purrr package to apply operations over many sheets at the same time (if you don’t know how purrr works check Jenny Bryan’s tutorial)

The first thing to do is explore each file and the number of sheet it has using readxl::excel_sheets.

# first we load the packages used during the analysis
library(dplyr)
library(tidyr)
library(stringr)
library(purrr)
library(readxl)


files <- list.files("./post_data/a_data_wrangling_case_with_spreadsheets_using_r", full.names = TRUE)
files
## [1] "./post_data/a_data_wrangling_case_with_spreadsheets_using_r/deb_2013.xlsx"
## [2] "./post_data/a_data_wrangling_case_with_spreadsheets_using_r/deb_2014.xlsx"
## [3] "./post_data/a_data_wrangling_case_with_spreadsheets_using_r/deb_2015.xlsx"

sheet_per_file <- map(files, readxl::excel_sheets)
sheet_per_file
## [[1]]
##  [1] "Enero"      "Febrero"    "Marzo"     
##  [4] "Abril"      "Mayo"       "Junio"     
##  [7] "Julio"      "Agosto"     "Septiembre"
## [10] "Octubre"    "Noviembre"  "Diciembre" 
## [13] "Hoja13"    
## 
## [[2]]
##  [1] "Enero"      "Febrero"    "Marzo"     
##  [4] "Abril"      "Mayo"       "Junio"     
##  [7] "Julio"      "Agosto"     "Septiembre"
## [10] "Octubre"    "Noviembre"  "Diciembre" 
## 
## [[3]]
##  [1] "Enero 2015"      "Febrero 2015"   
##  [3] "Marzo 2015"      "Abril 2015"     
##  [5] "Mayo 2015"       "Junio 2015"     
##  [7] "Julio 2015"      "Agosto 2015"    
##  [9] "Septiembre 2015" "Octubre 2015"   
## [11] "Noviembre 2015"  "Diciembre 2015"

Now we need at least two arguments for read_excel to bring each excel sheet into R:

  1. path to the xlsx file
  2. name of the sheet to read.

A possible way to build this pair of arguments are the cross product between the variables files and sheet_per_file. But before that, the cruel world gives us an inconvenient, the first file contain more than twelve sheets and, as we know, a year has twelve months…so there are some useless sheets (Hoja13).

# for each sheet name vector we discard the names that aren't relevant
pattern <- c("Enero|Febrero|Marzo|Abril|Mayo|Junio|Julio|Agosto|Septiembre|Octubre|Noviembre|Diciembre")
relevant_sheet_name <- map(sheet_per_file, 
                           ~ .x[str_detect(.x, pattern)])

We continue with the cross product (purrr::cross) and we obtain a nested list of 36 elements (arg_list), in which each element of the list has the two arguments necessaries to read each sheet. Below you can see the first two pairs of arguments to feed read_excel.

arg_list <- map2(files, relevant_sheet_name, ~ list(.x, .y))
arg_list <- map(arg_list, purrr::cross)
arg_list <- purrr::flatten(arg_list)
head(arg_list, n = 2)
## [[1]]
## [[1]][[1]]
## [1] "./post_data/a_data_wrangling_case_with_spreadsheets_using_r/deb_2013.xlsx"
## 
## [[1]][[2]]
## [1] "Enero"
## 
## 
## [[2]]
## [[2]][[1]]
## [1] "./post_data/a_data_wrangling_case_with_spreadsheets_using_r/deb_2013.xlsx"
## 
## [[2]][[2]]
## [1] "Febrero"

Now two things will happen, first we will iterate a list (arg_list) over a function (guess which one?…read_excel) to read each sheet and store in a list called raw_data. Then we will give a name to each element (a dataframe) of the output list raw_data. We need this metainformation to identify each dataframe with his corresponding file and sheet. By using regex we extract from each arg_list element the year and month from the first and second argument respectively. Look how the sheets are in raw_data, you can see the name after the dollar sign.

raw_data <- invoke_map(read_excel, arg_list)

# add metainformation from the names of the files and sheet into the name
# of elemenet list
list_names <- map(arg_list, ~ paste(str_extract(.x[[1]], "[0-9]+"), 
                                    str_extract(.x[[2]], "[aA-zZ]+"), sep = "_"))

names(raw_data) <- list_names
head(raw_data, n = 2)
## $`2013_Enero`
## # A tibble: 1,127 x 25
##    `DETALLE DEUDA ~ X__1  X__2  X__3  X__4  X__5 
##    <chr>            <chr> <chr> <chr> <chr> <chr>
##  1 BONOS CORPORATI~ <NA>  <NA>  <NA>  <NA>  <NA> 
##  2 al 31 de enero ~ <NA>  <NA>  <NA>  <NA>  <NA> 
##  3 <NA>             <NA>  <NA>  <NA>  <NA>  <NA> 
##  4 <NA>             <NA>  *VAL~ <NA>  <NA>  2280~
##  5 Rut              <NA>  Soci~ "Tip~ Nº I~ Fech~
##  6 76675290         K     AD R~ Línea 694   40884
##  7 76675290         K     AD R~ 1E    694   40892
##  8 94272000         9     AES ~ Línea 516   39395
##  9 94272000         9     AES ~ 1E    516   39400
## 10 94272000         9     AES ~ Línea 517   39395
## # ... with 1,117 more rows, and 19 more
## #   variables: X__6 <chr>, X__7 <chr>,
## #   X__8 <chr>, X__9 <chr>, X__10 <chr>,
## #   X__11 <chr>, X__12 <chr>, X__13 <chr>,
## #   X__14 <chr>, X__15 <chr>, X__16 <chr>,
## #   X__17 <chr>, X__18 <chr>, X__19 <chr>,
## #   X__20 <chr>, X__21 <chr>, X__22 <chr>,
## #   X__23 <chr>, X__24 <chr>
## 
## $`2013_Febrero`
## # A tibble: 1,131 x 25
##    X__1  `DETALLE DEUDA ~ X__2  X__3  X__4  X__5 
##    <chr> <chr>            <chr> <chr> <chr> <chr>
##  1 <NA>  BONOS CORPORATI~ <NA>  <NA>  <NA>  <NA> 
##  2 <NA>  al               41333 <NA>  <NA>  <NA> 
##  3 <NA>  <NA>             <NA>  <NA>  <NA>  <NA> 
##  4 <NA>  <NA>             *VAL~ <NA>  <NA>  2283~
##  5 Rut   <NA>             Soci~ "Tip~ Nº I~ Fech~
##  6 <NA>  <NA>             <NA>  <NA>  <NA>  <NA> 
##  7 7667~ K                AD R~ Línea 694   40884
##  8 7667~ K                AD R~ 1E    694   40892
##  9 9427~ 9                AES ~ Línea 516   39395
## 10 9427~ 9                AES ~ 1E    516   39400
## # ... with 1,121 more rows, and 19 more
## #   variables: X__6 <chr>, X__7 <chr>,
## #   X__8 <chr>, X__9 <chr>, X__10 <chr>,
## #   X__11 <chr>, X__12 <chr>, X__13 <chr>,
## #   X__14 <chr>, X__15 <chr>, X__16 <chr>,
## #   X__17 <chr>, X__18 <chr>, X__19 <chr>,
## #   X__20 <chr>, X__21 <chr>, X__22 <chr>,
## #   X__23 <chr>, X__24 <chr>

It is necessary to clean raw_data before we collapse it into one dataset. We want to discard rows with meta-information and select all the columns until column “Valor Par (en miles de $)”. So first we need to detect the row that contain the column names of the table in the spreadsheet, then the name of our last column, and finally the last row that contain values (rewrite this part of the paragraph). We can do this by applying regular expressions on coordinate view and encapsulate all this process into the function clean_spreadsheet to apply it simultaneously over each dataframe.

clean_spreadsheet <- function(df) {
  tbl <- tidy_excel(df)
  last_col <- tbl %>% 
    filter(str_detect(value, "[Vv][Aa][Ll][Oo][Rr] [Pp][Aa][Rr]")) %>% 
    select(column) %>% 
    max()
  last_row <- tbl %>% 
    filter(str_detect(value, "[Tt][Oo][Tt][Aa][Ll]")) %>% 
    select(row) %>% 
    min()
  first_row <- tbl %>%
    filter(str_detect(value, "[Rr][Uu][Tt]")) %>% 
    select(row) %>% 
    min()
  sub_tbl <- tbl %>%
    filter(row <= last_row - 1, column <= last_col,
           row >= first_row + 1) %>% 
    tidyr::spread(column, value) %>% 
    select(-row)

}

clean_data <- purrr::map(raw_data, clean_spreadsheet)
head(clean_data, n = 2)
## $`2013_Enero`
## # A tibble: 962 x 19
##    `1`   `2`   `3`   `4`   `5`   `6`   `7`   `8` 
##    <chr> <chr> <chr> <chr> <chr> <chr> <chr> <ch>
##  1 7667~ K     AD R~ Línea 694   40884 U.F.  4000
##  2 7667~ K     AD R~ 1E    694   40892 U.F.  2000
##  3 9427~ 9     AES ~ Línea 516   39395 US$   200~
##  4 9427~ 9     AES ~ 1E    516   39400 U.F.  2800
##  5 9427~ 9     AES ~ Línea 517   39395 US$   400~
##  6 9427~ 9     AES ~ 1E    517   39400 U.F.  5600
##  7 9427~ 9     AES ~ 2E    517   39905 US$   196~
##  8 7612~ 3     Agro~ Línea 678   40801 U.F.  8500
##  9 7612~ 3     Agro~ 1E    678   40802 U.F.  5000
## 10 7612~ 3     Agro~ 1E    678   40802 $     100~
## # ... with 952 more rows, and 11 more variables:
## #   `9` <chr>, `10` <chr>, `11` <chr>,
## #   `12` <chr>, `13` <chr>, `14` <chr>,
## #   `15` <chr>, `16` <chr>, `17` <chr>,
## #   `18` <chr>, `19` <chr>
## 
## $`2013_Febrero`
## # A tibble: 965 x 19
##    `1`   `2`   `3`   `4`   `5`   `6`   `7`   `8` 
##    <chr> <chr> <chr> <chr> <chr> <chr> <chr> <ch>
##  1 7667~ K     AD R~ Línea 694   40884 U.F.  4000
##  2 7667~ K     AD R~ 1E    694   40892 U.F.  2000
##  3 9427~ 9     AES ~ Línea 516   39395 US$   200~
##  4 9427~ 9     AES ~ 1E    516   39400 U.F.  2800
##  5 9427~ 9     AES ~ Línea 517   39395 US$   400~
##  6 9427~ 9     AES ~ 1E    517   39400 U.F.  5600
##  7 9427~ 9     AES ~ 2E    517   39905 US$   196~
##  8 7612~ 3     Agro~ Línea 678   40801 U.F.  8500
##  9 7612~ 3     Agro~ 1E    678   40802 U.F.  5000
## 10 7612~ 3     Agro~ 1E    678   40802 $     100~
## # ... with 955 more rows, and 11 more variables:
## #   `9` <chr>, `10` <chr>, `11` <chr>,
## #   `12` <chr>, `13` <chr>, `14` <chr>,
## #   `15` <chr>, `16` <chr>, `17` <chr>,
## #   `18` <chr>, `19` <chr>

Are the different dataframes of the same dimensions? We are interested that each dataframe has the same number of column because they need to have the same type of information, evidently they could have different number of rows (more or less bonds as observations).

purrr::map(clean_data, ~ ncol(.)) %>% 
  unlist() %>% 
  table()
## .
## 17 19 20 
##  3 31  2

Approximately a 86% of the data has 19 columns, then it is make sense to focus on the case of with 19 columns. The other two cases require to compare columns to find the missing or extra column but this extends the scope of this post (focus on the big picture).

data <- clean_data %>% 
            purrr::keep(~dim(.)[2] == 19) %>% 
            purrr::imap(~ mutate(.x, periodo_reporte = .y)) %>% 
            bind_rows() %>% 
            mutate(mes = str_extract(periodo_reporte, "[^_\\a]+$"),
                   anho = str_extract(periodo_reporte, "^[^_\\D]+")) %>% 
            select(anho, mes, `1`:`19`)

col_names <- c("anho", "mes", "rut", "dv", "sociedad", "tipo_bono", "num_inscripccion", "fecha_inscripccion",
  "unidad", "monto_inscrito_miles", "serie", "tasa_emision", "objetivo_emision1",
  "objetivo_emision2", "objetivo_emision3", "anhos_vencimiento", "valor_nominal_inicial", "valor_nominal_vigente",
  "valor_nominal_reaj", "int_dev_no_pagado", "valor_par")

names(data) <- col_names
data
## # A tibble: 33,261 x 21
##    anho  mes   rut   dv    sociedad tipo_bono
##    <chr> <chr> <chr> <chr> <chr>    <chr>    
##  1 2013  Enero 7667~ K     AD Reta~ Línea    
##  2 2013  Enero 7667~ K     AD Reta~ 1E       
##  3 2013  Enero 9427~ 9     AES Gen~ Línea    
##  4 2013  Enero 9427~ 9     AES Gen~ 1E       
##  5 2013  Enero 9427~ 9     AES Gen~ Línea    
##  6 2013  Enero 9427~ 9     AES Gen~ 1E       
##  7 2013  Enero 9427~ 9     AES Gen~ 2E       
##  8 2013  Enero 7612~ 3     Agrosup~ Línea    
##  9 2013  Enero 7612~ 3     Agrosup~ 1E       
## 10 2013  Enero 7612~ 3     Agrosup~ 1E       
## # ... with 33,251 more rows, and 15 more
## #   variables: num_inscripccion <chr>,
## #   fecha_inscripccion <chr>, unidad <chr>,
## #   monto_inscrito_miles <chr>, serie <chr>,
## #   tasa_emision <chr>, objetivo_emision1 <chr>,
## #   objetivo_emision2 <chr>,
## #   objetivo_emision3 <chr>,
## #   anhos_vencimiento <chr>,
## #   valor_nominal_inicial <chr>,
## #   valor_nominal_vigente <chr>,
## #   valor_nominal_reaj <chr>,
## #   int_dev_no_pagado <chr>, valor_par <chr>

The result is a data with 21 columns and 33261 observations. We are almost ready, but before it’s over, put your attention into the variable fecha_inscripccion(This variable’s name is translate to “inscription date”).

data %>% 
  select(fecha_inscripccion)
## # A tibble: 33,261 x 1
##    fecha_inscripccion
##    <chr>             
##  1 40884             
##  2 40892             
##  3 39395             
##  4 39400             
##  5 39395             
##  6 39400             
##  7 39905             
##  8 40801             
##  9 40802             
## 10 40802             
## # ... with 33,251 more rows

These numbers doesn’t look as a date but actually it’s how excel stores the dates. David Robinson use the following function to clean these values.

convert_excel_date <- function(x) {
  # created by David Robinson
  result <- as.Date("1900-01-01") + as.numeric(x) - 2 
  ifelse(is.na(result), x, as.character(result))
}

data <- data %>% 
            mutate(fecha_inscripccion = lubridate::ymd(convert_excel_date(fecha_inscripccion)))

data %>% 
  select(fecha_inscripccion)
## # A tibble: 33,261 x 1
##    fecha_inscripccion
##    <date>            
##  1 2011-12-07        
##  2 2011-12-15        
##  3 2007-11-09        
##  4 2007-11-14        
##  5 2007-11-09        
##  6 2007-11-14        
##  7 2009-04-02        
##  8 2011-09-15        
##  9 2011-09-16        
## 10 2011-09-16        
## # ... with 33,251 more rows

Finally we have a clean dataset ready for you to analyze. You can take a look to a few observations!

anho mes rut dv sociedad tipo_bono num_inscripccion fecha_inscripccion unidad monto_inscrito_miles serie tasa_emision objetivo_emision1 objetivo_emision2 objetivo_emision3 anhos_vencimiento valor_nominal_inicial valor_nominal_vigente valor_nominal_reaj int_dev_no_pagado valor_par
2013 Enero 76675290 K AD Retail S.A. 1E 694 2011-12-15 U.F. 2000 A 6.05 RP FI NA 9.5 2000000 2000000 45615080 986849 46601929
2013 Enero 94272000 9 AES Gener S.A. 1E 516 2007-11-14 U.F. 2800 O 3.4 FI MNE NA 7.5 1200000 1200000 27369048 153787 27522835
2013 Enero 94272000 9 AES Gener S.A. 1E 517 2007-11-14 U.F. 5600 N 4.0999999999999996 FI MNE NA 21 4400000 4400000 100353176 678889 101032065
2013 Enero 94272000 9 AES Gener S.A. 2E 517 2009-04-02 US$ 196000 Q 8 FI NA NA 10 196000000 102200000 48181168 1260130 49441298
2013 Enero 76129263 3 Agrosuper S.A. 2E 678 2012-08-17 U.F. 3500 E 3.5 RP NA NA 5 1500000 1500000 34211310 498450 34709760
2013 Enero 76129263 3 Agrosuper S.A. 1E 679 2011-09-16 U.F. 5000 D 3.8 RP FI NA 21 5000000 5000000 114037700 1802612 115840312
2013 Enero 61808000 5 Aguas Andinas S.A. 4E 305 2005-12-09 U.F. 5000 F 4.1500000000000004 RP NA NA 21 5000000 3684210 84027767 575282 84603049
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 527 2008-04-08 U.F. 2500 G 3 FI NA NA 6 2500000 2500000 57018850 565969 57584819
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 580 2009-05-13 U.F. 3000 I 3.7 FI NA NA 6.5 2000000 1360000 31018254 189543 31207797
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 580 2009-05-13 U.F. 3000 J 4 FI NA NA 9.5 1000000 1000000 22807540 150560 22958100
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 629 2010-03-30 U.F. 4000 K 20.9 FI NA NA 6.5 1000000 840000 19158334 183868 19342202
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 630 2010-03-30 U.F. 4000 M 4.2 FI NA NA 21 1750000 1750000 39913195 553037 40466232
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 654 2011-03-31 U.F. 4400 N 3.17 FI NA NA 5 1250000 1250000 28509425 298893 28808318
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 655 2011-03-31 U.F. 4400 P 3.86 FI NA NA 22.5 1500000 1500000 34211310 436012 34647322
2013 Enero 61808000 5 Aguas Andinas S.A. 2E 655 2011-12-14 U.F. 1650 Q 4 FI NA NA 20.5 1650000 1650000 37632441 248424 37880865
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 712 2012-03-28 U.F. 3500 R 3.3 RP FI NA 7 1000000 1000000 22807540 248845 23056385
2013 Enero 61808000 5 Aguas Andinas S.A. 1E 713 2012-03-28 U.F. 3500 S 3.9 RP FI NA 23 2300000 2300000 52457342 675406 53132748
2013 Enero 76030156 6 Aguas Nuevas S.A. 1E 502 2007-06-26 U.F. 3750 A 4 RP NA NA 21 2500000 2500000 57018850 483041 57501891
2013 Enero 99586130 5 Almendral Telecomunicaciones S.A. 1E 432 2005-08-19 U.F. 5800 A 3.5 RP NA NA 10 5800000 1919998.08 43790433 196195 43986628
2013 Enero 59123340 8 América Movil, S.A.B. de C.V. 1E 474 2009-04-13 U.F. 4000 A 3 FP FI NA 5 4000000 4000000 91230160 679170 91909330
2013 Enero 59123340 8 América Movil, S.A.B. de C.V. 4E 474 2010-05-25 U.F. 8000 D 4 FI NA NA 25 5000000 5000000 114037700 847969 114885669
2013 Enero 59181460 5 Banco Pine S.A. 1E 738 2012-11-30 U.F. 1500 A 6 FP NA NA 5 1500000 1500000 34211310 289158 34500468
2013 Enero 96528990 9 Banmédica S.A. 1E 408 2005-03-14 U.F. 2000 A 3 FI NA NA 8 1200000 150000 3421131 73390 3494521
2013 Enero 96528990 9 Banmédica S.A. 1E 409 2005-03-14 U.F. 1500 B 4 FI NA NA 21 800000 533334 12164037 347915 12511952
2013 Enero 96528990 9 Banmédica S.A. 1E 528 2008-04-09 U.F. 1700 C 2.75 FI RP NA 5 700000 700000 15965278 329585 16294863
TAGS: R, TIDYVERSE
comments powered by Disqus Go th the home page