+44 (0)2476 692 600



Build your own Race Strategy gapper tool

In our previous blog, we discussed how teams analyse F1 race strategy using race history or gapper plots. In this article we will explain how to build your very own gapper tool using Excel. This will allow you to create a gapper plot for any F1 race, so you can analyse different strategies and practice your race strategy skills.

Step 1: Data sources

As a student or a fan, access to live timing data can be hard to come by. In F1, it is streamed to the teams and to the F1 app, for those that subscribe. Fortunately, there are many sources of timing data that we can use instead. 

The FIA publish basic lap timing data for every F1 race weekend in PDF format on their website. For example, the data for the 2021 Abu Dhabi GP can be found here. This isn’t at the level of detail issued to teams, which includes full sector time and speed trap data, but it is more than adequate for developing a gapper tool.

F1 is not the only series to publish timing in this way. BTCC and British GT (and their support series) publish all of their timing via TSL Timing. Similarly, Alkamel publish World Endurance Championship timing, among other championships.

2021 Abu Dhabi F1 lap analysis
2021 Abu Dhabi F1 lap analysis. © 2021 Formula One World Championship Limited

Step 2: Importing data into Excel

We’ll use Excel because most people have access to it and it’s remarkably powerful. The bespoke software tools used by F1 teams are great, but they carry a huge overhead in cost, software writing and maintenance.

There are two timing PDFs we need: 1) The overall race classification 2) The race lap analysis. For this example we'll use the data from the 2019 Australian grand prix. A disclaimer here, this timing data is copyrighted by the FIA/FOM other than for journalistic use.

Most of the work is done using the race lap timing analysis document. Getting the text from this into Excel is straightforward. First, open the document in Adobe Acrobat Reader, select all (Ctrl A), copy and then paste into an Excel worksheet. This will give a list of text giving each lap time for each driver, as shown below.

2019 Australian grand prix timing data
Example of pasted timing data in Excel

There are many ways to convert this into a more useable format. 'Text to Columns' helps to split out the items in each line, you can write a VBA macro to do it more neatly or just manually type the data in.

Step 3: Processing the data

Ultimately, the aim is to have a set of data for each driver with lap-by-lap times and cumulative race time. In the example below, there are two columns of data per driver, the lap times are in the right-hand column and the cumulative race times are in the left.

However, there is a quirk in the race lap analysis document which means there is no first lap time given. We can calculate this by summing all of the other lap times given for each driver and then subtracting the total from the overall race time given in the overall classification document. These times are highlighted in yellow.

2019 Australian grand prix race timing data
Example race timing data split for each driver in Excel

Step 4: Plotting the data

Once the data is neatly compiled, you can do almost anything with it. For our purposes, we want to create a gapper plot and this means comparing the total race times to something. The most straightforward comparison is to a given car, such as the winner, which in this case was Valterri Bottas. For this, you simply subtract the desired car’s cumulative race times from all of the others and create the plot, as shown below.

Lap times relative to the race winner
Lap times relative to the race winner

The resulting formatted gapper plot
The resulting formatted gapper plot

This works equally well for any driver. It’s usual for a team to look at the race for each of its drivers individually when discussing how their races worked out. For example, for Gasly the race looked like this:

2019 Australian grand prix F1 race gapper pot relative to Gasly
Race gapper plot relative to Gasly

More normally, you can create a column of data representing the winner’s average lap time to use as the comparison, which is easy to do. First, take the winner’s race time and divide it by the number of race laps to get an average lap time and then sum this lap-by-lap. This is how the other gappers in this article are plotted.

Winners average lap time
Winners average lap time

Alternatively, you can select the first car to start each lap as the reference, by simply using the MIN command across each row. There are a lot of possibilities for you to try so feel free to experiment.

2019 Australian grand prix F1 gapper plot relative to lead car on each lap
Race gapper plot relative to lead car on each lap

The rest of the display is about colour coding and formatting. Once you have a working spreadsheet, you can create buttons and macros or write VBA code to do a lot of the heavy lifting for you. This is particularly useful at the start of a new season if there are team and driver changes to account for.

Creating the single lap displays to look at pit stop timing involves simply plotting a single row of the data in the table. Adding the greyed-out ghost data for the lapped cars requires a little more data manipulation, so why not try and see if you can do it for yourself.

Geoff Dymott
Written by: Geoff Dymott

Former Senior Performance Engineer Williams Racing F1


This product has been added to your cart