Excel formula for playing in-running

[one cool dog]

If you run an excel sheet with API running to it, has anyone tried this sort of approach?

Can you get it to check the cells for a certain price, then back it.

This is my idea.

I want to back the first horse to go 1.7 in-running. Let's forget, for now, if it is profitable as I can refine.

What sort of formula am I looking at, is it the 'IF' arguement? What other arguements are involved?

Can someone show me the formula that would back the first horse to go 1.7 in-running and then not back any more in-running, even if another went 1.7 in-running?

I do not have Gruss installed yet, I am just thinking of ideas and learning excel as I go along. I tend to learn much more quickly if I see a formula and am able to break it down.

Any help is, as always, appreciated.

[GeorgeUK]

Hi OCD

There are 2 parts to your query here

you want BACK to appear as a trigger if a horse has odds of 1.7 or less
you don't want the trigger to activate if a bet has already been made.

I'm still not using gruss yet so not really sure of the layout on excel, but can give you a guide to this.

Lets say the betref column is I and the odds you are looking at are in E
=IF(Counta($I$2:$I$20)<1,IF(E2<=1.7,"BACK",""),"")
This would be the trigger formula for row 2

IF (count the no. of cells in I2 to I20) <1 ie betref column has no bets
then
IF odds for that row are <=1.7 make trigger "BACK", otherwise leave cell empty with ""
Otherwise
If there is a value in the betref (a bet has been made) then make the trigger cell "" (blank)

Is this what you were meaning?

[Captain Sensible]

Just need to stick a few IF and AND statements together. Most reliable way of checking the price would be to check the available to lay odds (using the back price will give a false reading when markets are reforming if someone is preparded to back at 1.7 you can assume that price has been matched) so the simple formula would be

=IF(H5<=1.7,"BACK","")

Obviously this would kick off before going in play so you need to add other criteria using the AND statement. You need to chck the market is In play and no other bets have been placed. E2 usually shows the market in lay or not so thats easy enough. To stop further bets being placed you can check the bet reference column to see if a bet has already been placed somethiong simple like COUNT(T5:T41)<1 should work.

Adding them all together

=IF(AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5<=1.7,H5<>""),"BACK","")

[Captain Sensible]

Beat me to it George, guess I'm just a slow typer

[one cool dog]

Just need to stick a few IF and AND statements together. Most reliable way of checking the price would be to check the available to lay odds (using the back price will give a false reading when markets are reforming if someone is preparded to back at 1.7 you can assume that price has been matched) so the simple formula would be

=IF(H5<=1.7,"BACK","")

Obviously this would kick off before going in play so you need to add other criteria using the AND statement. You need to chck the market is In play and no other bets have been placed. E2 usually shows the market in lay or not so thats easy enough. To stop further bets being placed you can check the bet reference column to see if a bet has already been placed somethiong simple like COUNT(T5:T41)<1 should work.

Adding them all together

=IF(AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5<=1.7,H5<>""),"BACK","")

Hi cheers for the responses as usual.

I would be lying if I said I totally got the above (I only picked this one as random, as have no preference) so wondered if I could indulge a bit more if I ask nicely

As we can see above, we have three sets of brackets.

I presume, and can be wildly wrong hence why I ask, the 3 brackets are as follows -

(AND,"BACK","")

(COUNT<1,$E$2="In Play",H5<=1.7,H5<>"")

($T$5:$T$50)

Please correct if wrong.

Which order, if they are correct, does it get read if that makes sense or, probably easier to understand, in which order does the brackets go?

I am almost sure that ($T$5:$T$50) means to look at the information between these absolute references (which is possibly the column (T) that the prices refresh in ?


You have been great so far, and I am a bit gimp, but extra help is appreciated. These long formulas are still a bit daunting for me at the mo.

Cheers

[Captain Sensible]

np, if you go back to your old maths training at school everything gets read in order of th brackets. If you copy the formula to excel then press the fx button next to the cell formula it'll bring up the function arguments in excel and may be easier for you to step through.

like maths your first bracket gets read which is the IF statement

=IF(

AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5<=1.7,H5<>"")

,"BACK","")

basically thats saying if AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5<=1.7,H5<>"") is TRUE then result is entering BACK in the cell if not just enters nothing in the cell ("")


The AND formula says all of those criteria must be met in order for the statement to be true

AND(

COUNT($T$5:$T$50)<1, -------- this bit just returns the count of any cells within that range that contain a number, if it's less than one i.e. no betrefences are present then its TRUE

$E$2="In Play", -------- this bit is just checking the markets in play
H5<=1.7, -------- this bit is checking the current to lay odds are either 1.7 or below
H5<>"" -------- this bit checks that there are odds available otherwise nothing would be assumed as below 1.7
)


It's worth using the fx (insert function) button in excel to amend or add formulas as the help menu through fx is very good and will step you through any formulas if needed

[one cool dog]

Thank you for your help.

