Bet Angel and Excel

[CPerry]

Cell F3 shows a start time so this will change every 10-25 minutes or so (depending on the time of the next race obviously) and Cell F4 shows a countdown which will activate 10 minutes before (as set by me via BA directly).

If you don't want to log the entire Race, we have to adjust the Worksheet_Change Code.

Yeah, I'd like to log the entire 10 minutes prior which will be the 'trading time' but perhaps even log a few 'in-play races' which commence obviously once the horses have started to run. This will be interesting to see if there are any trends that occur at super high-speeds when the odds fluctuate like hell. I'll do this after the already-agreed pre-market horse races and football matches (Once I have several days of each!)

I've also began looking at pattern recognition methods whilst I wait and formulas for the most common indictors that I think should be explored.

CPerry.

[SamT]

F3 changes indicates a new race??? Always???

F4 indicates start time - 10:00.

I'll put some Constants in the Worksheet Change sheet so you can adjust the times you want logged relative to StartTime. CODE NOTE: If Not TimeToLog then Exit Sub

see if there are any trends that occur at super high-speeds when the odds fluctuate like hel

Can you bet during that time? Isn't there a cell that indicate close of betting?

[CPerry]

Well F3 shows the start time so for example F3 would be 12:00 at midday and F4 would count down from 11:50-12:00 and then if the next race was 12:10, F3 would change at 12:00.
If it were 12:15 the next race, the sheet would freeze for 5 minutes and then tick again at 12:05, this would be indicated by F3 changing from 12:00 to 12:15 so could use this cell as a start-trigger.

There's no cell that indicates close of betting, we just need to go by the countdown reaching 0 but for in-play, you can bet along as Cell H1 doesn't show "Suspended" as it comes towards the end of the race the winners odds will drop towards 1 so we could just say if odds are =< 1.3 (can be altered if this is too tight of a window) then wrap up shop. I just think it would be interesting to explore how these numbers move in comparison to each other to understand the makings of the market more. Just me being a bit of a nerd really, I'm fascinated by numbers and patterns; it's what got me into this kind of stuff in the first place. Can never collect too much data, I say!

CPerry.

[SamT]

F3 changes at End_Race
And
At 15:00 before Start_Race
F3-F7 =< 10:00 at Start_BARun
close of betting, we just need to go by the countdown reaching 0 but for in-play,
F3 = F7 = End_Betting. Got it.

you can bet along as Cell H1 doesn't show "Suspended" as it comes towards the end of the race the winners odds will drop towards 1 so we could just say if odds are =< 1.3 (can be altered if this is too tight of a window) then wrap up shop.

That's for the Helper App. I'm only considering Data Mining at the moment.

Please don't get the idea that I'm trying to discourage you. You have an imagination and you use it. THat's good. Think of my comments as a guide to HowTo imagine.

[CPerry]

Good morning gents,

I have had a busy few days looking into pattern recognition and have a few ideas for when the code is finished and I have logged a day’s worth of data.

I have also been looking at the next stage of my ambitious endeavours, something that could push this big project of ours forward massively. This is to include some fascinating techniques in the field of machine learning, unsupervised deep learning and basic artificial intelligence for price predications.

Please find below a few links. I have spent the last few days looking into this and it appears a LSTM (Long Short Neural Network) looks to be the most promising, I still need to research it’s relevance to the type of market that BetFair operates (as Offthelip pointed out, very similar to a type 2 market)

https://www.qplum.co/documents/deep-learning-trading
Machine learning and deep learning exercises (python coding examples for deep learning)
https://github.com/jdwittenauer/ipython-notebooks
https://datascience.stackexchange.co...lkit-for-excel
Machine learning specifically in Excel.

In the last link it mentioned something called Xlwings allows two-way communication between Excel and Python. I mention this because Python is the programme of choice in the second link (though it is similar to vba I know there are many differences). At this stage, I’d just like to ask, do either of you know much about: Python, ‘deep learning neural networks’, their code structures and their potential relevance to this project? It is a field that is increasingly capturing my attention and something I think would be fun to explore and learn about – again my inner nerd can not control himself.

In the meantime, I hope the code for the logging is coming along nicely – I can’t wait to crunch some numbers and I hope you are both well.

CPerry.

[SamT]

VBA is not suitable for AI.

In the Stock market you make a profit by buying and selling many times at any time, and the "Race" never ends.
At the Races, you make your profit by "buying" the right bet(s) once and waiting til the end of the "Market."

