Excel template for all 1435 Bris fields

[raybo]

I am creating an Infotran control file and an Excel spreadsheet that will put all 1435 fields, in the Bris and TSN single file format data files, in the spreadsheet and create the PPs. This will be designed to work with Excel versions '97-'03 as well as 2007.

I need input from those interested concerning what should be included in the spreadsheet, as a template type app.

[Tom]

You mean like calculating things from the basic data?

[raybo]

Originally Posted by Tom

You mean like calculating things from the basic data?

That and what the different views should look like.

[raybo]

I have completed the control file for use with "Infotran/ITF.exe". It will place all 1435 fields in Bris' or TSN' single file format comma-delimited data files in Excel, solving the problem with Excel's columnn limitation of 234 columns, for versions prior to Excel 2007.

I will try to attach it here.

You can open the file in "Notepad" or any other standard text editor.

Below the file name (AllData.brs) is an explanation for what you see.

Note: Lines beginning with */ are comment lines and are ignored by Infotran. Lines beginning with a positive number are the actual fields from the comma-delimited raw data file. Lines beginning with -1 are blank fields (or cells in Excel). Lines that begin with -111 denote the end of an Excel row, thus, the next field number will appear on the next Excel row.

Each horse's data will encompass 15 rows in Excel. The same data type, for each horse is incremented by 15, so, for example, today's track name for horse #1 appears in cell "B1", if you increment that cell location by 15 you get "B16", thus, today's track name, for Horse #2, will appear in cell "B16", similarly, for horse #3, it will appear in cell "B31", etc.

I will attach instructions for the use of "Infotran", which is a free download on the Brisnet site, and the procedure for importing the resulting data into Excel, in a future post.

This control file will work for all versions of Excel (of course, in Excel 2007, you could simply open the comma-delimited file directly in Excel, but, the resulting data will not be organized, for easy reference, as it is using "Infotran")

I would appreciate it if several of you would double check the control file to make sure I haven't left out any of the data fields, (my eyes started crossing several times while writing this control file, so.....) the possibility exists that I may have left a data field (or 2 or 10 ) out of the file.

PS: I had to "zip" the file in order to upload it here. Just "unzip" it and open the "AllData.brs" file in a text editor"

[raybo]

The attached file is an explanation about how to use "Infotran"

[raybo]

Here's the next explanation file.

[raybo]

And, the final explanation file.

[raybo]

I'm still waiting for some suggestions concerning what the spreadsheet should look like and what views should be included (like basic DRF style PPs, include Sartin figures, include a summary view that shows all the horses' using each horse's selected paceline, etc.)

If I don't get some input on this I will be forced to create what I would want to see, and, some of you won't like it, I'm sure.

[Warren Henry]

Originally Posted by raybo

I'm still waiting for some suggestions concerning what the spreadsheet should look like and what views should be included (like basic DRF style PPs, include Sartin figures, include a summary view that shows all the horses' using each horse's selected paceline, etc.)

If I don't get some input on this I will be forced to create what I would want to see, and, some of you won't like it, I'm sure.

Ray,

Do it YOUR WAY. There will be some that don't like it no matter what you do, so you might as well fix it as you would prefer.

You have done the hard part, anyone else should be able to figure out how to modify it for their own purposes.

Thanks for your efforts.

[Tom]

Big thanks, Raybo, that was a lot of work.
I will definitely check it all out.

[vikingrob]

Another thing to consider, but Excel 2008 for the Mac will take up to 16,384 (2^14) columns. Now if only there was an easier way to transpose a CSV.....

The fact that a DRF single file has over 1,000 fields per horse is one of the weaknesses in the format. In the days when I was loading BRIS data into Excel, I was using the DRF multi format for that purpose, because you won't go out of the A1:IV65536 range. Granted, a little creative use of the VLOOKUP() function could make a perfectly usable file from the result, but then I have created tools such as one that can look at all combinations of up to 15 numbers to find one that adds up to a certain sum, and another that reduces the entire NFL regular-season schedule to a one-page matrix.

[raybo]

Originally Posted by vikingrob

Another thing to consider, but Excel 2008 for the Mac will take up to 16,384 (2^14) columns. Now if only there was an easier way to transpose a CSV.....

The fact that a DRF single file has over 1,000 fields per horse is one of the weaknesses in the format. In the days when I was loading BRIS data into Excel, I was using the DRF multi format for that purpose, because you won't go out of the A1:IV65536 range. Granted, a little creative use of the VLOOKUP() function could make a perfectly usable file from the result, but then I have created tools such as one that can look at all combinations of up to 15 numbers to find one that adds up to a certain sum, and another that reduces the entire NFL regular-season schedule to a one-page matrix.

I think you will see the advantage of using Infotran, after I start designing the spreadsheet. I'll be able to use a 15 row format for each horse and only have to write formulas for 1 horse. Because the "transposed" csv file is also in a 15 row format, I can just copy the first horse's formulas down the sheet to create all the other horses in the race. Then I'm done. It will take very little time to create a DRF style "Form" this way. The first time I created a spreadsheet it took over 2 weeks of writing formulas to get the basic PPs.

[CBedo]

Originally Posted by vikingrob

Another thing to consider, but Excel 2008 for the Mac will take up to 16,384 (2^14) columns. Now if only there was an easier way to transpose a CSV.....

I have a Ruby program I wrote (all of about 10 lines of code) that transposes an entire csv file. I'm more than willing to share, but Ruby doesn't like to be compiled, so it works best with Ruby installed on your machine.

I think there is a "script to exe" program that will collect the program and all its dependencies and package them up so it works like a standalone executable so you wouldn't need a Ruby installation. I can try that if anyone wants.

If anyone is interested let me know.

[raybo]

I've almost got the basic PPs template ready, complete with automation macros. I'll attach it as soon as I feel it's in a presentable form.

This will be the 1st version of the template, as I will add other "views" as I get suggestions and get them incorporated. I will do a Sartin velocities view for sure and plan to incorporate a summary view that will allow the user to use a computer generated paceline selection or override some or all of those with manual paceline selections. The summary view will contain all horses in the race along with each horse's selected paceline data, all in one view. This makes comparisons between horses and being able to see "the big picture", for the race (like pace scenarios, etc.), much easier than just looking at the PPs or other views.

I'm still open to suggestions from everyone concerning what "views"/methods you want incorporated into this thing.

PS: I will attach a ".xls" file first but can convert it to ".xlsm" for those with Excel 2007.

[raybo]

Ok, I've got the basic PPs portion completed, I think .

I also have an update to the "Infotran" control file that I attached in an earlier post, so, please replace the original one with the new one, as I found a couple of errors that will affect where the data fields go in Excel.

Just follow the previously attached instructions to run "Infotran" to load your Bris or TSN data file into the "target.txt" file in your "C:/Infotran" folder.

Once your data file is in "target.txt" you can close everything and open the attached "ALLDATA PPs.xls" file in Excel. (I'll probably have to zip the file, in order to meet the attachment size requirement here at PA, so please unzip it after you get it)

When it opens you will be looking at the "Current Race Card"/"Race Selection" area of the spreadsheet. Click the "Load New Race Card" button just below the "Current Race Card" data. This macro button will load the "target.txt" file into the "ALLDATA PPs" spreadsheet (the race card data starts in cell "EA1"). In the "Current Race Card" area (yellow filled cells) you will see the race card info for the race card you just loaded. Notice there is no race number yet, telling you that you have not selected a race, from the card, to work with yet.

In the area below the "Load New Race Card" macro button, in the "Race Selection" box (blue area), you will see 20 macro buttons with race numbers on them. These macro buttons, when clicked, will copy that particular race data and paste it to the race data area (starts in cell "A1"). Also, when you click one of the race buttons you will be taken to the PPs area where you can view my version of the "Daily Racing Form" PPs.

The PPs are pretty much self explanatory, but, I have inserted some labels for clarification purposes. The PPs will accomodate up to 20 horses per race, so, some of the PPs in the lower portion will be blank due to the race having less than 20 horses entered. This PPs area is used, over and over, for any race on the card you select in the "Race Selection" area.

So, in summary:

1. Run Infotran/ITF.exe.
2. Open "ALLDATA PPs.xls" in Excel.
3 Click "Load New Race Card" button.
4. Click any of the "Race #" buttons.

PS: At the top of the PPs area you will see another macro button named "New Race". This macro button, when clicked, takes you back to the "Current Race Card"/"Race Selection" area.

Let me know what you think of the spreadsheet and if you have any problems or suggestions.

There's more to come folks, so, don't think that the basic PPs are all that you will be able to do with this spreadsheet.

Again, if any of you have requests for particular handicapping methods or views, please let me know.

All ideas/suggestions are welcome!