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!