Lay The Field Spreadsheet

Samples developed by Cymatic and others. Please share your spreadsheets here.
Forum rules
Users can post their spreadsheets here, to share with others and for discussion purposes.
If you download a spreadsheet from here, please carefully test any strategies yourself before using it with real money. Cymatic accepts no liability for any spreadsheets in this forum.
Now bring on the robots! Happy automated trading... :)

Lay The Field Spreadsheet

Postby Gavin » Tue Jul 14, 2015 11:37 pm

Hi,

You can also watch out the video explaining this strategy:
https://www.youtube.com/watch?v=07cwdtPMKfQ

The attached spreadsheet can be used to 'lay the field'. Note that I have only copied the formulae down to first 20 rows of selections, so any selections further down than that would never be layed. If you want to copy the formulae down further you can do.

'Laying the field' is a strategy in which you seek to lay more than one horse in-play at a low price (for example 1.6), so that you make a profit whatever the outcome. I'm not claiming that a simple 'Lay The Field' strategy is profitable, it might or might not be, but this spreadsheet is merely intended to demonstrate some of the techniques and principles involved in using Excel and Cymatic together.

The spreadsheet will lay only those selection that meet the criteria set, where the best available lay price is less than the price specified in cell BB1. So if you set this value to be bigger than the highest possible odds (1000), you would instantly lay all 20 selections. If you set this value to something like 2.0, you will only try to lay the selections when their prices fall below 2.0, so that way you would use less account funds because you won't send as many bets.

Cells BB2 specifies that actual price that you want to get matched at. So setting this to 1.61 would specify that the bets are sent with a requested price of 1.61. So settings BB1 to 2.0 and cell BB2 to 1.61, would result in sending triggered bets when the best price falls to 2.0, hoping to get matched at your requested price of 1.61 if the market price continues to fall from 2.0 down to 1.61. If the price doesn't reach 1.61, those submitted bets obviously won't get matched.

Hopefully this spreadsheet will demonstrates some techniques:

