Overview

I was asked by MinPlat to provide a discussion on effective use of Excel  by the Mining Companies.

There are few issues here:

1. Do Mining Companies use Excel?
2. Do they use Excel efficiently?
3. If they could use Excel efficiently, what would be the benefit?

Clearly the proposition here is that Mining Companies do use Excel, not use Excel efficiently, and there are multiple benefits to be discussed in much greater detail in this document.  The professional focus here is engineers and scientists.

Context

This discussion document is largely aimed at mining industry engineers and scientists, yet the document is certainly relevant to many other professional groups – and indeed nonprofessional readers who are computer-savvy.  The discussion document is also written largely from an Australian perspective where the author is more familiar with the education systems.  It is of course expected that the Australian scenario is similar to most other education systems.

Do Mining Companies use Excel?

Well… clearly the answer to this question is yes, but related sub-questions are how much do they use Excel, have they been trained in how to use Excel.

My answer is of course an opinion, and is largely based on providing courses to the Mining Industry in simulation and optimisation.

Perhaps a better question is: if a Professional Mining Company employee does not use Excel, then what would they use.

There are of course options:

• Matlab
• Relational databases.
• Advanced computer languages
• Customised software systems.

This document does not discuss these options in detail but does provide an overview

Matlab

Matlab commercial purchase is  quite expensive and requires good understanding of the system.  It is my opinion that some 40% would use Matlab at University and only <1% in industry (although in research the use of Matlab is much higher partly because licenses are much cheaper).

Relational databases –

In general, about 30% of professionals with computer science degrees are taught relational databases.  One can generally presumed that an engineering graduate has not been taught relational databases, and indeed one can often presume they are unaware of what a relational database is.  Hence relational databases are generally the realm of IT Departments (if they exist) and often disconnected from engineering problems.

Advanced computer languages. 

Computer languages have evolved substantially, so that whereas language understanding could be part of engineering and science courses it has become difficult to teach a language well within a course.  Consequently it appears that graduates are being exposed to languages less although specialised engineering courses (software engineering, electrical engineering) teach some languages  - yet they the language taught at graduate level may not be ideal for the Mining Company position.  It is my opinion that in general computer languages taught at University may not be the best language for their professional position.  Languages that are most often used in the workplace are:

• R
• Python
• C, C++, C#
• VBA

