EXCEL Ladder Trading

[tkp]

Hi Mitch - see you're a night owl too.

""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.
""
Cheers for that - I know what I am doing wrong now.....I'm not allowing enough time for the refresh delay...

""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?
"" - you dont need a macro for each cell....you need to check each cell to see if it has been clicked.....then you can call any routine you want. That's what the code below does:

If Not Application.Intersect(rTarget, Range("L12")) Is Nothing then
doFireABet or some other routine
End If

So this will happily wait for cell L12 to be click - when it is it runs a routine to make a bet....


I have mine set that in Selection_change event I monitor the trigger cells...when one is selected I perform either a lay or back (depending on the side of the ladder). Simple - no need to have invisible objects on the screen - which will slow things down.


""Have you populated your ladders using formulae or code?""

Code. How on earth have you managed to do it with a formula ? Perhaps this could be faster than my code - any chance you could let me see your formula....I could then test for speed.

Not stuck on anything else at the mo - now that you have answered my CANCEL trigger question.

I'm just tidying the interface now and then taking out/re-writing any speed reducing code.

Let me know if you still need help with the selection_change thing - as I can send you an example if you want.


tkp

[Mitch]

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

I'm quite happy for anyone to have mine, but only after I've finished and tested it. Don't know how long that will be though.

If it is just a case of you can't quite get it to do what you want it to then there seems to be 3 people working on one who could give you ideas.

[Mitch]

Hi Mitch - see you're a night owl too.

Yes, I'm terrible for it. The only time I get a decent kip is at the weekend when I can get up late.

""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?
""[/i] - you dont need a macro for each cell....you need to check each cell to see if it has been clicked.....then you can call any routine you want. That's what the code below does:

If Not Application.Intersect(rTarget, Range("L12")) Is Nothing then
doFireABet or some other routine
End If

So this will happily wait for cell L12 to be click - when it is it runs a routine to make a bet....

But do you have a check for each cell? When I got round to it I was going to do the intersect thing with named ranges to see if anywhere in the ladder back or lay sections had been clicked so it would only require 1 line of code. Then use Target.Offset(0,1) to get the odds if the back side is clicked or Offset(0,-1) if it's a lay

""Have you populated your ladders using formulae or code?""

Code. How on earth have you managed to do it with a formula ? Perhaps this could be faster than my code - any chance you could let me see your formula....I could then test for speed.

This is my formula, did I mention it wasn't pretty?

=IF(MOD(MATCH(Main!$C13,INDIRECT("'From BA'!"&"R"&scrsel1&"C2:R"&scrsel1&"C6",0),FALSE),2)=1,INDEX('From BA'!$A$1:$M$44,scrsel1,MATCH(Main!$C13,INDIRECT("'From BA'!"&"R"&scrsel1&"C2:R"&scrsel1&"C6",0),FALSE)+2),0)

Main is the sheet my ladders are on.
From BA is the sheet BA populates.
scrsel1 is the name of the cell that the scrollbar to choose the selection is linked to.

I suppose you can only test the speed of code vs formula if you have set the rest of your sheet up in the same way as I have.

Your comments would be appreciated.

[tkp]

This is my formula, did I mention it wasn't pretty?
=IF(MOD(MATCH(Main!$C13,INDIRECT("'From BA'!"&"R"&scrsel1&"C2:R"&scrsel1&"C6",0),FALSE),2)=1,INDEX('From BA'!$A$1:$M$44,scrsel1,MATCH(Main!$C13,INDIRECT("'From BA'!"&"R"&scrsel1&"C2:R"&scrsel1&"C6",0),FALSE)+2),0)

That's a work of art !

sort see what it's doing...so where does this go - in the indexed cells(main) or in the ladder ?

But do you have a check for each cell? When I got round to it I was going to do the intersect thing with named ranges to see if anywhere in the ladder back or lay sections had been clicked so it would only require 1 line of code. Then use Target.Offset(0,1) to get the odds if the back side is clicked or Offset(0,-1) if it's a lay

I think you're right about the using a range of cells rather than many individual cells in selection change. So long as you can only select one cell with

If Target.Cells.Count > 1 Then Exit Sub