1. Using formulae to trigger bets. There are formulae in the Command, Odds and Stake columns.
2. A button to clear the contents of the Status column, to re-arm the triggers ready for sending new bets. Clicking the button runs some Visual Basic code. To see the code, choose the developer section in the Excel ribbon, then click on Visual Basic Editor (it's the button at the left end of the ribbon in Excel 2007). You'll find the code within Module1.
3. Some rules (conditions) have been typed in cells at the top of the Cymatic worksheet, just above the Command columns, although these could equally well be put anywhere in the columns to the right of the Report column, or even in a separate worksheet. The formulae in the command columns contain conditions that are based upon the values in those rules cells.

Hover your mouse over the rules cells to see my comments explaining what they do.

This worksheet could potentially be used to lay the field on all horse racing markets, all day, if used in conjunction with the auto-pilot to automatically change the market throughout the day. You would also need to select the option to 'Clear the contents of status cells when selecting a different market'. However please note that this could result in you instantly laying a strong favourite before the race starts if a market is selected that contains a strong favourite (with a price at or below 2.0 in the example given above). Lay-the-field strategies tend to involve laying horses in play once their odds fall below a certain level, not merely laying strong favourites before the race.

The spreadsheet was designed in Excel 2007, but it has been saved in the old 2003 format in case that helps some folks who only have the older version of Excel. It would work equally well if saved as a macro enabled Excel 2007 file (with the file extension xlsm). I have not used Excel 2003 with Cymatic, so I'd be glad to hear from anyone who does, just to confirm that everything works equally well in Excel 2003 (which I think it should).

UPDATE (15/7/2015): The file has been uploaded again, with the correct formulae this time! Apologies.

Happy trading!

Kind regards,

Gavin
You do not have the required permissions to view the files attached to this post.
User avatar
Gavin
Site Admin
 
Posts: 3268
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Lay The Field Spreadsheet

Postby canuk » Wed Jul 15, 2015 4:47 am

Hi Gavin
Thank you for this great arrangement, I am looking forward to pushing myself to learn some visual basic coding, to get the best out of your development.
I downloaded the first of your samples excel Lay The Field, and could not get it to activate as expected.
I am in Canada East Coast at midnight playing Aussie horse in trial mode, the only issue there is the Autopilot (I recall another post on that issue)

To get it to perform as expected I modified the formula in the activation cells, and it worked OK for me.

BA8......... =IF(AND($BB$4=TRUE,$J8<$BB$1,$J8>0),"LAY","waiting")
BB8......... =IF(AND($BB$4=TRUE,$J8<$BB$1,$J8>0),$BB$1,"")
BC8......... =IF(AND($BB$4=TRUE,$J8<$BB$1,$J8>0),$BB$3,"")
Dragged to row 27 or more if required course

Please comment on suggested change, and thank you again , for your development

Good Night
Bob
canuk
 
Posts: 36
Joined: Mon Jul 13, 2015 8:59 pm
Location: Ontario Canada

Re: Lay The Field Spreadsheet

Postby Gavin » Wed Jul 15, 2015 8:58 am

Hi Bob,

Glad to hear your comments.

Thanks, using the AND statement looks like a more concise and elegant way of doing things, rather than nested IF statements. Easier to read.

I think your second formula would do something slightly different to what I intended. This would cause the price of the new bet to be the contents of BB1, rather than BB2. Please note that BB1 is meant to be the trigger price, not the actual price of the bet that you send. The actual price of the bet is specified in BB2. So I think your formula should be:

BB8......... =IF(AND($BB$4=TRUE,$J8<$BB$1,$J8>0),$BB$2,"")

The idea is to send a bet when price is starting to fall and is getting close to the actual price that you want. When price falls to BB1 (for example 2.0), you send a lay bet at a price of BB2 (for example 1.61).

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3268
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Lay The Field Spreadsheet

Postby Gavin » Wed Jul 15, 2015 9:14 am

...and also, for anyone wanting to use the autopilot to trade the Lay The Field strategy on all races, automatically, all day:

I suggest that you only tick races that do not have a strong favourite (especially if its price is less than 2.0 in the example I gave above!). So before connecting to Excel, load the autopilot with all the races of the day, with them all ticked, then actually open each race in the main Cymatic window one by one, checking whether there is a strong favourite. Remove the tick from the race in the autopilot list if the race has a strong favourite.

You might also consider only choosing short races such as 5f, 6f, or 7f, if you feel that those have a better chance of a close finish. You don't want a long race where one horse ends up dominating and no other horse ever drops to very low odds (but I suppose you could watch the races on live video and decide whether to trade it or not, simply click the 'Activated' check box in Excel to turn trading on or off). You want races where more than one horse might compete at the finish and thus you get matched on more than one horse at very low odds to guarantee a profit. If you only get matched on one horse then you will obviously make a loss (because it will have gone on to win).

It won't take very long to find and exclude any races that you don't want (especially if you use the autopilot's 'Next' button to help you jump to each race, vet the runners, then remove the tick if necessary before clicking 'Next' again to look at the next race).

Once you are happy that only suitable races are ticked in the autopilot, you can then select the first race of the day again. Make sure that the autopilot itself is activated (it has a separate box to tick in the Tools section in the main window).

Now connect to Excel, ensure that the Activated button is ticked in the Cymatic worksheet and you're ready to go.

That way, the autopilot will be able to skip any races that you have decided in advance are not suitable to use with this strategy.

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3268
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Lay The Field Spreadsheet

Postby Gavin » Wed Jul 15, 2015 10:40 am

Hi Bob,

Yes, my original formulae was actually not working, thanks for informing me. I have re-loaded the file with the corrections (and some slight improvements).

The problem in the formula was the part that was checking whether BB4 = TRUE, because I had changed the contents of BB4 from being a text string to being TRUE/FALSE (Boolean) when I linked it to the 'Activated' check-box.

I ended up using the AND statements in the Odds and Stake columns, but a nested if in the Command column as shown below:

=IF($BB$4<>TRUE,"Deactivated",IF($J8>=$BB$1,"Watching prices",IF($J8=0,"Price is 0","LAY")))

I also unchecked the 'Activated' check-box before saving the file, so that it doesn't start betting as soon as you open it. So now you just need to click the 'Activated' check-box after opening the spreadsheet, when you are ready for it to begin.

Thanks and kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3268
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Lay The Field Spreadsheet

Postby sjaak1943 » Wed Jul 15, 2015 2:24 pm

I noticed this, the LAY trigger places a lot of bets under 1,61, last race 5 on the same horse.
Need a cell where i can see a bet on that runner is placed, normally the betref cell, but it was still empty after 9 bets placed on the same horse in the next race.

Sjaak
Sjaak

To make a bot is easier then making money with it!
sjaak1943
 
Posts: 63
Joined: Tue Mar 03, 2015 6:31 pm

Re: Lay The Field Spreadsheet

Postby Gavin » Wed Jul 15, 2015 4:08 pm

I have now replaced the Excel file with another version, I had forgotten to copy the new formulae down the stake and odds columns.

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3268
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Lay The Field Spreadsheet

Postby Gavin » Wed Jul 15, 2015 4:10 pm

sjaak1943 wrote:I noticed this, the LAY trigger places a lot of bets under 1,61, last race 5 on the same horse.
Need a cell where i can see a bet on that runner is placed, normally the betref cell, but it was still empty after 9 bets placed on the same horse in the next race.

Sjaak


Hi Sjaak,

Please confirm if you using the Lay The Field spreadsheet without any changes, or something that you designed yourself?

When the LAY command appears and is detected by the Cymatic program, Cymatic will put the word "PENDING" in the status cell on that row. Any text in the status cell will prevent another bet being sent on that selection (until you clear the contents of the status cell). When Betfair responds, the word "PLACED" will be put in the status cell and the bet ID will appear in the Bet Ref cell.

So ensure that you have not written any code / macros that are clearing the status cells lots of times.

Also, don't keep clicking the 'Clear Status' button. It is only there to demonstrate how to clear the cells using a button, or for anyone who has not selected the option "Clear status cells when selecting a different market". You only want to clear the status cells once at the start of each market. You don't want to do it again during the race.

Kind regards,

Gavin
User avatar
Gavin
Site Admin
 
Posts: 3268
Joined: Wed Jan 23, 2013 9:11 pm
Location: United Kingdom

Re: Lay The Field Spreadsheet

Postby zogark » Wed Jul 15, 2015 5:40 pm

i ve tested Lay The Field Spreadsheet and it work like a charm the trigger fires, but sometimes the bet doesn't get matched loooooool

also the Spreadsheet is user-friendly i like that ( you can edit according to your preferences in the green BA , BB )

PS - I do not know anything about excel formulas

Image
zogark
 
Posts: 40
Joined: Tue Apr 14, 2015 9:24 pm

Re: Lay The Field Spreadsheet

Postby sjaak1943 » Wed Jul 15, 2015 7:15 pm

Gavin wrote:
sjaak1943 wrote:I noticed this, the LAY trigger places a lot of bets under 1,61, last race 5 on the same horse.
Need a cell where i can see a bet on that runner is placed, normally the betref cell, but it was still empty after 9 bets placed on the same horse in the next race.

Sjaak


Hi Sjaak,

Please confirm if you using the Lay The Field spreadsheet without any changes, or something that you designed yourself?

I have downloaded the workbook again to be sure! Now it placed only 1 bet, "PENDING" was in 3 statuscells, but nothing in the other cells, like Matched, Amount and Betref.
The only thing I changed was the stake.

In the second race the 3 "PENDINGS" were stll there from the first race and 6 LAYbets were placed on a clean row ofcourse on the same horse and there was FAILURE in the statuscell.


Sjaak
Last edited by sjaak1943 on Wed Jul 15, 2015 9:08 pm, edited 1 time in total.
Sjaak

To make a bot is easier then making money with it!
sjaak1943
 
Posts: 63
Joined: Tue Mar 03, 2015 6:31 pm

Next

Return to Excel - Sample Spreadsheets

Who is online

Users browsing this forum: No registered users and 0 guests

cron