Excel Pro Logo

Development Services

A great result, every time

Automation

Automation can save a huge amount of time and money by freeing up your staff to concentrate on their core functions. As well as taking care of repetitive tasks Excel automation can also yield increased accuracy by eliminating human error. Almost any process can be automated, some examples:

  • Copying from dozens of seperate workbooks into a central register. I can automate such a process, freeing up time for you to concentrate less on administration and more on your core business.

  • Checking stock prices online with Google Finance. I can automate the retrieval of hundreds of prices at once, allowing almost real time visibility of the markets.

  • Creating hundreds of charts from survey data. Production of any number of complex charts can be fully automated.

  • Integrating data from numerous departments and dashboards. I can produce self-maintaining dashboards that act as the central source of truth for your business, reading from many different sources.

Automation image

When it comes to automation the possibilities are almost limitless. Leveraging the power of Microsoft Office's native scripting language (VBA) Excel can be made to automate almost any task and it's power isn't limited to the host application - Excel can control any other Office application and work with any files on your hard drive or network including non native formats like .PDF and .txt.


Formulas and User Defined Functions

Getting a complex formula to work can be tricky. With a huge amount of experience I can craft just about any formula you can imagine and if Excel's native functions can't get the job done I can create a custom one for you with VBA.

If you think you'll need help regularly consider time banking, or perhaps some training.


Visual basic For Applications (VBA)

VBA is a powerful and mature programming language built into the core of almost all Microsoft Office applications. With it a skilled developer can make Excel do just about anything, from retrieving data from the web to producing full screen applications that look like stand-alone software. Whether you need to automate a process, do something you can't do with Excel's built in functions or create an interactive report I have the expertise to help realise your vision. Check out the portfolio page for some great ideas.


Analysis and Modelling

Data is always useful, even if it's use is not immediately apparent. I have proven experience in helping clients get to grips with their data and realise it's potential. From target setting, to trending to financial Monte Carlo simulations I have the expertise to help you gain useful and profitable insights from your data.


Image of man entering data
Data Validation

Data validation is an important multi-stage process designed to ensure the accuracy and consistency of your data, whether it's input manually or derived from another data source. Poorly controlled data can cause many problems down the line, limiting your ability to gain meaningful insights through analysis and making automation difficult. It's always best to control what is being entered right from the start, though if need be the data can be cleansed before a validation system is introduced.

Good validation ensures that typos can't be made, that dates are in an appropriate format, that data isn't entered into a field inappropriately, that data derived from external sources is the correct data. This may not seem all that important at first but consider if you wanted to report on what states or countries your customers come from so that you can better target your advertising; if a state or country has a number of spelling errors you can't easily sum the numbers for that item, you'll need to cleanse the data first.

Validation can also save time in the form of drop down lists, predictive text and more.



Migration

Whether moving data between applications or updating legacy code in old workbooks the migration process needs to be carefully managed to ensure your valuable data remains intact.

An effective migration strategy ensures your data is kept safe while you benefit from it's new home. I can help you consider every aspect of the move.


Data Cleansing

Without good validation processes data quality issues are a fact of life and can be time consuming to correct. They limit your ability to gain meaningful insights from your data and prevent easy automation. Using a combination of Excel know-how and VBA I can do away with any number of errors in short order using sophisticated custom algorithms.


SQL Databases

A common mistake that can cause a lot of problems is to use Excel as a permanent store for large quantities of data. If necessary I can set up an Access database for you to store the data and link it with an Excel front end for manipulation or to display charts and tables.

If your business already uses SQL Server I can automate the process of data retreival, and better yet - filter the data retrieved based on user input from a simple interface in Excel. I've even created Access style visual query designers so users who have no knowledge of SQL can still effectively query the database with very little training.


User Interface and Experience

A great Excel application should be powerful, informative and easy to use. There should be no need for complicated instructions and it should be obvious how to use the application from it's presentation.

Excel development is part science and part art and the influence of a good clean design should not be underestimated. I consider a good aesthetic to be fundamental to a great user experience; charts should be easy to interpret, colors should be pleasing, tables well laid out and so on.

A poor design will obfuscate the message in your data whilst a great design will help provide insight.

It's often the case that the easier something is to use on the surface the more complex it needs to be behind the scenes. I've many years' experience of balancing front end simplicity with back end sophistication.


Image of a woman using a laptop in a cafe

Web Queries, Scraping and Automation

Using VBA I can automate file downloads, data entry and many other common tasks on the web. Data can be retrieved from any website where it is stored in a tabular format e.g. Google Finance. Many websites that provide data adopt a tabular format. If you have an idea for something you'd like to retrieve from the web with automation then get in touch, I'd be happy to help.

Even if a webpage doesn't have a tabular layout I can often retrieve the data for you by parsing it's contents, this is known as web scraping.


Pivot Tables, Slicers and Data Cubes

Pivot tables and slicers are great for quickly prototyping ideas. Learning to use them is fairly quick and easy, learning to master them... not so much! I can open up the full power of these useful tools for you.


Flexible Time Banking

I offer a flexible time block system for customers who like to create their own solutions but need professional advice from time to time to help troubleshoot issues, make small amendments to existing formulas or VBA code, or would like a little instruction here and there. Simply book an amount of time and contact me whenever you need help. An account is opened time taken to answer your question is deducted from the time block.