|
JCapper Message Board JCapper 101
|
| Home |
Register
Log In |
| By | Playlist to SQL - Data folders/Validation &also OL | |
| PhilCVG 8/8/2013 7:38:54 PM | Gents, I'm finally going to bite the bullet and transfer the way I use JCapper from playlist to SQL. So far so good. I'm built a database on my 13Q3 folder and even made a UDM for synthetic surfaces. All seems well. I haven't messed with switching factors in or out nor editing the HTML report. Small steps and so far the factors are generally what I'd look for and the HTML report is much nicer than the playlist model. I also realize that I should edit the factors sooner rather than later to keep things consistent going forward. My question is ... My folders are currently set up 2011 Q1, 2011 Q2, 2012 Q1 etc. I build off of the 2011 Q2 playlist file and test against 2012Q1 or 2013Q3 as examples. However, it seems like in SQL mode, the database is built on the JCapper.MDB file in the main folder as oppose to each individual subfolder. That requires quite a bit of time to build. So, rebuilding the database for each month doesn't seem to be a very effective way of doing things. Am I to assume that it is best just to build the complete database on the JCapper.MDB folder and than use date ranges in the SQL windows to build off of small time frames within the database, than change those date constraints to test against new races? I hope that makes sense, my terminology probably isn't correct but I hope someone understands what I'm asking. Also, can anyone explain the OL factor on the HTML report? I'm assuming it is an Odds Line?
| |
| jeff 8/9/2013 1:30:46 PM | You wrote: --quote: "My question is ... My folders are currently set up 2011 Q1, 2011 Q2, 2012 Q1 etc. I build off of the 2011 Q2 playlist file and test against 2012Q1 or 2013Q3 as examples. However, it seems like in SQL mode, the database is built on the JCapper.MDB file in the main folder as oppose to each individual subfolder. That requires quite a bit of time to build. So, rebuilding the database for each month doesn't seem to be a very effective way of doing things."--end quote My reply: Your folder structure seems fine. Since you've already run build database routines on those folders, I'm thinking the next logical question would be getting the following question answered: Q. "How do I populate the StarterHistory table in my c:\JCapper\Exe\JCapper2.mdb file with data from my individual folders?" A. There are two ways: 1. Database Build Routines that are run in Modes 3, 4, and 5. 2. Data Window Export. If you used Modes 3, 4, and 5 to build databases on your folders - then the StarterHistory Table in your c:\JCapper\Exe\JCapper2.mdb file is populated and ready to go. From here the next step would be to fire up the Data Window in SQL Mode and learn the process of creating and executing sql expressions. Hint: Kicking the Data WIndow into SQL Mode is accomplished by: A.) Making SQL Mode the persisted setting on the User Sys Defs Screen before launchbng the Data Window. B.) Using the Data Window Reset Button. C.) Using the Data Window MENU. If you used Modes 1 and 2 when you ran build database routines on your folders - your StarterHistory table hasn't been populated yet. If this is the case, your options for populating it would be a Mode 3 build on your first folder - followed by Mode 4 builds on your remaining folders... Or, alternately, you can populate the table through Data Window Exports. Here's are links to a pair of previous threads where I've posted lots of relevant background info as well as how-to instructions for populating a starterhistory table... JCapper2.mdb file - what you need to know: http://www.jcapper.com/MessageBoard/TopicReader.asp?topic=1340&forum=JCapper%20101 Rebuilding Databases From Scratch - Folder Structure - Build Mode - Instructions: http://www.jcapper.com/MessageBoard/TopicReader.asp?topic=1520&forum=JCapper%20101 Also, here's a link to a previous thread where I've posted detailed information about JCapper related files: http://www.jcapper.com/MessageBoard/TopicReader.asp?topic=1097&forum=JCapper%20101 -jp .
| |
| PhilCVG 8/9/2013 4:05:51 PM | Thanks Jeff ... My previous builds have all been in mode 1. So the first population of the Starter Table was done in mode 3 on my 2013 Q3 folder. It seems from here on out, populate the rest of the .mdb file in mode 4 append with write to starter historytable and build as append checked? I think I understand how to populate the table, my question now is if all the data is written to a single .mdb file, how do you validate it against new data? For instance, right now, I"ll build a UDM off of my 2013 Q2 playlist file, than validate it against 2012 Q2 or 2013 Q3 playlist file. Do you constrain the dates while building UDM in SQL mode somehow? ~Edited by: PhilCVG on: 8/9/2013 at: 4:05:51 PM~
| |
| PhilCVG 8/9/2013 4:16:21 PM | And giving it some more thought, being that I have playlist files written out for all the data in each yearly, quarter folder, it would be much quicker to populate the start history table via the data window/play list file/export mode would it not?
| |
| jeff 8/9/2013 8:15:06 PM | Yes. Going the Data Window Export route (Clear first on the first export and Append on each subsequent export) is faster than running db build routines. And yes, if you go the db build routine route you would use Mode 3 on the first folder and Mode 4 on each subsequent folder. That said, the end result is the same whether you use Data Window Export or db build routines... You get a fully populated StarterHistory table in the c:\JCapper\Exe\JCapper2.mdb file. If you are doing a bunch of folders be careful not to let the c:\JCapper\Exe\JCapper2.mdb file reach 2.0 gigabytes (or 2000mb) in size. Once you have a populated StarterHistory table, breaking it into segments for testing is easy. One way would be to add date constraints to your sql expressions... like this: AND [DATE] >= #01-01-2012# AND [DATE] <= #12-31-2012# The above two lines will restrict the returned results set to starters that raced between Jan 01, 2012 and Dec 31, 2012 inclusive. IMPORTANT: Note that I've wrapped the date field in square brackets. IMPORTANT: Note that I've wrapped the date text in pound characters. HINT: BOTH are critical. UDM Development and Validation Strategy From a strategy standpoint, here's the gist of what I do: Develop a model using a development sample spanning a defined time period. During development, I will tune the model as I see fit - never straying outside of the development sample (unless I want to expand the development sample - more about that to follow shortly.) Each time I make a change to the UDM definition - I will use the TAG button on the Modify screen of the UDM Wizard to "TAG" the UDM with a date last modified so that when I run the UDM through the Data Window the tag date gets displayed as part of the results set... That tells me the date I last modified the UDM. (Or the outer boundary of my development sample.) After I am done tuning, I will confront the model with fresh races from a time period AFTER the tag date. Decisions related to promoting a model to live play status are based on its performance going forward in time against fresh races outside of the development sample. I am always tinkering with new ideas in the Data Window (creating UDMs.) Often times I will tag them with a date last modified and let them sit unused and un-looked at for few months. But eventually, I'll get around to looking at them. When I run one the first time I'll note the tag date. When I run it the second time I'll add sql date constraints (as illustrated above) to restrict the results set to AFTER the tag date. From that, the results set returned in the Data Window will show me UDM performance outside of the development sample. I'd like to think I'm as creative and experienced at creating UDMs as anybody. I can't even count the number of times I've created a UDM that tore it up in a development sample - only to see it disappoint going forward in time afterwards. Sometimes they bounce back under those circumstances. (But more often not.) Conversely, about half of my ideas DO perform reasonably well after their tag dates. From there it becomes a question of workload on race day. (Can I add one more UDM to live play without the added plays it produces bogging me down so much that it negatively impacts efficiency and decision making during live play?) Keep in mind that the game is something that is forever slowly evolving. Public betting trends change over time. All UDMs have shelf lives (duration unknown beforehand.) If you plot individual UDM roi on a graph over time you can see see the trend line. If you accept that you are not the only one looking for profitable trends it makes sense that the public WILL eventually catch on to factors that are overlooked in the odds. If your model is profitable in its development sample - but the roi trend line for the factors that model is based on are downward sloping... then you should probably ask yourself if the reason for the downward slope is because the public is already catching on... If that's the case, chances are the model was already near the end of its shelf life at the time you discovered the trend. Conversely, if a model is profitable in its development sample - but the public is largely unaware of the factors driving profitability - chances are the roi trend line is flat - or better yet upward sloping... And chances are the end of that model's shelf life is some point well off in the future. -jp . ~Edited by: jeff on: 8/9/2013 at: 8:15:06 PM~
| |
| PhilCVG 8/10/2013 9:30:26 AM | Got it Jeff. All is working perfectly. I wish I had made the change sooner.
|
| Reply |
|
Copyright © 2018 JCapper Software back to the JCapper Message Board www.JCapper.com |