In the Stock Market, there are no "Odds," only trends. At the races, there are only Trends affecting Payoffs due to the Human Factor. The Probabilities are determined by the group of Horses.

I see three factors
The Odds presented at the beginning, before the betting starts, by Expert Odds Makers.
The Human Factor affecting Payoffs at the end of the Race. What BA tracks is the Human Factor, ie, Prices.

The unpredictable Factor can be represented by the fact that a horse and rider can get disturbed in the gates. That requires The BA User to monitor the race in real time.



___________________________________________________
I don't know the Lingo, so I'm saying this wrong...
Say Bets are 2$
Say the odds of Horse A winning are 3:1 against, so the payout is 6$
If the Human Factor drive the Payout above 6$, bet that horse.

Say the odds on Horse B are 1.5: 1 so the payout is 3$
If the Human Factor leaves the Payout at $3.00, don't bet that horse.

IOW, only and always bet when the payout is greater than the odds.

[CPerry]

@ SamT

Are we any further with the new logging code, is there anything I can do to help you out at this stage?

I've looked at plenty of ways of analysing data for patterns in the past week (and I also have several simplistic things I'd like to explore too once we get some data - just like WOM of Brand 2 and 3 affecting Brand 1 foreshadowing movements and divergencies from averages) but do either of you have any suggestions of pattern recognition areas to research next?

The black-scholes equation and how prices are varying from this is something I thought about a while ago, but some simplistic form of machine learning (where the app is looking at how indicators have been doing previously during the very brief 10 minute market) looks like it could narrow and quantify reoccuring trends. No where near the AI level stuff on stock markets for continuously moving markets, just a system that 'learns' from most recent movements and uses this to predict future moves (taking into account info from the other horses (brands), particularly where the Money is!)

Do either of you have any recommended reading for pattern recognition whilst the log code is being produced? Random forest? Linear regression? Some basic perception model? @Offthelip - how would you best look for patterns in a level 2 market?

Cheers,
CPerry.

[SamT]

I'm sorry.Life has been hectic recently and I haven't even opened Excel at all.

I haven't forgotten, just can't find the right "head Space" to work on it.

[offthelip]

Pattern Recognition!!
Firstly although I have used level 2 , I never automated anything to do with level 2 and so I didn’t do any research on Level 2 pattern recognition.
One thing I must warn you about doing vast amounts of research on captured data; it is very easy to run analysis on this sort of data and identify a brilliant strategy not by pattern recognition but by “pattern matching”
This is the bug bear of all back testing of strategies. Whatever data set you have, there are going to be all sorts of random movements in the data set. In addition there are certain patterns that recur fairly frequently. Let us assume that there is a pattern that occurs 5 times in you data set, and assume that twice the price movement is a small loser and twice it is a small winner, then assume due to the random movement ( and not a causal relationship) the 5^th time the movement is a large winner.
This looks like a winning strategy but in reality it isn’t all you have done is matched the pattern of movement before a random large movement. The actual price movement is random!
The only successful strategies that I have managed to automate have relied on identifying some particular feature of the market from knowledge of the market, then working out how to identify when this occurs from the data that is available.
An example of this is when bad news breaks in a company, the next morning the shares open down and will often go down more, however by the end of the day they have often recovered quite few percentage point from the low point. ( the difficulty is identifying the low point). This is simple market overreaction and it is quite possible to exploit this.
Having worked out what pattern to look for purely from theoretical analysis, the next step is to back test the strategy to find out whether it works when used with real data.
( you can see in my example I am only interested in shares that open more than 3% or so down from the previous close)
When I watched the videos that you linked to, it was clear that the person using BA knew a lot about how the market in the horse racing moved and was identifying what was likely to happen in that market using his experience of watching it. It is going to be very difficult to replicate that sort of expertise automatically.
The movement of Level 2 on the stock market is similar. Every stock is slightly different, the time of day makes a lot of difference, what is happening in the news makes a difference. The general movement of the whole stock market makes a difference.
I suspect that horses are going to the same, the betting on the Grand National at Aintree on Saturday is going to be a completely different market to the market in the 12 o’clock on a Tuesday in January in East Nowhere.
So to make a success of what you are trying to do, I think you need to really understand the market you are working in, and identify an event, or succession of events, which you believe will cause the price to move . Then having identified the event you need to workout how we can identify from the data that the event is occurring, and hopefully before the price movement had finished.
Only when you know what data pattern you are looking for and why, should you then try back testing your strategy.
If you go straight into just looking for patterns you will be doing endless pattern matching, and your chances of producing a winning strategy are very low.