I am trying really hard to get this and think I am making in-roads. Given the hard work you did in explaining, I did not want to lie and say I got it, when I was still struggling. Things are slowly falling into place and hope I am getting it.

AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5<=1.7,H5<>"")

I do not have BA yet so my guess is that the range T5 to T50 is the bet reference column? Does that mean that all bet references are much bigger than 1, and if that column is completely blank, eg less than 1, that criteria is met.

Also, does that mean everything between (Count ---- H5<>"") needs to be met to make the bet either "BACK" or "" (i.e the True and False statement)

If I was to write a normal If statement, I would do it like this

=IF(A1+A2=3,"TRUE","FALSE")

The only puzzle, assuming I am mostly correct so far, is where does AND come from. I tried to find it on Fx but could see nothing.

Id rather tell the truth and make sure I get it after you put some work in for me.

[GeorgeUK]

Cap, hope you don't mind me jumping in here.

OCD - you definately seem to be getting it, but it may be worthwhile looking at the conditions of each part.

In a blank cell, put =count(
then press the fx button to the left of the formula bar (don't forget that bracket)

Here you will see that the count formula is not looking at the values in the cells, but the number of cells that have a value.

If you put
0
2
4

the formula would count 3 in those cells as this is the number of cells that have a numerical value
I used counta as i wanted to count the number of cells that had any value - not just numerical

0
2
4
fish

would count 4. I used this because i didn't know if sometimes the betref wasn't a number.
Even though there is a 0 in one of the cells, it would be counted for your formula. It isn't a question of the value of the number, but whether there is one or not - and in how many cells

Another formula to try with the fx
=if(and(
(remember that bracket!!)

Here you should see what the and is looking for. It is expecting a list of conditions.
Each condition is seperated by a ,
so if you wanted (A1+A2=3) AND (B1+B2=3)
you would put
AND(A1+A2=3,B1+B2=3)

The only way to learn something is to ask the question, so don't worry. It's better to make sure than end up either confused or worse, misunderstanding and losing money.

[Captain Sensible]

Thanks George the more people contributing the better as the only way to learn is by asking questions. Most of my limited VB knowledge has come from you and others on the board so I'm more than aware it's worth posting even the stupidest questions so you're happy you understand the formlas are doing what you want them too before letting them lose with your hard earned cash.

Luckily this board still has the community feel with everyone willing to chip in without the sniping you get on the majority of forums out there.

AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5<=1.7,H5<>"")

I do not have BA yet so my guess is that the range T5 to T50 is the bet reference column? Does that mean that all bet references are much bigger than 1, and if that column is completely blank, eg less than 1, that criteria is met.

Also, does that mean everything between (Count ---- H5<>"") needs to be met to make the bet either "BACK" or "" (i.e the True and False statement)

I imagine it's quite hard to visualise everything without having BA installed as the columns probably mean nothing to you. I've stuck an example of the data BA exports here

http://www.mediafire.com/?dbjjukswtn9

so you can get an idea of what the columns show.


You're right that the range T5:T50 holds the betreferences. All bet references are bigger than 1 but as George said the count function is only counting cells the have an entry in them and not adding up the individual bet numbers. BA only exports the betrefernce if one has been placed via excel so all other entries would be blank even if you' d placed a bet via the web interface.

All the criteria within the and brackets needs to be met for the IF statement to be true and then BACK would appear in the cell. If any of the criteria was not met and the statement was false the IF formula would enter "" (i.e. nothing) in the cell.

So ignoring the AND for now the commas break up the formula

=IF(something=somethingelse,"BACK","")



IF(

something=somethingelse, This is our criteria that will either be true or false

If TRUE the "BACK" would be the result
If FALSE "" (i.e. empty cell) would be the result

)


With our formula we need something=somethingelse to be a bit more complicated as we want to covers all options so we use the AND statement

AND(COUNT($T$5:$T$50)<1,$E$2="In Play",H5<=1.7,H5<>"") just replaces something=somethingelse in the overall formula.

AND(
COUNT($T$5:$T$50)<1,
$E$2="In Play",
H5<=1.7,
H5<>""
)

All these conditions now need to be TRUE in order for the formula to show as TRUE for the IF statement to result in "BACK" if one fails then the AND statement returns FALSE and the IF statement shows ""

[one cool dog]

You guys are helping so much and I knew I would start to get it with good guidance and help.

I need to read things carefully, absorb and write some test ones. And by jove, I think I have got it. These seem to work -

=COUNTA(G1:G22)

I added 10 characters in those cells, 8 numbers and 2 letters, and it returned 10. Once changed to =count(g1:g22) it returned 8. So all looks good.

I also did this one.

=IF(K3<=1,"BUM","BALLS")

I type 1 and I get BUM and 7 returns Balls.


It is a small break through, but the more I plough on with these sorts of tips, the better it will be.

I will probably return shortly with more questions but many, many thanks so far.

[one cool dog]

I knew I would be back soon.

The stuff is genuinely sinking in and I thought I was getting clever, seems I may be a bit misguided.

Using the template of Excel handling Betfair as provided, I amended the formula.

=IF(AND(COUNT($T$5:$T$50)<1,$E$2="In Play",F5<=1.7,F5<>""),"BACK","")

I changed it to F5 because that is the back primary back column. Is that true?

Does it matter where I place the formula, as long as it does not go in any of the data fields. I.e, if a race has 20 runners, I could safe place the formula in A3O and get the right result?

Finally, that above formula, I believe, works for the cell F5 only. I want it to look for the 1.7 throughout the race. Now this is where I thought I was smart but clearly I am not.

I changed the bit highlighted in red to F5:F20<=1.7, F5:F20 <>""

Now I reasoned that it could cover all the runners, up to 15 in this case, and would look for 1.7 in that column. However, on clicking return I found the word Value in the cell.

I have just tried this before going to bed, so have not tried to find my error, so any one kind enough to look in before I have a crack again will be most appreciated. Can it work with a range of cells? Is it only possible for 1 cell per 1 formula? Do we need to introduce more criteria to the formula?

[GeorgeUK]

I changed it to F5 because that is the back primary back column. Is that true?

Yes - If only all your questions were this easy...

Does it matter where I place the formula, as long as it does not go in any of the data fields. I.e, if a race has 20 runners, I could safe place the formula in A3O and get the right result?

Yes this is right... BUT

The Formula is returning the value "" or BACK which is a trigger command, so i thought you would want this in column Q to display the trigger BACK when your criteria were met. ie Q5

If you want all the runners in your sheet to have the same selection criteria, then this formula should be copied into all the trigger cells. ie Q5:Q50

To copy a cell (and the formula in that cell), you will notice that the selected cell has a small black square in the bottom right corner. If you move your cursor over this, it will change to a black cross. While it is like this, you can click and drag down to copy that cell down to others.

The reason this will work without altering your range, but still looking at the odds for the appropriate row, is because of the absolute references you put in the formula.
If you put in a cell =sum($A$1:A2)
and copy this down, you will see that $A$1 remains, but the A2 has changed or increased by 1 to A3
Excel will alter the rows for you unless you specify exact cells to look at - which you do using the $

$A$1 means your formula is looking at only column A and only row 1 (only cell A1)
So no matter where the formula is copied to Excel knows you want to look at only that cell because of the $ you put in the formula
A1 is just a cell, so if you copied this formula and put it in a cell 4 rows down, it would add 4 to the number of rows (A5)

I won't be able to post for the next few days, so hopefully others can clear up any confusion i've left/caused

good luck
George

[one cool dog]

I changed it to F5 because that is the back primary back column. Is that true?

Yes - If only all your questions were this easy...

Does it matter where I place the formula, as long as it does not go in any of the data fields. I.e, if a race has 20 runners, I could safe place the formula in A3O and get the right result?

Yes this is right... BUT

The Formula is returning the value "" or BACK which is a trigger command, so i thought you would want this in column Q to display the trigger BACK when your criteria were met. ie Q5

If you want all the runners in your sheet to have the same selection criteria, then this formula should be copied into all the trigger cells. ie Q5:Q50


Good news. In this case, the column set up as trigger is where you would place the formula rather than any random one?

To copy a cell (and the formula in that cell), you will notice that the selected cell has a small black square in the bottom right corner. If you move your cursor over this, it will change to a black cross. While it is like this, you can click and drag down to copy that cell down to others.

I think I can get this. I often copy down formulas through cells.




I am not sure what the below is about completely, but I know the bit about dragging an absolute reference. I was just slightly confused about the reference to $A$1 as an example.



The reason this will work without altering your range, but still looking at the odds for the appropriate row, is because of the absolute references you put in the formula.
If you put in a cell =sum($A$1:A2)
and copy this down, you will see that $A$1 remains, but the A2 has changed or increased by 1 to A3
Excel will alter the rows for you unless you specify exact cells to look at - which you do using the $

$A$1 means your formula is looking at only column A and only row 1 (only cell A1)
So no matter where the formula is copied to Excel knows you want to look at only that cell because of the $ you put in the formula
A1 is just a cell, so if you copied this formula and put it in a cell 4 rows down, it would add 4 to the number of rows (A5)

I won't be able to post for the next few days, so hopefully others can clear up any confusion i've left/caused

good luck
George

[Captain Sensible]

think you missed out your question ocd

[Captain Sensible]

The formula

=IF(AND(COUNT($T$5:$T$50)<1,$E$2="In Play",F5<=1.7,F5<>""),"BACK","")

would need to be copied down all the trigger cells from Q5 to say Q50 as that would cover 45 runners more than enough. Thta's why relative references were used for $T$5:$T$50 & $E$2. The F5 would just covert to G5 ec when copied down.

I've stuck up an example at

http://www.filefactory.com/file/aeb714/

just amend the the lay odds to 1.7 and you should see the change and how ba exports data