-I'd already implemented the target.offset in my bet routines-

.....yep just tried it - fine. Should shave a nano second of overall processing.....every little helps !


Cheers for the tips Mitch.

tkp

[Mitch]

That's a work of art !

sort see what it's doing...so where does this go - in the indexed cells(main) or in the ladder ?

If I'm understanding the question correctly, the formula I pasted above goes in the ladder, which is the indexed cells in 'Main', if by indexed you mean they are named ranges, please correct me if I'm wrong.

It uses the value of the selection scrollbar to see which row on the BA info to look at, then sees if the odds on the same 'rung' of the ladder are available on that selection and takes the amount from it.

I had to put the MOD part at the start to prevent it using £ amounts as odds available.

If the odds aren't available an error is returned, so the cells are conditionally formatted to white text if the cell contains an error.

For all the mess it looks (and there are other messy things in it too) it does appear to work very fast. You can drag the scrollbar up and down as much as you like and the amounts move instantly, which my original attempt with code couldn't do. That was probably as much to do with inefficient code as anything else though.

Scrollbar retaining focus still pi$$ing me off tho

[tkp]

Cheers Mitch - I still don't really understand it - and probably won't unless I see it...

that it works for you is all that matters.....I'll stick to my code until I figure out what you have done.


Catch you later.

tkp

[a1ehouse]

Firstly - as it's my 1st post - massive thanks to Gary for developing and releasing his software free of charge - absolute star!! OK - so I've jumped on the "ladder" and developed a VBA version without spreadsheet formula or anything like that. Pure VBA!!

Still features to add, but I've knocked this up in a couple of hours and named it "bLadDeR" (bet Ladder). Loads of functions already in (Selection choser, default stake buttons, auto centre the ladder, double/single click on ANY price to place bet), but it proves the potential with Betting Assistant.



What's next:
cancel bet(s) button;
matched bet tracker (thunderfoot - may need some hints there!);
maybe a back and lay/lay and back function;
error handling
more info on available prices
WOM indicator etc.etc.
Potential is only limited by what we think of!!

Can't wait to try it out!!

A1ehouse

[jmorrell]

looks good!!. would you be willing to share it with the rest of us when the rest of the features have been implemented. You'd Be the first the share theres!

nice work

joe

[Dedes]

Nice!

Maybe a Net buttom would be nice to people that trade big amounts. Any help you need just post.

[a1ehouse]

Joe - Don't see why not, if Gary has set the tone, I'll share and share alike!!

Dedes - do you know how this net button would work - i.e. the calculation? Also does anyone know if/how the Matched Bets are stored and if they can be output to Excel (Gary/Thunderfoot)?

[Dedes]

a1ehouse,

The net button would work like this:

If you make a Back bet, for instance of 100£ and only 87,53£ gets matched the net button needs to know this number. Then it could cancel the 100£-87.53£ that weren't matched and lay a bet of 87,53£. This information is on the profit/loss collum but with a minus (its our loss if we lose the bet).

If you make a Lay bet of 100£ everything works the same except the place where you can read the value for the second bet. With a Lay bet the value for the second bet can be read on the profit/loss collum on the selections that makes us win.

About the matched bets, whenever you make a bet via Excel you get a ref of your bet. So, the only thing you need to do is to copy that information to a diferent place to get it stored.

Dedes.

[a1ehouse]

Dedes, being a bit simple, can you post a screenshot with the prices that need to be captured and specify how the calculation works. It's easier for me to program then. It could be striaght forward, but it depends on what cells I need to get the sums from to do the Net calculation and what the action is to take. I have read your post a few times, but a picture paints a 000 words!

An indication of what the bets looked like i.e. matched/pending/unmatched etc, and all cells/columns that are involved in netting up.

Ta

[Dedes]

Do you want a screenshot from BA or from Excel or both?

[a1ehouse]

Just Excel really will do with examples of:
Back and Net Lay
Lay and Net Back

Which columns the data comes from, highlighting where the Net value comes from. Even if it's "dummy" data, so long as I know exactly where it comes from I can write some code to do the sums.

[Dedes]

Ok!

I will do it. Just give some time.

Dedes