[CPerry]

@ SamT

No worries, I understand. Projects can be fun and exciting for nerds like us but then life just throws you a curve ball and puts everything into perspective. Take your time, if there's anything I can do to help though, just let me know!

@ Offthelip

Very good advice! That Peter Webb from the video has over 20 years experience with the markets and knows all of the underlying mechanisms that make it up, I'm looking at attending one of his trading classes in the new year to add to my own experience. I understand a few mechanisms of the market so I'm confident I will be able to find some reoccurring trends and capture them early with the data I collect. I'm looking forward to the challenge! In the meantime, I'm playing the markets manually on practise mode to uncover even more around reading, learning and exploring.

CPerry.

[SamT]

I need a full, 30 horse, BA sheet for testing the Data Logger. One update at around the 3 minute mark will do

[CPerry]

The number of maximum numbers of horses today is 20 at the 2:05 race, will this do?
I can connect the standard spreadsheet to this race a few minutes before the start and save it immediately, this will be the same as capturing a single update. I assume you only need it for cell references etc?

The number of horses in each race today are as follows (to get an idea of how much the finished app will have to account for on a regular basis):
7, 7, 7, 5, 7, 9, 20, 5, 5, 6, 9, 14, 8, 15, 10, 15, 9

CPerry.

[CPerry]

BetAngel 20 Horse Snapshotxls.xls

Here is a snapshot from the 2:05 race with 20 horses running. A single update at the 3 minute before mark.

