Fancy a date?

Oh, Excel! You fry my brain, you buggy git.

If you want to slow down VBA code then you add loads of sheet read and writes. Equally, to speed things up, you get rid of the read writes. I’ve been scraping data from the web and for each worksheet row of data, I could find an individual element and write it to a cell, then move to the next and repeat to complete the row. This is vastly speeded up by collecting data in to an array and then pasting it in one go, neat little trick.

This appeared to be working fine until I noticed a date value not pulling through consistently. I was holding the date, as read from a webpage, in the format ddmmyy. However, upon writing to the worksheet it altered to mmddyy, but only when the day value was less than 10. Once the day was 10 or greater, the value appeared in the correct format.

I tried to solve this. I tried setting the format of the cell in the worksheet before writing – didn’t work. I tried formatting within the array – didn’t work. Even though it appears correct in the array when viewing in the locals window of VBA, the problem persisted. I checked the regional settings on my computer – all ok. The array holds lots of values of differing data types and all save and paste ok except this. I even tried pulling the day, month and year value separately and then combine prior to writing – no better.

Searched the web and found some reference to the problem but no solutions.

I eventually solved this though through just guessing. I changed the format of the saved date value within the array to yymmdd. Now, when the array is pasted to the sheet, the regional settings display the value in the correct format, ddmmyy. Seems a ridiculous bug and I’ve no idea why VBA would do this only with certain dates and not throw an error. It’s like it’s guessing at autocorrecting but I can’t turn it off.

Extending the fun

3D printing – it’s the future (well it’s actually now). Besides all the pointless novelty items you print when you first get a 3d printer – a hand(?), glasses holder, magic table and the like – there are some things that actually do something worthwhile. And if there aren’t, design it yourself.

So I did. Using Tinkercad (it’s free).

I’d printed some phone stands and was wondering how I could make my own version. I designed a bracket for my phone to sit above my laptop screen. It was OK, but not actually needed – the phone works fine laid on the desk at the side of the laptop. From there though, came my idea – dual screen my laptop.

Quick search and found there’s a dual screen kit on Kickstarter for £200 or so (granted, it includes the screen). But I’ve made a better and much cheaper alternative –

A pair of brackets, mirrored for left and right. These slide on to the top of the screen, with my tablet sliding into them. I’ve made them with a slight angle, 11 degrees, which seems to be enough.

Now I can dual screen wherever I’m at. Coding is so much easier with two screens and trading would be fun too, if I did any, but you get the picture, literally.

The set up is a Dell laptop and Lynx tablet, both running Windows 10. The laptop won’t project the screen for some reason, maybe it’s too old, so I’m using Spacedesk software (it’s free at the moment). The mouse was a bit jittery on the tablet with them both connected to my home wifi, so I hotspotted my tablet and connected the laptop to that. Now almost perfect mouse and drag/drop action, wonderful. Now back to coding…

Q. How do you turn green into red?

A. Change the fundamental rules of the exchange, of course.

Despite the presence of rounding errors since the days of Flutter, Betfair decided one regular Friday to implement a change to the allowable bets on the exchange. Result – chaos. (I wrote about my experience of this rounding error back in 2015 here)

And why this rush to get the change in before any announcement or consultation with third party vendors? Who knows?

Surely one option they had, and this may be a bit novel, would be to limit or ban those that are trying to make “consistent attempts to take advantage of bet rounding”? Instead, Betfair’s unique approach to customer service was to inflict a solution that effected everybody. Here’s the link to the announcement. Note also the edit in the announcement, a good two days later, when they bothered to include an example of the new rules.

Looking over the socials you can see that all the software providers were hit equally, with very poor communication from Betfair. Gruss worked late into the night to come up with a fix, releasing a new version in the small hours of Saturday morning. However, with the impacts still being realised, further updates followed over the next few days. It still appears that in some circumstances greening just won’t work, although I think a close to green trade could probably be made, slightly weighted to back or lay, thus avoiding being rejected.

