Automate selection service,odd and stake

[slochin]

Hello,
I have been reading the Best Assistant manual and watching some related videos relating to Excel.
It would appear that my situation could be automated.I have a couple of queries to point me in the right direction please.
I receive from a selection service price ratings for all horses,with suggested stake as in the format below.Now what I would do manually is check the Betfair prices shortly before the race and if the prices of Betfair are higher than what the service lists then I would back that horse or horses with the stake from the outlay column.
For example :

In this race Justpitboss was rated a $1.51 chance and Betfair was paying $1.70 so that is regarded as an overlay and the outlay or stake on that horse was $331.There was only one other horse that was over the odds and that was Jodan rated at $4 and Betfair was showing $4.8 so an outlay of $125 was bet.
Now from what I have read so far I could organise triggers in Excel to automate this process. Something like if the Betfair Column is > the rated column then place bet with amount from outlay column else do nothing.This is to be done for each horse.
Here I am not sure ,will I need to place the information I receive from the service alongside the Bet Assistant info in Excel and then construct the triggers ,or will I need to place it in a separate worksheet.
I have been advised that it is necessary to make sure that the names of the horses correspond identically with the names form Betfair.
The only columns I really need are location,race number ,horse ,rated and outlay.However if Betfair can recognise on horse alone then I would not need the location and race number column.
From what I have seen so far bet Assistant loads the fields and then deals with each individual race as it comes along.What I am not sure about is how would the Bet Assistant be able to check each individual horse's Betfair price against my Rated price.
Hoping I can gain some further insight into this process.
Thankyou.
Gil

[Captain Sensible]

MIght be worth your while checking out the example sheets here viewforum.php?f=8

There's an example that shows how to use vlookup to match the horse to race viewtopic.php?f=8&t=3557

No reason why BA and excel can't do what you're looking for as it's only a case of matching up the runner names then applying a condition on the odds. With the names you can scrape out things like apostrophes,spaces etc to have a btter chance of matching and the races don't have to load just before the race you can continually loop thru all races and delete the runner/race or flag the ones you've already bet on to avoid duplicate bets.

I think the same question has been asked before where someone wanted to automate their email selections, try a search as it may have been solved already. At the end of the day it really comes down to how proficient you are with excel if you want to do it yourself or if you want someone to do it for you. There a excel developer section on the forum and the member Osknows has always been highly regarded on the forum if you need someone to code it for you.

[slochin]

Hello Captain Sensible,
Thankyou for your prompt response.I would like to take the time to learn to do this myself if possible.I did a previous search which did not find a lot of info and now searched for the post re email selections to no avail.I then started the long trawl through the posts grabbing any that I though might relate to my situation and I had to go back to Thu Feb 16, 2006 when a certain Captain Sensible assisted another person with a query close to mine.
Congratulations on assisting members for all these years.
Basically the suggestion was to put in Sheet2 Horses name Col A,odds Col B and Stake in Col C.
Then in Sheet 1 In the odds ColR and stake ColS paste this taking it down to 50odd rows :
=IF(ISNA(VLOOKUP(A5,Sheet2!$A$1:$C$100,2,FALSE)),"",VLOOKUP(A5,Sheet2!$A$1:$C$100,2,FALSE) )
This checking to see if there is a match of horses,and if it finds a match it will stick my odds in the Col R and my stake in Col S.
(In the earlier copy of BA it would appear odds were in Col O and Stake in Col P).
Now the only difference in this post and mine is the trigger.I want to place a Back bet with my stake that would be in Sheet2 approx one minute before the start of the race if the Betfair price is higher than the odds quoted in my sheet.
Do you think something like this would work :
=IF(F5>R5,"BACK","")
I am not sure where to place the time before race.There were a couple of posts saying make sure an hour condition was placed.
You also provided a macro for the worksheet to clear the cell references when it switches between markets on auto select.,which was updated by Jokerjoe due to a change in the program.
Thanks again for your assistance.
Post I found
viewtopic.php?f=5&t=173&start=15

[Captain Sensible]

The program's changed alot since 2006, I don't think you need to put in the VBA to clear bet references as there are options to set those now when you link the excel sheet to betting assistant. Just select the options "Clear bet refs on auto select market" and "clear bet refs on manual select market".