Just for context:
£100,000 was matched with around 10 minutes to go,
£300,000 was matched with 1 minute to go,
At the end of the race - £392,000 was matched (It hit £400,000 10 seconds after the countdown but with this app we'd have closed all orders a whole 20 seconds before this)

CPerry.

[SamT]

Here is a Master Workbook that gives all the layouts for all sheets in this Project. It also has a couple of sheets with info the Developer needs. I have modified it from all previous versions.

This is what I am basing all code on.

If you modify it, please upload a copy for otl and I to keep. Actually, please name the last 4 columns on the Bet Angel sheet and update the two sheets that use those names and addresses.

[CPerry]

BA Helper Master Sheet Layouts Final.xls

Just a couple of small tweaks, it's looking pretty good!

We just need to capture a few markets now to explore the patterns available then we can add to the final app and piece everything together now!

CPerry.

[SamT]

I renamed the one I sent you to BA Helper Master Sheet Layouts.v1.xls
I saved a copy of the one you sent as BA Helper Master Sheet Layouts.v2.xls
I saved the one you sent me as BA Helper Master Sheet Layouts.xls

My thoughts are that the working version should not have a v#. That the latest v# is a copy of the working version. We just have to notify each other when a change is necessary.

Oh, yeah, I changed all the Red (edits) Fonts back to standard.

[SamT]

Here's what I have for a data recorder. It is NOT tested. I suggest you learn how the ErrorHandler operates first, then go thru the rest of the Modules and add the same process to any Procedures you think might need it. By the time you've done that, you should have a good handle on all the Classes, (User Defined Objects.)

Theory of Operation
All Code uses Worksheet CodeNames

Bet_Angel: Main Driver is Worksheet_Change Event Sub; Starts And Stops code Operation with Macros, "StartRecording" and "StopRecording"; Observes Worksheet Changes and sends appropriate data to the Recorder.

Bet Angel Worksheet Events
Range("A1") Change: Call Recorder.StartUpdate
Range(AF9") Change: Let Recorder.HorsesData = HorsesDataTable.Value
Range("C2") Change: Let Recorder.MarketData = MarketDataRange.Value

Recorder, (clsRecorder): Owned by Bet_Angel; Generates a Unique Key; Sends MicroTime and Key to the MarketWriter, all Horse Data and Key to HorsesWriter, Then all Market Data to MarketWriter.

MicroTimer, (clsMicroTimer): Owned by Recorder; Provides an Elapsed time in Seconds, to microsecond accuracy.

MarketWriter, (clsTableWriter_Market): Owned by Recorder; Parses 2D MarketData into a 1D OutPut array with Key and MicroTime included; Writes Output to MarketData, A Worksheet

HorsesWriter, (clsTableWriter_Brand): Owned by Recorder; Parses a Defacto 3D array of Horses Data into a simple 2D OutPutData array with Key included; Writes Output to BrandData, A Worksheet.


ErrorHandler, [Module- ErrorHandler]: Has one function, Accepts a String, selects the Message Associated with that String, Displays Message, Stops all code execution.

______________________________________________

The Processes above have been organized to use time as efficiently as possible since Updates are coming in at 20 - 50 per second.

[CPerry]

Happy Monday lads!

I have just turned my laptop on and have opened up everything I need for the day. Just before I take my dog out I thought I’d have a quick look at the logger to see what the first inevitable error would be so I could have something to think about in the snow. I get:

Run-time error ‘13’:
Type mismatch

When I debug it shows the line ‘Erase OutputData’ in the Class_Initialize sub, I see your little note of ‘1x2x8x19

So I’m just going to take the dog out now and then I’ll try and figure out what’s going on. Also I’ve been working on a simplistic strategy to fine-tune my logical thinking last week whilst waiting for the logger and I’ve arrived at a problem I hadn’t considered before. When firing a bet if the “BACK” in Cell L9 disappears before the bet can be processed – and L9 can disappear rapidly under quick moving connections – then it shows an error. Have either of you got any ideas how to ‘keep’ L9 as “BACK” until the point O9 says “PLACED” then it can be cleared and replaced with the original trigger function again ready for the next bet.

L9 shows my trigger (For this it’s very simply:

Brand 1

=IF(A1=1,B1=1,C1=1,1,0)

Brand 2

=IF(A2=1,B2=1,C2=1,1,0)

etc etc for 6 brands.

This solution will obviously be relevant for the primary project also so thought it worthwhile to mention in here. Ok I’m off to brace the snow so I can get straight on with some work!

CPerry

[CPerry]

Grey Hound Spreadsheet.xls

Still working on the logger but thought I'd just share my fun little side-project with you as has made me think about a couple of things, It is a grey hound spreadsheet as there is nearly always 6 runners - here's how it works:

Firstly, the red text section at the bottom (Cells B39:E44) shows the order of the grey hounds next to their names, odds and how much of the market their volume is occupying.

Next the cells yellow cells put this information into ascending order from smallest odd to largest. This time including the WOM% (displaying numbers -50:50 using the formula I used ages ago) then underneath I have multiplied the WOM together with the % of the market their volume is occupying to create a new weighted figure and then the 'calgorithm' (dorky name I know) compares these figures to one another. From there I have a couple of triggers as you can see just to the right.

Now the problem.

As Cells D31:G36 (the triggers) flick between 1's and 0's, it causes the instruction fields to display "BACK", "LAY" or "CLOSE_TRADE", like I mentioned this morning however sometimes they flash on too quickly before disappearing so I need a way of saying "If the trigger has shown '1' for longer than a whole second (adjustable) then place the order and L9 must show that order BACK/LAY/CLOSE_TRADE for as long as it takes until O9 displays "PLACED". This is obviously just for brand 1.

So two problems basically which are going to occur in the primary project also, 1) the orders can sometimes disappear before they hit the market and 2) When placed, everything needs to be reset without losing any of the 'trigger' functions. What about within this time of processing a BACK order, CLOSE_TRADE displays too?? I've found this mini-project quite useful for understanding how the laptop in front of me is going to be a sod at every single piece of code I try to type up.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 15 And Target = "Placed" Then
rn = Target.RowCells(rn, 12) = ""
Target = ""

End If

End Sub

^^ I managed to get this which wipes L9 and O9 but I don't want L9 cleared, rather 'reset' to the original function as you can see on the attachment. Because I'm working with multiple brands also I think I'm going to have to use R1C1 notation aren't I? Any ideas gents?

Whilst I eagerly await your reply, back to the logger

CPerry

[offthelip]

When firing a bet if the “BACK” in Cell L9 disappears before the bet can be processed – and L9 can disappear rapidly under quick moving connections – then it shows an error.

This sort of requirement is a common problem with this sort of development, where the control logic changes during development. A good way to manage this is to keep all the control logic in one place. (could be in VBA or on the worksheet, I think VBA is easier) The way your system works is basically a "state machine" . i.e. some actions can only take place after others. one way managing this is to draw a simple state diagram see attached.
I am not sure I have got the logic correct, but this technique will help you solve the problem and allow you to keep a clear idea of what you are doing and what is happening.