How can I grab data from line items in various Excel formatted and called differently on each one e.g. financial statements for myltiple companies?
Is it possible to post an example of how the raw file looks like and what is the expected output?
Hi Monnica, and welcome to the Community!
Typically, you would need to rename similar lines in different statements to the same (standard) name. Which means that in an EasyMorph project you would need two things:
-
Load from an Excel spreadsheet or create a reference table with 2 columns where in each row the description of a statement line corresponds to a standard line name. For instance:
| C.O.G.S. | Cost of goods sold | -
Load the statement and use he “Lookup” action (which does what he VLOOKUP function does in Excel) that replaces non-standard line descriptions with standard ones. You can read more about the “Lookup” action in our tutorial and web-help.
Once you replaced non-standard descriptions in different statements to standard ones, you can consolidate statements from multiple companies.
I hope, I understood your question correctly. An example requested above by @vaibhavtandon87 would be helpful too.
Here is a simplistic example:
Goal: get the data for Sep 2019 and Dec 2019 (columns)
File 1:
Column title: Q3 and Q4
Row line name: Net Operating Income
File 2:
Column title: Sep 2019 and Dec 2019
Row line name: Net Income
Here is a sample project in EasyMorph that makes files from your example uniform.
To select a file, change the value of parameter {File name} - pick either File1 or File2.
The project has 2 mappings - one for columns, one for rows. The mappings are defined in Mappings.xlsx on two sheets. You can cover all possible renamings in one mapping file and use it to standardize any statements.
Note that values in “Old name” must be unique.
finance-statement-standartization.zip (21.6 KB)
