If you are in the asset maintenance business, you know how important it can be to accurately predict when a certain asset will require maintenance. If you are on a large budget, there are plenty solutions available, but if you have to rely on Excel, you are up for a challenge. Especially if you have to account for a maintenance history that does not quite follow the maintenance manual or if your schedule is a mix of use based and calendar based intervals, accurate predictions will soon become a nightmare. But not anymore! You can now make complex maintenance predictions from a simple Excel spreadsheet, for free!
How does it work?
In this post I will guide you on how you can accurately make a long term schedule prediction in Excel, together with the maintenanceman.nl API. The heavy lifting is not done in Excel but by the maintenanceman.nl API server. Therefore, it will require an active internet connection and a free subscription to the Maintenanceman.nl API, through rapidAPI.com. Other than that there are no requirements.
To get started, I already prepared a sample Excel spreadsheet for you to download. This spreadsheet already contains the Macro’s required to send requests to the API server and to process its response. The macro’s are required because Excel does not natively support everything that is required for sending and receiving proper API calls to an API server. At least not if you are on any other version than Excel 365. I may give some more explanation on how these Macro’s work under the hood, in a later post, but for the average user it’s fine to just know that they are there. You can download the example spreadsheet from the download link below.
Enabling macro’s
Once you have downloaded the example file to your hard drive and opened it in Excel, you will get a message, warning you that Excel blocked the execution of macro’s because the file comes from an untrusted source. (I.e. you downloaded it from the internet). Although this is a very legitimate message and you should indeed be extremely cautious with any unknown macro that you run from your hard drive, not enabling macro’s will mean that the spreadsheet will not work. How to enable excel macro’s is explained very well here, so we will not go over that in this post.
If you are not comfortable with enabling the excel macro’s and using a Google Spreadsheet instead of Excel is an option for you, then I would recommend my other post on using the maintenanceman.nl API from a Google Spreadsheet.
Signing up for the API
As already mentioned, this spreadsheet will only work if you have signed up for a free account at rapidAPI.com and subscribed to the maintenanceman.nl API. The basic plan is free and is suitable for most users. If you become a very heavy user, then you may want to scale up to a paid plan, but for most users, the free plan will easily suit their needs. Once you have subscribed, you can copy your personal X-RapidAPI-Key from the ‘Endpoints’ tab on the maintenancean.nl rapidAPI page, as shown in the below image. The X-RapidAPI-Key is a long string of seemingly random characters, that is used to link the API requests you make to your subscription.
Using the spreadsheet
Once you have downloaded and opened the example spreadsheet and enabled macro execution, you will see the basic spreadsheet as shown in the below picture, existing from the yellow header containing the ‘refresh’ button, the ‘Input’ section containing two tables with input data and a bottom section, containing as single table that will show the results of each query.
Entering the X-RapidAPI-Key
As you will notice, the results table at the bottom, will not contain any data, but only an ‘Invalid API Key’ message. This is of course because you did not yet enter the X-RapidAPI-Key that you obtained in the previous paragraph. You can now enter that in the appropriate field in cell C17. Once you entered the key correctly, the cell will no longer be highlighted in red. To confirm that everything works, you can now hit the refresh button in the yellow header, to verify that everything works correctly. Because the input section is already filled with example data, after you hit the refresh button, you will see result data showing up in the results table at the bottom of the spreadsheet. If this is not the case, then something has gone wrong in the steps before. Please make sure that you can successfully make the example request, before continuing.
Dissecting the example input
Now that we’re up and running, let’s look at the example input. The main input table contains the rules that your maintenance schedule is built from and exists out of five main sections. From left to right, the first section contains two columns that are used to identify the maintenance schedule rule. The Id field needs to be a unique identifier that may be used in other rules to refer to the current one. The ‘name’ field is only there for readability and contains a recognisable description of the rule.
The second section specifies the interval at which the maintenance schedule rule should be executed. This can be specified in a measure of use (i.e. a mileage, running hour measurement, or any other countable value that may indicate a need for maintenance.) or in a number of calendar days. This is specified in the columns ‘units’ and ‘orDays’. If both columns contain a value, the API will determine which one will occur first and use that for the output. The third column in the interval section named ‘after’ allows you to execute a rule only once, at X units after another rule is executed. The ‘after’ column should contain the ‘id’ of the rule that triggers the current rule. You can specify multiple id’s by separating them by comma’s.
The third section will manage possible resets of other schedule rules. This is done by entering the id’s of the rules to be reset in the ‘willReset’ column, separated by comma’s. Resetting a schedule rule means that the interval will restart counting from zero. This is particularly useful when a maintenance schedule rule includes the scope of another rule. The ‘preserveOnOverdueReset’ value can be either true or false and specifies how to handle resets of the current rule when the resetting rule is overdue. You can read more about that in this post.
The fourth section named ‘mergeLimit’ will allow you to specify the minimal gap to the next rule that will reset the current one. E.g. if your asset is due for an oil change, but a complete overhaul of the asset is less than 90 days away, you probably want to postpone the oil change. This is explained more thoroughly in my post about reset prevention and merging.
The final section specifies the actual current state of maintenance with the last execution date and value. The lastExecutedValue field course refers to the same unit of measure as the ‘units’ field in the ‘interval’ section.
To summarize the above, looking at the first rule in the example you see that it has ‘OIL’ as its unique identifier and it specifies an oil change that should be executed every 20000 units (km, miles, running hours, etc.) or every 365 calendar days. Whichever comes first. Also you see that the last actual execution of this rule took place on July 10th, 2023 at 83500 units. The second rule has ‘INT’ as unique identifier and specifies an intermediate service that has an interval of 35000 units, includes the scope of an oil change and therefore must reset ‘OIL’. The third rule specifies an adjustment that must be executed 5000 units after the intermediate service.
Although the example table only has seven rows, please note that you can extend the table to as many rows as you may require.
The second input table on the sheet, that you have already encountered while filling in the X-RapidIPA-Key, contains the general data of your query. Form top to bottom the input fields in this table are:
- X-RapidAPI-Key:
this is the API Key required for communication with the API server. - Period in years:
defines the time span for which you want to make your prediction. Please note that this value is automatically limited to 50 years. - period:
same as Period in years, but converted to days, because time measurement in the API is done in days. - unitsPerDay:
the average daily increase to assume for the measure of use that you defined for this schedule (km, miles, running hours, etc.). This is one of the main values to play with when simulating different scenario’s. - currentDate:
The most recent date that the measure of use (see: currentUnits) has been recorded. This does not necessarily have to be today’s date. - currentUnits:
the last known value for the measure of use. - dateZero:
the date of the first operation of the asset (if known). If this is left blank, the API will assume a date based on the other input.
Dissecting the results
Finally, after hitting the refresh button, at the bottom of the sheet you will find the results table as shown below. The table will show a simulation of all future maintenance actions to be performed in chronological order, following the schedule that was defined earlier in this post. The columns in the results table will look familiar as these were previously provided with the input data. From left to right we first encounter the ‘id’ and ‘name’ field which of course refer to the previously defined maintenance rules. Then we see the ‘value’ column, which will hold the ‘measure of use’ value (km, miles, running hours, etc.) at which a certain maintenance action ought to take place and next to that is the corresponding calendar date. The final three columns ‘year’, ‘dueAt’ and ‘dueDate’ are included for convenience. The ‘year’ column represents the year of the execution date. The ‘dueAt’ and ‘dueDate’ are only filled when a certain service interval is overdue and respectively represent the measure of use and the date at which the service should have been executed.
In the above example, you can see that the different types of services are predicted on regular intervals until the end of the specified simulation period. Because we specified the ‘Oil change’ to be executed at least every 365 days, and the unitsPerDay value does not reach the use based interval for the ‘Oil change’ within that period, the oil changes are just planned on an annual interval. Increasing the ‘unitsPerDay’ input value, will demonstrate the execution dates to come closer together when the value is increased and from a value of 55 units, the annual oil change interval will change to a used base interval accordingly.
Happy scheduling!
To wrap up the above; you can make as many copies of the original spreadsheet as you like as long as you do not add any named tables with names starting with ‘schedule’, ‘generalParameters’ or ‘resultsTable’. These are the table names that the aforementioned Macro’s will search for, as soon as you hit the ‘refresh button’. Otherwise adding tables is perfectly OK. Also you can perform any normal Excel action on the results table like creating a pivot table or VLOOKUP values.
Finally you have probably noticed the odd looking names for input fields like ‘unitsPerDay’ and ‘orDays’. These names are deliberately stated like this to be the same as the JSON field names in the actual API call that is made in the background. This facilitates a smoother transfer when you decide to use the same API in a more advanced solution like an ERP system or software.