p1ts-csv - Developer's Guide

Version 0.0.1 - December 26, 2017

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.

Overall Architecture

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.


1. Install

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.

install run

2. Run

As we saw above, when p1ts-csv is run, it will generate the following CSV files. Appendix A details their contents and includes examples.

We are dealing with the left half of the earlier overall architecture diagram, specifically this ...

architecture left

Running the p1ts-csv --help command prints out the possible options.

architecture right

2.1. Options

Option Default Definition and Use
--cars <list> (none)

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.

--host <addr> localhost

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.,

--dir <dir> .

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").

--sep <chart> ,

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).

--app <name> Excel

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.

  • "No value" is presented as =NA().
  • True and false are represented as TRUE and FALSE.

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.

--int <sec> 10

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.


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.

2.2. Desktop Shortcut

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.

  1. Right click p1ts-csv.exe in File Explorer and select Send to / Desktop (create shortcut).
  2. Right click the p1ts-csv - Shortcut icon on your desktop and select Properties. Edit its Target field to include the options you use. I've added --cars 28 here, 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.

2.3. Errors

The following Unauthorized message indicates that you must login to the P1TS server in a browser running on the same machine as p1ts-csv.

run unauthorized

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.

run connection refused

3. Excel

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.

architecture right

These examples are taken from p1ts-csv-hero-demo.xlsx.

3.1. CSV Import and Automatic Refresh

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.

  1. Start the wizard via the Excel menu Data / Get External Data / From Text.
  2. Select the cardata-laps-carnum.csv file for the carnum you are interested in, and Import.
  3. Text Import Wizard - Step 1 of 3: needs no changes. Our file type is Delimited text.
  4. Text Import Wizard - Step 2 of 3: change the delimiter from Tab to Comma.
  5. Text Import Wizard - Step 3 of 3: needs no changes. Press Finish.
  6. Import Data: press the Properties button.
  7. External Data Range Properties:
  8. Finally OK the Import Data dialog.
  9. Right click on the sheet's bottom tab and Rename the sheet to cardata.

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.

cardata sheet

3.2. Useful Formulas

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.

3.2.1. Find Each Lap's cardata Row

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 ① = $A:$A -
Lap number value (1, 2, etc.) for this ROW() in Fuel Calc ② = INDEX(①, ROW(), 1) 3 (cell A4)
cardata's lap number column (A) ③ = cardata!$A:$A -
Corresponding row number incardata sheet for lap ② ④ = MATCH(, , 0) cardata!$4:$4

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.

3.2.2. Find Each Lap's Driver

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.


driver column

The following table breaks down what is happening.

To find this ... Use this formula ... Produces ...
Entire cardata sheet (<999 laps) ① = cardata!$1:$999 -
This entire Fuel Calc sheet ② = $1:$999 -
Corresponding cardata_row number ③ = INDEX(②, ROW(),2) 4 (cell B4)
ln last name from cardata row ④ = HLOOKUP("ln", , , FALSE) Murcott

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.

3.2.3. Find Each Lap's Lap Time

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.


laptime column

3.2.4. Find Each Lap's Gal Remaining

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.

gal remaining column

The following summarizes the formulas used in each of the Gallons Remaining column.

Cell Formula Description
E2 =Gallons_Full-(3*Green_Gallons_per_Lap) 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.
E3 =E2-Green_Gallons_per_Lap Lap 2 is then lap 1's gallons remaining minus Green_Gallons_per_Lap.
E4, E5, etc. =E3-Green_Gallons_per_Lap 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.

3.2.5. Settings

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.

settings sheet

The Settings cells were named by selecting the cell, and then using Formula / Define Name to give it its cell name.

Cell Cell Name Formula Definition
B2 ¹ =HLOOKUP("trkn",snapshotcold!$1:$2,2,FALSE) Track name
B3 Miles_per_Lap =HLOOKUP("trkl",snapshotcold!$1:$2,2,FALSE) 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
B9 Green_Gallons_per_Lap =Miles_per_Lap/Green_Miles_per_Gallon 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
B12 ¹ Yellow_Gallons_per_Lap =Miles_per_Lap/Yellow_Miles_per_Gallon 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.


Appendix A. CSV File Structures

A.1. snapshotcold.csv

Contains relatively static information about the current session. This CSV will have a header row followed by a single row of values.

Col Type Definition
trkn Quoted String Track name
trkl Quoted String Track length
rund Quoted String Session description
dt Quoted String Session initialization date
tm Quoted String Session initialization time


  "Road Atlanta","2.54","ICTSC - Race","6 Oct 17","13:33:36.856"

A.2. snapshot.csv

Contains changing overall session information. This CSV will have a header row followed by a single row of values.

Col Type Definition
lr Integer Number of laps remaining in current session (always 9999 for IMSA)
tr Integer Number of milliseconds remaining in current session
t Integer Current time of day in milliseconds
st Integer Current session time in milliseconds
f Quoted String Session flag
l Integer Completed laps in current session



A.3. cardata-laps-carnum.csv

Contains detailed lap information about a single car. A separate CSV is produced for each of the p1ts-csv --cars argument list. Each CSV will have a header row followed by a multiple rows of values - one for each completed lap.

Col Type Definition
l Integer Completed lap number. Guaranteed to be the first column,
to facilitate Excel VLOOKUP by lap number.
c Quoted String Car number
cln Integer Car class number
cld Quoted String Car class description
f Quoted String Flag at lap completion
  • "Green" - Green flag
  • "Yellow" - Yellow flag
  • "Red" - Red flag
  • "Finish" - Finish flag
  • "" - No active session
di Integer Driver index (plug)
di Integer Driver index (plug)
fn Quoted String Driver first name
ln Quoted String Driver last name
p Integer Overall position at lap completion
cp Integer Class position at lap completion
lt Integer Lap time in milliseconds
tt Integer Session time in milliseconds at lap completion
pit Boolean TRUE if car was in pit lane on this lap, FALSE otherwise