connect via excel - auto bet my selections

[liero1]

Hi,

I probably looked in the wrong places.. but can't find a solution for a beginner like me - so I want to place BSP bets (some with min. BSP rule) on a list of selections that I compile externally, sth like the below -

Date Time Course # Horse
10/05/2019 17:40 Wolverhampton 4 Miss Enigma
10/05/2019 19:30 Ripon 2 Acclaim The Nation

Is that possible to connect to a spready that somehow links that list and then performs the BSP bets?

Thanks,
liero

[jimibt]

yes, there are a few ways to do this. however, for any of these to work, the races will still need to be loaded into guardian (you could just load the entire day TBH and the selections would only be used where found in your data sources -to be explained below).

By and far the simplest way is to pre-load this as a static *list* in a new tab on excel (let's call this tab *Candidates*). This can be as complex or simple as you like. The simplest aproach being to manually type the entries. In your Candidates tab each day, you'd add the entries literally by using their name (or preferably, their selection id from betfair - but we'll stick to name for now). This list might look a bit like this:

Miss Enigma
Acclaim The Nation
Fire Diamond
etc, etc

In the Bet_Angel sheet, you'd do a VLOOKUP against the Candidates sheet and where a MATCH was found, you'd action your bets as required.

This is purely a setup that you could use, the implementation would of course take a little bit more (i.e. you'd need to party inside the Bet_Angel Worksheet_Change event)

[liero1]

perfect thanks. I don't have a problem with a bit of excel deep diving - is it possible to link a BOT to those selections though?

Thanks,
liero

[jimibt]

perfect thanks. I don't have a problem with a bit of excel deep diving - is it possible to link a BOT to those selections though?

Thanks,
liero

yes and no... the excel *link* would merely place the bet for you. behind that, you'd have your Guardian rule runnning to monitor that transaction and take any appropriate steps thereafter.

think of the excel link as being the catalyst/initiator and guardian as being the engine thereafter.

[liero1]

ok no problem. I think i need to replicate the rule as close as possible within excel then..

I don't suppose you can use BSP for the odds price you want to take?

I am now thinking of having a quick IF rule to check countdown, if time <60 secs, place best market back (you just leave odds empty for that?)..

Sorry many questions - just hard to find a proper full depth guide to this..

thanks!

[jimibt]

ok no problem. I think i need to replicate the rule as close as possible within excel then..

I don't suppose you can use BSP for the odds price you want to take?

I am now thinking of having a quick IF rule to check countdown, if time <60 secs, place best market back (you just leave odds empty for that?)..

Sorry many questions - just hard to find a proper full depth guide to this..

thanks!

re BSP... that of course only becomes an entity once the market goes IP. A 1st stab would be to use the timer as you mention and take Best price somewhere around the 5-10 second mark. that MAY take you close but as you well know, BSP is a beast all in itself and bears an infuriatingly small resemblence to the price just before the off.

[liero1]

cheers. what about take SP all as global setting and have a 1000 odds bet on the selection pre off? would that not work well?

[jimibt]

cheers. what about take SP all as global setting and have a 1000 odds bet on the selection pre off? would that not work well?

YES

[liero1]

[liero1]

the selection process works well, but I come across the situation that the 'Status' cells won't blank for the new market when excel switches to the next market, that seem to hinder any new bets? ..

[jimibt]

the selection process works well, but I come across the situation that the 'Status' cells won't blank for the new market when excel switches to the next market, that seem to hinder any new bets? ..

you may have to manually intervene there. typically, i'd use a module level variable to identify the current market, then compare that to the market in Bet Angel sheet B1. If this is different, then clear out the cells (i.e. in code). I in fact have a little clear up routine that i use specifically for this purpose. This routine gets called in the Bet_Angel Worksheet_Change event:

Code: Select all

Private Sub ClearBetStatuses()
Dim intRow As Integer, wsSource As Worksheet

    Const BET_NOTIFICATION_COL = 12
    Const BET_STATUS_COL = 15
    Const BET_STATUS_ROW_START = 9
    Const BET_STATUS_ROW_END = 60
    
    Set wsSource = ThisWorkbook.Sheets("Bet Angel")
    wsSource.Range("L6:O6") = ""

    For intRow = BET_STATUS_ROW_START To BET_STATUS_ROW_END Step 2
        wsSource.Cells(intRow + 1, BET_NOTIFICATION_COL) = ""
        wsSource.Cells(intRow, BET_STATUS_COL) = ""
    Next
    
End Sub

[liero1]

ah thanks, perfect. So i just add it as a module? Testing now.. This is really a headache, what's the point in automating when you need to manually delete the status cells... But this might work well,let's see.

Otherwise maybe just look for the 'inplay' status in G1 and empty those status cells...

[liero1]

anyone else with this problem, there was a thread here - viewtopic.php?f=31&t=7181

[jimibt]

ah thanks, perfect. So i just add it as a module? Testing now.. This is really a headache, what's the point in automating when you need to manually delete the status cells... But this might work well,let's see.

Otherwise maybe just look for the 'inplay' status in G1 and empty those status cells...

you can add it as a sub in the same namespace as the bet Angel worksheet_change event... just paste it below, then, call if from the worksheet_change event when the market changes to another market. Below is how i track this change in that event:

so, module level variable:

Code: Select all

Private m_LastEventName As String

then, in the worksheet_change event:

<--- cut

Code: Select all

    Const RACE_NAME_COL = 1
    Const STATUS_ROW = 1

    Set wsSource = ThisWorkbook.Sheets("Bet Angel")
    Set wsCalc = ThisWorkbook.Sheets("Calculations")
    
    Set runnerRange = wsSource.Range("B1:K50")
    Set mainCells = wsSource.Range("B1:G6")
    
    If Not Application.Intersect(runnerRange, wsSource.Range(Target.Address)) Is Nothing Then
        ' change event info on cell contents being updated
        If (m_LastEventName <> mainCells.Cells(STATUS_ROW, RACE_NAME_COL)) Then
               m_LastEventName = mainCells.Cells(STATUS_ROW, RACE_NAME_COL)
               ClearBetStatuses
       end if
    end if

<---cut

[liero1]

sorry, I think I need a more detailed help here - where do I add the module variable, and where do I find the change event code... Thanks!