Tyre Analysis Spreadsheet

sushifiesta

Champion Elect
Contributor
Grab a coffee and your reading glasses because this will probably be a long one!

Introduction

This is something that me and jez have been working on for a long time but I think we've both run out of time and energy to develop or maintain it any further. It would be a shame for it just to disappear however, so even though it isn't completely in the state that perhaps we were hoping for initially I will post it so others can try and use it.

Basically what we have done is to make an excel spreadsheet that allows the performance of the tyres to be analysed with very little work required apart from pasting in a few FIA and Pirelli documents. This may be comparing compounds, drivers, or teams for example. We have made a few articles already that will perhaps give a better idea of what we were trying to achieve and how we got to where we are now:
Instructions - Downloading and Using the Spreadsheet

NB: I will try and write these so anyone can understand them, but in practice I suspect that if you haven't got much experience with Excel you may struggle.
  1. Download the spreadsheet from here. You'll need to select the row the file's on then hit download on the right hand side.
  2. Open the spreadsheet and you will see that it already has the Hungary race data inserted. See below for entering your own.
  3. Race Lap Analysis worksheet: This contains all the data a person could wish for. The drop down lists accessed by hitting the arrows beside the row columns can be used to filter the data.
  4. Blue and Green highlighted worksheets: Unfortunately these may not work unless you have a recent version of excel (probably 2007 or 2010, but don't quote me on that). They contain graphs showing the degradation (blue) and fastest laps (green) for each of the compounds for drivers, teams and overall. Again, anything with an arrow beside it can be clicked to filter what you want to appear on the graph. Across the top you will see boxes like "Strange Lap Time", with "YES" deselected in the filter. This is so that any unusual lap times that could skew the results are not shown. If you are using an older version of excel you could try looking at "Chart1" and "Chart3" at the right end of the list of Worksheets, or use the Race Lap analysis worksheet etc. to create graphs from.
Instructions - Inserting Your Own Data

  1. Remove the old data:
    • Race History Worksheet - Select Column B and press delete.
    • Race Lap Analysis Worksheet - Select Column B and press delete.
    • Tyre Data Worksheet - Select Column C and press delete.
  2. Find and insert new data:
    • User Inputs Worksheet - This contains some circuit information and other variables that are used throughout the spreadsheet. A good source for this information is the James Allen Strategy Report.
    • Race History and Race Lap Analysis Worksheets - For these you will need the FIA's Race History and Race Lap Analysis documents, which are found here. Unfortunately the FIA only make these available for the last race, so if you want data for another race you will have to pester someone who has downloaded them before (ahem... Brogan... ahem). Once you have these, select all the text in the PDF (CTRL-A) then paste into the appropriate cell in the spreadsheet as instructed.
    • Tyre Data Worksheet - For this you will need to go to the Pirelli F1 news site, then search for something relevant to the race you're interested in (for example the circuit name or country). The article you want is basically a race summary with a title along the lines of "Button takes second win on Pirelli P Zero". Here's the Hungary one for reference. Once you've found it download the attached PDF, then select and copy the table with the tyre information (just the table, not the article etc. as well). It is important that you do this from the PDF and not the article itselft. Then paste this into the spreadsheet where indicated.
  3. Save the Spreadhseet - Some patience is going to be needed here because it takes a LOOOOOOOOOONG time for Excel to process everything. Some of the formulas to interpret and analyse the data are ridiculously long and complex. I'm not kidding when I say you could be looking at hours depending on the performance of your PC.
  4. Close and reopen the Spreadsheet - Now, finally, all the data and graphs should contain data from the race you've inserted. If the graphs don't work you may need to refresh the pivot tables found in the black highlighted worksheets.
Bugs and Imperfections

Remember I said the spreadsheet wasn't really finished? Well that means some things don't work, usually because I went and fiddled with something jez made and ended up breaking it. Some things I've noticed are the gaps to the car in front/behind on the first lap are not working, and the strange lap time flags don't always remove dodgy lap time sand do remove some ok times (this was particularly difficult to get right). Feel free to rant here and we may be able to help but I don't think either of us will be spending much more time on it. Also, be wary that you are unlikely to get any meaningful answers about the tyres from wet races as the track condition is changing all the time.

Tyre Analysis Threads

If anyone would be interested in creating a series of Tyre Analysis threads for each race using this spreadsheet then feel free to. Again, it was something we were probably planning on doing initially but we've run out of time and motivation, at least on my part.

The End!

I'll leave it at that for now, I haven't gone into much detail in the end so if you have questions ask away and we'll do our best to answer them. Finally, I'd like to thank jez for taking this so far and opening my eyes to some truly mind boggling excelery.
 
Excellent work.

I shall be having a look at this as soon as I have time and will run some of the other races through it to see what comes out.
 
I've done everything as instructed, and numbers have appeared. Unfortunately the Upload File button is on strike so I can't do much at the moment.

Also I couldn't find tyre wear information, so it is default, but probably wrong.
 
The tyre wear information doesn't really affect too much, it's just those three yellow cells on the user inputs sheet for the circuit info that are important. As for uploading we came across that before and it's probably not working because the file is too big! You can try uploading it at a different site then posting the link, I used Windows Live Sky Drive, or there's places like megaupload or rapidshare you can try (just google file upload I guess).

I've tried putting the Spa info myself and I noticed that the spreadsheet doesn't deal with the safety car period very well, it isn't removing all those laps from the analysis like it should. Just another bug to be aware of, I would try and fix them but it would end up being a full time job!

EDIT: Another thing - Pirelli have an annoying habit of changing the formats of their tables slightly. For Spa most of the names didn't have commas after, which the formulae on that sheet don't work properly. I fixed it by putting a comma in after the names manually. For the formulae to work the entries in column C should be this format:

Sutil: SN SN (13) MN (31) 2​
Senna: SU SN (1) DT (8) MN (21) 3​
Vettel: SU SN (5) SN (13) MN (30) 3​

The numbers in brackets are the lap of the pitstop, and the number at the end is the total number of stops. I can't remember whether I explained this already but the letters like SU, MN are the tyre compound. The N or U on the end tell you whether it's a New or Used tyre and the other letters tell you the compound, e.g. S = soft, M = medium, SS = super-soft, I = intermediate etc. Oh, and DT is a drive through.​
 
On some of the entries on the tyre worksheet it looks like there's more than one space between words for some reason, for example:
Button: MN SN (3) SN (13) SN (32) 3​

This means some of the very fussy (and poorly written by me...) Excel formulae think some of the stints are done on the (blank) tyre compound... If you remove those spaces then it should work properly and hopefully will end up with more data points on the graphs.​

The spreadsheet's a bit delicate to say the least...​
 
Couple more links for you, I thought I had a decent number of past races but it turns out I don't (apart from the ones already linked to in the OP).
toonchese - I think you might need to change the sharing settings on your new link? I'm getting the google docs sign on page when I click it at the moment :).

Edit: Working now! :thumbsup:
 
Sorry to keep noticing things lol, I hope you don't mind but it's good to work through them all here so everyone can see all the little annoying things... safe to say I've spent my fair share of time shouting at my laptop over the past couple of months....

Anyway, have you put in the Spa data on the Race History sheet? I notice there's still a lot of cells that aren't working and I think it might be because the Hungary data is still in the Race History sheet?

P.S. It goes both ways though because I just noticed I'd left the Hungary Circuit data in my version of the spreadsheet for Spa!
 
It's fine, best to have it working properly.

After 44 laps it shows the Hungary data, which isn't a huge problem, so I won't release a new one, but its something to bear in mind. Up to lap 44 it contains woking spa data.
 
It is only the race leader that is VALUE, that is because there is no gap to leader. If you manually insert a zero it corrects itself:

NEW CELL - 8 0 1:59.687
OLD CELL - 8 1:59.687
 
Sorry I should have made myself clear - I was talking about the Race Lap Analysis worksheet. If you look at Rosberg (starting row 312) and columns AL to AY, for example. Maybe it's just my laptop being weird and me being tired though lol.
 
Sorry I should have made myself clear - I was talking about the Race Lap Analysis worksheet. If you look at Rosberg (starting row 312) and columns AL to AY, for example. Maybe it's just my laptop being weird and me being tired though lol.
If you want I could take a look. I have some time tomorrow. Pm me with the issue(s)?
 
Back
Top Bottom