Cymatic Trader Community

[Iain]

Dutching in its simple form allows you to back several selections (lets assume horse racing) to guarantee the same profit regardless which one wins. When you place your dutch bet do you think they all have an equal chance of winning? I don't think so. Most likely you decide which horses may be a contender and include them in your dutch. The more horses you include the lower the profit. Wouldn't it be better to vary the amount of profit each selection provides? The most likely winners provide maximum profit and the horses with a lower chance a smaller profit or just break even.

Searching for 'dutching' on the internet will give loads of results for dutching calculators. However, try searching for 'variable profit dutching' and you will struggle to find anything relevant, mostly the same results as your 'dutching' search provided. The maths involved for variable profit dutching is certainly more complex.

This spreadsheet provides it. It is very simple to use, in cell M3 enter your total stake or in cell M4 enter your profit target for 1% profit. So if you enter a profit target of 0.50 then you will gain 50.00 units for a 100% stake, 25 units for a 50% stake and so on. With this type of staking there is no ceiling to your liability, so keep an eye on it. To use the total stake in M3 the M4 cell must be empty, target staking will be used if there is a value in M4. There are checkboxes beside each horse, just select each horse you would like to back and adjust the slider bar to choose the amount of profit. The default profit value is scratch so when you select horse(s) nothing will happen until you choose a profit for at least one horse. It can also be used for conventional dutching (just set the profit slider to 100% for each selection.) Once you are done just click the 'Submit Bets' button

It is also possible to move the profit slider to produce negative values. You maybe think a horse has an outside chance so you can set it to lose half your stake. However there is a problem when using negative values. The spreadsheet works by calculating the amount of stake needed to provide 1% profit and multiplys that by your profit setting, so say 100% profit generates 200.00 then 10% will give 20.00, but negative values still use the potential profit for its calculations so in the above scenario a 10% loss would mean a loss of 20.00 when our stake is 100.00 so it is actually a 20% loss of stake. When using negative values ignore the percentage setting and be guided by the profit/loss, which is correct. The other problem is you can move the profit slider too far which produces negative bets, This will cause an error if you attempt to submit bets.

So please try it out, it certainly works fine when you don't use negative profit. Any suggestions for improvements or how I can fix the negative values problem are welcome.

As always, demo mode only until you are confident it works as you expect.

[Gavin]

Hi Iain,

Very good work on the maths, well done. There are some efficiency aspects that I noticed that you could improve, to get it running really fast, which I'd recommend addressing if you get a chance.

Your code below works but I'd recommend that you look for different way of doing it, I explain below:

Code: Select all

Private Sub CommandButton1_Click()
    For i = 0 To 39
        If Sheets("Dutch").Range("A2").Offset(i, 0) = True Then
            Sheets("Cymatic").Range("BC8").Offset(i, 0) = Sheets("Dutch").Range("E2").Offset(i, 0)
            Sheets("Cymatic").Range("BB8").Offset(i, 0) = Sheets("Dutch").Range("C2").Offset(i, 0)
            Sheets("Cymatic").Range("BA8").Offset(i, 0) = "BACK"
        End If
    Next
End Sub

The problem with this code is that it triggers Cymatic to send a separate order (i.e. a separate API to Betfair) for each row on which there is a command. After every line in your code, you are changing one cell in the Cymatic worksheet. Each change in that Worksheet, causes an event to fire which triggers Cymatic to analyse the sheet and look for commands. So if you want to make 5 bets, each of which has three aspects (command, odds and stake) then you will be triggering the analysis 15 times! So Cymatic will send the 5 orders in 5 separate actions, rather than as a single batch, and it will take 15 actions to find the 5 commands.

This really slows things down, so it would be better to release all the commands at the same time, in one action, so that they are all sent together as a single batch of bets and only causes Cymatic to do one process instead of 15 in the example above.

Sending the bets as a single batch, also has the benefit that if one order is invalid, Betfair will reject those entire batch.

So how to do it? There are a number of ways, depending on what you prefer. Here is one such example of how you could do it, by using formulae instead of inserting values with VBA:

Put a formula in the Command, Stake and Odds cells in the Cymatic sheet refer to your calculations in the Dutch sheet.

You'll want those formulae to include some test for whether you are ready to submit, otherwise they evaluate to blank ("").

For example:

=IF(Dutch!$L$9=TRUE,"BACK","")

in the command cells in the Cymatic sheet.

