Close All Positions in Excel

Please post any questions regarding the program here.

Moderator: 2020vision

Close All Positions in Excel

Postby rluk » Fri Jul 24, 2020 1:54 am

Hi,

I am looking for a simpler way to "Close All Positions" in Excel.

Currently I use CLOSEC at a certain point before the off. The issue is that the "Bet ref" field is nearly always empty so I need to manually populate it. I do this in one of two ways:

- Method 1: trigger a bet in that won't get matched
- Method 2: VBA code to put a dummy number into the field

I can get both of these to work, but each one has issues. Method 1 uses a lot of trades per hour and Method 2 is pretty clunky and means practically I can only have one market per excel instance.

Am I a bit dim and missing something here and is there a simpler way to either populate the Bef Ref or square up? Essentially all I want to do is press that big green button that says "Close all position" when the countdown clock reaches 5.
rluk
 
Posts: 9
Joined: Wed May 13, 2020 11:44 pm

Re: Close All Positions in Excel

Postby MarkRussell » Fri Jul 24, 2020 11:46 am

Hi,

You can do this without using an Excel trigger.

You can right click the "Close all positions" button above the Grid and put the number of seconds before the off that you want to level up.

A countdown clock will display below the button to show when the level profit bet(s) will be submitted.

You must keep the market open until the Close all positions countdown has reached zero and the bets submitted.

Regards,
Mark
User avatar
MarkRussell
Site Admin
 
Posts: 1787
Joined: Tue Feb 20, 2007 6:38 pm
Location: Birmingham

Re: Close All Positions in Excel

Postby rluk » Fri Jul 24, 2020 12:02 pm

Ahh, I see - wasn't aware of that! That will be very useful.

Just to clarify though, if doing automated trading in Excel I still need to use CLOSEC to trigger a close from Excel? i.e. no command to trigger this function from Excel.
rluk
 
Posts: 9
Joined: Wed May 13, 2020 11:44 pm

Re: Close All Positions in Excel

Postby MarkRussell » Fri Jul 24, 2020 12:38 pm

Hi,

Yes sorry if you are looking to have it fully automated then you will need to use CLOSEC to trigger it from Excel.

Regards,
Mark
User avatar
MarkRussell
Site Admin
 
Posts: 1787
Joined: Tue Feb 20, 2007 6:38 pm
Location: Birmingham

Re: Close All Positions in Excel

Postby Captain Sensible » Fri Jul 24, 2020 2:16 pm

MarkRussell wrote:Hi,

You can do this without using an Excel trigger.

You can right click the "Close all positions" button above the Grid and put the number of seconds before the off that you want to level up.

A countdown clock will display below the button to show when the level profit bet(s) will be submitted.

You must keep the market open until the Close all positions countdown has reached zero and the bets submitted.

Regards,
Mark



Never knew that function existed. Every so often I find a new function attached to Gruss that I didn't know about and I've been using it pretty much since the start.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: Close All Positions in Excel

Postby brumbie » Sat Jul 25, 2020 3:25 am

It's been a long time since I used CLOSEC but digging up an old spreadsheet I found this:

Code: Select all
Sub CLOSEC()
'
' CLOSEC Macro
'

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim rng As Range
  Set rng = Range("Q5:Q55")
  rng.Formula = "=IF(RC[3]="""","" "",""CLOSEC"")"
   
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
   
   
End Sub


Also, although you probably already have it, Mark or Gary I think put this code up for a countdown timer a few years back:

Code: Select all
=IF(LEFT($D$2)<>"-",(HOUR($D$2)*3600)+(MINUTE($D$2)*60)+SECOND($D$2),-((HOUR(SUBSTITUTE($D$2,"-",""))*3600)+(MINUTE(SUBSTITUTE($D$2,"-",""))*60)+SECOND(SUBSTITUTE($D$2,"-",""))))


You could just put it all together on a Worksheet_Change/Worksheet_Calculate sub in your market sheet.
brumbie
 
Posts: 197
Joined: Tue Dec 28, 2010 2:00 am
Location: Brisbane,Australia

Re: Close All Positions in Excel

Postby rluk » Sat Jul 25, 2020 11:43 pm

Cheers, I have something similar, basically when the clock reaches a certain point W1 goes Y and the macro triggers. I wanted to avoid macros though as I am logging prices in the workbook and triggering a macro means I can only practically have one market per workbook. I can do CLOSEC by formula, but it is those pesky Bet Refs I can only seem to fill by macro. As I regularly have unmatched offset bets at the close Col T is often blank.

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Range("AD1") = "Y" Then
Refresh
Else
End If
If Range("W1") = "Y" Then
CloseAll
Else
End If
Application.EnableEvents = True
End Sub



Code: Select all
Sub CloseAll()
Application.ScreenUpdating = False
    Sheets("Market").Select
Range("Q5:Q24").Select
 Selection.ClearContents
    Range("T5:T24").Select
    Selection.ClearContents
    Range("T5").Select
    ActiveCell.FormulaR1C1 = "777"
     Selection.AutoFill Destination:=Range("t5:t24"), Type:=xlFillDefault
    Range("Q5").Select
    ActiveCell.FormulaR1C1 = "CLOSEC"
    Selection.AutoFill Destination:=Range("Q5:Q24"), Type:=xlFillDefault
    Range("Q5").Select
End Sub
rluk
 
Posts: 9
Joined: Wed May 13, 2020 11:44 pm


Return to Help

Who is online

Users browsing this forum: No registered users and 45 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.