-1

I'm fairly new to the data integration part of R, and in this instance I am trying to convert monthly financial trial balances into a flat file such that it is ready to go for further analyses. The input data can be seen on the attached picture. It is monthly trial balances with opening and closing balances for each account, which is specified with a name and account number. The "training files" are all located in the same folder as is the working directory under the names "Jan19.xlsx", "Feb19.xlsx" and "Mar19.xlsx".

I want to merge the input data such that the closing balances for each month is listed horizontally. What needs to be specified in the commands is that e.g. in Mar19, a new account is added in the firm's trial balances, and this needs to be shown in the wanted outcome (as can be seen on the attached image). The value (closing balance) for each month before this needs to be set to zero. Anyone with an idea on how to solve this with R?

Help is much appreciated!

enter image description here

PC Enge
  • 1

1 Answers1

0

First read in the data.

library(readxl) # from the tidyverse to read excel files

filenames <- c('Jan19.xlsx', 'Feb19.xlsx')
# example with two files (can be easily extended)

# skip the first 5 files when reading the data
datalist <- lapply(filenames, function(z) read_excel(z, skip = 5))

Now merge them, make sure to merge by Account number, and don't forget all = TRUE or else you will do an inner join.

merged_data <- Reduce(function(x, y) merge(x, y, by= c('Account', 'Account name'), all = T), datalist)
merged_data[is.na(merged_data)] <- 0 # set NA to zero

Finally rename the columns.

colnames(merged_data)[-c(1:2)] <- sapply(filenames, function(z)unlist(strsplit(z, "[.]"))[1])
phuebner
  • 26
  • 3