excel dutching calculator

[r_ivica]

i tryed all this excel samples distributed here on forum and didn't find the answer to my problem, or i am not looking it right.

Here it is showing it on example:

I have this situation (all before match starts):
ManUtd -74
Draw +218
Arsenal +324

and now i want to green up this situation by using excel

BF odds are (back/lay):
ManUtd 1,4/1,41
Draw 4,1/4,2
Arsenal 7/7,2

so i want to input in excel manually situation which i have on betfair according to this example so that excel calculates how to green up this to get each way equal profit.
(all numbers want to input manually, no need for auto refreshing prices etc.)

Please help me providing me some excel example for this.
Thanks for help

[osknows]

Here it is showing it on example:

I have this situation (all before match starts):
ManUtd -74
Draw +218
Arsenal +324

This doesn't look like dutching to me, it looks like you've layed Man U then placed another bet on either Arsenal to win or layed the Draw?
The files on this thread won't help with this.

[r_ivica]

well, yes, before this situtation was several different bets.
now need to equal profit with this situation

do you have any idea where to find this solution?
i looked everywhere with no luck

[osknows]

Gary has a solution here http://gruss-software.co.uk/forum/viewtopic.php?t=4659 which relies on all previous bets in the market being known. This is by far the easiest way to do what you need.

I don't think it's possible to solve as a single equation when the original positions are unknown since the difference in stake, odds and position of the original bets cannot always be resolved by a single position to give equal profit.

Here is an example spreadsheet which creates 3 positions and then summed to give a known overall position as in your question; Without knowing your original bets I tried to get something similar but the -£74, £218 & £324 figues are different in my example.

Trying to solve gives you 6 possible positions each of which have 3 simultaneous equations which cannot be solved to an equal profit. I believe sensitivity analysis is the only way to solve these where you have to decide:
a) likely event outcome
b) how much profit you want to weight for each outcome

There may well be a way to solve these equations but would need someone smarter than me I'm afraid. Anyone else have any ideas?

http://www.mediafire.com/?jmioyzog0yi

[jokerjoe]

I'd say there were only three possible variables as it doesn't make sense to both back and lay at the same time. Unfortunately that means you then have a function to convert the new position to either a back or a lay, which makes solving pretty hard. I'm not sure how you would solve it in a closed form.

Fortunately you can always use Solver. It has a few quirks, in this example you need to wipe the variable cells and run twice to get the results. That could prob be fixed.

http://www.mediafire.com/?dodtzgngnzn

[osknows]

That's a really neat method jokerjoe, thanks. I'd never used the solver before.

[jokerjoe]

johnnic suggests an iterative approach here.

In this version you hedge by laying in order of greatest potential P/L, or by backing in order of lowest potential P/L. It's quick and easy but like an carpet that's not cut right in won't ever quite fit 100%, you can do more loops to fine tune it but then you'll end up backing/laying the field against the overround.

Interesting to note that in this example Solver didn't pick up the laying solution which has the higher greened-up P/L. Again this can prob be fixed.

I think there are only two ways of doing it like this, doing it all backs or lays in order, that's just a hunch though. You then compare the two to see which is better. The difference is massive in this case because if you look at the market overrounds there's an arb available.

http://www.mediafire.com/?lnnl4jmlmtulzdm

[johnnic]

Thanks JokerJoe, that is very useful.

Now, all I have to do is work out how to put it all in VBA.javascript:emoticon(':?')

John

[osknows]

I was having a read around the web and there seems to be a lot of engineering type problems where the Excel solver is finding localised max and min solutions and not the global. Perhaps that's why the solver needs to be run twice

The site below seems like a good find, there are lots of examples and VBA addins with ability to view source code. Some of the addins also introduce randomisation to overcome localised solutions

http://www.me.utexas.edu/~jensen/ORMM/c ... start.html

http://www.me.utexas.edu/~jensen/ORMM/c ... index.html

[Trussy]

I'm probably missing something blindingly obvious here. I have a spreadsheet which calculates from a fixed stake the bet required to win a fixed amount. It works fine until I try to place the bets on the exchange and as I place each bet, the profit drops on the previous horse(s) so that if I dutch say six, by the time the sixth bet goes on the profit on the fitrst one, two or three is showing a loss.

I know that I am missing something in my calculations but have you any idea what? So that they all show an equal profit?

[Shaun]

There are 2 options here.
1) Dutch Stake - this is where you have a set amount you wish to dutch for equal profit.
Convert your selections from price to a percentage then use this formula
(selections %/total of all selections %) x Stake
Do this for each runner.

2) Dutch Profit - this is where you want to win a set profit no matter the winner.
First convert price of selection to percentage then add these together = A.
Then 1-A = B
Then (A/B)+1 = C
Then ((Selections% X Stake) X C ) (this formula for each runner.)

[Trussy]

many thanks for the quick reply. I will have a play with this over the next few days and see how it goes

Thanks again

Trussy

[mr8147]

http://www.gruss-software.co.uk/forum/v ... php?t=5366

[Trussy]

Hi mr8147
Have downloaded your spreadsheet but being a newbie and not that ofay with excel any chance of some instructions to go with it at all please.

many thanks

Trussy

[r_ivica]

i tryed all this excel samples distributed here on forum and didn't find the answer to my problem, or i am not looking it right.

Here it is showing it on example:

I have this situation (all before match starts):
ManUtd -74
Draw +218
Arsenal +324

and now i want to green up this situation by using excel

BF odds are (back/lay):
ManUtd 1,4/1,41
Draw 4,1/4,2
Arsenal 7/7,2

so i want to input in excel manually situation which i have on betfair according to this example so that excel calculates how to green up this to get each way equal profit.
(all numbers want to input manually, no need for auto refreshing prices etc.)

Please help me providing me some excel example for this.
Thanks for help

I created a correct calculation for this!

https://skydrive.live.com/redir.aspx?ci ... 5ktNKV4%24