It is difficult to determine what languages are taught at University (although I think it is fair comment that VBA (which is freely available in Excel) is not taught, and may well be the most useful.

In this discussion document I focus on VBA; of course many of the points I raise about VBA are applicable to other languages.  Similarly it is expected that at some point a language will emerge that successfully replaces VBA as the embedded language of Excel.  However currently it is not obvious what that language will be.

Customised software systems

Customised software systems are of course systems developed by 3^rd parties.  In general such software systems are suitable if they are well-designed.  Often this is not the case, and users then also have to focus on integration.  Often integration is done via Excel, with csv files being both extremely common and extremely crude.

A common joke: What is the most important function someone would like in Power BI?  Answer: Export data to Excel.

Similarly 90% of all data input into Matlab is via Excel.

Summary

Excel is a powerful system.  It includes multiple functionality:

1. Data input
2. Data transfer
3. Charting
4. Data searches
5. Data linkages
6. Data modelling

The simple answer is that Excel in the Mining Industry is unavoidable.

 

Do Mining Companies use Excel efficiently?

Of course the answer to this question is no, yet we need to explore why this is the case.

Here I propose a number of reasons.

1. Excel is highly personal
2. Excel is multifunctional, and seen as a one-stop shop.
3. Excel is easy to learn
4. Excel is not taught at either University or High school
5. VBA is not taught
6. There is a general lack of understanding of software concepts.

I shall not explore the above remarks in detail as I consider them to be relatively self-evident.  It is enough to say that Excel is not taught effectively and Excel use is adhoc.  The effect of this adhoc approach is:

1. Spreadsheets that are very difficult to understand
2. Spreadsheets with errors
3. Spreadsheets that are highly manual.

I will explore all these 3 points.

Spreadsheets that are difficult to understand.

There are 2 subissues here.  The first is that Excel is highly personal.  One may well develop an Excel workbook for that persons use only.  The problem comes about when the workbook is to be ‘shared’ by colleagues.  I therefore consider a ‘Professional Excel developer’ one whose spreadsheet is used by more than 10 people.  If that is the case the spreadsheet should go through a review and modification process.

Here is a  summary of some basic skills:

1. Use formatting
2. Ensure there are consistent colour styles
3. Ensure there are meaningful named ranges
4. Avoid A1 notation in formulae. Use named ranges.
5. Avoid complicated formula.

Some more advanced skills include avoiding excessive lookup.

Identify where:

1. Excel tables should be used
2. Pivot tables should be used
3. a relational database should be used.

Avoiding errors

It follows that by focusing on making the spreadsheet user-friendly or transparent, that many errors are removed.  However error-reduction in itself is a skill.  Again I list here some basic skills.

1. Avoid retyping the same equation.
2. Use array equations
3. Use VBA for formulae.
4. Use templates
5. Use addins.

It is almost impossible here to provide appropriate explanation of these skills, and is indeed why a Professional Excel Course has been developed by the author.

However  a key issue is that only about 10% of engineers in the Mining Industry even know what VBA is, with perhaps less than 1% of engineers using VBA.

VBA is Visual Basic For Applications.  It is a language that has origins in Visual Basic, but has largely been modified to:

1. Integrate object-oriented approaches
2. Allow integration of software systems.
3. Automate many Excel tasks.

VBA did not exist in the first Excel but was introduced in 1993.  There are issues surrounding VBA – notably that VBA is no longer being developed by Microsoft (in favour of VB .Net); yet VBA still exists in Excel.

If one does develop code in VBA, it is possible to convert the code to VBA .Net, although I would suggest this is done by a professional programmer or software developer because VBA .Net is too a bit challenging for novices.  Here I simply focus on VBA.  VBA allows common functions (or subroutines) to be coded.  This means to call the same equation one simply re-calls the VBA function.

What this means in practise is that the equation is created once only.

In other words the more common approach of retyping the same equation is highly error-prone. Indeed it can be argued that an approach of retyping the same equation will inevitably lead to error.

Hence it is often argued that 93% of spreadsheets contain errors:

And that Excel developers think that:

Only 10% of spreadsheets contain errors

That is most errors are not so much undetected, rather have never been subject to basic checks.

Error detection systems

Error-detection systems are emerging as a means to detect and correct Excel errors.

These systems have a clear place.  However strategies that rely on error-detection systems rather than good practice are of limited value.

Hence the best error-detection systems are used in conjunction with good practice.

Automation

Automation is the process in which manual tasks are replaced by computer-language based processes.  Here the focus is VBA.

It is almost impossible to reconcile that in 2018 with the advent of supercomputers that many users (perhaps 95%) of Excel still use manual methods.

Yet it becomes clear that this is because automation methods for Excel have not been taught at either University or High School.

The key concept is to ‘think’ mathematically.

Think mathematically

Unfortunately our education systems have largely failed when it comes to mathemat6ics teaching.  Of course this statement is arguable; yet I am now convinced that this is the case, as evidenced by the poor use of excel by many graduates of our mathematics curricula.

Students learn mathematics; but do not learn to ‘think’ mathematically.

What this means in practice is that students go through a process where they are taught various mathematical techniques such as calculus; and exams and assignments are largely permutations of the taught framework.  Seldom are students given a problem, and then asked to identify the problem as a mathematical problem with clearly defined inputs, outputs and parameters.  That is students focus on mathematical solution rather than mathematical formulation.

If one exercises their mathematical formulation ability it then becomes straightforward to identify problems that can be solved by the same techniques.  This is the foundation of automation.

Mathematics as the foundation of automation

That is a mathematical problem, or indeed a process that is repeatable should only be specified once; automation should be used on any repeats of the defined problem or the process.

VBA is the foundation of automation.  A well designed VBA application can reduce task from months to seconds.

Flowcharting

There is much ‘hype’ about two buzz-phrases:

• Data Science
• Data Analytics

With very little explanation of what these phrases mean.

Flowcharting is the process in which one describes both dataflow and process flow.

It needs to be clear that here ‘process flow’ can mean either:

The process itself.

i.e. processing of ore to create a concentrate, or the process in which calculations is made.  The latter we can describe as a dataflow.  Dataflows are not necessarily the same as actual process flow.

Both the user and developer of an Excel system should be able to understand both dataflow and the actual process-flow by considering a flowchart.

There are two main flowchart tools I recommend: Excel itself and Visio.

By constructing a flowchart one may well be able to identify where calculation steps are repeated, and then this provides the basis of identifying VBA subroutines or functions that should be created.

Current status of flowchart-based process modelling system

The author has developed a flowchart-based process modelling system.  This is not yet released, but will be the basis of an online course to be developed in 2019.  Expressions of interest are welcome.

Summary

This document discusses many issues related to effective use of Excel.

Two related documents are available on request.

 SEES – Structured Efficient Excel Standards

Professional Excel Course.

Also, the following link gives a youtube video outline of the course and covers many of the issues discussed in this document.

https://www.youtube.com/watch?v=7b0TQBXqbWA&t=430s&list=PL6kzwYj1Ss_FRU_0oDFB5wYUaf5GVYi_8&index=16

Thus far we have outline benefits of using Excel effectively in order to:

1. Make user-friendly workbooks
2. Reduce error
3. Automation

This document has not covered all the benefits and methods available.

In brief:

1. Once a user is proficient in Excel they can start integrating with other systems such as a relational database.
2. Once a user can construct models then this approach provides a framework for intellectual understanding of these models; the user no longer needs to rely on 3^rd party models that may be valid.

Note that the author is also currently developing a database integration course.

Final summary

The final question to be addressed is what are the benefits to the Mining Companies?

Whilst we have discussed issues such as:

• User-friendliness
• Reduced errors
• Automation

These advantages can be considered fairly general.

If Excel is used efficiently then a whole world opportunity opens up.  This includes:

• Develop methods to effectively analyse mine data
• Understand mining resources
• Develop strategies for plant optimisation
• Develop project management systems
• Facilitate structured data exchange between groups.
• Develop Maintenance schedules.

I consider it fair comment that a Company that does not use Excel efficiently cannot itself be an efficient Company.