Venturing into BA's Excel world

[firlandsfarm]

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]

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]

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]

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.

[firlandsfarm]

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]

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]

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]

Yes I believe simplicity is key too, glad you've got something working. Celebrate the mini-victories! Happy to help!

[ShaunWhite]

… 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]

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.

[firlandsfarm]

OK, have set up two sheets in the Multiple wb one for the Donor sheet and one for the Master sheet. Have configured Guardian so that it automatically scrolls down the list of markets that are ordered in Guardian as Donor Market 1, Master Market 1, Donor Market 2, Master Market 2, … Donor Market X, Master Market X and that works fine. Have my VBA script embedded in both sheets to clear them on change of market … off we go. The screen has an irritating flicker but it seems to be working, bets are being placed . Check the Market Reports and … multiple bets! (not many, 3 or so for a selection instead of just the 1 targeted). I mull this over and awake this morning having thought, the flicker, I wonder if there is an infinitesimal period of null value in cell B2 (the Market cell) that is causing the VBA to think "wait a minute, didn't that cell value just change? I've got work to do clearing everything" and so multiple bets! And then I read Shaun's comment …

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.

… so thanks Shaun, that comment may explain the flicker and the reason for the multiple bets. If so then the Worksheet_Change route is probably a no-no because the worksheet is constantly changing but I have a few thoughts to test so will go off and report back later.

Thanks for your continuing interest guys.

[CallumPerry]

Yeah the flicker will 100% be because you're refreshing rapidly and each time the sheet is refreshing because of the worksheet_change. This may be what that longer code you saw was handling. It wrote a value to cell B2 a single time and then your code checks whether this one cell changes. Only having to check the status of one cell would make things run much smoother, perhaps get rid of the flicker entirely and make your system that little bit tighter (single bets per single triggers). Good luck.

[firlandsfarm]

Yeah the flicker will 100% be because you're refreshing rapidly and each time the sheet is refreshing because of the worksheet_change.

OK, had a rethink of the logic but can't get it to work probably because of my lack of knowledge. I thought … I'm not placing any bets beyond the start time but I let Guardian refresh the wb until 5 seconds after Start Time so how about I clear everything when the Countdown goes negative … simple. Err, no! I thought this would do it …

Sub Clear_Cells()
If Range("f4").Value < 0 Then Range("B9:K68").ClearContents
If Range("f4").Value < 0 Then Range("O9:AE68").ClearContents
End Sub

I might have known that would be too simple. I've tried every variant of "<0" I could think of but to no avail. (BTW in my experimenting I discovered a 'strangeness' in the F4 cell and wondered if that might be it but no.) (F4 shows a negative countdown beyond the start time but Excel left to itself shows negative time values as "#######".) So I thought make [G4]=F4 and format that as a simple number … that's great until it goes negative when the formatting of G4 switched, all by itself, to a (negative) time format! I tried to compare the Time element of Now() with the Start Time in F3 but again no go. What's annoying is that if I run the macro from the marco dialogue box it works fine so it's clearly a lack of a trigger that is preventing it. Any thoughts please.

[firlandsfarm]

OK, I seem to have solved it by sticking with my original code and increasing the refresh time to 1 second, I guess that gives everything the chance to settle down between refreshes. I have had one double bet placed but that may have been because I was fiddling with it! Yeah I know, could be dangerous.

I'm still confused why the code in my last post wouldn't trigger. Any thoughts?

[spreadbetting]

Yeah the flicker will 100% be because you're refreshing rapidly and each time the sheet is refreshing because of the worksheet_change.

OK, had a rethink of the logic but can't get it to work probably because of my lack of knowledge. I thought … I'm not placing any bets beyond the start time but I let Guardian refresh the wb until 5 seconds after Start Time so how about I clear everything when the Countdown goes negative … simple. Err, no! I thought this would do it …

Sub Clear_Cells()
If Range("f4").Value < 0 Then Range("B9:K68").ClearContents
If Range("f4").Value < 0 Then Range("O9:AE68").ClearContents
End Sub

I might have known that would be too simple. I've tried every variant of "<0" I could think of but to no avail. (BTW in my experimenting I discovered a 'strangeness' in the F4 cell and wondered if that might be it but no.) (F4 shows a negative countdown beyond the start time but Excel left to itself shows negative time values as "#######".) So I thought make [G4]=F4 and format that as a simple number … that's great until it goes negative when the formatting of G4 switched, all by itself, to a (negative) time format! I tried to compare the Time element of Now() with the Start Time in F3 but again no go. What's annoying is that if I run the macro from the marco dialogue box it works fine so it's clearly a lack of a trigger that is preventing it. Any thoughts please.

I don't actually use BA but it's possible that when the countdown is negative it's actually displaying the time as a string rather than number, you could always try to use excels ISTEXT function to catch that.

Code: Select all

If WorksheetFunction.IsText(Range("f4").Value)  Then Range("B9:K68").ClearContents

There are plenty of ways you can optimise excel to stop the flickering by turning off screen updating and calculations whilst the code is running and also exiting the sub early if there's no need to run thru the whole code. Mainly you're looking to ensure code only runs when it's needed so after it's run you'd usually set a flag so it doesn't run again til it needs to by resetting that flag. And it does get a lot easier once you start to understand the quirks which aren't as hard as they first appear when you're starting out.