Portfolio

Excel Template Importing Saved CSV/XML Data File

Financial Data Template

This Excel file is a simplified example of work done for a client who has several Excel projects that link multiple large workbooks (totaling dozens of megabytes). These workbooks are used as templates to gather data and perform analysis to provide financial consulting to their clients. In this example, instead of using one of the Excel projects that uses over 10 linked workbooks as my client does, a single and much abbreviated portion of the project is presented.

In practice, their client will use one of the large Excel projects to fill out financial data and then export it to a comma separated value (CSV) text file. The text file is exported using VBA (Visual Basic for Applications) which gathers the data from over 100,000 named ranges throughout the various workbooks. This text file can then be emailed to my client, the consultant, who then imports it into their version of the Excel project. As before, the VBA code then imports the file and assigns the values to the corresponding named ranges throughout the Excel project. Since the consultant's clients do not often have the ability to transfer large files, this method of transferring text files is done since they are much smaller and easy to manage.

The example file "Financial Data Sample.xlsm" is initially blank. You can enter data in the colored data entry fields and test the export process by going to the custom tab labeled "Budget Tool" and clicking on "Export CSV File". This button will run the VBA code necessary to create a CSV text file based on the information entered. It is not necessary to fill out every data entry field. To bring data into the file, the same procedure is followed, except of course, the "Import CSV File" button is clicked. This will bring up a dialog box to find the text file containing the financial data. I have included a text file named "Endres Reporting Solutions, LLC_2019.txt" containing random data to test this feature.

Another way to save data like this to a text file is to export it as XML data. While this is not how my client transfers data for their project, I have included this as a feature to show how it works since it is an alternative to CSV text. To the user it is very similar in nature to the CSV text file method, but behind the scenes it works differently. I have included a sample XML file named "Endres Reporting Solutions, LLC_2019.xml" containing random data to test this feature. The financial data is the same as used for the CSV text file.

There are pros and cons to both ways of saving the text data and which method you should use will depend on your project.

The files can be downloaded below (you will need to enable macros and enable editing in Excel):