Betfair fav and matching selections

[slochin]

Hello,
Hoping for some assistance please.
For one of my strategies this is what I do manually.
About three mins before the off I check to see which is the Betfair Favourite. I then check this against my top two selections for that race.
If the Favourite is one of my two selections then I place a Dutch bet on my two selections.
Now I am trying to work through if this could be automated using Excel and BA.
If I use the sort function in BA does this automatically mean that the Betfair fav will always be on top and in A5?
If this is so I have placed an= to A5 in my second sheet in N1 .giving me the name of the favourite.
Now I would like this cell to be used to check against the horses in my second sheet (I have all the horses for the day's races listed with a blank row separating each group of two horses) and if there is a match between the fav and one of my top two selections then somehow I would like to have these two triggered to place a bet by using the back command. If the fav is not among my two selections then I would need a command to have no bet placed. The difficulty here I see is that if the fav is among my two selections it could be either first listed or second listed and I would need somehow to be able to have both listed a bets.
The final issue is the staking.
I usually Dutch Bet these using a set outlay Gary pointed me in the direction of tutorial relating to the Dutch betting facility in BA. Not sure if this could be adapted or used in automating the staking once my selections have been made. I have developed a simple Dutch betting prog in Excel for two horses as I will only be betting on two horses per race.
Once again the Back trigger would need to be able to see what the stake would be for the horses.
Hoping someone can assist or suggest an improved way of tackling this project.

[Captain Sensible]

The sort function only works on the grid display it won't change the order the runners are on excel. You could always use INDEX to get you the runners nae with the lowest odds something like this would be a starter to display the lowest odds runner

Code: Select all

=INDEX(A5:A55,MATCH(MIN(F5:F55),F5:F55,0))

[Captain Sensible]

VLOOKUP should sort out all your matching but to be honest with all the stipulations you may find it a lot easier and have more control by learning a bit of VBA

[slochin]

Hello,
I am nearing closer to completion of automating my strategy.
I have managed to develop with a combination of Excel formulas and a VBA module to get to the stage where I have my two selections listed with the stake worked out according to my Dutchbet formula in the spreadsheet.(I am assuming I am unable to use the Dutchbook on BA with my calculations)
Now I am not sure how to get the 2 stakes into the stake column.
I have one horse in AB6 with the stake worked out in AF2,and the other horse in AC6 with stake in AF3.
Also as I am loading the race 3 Min before the race starts and that is the time I wish to place my bets I am not sure what should happen with the odds column.
I wish to place the bets on my two horses so want to make sure my bets are matched.
The only other issue relates to my VBA module. It works perfectly for the first race. The excel formulas continue to work for all the next races,but the module does not.
Now still going through the learning process of Excel/VBA I would appreciate some help as to how to have my module automated so that it runs every time a new race is automatically loaded.

[Captain Sensible]

Without people being able to see , or even know what your VBA code is or does there's no real way to guess why it's failing after the first race.

I assume your sheet already uses VLOOKUP to select the runners so you'd just use that also to get your stake in the correponding columns just have it reference AB6:AB7 and AC6:AC7.

If you've already code in place for your trigger timing just use that to populate the stakes cell i.e. when the trigger cell Q5 is not blank populate the cell with your stake

=IF(Q5<>"","Your VLOOKUP STAKE MATCHING ROUTINE","don’t bet")

............. so want to make sure my bets are matched.

Only real way of being sure your bets are matched is placing the at odds of 1.01 but that means you may well end up with crap odds if the market isn't very liquid especially 3 minutes from the off. Is there any method in you placing bets 3 minutes from the off? i.e. are you more likely to get better odds on your selections at that time? If not I'd reconsider and place bets alot closer to the off when the market is a lot stronger and more liquid. As long as your bets are over the minimum Betfair SP limits you may want to send the bets as BSP on close bets so any unmatched part goes into the BSP pools and at least you'll be matched.

[slochin]

Hello,
I have been able to have my Dutchbet calculations worked out in a separate area and then the stakes for the two horses placed in the stake Col S matching the horses that I have selected.
All I did with the odds was to make Col O5 =F5 and take it down.
I have placed the criteria re inplay and suspended in Col AA1.
=IF(AND(E2="Not In Play”,F2<>”Suspended”,ISTEXT(D2)),
”Yes”,”No”).
Now I would appreciate if someone could advise how the trigger could be set-up to back the two hoses that have stake amounts alongside their name. The other horses will not have any stake amounts alongside them
This is the criteria I wish to use to trigger the back bet added to the inplay above.
=IF(AND$AA$1="Yes"),.....?........"BACK","")

[Captain Sensible]

If the only horses with figures in the stake column S are the ones you want to bet on just include the criteria IF Column S is not equal to nothing (i.e. not empty) then bet something like below and copy it down the column

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

[slochin]

@ Captain Sensible,
First of all my wholehearted thanks for the continued assistance you have given me for my project.
Well I took it out for a test run today using small stakes and it was a real delight to see all the criteria being met and bets placed.
It has been fully automatic at this stage except for the one issue I mentioned earlier and that is a VBA code that I received works perfectly when I click on the run arrow but my attempts to have it run automatically on a change of races has not worked.
From my reading I thought I could have it run automatically on a change of cell in my worksheet,so I chose A1 which is always changing with each race as it is loaded.
This is what I thought would work.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call MG08Dec04
End If
End Sub
however nothing happens.
The only other issue that might pop up at the moment is in relation to liquidity. The odds I have in Col R are the same as the BF odds in Col F. As I was using small stakes there were no problems getting matched. I wonder if there is not enough liquidity and only part of my stake is matched is there anyway to have Gruss place the balance at BSP.

[Captain Sensible]

Most of us just use the following code to kick off any VBA after each refresh, The Target.Columns.Count <> 16 just exits the routine if we haven't received a price refresh data and also turns off events and calculations whilst running any code to make it more efficient and stop any unneccessary calcs etc.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Application.Calculation = xlCalculationManual



Call MG08Dec04    ' your code goes here








Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

If it still doesn't run there's most likely a problem with your Sub code MG08Dec04 so you need to either show or say what it's doing.

To get matched at BSP you either use "BACK-SP" and any unmatched part (as long as it meets the minimum BSP requirements) will go into the BSP and get matched at whatever odds the BSP returns. Alternatively you can use "BACKSP" and the whole bet goes into the BSP pool but will only get matched if it meets the minimum odds you enter in the odds column R.

[slochin]

Hello,
Well I must be doing something basically wrong as I cannot make it start the VBA code automatically when the sheet changes races. As I said previously the code works when I press the run button each time.
I tried various forms of the code in the Private sub and call subcode etc. to no avail.
This is the code.
Sub MG08Dec04()
Dim Rng As Range, dn As Range, R As Range
Set Rng = Sheets("selection").Range("A:A").SpecialCells(xlCellTypeConstants)
For Each dn In Rng.Areas
For Each R In dn
With Sheets("gruss")
If R.Value = .Range("AA6").Value Then
.Range("AB6").Resize(, 2).Value = ""
.Range("AB6").Resize(, 2).Value = Application.Transpose(dn.Value)
End If
End With
Next R
Next dn
End Sub
This checks the fav in my gruss sheet with my selections in selection sheet and if there brings in the two selections required. As I say it works perfectly when I press run.
Two other queries relate to hiding columns and setting time for the races to load.
As I only need the Back Odds 1 Column I tried using the display columns in the Grid Interface Preferences to uncheck the columns I did not want.This works with the grid but the Excel sheet remains with all columns.
The auto select markets -specific time before off. I seem to have to tick and set this each time I start Gruss. Is this normal?

[alrodopial]

I read elsewhere:
"The Worksheet_Change event fires when content in a cell changes, while the Worksheet_SelectionChange event fires whenever a new cell is selected."

[Captain Sensible]

If the Sub routine works fine it's worth checking your worksheet change is firing properly and in the correct place, just have it do something simple like a refresh counter like below.

The following code needs to be placed in the worksheet not a module, I'm guessing you have sheet1 named as gruss. so we'll stick it under that worksheet. This should just simply count the refreshes in cell Z1 and show if the worksheet event is firing correctly

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub 'If columns changed <> 16 then exit sub
Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Application.Calculation = xlCalculationManual

Worksheets("gruss").Range("Z1").Value = Worksheets("gruss").Range("Z1").Value + 1


Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub


[Captain Sensible]

As I only need the Back Odds 1 Column I tried using the display columns in the Grid Interface Preferences to uncheck the columns I did not want.This works with the grid but the Excel sheet remains with all columns.
The auto select markets -specific time before off. I seem to have to tick and set this each time I start Gruss. Is this normal?

To hide columns on sheets I just click the column and drag the column size to 0, as soon as you've saved the sheet it's remembered the layout next time it's opened, no need to use vba to set it each time.

You have to set the auto select each time if you connect your sheet to excel using the Log Current Prices option otherwise every sheet you logged would have the same auto refresh.

Under Log Current Prices you have the option Log Multiple Sheets Quick Link which gives you the option to add multiple sheets all with their own auto select settings etc Most people use the Quick link once they've got the sheet working as they want as it's a lot quicker to set up and running.

[slochin]

Hello,
Thanks again Captain Sensible.
The refresh counter certainly clicks over ok. In the meantime I have received Excel formulas which seem to work well with my trialling,so I am a bit undecided which way to go at the moment.

[Captain Sensible]

Just go with the one you're most confident with whilst you're learning you can always switch things over when you get more confident or can't do things with formulas.

I started off just using formulas as that's all I could do with excel, still have some old bots running purely on excell just because they make money and can't see any point porting them to VBA. Eventually I switched over to writing in VBA just because formulas couldn't do what I wanted, wasn't as hard as I thought and plenty of people on the forum have helped with the snippets of code. Personally I wish I'd just gone down the VBA route to start as it would have saved time later on