JCX File Exports Module - JCapper Help Doc

JCX File Exports Module - JCapper Help Doc
This help doc is still being written.
Last Updated: March 09, 2024
(Check back frequently for updates.)

JCX File Exports Module Help Doc

TABLE OF CONTENTS:

GENERAL OVERVIEW of the JCX File Exports Module found in JCapper Build 198.

BASIC OPERATING INSTRUCTIONS for Connecting the JCX File Exports Module to a JCapper2 .mdb File.

BASIC OPERATING INSTRUCTIONS for Connecting the JCX File Exports Module to a JCapper .mdb File.

BASIC OPERATING INSTRUCTIONS for using the SQL Expression Tool in the JCX File Exports Module to expose tables, data field names, and data field attributes in a connected to .mdb File.

Using the SQL Expression Tool in the JCX File Exports Module to create, edit, save, and delete reusable sql expressions.

BASIC OPERATING INSTRUCTIONS for using the JCX File Exports Module to export data out of JCapper tables to .CSV files on your hard drive.

BASIC OPERATING INSTRUCTIONS for using the Data Transformations Tool to export data out of JCapper tables to .CSV files on your hard drive.

BASIC OPERATING INSTRUCTIONS for Connecting the JCX File Exports Module to a JCapperSDK .mdb File.

BASIC OPERATING INSTRUCTIONS for using the JCX File Exports Module to export data out of a JCapper StarterHistory table to the two Data Window compatible tables (ExoticsExport1 and ExoticsExport2) in the Build 198 (and later) c:\JCapper\Exe\JCapperSDK.mdb file.

BASIC OPERATING INSTRUCTIONS for connecting the Data Window to a JCapperSDK .mdb file.

BASIC OPERATING INSTRUCTIONS for making Data Window query results executed against a connected to JCapperSDK.mdb file display ticket structure matrices for Exacta, Trifecta, and Superfecta wagers.

BASIC STUFF YOU NEED TO BE AWARE OF.

<-- Back to Table of Contents

GENERAL OVERVIEW of the JCX File Exports Module found in JCapper Build 198

Exporting Data from JCapper Tables:

The JCX File Exports Module is a powerful nuts and bolts module that gives you the ability to export JCapper data out of JCapper tables so that you can use it in outside of JCapper data projects.

The JCX File Exports Module enables you to create, edit, and save (reusable) sql expressions to drive your exports.

The SQL Expression Tool in the JCX File Exports Module enables you to display the table names found in any JCapper .mdb file - as well as the attributes (field name and data type) of the data fields found in any JCapper table.

You can export entire tables. You can export selected columns and selected rows from a single table. You can also export selected columns and selected rows from (multiple) joined tables.

The choice of what to export is yours and is controlled by your sql expressions.

The JCX File Exports Module enables you to export data from JCapper tables directly to .CSV files on your hard drive.

Once you have .CSV data sitting on your hard drive: You should easily be able to get JCapper data from .CSV file into the outside of JCapper data project(s) of your choice such as Access, Excel, MySQL, Python, R, SAS, SPSS, SQL, etc.

The above applies to every table in every JCapper .mdb file.

About the Table of Contents:

The Table of Contents in this help doc is made up of clickable links. Click a topic you are interested in - and you will be taken to the section for that topic.

To return to the Table of Contents - simply click one of the many Back to Table of Contents links found throughout this help doc.

After reading this help doc you should be able to use the JCX File Exports Module in Build 198 (and later) to:

Connect the JCX File Exports Module to any JCapper .mdb File.

Use the SQL Expression Tool in the JCX File Exports Module to expose table names, data field names, and data type of any data field from any table in a connected to JCapper .mdb File.

Use the SQL Expression Tool in the JCX File Exports Module to create, edit, and save reusable sql expressions.

Use the JCX File Exports Module to export data out of JCapper tables directly to .CSV files on your hard drive.

From there you should easily be able to get JCapper data from .CSV file into your own outside of JCapper data project(s) such as Access, Excel, MySQL, Python, R, SAS, SPSS, SQL, etc.

Use the JCX File Exports Module to connect to JCapperSDK .mdb files.

Use the Exotics Export Tool to export data out of a JCapper StarterHistory table to the two Data Window compatible tables (ExoticsExport1 and ExoticsExport2) found in JCapperSDK .mdb files.

Understand what a Top Line Power Rating is - and how it gives you control over factor rank in Data Window matrices that display ticket structure for vertical exotics data (Exacta, Trfiecta, and Superfecta, etc.)

Connect the Build 198 (and later) Data Window to JCapperSDK .mdb files.

Use the Build 198 (and later) Data Window to execute SQL Expressions and SQL UDMs against the two Data Window compatible tables (ExoticsExport1 and ExoticsExport2) found in JCapperSDK .mdb files.

Use the Build 198 (and later) Data Window to display ticket structure matrices for vertical exotic wagers (Exacta, Trifecta, and Superfecta, etc.)

<-- Back to Table of Contents

BASIC OPERATING INSTRUCTIONS for Connecting the JCX File Exports Module to a JCapper2 .mdb File:

On the JCX File Exports Module click MENU, select Connect to JCapper2.mdb File, and click YES at the prompt to bring up a Dialog Box.

Screenshot showing the MENU just before selecting Connect to JCapper2.mdb File:
http://www.jcapper.com/messageboard/avatars/JCX_MenuConnect02.jpg

Use the Dialog Box to select the JCapper2 .mdb file you want to connect to:
1. Single click the file you want to connect to.
2. Double check your selection.
3. Click the OPEN button on the Dialog Box and click YES at the prompt to finalize your selection.
Screenshot showing the Dialog Box after single clicking a JCapper2 .mdb file to select it and just before clicking the OPEN button to finalize the selection:
http://www.jcapper.com/messageboard/avatars/JCX_DialogConnect02.jpg

Screenshot showing the connected to file displayed on the face of the module:
http://www.jcapper.com/messageboard/avatars/JCX_Connected02.jpg

That's It!

-jp

.

<-- Back to Table of Contents

BASIC OPERATING INSTRUCTIONS for Connecting the JCX File Exports Module to a JCapper .mdb File:

On the JCX File Exports Module click MENU, select Connect to JCapper.mdb File, and click YES at the prompt to bring up a Dialog Box.

Screenshot showing the MENU just before selecting Connect to JCapper.mdb File:
http://www.jcapper.com/messageboard/avatars/JCX_MenuConnect01.jpg

Use the Dialog Box to select the JCapper .mdb file you want to connect to:
1. Single click the file you want to connect to.
2. Double check your selection.
3. Click the OPEN button on the Dialog Box and click YES at the prompt to finalize your selection.
Screenshot showing the Dialog Box after single clicking a JCapper .mdb file to select it and just before clicking the OPEN button to finalize the selection:
http://www.jcapper.com/messageboard/avatars/JCX_DialogConnect01.jpg

Screenshot showing the connected to file displayed on the face of the module:
http://www.jcapper.com/messageboard/avatars/JCX_Connected01.jpg

That's It!

-jp

.

<-- Back to Table of Contents

BASIC OPERATING INSTRUCTIONS for using the SQL Expression Tool in the JCX File Exports Module to expose tables, data field names, and data field attributes in a connected to .mdb File:

Launch the JCX File Exports Module - and connect to a JCapper .mdb file, JCapper2 .mdb file, or a JCapperSDK .mdb file.

Hint: See the Table of Contents (above) for links to individual sections within this help doc for connecting to each type of file.

Click the Use Custom SQL Expression box to launch the SQL Expression Tool.