For the timing you have the option with betting assistant to have the program automatically select the market 1 minute before the off but it's always wise to include your own criteria in the trigger if that 1 minute is important. The time to the off goes into cell D2 by default so it's easy enough to include.

What I'd do, if your emails are in a regular format each time, would simply cut and paste it into excel sheet2 and from there start to make your spreadsheet from scratch so it'll be tailored to what you need rather than amending an existing sheet. The formulas can still be used but this way it'll cut down your time spent each day setting it up if the emails are the same format every time.

[slochin]

Hello Captain Sensible,
Thanks again. I am getting close to giving this a try. Would you mind glancing over the procedure I have come up with to see if it sounds ok please.
From the info I receive I will be able to place the required info into sheet two. I will not need to put all the info provided only the
Horses name Col A,odds Col B and Stake in Col C .
I suppose I could beforehand copy and paste the Betfair list of horses and my list in another sheet and have the comparison done to make sure names are matching for the Vlookup.
Then back in sheet one paste the Vlookup formulas as above in the second post. As all horses are listed it should populate the complete list of horses for the race with my odds and stake from sheet two.
Now in the trigger column in sheet one I will place the formula
=IF(F5>R5,"BACK","")
and copy this down for about 50 rows.
This will check the current Betfair price in F5 and if it is greater than my proposed odds in R5 then a back will be placed using my stake from the odds column,if not nothing will happen.
As I have the BA to automatically load the races two minutes before the off I presume that this trigger will continue to work through the meeting. Is that correct?
Best wishes,
Gil

[alrodopial]

It will go through the meeting but you should also place some ifs in your formulas to avoid placing bets inrunning
(I maybe wrong with this)

[Captain Sensible]

Have you considered using the Betfair SP and just setting limits on the odds you'll accept. It's hard to see how placing the bets 1 minute before the off is going to acheive you better odds to be honest and using a simple "BACK" trigger will always bring the possiblity of unmatched bets whereas the BSP should give a full match at your requested odds or above. Obviously both have their pro's and con's when using simple strategies and in weak markets large stakes aren't the wisest move for the BSP pool.

The formulas aren't the hardest thing to code but I think you really need to take time looking at all the possible problems that can occur when botting as a poorly coded bot will soon drain your account. Think about all the criteria you want to cover in your trigger before starting, time, odds etc what to do if bets aren't matched. With most systems sod's law means any unmatched bets will be winners so that may be another reason to consider using the BSP if the markets you play can handle them.

Just copy and paste an email into sheet 2 and say which columns the name, odds and stake go in from there it's easier to advise you on any formulas.

[slochin]

Hello,
@ alrodopial ,
Thank you for your contribution. I thought leaving the box that says keep in play unticked as one of the betting options would prevent bets being placed in running. Is this not correct?

@Captain Sensible

