Wanted: Microsoft Excel Formula - Quirin Speed Points

[Dan H]

Looking for formula(e) to help me automate the calculations of Running Styles and Quirin Speed Points.

Assumptions:

1. Formulae for paper and pencil methods are in the public domain.
2. Data is already imported into Excel from ITS – (no BRIS CSV available).
3. Microsoft Excel 2007 – beginner to intermediate skill level.

No luck with PA search for this info,

Thanks, Dan H

[raybo]

Looking for formula(e) to help me automate the calculations of Running Styles and Quirin Speed Points.

Assumptions:

1. Formulae for paper and pencil methods are in the public domain.
2. Data is already imported into Excel from ITS – (no BRIS CSV available).
3. Microsoft Excel 2007 – beginner to intermediate skill level.

No luck with PA search for this info,

Thanks, Dan H

Do you have the paper and pencil methods?
Do you have the ITS data structure?
How familiar are you with Excel and writing formulas?

I don't use Quirin speed points but if you know how you want them calculated/manipulated there are many here who can write the formulas/macros for you.

To calculate running styles you need to decide how you want to do that before you ask for Excel formulas.

[CincyHorseplayer]

I don't think it's something that you really want automated.

There are enough things to be noticed looking through a horses running lines that would get glossed over by an objective selection method.Sometimes a single running line clues you into a horse's capabilities.Or looking through 5-10 lines you get to know the running style personality of a horse when you were simply trying to place him at the 1st call.

Just my 2 cents.I have always adhered to Davidowitz's advice to scan the lines bottom to top,top to bottom and be able to absorb that history at a glance.Most times it's all you need to know.

[BIG49010]

If someone can come up with formula, it would be interesting to check it against the Bris Files which put the data in.

[Dan H]

Do you have the paper and pencil methods?
Do you have the ITS data structure?
How familiar are you with Excel and writing formulas?
.
QSP rules can be found at:

http://www.free-horseracing-info.com/universalbias.html (Calibration Handicapping site)

The ITS Data Structure can be found at:

http://www.itsdata.com/tech_info/ptd_exported_cdf_format.html (Post Time Solutions site)

I can write IF, SUM, and other basic formulae. No Visual Basic.

Here's the formula I created to identify a Z-Pattern in a running line:

=IF(AND(AK11>=AL11,AL11<=AM11,AM11>AN11),"ZPAT","")

Thanks for the responses so far.

Dan H

[LottaKash]

Just my 2 cents.I have always adhered to Davidowitz's advice to scan the lines bottom to top,top to bottom and be able to absorb that history at a glance.Most times it's all you need to know.

That would be me......There are many additive and bonus things that have signifcant value, but Bottom/Topping each line is the heart and soul of it for me...... Superior Pace numbers combined with Good Form tells the relevant story.......Making mountains out of mole hills is a great waster of time, in my opinion....Sure you can get those extra few wins, but overall, at what cost ?..Simplicity works best for me, as well.....

best,

[CincyHorseplayer]

That would be me......There are many additive and bonus things that have signifcant value, but Bottom/Topping each line is the heart and soul of it for me...... Superior Pace numbers combined with Good Form tells the relevant story.......Making mountains out of mole hills is a great waster of time, in my opinion....Sure you can get those extra few wins, but overall, at what cost ?..Simplicity works best for me, as well.....

best,

Maybe we think alike because we have the same birthday?

I noticed your name above mine on Jan 9:cool:

Must say your approach is eerily similar to how I approach the game too.

[LottaKash]

Maybe we think alike because we have the same birthday?

I noticed your name above mine on Jan 9:cool:

Must say your approach is eerily similar to how I approach the game too.

It must be a Capricorn thing........You know dem goats.....Mark Martin also shares that great day as well.....

But, getting back to the original thing, I believe that when you stay focused and are adept at deciphering the running lines and the companion pace's of those races, well then, most everthing else is less, and yet there are those who accomplish the same results but with a mountain of work and scads of numbers to get the same........yeesh, I am too old for the all that drudgery.....

best,

[raybo]

QSP rules can be found at:

http://www.free-horseracing-info.com/universalbias.html (Calibration Handicapping site)

The ITS Data Structure can be found at:

http://www.itsdata.com/tech_info/ptd_exported_cdf_format.html (Post Time Solutions site)

I can write IF, SUM, and other basic formulae. No Visual Basic.

Here's the formula I created to identify a Z-Pattern in a running line:

