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.

Contents

• 1. Install
• 2. Run
  • 2.1. Options
  • 2.2. Desktop Shortcut
  • 2.3. Errors
• 3. Excel
  • 3.1. CSV Import and Automatic Refresh
  • 3.2. Useful Formulas
    • 3.2.1. Find Each Lap's cardata Row
    • 3.2.2. Find Each Lap's Driver
    • 3.2.3. Find Each Lap's Lap Time
    • 3.2.4. Find Each Lap's Gal Remaining
    • 3.2.5. Settings
• A. CSV File Structures
  • A.1. snapshotcold.csv
  • A.2. snapshot.csv
  • A.3. cardata-laps-carnum.csv

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

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.

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

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:
   • 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.
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.

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.

The following table breaks down what is happening in our particular call to MATCH.

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.

The following table breaks down what is happening.

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.

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.

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.

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

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.

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.

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