Using Excel Power query for Construction Billing & Data Mining

By -

 This blog published automating construction billing during 2013. It is tried and tested and customises, nested ifs, data validation, Vlookup and workbook protection skills to meet the accounting department's requirements. Excel since then has transformed into analyzing and visualizing the big data. Construction projects have also transformed. 2013 was the time when the dashboard became fashionable, it served the purpose.

Now is the time for low code, no-code development —dashboard remains in the front but the real value and not just automating a semi-manual billing that was inherited; what present-day site monitoring and management requirements are much beyond. Construction quality and safety as demanded today can be met by freeing the site engineers from all possible non-related activities. And meeting the requirements of construction data mining. 

 Why today's IT pro and construction software tools won't work? Many reasons can be listed; probably our not knowing what present-day low code no-code tools can do is the most important and sums it all. 

Just look at an example where I have put in the construction bills in the simplest form. Now when an additional bill is approved say is converted to a CSV or pdf -- all billing data is updated; yes— bye bye copying pasting or doing anything else: Download the folder and check it up yourself. If it does not work check-up with your Excel version. I tested it on Version before I put up this. 

You can download the step by step method and the sample data folder here.

ForConstructionpros.com informs that a big project today generates an average of 130 million emails, 55 million documents and 12 million workflows. And we now require to integrate these organization-wide.

Surely, the construction industry; Indian construction industry, in particular, is not ready. The new Excel offers all these and more.

This blog would further explore how the same construction billing requirements can be fitted into low code, no code model and provide insights into project monitoring and lessons learnt which can be utilised while on the go.



Comments

  1. seoprofessionalFebruary 11, 2021 at 11:01 PM

    This comment has been removed by a blog administrator.

    ReplyDelete
  2. RentHire.inFebruary 12, 2021 at 8:44 AM

    You have shared a nice article here about the hire charges of construction machinery. Your article is very informative and nicely describes the causes and natural remedies of Snoring. I am thankful to you for sharing this article here. hire charges of construction machinery India

    ReplyDelete
  3. Office Setup HelpMarch 1, 2021 at 8:00 AM

    Power queries are really powerful. Thank you for sharing this interesting application of MS Excel. Learning from lengthy guides at office.com/setup is really tedious, looking forward for these articles.

    ReplyDelete

Post a Comment

Popular posts from this blog

Application for Making Parabolic Camber Board

By -
This Excel Application Calculates the Points for making a parabolic camber Board and draws a chart for the half road section profile. You are required to enter the road width and the % slope. The parabolic shape is given so that the profile is flat at the middle and steeper towards the edges, which is preferred by the fast moving vehicles as they have to frequently cross the crown line during overtaking operations.  on a two lane highway. Download Application here.
Read more

Decision Making Through Life Cycle Costing- A Simple Practical Example

By -
Decision Making Through  Life Cycle Costing- A Simple Practical Example Problem Statement: To compare the life cycle costing by calculating the single Present  Value for different scenarios. A choice is required to be made for air conditioning by Installing AC only Installing AC along with insulation works and solar film . The variables would be the Initial costs and the Cost of Capital. The link below would take you to the Excel worksheet wherein the comments in each cell would make you understand the process fully. It is assumed that the user is a knowledge worker and the input parameters have been evaluated through experts. Excel Workbook has been set in a manner that is fit for a novice excel user. Download The Excel File Related Article - THE ECONOMY OF ENERGY EFFICIENT BUILDINGS; AN INDIAN PERSPECTIVE
Read more

Using Excel conditional formatting for comparative statements

By -
Image
Project Control: Using Excel Conditional Formatting for guiding rate negotiations in Comparative Statement. Project control requires that the various work packages are carried out as planned. Civil construction is typically based on approved building plan and specifications. In view of progressive elaboration of project, Item rate tenders are most prevalent and provide the transparency and flexibility to both the owner and contractor. Projects are always mired with some uncertainty and items and their quantities often deviate. Various known and unknown factors affect the prices quoted by a contractor. Success of the project greatly depend on the Project’s ability to negotiate the contracts at a mutually win-win cost, performance and time frame even in the face of uncertainties. Practice of defining a Project/ work package through a Priority matrix is a handy tool for exploring on the negotiation possibilities. Time Performance
Read more