=IF(AND(AK11>=AL11,AL11<=AM11,AM11>AN11),"ZPAT","")

Thanks for the responses so far.

Dan H

I'll see what I can do this evening.

Are the different files in separate locations in Excel? Are the fields already in separate cells in Excel or are they still in comma-delimited format? (I'm assuming, at this point, that you simply "opened" the files in Excel 2007 without using a parsing program to select and order the comma-delimited data)

It might help if you send me a sample file so I can open it in Excel 2007 and then go from there.

[raybo]

I started working out the Quirin speed numbers using a Bris data file and it is quite complicated, but I'll figure it out before long.

You'll have to modify it to work with your ITS data files, but that shouldn't be a problem for you. At first I had thought of putting it all in one huge formula, since you have Excel 2007 and that version allows such a huge formula, but doing it that way was way too complicated so I decided to use several columns to separate some of the "IF" statements to allow a more efficient way of getting all the variables taken care of.

[Dave Schwartz]

Okay... I have been playing with this.

I have the sprints thus far.

Please note that this is just the calculation AFTER you have what you need from the pacelines loaded into a 10-race grid.

http://www.horsestreet.com/BBSImages/QuirinEsPts.xlsx

[Dave Schwartz]

Oops... penalty needs work.

[Dave Schwartz]

Okay. I think I have it now. Sprints look good. Give it a test.

[Dan H]

Dave,

Thanks for the help, the link you offer saves to my computer as a zip file that extracts to xml folders and files. If that's by design, then you're miles ahead of me on this.

Dan H

[Dave Schwartz]

Dan,

Alll I did was save the file. (Excel 2007) I did not zip it.

Were you able to open it? If not, let me know and I will save it in an older version of Excel.

Dave


PS: Let me just put it up as a 2003 file:
http://www.horsestreet.com/BBSImages/QuirinEsPts.xls

I fthis doesn't work for you, I WILL zip it and put it up again.

[Dan H]

Thanks again Dave, the 2003 file opened successfully. It will take me a little while to digest it.

Dan H

[raybo]

Okay... I have been playing with this.

I have the sprints thus far.

Please note that this is just the calculation AFTER you have what you need from the pacelines loaded into a 10-race grid.

http://www.horsestreet.com/BBSImages/QuirinEsPts.xlsx

LOL,

You tackled the problem like I was proceeding. Rather than trying to put all the options in one very long formula, possible in Excel 2007, I, too, opted to create several columns of "tests". I got as far as looking at the bonus point for in the lead or within a neck in all 3 rated races. You beat me to the punch.

May I ask you a question about your formula syntax? Instead of using traditional "IF" statements you seem to accomplish the same thing by enclosing the criteria in parenthesis and then multiplying by 1. Can you explain how this works and is this syntax something outside the realm of Excel help files?

Thanks!!

[Dave Schwartz]

Sure.

Pretty simple really, but executes much faster than an if-then statement because it is all math.

I have a cell called "Sprint?" that is populated with a statement like this:

(B6<8)*1

Since cell B6 contains the distance of the race, that is really the same as saying:
(Dist<8)*1

(B6<8) is a "truth" statement, just like an if-then. If you put just that into a cell, the cell will read "true" or "false." Actually the "*1" is probably unneccessary - it just reads better for me.

So, if the statement is true, it produces a "1" otherwise a "0."

Then, later on, instead of saying "If the race is a sprint" or, more accurately, (B6<8) I simply mulitply a value times that cell.


Hope this helps. I doubt that I will spend any more time on this.

I think the coolest part of the table is the part where you decide - based upon the ratable races - how many points the horse gets, and whether or not he qualifies for a penalty or a bonus.

BTW, be aware that I did not bother with only going back 5 races maximimum. Personally, I will go back as many as necessary to get the 3 ratable races.


Dave

Dave

[raybo]

Sure.

Pretty simple really, but executes much faster than an if-then statement because it is all math.

I have a cell called "Sprint?" that is populated with a statement like this:

(B6<8)*1

Since cell B6 contains the distance of the race, that is really the same as saying:
(Dist<8)*1

(B6<8) is a "truth" statement, just like an if-then. If you put just that into a cell, the cell will read "true" or "false." Actually the "*1" is probably unneccessary - it just reads better for me.

So, if the statement is true, it produces a "1" otherwise a "0."

Then, later on, instead of saying "If the race is a sprint" or, more accurately, (B6<8) I simply mulitply a value times that cell.


Hope this helps. I doubt that I will spend any more time on this.

I think the coolest part of the table is the part where you decide - based upon the ratable races - how many points the horse gets, and whether or not he qualifies for a penalty or a bonus.

BTW, be aware that I did not bother with only going back 5 races maximimum. Personally, I will go back as many as necessary to get the 3 ratable races.


Dave

Dave

Thanks Dave,

Your explanation is very understandable. I thought that since the "IF" statement produces a discrete output, then the output would be either a 1 or a 0. The "*1" was the tipoff. However, like you, I wondered if the "*1" was necessary in that particular test.

I guess what is confusing is how Excel knows that the "()" designates an "IF" statement. I assume it's part of the Visual Basic programming language. Is that correct?

I'm very glad to know this, it will make creating long nested "IF" statements much easier.

I think I lean towards your thinking concerning going back past the last 5 races for the 3 ratable races. In my experience, sometimes one must go back further than the last 2, 3, 4, or 5 races to get at a horse's true potential, considering current form.

I also found the part where you find the 3 ratable races, prior to adding a bonus point or deducting a point, as the most challenging, at least where my nested "IF" statements are concerned.

[Dan H]

Thanks to Raybo and Dave. You've pushed me in the right direction. Until I understand the concept behind Dave's solution, I'm going to build nested IF statements (a'la Raybo's solution). Build from there. Again, thanks.

Dan H

[raybo]

Thanks to Raybo and Dave. You've pushed me in the right direction. Until I understand the concept behind Dave's solution, I'm going to build nested IF statements (a'la Raybo's solution). Build from there. Again, thanks.

Dan H

Yeah, I hope he posts again and explains the theory behind his syntax a little more.

[Tom]

That is a handy little nugget dropped on us. :ThmbUp:

[raybo]

That is a handy little nugget dropped on us. :ThmbUp:

For sure! But I would like to know how the syntax works and why.:confused:

[Dave Schwartz]

Raybo,

I do not understand how I can help you better than I did.

You have the code. It does what it does.

If I said, "(X=14)" that is a truth statement that results in a 0 or 1. If X is anything except exactly 14 it is false, i.e. a 0.

It works just like "If X=14 then POST TRUE else POST FALSE."


Dave

[Dave Schwartz]

Here is a link to a great website about Excel:

http://www.mrexcel.com/

[raybo]

Here is a link to a great website about Excel:

http://www.mrexcel.com/

Thanks Dave!

I found that the answer to my question concerning your syntax lies in Boolean Logic.

In Boolean Logic, Excel treats "*" as meaning "AND", "+" means "OR". A "TRUE" result to a formula equals a "1" and a "False" result to a formula equals a "0".

In my days of working with old punched tape programmed computers like the ones I worked on in the Air Force in the early '70s, we called these "discrete" inputs and outputs (yes or no, true or false, high or low, on or off, etc.) all these outputs were either 1 or 0 which is the way these primitive computers operated. Everything was calculated using registers containing bytes (8 bit, 16 bit, etc., and you had Binary, decimal, hexadecimal, octal, etc. coding) of 1s and 0s (the 1s and 0s were actual outputs of physical, digital "gates" on the circuit boards, you had "And" gates, "OR" gates, "NAND" gates, "NOR" gates, etc.. Two inputs, 1 or 0, present at a gate resulted in one output, a 1 or 0 (High or Low, on or off). These outputs were combined and stored in registers that represented actual values, like airspeed, altitude, distance to target, trajectory, etc., etc. for weapons delivery systems that were handled by the airborne computer in the airplane to calculate the release point of a bomb, for example, or other navigational uses. The "black box" in the aircraft recorded all the vital states of the aircraft and the weapons delivery computer at weapons delivery time so that we, as technicians, could download them on the ground and figure out why our 500lb bomb missed the target by 200 ft.

You could represent any number with 1s and 0s (in Binary code, for example, the number 8 can be represented with a byte containing the Binary code: 1000
5 = 0101
3 = 0011
etc..

The "IF", "AND", and "OR", in traditional Excel statements, are not needed when Boolean Logic is applied. Thus the formula, =IF(And(A1>10,OR(B1=5,C1<3)),1,0) can be represented by: =(A1>10)*((B1=5)+(C1<3)),1,0)

Hope this helps others who were confused by your use of Boolean Logic in your formulas.

[Dave Schwartz]

Glad you finally got it.