Venturing into BA's Excel world

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
firlandsfarm
Posts: 1296
Joined: Sat May 03, 2014 8:20 am

In the past I've looked at using Excel but never ventured but as I consider myself a reasonably proficient user of Excel (though never entered the world of VBA) I've decided to give it a go mainly because I have a couple of projects that I can't see a way to achieve using Automation. But, I have hit a few 'issues'!

I am sequencing through the day's races. I have set Guardian to update Excel from 200 seconds before the scheduled event start until 1 second before the scheduled event start but I am finding that when switching from one market to the next it seems to scroll through the market list in Guardian as if it is searching for markets to update. The worksheet is not updated other than to show it is looking at a different market … is it searching for a market to update or is this not expected?

When it switches to another market it clears the (coloured) matched/unmatched bets cells but does not clear the green "Status" cells from the previous market (nor the white bet details cells) and as this means there is 'text' in the Status cells it does not fire bets into the new market! :( This means I am having to monitor Excel when it switches markets and clear the Status cells.

I have set the Global Command cell (L6) to "TAKE_SP_ALL" but when the bet is placed in the market the Persistence Flag assumes the default of "Cancel". The value of cell L6 is just a static value do I need to do more?

With one project I want to compare two markets related to the same event and can set up SheetA and SheetB in a multiple markets workbook no problem but I then want to scroll through a day's schedule of events. I could copy the pairs of sheets within the workbook (A1, B1, A2, B2, … etc) and bind them … this would overcome the lack of clearing of the Status cells mentioned above. Or I could open two instances of BA and two instances of Excel each with a single market sheet, bound to each instance of BA (SheetA to one and SheetB the other) and link the Excel sheets for the comparison. The two BA/Excel combinations could then scroll through the day's schedule of events but the problem of clearing the Status cells would need to be overcome.

All thoughts/suggestions gratefully received.
CallumPerry
Posts: 507
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Some simple VBA to get you started. Google IF commands. Basically: IF (Name of event) has changed Then (Cells you want to clear).ClearContents. That should take no more than 30 minutes reading to figure out and you'll have officially started. Half an hour a day learning VBA is something I recommend - even if it's just from now until Christmas. You'll be surprised how quick you understand the basics.

I would then - you can do this just using normal formulae within the sheets - have some formula that 'identifies' whether this market is an A market or a B market. For example, with regard to football, I would have a bit of code look at the name of the selections. If B13 = "Draw" you're displaying the Match Odds market. If B9 = 0-0, you're in the correct score market. You may want to add more checks to be sure i.e. B9="0-0", B11 = "1-0", B13 = "2-0" - I think that's the order they go off the top of my head. Hopefully you get the idea.

Then you'd have formula that looks at this IF statement and think "Oh, I'm in the Match Odds market. I better use my predefined Match Odds triggers"
firlandsfarm
Posts: 1296
Joined: Sat May 03, 2014 8:20 am

CallumPerry wrote:
Wed Nov 13, 2019 9:51 am
IF (Name of event) has changed Then (Cells you want to clear).ClearContents.
Thanks for your reply Callum. Yep, I understand that, I used to code in BASIC at the start of the home computer invasion. :) I cobble together code understandings as and when I need them (SQL, the more exotic Excel and Access functions etc.), maybe this is a 'need them' moment. :) It's just the getting started bit that's put me off a bit, never had to DIM things and the like in the past. I'll find a vid to watch.

I understand your thinking on the second half but that's not what I'm doing :( Sorry. I'm not betting in/on This and/or That markets. I'm betting in This if f(This) = f(That) so I need both sheets/books open at the same time to monitor f(This) and f(That). To take your football analogy (and this may be a nonsense example but just to illustrate) "if the odds for [Correct Score]>X and the odds for [Home win]>Y then place bet on [an outcome]". (I hope that makes sense!)
CallumPerry
Posts: 507
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

firlandsfarm wrote:
Wed Nov 13, 2019 11:33 am
CallumPerry wrote:
Wed Nov 13, 2019 9:51 am
IF (Name of event) has changed Then (Cells you want to clear).ClearContents.
Thanks for your reply Callum. Yep, I understand that, I used to code in BASIC at the start of the home computer invasion. :) I cobble together code understandings as and when I need them (SQL, the more exotic Excel and Access functions etc.), maybe this is a 'need them' moment. :) It's just the getting started bit that's put me off a bit, never had to DIM things and the like in the past. I'll find a vid to watch.