I have not used BSP much at all. My aim is to only place bets on those horses where the Betfair price is higher than that suggested by the selection service.
I am not sure how that would work with BSP.
What I am doing at the moment is to place the horses in Sheet 2 in Column A,then the required odds in Column B,and then the stake in Column C.
I thought then using the Vlookup commands listed above in sheet1,which is the BA sheet, in the odds (Col R) and Stake(Col S) this will then transfer my odds and stake from sheet 2 into the BA sheet.(As long as there is a match between Betfair and my list of horses,(correct format according to Betfair's names required).
Now that I have my odds required and stake listed in the BA sheet 1 I set my trigger to place bets on each runner where I can obtain better Betfair odds (Col F) than my selectors suggested odds (Col R).
Then place my stake on these from Col S.
I thought to accomplish this I put in the trigger Col Q the formula:
=IF(F5>R5,"BACK","")
hopefully this says if the Betfair price (Col F) is greater than my selectors price (Col R) then place the Back bet using my stake from Col S ,else do nothing.
Gil

[Captain Sensible]

The Betfair starting price lets you stipulate the lowest odds you'll accept so it's basically what you're looking for and you can place all bets at the start of the day rather than waiting. Only problem with BSP is that it can move markets with little liquidity.

Looking at your sheet youre playing AUS races and I don't think it'd be the wisest thing to put the odds off your sheet into the odds column R. You'll probably end up taking crap odds on most of your bets by requesting the lowest odds you'll accept. Might be better off using current odds on offer and maybe use BSP options to ensure a full match. If you're sticking $331 on AUS markets I can easily see you taking down the queue to the lowest price.

No reason why =IF(F5>R5,"BACK","") wouldn't work but it will place bet's in running, the tick box you mention just dictates what happens to a bet after the market goes in play.

Alot of people will set aside a separate cell to check the market conditions are met before placing a bet, the advantage of having a separate 'flag' is it stops the main formula getting complicated and you can add to it easily rather than re-writing all trigger cells. So if we set aside cell AA1 as our flag we can add conditions about the time and market status then simple reference that using AND in our trigger.

D2 holds the time to the off, E2 has the market in play status and F2 has the overall market status.

So in AA1 we can put the following so it'll just say Yes if our market conditions of under 1 minute to the off, and the market is neither in play or suspended are met and will show as No if they aren't. I've no idea how clued up you are with excel so I'll assume you understand the formula, if you want it explained just ask.

Code: Select all

=IF(AND(E2="Not In Play",F2<>"Suspended",OR(D2<=TIMEVALUE("00:01:00"),ISTEXT(D2))),"Yes","No")

We can now include that as an extra condition to the trigger cell using AND like so

Code: Select all

=IF(AND(F5>R5,$AA$1="Yes"),"BACK","")

We use $AA$1 so the value doesnt change when you drag and copy the forumula down the column

Trouble with your code is where there are no odds F5 will always be more than an empty R5 so you need to clean that up just by stipulating it shouldn't be an empty cell. i.e. if nothings in there then obviously there's no VLOOKUP match.

Code: Select all

=IF(AND(R5<>"",F5>R5,$AA$1="Yes"),"BACK","")

[slochin]

Hello Captain Sensible,
Sorry for late response. For some reason I am not receiving email notifications re posts.
I really appreciate the time and effort you have put into the program to make sure the set up runs smoothly.
As I said in the first post not overly experienced but am learning all the time. So the If will prevent bets being laid if the time is further away than the 1 minute, and when the race goes into play it will show “No” .
I assume when the old race is finished and the next race is loaded from the quick pick list this will continue?
Once again appreciate your support. I am now nearly to the stage of giving it a trial with limited staking to see how it all pans out.

[Captain Sensible]

Yes as long as you've selected the tick boxes Clear bet refs on auto and manual select market when you log the sheet to excel it'll be ready to fire again for when the next race is selected.

Just test with small stakes as that usually when an errors in either your coding or strategy will show up.

[Captain Sensible]

I tweaked the code slightly

Code: Select all

Function FindWord(Source As String, Position As Integer)
Dim arr() As String

On Error GoTo ErrHandler:



arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
FindWord = CInt(Replace(FindWord, "m", ""))
Exit Function
ErrHandler:
FindWord = 0
End Function


[slochin]

Hello @Captain Sensible.
I have a couple of minor queries please.
Early in the thread you provided a formula to enable betting within one minute.I can understand it bar the ISTEXT relating to D2.
=IF(AND(E2="Not In Play",F2<>"Suspended",OR(D2<=TIMEVALUE("00:01:00"),ISTEXT(D2))),"Yes","No").
Would you mind explaining it in more detail please.
Secondly in relation to Q2 -6 for refreshing results.A staking system I am hoping to incorporate relies on obtaining results as rapidly as possible.
Now is there any way, and is it ok to have the Q2 -6 refresh automatically?

[Captain Sensible]

When the time goes past the off time it gets sent to BA as text rather than a negative time so using ISTEXT just catches when it's a negative number and less than 0.

Yes you can easily send a -6 to Q2 using VBA just decide whenever you want it to refresh, I've never been a fan of staking sytems as I can't see why the result of a previous race will affect the next race but that's by the by. I've never used the results sheet so no idea how often they refresh normally, if it refreshes on market change you may as well set your auto select to 1.1 minutes so the most up to date data is there in time for your betting criteria of 1 min before the off.

Otherwise just add some routine like this

Code: Select all

 If Range("D2").Value <= TimeValue("00:01:00") And Range("A1").Value <> Range("AT1").Value Then
 Range("AT1").Value = Range("A1").Value ' to stop the routine looping
 Range("Q2").Value = -6
 End If