|From the email inbox:|
"Hi Jeff, --end quote
I'm not a programmer. But I eventually want to write my own macro or vba function to handle .JCP file data in Excel.
I'm new to JCapper but very familiar with HDW data.
If my first order of business is to pull PSR plus a handful of other data fields into Excel:
What's the easiest way to hit the ground running?"
On most machines where Excel has been installed - the default Windows File Associations are set so that if you double click a .CSV file it will pop open in Excel.
For that reason my first reaction would be:
Create a daily .CSV file that contains the specific data fields you want.
And from there, bring that file into Excel where you can see the data and work with it.
If I were doing this myself - I'd create a stand alone VB program or a VBA function in Excel that would:
- Open .JCP files for race cards I am interested in.
- Read in selected data fields for each horse.
- Write selected data fields as output to a .CSV file.
That's what I would do.
But if I didn't have the ability to create that inside of an hour or two...
Until such time as I could create a VB program or a VBA function in Excel to get that done...
I would use the following high level strategy to create the same output .CSV file using JCapper:
Each Day I would do the following:
- Download HDW data files for today's race cards.
Note that this step includes running .JCP File Build Routines on the downloaded HDW files. This creates comma delimited text .JCP files on the current active data folder of the hard drive.
Note that the entire file download process can be automated. But recommend you get the hang of running the HDW File Download Tool manually first. (And then automate once you have a clear understanding of the manual process.)
- Use the DFM Card Loader to load .JCP files into JCapper.
- Use Scratch BOT to get scratches and changes.
- Run a SQL Calc Races
- Use the JCX File Exports Module to execute a saved sql expression and export a daily .CSV file out onto the hard drive.
Note that the .CSV file will contain the exact data fields you want as defined by the saved sql expression. More on that below. (For now I'm just covering the basic steps from a high level.)
- Double click the .CSV file created in the above step to open it in Excel (so that you can see and manipulate the data.)
Ok. That's the daily strategy (from a high level.)
Now, let's give each of those areas a closer (ground level) look:
Download HDW data files
Start by going to the JCapper 101 Audio Video Page at the following link:
Watch the The DFM (Data Folder Manager) - Basic Operating Instructions video in the JCapper BASIC section:
You need to know how to use the DFM to control the current active data folder. That's where files downloaded from the HDW site end up. (And it's covered in the video.)
Watch the Monthly Downloaders: Downloading Files from the HDW Site (and building comma delimited text .JCP Files) video in the Working with HDW Data section:
You need to know how to use the HDW File Download Tool to queue up data files and download them to your current active data folder. (Covered in the video.)
You need to know how to use the File Build Filter Tool to run JCP File Build Routines on HDW binary race files downloaded to your current active data folder. (Also covered in the video.)
Once you are confident you can do the above without referring back to the videos...
Let's Automate the File Download Process a bit:
- On the Main Module click System Settings.
- On the System Settings Interface click Enhanced Settings.
- In the Enhanced Settings Module - change four of your settings so that they match the four highlighted settings shown in this screenshot:
- Click the SAVE button after you make the four settings changes and x-out of the Enhanced Settings Module.
Now, with the correct settings persisted - and you shouldn't ever have to touch those settings again...
From this point forward - each day when it's time to download data files...
DOWNLOAD YOUR DATA FILES Using THESE STEPS:
- Launch the HDW File Mgr.
- Click MENU - select SWITCHES - then select GETDATAFILES TODAY AUTOBUILD NOEXIT - and answer YES at the prompt to run the switch.
Here's a screenshot of what the Switches Menu looks like in the HDW File Mgr:
Each time you run the GETDATAFILES TODAY AUTOBUILD NOEXIT switch...
The HDW File Manager will perform the following tasks automatically:
- Navigate the HDW site and queue up today's data files.
- Download today's data files to the current active data folder persisted in the DFM.
- Run a .JCP File Build Routine on files downloaded in the previous step and create .JCP files on the current active data folder.
--The best part is that ALL of this runs automatically without you having to click anything else.
Use the DFM Card Loader to load .JCP files into JCapper.
Start by going to the JCapper BASIC section of the JCapper 101 Audio Video page at JCapper.com:
Then watch the Loading .JCP Files into JCapper using the DFM Card Loader video.
You need to know how to use the DFM Card Loader to load .JCP files into the program on at least data folder one. Better if you understand how to break up your race day using all three folders depending on how many race cards you plan on playing. (But data folder one at a minimum.)
Once you are confident you can use the DFM Card Loader without referring back to the video - then it's time to move on to the next step.
Use Scratch BOT to get scratches and changes.
Start by going to the DAILY REPORTS section of the JCapper 101 Audio Video page at JCapper.com:
Then watch the SCRATCH BOT - Parsing Scratches and Changes from the Internet video.
You need to know how to click the XML button in Scratch BOT to get current scratches and changes.
FYI, the video at the above link is from 2009 and Scratch BOT has been upgraded many times since then.
FYI, the most recent versions of JCapper Build 198 (both Silver and Platinum) arrive on your hard drive with a current set of .JCP File compatible entries in the TrackAbbreviations table - enabling you to parse the XML for scratches, races off the turf, and race distance changes for all thoroughbred track codes currently running as I type this (mid May 2017.)
And after making the four Enhanced Settings changes (above) you will be equipped to parse and process track condition changes and rider changes from the XML.
Meaning that the main thing you really need to know (initially) about operating Scratch BOT is that scratches and changes start showing up about 15 mtp for R1 at most track codes - that scratches and changes are updated in something approaching real time during each race day - and that the easiest way to get current scratches and changes into JCapper is to click the XML Button in Scratch BOT for a given folder prior to running a Calc Races for that folder.
FYI, you can also find complete background info in the Processing Changes With the New Scratch Bot on the Help Docs page at JCapper.com - here:
FYI, you can also find complete background info about using Scratch BOT to process Rider Changes in the Rider Changes Help Doc on the Help Docs page at JCapper.com - here:
But, at the end of the day, the main thing you need to know (initially) about operating Scratch BOT is that the easiest way to get current scratches and changes into JCapper is to click the XML Button for a given folder prior to running a Calc Races for that folder.
FYI, any time you are operating JCapper on a machine that is not connected to the internet - then you should know how to process scratches and changes manually.
For that you want The Manual Changes Module - and to that end (at some point) you should read the Manual Changes Help Doc and you should spend a little time saving some manual changes to make sure you know how. (Both as a backup and when there's a gate scratch and you don't have time to wait for the scratch to be propagated to the XML.)
Link to the Processing Changes With the Manual Changes Module Help Doc - here:
Once you are confident you can use the XML button in Scratch BOT without referring back to the video - then it's time to move on to the next step.
Run a SQL Calc Races
Note that I said a SQL Calc Races.
JCapper can be operated in two modes: Playlist File Mode and SQL Mode.
Playlist File Mode is simpler and easier for the less technically inclined. In Playlist File Mode the UDMs you create involve using the UDM Wizard to select factor names from a drop down... clicking a test box to make that factor active... keying min and max ranges into boxes... and hitting the Save button.
SQL Mode is for those who are more technically inclined. And in my opinion SQL Mode is infinitely more powerful than Playlist File Mode.
In SQL Mode the UDMs you create are based on SQL Expressions that you create.
SQL has been around for a long time and many of you have been exposed to it in school or on the job. I'm in my late 50's right now as I type this. (Believe it or not I was first exposed to the sql programming language my freshman year of college!)
SQL isn't terribly difficult so far as programming languages go. (And you really should plan on learning it if you want to get the most out of JCapper.)
That said, the first thing you need to know about SQL Mode is where to find the setting for switching back and forth between SQL Mode and Playlist File Mode.
FYI, the setting is on the User Sys Defs Screen. To bring up the User Sys Defs Screen: Click System Settings on the face of the JCapper Main Module. Then, on the System Settings Interface: Click System Definitions.
To change modes you simply bring up the User Sys Defs Screen, check the box, and hit the Save button. It's really that simple. (But you have to know how.)
FYI, The JCapper User System Definitions Screen Help Doc can be found on the Help Docs page at JCapper.com - here:
Hint: The checkbox is located in the upper right hand area of the screen and it's clearly shown in the first screenshot at the very top of the help doc.
How to run a SQL Calc Races:
- With .JCP Files loaded into JCapper via the DFM Card Loader
- And after getting scratches and changes
- And with JCapper set to run in SQL Mode
- Click the Calc Races button.
Note that if you loaded .JCP files into JCapper on Data Folder One in the DFM Card Loader - to run a Calc Races on the .JCP files that you loaded using Data Folder One: Click the Calc Races button for Data Folder One.
Note that if you loaded .JCP files into JCapper on Data Folder Two in the DFM Card Loader - to run a Calc Races on the .JCP files that you loaded using Data Folder Two: Click the Calc Races button for Data Folder Two.
Note that if you loaded .JCP files into JCapper on Data Folder Three in the DFM Card Loader - to run a Calc Races on the .JCP files that you loaded using Data Folder Three: Click the Calc Races button for Data Folder Three.
Clicking the Calc Races Button for any of the three data folders will cause a SQL Calc Races to be run on the .JCP files loaded into the program on that folder.
During the Calc Races you see progress bars that (slowly) fill as the events that perform number crunching are executed. At the end you'll get a Calc Races completed message.
At this point, if you are operating the program in SQL Mode: The data that you need for your export (for the race cards that you just ran a Calc Races for) is now sitting in the StartersToday table of your c:\JCapper\Exe\JCapper2.mdf file.
From here, it's time to move on to the next step.
Use the JCX File Exports Module to execute a saved sql expression
FYI, not now - but when you have time - you'll want to read The JCX File Exports Module on the Help Docs page at JCapper.com - here:
Understandably there's a lot there to digest.
For now I'm just going to provide step by step instructions -- with screenshots -- and a link to a sql expression you can use -- to drive the export you will use to create a .CSV file on your hard drive that will enable you to get PSR plus a handful of other data fields into Excel.
BASIC OPERATING INSTRUCTIONS for saving the SQL Expression - and USING IT TO DRIVE YOUR EXPORT and CREATE YOUR DAILY .CSV FILE:
- Launch the JCX File Export Module -- On the Main Module click System Settings. Then on the System Settings Interface click the JCX File Exports button. (That's it. Module launched.)
- Launch the SQL Expression Tool -- Click the "Use Custom SQL Expression" box on the face of the JCX File Exports Module to launch the SQL Expression Tool.
- Click the following link -- to get a text file that contains a sql expression you can use as a template for this project:
Note that in the sql expression at the above link I am referencing the [date], track, race, saddlecloth, horsename, and valf30 fields of the StartersToday table.
FYI, in the Default SQL Factor Setup for JCapper Silver: PSR is assigned to the VALF30 field. (Meaning that the VALF30 field contains PSR.)
- Having clicked the above link to the PSR_Export.txt file: Copy and Paste the sql expression from the text file into the large box on the face of the SQL Expression Tool.
When you are done - your SQL Expression Tool should look like the SQL Expression Tool in this screenshot:
- With the SQL Expression from the text file pasted into the SQL Expression Tool: Key a name for the SQL Expression into the Name field and hit the Save button.
Note that in the following screenshot, I used "_PSR-Export" (without the quotes) as the name for my new SQL Expression. And that after keying a name into the Name field - and after hitting the Save button - Your SQL Expression Tool should look like the SQL Expression Tool in this screenshot:
Q. Why give the SQL Expression a name and Save it?
A. Saving a SQL Expression enables you to pull it up later (by name) and re-use it.
To pull up a saved SQL Expression: Click the SQL Expression Names drop down - and then select the sql expression you want (by name.)
As soon as you click the name of a saved sql expression in the drop down: The interface will display that sql expression in the SQL Expression Tool.
So the next time you go about completing these steps to create a daily export .CSV file: Select the sql expression (by name) from the drop down - instead of keying it manually (or cutting and pasting it from the linked to text file.)
Here's a screenshot:
- Click the APPLY button and then click Yes at the prompt. This will apply the sql expression (to drive your export) and close the SQL Expression Tool.
The following screenshot shows what your SQL Expression Tool should look like after clicking APPLY and right before clicking Yes at the prompt:
- Click the Browse button on the face of the SQL Expression Tool. This will launch a Dialog Tool that will auto-point at your c:\JCapper\Exe folder.
Note: The Dialog Tool provides visual elements that will enable you to point it at any folder on your machine. (Use those elements to point at a different folder as needed.)
- Key the filename for your .CSV export file into the File Name box on the Dialog Tool and click the Open button. This will close the Dialog Tool and cause the interface to paste the file name from the Dialog Tool to the Output File Name box on the face of the JCX File Exports Module.
The following screenshot shows the Dialog Tool after I keyed "psr-export-05182017.csv" (without the quotes) into the File Name box on the Dialog Tool just before clicking the OPEN button:
The following screenshot shows "c:\jcapper\exe\psr-export-05182017.csv" (without the quotes) pasted by the interface into the Output File Name box of The JCX File Exports Module after clicking the OPEN button on the Dialog Tool:
- Click the CREATE EXPORT button on the JCX File Exports Module to run the export.
Clicking the CREATE EXPORT button causes the interface to execute the sql expression that was applied in step 6 (above) and write data pulled from (in this case) the StartersToday table to the .CSV eport file.
The following screenshot the shows the JCX File Exports Module after after clicking the CREATE EXPORT button:
At this point the .CSV file is now sitting on your hard drive.
- Use Windows Explorer to Navigate to your c:\JCapper\Exe folder. (Or alternately, the folder you used in the File Name box in step 8 above.)
The following screenshot shows the "c:\jcapper\exe\psr-export-05182017.csv" file (without the quotes) displayed in Windows Explorer:
- Double Click your .CSV File to open it in Excel.
The following screenshot shows the "c:\jcapper\exe\psr-export-05182017.csv" file (without the quotes) displayed in Excel:
Note that the columns in the resulting .CSV file are the same as the data fields (or column names) referenced in the sql expression that was used to drive the export.
Note that each row contains data for one horse.
Also note that PSR is displayed in the VALF30 column.
At this point you have horse data with PSR sitting in Excel.
Repeating the above steps will enable you to create a daily .CSV file that can be opened in Excel.
Be aware that you are by no means limited to the column names I used in the above example.
The following link will get you a text file that contains field mapping for all of the fields (or column names) in the StartersToday table:
~Edited by: jeff on: 5/21/2017 at: 5:36:07 AM~