I understand your thinking on the second half but that's not what I'm doing :( Sorry. I'm not betting in/on This and/or That markets. I'm betting in This if f(This) = f(That) so I need both sheets/books open at the same time to monitor f(This) and f(That). To take your football analogy (and this may be a nonsense example but just to illustrate) "if the odds for [Correct Score]>X and the odds for [Home win]>Y then place bet on [an outcome]". (I hope that makes sense!)
Firlandsfarm Exp.xlsx
I've attached a VERY basic example of how this could work. Note how the formulae is the same on both sheets but only one shows TRUE, this means you would only fire one Back bet per event. If you bound these sheets using Guardian you'd add in an extra IF (Same event name) statement obviously.

In my example, instead of having 'Trigger 7' and 'Trigger G' showing, this would be:
'Trigger 7' = IF[Correct_Score]>X Then TRUE.
'Trigger G' = IF[Home_Win]>Y Then TRUE.

As for DIM'ing things, when things aren't too complicated, I was told that type declaration in VBA is not particularly strict so just Dim everything as Variant. This at the very least picks up typos on the variable names. First thing you need to do is think clearly about how you are going to set out your workbook so that it does all the work for you.
You do not have the required permissions to view the files attached to this post.
firlandsfarm
Posts: 1296
Joined: Sat May 03, 2014 8:20 am

CallumPerry wrote:
Wed Nov 13, 2019 2:56 pm
I've attached a VERY basic example of how this could work. Note how the formulae is the same on both sheets but only one shows TRUE, this means you would only fire one Back bet per event. If you bound these sheets using Guardian you'd add in an extra IF (Same event name) statement obviously.

In my example, instead of having 'Trigger 7' and 'Trigger G' showing, this would be:
'Trigger 7' = IF[Correct_Score]>X Then TRUE.
'Trigger G' = IF[Home_Win]>Y Then TRUE.
Hi again Callum and thanks for your continued help but … I'm sorry but I can't see what your example sheet is trying to show! :( I am more than happy controlling my sheets by simple IF(cell=value, [do this], [else do something else]) and have all that working but with me having to monitor and clear the Status and market odds cells manually at the moment so that I can move on to the next event. I liked your suggestion of using a VBA control that "if market changes clear the cells" and am going to pursue that or is your example introducing another (better) way to do it? For clarity I would confirm that I only want to place one bet for each 'Runner' that meets the criteria in a brief 10 second inspection window before moving on to the next event. I'm not looking at a multi-trading BOT.
CallumPerry
Posts: 507
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

In that case just focus on one thing at a time then, and if you need any additional help with anything specific after this, just post again in here or a new thread or PM me!

A single bet per several runners should be easy to implement. My latest suggestion was just a way of creating a dynamic sheet that sorted itself out when auto-binded through Guardian. Before you get to that stage however, just look at VBA If-Then-Else statements, this should be all you need to get a Version 1 of your system off the ground.
firlandsfarm
Posts: 1296
Joined: Sat May 03, 2014 8:20 am

CallumPerry wrote:
Wed Nov 13, 2019 9:51 am
IF (Name of event) has changed Then (Cells you want to clear).ClearContents.
Hi again Callum … I literally Googled your phrase and found many examples … some short and sweet and some seemingly long and complicated … I believe in KISS so adopted one of the shorter ones, adapted to suit and came up with this this …

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
Range("B9:K68").ClearContents
Range("O9:AE68").ClearContents
End If
End Sub

(Credit and thanks to: https://www.extendoffice.com/documents/ ... anges.html)

… I am left with no understanding as to why some examples code sets are 3 or 4 times longer, I assume they contain some form of 'trapping' facilities and/or are taking a more convoluted route … one copied and saved to another cell the value of the monitored cell and then compared the value in the monitored cell with the copied cell to identify a change. Seems a bit long winded to me if there are readymade code functions that perform the same task.

Anyway, all is working in manual trialling so off to find some Practice mode events to try it one.

Thanks for all your help but … I'm sure I will be back with more questions! :)
CallumPerry
Posts: 507
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Yes I believe simplicity is key too, glad you've got something working. Celebrate the mini-victories! Happy to help!
User avatar
ShaunWhite
Posts: 6188
Joined: Sat Sep 03, 2016 3:42 am

firlandsfarm wrote:
Thu Nov 14, 2019 10:22 am
… I am left with no understanding as to why some examples code sets are 3 or 4 times longer, I assume they contain some form of 'trapping' facilities and/or are taking a more convoluted route … one copied and saved to another cell the value of the monitored cell and then compared the value in the monitored cell with the copied cell to identify a change. Seems a bit long winded to me if there are readymade code functions that perform the same task.
Glad you're making progress.
The longer version of the code (and hard to comment having not seen it) sounds like it's trying to be more efficient. Worksheet_Change works fine, but it's a bit over enthusiastic because it sticks it's nose in whenever anything anywhere changes, for each change. A cell updates and Worksheet_Change pops its head up and asks if there's anything you need doing. Having more control over that by testing conditions via a timer or with your own 'is this cell now different to that cell' code can/might/should be faster, but don't worry too much about that at this stage..... But hopefully that explains the sort of reasons why there's often several ways to skin the same cat.
firlandsfarm
Posts: 1296
Joined: Sat May 03, 2014 8:20 am

Hmmm, hit a snag. I have two single market workbooks, let's say one is the Master workbook and the other the Donor workbook. I open two instances of BA, one connected to the Master wb and the other to the Donor wb. And let's also say the intention is that the Master wb reads the odds from the Donor wb in real time, does some jiggery-pokery comparisons and decides if to place some bets.

I tested the workbooks without any BA involvement and manual data entry to the Donor wb and the odds are immediately read into Master wb … so all fine there. But … if I open the Donor wb through BA (just open it, I don't 'connect' it) the Master wb no longer reads from the Donor wb. :( Interestingly if I try to reference a cell in the Donor wb to the Master wb using the cursor directly on screen it will not do so. It's as if when the workbook is opened by BA it blocks all other attempts to connect and nothing else can communicate with it. Does Excel only allow one gateway at a time?

So I'm now going to try and use the Multiple Sheet wb.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”