Triggered betting from excel at the market middle

[chopper]

Hello,

I'm trying to (semi) automate my trading from excel.

I'll be trading in more than one market and basically as my position changes in one market it will give me an open position in a 2nd market and I'll be required to make a trade in this 2nd market.

As the prices in the 2nd market change so will the size of the stake at which I need to trade.

The prices will be continually updating in my spreadsheet and therefore so will my desired stake. Say I wish to trade at the current middle of the 2nd market on betfair at the corresponding stake this price gives me...

I feel like the only way I can trigger bet this from excel is by using a macro to first copy and paste the current middle and the corresponding stake into some cells which when populated will shoot off my trade.

Do you guys agree that this is the best way to do this?

If so, I'd be grateful if you could give me some efficient code so I can create a macro to enable me to do this.

Cheers!

[doris_day]

I love your avatar. I'm a great fan of Eric and I'm looking at him right now in my study. He has his nephew with him too.....

[chopper]

He's my lucky mascot.

Anyone got any suggestions for my query?

[Ilena]

Hi Chopper,
What do you mean by the middle of the second market? I read that as the midpoint between the best or best three back and lay prices (maybe with volume), which could be tracked with a link. I don't think it can be that though, as there would need to be another criteria to fire the trigger.

Regards,

Ilena

[chopper]

By the middle I just meant the average of the best back and the best lay.

So if the best back is at 2.4 and the best lay is at 2.6 then I can buy at 41.67% or sell at 38.46% and the middle (the average of these 2 numbers) is 40.06% which is 2.496 in odds form. Obviously you have to trade in certain increments on betfair, this is just an example.

Actually I've complicated matters by mentioning the middle as its not relevant. In a liquid market I'll probably be happy to take the best back or lay, I just didn't want anyone suggesting to do this I simply had to request worse odds and therefore I'd get done, I want more control.

Here's an example of my actual problem:

Say according to my strategy I need to place a Back. Let's say at 2.4 my spreadsheet tells me I need to back at £150. However if the price goes down to 2.2 then my optimum trade would be say £130. I still need to cover my position so I'd be happy to take the 2.2. But how do I freeze these constantly changing prices and their corresponding constantly changing stakes, and shoot them off as an order as quick as possible?

The only way I can think of is a macro to copy and paste them, but maybe there's a better way?

[GaryRussell]

It doesn't matter how quickly the odds are changing unless I haven't understood what you are trying to do. It's possible that you are overcomplicating matters.

For example:-

Trigger column: =if(h5<=2.4,"LAY","")
Odds column: =h5
Stake column: =5/(h5-1)

In the above example it doesn't matter how quickly the odds have changed it will use the correct stake to lay to £5 liability. The stake column is calculated on every refresh and before the trigger is processed. eg. if the odds suddenly drop straight past 2.4 to 2.0 the requested stake will be £5

[Ilena]

I see.
Are you saying you have like a dynamic calculator that updates your stake based on the moving odds? If so, I think you should be able to do that with if statements.

[chopper]

The trade I'll be doing is effectively a hedge for my overall position.

Prices will come from Market 2 into my spreadsheet and given the possibility to trade at these prices I'll be happy to trade at certain prices in a related Market, Market 1.

I'll therefore make a market in Market 1 and if I'm taken I'll then need to hedge in Market 2. Because I'm looking at my overall position I'm not actually interested in trading to a certain liability in one market. Different prices will imply different stake levels and effectively therefore different optimum liabilities.

So say the price in Market 2 is 2.4 and my spreadsheet says 'given I can trade at this price, I'll be happy to trade at a certain price in Market 1'. If I get done my spreadsheet will look at my position in Market 1, the price in Market 2 and will calculate the optimum stake I need to trade. It won't care anymore about the 2.4, it will just care about getting my position flat. If the price in Market 2 changes then there will be another optimum stake (with another liability).

The main thing for me is to get my overall position flat, even though I made my trade in Market 1 based on the 2.4, this number is not important to me any more because given my open position all I need to do now is close it at the best price I can, and the stake this price gives me. Effectively my calculator is memoryless.

I'm sorry if this isn't making much sense. If its not, could someone still tell me what they think about a macro to copy and paste my price and corresponding stake into other cells?

Is that easy to do? Does it use much resources? If someone could give me some VBA code that would be great.

Cheers.

[chopper]

I suppose as I just watch the price and its corresponding stake change I could just trigger the trade.

However it just feels more secure if I copy and paste them first.

Even if I copy and paste it still seems there is room for error as I might copy the price but if it changes suddenly I might copy the wrong stake.

[GaryRussell]

Every time the price changes the stake changes too? If this is the case then I don't understand why you need to copy the stake if all you are going to do is copy the stake every time it changes?

I might be teaching you to suck eggs here if I have misunderstood, but are you aware you can refer to a cell in one worksheet from another? For example you can have a cell in market1 constantly updating with the calculated stake from market2 without using copy and paste, just a formula.