(put similar formulae in the Cymatic sheet's Odds and Stake columns, that refer to the relevant cells in the Dutch sheet, with the same condition based on L9).

Then in the Dutch sheet, use either a button or a checkbox control, to toggle the value in cell L9 to either TRUE or FALSE.

When L9 is changed to TRUE, all the bets will be instantly sent as a single batch.

The set L9 back to FALSE if you want to start making choices for a new set of bets, ready to use again. Then just set L9 to true as soon as you are ready for the bets to be submitted.

So by using formulae the bets will all be processed by Cymatic in one action and sent as a single batch, which occurs after the sheet calculates, rather than every time you insert a command in a cell using VBA.

I'd also recommend avoiding using the OFFSET command and also dynamic named ranges, unfortunately they are very inefficient in Excel and slow things down. Your named ranges are dynamic (i.e. the size of them is based on OFFSETs), so I'd try to eliminate the need for dynamic named ranges altogether. Perhaps you could replace the dynamic ranges and offsets, with static named ranges that refer to the whole 40 rows, then add something to the dutching formulae that ignores any rows that are empty or not ticked? I'm not sure if that is possible, but if it is then it could be a lot more efficient than dynamic ranges and OFFSETs. If it is not possible to adapt the maths to avoid dynamic ranges, then they'll have to stay.

Kind regards,

Gavin

[Iain]

Hi Gavin,

Thank you very much for looking at the code and your suggestions.

I have modified the spreadsheet as you suggested. I have also changed the dynamic named ranges to static. When I submit bets it is noticeably faster.

The new version is attached.

[Gavin]

Hi Iain,

Perfect!

Kind regards,

Gavin

[Silverhorse]

Thanks for sharing Lain.
I will have a play around with it sometime this week. A great asset towards sports trading and a variable Dutching calc is right up my street.

Just perfect.
Thank you

[Iain]

Hello everyone,

Here is a new version. It now includes lay dutching and you can dutch to a fixed liability or a target profit.The lay version is in a seperate sheet. It works the same way as the backing version, except you enter your fixed liability in M3 or your target profit in M4. As with the back version if there is a value in M4 then target profit will be used. There is also a slider to adjust profit/loss, but you are controlling liability on the individual selection. Don't confuse the percentage as a percentage of liability it is a percentage of the entire range from your maximum liability to your maximum profit. Setting the slider to 0% is not laying it at all and 100% is maximum liability. Somewhere in between you will reach the crossover point where there is no liability and moving it further will take you into profit.

One other change is when you click 'Submit Bets' on either the back or lay version the 'Submit Bets' buttons will be disabled until a new market is selected. This is to prevent accidentally trying to submit bets with different selections or submitting a back and lay dutch on the same market.

I haven't tested it extensively so please let me know if you encounter any problems?

[xplayer]

Hi Iain,

Thanks for sharing this fine work - love your grasp of the maths involved by the way.

Will have a play at the weekend in Demo Mode.

Ray

[Iain]

Thanks Ray, for your kind words.

Just a small update but an important one. I noticed a problem with both versions of dutching. After you submit bets, if you click on additional checkboxes an additional bet for that selection will immediately be submitted. This is unexpected and undesirable because your liability will be higher and it will give an unbalanced dutch bet. When you click on additional checkboxes, it wouldn't be your intention to submit those bets. Also you could navigate to another market, then come back to the market you had already placed a bet on and submit a new bet.

This update addresses both problems. When you click 'Submit Bets' on either sheet, the 'Submit Bets' buttons on both sheets will be disabled (after a short delay to ensure the Cymatic application has sufficient time to place the bets.) Clicking on additional checkboxes after this will have no effect on your bets. The spreadsheet will still calculate your dutch, but there will be no way of submitting new bets until you select a different market. If you select any market that already contains bets the 'Submit Bets' buttons on both sheets will be disabled.

I have also added a liquidity column to the left of the odds with a '$' header. This allows you to quickly see if there is enough money to instantly match your bets. Adding a column messes up the VBA code that refers directly to a range. I named all the ranges used in my VBA code and changed the code to refer to the names instead of specific ranges. This allows you to add or delete columns/rows without having to modify your VBA code. Excel adjusts your named ranges when you add/delete columns/rows. Just a tip for anyone else new to VBA.

[Getafe]

Hello Iain,

Many thanks for this. A first rate piece of kit

[footysystems]

Hi Iain,

I would also like to say a really nice spreadsheet having tried dutching many times this is a breathe of fresh air, currently trying out on the correct score market