EXCEL Ladder Trading

[thunderfoot]

See my EXCEL based Ladder Trading system at :

http://www.thunderfoot.f2s.com/ladder_trading/

[Anomaly]

That's very cool! I'd love to see it in action.

Richard

[Dedes]

Nice!

[Uncle Ernie]

Looks good - Are you releasing a version

Are the figures generated solely from layits prog in excel ? or your own software.

[thunderfoot]

It does fire-in Bets by one-click on the Active Trading Ladder, and does it quicker than you can see it happening.

Any delay is down to the 'conduit' application (Gruss/BetAngel) processing the bet through to Betfair.

That's the basics sorted as far as I can see for 'One Click Trading'. It's still being developed to add-in features that I want, such as automatically firing-in an opposite bet.

The idea of showing it now was to give you all an idea of what can be done in EXCEL ..... and why I'm such a fan of EXCEL .

[Uncle Ernie]

[Mitch]

Very nice TF.

Is it a big userform or controls on a worksheet?

The reason I ask is that I'd like to have a go at creating one just to see if I can rather than it being any use to me functionally, but I can't (on first thoughts) imagine how best to go about creating the 'vertical odds' part of it.

Would you be able to give me any clues/ideas?

Also, was it running when that picture was created?

Thanks,
Mitch

[thunderfoot]

Very nice TF.
Is it a big userform or controls on a worksheet?
The reason I ask is that I'd like to have a go at creating one just to see if I can rather than it being any use to me functionally, but I can't (on first thoughts) imagine how best to go about creating the 'vertical odds' part of it.
Would you be able to give me any clues/ideas?
Also, was it running when that picture was created?
Thanks,
Mitch

Hi Mitch,

Some EXCEL 'tips' ....
To get the 'ladder' format you simply have to use the 'scroll bars', which have a cell linked to them ... I've set mine to the 'middle' section of the 'ladder' and used formulae to increment by 1 either side. Then I've set-up a table showing all the available Odds in Betfair, and used VLOOKUP to populate the 'Ladder' from this table.

To 1 click trade I've set-up an invisible (i.e. No Lines or Fill) Text Box above each section that would show the money on the LAY & BACK side ... so 19 on each side, but only on the 'Active' ladder. I've then written simple macro(s) to fire-in the 'pre-set' stake at the Odds I've clicked against, and attached these to each Text Box.

That should get you up 'n running

To be honest I don't think it was actually operating when I did the screen-shots, but I just wanted some figures to be shown so that cells were populated with some realistic data.

[Captain Sensible]

Do you have the flickering mouse pointer on every excel refresh ? Gotta say that's beginning to bug me

[Mitch]

It might just be my imagination but I find the flickering not as bad if I have BA minimised rather than just opening Excel over the top of it

[Mitch]

Thanks for the hints TF. In the time since I posted I had a little go.

I've used INDEX linked to the scrollbar value for the odds in the middle and INDEX/MATCH to populate the money available parts either side.

I've created a table of odds like you have and used a macro to populate the columns to the right of it with the (3 ladders selections) back and lay money available with a macro each time BA inputs to Excel.

One question about the scrollbars.... how do you release the focus from it after you've clicked it - or does the flashing square not drive you nuts like it does me?

Do you have 38 macros for placing bets - 1 for each text box?

I also don't think I've understood how you've populated the money available on either side of the ladder. How do you get the back money available for Selection 1 from G5 to the ladder. The way I've done it makes it unacceptably slow, and although my pc isn't state-of-the-art it isn't a relic either.

[tkp]

Hi Thunderfoot and Mitch ...your interface looks very nice TF.

I've got something similar - almost complete (it fires back and lays) but I 've got to a point where I can't progress with what I want it to do - i.e. I want to be able to keep a track of matched amounts by bet ref - so that I can then lay or back at just the amount matched etc.......

How do you keep track of the pending offers/outstanding amounts by bet ref ? I don't see anyway of getting this from BFT ?

Mitch - there is a simpler way to fire bets off - using the Worksheet_selectionChange of a sheet....... instead of having to use text boxes you can just click into the selection (cell) and excel will trigger a macro.

Code it like this: (you put this into the SHEET OBJECT)

Public rTarget As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub 'this stops this code firing if more than one cell is selected

Set rTarget = Target 'sets up the selected cell as a public variable so we can use it again to get data from or from adjacent cells etc etc

If Not Application.Intersect(rTarget, Range("L12")) Is Nothing And Target.Value > 0 Then 'what this line says is that when L12 is clicked into and it has a
'value then it will perform the Lay1 (I use the >0 to ensure that I don't fire a bet off on a cell with no VOLUME - as my code puts the volumes into an array too)
Call Lay1
End If
'you have to do this for each cell you want to be able to fire a bet off from - back and lay side within your ladder.
'it also means you can put other criteria into this so that you can test for other flags/options being set.

Hope that helps.

Perhaps we should all compare our efforts - although mine doesn't look as nice as TFs.


Cheers

tkp

ps: Scrollbars and releasing focus - if anyone knows how to do this then please help - it is so annoying !

[tkp]

Ok. Got ladder trader working.....wow - it really makes a difference in how you (I) view a market !

Figured out how to do what I wanted to do with the matched stake.

Only prob I have is with the CANCEL trigger - any one have any experience of this - what's it 'sposed to do ?

I had thought it should CANCEL outstanding bets or the bet which had CANCEL next to it ?

How long does it have to be left in the trigger cell for ?

Cheers,

tkp

[Mitch]

Hey tkp, nice when you get something to work how you want isn't it..... so I'm told

The cancel trigger works pretty much immediately (well, the next time BA refreshes Excel) but it has to be used with a bet ref in col Q, otherwise it won't know which bet to cancel. There isn't a trigger to cancel all unmatched bets.

I was going to use the Selection_Change event to place the bets but I still don't understand the need for a macro for each cell you want to bet from.... am I missing something obvious?

Agree 100% on the scrollbar focus thingy. The only way I've found to stop it is to use code to switch focus back to the worksheet rather than the control but it has the drawback that you can't leave the mouse pressed on the scrollbar button to keep scrolling. I notice there is a KeyUp event for the scrollbar which I had hoped would be fired when the mouse was released but it didn't work for me so maybe I misunderstood it.

Have you populated your ladders using formulae or code? Mine is all formula but the ones to get the amounts available is about 3 lines deep in the formula bar and looks ugly although it certainly works fast enough.

Is there anything either of you are still puzzling over..... 3 heads must be better than 1

[nhmaster]

Is anyone prepared to offer their spreadsheet version of ladder trading for those of us who have not managed to master it yet, I MUST TRY HARDER

nhmaster2001@yahoo.co.uk

Thanks in advance

Shaun