***** excel triggered betting making a simple bot

[1/4 of a millionaire fund]

Scumbag was asking how to make a simple bot to lay bets a certain amount of ticks off the pace using *****. I said I'd do a simple tutorial to show how it can be done.

Remember this is a very basic program just to show some of the excel/***** features rather than a usable bot so don't go placing bets you dont want to. My knowledge of VBA and excel is quite limited but hopefully it should show people a little knowledge can go a long way. Also alot of the ways I approach ***** and excel may be different to others with regard to referencing cells etc so be aware this is not a master class just my way of approaching excel betting.

OK we now need to start logging the market to an excel sheet to see all the info available to us.

Start excel and create a new sheet and save it as tickbot A10

***** allows us to start logging the market info at any starting cell we want to , the default value is A1, I usually use A10 as that allows me to place any variables I may want to change (stakes etc) in the cells from A1 -A9 and ensures they're not overwritten on each refresh and on view for easy changes. The A10 in the name is just there as a reminder so I dont start logging to A1 and overwrite all my hard work.

We now need to get ***** to talk to excel and start logging all the market data we need, this couldn't be simpler and is just a case of load up any betting market in *****, clicking the excel tab at the top of ***** , select our excel sheet from the workbook name change the top left hand cell to A10 from the default A1 and press OK.



Ok we should now have our market into excel



We can now go back to ***** and click the excel tab again and untick log current prices as we've all the info we need to start and no need to carry on logging for now

[1/4 of a millionaire fund]

OK we've got the market in excel so lets add so VBA to calculate the tick side of calculations for us. The tick code hasn't been written by me but was provided by Gary on the ***** forum http://www.*****-software.co.uk/phpB...pic.php?t=2399

here's the code we'll copy into our excel sheet

Function getPrevOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select
If Math.Round(odds - oddsInc, 2) >= 1.01 Then
getPrevOdds = Math.Round(odds - oddsInc, 2)
Else
getPrevOdds = 1.01
End If
End Function

Function getNextOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getNextOdds = Math.Round(odds + oddsInc, 2)
Else
getNextOdds = 1000
End If
End Function

Function plusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getNextOdds(odds)
Next
plusTicks = odds
End Function

Function minusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getPrevOdds(odds)
Next
minusTicks = odds
End Function



To open up the VBA editor we just press ALT + F11, click insert and select Module. It should show module 1 in the side window, click on it to open up the window and paste in the above code.We can now close the VBA editor window for now.

OK so what can that code do ? Well it now allows us to take any odds we want and work out various tick options either adding/subtracting ticks or even seeing the amount of ticks between two prices. To check it works we'll just enter the following into cell R14 =minusTicks(F14,20)

This should give us a price 20 ticks below the current back price on the exchange. We could enter =minusTicks(O14,20) which would give us a price 20 ticks below the Last matched price (O14) but we'll err on the side of caution for now as someone may have boobed/self matched at 1000 and we don't want to be laying 20 ticks below that.

[1/4 of a millionaire fund]

OK so we've now got our odds, let start thinking of other things we possibly need like stakes , some sort of start/stop firing trigger.

We could just enter a figure into the stake column S of £2 etc but we'll treat everything in terms of a book payout for now to limit our exposure. We'll enter these in our cells at the top of the worksheet so we can amend whenever we need. Well keep it simple for now but these can easily be entered onto the sheet at a later date as drop down boxes etc.

OK let's stick in some stop start for the trigger functions we'll add later

in Cell A1 we'll type start in Cell B1 n for now.

in Cell B1 well type payout in cell B2 we'll put 20

As we're likely to use the figures in various cells all over the sheet we want them to be referenced so when we copy and paste we don't lose their relative cells. We can reference them by using the $ sign i.e. $B$1 when copied and pasted around the sheet will retain the cell reference $B$1 but I find it easier to use named references.

To reference the cell we just click on the cell B1 go to the little box in the top corner of the sheet showing the reference click on it and type in start and press enter. We've now made a reference to B1 called start which we can call up anywhere within the workbook.

We'll do the same with the B2 cell only this time call it payout this now allows us to refer to that cell anywhere on the sheet for our calculations and rather than changing lots of cells we just amend the payout figure in B2, simples.

[1/4 of a millionaire fund]

Ok so lets get the sheet to calculate the stake needed to give us a payout of £20.

In cell S14 we'll enter

=payout/R14

Now amend your payout in cell B2 to say 50 and magically our stake should change to the new figure, this might seem alot of hassle now but when we come to dragging cells to copy into all the other runners columns it'll save us a lot of hassle later on

OK our stake is looking messy because it's not a valid monetary amount so we'll clean that up to two decimal places, I think ***** does have built in functions to ensure valid stakes are sent but lets be on the safeside and use excels ROUNDDOWN function. We'll change the formula in S14 to


=ROUNDDOWN(payout/R14,2)

the 2 just means we're rounding down to 2 decimal places we can amend that to less if we wish

OK things looking a little tidier now and we can change our payout just by changing the value in B2 but surely not all of us want to be continually laying at 20 ticks below the current price ?

[1/4 of a millionaire fund]

Time to now amend the amount of ticks we want to lay at

In cell A3 we'll type Ticks in cell B3 we'll enter 20 as our ticks for now. Because we need access to that tick value we need to reference the same the same as we did for the payout, we'll reference B3 as tick

Lets now amend our odds cell R14 to be able to use the tick value, surely thats hard to do ? Well no because we are referencing cells we just need to amend the formula from

=minusTicks(F14,20)

to

=minusTicks(F14,tick)

You should now be able to amend the value in B3 and automatically it'll calculate the new price and new payout stake.

You can now copy those cells R14 and S14 down for all the runners and it'll work out odds and stakes for all.

[1/4 of a millionaire fund]

But surely the number of runners change race by race and it's sticking in 1.01 where theres no runner I might have a race with 30+ runners to cover?

OK that is a problem we at least want the sheet to look tidy and don't want alsorts of #value errors showing etc

As we're using F14 to set our prices we only want stakes and odds populated if that cell contains a value. Lets use the IF statement so if that cell is empty or less than 1.1 we won't fill those cells

OK so for the empty or less than 1.1 we need an OR statement

OR(F14="",F14<1.1)

lets incorporate that into an IF formula

Cell R14 now changes from

=minusTicks(F14,tick)

to


=IF(OR(F14="",F14<1.1),"",minusTicks(F14,tick))

and lets change S14 our stakes cell

from

=ROUNDDOWN(payout/F14,2)

to

=IF(OR(F14="",F14<1.1),"",ROUNDDOWN(payout/R14,2))


We can now copy those cells all the way down to S54 knowing we're covered for up to 50 runners and values will only show if theres a runner



Thats probably overkill for one day, tomorrow we can start looking at various triggers available

[Scumbag trader]

Mate this is exactly what i have been looking for for so long but couldn't find anything.

Cheers for this

[1/4 of a millionaire fund]

Mate this is exactly what i have been looking for for so long but couldn't find anything.

Cheers for this

No problem ST, I'm just trying to show some of the basics at the moment and trying to instil the fact that by using global settings for stakes etc we can affect all prices. By using global settings we can start using them later when deciding when to trigger a bet and the more conditions we add, the less likely we are to cock up sending duff bets

Currently the tick setting affects all runners but we can easily amend the formulas so each individual runner's price will react to the data for that runner only, i.e we can determine the number of ticks between back and lay prices and decide to bet if the spread is only over 3 ticks or leap frog the current price by a tick

If we look at the actual data excel receives we have a wealth of info regarding the market and individual runners

We have the 3 back/lay prices from there we can work out the current book %'s i.e we might not want to bet into poorly formed markets and only go in when the back % is over 99% .

From the prices we can RANK all runners to pick out the favourite second favourite etc we can omit runners over or under a certain price i.e dont bet over 5/1 only bet if the difference between the fav and second fav is 10 ticks or a certain %/prices

We have access to the market name and can determne if we only want to be in handicaps or non handicaps

We can see the total matched maybe only bet when a certain amount is matched, we can see individual runners matched amounts do we want to use that or even use some WOM type triggers for the current available amounts

We know how many winners the market has so can use that to avoid backing into the place markets if we click on them in error, we can see how many runners in the markets only bet 10+ runners etc

We can see if the market is inplay or even it it's going to go in play and decide if we want to use any of the keep options like SP or Keep in play

We have access to the time from the off so can decide to only bet 5 minutes from the off, stop betting 10 seconds form the off to close out etc we can log prices to use later at 1 minute intervals and so on

We can even link more than one sheet to excel and have next goal and CS markets logged if we want to look for arbs or even ways to reduce PC, have win and place markets linked etc

As you can see we have access to so much info and if you're botting you should make use of that info the same as you would when looking at the market and deciding when to bet. You might not lay a price if £20K was stacked up in lay box 1 when doing it manually so don't do it automated either as you have access to that info but unless you think carefully at the beginning it's easy to miss those conditions.

[starter22]

Nice work Millionaire

It's the basics that I need to learn first, am not very good on Excel yet but willing to
learn by example. Hopefully this could be of much use to me? Triggers and stuff
especially for the dogs are something I have been wanting to get into for some time.
Maybe this will encourage a few more examples from those wiser than me?

All the best - Mick

P.S. Thanks to TheGeek for allowing info from a different program too.

[1/4 of a millionaire fund]

Nice work Millionaire

It's the basics that I need to learn first, am not very good on Excel yet but willing to
learn by example. Hopefully this could be of much use to me? Triggers and stuff
especially for the dogs are something I have been wanting to get into for some time.
Maybe this will encourage a few more examples from those wiser than me?

All the best - Mick

P.S. Thanks to TheGeek for allowing info from a different program too.

No problem starter22, everyone has to start somewhere and the sooner we start the quicker we learn, I'll list some of the triggers later and the options available to us for offsets etc

Hopefully if people put forward some simple ideas for a bot we can then go through the process of setting one up from scratch just to get something running

[Scumbag trader]

No problem starter22, everyone has to start somewhere and the sooner we start the quicker we learn, I'll list some of the triggers later and the options available to us for offsets etc

Hopefully if people put forward some simple ideas for a bot we can then go through the process of setting one up from scratch just to get something running

How many triggers are there MF ?

I am as far as placing a bet on excel lol

[Timstertoo]

Ok tutorial done up till now. Is it tomorrow yet?

[1/4 of a millionaire fund]

How many triggers are there MF ?

I am as far as placing a bet on excel lol

There actually quite a few triggers and they're all listed in the documentation at

http://www.*****-software.co.uk/Bett.../help/help.htm

under the "using excel " chapter.

We have all the basic triggers you need such as LAY BACK CANCEL UPDATE which give us the same options we'd have manually betting thru any API interface. note that all triggers need to be entered in uppercase to work.

If we've filled the odds and stake columns the only thing we need to do now is enter a trigger alongside the stakes etc in the trigger column Q i'e putting in LAY will trigger a LAY bet using the odds from column R with a stake equal to whatevers in column S. Once that bet has been placed the columns T and U will be updated with the bet reference number and time of the bet respectively.

If there is data in the Bet Ref column T no further bets will be placed regardless of whatever trigger is in column Q. This means we don't have to worry about duplicate bets continually being sent after the bet is placed.

If we wish to place further bets on that runner we need to use the CLEAR trigger which simply clears the bet reference and bet time columns nothing else, it will not cancel any bets just clear the data from the columns T & U. If we wish to cancel an unmatched bet we need to use the CANCEL trigger command.

Lets not worry too much about the CANCEL and CLEAR just now and look at the more obvious trigger LAY and BACK and additional options available with those.

[Scumbag trader]

One other thing i am maybe wanting to do with excel, is to have live charts on with technical indicators. Is that possible MF ? I'm not ready to go into that just yet though

[1/4 of a millionaire fund]

Trigger modifiers

Much like the options available to us via one click software we can also use a variety of options with our lay and back triggered bets.

I'm no expert with all of the triggers available and quite a few seem to have been added since I last looked so I'll avoid those for now but they should be obvious what they do from the manual.

I'll explain a few of the modifiers for LAY but they can also be applied when backing and do the same thing just in the opposite direction

The modifiers now allow us to have set options with any placed bets the same as if we placed them manually ranging from fill and kill, where we cancel the bet or any unmatched portion after a set time.

We can have offset bets automatically placed once all or part of the bet is matched, we can determine how many offset ticks we want as well as specifying if the bets use the keep options to go in play or into the SP markets once the market goes inplay. We can choose if the offset bet is sent to green any profit by calculating the correct stakes or simply send an opposing bet with the same stake.

Any offset bets can also have a variety of stop loss attributes ranging from simple stop losses for a number of ticks or more ambitious stops such as sliding and percentage based stops.

All the trigger modifiers need to be entered in the correct order for them to work and are separated with a hyphen -

the correct order for the modifiers is Tick Offset - Fill or Kill - Stop Loss - Keep in Play

we do not need to use them all or even at all but they must be in the correct order if used



OK so lets have a look a simple LAY bet we can place with various modifiers applied

LAY-T5

this places a lay bet for us when any of the bet is matched an offset BACK bet will be place equal to the matched portion 3 ticks above our matched price i.e £20 is matched @ 2 an offset back bet of £20 will be sent at odds of 2.10 (5 ticks above 2)

Maybe we'd prefer the bet to be sent with a green up stake for us to save us hedging or building profits on one runner

LAY-TL5

the trigger TL means any offset is now levelled and the program will calculate the new stakes for us, in this case £20 matched at 2's would mean the program sends a stake of £19.05 @ 2.1 giving us a level green of 95p if the offset is matched


An example of a more complicated trigger would be

LAY-TL3-F120-SL4-IP

This will place a lay bet with fill or kill options attached meaning any unmatched portion of the bet is cancelled after 120 seconds, any matched part of the bet would be offset with a levelled stake 3 ticks above the matched price, a stop loss is also applied on this bet at 4 ticks lower than any matched price, the bet also has 'keep' options applied meaning the bet and any offsets would go inplay if it hadn't been already filled or killed.

As you can see we can add plenty of options with the triggers and there's little point me going in depth for all of them but if anyone wants a modifier explained I'm happy to give it a go.