Whether Betfair make further changes to improve/worsen the situation remains to be seen. You can be sure you’ll find out, after the event. But for those that provide the liquidity to enable an attractive exchange (yes that is the traders), it probably just feels a bit shit, to be fair.

Minus the problem

[I’d left this post in drafts a while ago and forgot about it. Can’t quite remember what I was doing, think it may have been a time thing. The solution is the bit that counts.]

I don’t know why I suddenly had an issue with calculations in my code, but they were throwing up some real problems. Only in one section though, which is thoroughly annoying.

I have a value held as a double, 0.975, and I’m subtracting another value held as a double, 0.988. The result should be -0.013. But it’s giving a result 1.3333333333E-10 (I didn’t write it down but it was something like that).

I know that there are issues with accuracy when working with different data types as the way values are stored digitally can give weird results for some numbers. But I’ve always thought this was when using division, not what appears to be a simple subtraction. I’ve dealt with an accuracy issue previously by using rounding to 2 decimal places, as that was fine then. However, I didn’t want to restrict this value because it might run to a few decimal places.

Although I found an article saying you can declare as a decimal in VBA, it didn’t work when I tried it and gave an error. I did find another suggestion though which said that it is possible to declare as a variant, then use that value converted to a decimal, using CDec(value). So you can convert to decimal but not declare as decimal, another quirk of the now legendary VBA. When I did this with the two values, it worked –

ValueA = 0.975

ValueB = 0.988

Result = CDec(ValueA) – CDec(ValueB)

I can’t work out why in one module it didn’t cause an issue, but in another, it did. Same numbers, same data types, one works, the other doesn’t.

What’s been happening

Way back in March…

I’d worked on improving the analysis code and trying to pull something out of my results, then the racing stopped. So I fiddled with the bot’s code and left it quietly ticking along on the Aus gallops, just to give the VPS something to do. Results from this were pretty much breakeven for the two months or so before the UK dogs restarted. That’s not such a bad outcome when you take into account commission of upto 10% in Australian markets.

In the meantime I’ve done a bit more coding along with general tinkering. I’ve been very fortunate to be able to keep working throughout the restrictions, but have managed to do more with the kids, playing in the garden and going out for walks, which has been nice. Keeping them positive is essential.

Since the dogs restart I’ve dialled in the triggers based on the earlier analysis and progress is good, apart from when Betfair fell over last week – the following period was negative in a consistent way. I do wonder if an effect of the exchange crashing is that either a load of bots drop out of the market for some period or the manuals are acting differently, maybe overly cautious, or perhaps both? Or, something else – coincidence? Who knows? Anyway, since then it’s continued on the path of good progress.

The free data I talked about in my last post could be useful, I’m sure. I had a quick search looking at how to read it and extract data, such as pre-off prices – it doesn’t look like a simple task so I’ve not moved at all with that. I’m currently developing a method of creating my own speed ratings and am only finding industry SP in the data I’ve scraped, which is where the historical data may help. I’d prefer to work with pre-off prices than BSP, although if the method gets any sort of return, whichever price I work with should only have a minor impact (this is an assumption as I think the last pre-off price will be on average the same as BSP).

Free data

Betfair are giving away historical data at the Advanced level (1sec frequency) for Jan2020 to May2020 and Pro level (50ms frequency) for Apr2020 to May2020. This is for all sports.

This is due to the lack of events, but for testing purposes you may find the data available as useful. You can normally get 1 minute data for free with the basic plan.

Scroll down to the Discount Price Plans to get the free data. Don’t use the Purchase Data selector.

Here’s the link but it’s easily Googled –

http://historicdata.betfair.com/#/home

Not finding errors

I read this while looking for help –

“There’s only two types of programming languages: the ones people complain about, and the ones no one uses.”

I’d like to think it’s true.

