Excel Help

[Freefall]

Generally I use gruss for trading but I would to have a go at automating a few systems to run while I am at work.

How do I import multiple races into excel? I have just downloaded the alpha program so that might come in handy but I would like to know if you can do this in excel.

http://gruss-software.co.uk/forum/viewtopic.php?t=1659

I already have an advanced sheet which calculates my progressive staking plan and safe trade values depending on my balance.
I have a system where I bet on horses below odds of 2 as long as a few other criteria are met. The problem is, if one doesn't win then the stake becomes progressive to allow me to make my desired profit and also regain my previous loss.

Do I have to manually link all open tabs to a separate sheet, then leave the tabs open so the sheet will bet to the desired criteria?
I could then link the sheets together to make it progressive, but as I have not done it before and the help file is sparse then i'm not sure i want to risk my bank. I have got visions of one incorrect formula and bang a hundred £2.00 bets are placed, is there any safeguards against this?

Is there any posts which easily explain how to use excel with gruss? I am very good on excel but its my first time linking it with gruss.

Help!

[Freefall]

The auto bet feature will not work, I need to look at all runners and only bet on the favorite as long as its not a joint favorite.
I can only do it in excel, but i still not 100% how to get it going.

1.I select around 8 possible races for the day which fit my criteria
2.I monitor the favorites 1 to 3 minutes before the off and bet if they are below odds of 2 and the nearest horse is not withing a 1.00 point on the odds.
3.If the horse loses I do the same thing until I achieve a win using my progressive staking plan, as soon as 1 of my selection wins I stop.

I have roughed idea of the formulas i need to do it.
I am stuck on getting the right data in from gruss, then how to get the correct amount bets out.

[Freefall]

Incase anyone else is interested in an example excel guide:

It seems this was made for an older version of BA but it will be useful to examine the formulas which have been used.

http://www.ukhorseracing.co.uk/tools/BetfairGrussTraderGuide.asp

[barnov]

Hi Freefall, I'm slightly nervous replying, as like you I'm a novice in VBA and I'm sure my code could be much better, but hopefully a starter for 10. By by trawling the site and asking questions, I did mange to run a trigger which was based on balance/profit target.

Try the following as a sample - it's not great but it's a start. My code assumes and is based on the following idea:-

1) Firstly you check to see if the market has changed (code cribbed from threads on this board)
2) you will use tha range Z5 onwards for you own excel calculations
3) I use cells Z2:E2 as follows :-
Z2 - enter start balance manually
AA2 - enter my profit target manually
AB2 - excel sum to add previous cells together
AC2-AE2 - used to update the balance available, any exposure and any shortfall I still have based on my profit target

4) a non-zero value in each cell in array AD5:AD50 (this is wher perhaps you excel calculations kick in) indicates that you want to submit a bet, and you'd need to work out where you want to take your input from and amend the missing values in the submit bet routine

5) once a bet has been submitted it won't submit another during the same event

Code: Select all

' Declare Variables

Private MyMarket As String
Private RefreshCount As Long

Private BalLookup As Boolean
Private BalAvail As Double
Private BalExposure As Double

Private SubmitBetCount As Long

Dim WithEvents ba As BettingAssistantCom.ComClass

Private Sub worksheet_change(ByVal Target As Range)
       
 Application.EnableEvents = False
 Application.Calculation = xlCalculationManual
   
 Select Case Target.Columns.Count
   Case 16 'odds and market info update
     
     Call Market_Change 'check for new market and track refreshes
     
     If RefreshCount > 3 Then
     
        If SubmitBetCount > 0 Then GoTo LeaveRoutine
        Call Lookup_Balance
          If BalLookup = True Then GoTo LeaveRoutine
        Call Submit_Bet
     End If
                             
   Case 5 'bets update
   Case 1 'refresh update
   Case 8 'bet column headers update
   Case Else
 End Select

LeaveRoutine: 'simple marker
 Application.EnableEvents = True
 Application.Calculation = xlCalculationAutomatic

End Sub
Private Sub Market_Change()

 'check to see if market has changed
 If Worksheets("wsBA").Range("A1").Value = MyMarket Then
    RefreshCount = RefreshCount + 1 'increase counter for each refresh
 Else
    MyMarket = Worksheets("wsBA").Range("A1").Value 'update MyMarket variable
   
    ' reset counters used elsewhere in routines
    RefreshCount = 1
    SubmitBetCount = 0
   
    'clear contents of various ranges
    Worksheets("wsBA").Range("Q5:X50").ClearContents
    Worksheets("wsBA").Range("AC2:AE2").ClearContents
       
 End If
       
End Sub
Private Sub Lookup_Balance()

startBal = Worksheets("wsBA").Range("Z2").Value 'define value - manual cell input

If ba Is Nothing Then
   Set ba = New BettingAssistantCom.ComClass
End If
     
Set Bal = ba.getBalance(1)
   
BalAvail = Bal.availbalance 'set BalAvail value based on COM input
Worksheets("wsba").Range("AC2").Value = BalAvail 'record in target cell
BalExposure = Bal.exposure 'set BalExposure value based on COM input
Worksheets("wsba").Range("AD2").Value = BalExposure 'record in target cell
Shortfall = Worksheets("wsBA").Range("AB2").Value - BalAvail
Worksheets("wsBA").Range("AE2").Value = Shortfall
       
If BalAvail > startBal Then
   BalLookup = True
Else
   BalLookup = False
End If
 
End Sub

Private Sub Submit_Bet()

For Each bcell In Worksheets("wsba").Range("AD5:AD50")
    If bcell.Value <> "" Then
       bcell.Offset(0, -13).Value = "BACK"
       bcell.Offset(0, -12).Value =
       bcell.Offset(0, -11).Value =
     End If
Next bcell

SubmitBetCount = SubmitBetCount + 1

End Sub

The code should be inserted in the worksheet in VB Editor. Also you will need to set up BA COM (best to review help file 1st).

Would suggest that you omit the submit bet part of the code initailly until you're happy that all info and calculations are being actioned correctly, then consider the submit bet code (just my personal opinon)

Paul

[barnov]

sorry should have added a little info about your need to submit bet x secs/mins beore the off

again I pick a particular cell to keep track of time e.g. cell AF2 which contains the following formula

=IF(LEFT(D2)<>"-",(HOUR(D2)*3600)+(MINUTE(D2)*60)+SECOND(D2),-((HOUR(SUBSTITUTE(D2,"-",""))*3600)+(MINUTE(SUBSTITUTE(D2,"-",""))*60)+SECOND(SUBSTITUTE(D2,"-",""))))

and the call submit bet would look something like the following, where in this case i will only submit with <35secs to go before the scheduled off.

Code: Select all

If Worksheets("wsBA1").Range("AF2").Value > 35 Then GoTo LeaveRoutine
        Call Submit_Bet

[Freefall]

Hi Barnov,

Thanks for your help, your code seems to be just what i am looking for.

I'm getting on quite well with my spreadsheet as what I am trying to do doesn't seem to be as advanced as I first thought. I have used the sheet from the link above to give me some pointers and have made a good start.

Any examples really are a great help and I am sure I will use some of the code above.

Cheers