Excel bets duplicating & refreshing excel tabs

[Markwinnsmith]

Hi all

I have set up an excel file with various automation set through macros and I often find the same issue recurring. I try to place a bet and it gets accepted multiple times into the market. For example, the macro may put lay, 3.0 and 10 in the Bet Rules / Individual Commands, Odds and Stake cells respectively and the macro does this just once and leaves the text there in the cells. However, some of the time Bet Angel will put a duplicate bet into the market, so in this case I would have a £20 lay stake put into the market at 3.0, rather than a £10 stake. Is there any way to stop this from happening?

Another issue I am having (although it's not really a massive problem, it's just annoying). I might load all the races for one event in a day to different excel tabs. In Bet Angel itself I can see that the markets are being regularly updated and resfreshed, however in Excel one or two markets often take quite a few minutes to load onto the relevant tab and I can't understand why. Has anyone resolved this issue or is this just an problem with the software?

Any help would be much appreciated!

Thanks

Mark

[EyePeaSea]

Hi Mark,

Trades made via VBA/Macros being duplicated is something I've struggled with since I started (2011). Having had a break for a few years and now back working on a new system, I've made an effort to fix this. It hasn't happened since, but to be honest, it didn't happen very often, so maybe I've just been lucky. Anyway, here's my approach.

The 'Worksheet_Change' event for each tab calls my code which decides whether to make a trade.

My code (in a separate module, not in the worksheet) puts in the trade command, odds, and stake as appropriate and then does a few extra things.
If the code decides to make a trade, then:
Set two variables:

Code: Select all

g_blnDoNotProcess_TradeInProgress = True
g_dteLastTradeMadeDateTime = now

Back to the 'worksheet_change' event - I have some extra code, right at the start:

Code: Select all

' New code to try and avoid the dreaded 'double trading'.
' Wait X seconds after making a trade before going anywhere near
' my trading code.  Just keep EXITing Worksheet_Change until
' a number of seconds has passed.  
' *Always* let BetAngel process sheets, to get the bet 
' through the "Placing" and "Placed" phase

If g_blnDoNotProcess_TradeInProgress = True Then
        
     ' Has enough time elapsed?
     If DateDiff("s", g_dteLastTradeMadeDateTime, Now) < 15 Then
          ' Don't do anything.  DoEvents, ensure 
          ' EnableEvents is true and exit
          DoEvents
          Application.EnableEvents = True
          DoEvents
          Exit Sub
            
     Else
          ' If more than X seconds has passed, then
          ' Reset the flag so that my code can be run again
          g_blnDoNotProcess_TradeInProgress = False
     End If

End If

' Now just add the normal code in worksheet_change SUB below here

So here, I'm waiting 15 seconds after my trade. This is more than enough. But I'm in no hurry as I only make one set of trades per event.

One other thing I always find useful. Stop BetAngel from updating the worksheet whilst I'm trying to do something. I'm not sure if this is 100% necessary, but I find it sensible..

So when worksheet_change is called (by BetAngel changing the spreadsheet), always turn off application events.

Code: Select all

Application.EnableEvents = False

And then after your code has finished deciding whether to trade or not, turn on application events again, so that BetAngel can continue updating the spreadsheet.

Code: Select all

Application.EnableEvents = True

I can't guarantee that this will fix the problem as the issue is intermittent and therefore difficult to reproduce. But my 15 second wait seems to lets BetAngel process anything, before I go near that work sheet again.

Code: Select all

<granny sucking mode=on>
I don't find a need to clear the trade command after I make a trade.  But if you do,  make sure you never clear the 'status' field (Column O on the spreadsheet).  Always leave this to BetAngel.  If you clear that command before clearing the individual command, then BetAngel will definitely double-trade.
Turning off application events helps this a bit, but there's never a reason to clear the "traded" cell in Column O, so just leave it alone.
</granny sucking mode>

Hope that helps.

Ian

[/color]

[EyePeaSea]

Mark,

About the Excel sheets not being updated straight away.

Even if Guardian changes the 'last update' time, I think that just shows the last time it checked BetFair. If there aren't any changes to odds, then it won't update the worksheet.

So, if you loaded all the UK Racing events up, at 6am, then you might see the delays. But if you left it until 11:30am, when there's a lot more things going on I think you'd notice updates coming through much quicker.

Ian

[Markwinnsmith]

Hi Ian

Thanks so much for your replies. Glad i'm not just going crazy!

Similarly to yourself, this doesn't happen to me that often, but it can happen which is the worrying thing.

Regarding clearing column 'O', my sheet does do this. Reason being, early on in the day an initial back bet is placed to take SP and later on a lay is placed at a certain level when the race starts. Therefore, my code for placing the lay starts by clearing columns with the odds and stake and then columns 'O' and then enters the data for the relevant lay. I will see there's a better way to do it though.

Interesting regarding enableevents, I will certainly see how this affects my sheets, i've not tried that before.

Regarding updating of the sheets. Would that mean that if for any reason the odds were completely static, you would not be able to load that market into Excel? I realise they will definitely change at some point so I guess it's not too much of an issue.

I'll try all the things you suggests and thanks a lot for you help!

Mark

[workpeter]

You control the flow of trades by comparing the numbers in the unmatched and matched columns on the right of the sheet. You will figure out the best logic to suit your needs.

[Markwinnsmith]

Hi workpeter - I'm not sure I follow. The problem I was having is that in the simplest instance, you put "Back", the odds and the stake in the relevant cells at which point Bet Angel may place 2 bets. This is before there has been any chance of Bet Angel and Excel updating to show unmatched and matched figures. It rarely happens, but it does happen sometimes.

[workpeter]

