The p1ts-csv command line tool generates and then refreshes CSV files from P1TS server data, for use by Excel or other programs. Your spreadsheet can use the automatically updated CSV files to perform your custom fueling calculations, chart lap times, etc. for any cars you specify. In addition to describing p1ts-csv, this guide also describes how you might use it to power a basic fuel calculation spreadsheet, like p1ts-csv-hero-demo.xlsx.
This diagrams the overall architecture and its two subsystems.
While you most likely will install and run p1ts-csv locally on the
same PC as Excel, the P1TS server can be either remote (see
--host) or local
(reside on the same PC).
To use p1ts-csv you only need the following.
Although the p1ts-csv install and run examples shown are for Windows, p1ts-csv is also available for Mac and Linux.
On your Windows PC running Excel, either start Command Prompt (shown below) or Windows PowerShell. Use Search if you don't know where it is.
--carsoption. The 3 resulting files are also highlighted in green. More on running p1ts-csv in the next section.
As we saw above, when p1ts-csv is run, it will generate the following CSV files. Appendix A details their contents and includes examples.
--carsoption to specify a list of carnums.
We are dealing with the left half of the earlier overall architecture diagram, specifically this ...
p1ts-csv --help command prints out the
|Option||Default||Definition and Use|
Comma-separated list of car numbers (no spaces) whose detailed lap histories will be generated to cardata-laps-carnum.csv files. The keyword ALL (any casing) may be used to generate CSV for all the cars in the session.
If you intend to maintain a spreadsheet for say fuel calculations, you probably want to just specify your hero and sister cars.
The p1ts-csv makes network calls to request information from the P1TS Server. If you're running the P1TS Server on the same computer that's running p1ts-csv, there's no need to use this option, since it defaults to localhost, which means "this computer".
If you wish to use a P1TS server that is not running on the same computer that's running p1ts-csv, use this option and provide the P1TS Server's hostname (e.g., www.p1ts.com) or IP address (e.g., 22.214.171.124).
The generated CSVs will be created and refreshed in this specified output directory. It you wish to write to the "current working directory", there's no need to use this option, since it defaults to ., which means the "current working directory".
If you want the files to be generated in a specific output directory, provide that directory name here (e.g., "c:\Users\klin-m4700\p1ts-csv").
The fields in generated CSV files have a separator. If you wish to use comma, there's no need to use this option, since it defaults to comma.
If your application uses a different separator character, provide that character here (e.g., semicolon).
Various applications expect particular representations of special values, like "no value" and true/false values. If you plan on using Excel, there's no need to use this option, since it defaults to Excel.
If you are using a non-Excel program, supply any other string (e.g., "") to avoid =NA() from being emitted when a value is not available for that field.
p1ts-csv normally loops every <sec> seconds, to refresh the CSV files with the latest data from the P1TS server. If you want a 10 second refresh interval, there's no need to use this option since it defaults to 10.
If your P1TS server isn't expensive to access (e.g., not cellular data) and your PCs are not slugs, you can reduce the interval to even 1. For reference, the P1TS web app running in Google Chrome runs at 1 second refreshes from the P1TS Server.
Use CTRL+C to quit the program.
Use the special value 0 to generate the CSV files once, then quit.
Number of seconds to wait for P1TS server to send responses before aborting the request.
Quiets the p1ts-csv program so it does not print out any informational messages. In general there is probably no reason to use this option.
Once you've settled on p1ts-csv options, you'll probably use them repeatedly. You can do this via Windows Desktop Shortcut. See How to Create Shortcuts in Windows 10 at www.tomshardware.com.
--cars 28here, highlighted in blue. Press OK.
You can now double click this desktop shortcut rather than explicitly opening the Command Prompt and typing the p1ts-csv command with your options.
The following Unauthorized message indicates that you must login to the P1TS server in a browser running on the same machine as p1ts-csv.
The following Cannot connection message indicates that the specified host is reachable, however there is no P1TS server responding on that port. Make sure your P1TS server is running on that machine.
Excel is a data analysis tool used by engineers and strategists. Although I am not an Excel guru, I've assembled these commonly used instructions and formulas for how you might perform simple per-lap fuel calculations using p1ts-csv generated CSV files.
We now concentrate on the right half of the earlier overall architecture diagram, specifically this.
These examples are taken from p1ts-csv-hero-demo.xlsx.
First import each of the CSV files into individual sheets named snapshotcold, snapshot, and the hero car's lap data in cardata. You can of course give the sheets other names, just remember to adjust the names in the sample formulas below.
Perform the following steps to load cardata-laps-carnum.csv into a sheet in your Excel workbook. These steps are taken from Excel 2010, but later versions should be similar. A complete description is available from these Microsoft articles - Text Import Wizard, and Refresh connected (imported) data.
Repeat the import steps above for the snapshot.csv and snapshotcold.csv files if their data is useful to you.
The following screen shot shows the resulting cardata sheet in opening stages of the race, where the CSV contained only the first 3 laps.
The p1ts-csv-hero-demo.xlsx Excel workbook assumes you are creating custom fuel calculations in a sheet renamed to Fuel Calc which references each of the CSVs imported into individual sheets named snapshotcold, snapshot, and cardata. Do not deposit formulas or otherwise edit any of these sheets containing p1ts-csv data as they will be overwritten every 1 minute. Instead, perform your calculations in Fuel Calc or elsewhere.
The following Excel Lookup and reference functions will be useful.
You've noticed that the cardata row is organized by lap, where its first column (A) is its lap number labeled l. This first column placement will make it easier to lookup a cardata row's column values by using Excel's HLOOKUP (3.2.2).
☢ Do not assume every lap in Fuel Calc has its corresponding data in the identical row in cardata. Although this seems like a reasonable assumption, in real life there have occasionally been laps missing (even in IMSA's official CSVs). Therefore we must first search for the Fuel Calc row's lap number in cardata and determine its row number using MATCH.
Paste the following formula into all cells B2, B3, etc.
The following table breaks down what is happening in our particular call to MATCH.
|To find this ...||Use this formula ...||Produces ...|
|Lap number column (A) in Fuel Calc||
|Lap number value (1, 2, etc.) for this ROW() in Fuel Calc||
||3 (cell A4)|
|cardata's lap number column (A)||
|Corresponding row number incardata sheet for lap ②||
Notice that cells B5, B6, etc. have #N/A. This is because in our example, the cardata sheet had contained data for only the first 3 laps.
I changed the cardata_row's font color to gray, as this is an intermediate calculation that isn't directly interesting for purposes of Fuel Calc. You might also want to hide this column.
Now that we've found the cardata_row number for each of the Fuel Calc rows in cardata, we'll get information about each lap. We'll start by getting each driver's last name ("ln") and copy it into a Driver column in Fuel Calc. The cardata-laps-carnum.csv data is structured to lookup information by each lap's lap number, stored in its first column. This makes it easier to to find lap information via Excel's HLOOKUP.
Paste the following formula into all cells C2, C3, etc.
The following table breaks down what is happening.
|To find this ...||Use this formula ...||Produces ...|
|Entire cardata sheet (<999 laps)||
|This entire Fuel Calc sheet||
|Corresponding cardata_row number||
||4 (cell B4)|
|ln last name from cardata row||
Notice that cells C5, C6, etc. have #REF!. This is because in our example, the cardata sheet had contained data for only the first 3 laps, and our HLOOKUP formula references a cardata_row cell with #N/A.
Now that we've seen how to get the Last Name for each lap, getting the lap time ("lt") is very similar. One twist is that we divide each "lt" value, which is in milliseconds, by 1000 to get seconds.
Paste the following formula into all cells D2, D3, etc.
On the Fuel Calc sheet, column E will calculate the number of gallons of fuel that remain after the lap is completed. This sheet will reference cells named Gallons_Full and Green_Gallons_per_Lap which we'll defined in the Settings sheet described in the next section.
The following summarizes the formulas used in each of the Gallons Remaining column.
||Assumes that we full fill the car (Gallons_Full) and burn 2 formation laps plus lap 1 at a rate of Green_Gallons_per_Lap.|
||Lap 2 is then lap 1's gallons remaining minus Green_Gallons_per_Lap.|
|E4, E5, etc.||
||In Excel you can use your mouse to Copy cell E3 and paste it into E4 through E999 and it will automatically adjust column E's row numbers in the pasted cells.|
In the previous section we referenced cells named Gallons_Full and Green_Gallons_per_Lap. We've defined them here in a sheet we've named Settings, so they are out of the way of Fuel Calc.
The Settings cells were named by selecting the cell, and then using Formula / Define Name to give it its cell name.
||Track length in miles|
|B5||Gallons_Full||(user entry)||Number of gallons in a full tank|
|B6 ¹||Gallons_Empty||(user entry)||Number of gallons in an "empty" tank|
|B7 ¹||Green_Lap_Time||(user entry)||Number of seconds for a green lap|
|B8||Green_Miles_per_Lap||(user entry)||MPG for a green lap|
||Number of gallons used for 1 green lap|
|B10 ¹||Yellow_Lap_Time||(user entry)||Number of seconds for a yellow lap|
|B11 ¹||Yellow_Miles_per_Lap||(user entry)||MPG for a yellow lap|
||Number of gallons used for 1 yellow lap|
¹ Some of the named cells above are not actually used in p1ts-csv-hero-demo.xlsx (and are grayed) but may be useful for your own calculations.
Here are all the named cells (all from the Settings sheet) and 3 named sheets used. You can find it via Formula / Name Manager.
Contains relatively static information about the current session. This CSV will have a header row followed by a single row of values.
||Quoted String||Track name|
||Quoted String||Track length|
||Quoted String||Session description|
||Quoted String||Session initialization date|
||Quoted String||Session initialization time|
||Integer||Sector 01 length in inches|
||Integer||Sector 02 length in inches|
||Integer||Sector 03 length in inches|
trkn,trkl,rund,dt,tm,s01,s02,s03 "Road Atlanta","2.54","WeatherTech Championship - Race","12 Oct 19","15:08:39.810",22023,77961,60950
Contains changing overall session information. This CSV will have a header row followed by a single row of values.
||Integer||Number of laps remaining in current session (always 9999 for IMSA)|
||Integer||Number of milliseconds remaining in current session|
||Integer||Current time of day in milliseconds|
||Integer||Current session time in milliseconds|
||Quoted String||Session flag|
||Integer||Completed laps in current session|
Contains detailed lap information about a single car.
A separate CSV is produced for each of the p1ts-csv --cars
Each CSV will have a header row followed by a multiple rows of values -
one for each completed lap.
Note that the number of sector time columns is not necessarily
constant, so they have been placed rightmost -
S03 are pictured in the example below.
||Integer||Completed lap number. Guaranteed to be the
to facilitate Excel VLOOKUP by lap number.
||Quoted String||Car number|
||Integer||Car class number|
||Quoted String||Car class description|
||Quoted String||Flag at lap completion
||Integer||Driver index (plug)|
||Quoted String||Driver first name|
||Quoted String||Driver last name|
||Integer||Overall position at lap completion|
||Integer||Class position at lap completion|
||Integer||Lap time in milliseconds|
||Integer||Session time in milliseconds at lap completion|
||Boolean||TRUE if car was in pit lane on this lap, FALSE otherwise|
||Integer||Speed trap A's MPH x 1000 (176713 = 176.713 mph) or #N/A|
||Integer||Speed trap B's MPH x 1000 or #N/A|
||Integer||Sector 01 time in milliseconds|
||Integer||Sector 02 time in milliseconds|
||Integer||Sector 03 time in milliseconds|
l,c,cln,cld,f,di,fn,ln,p,cp,lt,tt,pit,spa,spb,s01,s02,s03 1,"10",2,"DPi","Green",1,"Renger","Van Der Zande",7,7,75532,75532,FALSE,#N/A,#N/A,9447,39790,25291 2,"10",2,"DPi","Green",1,"Renger","Van Der Zande",7,7,71502,147034,FALSE,#N/A,#N/A,8443,38023,25036 3,"10",2,"DPi","Green",1,"Renger","Van Der Zande",7,7,71514,218548,FALSE,#N/A,#N/A,8379,38044,25091