WHAT TYPE OF STATUS CLEARING
Sometimes when we go back to worksheets in BETANGEL it can be a muddle especially when we
are dealing with the clearing of status in BETANGEL sheets. My Plan was to have a Macro that automatically
clears the STATUS COLUMN to allow subsequent bets.
The idea was to clear the status at about 28 secs before race start (and have a feature to allow that time to be adjustable to any time one requires) , priming the sheet for whatever Back or Lay bet is employed later.
To ensure no unwanted double bets I made the bets to trigger at a time later than the STATUS CLEARING time used.
Here is what happens when the bets activate at the same time as the clearing of the STATUS indicator-You get double bets - hence the need for time separation.
double_Bets_again.png
DOUBLE BETS AGAIN BELOW- THREE TIMES OVER a 1 HOUR PERIOD
double_Bets_again.png
Another aim was to ensure that no Column headers became deleted (which happened in my previous feeble effort) and that Bet Rule and Command rule option info was retained at Cols L to AE. I am pleased to say that the
macro appears to work well over 2 day testing and it completed both the UK/Irish and US markets without a hitch. The macro was designed around the macros created previously on this forum but bits added also to avoid error messages.
I realise now that to make Macros that work you to have to understand EXACTLY how the worksheet works
in BetAngel Excel otherwise you will perceive macro problems that essentially of your own making. I am laying out the summary of my research in this regard which might help anybody who might struggle with STATUS CLEARING
SETTING UP THE SHEET WITH THE MACRO
The Macro comes in 2 parts the latter triggered by the first. They both are in place in the attached Sheet.
They operate automatically to clear the STATUS indicator 28 secs PRE-START-TIME to free the sheet for a bet
at whatever time condition is employed in the bet formula. I used the Race START-TIME for the bet to trigger
-The Private Sub - Which calls up the Reset_Bet sub at whatever time you designate and can be changed by changing the TIMESERIAL VALUES in the Private Sub for countdown visible at F4 .
If (not "In-Play") is another condition imposed as you may not want to automatically bet in-play. This is a precautionary measure as the timings setup means that should not happen anyway.
Both conditions can be simply changed also by going to developer/Visual Basic and editing.
The Private Sub Macro is accessible to edit in the Sheet 1(Bet Angel) Folder in Developer/Visual Basic/
by rightclicking and "view code" and editing/saving directly if you want to change the timings or change not in_play
which shows as "<>In-play" to in Play which can be revised/Adjusted TO SHOW as "=In-Play".
WARNING: Usually the Module 1 code is automatically visible on developer/Visual Basic entry so that it is ALWAYS necessary to highlight the Sheet 1(Bet Angel) Folder- rightclick/view code in order to access that folders code.
[
- The Reset_Bet Sub- Deletes the Status Data from Col O9 down
The Reset_Bet Sub is accessible to edit either in Macros or again in the Module 1 Folder in Developer/Visual Basic/.
by highlighting Module1 and rightclicking/view code and editing directly but I cannot see any likely changes.
The name of the sheet in the macro must always coincide with the sheetname "BetAngel_1.xlsm" or if you change
the sheets name you must do the same also in the Macro "Reset_Bet" in the editing manner described above.
KNOWING HOW SHEETS OPERATE IN BETANGEL IS ESSENTIAL TO KNOWING IF/WHY YOU HAVE A PROBLEM
A good understanding about how/when/why sheets load in betangel is required if you encounter something
unusual happening so that you avoid the temptation to change things when no change is needed or you think
the Macros are wrong when in fact its something else. Yes I had this problem myself!!!
A knowledge of the expected sequence of events is helpful so that you know where you are at any point.
and I have set out that below as a guide. For proper use of excel it is critical that the sheets bind from race to race
as the races finish.
AUTO BIND/AUTO CLEAR BINDING of BetAngel sheets-Will only bind if
(1) AutoBind/ClearBind are TICKED in Guardian in the excel section.
(2) Your worksheet is connected (preferably from the same folder for good housekeeping as the betangel.exe file for the currently running BA as you may wish to use different Macros with different timings for different strategies)
(3) The refresh rate is set to be currently active (recommend 1/2 hour before/After race start should guarantee binding as some races arise 30 mins apart (set at 1800 sec before and 1800 sec after) or the refresh rate restriction unticked which makes it always active.
(4) The previous race has finished,
(5) "in-Play" drops from the Guardian screen and the worksheet simultaneously (usually within Mins of race end)
then the Bet angel sheet, provided (1) to (4) are in place, will "bind to/make active" the next market and the new racecard then populates the worksheet. The previous report of bets made (n Col L) is also overwritten together with the other details of the bets matched,amounts etc.(in Cols P to AE)
STATUS "PLACED" BET BLOCKER REMAINS
Critically the only thing that still remains from the previous race is the STATUS which usually states "Placed". No Bets can be made for the new race until the old Status indicator is either deleted by highlight/delete in excel proper or automatically from a macro or manually by Macro using a designated shortcut key.( Ctrl v is used in the sheet)
BTW when assigning a new macro key in Developer/Macro/options ensure the sheet is saved to retain the Shortcut Key.
The BINDING HAS MOVED TO A NEW RACE AND THE GLOBAL STATUS "PLACED REMAINS"
The "STATUS" CARRY of course is a security measure for users to ensure bets are not fired unintentionally.
However be careful as it may occur that the previous race has only say 8 runners to say 10 in the current
race. The carried forward STATUS blockers will only be 8 in number potentially allowing the remaining 2 runners
to have their bets placed immediately even up to 30 mins before the race depending on your timings for the macros
and the BACK or LAY bets.. The market may not be properly formed at that time (in US Races particularly) and therefore early bets may be a disaster. That is another reason why bets have to be delayed in the Bet Formula.
THE STATUS "Placed" is cleared at the designated time 28 secs from the OFF ready for the bet to trigger at OFF.
HOW TO DELAY YOUR ACTUAL BET FOR A TIME AFTER THE "STATUS INDICATOR" IS REMOVED
When the "PLACED" from the previous race is deleted you will know you are in the Bets allowed phase
because the bet report info is no longer in Col L as shown below.
Col L should until normal circumstances be blank without any report of bets actually made.
However occasionally "Back" or " Lay" is visible when it is no longer possible to process a bet as the designated time has passed beyond the bet formula timing. This rare event is explained later where a manual intervention arises.
THE BETS ARE PLACED AT THE "OFF" TIME
In my opinion the timing separation part of the Macro and later the Bet timing itself are very important features as they allows 1 only bet to be placed at a time after the "Placed" blocker has been removed where multiple bets might otherwise occur if both the STATUS indicator removal and the bet placement were to happen at the same time.
HOW TO AVOID BETS BEING PLACED AT THE STATUS REMOVAL TIME.
Within the bet formula itself you could use $C$3>$F$3 to represent the current time is Greater (later) than the Start time ensuring bets are only placed at start time which in this setup is about 28 seconds after the "STATUS" indicator.
Also using AB10<>1 in the bet formula ensures no further bet when one bet is already placed although it
should be unnecessary as a new STATUS " "Placed" indicator should do the trick. Lets call it a security backup.
USE FORMULAS IN YOUR BACK /LAY BET and the PRICE and STAKE to avoid being part of any DATA clearout
Altogether I used =IF(AND(AND(H9>0,AB10<>1,$C$3>$F$3)),"LAY","") to execute the LAY BET.
For the price/odds I used =IF(i9=0,"",i9) and for stake I used =IF(H9=0,"",$AE$1/H9) in AE1 +50+50 was entered.
Note that my sample betting by Laying the Field by Book % is not a viable strategy except maybe when some floor
and ceiling limits are applied which is not done here and should be used only in practice mode.
BINDING IS ESSENTIALLY BETFAIR GENERATED
Binding is dependent time-wise on when exactly an event ceases and is closed by betfair and is not exact time predictible for that reason. You can only therefore generate the Status remover relative to the current race time.
The changing of the market is generated by Betfair and is dictated largely by when an event finishes and presumably related to market settlement time which is an unknown quantity.
MARKET SWITCH TIME
Is exclusively for Automation and the timing of the switch to a one click screen window.
It does not in any way relate to the new market generation in excel which as explained is sheet re-binding dependent.
FURTHER NOTE ABOUT REFRESH RATE TIMING
If you untick Refresh Rate then it cannot cause a problem as regards status clearing.
However different refresh rates are required for different strategies. Typically a pre-race trader will have the refresh rates set in a short span so that they will have that market continually refreshing at a fast rate so that the most
up to date "prices" and "available to bet" amounts and "volume" can be seen. For traders the refresh rate
operating near to a race will cause the market only to bind near the race time.
If on the other hand Pre-Race bettors would likely have no restriction on the refresh rate by having it unticked
or have the span for restriction at 1800 secs before/1800 secs after the race.
POSSIBLE MANUAL INTERVENTION TO SORT A NATURALLY OCCURING "LATE RACE" PROBLEM
Sometimes it occurs that a race is so late that its finish/settlement time is near or may overrun the start of the next race. If its time is run after 28 secs before the new race start (or whatever timing your using) the situation might be retrieved manually if you can in guardian/excel sheet col, remove the "Bet Angel" by going to the blank area at the top and then in the upcoming race you wish to bet on,apply "BetAngel" to manually force a binding .
WHEN RETRIEVAL TIME IS MORE THAN 28 SECOND BEFORE OFF-TIME
Then use your Macro ctrl v shortcut to clear the status before the start time and the bet timed for OFFTIME should occur as normal. In fact you can manually force a bind for any future race you like passing over any interceding races.
If a race is late it can often affect a passover of a number of races in succession as usually a rebinding will occur only at the end of the current active race.
RETRIEVE AFTER 28 SEC BUT BEFORE OFF-TIME
If the current active race is not yet off and you are anxious to get on then the following procedure is recommended provided you have the time to deploy.
(1) Move the "Bet angel" from the old race to the required race manually as expained(It will take a few seconds max)
(2) Copy the adjusted bet with no timing at AE2 to L9 and then copy from L9 to L11,L13,L15 etc down to the last bet space in that column adjacent to a runner.
(3) The bets will place as you copy. Without saving the sheet ,In guardian/Excel close the workbook and Browse
to find and open the file "BetAngel_1.xlsm". Click open again in Guardian/excel and tick connect. The
original formulas should then be restored.
I appreciate that for many my post will be mind numbingly boring, for others it will be hilarious
and for a few of my own ilk it might be useful. To all but the last I apologise.
Question : What were you doing during the lockdown? Answer : "Status Clearing"!!
-
You do not have the required permissions to view the files attached to this post.