First place i would look is your logic used to clear the bet status cell. feel free to send me anything to review

[EyePeaSea]

I'll try all the things you suggests and thanks a lot for you help!

Mark

Hi Mark,

How did you get on? The reason for asking is that I haven't seen double betting with BetFair, but I think that's more to do with the streaming API refreshing Excel more steadily than the changes I made to my code.

I am however being hit with double bets on BA for BetDaq. The difference over the last week is that I'm now betting further in advance of the off, so there are a lot of worksheets being processed at any moment in time. Hence the reason I'm becoming convinced it's only a problem when BA is accepting trades from Excel and trying to update lots of other sheets at the same time.

What I'd really like (and I think this was asked ages ago), would be a "don't make a second bet within X seconds" option.

It shouldn't really worry me as the system I'm using makes just a single set of trades once for a race. If my system is profitable over time, then it just means that on BetDaq my losses will be double, but so will my winnings. But... I'd really like to feel a bit more in control

Ian

[workpeter]

You are triggering a bet on a given condition and your VBA code is putting in a bet command,stake and odds. You expect second bet should not to occur because the status is no longer blank or you have some logic which checks the unmatched/matched bet numbers to control your bet flow.

I suspect its double betting due to very tiny occasional delays in the status or unmatched/matched numbers updating, so your code updates the command,stake and odds twice with the same values causing a second duplicate bet.

I had the same issue, which i solved by using a boolean in my VBA code. Essentially when i open a bet, a change the boolean to TRUE which represents a bet has been triggered. In order to place a bet in the first place the boolean must be FALSE. So straight away this stops double bet. The boolean only gets set back to false when the timestamp has changed (ie moved by 1 second). This is enough time for the spreadsheet to update.

[EyePeaSea]

I suspect its double betting due to very tiny occasional delays in the status or unmatched/matched numbers updating, so your code updates the command,stake and odds twice with the same values causing a second duplicate bet.

Hi Peter,

Thanks for the reply - I missed this one until you posted the link.

I understand what you mean, but that's not my problem. I do a similar thing to you - as part of the betting, I place a marker on the sheet (rather than set a variable). If that marker exists, then the trade code won't kick in. The only change I make to a sheet is when a bet has been made, a separate routine checks for the words "Placing", "Placed", or "Error". If either of the first two, it just clears the command+odds+stake fields. If it's 'Error', then it clears the status column and allows BA to retry.

There certainly can be (and are) bugs in my code, but the reason I'm sure this is a BA thing is that the problem has disappeared with Streaming on BF and also only happens on BD when I have too many concurrent races being tracked. It's not a huge deal and of course I could be wrong.

Ian

[workpeter]

There is no need to clear whilst placing, i would wait until a trade has completed before tampering. Not suggesting this is your solution but its cleaner code.

[workpeter]

I suspect its double betting due to very tiny occasional delays in the status or unmatched/matched numbers updating, so your code updates the command,stake and odds twice with the same values causing a second duplicate bet.

Hi Peter,

Thanks for the reply - I missed this one until you posted the link.

I understand what you mean, but that's not my problem. I do a similar thing to you - as part of the betting, I place a marker on the sheet (rather than set a variable). If that marker exists, then the trade code won't kick in.

Ian

Let me explain the problem with this. My entry criteria for a trade is existing trades must be closed. I.E.BACK/LAY stakes are equal. When it detects this it will set the BOOLEAN to false which allows for a new trade to occur. The problem here is the 1st trade goes through but the BACK/LAY stakes are still equal because of slight delay in updating. The VBA code is so fast it sets the boolean back to false allowing for a 2nd bet. Now the spreadsheet has caught up and the BACK/LAY stakes are no longer equal but by this time the double bet as occurred. To solve this I don't allow the BOOLEAN to go back to false unless some time has passed.

If i were using spreadsheet markers like yourself, then visually you wont see the marker appear, disappear and appear. All you will see is the marker appear due to all this occurring in milliseconds.

[EyePeaSea]

Hi Peter,

Ah, yes, I see that in your situation, using a variable makes sense - as I only ever make a single set of trades, placing a marker on the sheet is enough.

And about clearing the cells when the status is 'Placing' - that was just another attempt to see if I could control double betting as waiting until 'Placed' didn't help.

Yesterday I moved up to 6 concurrent events, and didn't get any double betting and only had to skip 1 or 2 UK races. I'll keep stepping it up gradually until I find the sweet spot between double betting and skipping races.

Ian

[workpeter]

Hi Peter,

Ah, yes, I see that in your situation, using a variable makes sense - as I only ever make a single set of trades, placing a marker on the sheet is enough.

And about clearing the cells when the status is 'Placing' - that was just another attempt to see if I could control double betting as waiting until 'Placed' didn't help.

Yesterday I moved up to 6 concurrent events, and didn't get any double betting and only had to skip 1 or 2 UK races. I'll keep stepping it up gradually until I find the sweet spot between double betting and skipping races.

Ian

Ian,

Just to confirm, can you put this line of code in the same place where you open your trade
Debug.Print "trade opened"

If your code is working as expected, you should only expect to see this message once per trade. if you see it more than once you have a problem. You will be able to view the message in the 'immediate' window as your VBA code executes. If the window is not already displayed, Goto 'view' > 'immediate window'

[workpeter]

A fail safe option is to put some logic in your code to cancel your unmatched bets if they are larger than you intended and then resubmit the bet.

So how this will play out is you intended to do £100 bet, but it doubled betted to £200. You detect this is too much and put in the cancel command, then resubmit. Because you only double bet sometimes, this fail safe won't get stuck in a loop.

...I am assuming here that you are offering a price to the market rather than taking current price, allowing you time to cancel your order