I was searching for help on handling error 9 which pops up when referring to an array that isn’t initialised. I was seeing this error when monitoring the MyBets sheet. My code for logging the changes to MyBets works like this –

• On change to MyBets – lift the entire data range into an array (Array1)
• Check this data against previous data lift (Array2) to find any changes
• Log changes to another array (Array3)
• Once Array1 is checked, place Array3 in another sheet
• Save Array1 to Array2 for next time
• Repeat
• On market change – erase Array2

This doesn’t work on the first change to MyBets on a new market as Array2 isn’t initialised. It could be initialised if it was a known size but this can change dependant on other data I add to Array1. I could write code to size the array on each market entry, but I was looking for a quick way of saying – if referencing the array gives an error, load it with the current data and move on. I found a way to do this using the Not operator. The code looks like this –

If Not Not Array2 = 0 Then…

Now, instead of an error when referencing Array2, this “Not Not” will return a 1 or 0, so the code flows and continues unhindered.

Reading around various forums it’s said that this method is actually a bug from VB6, which is what VBA is based on. And some say that this method may disappear if the bug is fixed. I also read that VB6 is no longer maintained, so the chance of the bug being fixed is low.

For me, this works for now so I’ll use it where I need to.

Going through changes

For my analysis code I wanted to record everything that happened with navigation, bet placement, matching and cancelling. After some trialling I got what I thought was everything I needed. It’s not until you run the code for some time that you pull out all the bugs.

In the image below, between the thick black lines, a complete trade appears to be recorded within a 2 second period. However, on closer inspection the figures don’t add up.

This shows, at the line numbers –

• 28. back bet of 2.17@4 placed into the market @ 39secs to off
• 29. 1.42@4 Back Unmatched @ 39s
• 30. 0.75@4 Back Matched @ 39s
• 31. Below £2 bet entry @ 39s
• 32. 0.76@3.95 Lay Matched @ 39s (This is the offset from line 30)
• 33. Below £2 bet entry @ 38s
• 34. Offset bet matched flag reported before actual offset bet data @ 38s
• 35. 1.44@3.95 Lay Matched @ 37s

The missing information is the 1.42 Back bet getting matched. Why is the split Lay recorded but not the split back? This is the result of how I’d coded to handle changes to the MyBets sheet. I was comparing bet references and checking the Matched column, which shows U or F (Unfilled or Filled). When a bet gets a partial match the remaining portion stays in the market with the same bet reference. The initial match shows as F. So when the remaining bet is matched there is an updated line with the same bet reference and showing as F in the matched column, the code was skipping this as it had already recorded that reference as matched/filled; this is how I was missing it. Offset bets, lay bets in this case, are placed for each portion of the initial bet taken, so each has a different bet reference.

I added further code to capture changes to stake figure when showing as F. This has worked well so far.

Zero sum

I just downloaded some collected data from my VPS and thought I’d see how it compared to the Betfair Betting History. Coincidentally, the P&L since running on the VPS is zero, exactly. That’s after 11,500 bets settled across 2200 markets. I’ve only made very minor adjustments so far as I’m more interested in the amount of data I’m collecting – the more the better. Anyway, the data and betting history are well matched. Some discrepancies in the cancelled bets but this appears to be in how I’m capturing the greening trades, which doesn’t always include the sub £2 bets. It’s the bet modification that misses but I can code for this just in the greening procedure, so the fix shouldn’t be much and won’t impact the trading code.

Longing to be an integer.

Here’s an interesting fact from Microsoft –

… VBA converts all integer values to type Long, even if they’re declared as type Integer. So there’s no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them.

It’s taken from here –

link

With thanks to this answer for pointing it out –

link

Although the size of my code has increased, I still don’t think it’s very big, so any speed increase may be minimal. However, as I now know about this, and having spent time previously reviewing all declarations and changing to the smallest (least bits), I feel the need to go through the code and replace any integer declarations to long. Bring on the errors.