Auto clearing status cells

Example spreadsheets and comments on example spreadsheets.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Hi Callum

You could monitor column O to see if the bet has been placed, then clear the relevent row in col L. Something along the lines of

Code: Select all

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 15 And Target = "Placed" Then

rn = Target.Row

Cells(rn, 12) = ""

Target = ""

End If

End Sub
The above will clear both the report and status cells. Change to suit. Don't forget to disable/enable events if needed

Cheers, Nigel.
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Cheers Nigel but when the first bet is placed the entire formula in L9 disappears preventing me from firing more bets, I tried getting around this by pasting my formula back into the code between the quotation marks instead of just having "" but it displays a compile error, "expected end of statement". How would you get around this?

Say for example for horse 1: L9 shows =IF(A1=1,B1=1,C1=1,1,0) {Just a made up trigger instruction} but then on the second horse the code is =IF(A2=1,B2=1,C2=1,1,0). Because the cells all drop one row each time can you lock in VBA with $ or do I go about this some other way? I have 6 'brands' in total i.e. 6 horses/greyhound or whatever. So I need a code that will wipe column O when placed but redisplay the same (different on each row) trigger formula immediately afterwards and what about when L9 shows back briefly but disappears before the order can be processed?

:lol: It's funny isn't it how you think of the simplest of strategies that you think will take 10 minutes to create and then there are so many other variables that appear that you didn't even think of before!

Callum :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

If you want to fill a range of cells 'en-masse' this will be ok, and fill all the cells with the correct formula

Code: Select all

Range("D2:D10").Formula = "=IF(AND(A2=1,B2=1,C2=1),1,0)"
If you need to step through them and only fill every other cell, this will fail because it will always reference the original cells, ie, A2,B2,C2

Code: Select all

For x = 2 To 10 Step 2

Cells(x, 4).Formula = "=IF(AND(A2=1,B2=1,C2=1),1,0)"

Next
You have to use the R1C1 notation to get the correct formulas in each cell: (Just type your formula in as normal, then switch to R1C1 and copy the formula from the formula bar into your code).

Code: Select all

For x = 2 To 10 Step 2

Cells(x, 4).FormulaR1C1 = "=IF(AND(RC[-3]=1,RC[-2]=1,RC[-1]=1),1,0)"

Next
The 'expected end of statement' probably appears because you could have something like:

Code: Select all

Range("D2").Formula = "=IF(A2=1,"Back","")"
But if you have anything in quotation marks in the formula, they themselves have to be within quotation marks (real pain)

Code: Select all

Range("D2").Formula = "=IF(A2=1,""Back"","""")"
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

what about when L9 shows back briefly but disappears before the order can be processed
If you want it to stay there you'll have to code it in, if it's formula based it might only be there for a fraction of a second before your formulas change value.

Say I've added up several different formula and the result appears in F2, so I want to put the word "Back" in the command column. (F2 could just as well contain a total figure rather than a word).

Code: Select all

Private Sub Worksheet_Calculate()

If Range("F2") = "Back" Then
Range("F2").Offset(0, -5) = "Back"
End If

End Sub
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Thank you for such a detailed reply Nigel! I'm going to look into this - as I'm quite new to coding it may take me some time aha - but I should have plenty to go off with your reply. Thank you again!
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

CallumPerry wrote:
Mon Dec 11, 2017 10:55 am
It's funny isn't it how you think of the simplest of strategies that you think will take 10 minutes to create and then there are so many other variables that appear that you didn't even think of before!
Only just found this thread (don't know why I've missed it before as it's a subject I have played with) and fully agree. I get "what if" possible strategies spinning around in my head all day, most don't work but the time is in the proving of them!
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

I had the problem of the data clearing repeating up to 5 minutes which was annoying.
I imagine that some may only wish to clear the DATA while retaining the formulas in the
Sheet and have that occur ONCE or TWICE at most. I managed to do that by Plagurising & Cannibalising some of the stuff posted here.
1. Formula for A1 sheet2 =IF(AND('Bet Angel'!AK5=0,'Bet Angel'!G1="In-play"),"RESET REQUIRED","")
2. AK5 Formula…. =PRODUCT(AK9:AK67)
3. Each of the cells AK9,AK11…AK67….. =IF(AND($G9>0,G9<1.05),0,1)
The above (2) & (3) catches the winner when 1.05 to 1.01 and assigns a “0” but
Quickly reverts to 1 and the Macro stops as the race finishes aaand 1.01 goes to ””
The zero product rule makes AK5 =0 and triggers “RESET REQUIRED” which in turn triggers the
MACRO if the following sub is first placed in Sheet2
_______________________________________________________
Private Sub Worksheet_Calculate()
If Range("A1") = "RESET REQUIRED" Then Call ClearAllButFormulas
End Sub
________________________________________________________
And The Macro ……ClearAllButFormulas() shown below is Placed in Module 1
Sub ClearAllButFormulas()
Dim wks As Worksheet

'ignore errors in case there is only formulas
On Error Resume Next
For Each wks In Worksheets
wks.Cells.SpecialCells(xlCellTypeConstants).ClearContents
Next
On Error GoTo 0
Set wks = Nothing
End Sub
Sometimes of course in Photofinishes t.01 or 1.05 remains and
Assigning a shortcut key Ctrl (Whatever character u like) offers a Manual solution
Anybody any better solution? To entirely automate?
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Be sure not to use this in Live mode unless you have your back bets limited because of the possibility of more bets firing before the end time
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi,
The attached sheet will clear any data or status cell stuff carried from the previous sheet. Found it was too dangerous to do so at end of previous race at Combination of "In-play" & "Suspended" as clearing of status cell would sometimes fire off bets.Also the status cell entries would recur anyway even with an initial clearing.
This does the final clearing of Data while retaining any user formulas in the sheet at the OFF TIME and runs for a few seconds only to avoid the juddering and inacessibility of the sheet while the macro would loop. Its set to run for 3 secs before off to avoid being missed in time.
On busy days the sheets will barely changeover by the off time.Its easily adjusted in the macro as so "F4" time before off and the length of its time span.
The "Back" and "stake" betting needs to be done by formula to avoid the clearout. For fixed stake for instance a 10 bet can be referenced from a fixed cell according to the formula 5+5.A sample back bet is included for reference.
It shows what can be done by persistent googling and would recommend to tackle VBA as a newbie in this way as the basics can be learned in this manner and the VBA barrier is broken down. Hundreds of "compile errors" later. :D
Copy of BetAngel_Clear_Status(AT_OFFTIME_preplay).zip
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi,
There a macro there also with Shortcut Ctrl k that does the same thing manually.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Hi grindhog this may well do exactly what I have been attempting to do with no VBA knowledge and not enough time! :) I will check it over later ... I just wanted to say thanks. :)
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi Firlandsfarm,
I hope its what you needed. I doubt if its an improvement on any of the other versions presented in this post and othe rposts but it was done for m own specific requirement and I would be delighted if it was of some use to you or others. It was only when i could read some vba script that I realised in fact their coding was beyond my limited capability and I could not understand it.
I reduced the time span in the sub to 2 seconds and would advise the same for others to avoid double bets.The delay in the market closing by betfair is a small problem but can be manually overcome by closing the excel "Bet Angel" in guardian and opening same in the new market. I was wondering if you were aware of any way that the market sheet can switch automatically as it does in automation with the automated market switch time of the markets area.
Regards
Grindhog
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

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.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi,
looks like my post got sent without most of the images and the sheet. I am going to attempt to load them here!!
THE PRIVATE SUB IMAGE
Grindhog
PRIVATE_SUB.png
Reset_bet_2020-06-13 231037.png
THE RESET_BET IMAGE
Reset_bet_2020-06-13 231037.png
STATUS_BLOCKER_IN_PLACE.png
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi Main macr0_Status_clearing sheet to attach
BetAngel_Multiple.xls
This FILE HAS TO SAVED AS AN XSLM TO ENABLE THE MACRO AS WRITTEN TO WORK. OTHERWISE CHANGE THE SHEET NAME IN
RESET_BET FROM BetAngel_1.xlsm to BetAngel_1_xls
You do not have the required permissions to view the files attached to this post.
Post Reply

Return to “Bet Angel - Example spreadsheets”