On the SQL Expression Tool:
1. Select a Table from the drop down (bottom center area of the screen.)
2. Click the Display Data Fields button (just to the right of the drop down.)
  
  The interface will generate a report showing a list of the data fields and data field attributes for the selected table in the main textbox of the SQL Expression Tool.
  
  Note that the interface will also change the text on the Display Data Fields button from "Display Data Fields" to "Display SQL." (I'll get to that in step c. below.)
  
  Like many of the screens in JCapper - the SQL Expression Tool can be resized to any desired height and width by the user.
  
  To resize the SQL Expression Tool: Simply grab the bottom right hand corner of the SQL Expression Tool with your mouse - and then drag it across the screen - until the desired height and width are achieved.
  
  Screenshot of the SQL Expression Tool after the user has selected a table from the drop down, clicked the Display Data Fields button, and resized the screen to display the report:
  http://www.jcapper.com/messageboard/avatars/JCX_TableDetailReport01.jpg
3. Click the "Display SQL" button to change the text on the button back to "Display Data Fields" - and to change the text in the main textbox back to the sql expression that was displayed before the button itself was first clicked in step b. (above.)
  
  Note that you can click the button as many times as you like - toggling back and forth between displaying either a sql expression or the report showing data fields and data field attributes on the SQL Expression Tool.
4. X-out of the SQL Expression Tool when you are done.

That's It!

Notes and Comments:

Tables, Data Fields, and Attributes:

This feature is really handy when you want to generate an Export that contains selected columns only.

Simply connect the JCX File Exports Module to the .mdb file with the table that contains the data you want to export, bring up the SQL Expression Tool, select your table from the drop down, click the Display Data Fields button -- and then:

BANG! You are looking at a report displaying the column names and field attributes from the selected table.

From there: Creating a sql expression referencing only the desired column names that you want in the .CSV file created by your Export just got a whole lot easier.

-jp

.

<-- Back to Table of Contents

Using the SQL Expression Tool in the JCX File Exports Module to create, edit, save, and delete reusable sql expressions:

Basic Operating Instructions for creating and saving a SQL Expression:

Launch the JCX File Exports Module - and connect to a JCapperSDK .mdb file.

See the section about connecting to a JCapperSDK .mdb File (above) for instructions on doing that.

Or alternately - if your sql expression will be querying the StarterHistory Table - leave the JCX File Exports Module connected to the c:\JCapper\Exe\JCapper2.mdb file - or connect it to an archived JCapper2 .mdb file.

Click the "Use Custom SQL Expression" box on the face of the JCX File Exports Module to launch the SQL Expression Tool.

On the SQL Expression Tool:
1. Key a SQL Expression into the main textbox in the center area of the screen.
2. Key a Name for your SQL Expression into the "Name of SQL Expression" field near the top of the screen.
3. Click the SAVE button.
  
  The interface will validate your sql expression (test it for errors.)
  - If your sql expression fails validation the interface will generate a 'validation failed' message - and you will have to correct the sql expression before you can save it. (After making corrections re-click the Save button.)
  - If your sql expression passes validation the interface will:
    - Write your sql expression to the SQLExpressions table in the connected to JCapperSDK .mdb file.
    - Add the name of your sql expression to the "Select SQL Expression" drop down.
    - Generate a 'SQL Expression Saved' message.
      
      Screenshot of the SQL Expression Tool after clicking the Save button:
      http://www.jcapper.com/messageboard/avatars/JCX_SQLExpressionSaved01.jpg

That's It!

At this point - the SQL Expression has been validated and saved. You can use it to drive an Export right now. Or you can select it from the drop down later and use it to drive an Export then.

Basic Operating Instructions for editing and saving a SQL Expression:

Launch the JCX File Exports Module - and connect to the JCapperSDK .mdb file that contains the sql expression you want to edit.

See the section about connecting to a JCapperSDK .mdb File (above) for instructions on doing that.

Note: If the sql expression you want to edit is one that queries the StarterHistory Table - leave the JCX File Exports Module connected to the c:\JCapper\Exe\JCapper2.mdb file (or connect it to the archived JCapper2 .mdb file that contains the sql expression you want to edit.)

Click the "Use Custom SQL Expression" box on the face of the JCX File Exports Module to launch the SQL Expression Tool.

On the SQL Expression Tool:
1. Select the SQL Expression that you want to edit (by name) from the drop down.
  
  The interface will pull up the selected sql expression from the SQLExpressions table in the (last) connected to JCapperSDK .mdb file - and populate the name field with the name of the selected sql expression - and then populate the main textbox with the the selected sql expression's expression text.
2. Key your edits to the sql expression text.
3. Click the SAVE button.
  
  The interface will validate the edited sql expression (test it for errors.)
  - If the edited sql expression fails validation the interface will generate a 'validation failed' message - and you will have to correct the sql expression before you can save it. (After making corrections re-click the Save button.)
  - If the edited sql expression passes validation the interface will:
    - Prompt you with a message box telling you that the sql expression exists - and ask you Y/N if you want to overwrite it.
      
      Upon clicking YES at the prompt:
    - The interface will write the edited sql expression to the SQLExpressions table in the (last) connected to JCapperSDK .mdb file.
    - Generate a 'SQL Expression Saved' message.
      
      Screenshot of the edited sql expression after clicking the Save button:
      http://www.jcapper.com/messageboard/avatars/JCX_SQLExpressionSaved02.jpg

That's It!

The edited SQL Expression has been validated and saved.

Basic Operating Instructions for deleting a SQL Expression:

Launch the JCX File Exports Module - and connect to the JCapperSDK .mdb file that contains the sql expression you want to delete.

See the section about connecting to a JCapperSDK .mdb File (above) for instructions on doing that.

Note: if the sql expression you want to delete is one that queries the StarterHistory Table - leave the JCX File Exports Module connected to the c:\JCapper\Exe\JCapper2.mdb file (or connect it to the archived JCapper2 .mdb file that contains the sql expression you want to delete.)

Click the "Use Custom SQL Expression" box on the face of the JCX File Exports Module to launch the SQL Expression Tool.

On the SQL Expression Tool:
1. Select the SQL Expression that you want to delete (by name) from the drop down.
2. Click the DELETE button.
  
  The interface will prompt you with a message box - and ask you Y/N if you want to want to delete the selected sql expression.
  
  Click YES to delete the selected sql expression -- or click NO to cancel the delete operation.
  
  Screenshot after clicking the DELETE button and selecting YES at the prompt:
  http://www.jcapper.com/messageboard/avatars/JCX_SQLExpressionDeleted01.jpg

That's It!

The selected SQL Expression has been deleted.

Notes and Comments:

Where SQL Expressions Get Saved:

When you click the Save button on the SQL Expression Tool - the interface saves the current sql expression (by name) to the SQLExpressions table in the (last) connected to JCapperSDK .mdb file.

When you first launch the JCX File Exports Module - it auto connects to the following two files:

c:\JCapper\Exe\JCapper2.mdb

c:\JCapper\Exe\JCapperSDK.mdb

It is important to note that:

The module is always connected to two .mdb files:
1. Either a JCapper2 .mdb file or a JCapper .mdb file.
2. And a JCapperSDK .mdb file.

Even though the module is connected to two .mdb files - when the module is first launched:

The ONLY file displayed on the face of the module as the connected to file is:

"c:\JCapper\Exe\JCapper2.mdb" (without the quotes.)

And even though you can't see it on the face of the module - be aware that in the background - the module is also connected to:

The "c:\JCapper\Exe\JCapperSDK.mdb" (without the quotes) file.

Further:

When the module is first launched - until you connect to a different JCapperSDK .mdb file - the (last) connected to JCapperSDK.mdb file is:

"c:\JCapper\Exe\JCapperSDK.mdb" (without the quotes.)

But if you connect the module to a different JCapperSDK .mdb file:

THAT file becomes the (last) connected to JCapperSDK .mdb file.

Getting back to what I wrote at the top of this discussion:

When you click the Save button on the SQL Expression Tool- the interface saves the current sql expression (by name) to the SQLExpressions table in the (last) connected to JCapperSDK .mdb file.

So if you're wondering where a sql expression that you know you have saved ended up:

Look in the SQLExpressions table of the (last) connected to JCapperSDK .mdb file at the point in time when you clicked the Save button.

Hope I managed to type most of that out in a way that makes sense,

-jp

.

<-- Back to Table of Contents

BASIC OPERATING INSTRUCTIONS for using the JCX File Exports Module to export data out of JCapper tables to .CSV files on your hard drive:

On the JCapper Main Module click System Settings and then click the JCX File Exports button on the System Settings Interface to launch the JCX File Exports Module.

On the JCX File Exports Module, under File Extension, check the .CSV File box.

On the JCX File Exports Module, click the Browse button to bring up a Dialog Box where you will select (or name) a .CSV file as the output file for your export.

Use the Dialog box to navigate the folders on your hard drive.

After choosing the folder where you want the interface to generate your output .CSV file -- either select the filename of an existing .CSV file -- or key the filename of a new .CSV file where you want your exported data written to.

Screenshot of the Dialog Box after single clicking an existing .CSV file and just before clicking the OPEN button:
http://www.jcapper.com/messageboard/avatars/JCX_DialogBoxFileSelected01.jpg

With the desired .CSV file single clicked and selected in the Dialog box - click the OPEN button on the Dialog box to finalize file selection and close the Dialog box.

Alternately, you can skip the Dialog box entirely and just paste or key the full path of your output .CSV file into the Output Filename box.

Check the Use Custom SQL Expression box to launch the SQL Expression Tool.

On the SQL Expression Tool - paste or key a sql expression into the (big) SQL Expression box.

Or alternately - select a saved sql expression by name from the drop down - which causes the interface to paste the selected sql expression into the (big) SQL Expression box.

After getting the desired sql expression text into the (big) SQL Expression box: Click the Apply button.

Screenshot of the SQL Expression Tool after selecting a sql expression from the drop down:
http://www.jcapper.com/messageboard/avatars/jcxSqlExpressionTool_aqu-1320-inner.jpg

After you click the Apply button, the interface will validate your sql expression (test it for errors.) If the sql expression passes validation you will get an expression validated message.

Screenshot showing the expression validated message after clicking the Apply button:
http://www.jcapper.com/messageboard/avatars/jcxSqlExpressionTool_aqu-1320-validated.jpg

Click OK at the validation message to close the SQL Expression Tool.

If the expression contains errors you will get an error message - and you will have to correct the expression before moving on to the next step.

After the expression has been validated and the Expression Tools closes: Click the Generate Export button to run the export.

The interface will execute your custom sql expression - and write the data returned by the results set to the .CSV file named in the Output Filename box.

The interface will generate a record count message that tells you the number of rows in the (newly generated) .CSV file.

Screenshot showing the record count message for a (newly generated) .CSV file:
http://www.jcapper.com/messageboard/avatars/JCX_ExportComplete01.jpg

Click OK at the record count message. The interface will then generate a final .CSV file generated message for the (newly generated) .CSV file.

Screenshot of the final .CSV file generated message for the (newly generated) .CSV file:
http://www.jcapper.com/messageboard/avatars/JCX_ExportComplete02.jpg

Click OK at this (final) message.

That's It!

Notes and Comments:

.CSV Files:

The .CSV file created by the Export is now sitting on your hard drive. It will be populated with JCapper data driven by your sql expression. You will find it in the path that appears in the Output File Name box on the face of the JCX File Exports Module.

From here you can double click the (just created) .CSV file to open it in Excel - where you can see the data.

Screenshot of the .CSV file just generated (above) after opening it in Excel: http://www.jcapper.com/messageboard/avatars/JCX_CSVFileInExcel01.jpg

Selected Columns:

The sql expression that I used in the above example uses "SELECT * FROM STARTERHISTORY" (without the quotes.)

Using the * or wildcard character in this manner causes the .CSV file generated by an export to include every column in the table.

When you want the .CSV file generated by an export to contain every column in the table:

Use the * or wildcard character like I did in the above example.

But when you want the .CSV file generated by an export to only contain selected columns:

Change your sql expression. Reference only the column names you want in the .CSV file created by the Export.

For example, if the following sql expression were used to generate an export:

"SELECT [DATE], TRACK, RACE, HORSENAME, ODDS FROM STARTERHISTORY" (without the quotes.)

The resulting .CSV file generated by the export will contain only the following selected columns:

DATE, TRACK, RACE, HORSENAME, and ODDS.

What if you want to make the columns in the .CSV file created by an Export appear in a specific order? For example:

ODDS, HORSENAME, DATE, TRACK, RACE

Simply change the order in which the column names are referenced in your sql expression - like this:

"SELECT ODDS, HORSENAME, [DATE], TRACK, RACE FROM STARTERHISTORY" (without the quotes.)

Selected Rows:

In the above sql expression, I used the following WHERE clause:

	WHERE track='aqu'
	and intsurface = 2
	and dist = 1320
	and not startslifetime = 0

	and [DATE] >= #01-01-2015#
	and [DATE] <= #12-31-2015#

The factor constraints after the WHERE keyword, in this case track, intsurface, dist, startslifetime, and [DATE], control the rows that are written to the .CSV file by the Export.

Change the expression text after the WHERE keyword to change the rows that appear in a .CSV file created by an Export.

Row Sorting:

In the above sql expression, I used the following ORDER BY clause:

	ORDER BY [DATE], val(RACE)

The field names after the ORDER BY keyword, in this case [DATE], val(RACE), control the order in which the rows within a .CSV file created by an Export are sorted.

Change the expression text after the ORDER BY keyword to change the sort order for the rows within a .CSV file created by an Export.

These behaviors apply to all Exports generated by the JCX File Exports Module.

-jp

.

<-- Back to Table of Contents

New for JCapper 2024:

The Data Transformations Tool in JCapper 2024 is designed to export JCapper StarterHistory table data to .csv file on your hard drive with the data formatted for analysis (Conditional Logistic Regression, Multiple Logisitic Regression, Linear Regression, etc.) in third party stat tools such as STATA, the Survival Package in r, the MLogit Package in r, SPSS, SAS, or Excel with Solver, etc.

The Data Transformations Tool User Interface enables you to:

Create, Save, Pull Up, Edit, and Apply two types of SQL Expressions:

PreScreen (when you want to limit races exported to those with 'udm' plays)
MAIN (the main sql expression driving your export)

Select an Export Type:

CLogit (Conditional Logistic Regression)
MLogit (Multiple Logistic Regression)
General Export

Apply Default Data Transformations specific to your Export Type:

Transform Odds to decimal win likelihood estimate given track takeout
GUID (Genuine Unique Identifier) for each Race
GUID (Genuine Unique Identifier) for each Horse
Skip Races with outliers such as:

Dead Heat Win and/or Coupled Entries
Purse Money Only Runners

Transform Rank to:

F-Rank (decimal win likelihood estimate given Fibonacci Sequence and FieldSize)
P-Rank (decimal win likelihood estimate given Rank and FieldSize)

Apply User Defined Data Transformations:

Check or uncheck the boxes for any Data Transformation on the Interface

Select a What to Model Event or outcome:

Model MVP Horse (MVP = or 1 or 0)
Model Race Winner (WinPayoff= 1 or 0)
Model Runner Up (PlacePayoff= 1 or 0)
Model 2nd or Better Finish (MVP= 1 or 0)
Model 2nd or Better Highest Odds (MVP= 1 or 0)
Model Max Place Returns (MVP= 1 or 0)
Model Third Place Finish (MVP= 1 or 0)
Model 3rd or Better Finish (MVP= 1 or 0)
Model Fourth Place Finish (MVP= 1 or 0)
Model 4th or Better Finish (MVP= 1 or 0)
Model Fifth Place Finish (MVP= 1 or 0)
Model 2nd or Worse Finish (MVP= 1 or 0)
Model 3rd or Worse Finish (MVP= 1 or 0)
Model 4th or Worse Finish (MVP= 1 or 0)
Model Leader at Start Call (MVP= 1 or 0)
Model Leader at First Call (MVP= 1 or 0)
Model Leader at Second Call (MVP= 1 or 0)
Model Leader at Stretch Call (MVP= 1 or 0)
Model Likelihood of being Post Time Favorite (MVP= 1 or 0)

BASIC OPERATING INSTRUCTIONS for the Data Transformations Tool:

On the JCX File Exports Module:

Click the Data Transformations button to lauch the Data Transformations Tool.

On the Data Transformations Tool:

Apply an Optional PreScreen SQL Expression to your Export:

Click the SQL PreScreen button to lauch the SQL Expression Tool.

On the SQL Expression Tool:

Key or pull up a SQL Expression.
Double check your work and click the APPLY button.
(This will apply your PreScreen SQL Expression and close the SQL Expression Tool.)

Skip this step if you don't want to Apply a PreScreen SQL Expression to your Export.

Notes about PreScreen SQL Expressions:

Important!
Adding a PreScreen to an Export REQUIRES the PreScreen SQL Expression to contain an ODDS constraint. AND ODDS > 0 should work just fine.

Many of the What to Model events require the Interface to evaluate official finish position, odds, and/or parimutuel payoffs. Purse money only runners have ODDS = 0, but can be written to the StarterHistory table via option 2 of the XRD File Build Mode Enhanced Setting.

For that reason I programmed the Interface to require PresScreen SQL Expressions to have an ODDS constraint when you click the APPLY button.

Q. What is a PreScreen SQL Expression? When would you want to use one and why?

A. Suppose for the sake of argument you already have a SQL Expression for a pretty good SQL UDM. If that's the case, depending on the concept behind the UDM, it can be worthwhile to use the SQL Expression behind that UDM as a PreScreen for an Export.

Applying a PreScreen SQL Expression to an Export causes the Interface to execute the PreScreen SQL Expression first.

During this stage the Interface separates races with one or more horses flagged by the PreScreen SQL from races that don't.

The Interface executes the Main SQL Expression next.

During this stage the Interface exports data for only those races with one or more horses flagged by the PreScreen. (Races with zero PreScreen Plays are excluded from the Export.)

The end result is this:

Only those races with one or more horses flagged by the PreScreen make it into your .csv export file.

When you create a model from your exported data you are modeling the likelihood of events (such as winning the race) for only those races with PreScreen (or UDM) plays.

Depending on the concept behind the PreScreen (or SQL UDM) your resulting model can differ sustantially from the more general fundamental models employed by the whale teams.

Of course you have to know what you are doing and you need to have developed some modeling 'chops.'

But do a good job with this and (at least for those of you who fit that description or are seeing incremental improvement while working towards that goal) there's some potential to be realized.

Screenshot of the SQL Expression Tool:
http://www.jcapper.com/messageboard/avatars/PreScreenSQL03082024a.jpg

Note: The above screenshot was taken after I clicked the SQL PreScreen button -- I keyed a SQL Expression from a SQL UDM -- I added an odds constraint (circled in blue) -- I gave the PreScreen SQL Expression a name (circled in yellow) -- I clicked the Save button (also circled in yellow) to save the PreScreen SQL Expression -- To bring it up later I can just select it by name from the drop down (circled in green) -- Finally I clicked the APPLY button (circled in red) just before clicking Yes at the prompt (also circled in red) to apply the PreScreen SQL to the Export. [whew! -jp.]

Apply a Main SQL Expression to your Export:

Click the SQL Current Export button to lauch the SQL Expression Tool.

On the SQL Expression Tool:

Key or pull up a SQL Expression.
Double check your work and click the APPLY button.
(This will apply your Main SQL Expression and close the SQL Expression Tool.)

Notes about Main SQL Expressions:

Reference the FIELDSIZE column in your SQL Expression. Otherwise, you cannot use any of the data transformations for RANK.

Stat packages like STATA installed from a CD, the Survival Package in r, the MLogit package in r, etc. have memory allocation limits.

Reference individual StarterHistory column names for the factors you intend to include in a model instead of "SELECT * FROM" (without the quotes.)

The fewer the number of column names referenced the smaller the amount of memory your stat package needs to allocate for max likelihood estimation on your exported data. And the smaller the likelihood of getting out of memory errors.

If your Stat Package does give you an 'Out of Memory' message during Max Likelihood Estimation, consider exporting a smaller .csv file by referencing fewer columns and/or exporting a smaller number of records by using contraints for track, intsurface, dist, fieldsize, start date, and end date, etc.

CLogit performs max likelihood estimation on individual groups (for example a 'group' being the horses within each individual race.)

MLogit performs max likelihood estimation using the entire database as a group. Computationally Singular errors generated by the MLogit package in r mean there are multiple horses in the data having identical values in the same columns. This can result in the mlogit package failing to distinguish one horse vs. the other.

One working solution to this is remove such races from the data as a transformation.

Another might be transforming the values of every record in the dataset for the offending columns up or down (determined by random number) by a tiny randomly generated amount. Hardly ideal. But expedient (and sometimes workable.)

Screenshot of the SQL Expression Tool:
http://www.jcapper.com/messageboard/avatars/MainSQL03082024a.jpg

Note: The above screenshot was taken after I clicked the SQL Current Export button -- I keyed a Main SQL Expression to drive the Export -- I referenced individual column names for factors I intend to use in a model instead of SELECT * FROM STARTERHISTORY because it enables my stat package to use less memory during max likelihood estimation -- I gave the PreScreen SQL Expression a name (circled in yellow) -- I clicked the Save button (also circled in yellow) to save the Main SQL Expression -- To bring it up later I can just select it by name from the drop down (circled in green) -- Finally I clicked the APPLY button (circled in red) just before clicking Yes at the prompt (also circled in red) to apply the Main SQL to the Export. [whew! -jp.]

Select an Export Type and Default Data Transformations:

Available Export Types from the drop down:

1. General Export - Select Transformations Manually
2. CLogit Export - Apply Default Transformations Y or N
3. MLogit Export - Apply Default Transformations Y or N

Notes about Export Types:

Select CLogit as your export type and accept the suggested default data transformations to format the data in your export for Conditional Logistic Regression (also known as fixed effects logisitic regression.)

Select MLogit as your export type and accept the suggested default data transformations to format the data in your export for Multiple Logistic Regression (also known as multinomial logisitic regression.)

Select MLogit as your export type and accept the suggested default data transformations to format the data in your export for Linear Regression (including least squares regression.)

Select General as your export type and manually select your own data transformations from the interface. Or, do not select any data transformations on the interface if you want to make your transformations to the data outside of JCapper (in Excel for example.)

You always have the ability to override the default data transformations. Check or uncheck data transformation boxes on the interface as you see fit.

Screenshot of the Data Transformations Tool:
http://www.jcapper.com/messageboard/avatars/DataTransformations03082024a.jpg

The above screenshot was taken after I selected CLogit as the Export Type (circled in yellow) -- The Interface auto-selected Model MVP Horse (MVP = or 1 or 0) as the What to Model Event (also circled in yellow) -- The Interface auto-checked boxes for Default Data Transformations (marked in yellow on the far left hand edge) -- The Interface originally auto selected Bhvr 45.5 =(Factor P-Rank) from the Transform Rank drop down -- But I manually overrode that by selecting Bhvr 45.4 =(Factor F-Rank) (circled in lime green) -- The Interface displays the word VALIDATED! to the right of the SQL PreScreen and SQL Current Export buttons meaning both SQL Expressions are applied to the Export (marked in blue) -- Finally I clicked the APPLY button (circled in red) just before clicking Yes at the prompt (also circled in red) to apply everything on the Data Transformations Tool to the Export. [whew! -jp.]

Select a What to Model Event or just go with the default What to Model Event auto-selected by the Interface when you select an Export Type:

Available What to Model Events from the drop down:

Model MVP Horse (MVP = or 1 or 0)
Model Race Winner (WinPayoff= 1 or 0)
Model Runner Up (PlacePayoff= 1 or 0)
Model 2nd or Better Finish (MVP= 1 or 0)
Model 2nd or Better Highest Odds (MVP= 1 or 0)
Model Max Place Returns (MVP= 1 or 0)
Model Third Place Finish (MVP= 1 or 0)
Model 3rd or Better Finish (MVP= 1 or 0)
Model Fourth Place Finish (MVP= 1 or 0)
Model 4th or Better Finish (MVP= 1 or 0)
Model Fifth Place Finish (MVP= 1 or 0)
Model 2nd or Worse Finish (MVP= 1 or 0)
Model 3rd or Worse Finish (MVP= 1 or 0)
Model 4th or Worse Finish (MVP= 1 or 0)
Model Leader at Start Call (MVP= 1 or 0)
Model Leader at First Call (MVP= 1 or 0)
Model Leader at Second Call (MVP= 1 or 0)
Model Leader at Stretch Call (MVP= 1 or 0)
Model Likelihood of being Post Time Favorite (MVP= 1 or 0)

Notes about What to Model Events:

When you select MLogit or General as your export type you will see the complete list of What to Model events (above.)

When you select CLogit as your export type you will only see a partial list of What to Model events.

Formatting a dataset for CLogit requires a lot of programming hours. My underlying assumption in preparing the data for CLogit analyisis is that each race is the 'group' being analyzed. Therefore, no two horses within each 'group' or race can have identical data for the column names selected for modeling by the user.

Some of the above events, for example 'Model 2nd or Better Highest Odds (MVP= 1 or 0)' requires source code that enforces a tiebreaker rule between the 1st and 2nd place finisher so it can 'pick' the one with the highest odds.

But what about the rare race when the 1st and 2nd place finishers have exactly the same odds? This doesn't happen often, but when it does, it causes some of the stat packages out there to blow up. As a result I had to handle this, and many other cases, in my source code.

It is for this reason you only get a partial list of What to Model events in the drop down after selecting CLogit as your export type.

The What to Model events you do see in the drop down after selecting CLogit as your export type are the ones that have been fully tested as of this writing.

Screenshot of the Data Transformations Tool:
http://www.jcapper.com/messageboard/avatars/DataTransformations03082024a.jpg

The above screenshot was taken after I selected CLogit as the Export Type (circled in yellow) -- The Interface auto-selected Model MVP Horse (MVP = or 1 or 0) as the What to Model Event (also circled in yellow) -- The Interface auto-checked boxes for Default Data Transformations (marked in yellow on the far left hand edge) -- The Interface originally auto selected Bhvr 45.5 =(Factor P-Rank) from the Transform Rank drop down -- But I manually overrode that by selecting Bhvr 45.4 =(Factor F-Rank) (circled in lime green) -- The Interface displays the word VALIDATED! to the right of the SQL PreScreen and SQL Current Export buttons meaning both SQL Expressions are applied to the Export (marked in blue) -- Finally I clicked the APPLY button (circled in red) just before clicking Yes at the prompt (also circled in red) to apply everything on the Data Transformations Tool to the Export. [whew! -jp.]

On the JCX File Exports Module:

Screenshot of the JCX File Exports Module:
http://www.jcapper.com/messageboard/avatars/JCXFileExports03082024a.jpg

The above screenshot shows the JCX File Exports Module just after clicking the APPLY button on the Data Transformations Tool -- All work from the Data Transformations Tool (circled in blue) now displays the JCX File Exports Module -- From here click the Browse button (circled in red) to open the Dialog Box and either select or key in the name of the .csv file for your Export.

Click the Browse button to open the File Dialog Box:

Use the File Dialog Box to:

Select an existing .csv file.

OR key the name of your Export .csv File into the box to the left of the OPEN button at the bottom.

Click the OPEN button to name your Export .csv File for output and return to the JCX File Exports Module.

Screenshot of the JCX File Exports Module:
http://www.jcapper.com/messageboard/avatars/JCXFileExports03082024b.jpg

The above screenshot was taken after clicking the Browse button and naming an Export .csv File for Output -- The Interface shows the name of the Export .csv File (circled in yellow) -- From here, double check your work, and click the Create Export button (circled in red) to run the Export.

Clicking the Slash button (circled in lime green) reverses the direction of the slashes in the Export .csv File path. The READ command in r chokes on backslashes. If you get a path not found error using the READ command in r click the button and try it again using the revised path with forward slashes.

Click the Create Export button then click Yes at the prompt to run the Export.

That's It! The export will run to completion.

After that, load the Export .csv File into your Stat Package using the full path displayed on the JCX File Exports Module (circled in yellow in the screenshot below.)

Clicking the Slash button (circled in lime green) reverses the direction of the slashes in the Export .csv File path. The READ command in r chokes on backslashes. If you get a path not found error using the READ command in r click the button and try it again using the revised path with forward slashes.

http://www.jcapper.com/messageboard/avatars/JCXFileExports03082024c.jpg

<-- Back to Table of Contents

BASIC OPERATING INSTRUCTIONS for Connecting the JCX File Exports Module to a JCapperSDK .mdb File:

On the JCX File Exports Module click MENU, select Connect to JCapperSDK.mdb File, and click YES at the prompt to bring up a Dialog Box.

Screenshot showing the MENU just before selecting Connect to JCapperSDK.mdb File:
http://www.jcapper.com/messageboard/avatars/JCX_MenuConnect03.jpg

Use the Dialog Box to select the JCapperSDK .mdb file you want to connect to:
1. Single click the file you want to connect to.
2. Double check your selection.
3. Click the OPEN button on the Dialog Box and click YES at the prompt to finalize your selection.
Screenshot showing the Dialog Box after single clicking a JCapperSDK .mdb file to select it and just before clicking the OPEN button to finalize the selection:
http://www.jcapper.com/messageboard/avatars/JCX_DialogConnect03.jpg

Screenshot showing the connected to file displayed on the face of the module:
http://www.jcapper.com/messageboard/avatars/JCX_Connected03.jpg

Notes and Comments

Maintaining Multiple JCapperSDK .mdb Files:

The above screenshots should make it clear that I maintain multiple JCapperSDK .mdb files.

I do this for several reasons.

First, all Access .mdb database files are subject to Microsoft's 2.0 gigabyte file size barrier. (Break the file size limit and the .mdb file becomes unusable.)

Best practice involves NEVER sending large amounts of data directly to the (original) c:\JCapper\Exe\JCapperSDK.mdb file.

I always make a copy of the c:\JCapper\Exe\JCapperSDK.mdb file - and connect the JCX File Exports Module to the copy - never the original.

That way, if I screw up and run an export that sends so much data to the (copied) file that it breaks Microsoft's 2.0 gigabyte file size barrier:

The copied file becomes unusable not the original.

Also, maintaining multiple (individually named) JCapperSDK .mdb files, where each file contains different types of data, provides some advantages.

When I decide to go into r&d mode - I prefer to work uninterrupted in the Data Window.

My preferred way of doing things when I decide to do r&d involving exotic ticket structure for vertical exotics is to first map out the exports I plan to run, create one new copy of the JCapperSDK.mdb file per each export that I plan to run, name each new copy of the JCapperSDK.mdb file so that the filename itself describes the type of data the file will contain after I run each planned export, and then run each planned export one file at a time - until I've run all of the exports mapped out at the beginning.

Then, I'll connect the Data Window to the file that contains data for the first export - and dive in.

I've developed a series of sql queries designed to help me evaluate win rate and roi potential for vertical exotics under different sets of circumstances. I'll run those queries one at a time on the (current) connected to JCapperSDK .mdb file. Along the way I'll note any useful observations about the data and occasionally paste Data Window results directly to text files or spreadsheets so that I can refer to them later.

When I'm finished with the first file I'll move on to the next - and repeat the process - analyzing the vertical exotics data in each new copy of the JCapperSDK .mdb file - until I've analyzed the vertical exotics data for all the exports that I've run.

When I'm done - It's likely I'll have some very good ideas about improving my ticket structure, race selection criteria, which of my UDMs have profit potential for vertical exotics, and which of them don't.

<-- Back to Table of Contents

BASIC OPERATING INSTRUCTIONS for using the JCX File Exports Module to export data out of a JCapper StarterHistory table to the two (new) Data Window compatible tables (ExoticsExport1 and ExoticsExport2) in the (new) c:\JCapper\Exe\JCapperSDK.mdb file:

Connect the JCX File Exports Module to the JCapperSDK .mdb File where you want your Exotics Export data written to.

Hint: You'll find the steps to do that in the section about connecting to a JCapperSDK .mdb File (above.)

Important Reminder:

Best practice involves NEVER sending large amounts of data directly to the (original) c:\JCapper\Exe\JCapperSDK.mdb file.

I always make a copy of the c:\JCapper\Exe\JCapperSDK.mdb file - and connect the JCX File Exports Module to the copy - never the original.

That way, if I screw up and run an export that sends so much data to the (copied) file that it breaks Microsoft's 2.0 gigabyte file size barrier:

The copied file becomes unusable not the original.

On the JCX File Exports Module click MENU and select Exotics Export (Chart Races and StarterHistory) to bring up the Exotics Export Tool.

Screenshot - the Exotics Export Tool when it is first launched:
http://www.jcapper.com/messageboard/avatars/ExoticsExportTool01.jpg

On the Exotics Export Tool:
1. Select a Previous Export from the Previous Export Definitions drop down at the top of the module. Then click YES at the prompt to Apply definitions from the selected Export to the Export you are about to run.
  
  The interface will auto populate each of the visual elements on the Exotic Exports Tool for you. Once the interface has validated the ChartRacesSQL and StarterHistorySQL expressions from the selected Export (this may take a few seconds) you will be presented with a message telling you that definitions from the selected Export have been Applied to the current Export.
  
  From here, look the interface over and double check the Export you are about to run. Skip steps 2-10 (below) and proceed directly to step 11 (below) where you click the Create Export button to run your Export.
  
  If this is your very first Export - the drop down will be empty - and you will need to manually complete each of the numbered steps (2-10) below.
  
  You can also choose not to select a previous export from the drop down - and manually complete each of the numbered steps (2-10) below:
2. Select a Base Wager Amt from the drop down.
3. Select a Wager Type from the drop down.
4. Select a factor as the Top Line Power Rating from the drop down.
  
  See the Notes and Comments (below) for a detailed explanation about Top Line Power Ratings.
5. Click the SQL ChartRaces button on the Exotics Export Tool to bring up the SQL Expression Tool.
  
  Then key or paste a sql expression into the SQL Expression Tool.
  
  Hint: Use a sql expression designed to query the ChartRaces Table in the JCapperSDK.mdb file for the Wager Type you selected in step 3. above.
  
  If you have a previously saved ChartRaces/WagerType compatible sql expression - select it by name from the SQL Expression drop down.
  
  IF YOU NEED A SQL EXPRESSION -- the following link will get you a text file containing the sql expression shown in the next screenshot:
  http://www.jcapper.com/messageboard/reports/triSql.txt
  
  Hint: Cut and paste the expression from the text file at the above link directly into the SQL Expression Tool. From there edit the date text and data field name for the vertical exotics pool as needed. (You can even save it for next time.)
  
  Screenshot of the SQL Expression Tool after selecting a ChartRaces compatible sql expression from the SQL Expression drop down:
  http://www.jcapper.com/messageboard/avatars/ChartRacesTriSql01.jpg
  
  After getting a ChartRaces/WagerType compatible sql expression into the SQL Expression Tool - click the APPLY button and click YES at the prompt.
  
  The interface will validate your sql expression and close the SQL Expression Tool.
  
  Screenshot of the Exotics Export Tool after clicking the APPLY button. Note that the words "SQL Validated" are displayed to the right of the SQL ChartRaces button:
  http://www.jcapper.com/messageboard/avatars/ChartRacesSQLValidated01.jpg
6. Click the SQL StarterHistory button on the Exotics Export Tool to bring up the SQL Expression Tool a second time.
  
  Key or paste a sql expression into the SQL Expression Tool.
  
  The sql expression that you use in this step should be designed to query a StarterHistory Table. Beyond that - can be as wide or as narrow as you want it to be.
  
  Hint: See the Notes and Comments section (below) for a few thoughts about StarterHistory/WagerType sql expressions needed for this step.
  
  If you have a previously saved StarterHistory/WagerType sql expression - select it by name from the SQL Expression drop down.
  
  IF YOU NEED a STARTERHISTORY/WAGERTYPE SQL EXPRESSION -- the following link will get you a text file containing the sql expression shown in the next screenshot:
  http://www.jcapper.com/messageboard/reports/diannaSql.txt
  
  Hint: Cut and paste the expression from the text file at the above link directly into the SQL Expression Tool. From there edit the date text and factor constraints as needed. (Save it for next time after you're done editing it.)
  
  Screenshot of the SQL Expression Tool after selecting a StarterHistory compatible sql expression from the SQL Expression drop down:
  http://www.jcapper.com/messageboard/avatars/StarterHistTriSql01.jpg
  
  After getting a StarterHistory compatible sql expression into the SQL Expression Tool - click the APPLY button and click YES at the prompt.
  
  The interface will validate your sql expression and close the SQL Expression Tool.
  
  Screenshot of the Exotics Export Tool after clicking the APPLY button. Note that the words "SQL Validated" are displayed to the right of the SQL StarterHistory button:
  http://www.jcapper.com/messageboard/avatars/StarterHistSQLValidated01.jpg
7. Check the table where you want the data for your Exotics Export Written to.
  
  Hint: Check the box for the ExoticsExport1 table. The field names are the same as a StarterHistory table - which makes things easier in the Data Window once you start analyzing your exported vertical exotics data.
8. Check the Clear First box as your Export Type. (Unless you plan to append data to a previous Exotics Export that you ran. In that case check the Append box.)
9. Double check the above steps.
  
  Look the Exotics Export Tool over and verify the following:
  - A Base Wager Amt is selected.
  - A Wager Type is selected.
  - A Factor is selected as a Top Line Power Rating.
  - The word VALIDATED appears to the right of the SQL ChartRaces button.
  - The word VALIDATED appears to the right of the SQL StatrerHistory button.
  - The ExoticsExport1 table box is checked.
  - The Clear First box is checked. (Unless you are running an append.)
10. Screenshot - Exotics Export Tool ready to click the CREATE EXPORT button:
  http://www.jcapper.com/messageboard/avatars/ExoticsExportValidated01.jpg
11. Click the CREATE EXPORT button to run the Export.
  
  The interface will pull up the starters meeting the criteria of both the ChartRaces/WagerType and StarterHistory/WagerType sql expressions - and write them to the ExoticsExport table (selected in step 7. above) in the connected to JCapperSDK .mdb file (selected in step A. above.)
  
  The interface will also write metadata about the Export to the ExoticsExportHistory table in the connected to JCapperSDK .mdb file. These table entries are used to populate the drop down at the top of the Exotic Exports Tool - where you can select any previously run export - and apply definitions from the selected export to a current export you are about to run.
  
  Screenshot of the Exotics Export Tool after clicking the CREATE EXPORT button:
  http://www.jcapper.com/messageboard/avatars/ExoticsExportUnderway01.jpg
12. After the Export has run to completion - click OK at the "Export Complete" message.
13. Click MENU (located in the upper left hand area of the Exotic Export Tool) and select UTILITIES to bring up the Utilities Screen.

On the Utilities Screen:

Click the Compact and Repair JCapperSDK button to bring up a Dialog Box.

Screenshot showing the Utilities Screen:
http://www.jcapper.com/messageboard/avatars/UtilitiesScreen01.jpg

Use the Dialog Box to select the connected to JCapperSDK .mdb file and run a compact and repair on that file:
1. Single click the file you want to compact and repair.
2. Double check your selection.
3. Click the OPEN button on the Dialog Box and click YES at the prompt to finalize your selection and run the compact and repair.
Screenshot showing the Dialog Box after single clicking the connected to JCapperSDK .mdb file to select it and just before clicking the OPEN button to finalize the selection and run the compact and repair:
http://www.jcapper.com/messageboard/avatars/JCX_DialogConnect04.jpg

Screenshot showing the Utilities Screen with the compact and repair underway:
http://www.jcapper.com/messageboard/avatars/UtilitesCompactAndRepairUnderway01.jpg

Click OK at the "Compact and Repair Complete" message after the compact and repair has run to completion and x-out of the Utilities Screen.

The Export is now complete. X-out of the Exotics Export Tool. Then X-out of the JCX File Exports Module.

Notes and Comments

The Export is now complete. The ExoticsExport table (that you selected in step 7. above) in the JCapperSDK .mdb file (that you connected to in step A. above) is now populated with vertical exotics data returned by the intersection of the two sql expressions that you applied (in steps 5. and 6. above.)

At this point, you can connect the SQL Data Window to the JCapperSDK .mdb file (that you connected to in step A. above) and execute sql expressions against the ExoticsExport table (that you selected in step 7. above) to see vertical exotics matrices for the Wager Type (that you selected in step 3. above) broken out by rank for the factor you selected as a Top Line Power Rating (in step 4. above.)

Top Line Power Rating:

When you select a factor name as a Top Line Power Rating: You are telling the interface to insert numeric value, rank, and gap for the selected factor (instead of UPR) into the UPR, RankUPR, and GapUPR fields in the target table of your Export.

After your Export is complete, and after you connect the Data Window to a JCapperSDK .mdb file that contains data resulting from an Exotics Export, when you use the Data Window to query the ExoticsExport1 or ExoticsExport2 tables in the connected to JCapperSDK.mdb file:

Instead of UPR Rank, the exotic wager matrices that you see in the Data Window are going to be based on the factor that you selected as your Top Line Power Rating in step 4. above.

This means that by selecting a Top Line Power Rating - the exotic wager matrices that you see in the Data Window can ultimately be based on your choice of the following:

Alchemy Rank
JPR Rank
PRating Rank
QRating Rank
UPR Rank
Rank for ANY of the 35 factors assigned to a slot number in your SQL Factor Setup!

StarterHistory/WagerType SQL Expressions:

The StarterHistory/WagerType SQL Expression that you use (Exotics Export Tool step 6. above) should be designed to query a StarterHistory Table. Beyond that - can be as wide or as narrow as you want it to be.

For instance, "SELECT * FROM STARTERHISTORY" (without the quotes) would be one example of a wide sql expression.

Just be aware that the wider the expression you use in this step - the more time you can expect to spend in the Data Window later - as you analyze potential ticket structure for the exotic Wager Type selected in step 3. above.

The sql expression that you use in this step can also be similar to (or even be) a SQL UDM that you want to investigate as a source of potential plays for the exotic Wager Type selected in step 3. above.

Keep in mind that vertical exotics - especially Trifecta and Superfecta - are (currently at most tracks) the highest takeout wagers on the wagering menu. Because of that, crafting sql expressions that generate close to break even (or higher) results in the Data Window for vertical exotics is more difficult than getting to break even (or higher) for lower takeout wagers such as Win and Place.

If your initial export definitions (Top Line Power Rating, ChartRaces/WagerType SQL, and StarterHistory/WagerType SQL) aren't getting you within shouting distance of break even when you analyze vertical exotics data in the Data Window -- you might want to stop -- take a breath -- and try a different approach. Try running a clear first export that uses a different Top Line Power Rating, ChartRaces/WagerType SQL, or StarterHistory/WagerType SQL. You may even consider changing all three.

Also keep in mind that the Trifecta and Superfecta ticket structure matrices in the Data Window are "canned" (or hard coded into the Data Window.) They may be too wide for the sql expressions you are using.

That said -- after running an Exotics Export: You CAN use the JCX File Exports Module to export vertical exotics data from the ExoticsExport1 and ExoticsExport2 tables in a JCapperSDK .mdb file out to .CSV file on your hard drive. From there, you can get vertical exotics data into Excel (or some other outside of JCapper data project.)

After that you could then write your own routines designed to analyze vertical exotics ticket structure of your own design.

About the Previous Export Definitions drop down at the top of the Exotic Exports Tool:

Each time you generate an Export, the interface writes metadata about your export to the ExoticsExportHistory table in the connected to JCapperSDK .mdb file.

When the Exotics Export Tool is first launched, the interface populates the drop with entries from the table - in descending date order - meaning that when you open up the drop down - the previous exports in it are sorted with the most recent Export displayed at the very top.

Whenever you select a previous Export from the drop down - the interface prompts you with a Y/N message box - asking if you want to Apply export definitions to the current export.

If you click YES at the prompt, the interface will use Export Definitions pulled from the previous Export that you selected from the drop down - and auto populate each of the visual elements on the Exotic Exports Tool for you. Once the interface has validated the ChartRacesSQL and StarterHistorySQL expressions from the selected Export (this may take a few seconds) you will be presented with a message telling you that definitions from the selected Export have been Applied to the current Export.

From there, after looking the interface over and double checking the Export you are about to run - you can skip numbered steps 2-10 (below) and proceed directly to step 11 - and click the Create Export button to run your Export.

There are two additional things I want to mention:

When you launch the Exotics Export Tool - and it is connected to a JCapperSDK .mdb file that has never had an Export written to it before - the drop down will be empty (and disabled) because the connected to JCapperSDK .mdb file does not yet have any entries in the ExoticsExportHistory table.

But as soon as you generate an Export - the interface will write an entry to the table - and populate the drop down with the new entry.

From that point forward - you will be able to select that (or any other entry for a previous export) from the drop down - and apply the Export definitions from the selected entry to the current Export you are about to run.

JCapperSDK .mdb files with ExoticsExportHistory tables in them were introduced in versions of JCapper Build 198 ver 2.0 published November 03, 2016 and later.

Versions of JCapper published prior to November 03, 2016 do not include JCapperSDK.mdb files that contain an ExoticsExportHistory table.

If your Exotics Export Tool doesn't have a Previous Export Definitions drop down at the top of the screen -- or if it does have a Previous Export Definitions drop down -- but the drop down is always disabled - and doesn't populate with new entries after running an Export:

The first thing you should do is check to see whether or not your connected to JCapperSDK .mdb files contain an ExoticsExportHistory table.

If none of your JCapperSDK .mdb files have an ExoticsExportHistory table - then it's time to head to the JCapper program downloads page - and get the latest program version.

About these instructions:

The written instructions in this section of the help doc are long. It takes more time to read them than it does to actually perform the steps and get to the point where you click the Create Export button.

After you've exported data for vertical exotics to a JCapperSDK .mdb file a few times - the steps have a way of becoming second nature - and I'm guessing you'll find yourself referring to these instructions less and less.

-jp

.

<-- Back to Table of Contents

BASIC OPERATING INSTRUCTIONS for connecting the Data Window to a JCapperSDK .mdb file:

Launch the Data Window in SQL Mode.

Click MENU, select Connect to JCapperSDK.mdb File, and click YES at the prompt to bring up a Dialog Box.

Screenshot showing the MENU just before selecting Connect to JCapperSDK .mdb File:
http://www.jcapper.com/messageboard/avatars/DataWindowConnect03.jpg

Use the Dialog Box to select the JCapperSDK .mdb file you want to connect to:
1. Single click the file you want to connect to.
2. Double check your selection.
3. Click the OPEN button on the Dialog Box and click YES at the prompt to finalize your selection.
Screenshot showing the Dialog Box after single clicking a JCapperSDK .mdb file to select it and just before clicking the OPEN button to finalize the selection:
http://www.jcapper.com/messageboard/avatars/DataWindowDialogConnect03.jpg

Screenshot showing the connection successful message in the Data Window:
http://www.jcapper.com/messageboard/avatars/DataWindowConnected03.jpg

Note that the connection successful message in the above screenshot shows the available Exports (if any) in the connected to JCapperSDK .mdb file.

<-- Back to Table of Contents

BASIC OPERATING INSTRUCTIONS for making Data Window query results executed against a connected to JCapperSDK.mdb file display ticket structure matrices for Exacta, Trifecta, and Superfecta wagers:

Connect the SQL Data Window to a JCapperSDK .mdb File that contains Exotics Export data.

Hint: You'll find the steps to do that in the section about connecting the Data Window to a JCapperSDK .mdb File and in the section about Exporting data to a JCapperSDK .mdb file (both above.)

Open up the Data Window Factors Drop Down - and select an Exotics Ticket Structure Matrix that matches the most recent ExoticsExport1 or ExoticsExport2 Export(s) in the connected to JCapperSDK .mdb File.
- If the connected to JCapperSDK .mdb File contains an Exacta Export: Select one of the Exacta Matrix options from the Factors Drop Down.
- If the connected to JCapperSDK .mdb File contains a Trifecta Export: Select one of the Trifecta Matrix options from the Factors Drop Down.
- If the connected to JCapperSDK .mdb File contains a Superfecta Export: Select one of the Superfecta Matrix options from the Factors Drop Down.

Click the SQL button to launch the SQL Expression Tool.

Key or paste an Exotics Export compatible sql expression into the SQL Expression Tool.
- If you want to query data in the ExoticsExport1 table of the connected to JCapperSDK .mdb File:
  
  Reference the ExoticsExport1 table (by name) in the FROM clause of your sql expression.
  
  Link to a text file that will get you an ExoticsExport1 compatible sql expression:
  http://www.jcapper.com/messageboard/reports/SQLExoticsExport1.txt
- If you want to query data in the ExoticsExport2 table of the connected to JCapperSDK .mdb File:
  
  Reference the ExoticsExport2 table (by name) in the FROM clause of your sql expression.
  
  Link to a text file that will get you an ExoticsExport2 compatible sql expression:
  http://www.jcapper.com/messageboard/reports/SQLExoticsExport2.txt
- Screenshot of an ExoticsExport1 compatible sql expression in the Data Window SQL Expression Tool:
  http://www.jcapper.com/messageboard/avatars/SQLExoticsExport1.jpg

Click the Execute button to execute the query.

Notes and Comments

The Data Window will execute the query in the normal manner. When the query results are returned - the Data Window will break the query results out by the Exotic Ticket Structure Matrix that was selected from the Factors Drop Down in step 2. (above.)

Screenshot of Trifecta results displayed in the Data Window:
http://www.jcapper.com/messageboard/avatars/DataWindowTriMatrix01.jpg

Note that in the above screenshot, I added some further factor constraints to the initial "Select * From ExoticsExport1" expression before clicking the Execute button.

I did this because I wanted to point out that the only real difference between this and executing a SQL UDM - is that here - you are executing the query against either the ExoticsExport1 or ExoticsExport2 tables in the connected to JCapperSDK .mdb file -- whereas with a SQL UDM - you are executing a query against the StarterHistory table in a connected to JCapper2 .mdb file.

In both cases, the basic steps for operating the Data Window are nearly identical.

Trifecta Matrices

Below is a brief explanation for two of the Trifecta Matrix options in the Data Window Factors Drop Down:

Trifecta Matrix2 (UPRa-UPRb-UPRcde)

Ticket structure explanation:
- Selecting this option causes the Data Window to display Trifecta Matrices for a simple Trifecta part wheel.
  
  Starting with the very first (rank=1) matrix:
  
  The first row in the matrix displays stats for rank=1 in the top slot / over rank=2 in the second slot / over ranks 3-4-5 in the bottom slot.
  
  The second row in the matrix displays stats for rank=1 in the top slot / over rank=3 in the second slot / over ranks 2-4-5 in the bottom slot.
  
  Each of the remaining rows displays stats based on a similar pattern.
  
  The pattern for the stats in each row are always described by the text that starts off that row.
- Even though the option displayed in the factors drop down (without the quotes) is labeled as "Trifecta Matrix2 (UPRa-UPRb-UPRcde):
  
  The stats displayed in the Data Window are always based on the factor of your Top Line Power Rating.
  
  Further, and looking specifically at (without the quotes) the letters a, b, c, d, and e from "(UPRa-UPRb-UPRcde)":
  
  "a" means rank=1, "b" means rank=2, "c" means rank=3, etc. (Again not for UPR but for the factor of your Top Line Power Rating.)

Trifecta KeyBox (UPRa-UPRbcde)

Ticket structure explanation:
- Selecting this option causes the Data Window to display a Trifecta Matrix based on a Trifecta key box.
  
  The first row in the matrix displays stats for rank=1 in the top slot / over ranks 2-3-4-5 in the second slot / over ranks 2-3-4-5 in the bottom slot. (Or a Key box with rank=1 on top of ranks 2-3-4-5.)
  
  The second row in the matrix displays stats for rank=2 in the top slot / over ranks 1-3-4-5 in the second slot / over ranks 1-3-4-5 in the bottom slot. (Or a Key box with rank=2 on top of ranks 1-3-4-5.)
  
  Each of the remaining rows displays stats based on a similar pattern.
  
  The pattern for the stats in each row are always described by the text that starts off that row.
- Even though the option displayed in the factors drop down (without the quotes) is labeled as "Trifecta KeyBox (UPRa-UPRbcde):
  
  The stats displayed in the Data Window are always based on the factor of your Top Line Power Rating.
  
  Further, and looking specifically at (without the quotes) the letters a, b, c, d, and e from "Trifecta KeyBox (UPRa-UPRbcde)":
  
  "a" means rank=1, "b" means rank=2, "c" means rank=3, etc. (Again not for UPR but for the factor of your Top Line Power Rating.)

Superfecta Matrices

The available Superfecta Matrix options in the Factors Drop Down cause the Data Window to behave in a manner that is virtually identical to that of the Trifecta Matrix options.

The key difference being that the stats presented in the matrices themselves are based on the amount wagered and payoffs for Superfecta (instead of Trifecta.)

Misreported Exotic Payoffs

On April 03, 2016 at LRL in R6, a full field of 12 went postward in a 5.5f turf sprint - and a horse named PUTURSEATBELTON won at odds of 40.10 to one.

The Equibase chart lists a $1.00 superfecta payoff of $140,028.80.

But there's a huge problem with that. (That $140k payoff isn't just misleading. It's a flat out lie.)

There was only $37,766.00 in the superfecta pool (with a base wager amount of 10 cents.)

The CORRECT superfecta payoff was $14,022.88 for each $0.10 wagered.

A very nice payoff - but nowhere close to the $140k the track would have you believe.

This is not an isolated example. (You'll likely run across several of these in any sizeable sample.)

Best Practice for dealing with misreported exotic payoffs:

When selecting a Base Wager Amt from the drop down: Always choose the lowest denomination possible (within reason) for your Exports.

Ideally, you want the Base Wager Amt of your Export(s) to be the same as that found on the wagering menu(s) of the track(s) where you play (usually $0.10 for Superfecta Exports, $0.50 for Trifecta Exports, and $1.00 for Exacta Exports, etc.)

Using the lowest possible Base Wager Amt reduces the likelihood that outliers (like the one in the above example race) will be introduced into your exported exotics data.

Regularly export data from the ChartRaces table in the connected to JCapperSDK .mdb file out to .CSV file on your hard drive.

From there: Open up the .CSV file in Excel - and LOOK AT THE DATA.

Specifically - for any race that has exceptional exotic payoffs: Look at the base wager amount - and compare the exotic payoff against the amount in the pool.

When you make a habit out of looking at ChartRaces data in Excel: You increase the likelihood of catching outliers that may be present in exported exotics data.

Before using exported exotics data for mapping out ANY wagering strategy that you intend to back with real money:

You need to be aware of - and account for - the outliers in the data.

This section of the help doc is still being written.

Check back frequently for updates...

-jp

.

<-- Back to Table of Contents

BASIC STUFF YOU NEED TO BE AWARE OF:

This section of the help doc is still being written.

Check back frequently for updates...

-jp

.

Final Thoughts and Comments

This section of the help doc is still being written.

Check back frequently for updates...

-jp

.