Bet Angel - Example spreadsheets : Clearing the status cells in Excel

Example spreadsheets and comments on example spreadsheets.
Post Reply
6thSense
Bet Angel
Bet Angel
Posts: 375
Joined: Fri Nov 30, 2012 5:02 pm

Thu Dec 20, 2012 3:12 pm

Clearing The Status Cells

I thought I would post up a spreadsheet that automatically does what a lot of people want - clear the status field once a bet has been placed.

There are a few ways of doing this but I am going to go down the VBA route and tie this with a reset flag.

In a cell somewhere on your worksheet, say sheet 2, cell A1 (never use the Bet Angel worksheet for calculations) type in the following:

Code: Select all

=IF(COUNTIF('Bet Angel'!O6:O67,"")<61,"RESET REQUIRED","")
This means the cell will display 'RESET REQUIRED' if any cell from O6 through to O67 is populated with anything.

You can then create a Sub in a module to clear the cells. Use this code:

Code: Select all

Sub Reset_Bet()
    
    Application.ScreenUpdating = False

    Sheets("Bet Angel").Select

Range("O6,O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47,O49,O51,O53,O55,O57,O59,O61,O63,O65,O67").Activate

    Selection.ClearContents
    
    Range("A1").Select
         
    Application.ScreenUpdating = True

End Sub
Then call this code whenever something the reset flag is set. This can be done by entering the following in the Sheet2 Object

Code: Select all

Private Sub Worksheet_Calculate()

If Range("A1") = "RESET REQUIRED" Then Call Reset_Bet

End Sub
This will clear the cells whenever there is a calculation made on the sheet. And when connected to a Bet Angel market, Excel is effectively automatically performing calculations constantly.

The attached sheet has all the above. Connect it to Bet Angel and try it out.
You do not have the required permissions to view the files attached to this post.

PeterLe
Posts: 3313
Joined: Wed Apr 15, 2009 3:19 pm

Thu Dec 20, 2012 6:26 pm

Hi
Just a friendly word of warning..
unless you have carefully designed your spreadsheet you could end up submitting many bets into the market...
In my early days I remember sitting in Dublin Airport connected to my VPS and watching about 600 bets (pre race) being submitted.. :o
I was the mad bomber that day and ended up pushing the price down!
I quickly greened up and made about £70 I recall...

Just be careful (or move most of your money into your Aus wallet whilst you test...)
Regards
Peter

6thSense
Bet Angel
Bet Angel
Posts: 375
Joined: Fri Nov 30, 2012 5:02 pm

Thu Dec 20, 2012 7:10 pm

Good point.

This spreadsheet has the capability to cancel the status cells many times per second whilst connected to Bet Angel.

So to anyone who wants to use this method you need to make sure your bet triggers need to be written in such a way to take this into account.

Corine
Posts: 11
Joined: Thu Sep 27, 2012 12:50 am

Sun Apr 28, 2013 6:15 pm

I have used this while testing but it does fire lots of bets into the market on whatever I trigger a bet on. Is there any way to modify the sheet so as it will only fire the one bet into the market and then stop altogether.
Thanks in advance

Corine

Wildefish
Posts: 2
Joined: Tue Dec 17, 2013 5:55 pm

Mon Dec 23, 2013 12:55 am

6thsense,

Many thanks for this and I can get it to work on the first Bet Angel Sheet. As I know nothing about VB, what would I have to change to to enable it to do the same for multiple sheets ("Bet Angel (2)" & "Bet Angel (3)")?
I placed the macros for these in sheet 2 in A2 and A3 and I can get the "reset required" to appear.

I tried experimenting with VB and guessing how to add 2 and 3, but it didnt work.

Cheers

Rob

Systemat
Posts: 2
Joined: Tue May 27, 2014 12:55 am

Mon Jan 12, 2015 7:43 pm

Hi,

I have been playing around with the BetAngel_Clear_Status.xls 6thSense produced but like Wildefish I've been unable to replicate the clear status cell results simultaneously across multiple worksheets. If anyone has any advice on how to amend the code above to achieve this I’d be really interested to hear your thoughts.

Thanks


Systemat
Posts: 2
Joined: Tue May 27, 2014 12:55 am

Wed Jan 14, 2015 3:44 pm

Thank you very much nigelk, this macro solves my issue.

calvinkbee
Posts: 11
Joined: Sat Jul 29, 2017 2:24 am

Tue Dec 26, 2017 7:02 pm

Thank you for posting this, it is really useful :)
One question, is it possible to alter the rate at which this will refresh i.e. to only refresh once every 2 seconds?
Thanks in advance

Panrich
Posts: 2
Joined: Wed Feb 14, 2018 3:54 pm

Sun Feb 18, 2018 12:28 am

Hi all.

New poster here and a new convert to BetAngel.

It's great to see the possibilities and have been looking at all the videos and looking at suggestions like those from this thread.

I created a spreadsheet last night that imports golf scores from MSN and then displays those beside the betfair price of the golfers in sheet 2 just as a test to see what can be done. It's not really a viable candidate for any serious betting as the MSN updates are waaaay too slow.

However, I am using Office 365 and have noticed a very strange anomaly that I cannot see reported anywhere else. Every time I start to enter a formula in any cell in sheet2 or any other sheet the data from sheet 1 populates in the appropriate cells. So just typing an = sign is enough to get all the data replicated in sheet 2 (without the formatting). I'm not sure if i am missing something obvious or if it's an issue with my version of excel, but the suggestion from post 1 here seems to indicate that it should be possible to turn this behaviour off somewhere. I've had this on both my win 10 tablet and my laptop.

Post Reply

Return to “Bet Angel - Example spreadsheets”

  • Information
  • Who is online

    Users browsing this forum: No registered users and 2 guests