p1ts-csv - Developer's Guide

Version 0.0.6 - December 12, 2019

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.

The left (pink) p1ts-csv subsystem generates CSV files. This is covered in section 2. Run.
The right (green) Excel subsystem uses the CSV files as a starting point for any custom calculations. This is covered in section 3. Excel.

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

Requirements

To use p1ts-csv you only need the following.

The p1ts-csv command line program. This can be any modest PC.
Login to a running P1TS server. It can be running on your PC (default), or accessible over the network.

1. Install
2. Run
3. Excel
- 3.1. CSV Import and Automatic Refresh
- 3.2. Useful Formulas
A. CSV File Structures

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.

The yellow steps show how we've made a folder named p1ts-csv, using mkdir, underneath our user home folder (mine is c:\User\klin-m4700) and copied p1ts-csv.exe from E: (e.g., a USB drive). You can use any folder you like. You can perform the equivalent steps in File Explorer.
The green command ran p1ts-csv, asking that detailed cardata be created for car number 28 via the required --cars option. The 3 resulting files are also highlighted in green. More on running p1ts-csv in the next section.
We pressed CTRL key and C simultaneously (magenta) to stop its refresh loop.

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.

snapshotcold.csv - Single file with relatively static information about the current session. No options are needed to generate this file.
snapshot.csv - Single file with changing overall session information. No options are needed to generate this file.
cardata-laps-carnum.csv - Multiple files, each with detailed lap history about its carnum. Use the --cars option to specify a list of carnums.

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

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

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., 34.194.62.247).
`--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.
`--timeout`	2	Number of seconds to wait for P1TS server to send responses before aborting the request.
`--quiet`		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.

Right click p1ts-csv.exe in File Explorer and select Send to / Desktop (create shortcut).
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.

When you double click, Windows will automatically open a Command Prompt window and type your command and options.
You can close the Command Prompt window when you are done without having to type CTRL+C.

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.

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.

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.

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.

Start the wizard via the Excel menu Data / Get External Data / From Text.
Select the cardata-laps-carnum.csv file for the carnum you are interested in, and Import.
Text Import Wizard - Step 1 of 3: needs no changes. Our file type is Delimited text.
Text Import Wizard - Step 2 of 3: change the delimiter from Tab to Comma.
Text Import Wizard - Step 3 of 3: needs no changes. Press Finish.
Import Data: press the Properties button.
External Data Range Properties:
- Uncheck Prompt for the file name on refresh so Excel won't pester you every minute.
- Check Refresh every and set the time to 1 minute.
- You may later experiment with Refresh data when opening the file.
Finally OK the Import Data dialog.
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.

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.

HLOOKUP Looks in the top row of an array and returns the value of the indicated column
INDEX Uses an index to choose a value from a reference or array
MATCH Looks up values in a reference or array
ROW Returns the row number of a reference

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.

=MATCH(INDEX($A:$A,ROW(),1),cardata!$A:$A,0)

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.

=HLOOKUP("ln",cardata!$1:$999,INDEX($1:$999,ROW(),2),FALSE)

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.

=HLOOKUP("lt",cardata!$1:$999,INDEX($1:$999,ROW(),2),FALSE)/1000

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.

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.

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
`s01`	Integer	Sector 01 length in inches
`s02`	Integer	Sector 02 length in inches
`s03`	Integer	Sector 03 length in inches

Example:

  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

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

Example:

  lr,tr,t,st,f,l
  9999,6904000,50114000,296000,"Green",3

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. Note that the number of sector time columns is not necessarily constant, so they have been placed rightmost - S01, S02, and S03 are pictured in the example below.

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
`veh`	Quoted String	Vehicle 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)
`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
`spa`	Integer	Speed trap A's MPH x 1000 (176713 = 176.713 mph) or #N/A
`spb`	Integer	Speed trap B's MPH x 1000 or #N/A
`s01`	Integer	Sector 01 time in milliseconds
`s02`	Integer	Sector 02 time in milliseconds
`s03`	Integer	Sector 03 time in milliseconds

Example:

  l,c,cln,cld,veh,f,di,fn,ln,p,cp,lt,tt,pit,spa,spb,s01,s02,s03
  1,"10",2,"DPi","Cadillac DPi","Green",1,"Renger","Van Der Zande",7,7,75532,75532,FALSE,#N/A,#N/A,9447,39790,25291
  2,"10",2,"DPi","Cadillac DPi","Green",1,"Renger","Van Der Zande",7,7,71502,147034,FALSE,#N/A,#N/A,8443,38023,25036
  3,"10",2,"DPi","Cadillac DPi","Green",1,"Renger","Van Der Zande",7,7,71514,218548,FALSE,#N/A,#N/A,8379,38044,25091

p1ts-csv - Developer's Guide

Version 0.0.6 - December 12, 2019

Overall Architecture

Requirements

Contents

Example:

Example:

Example: