[NCNCA] Complete 2006 BAR/BAT results

[kenhernandez at comcast.net]

Complete 2006 BAR/BAT results are now posted at http://www.ncnca.org/docs/.  
I am resuming my duties as BARBAT coordinator for 2007, but I didn't want the 2006 season to go without complete BARBAT results being posted. I requested and received what BARBAT data that was available from 2006 on 12/22/06. The data was incomplete and required a considerable amount of work so I decide to start from scratch. 
If your are wondering how this was done (in agonizing detail) read on.  I compiled the BARBAT data from the NCNCA 2006 road web page archive. I saved a copy of each race results page in a separate text format file. For most races this was simple, but for those sites that use a fancy web page to list their result this was very difficult (but not impossible). I then stripped out all but the top 10 results for each event and converted each text file into a tab or comma separated file with the following fields.
place, license#, first name, last name, race team, race name, race category, field size, race date 
Each results file was then imported into a separate excel spreadsheet that included the USCF database that correlated to the date of the race. Normally during the year I would download the USCF database every Friday and save it by date. Since I had given up the BARBAT duties at the end of 2005, I didn't have a complete set of database files for 2006, but I did have enough to do a reasonable job of determining when a rider was issued their USCF license. The spreadsheet has and additional field for BAR points. Bar points are then added to every rider based on field size. 
Each of these race result spreadsheets has two macros. The first macro does a cross reference and uses the first and last name listed in the results and looks up a license number in the USCF database sheet that is loaded for that race date. If a license is not found for the riders name, then a "?" is entered in the license field. Of course this relies on the correct spelling of each riders name. This works on about 80% of the names in the results. I run this process regardless if a license number is listed in the results. This insures that only riders on that date in the NCNCA region with valid licenses are awarded points (the database I download from the USCF is only for the NCNCA region). The next step is to sort the data by license#, last name, & first name. All the licenses with a "?" I have to look up manually to check for name misspellings or other anomalies that prevented the first macro from locating the rider's license number. The licenses numbers of riders that are 
 locate
d are then entered, otherwise they remain "?". All riders without a license number have their BAR points set to "0". The second macro then fills in the rider name and team base from the database info. This ensures that every race will contain the same data based on the license number. The process from text file to a clean spreadsheet takes about an hour per race.
The compiled data from each race spreadsheet is copied and pasted into the master BARBAT spreadsheet. This spreadsheet has a "Teams" worksheet that contains all of the USCF clubs with a field for eligibility (I'm adding an additional field to include the active date for 2007). If the USCF club is also a NCNCA member club, then the eligibility field is set to "1" otherwise it's "0". The "Points" worksheet contains all the previously copied data from every race for the entire year. For 2006, this sheet contains 6,938 race result entries for the top 10 results for 76 separate races that I was able to find from the 2006 road page (781 race events). 
The BARBAT spreadsheet has a series of Visual Basic macros. The "CatPoints" macro goes through each line in the "Points" sheet and checks the race category. If the race category qualifies for BAR then the points are entered in a separate sheet for each BAR race category below.
Elite 1/2/Pro, Elite 3, Elite 4, Master 35+ 1/2/3, Master 35+ 4/5, Master 45+, Master 55+, Women 1/2, Women 3, Women 4, Women 35+ 
The "TeamPts" macro also goes through each line in the "Points" sheet this time checking for team eligibility. If the race team matches a team in the "Teams" sheet and eligibility is "1" then BAT points are added to that team in the "team points" sheet. If the team is eligible then the points are added to the "BAT" sheet whether or not the event qualifies for BAR points. Several USCF and NCNCA member Clubs have multiple "Teams". For example, in the USCF database, Alto Velo Racing Club has riders listed as "Alto Velo Racing Club", "Webcor/Alto Velo", and "Webcor-Platinum". In this case all three are eligible for BAT points. It is therefore important that each rider has the correct team listed on his or her license. The remaining macros output the data into HTML files that are posted on the NCNCA web page. 
This process is very manageable if performed on a weekly basis and I have agreed to post results at least every other week for 2007. But to do a whole years worth of data a one time was very "intense". It took me 12 days (over 70 hours) to complete the task. 
Ken Hernandez
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sonic.net/pipermail/ncnca/attachments/20070104/3c7bcf77/attachment.html