Excel programming of a consolidation of real estate data

Development of a mechanism for data aggregation and automation of a control file

Knight Frank is one of the world's leading real estate consultancies. The valuation company commissioned eWorks to program an Excel 2010 tool that brings together existing individual results in a general overview. An interface for automated calculations was also implemented.

Calculation automation uses a control file to copy and apply the calculation schema to a valuation object. Up to 5 files can be selected in a file selection dialog, which are used to calculate an object valuation. The data is imported, analyzed and prepared for the valuation process. The import process can be repeated as often as required by the user.

In the second step, a program was developed that brings together the individual results for each object in a general overview. Using the open folder dialog, the user selects the source directory in which the relevant files are located. All subfolders in the source directory are searched and the relevant files are processed. The defined key figure fields are then determined and transferred to the overall overview.

The development for Microsoft Excel 2010 was carried out using Excel macro programming and VBA (Visual Basic for Applications). eWorks supported its client in the implementation of the Excel tool, starting with the planning and Excel programming of the VBA macros through to the handover and commissioning of the Excel solution. Following the installation, eWorks is available to its client for operating questions, possible error corrections (warranty) and expansion requests.