Bet Angel and Excel

[SamT]

What do you see when you compare one horse to the Field's average?

What if Rate of Change for the Field of all horses has significance?

Is an Absolute Rate of Change or two signed RoC's the only ways to observe the data.

Perry, Didn't you say that you could set up BetAngel to provide data for all horses for an entire Race? I think it uses Sheets(Bet Angel, Bet Angel1, Bet Angel2, ...) And I think I read on the BA Site that BA can output to a CSV file.

otl, what do you know about Cube Arrays? I know snb has a tutorial on his site, but I only scanned his code to see how it worked in general.

If my memory is correct, Perry can make 30 Race WkBks, with 30 Shts, with a DWord of fields, > 50K of Records, all 30 WkBks with an identical structure and names.

That can be an amazing opportunity for some basic Pattern searching. Predictions like Perry wants are based on Patterns.

OTOH, each WkBk will be several MB in size, and I imagine it could take a fairly simple query an hour to complete. And one has to run a lot of queries, looking at the data in as many ways as possible, narrowing down on the Pattern(s).

In Horse Racing, the Horse's activity on BA's 'Market Board,' will be strongly influenced by that, (and the others in the race,) Horse's history. The Human Factor.

[SamT]

Perry, We can't use the old Recording style code. I am writing a new version to compile all the data for all the horses. The output may need some tweaking afterwards, but it will be usable.

[offthelip]

Hi Sam,
I know nothing about Cube arrays since I have never used any of the cube functions.

I have just done some estimating as to how much data capturing one race would be and I came up with these figures ( all assumptions)

Brands per race	30
Data items per brand	10
Changes per minute per data item	60
Duration of race mins	30
Total Number data words	540000
Bytes per dataword	4
Megabytes	2.16

I then compared it to one of the systems that I used for back testing strategies for the stock market using the FTSE 350 stock prices over 260 days worth of data, I was surprised to find that it is almost exactly the same amount of data. I didn't have any real problems running through tests looking of various patterns. Obviously I was writing vba code using all the techniques that I knew to make it run fast.
I think the longest test I ran took about 3 hours. Most of them were just minutes.
I used to run the initial tests looking for possible patterns over one years worth of data, then try to repeat the results with a different year as an "out of sample" test.
If Cperry records a lot of data I suggest splitting the data randomly into two groups, one for intial testing the second for out of sample testing. This is to ensure that the strategy identified is a realistic strategy and not just a quirk of the initial race data use to find the pattern.

Stocks	350
Days	260
data items	6
Total Number data words	546000
Bytes per dataword	4
Megabytes	2.184

[SamT]

Changes per minute per data item = 60

Updates per Second per data item = 50 =~190MB

The DataCapture I'm writing, per Perry's last, is going to capture all updates.

Each Update will be placed on the Log Sheet at the end of each update period. a 30x10 block for Horse Data, and a one rowx10-14 block of Market Data. 14 cells if you want MicroTimes at Start/End of Update, and Start/End of Printing. I will be reformatting Market Data to one row, vice a 6x8 block.

h: Horse Data, m: Market Data, t: MicroTimes
hhhhhhhhhhmmmmmmmmmmtttt
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhhmmmmmmmmmmtttt
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh
hhhhhhhhhh

I was thinking that Perry can keep those huge WkBks and we can send him the analyzing code.

Logging two or more Races will provide different Blocks of data for testing possible Patterns

Thoughts? Suggestions?

[offthelip]

I agree the raw data for race will be of the order that you are talking about. however I was thinking of processing it down one row per change rather than one row per update which will bring it down to the size I am talking about. I would do this once before trying any pattern matching because it will make the pattern matching much faster.

[CPerry]

Afternoon gents,

I had never heard of the level 2 stock market display, that video link you sent was VERY interesting. I could immediately recognise a lot of similarities to the ‘ladder’ interface of BA. You are correct about the book and the sum of the horses always being less than certainty, because of that I believe the best patterns we will find will lie somewhere in how much the most traded brand’s (for example the front 4 runners out of 8 in a standard race) prices are being forced away from the ‘expected price’ and how volume pressures in other horses push the averages of the other brands more towards a 100% book. Because of that, if we can record each brand’s back prices, lay prices, traded amount and then compare that to the overall market information we may find a few underlying context clues early enough to get an order through the market and indicate when it’s best to put a safe closing order in.

@SamT

What do you see when you compare one horse to the Field's average?

What if Rate of Change for the Field of all horses has significance?

I think these kinds of things are definitely where we should start analysing. The averages of the market, each brand individually and the pressures on these average (how money forces numbers away from the 0 point in a basic standard deviation perhaps as a simplistic start point?).

That Peter Webb has used many indicators, charts and techniques over the years and regularly trades with what I also believe is a PSAR indicator present. He has played these markets for so longs though that he just sees patterns in his mind and always leaves a 10-minute market in profit. I figure if a human brain – as brilliant as his is – can do it, then an automated, emotionless system should hopefully return a good ROI also. I am under no illusion that a ‘robot’ can be perfect in these markets but all I’m looking for is a nice consistent set of patterns that can be traded regularly – should be easy right? Sarcasm alert

I’ve also just had a little read on variant arrays in the other thread and I amazed at the time differences this method can make!

Here is some discussion on CSV files from the BA forum, I strongly believe BA can output to a CSV file but I have personally never tried to do this myself: https://www.betangel.com/forum/viewtopic.php?t=15236

Perry, Didn't you say that you could set up BetAngel to provide data for all horses for an entire Race

I can create multiple ‘Bet Angel’ sheets in a workbook and then BA can dedicate each race to a separate sheet to efficiently capture a whole day’s worth of race data. Each sheet can show all the information we have previously been looking at for all runners present simultaneously so recording this will give us all the information we will need.

If my memory is correct, Perry can make 30 Race WkBks, with 30 Shts, with a DWord of fields, > 50K of Records, all 30 WkBks with an identical structure and names.

I am a little unsure what you been by this ^

I can’t wait to see the new recording style code and try it out on all the different markets! I will also record some football matches too so we can compare how these slow moving markets are similar/different to the quick trading horse racing markets where the volume can spike from 100k traded to 500k traded in 10 minutes.

Cheers,
CPerry.

[SamT]

one row per change

I agree that would be ideal. It's more code and more CPU overhead. What I envisioned was just capturing the data as efficiently as possible. The data table(s) can then be sorted and filtered as many times as needed afterwards, an other issue with that is that the Entire Horse Data Table is just one update.. The Market data is 6? updates of 1, 3, or 5 cells.

‘Bet Angel’ sheets in a workbook and then BA can dedicate each race to a separate sheet

I thought BA dedicated each sheet to a particular Horse.
Therefore:

Perry can make 30 Race WkBks, with 30 Shts, with a DWord of fields, > 50K of Records, all 30 WkBks with an identical structure and names.

I will also record some football matches too

I would follow otl's lead on that.

[offthelip]

The way I see this working is:
1:In real time:
Capture every update on a race and store it at one row per update with time since last update to create a huge file for each race.
Save it
2:After the race in non real time:
Process this huge file to a smaller file with one row per change with the time since the last change
3: Analyse the small file to see if we can find a specific pattern in the file which gives us a winning strategy
4: Repeat 3 using another pattern
5: If we find an pattern that seems to work repeat 1 to 3 with another race and the same pattern

Repeat 1 to 5 as required
This is part of the system development process and unless we get success in this part there is no point in going on to the next step of trying to lay bets in real time.

When I was developing a strategy I would record the values of lots of indicators ( not just the ones used for the trigger). The reason for doing this was I would then analyse the results of the strategy and see if these was any correlation with any of the unused triggers and the results. If there was I would try incorporating that indicator into the trigger. Hopefully this would then improve the results.

I am interested in the football data, because you say it is "slow moving" but neither of these is as slow as the stock market for some stocks. I don't think the speed of movement matters at all, what matters is the granularity of the movement compared to the range of the movement. The problem I see with the horses is the fact that there are only 6 to 10 different prices the whole race. With a spread of 1 price interval that mean to make the smallest profit the price has to move 2 steps which is between 20% and 30% of the whole movement. That is going to be difficult!!!

[CPerry]

Each sheet is dedicated to a particular race (with each horse {brand}) occupying a row in the information field. The only way I can split the horses up is by opening multiple windows of BA and having each particular window focus on a separate brand but doing this individually will be really inefficient and run the CPU levels wild. Don't forget cell C4 on the 'Bet Angel' sheet tells you how many runners are present so perhaps this could be the first bit of code so the log knows how much of an array it needs to track and then we do it the most efficient way from there (as you two were discussing) Then, we break down the brands individually for analysis and try and find some patterns from each in relation to the 'bigger picture' I will run a bunch of separate indicators and simple average checks independently of each other. Depending how long this new log will take to code, I will record a whole days worth of racing on separate sheets for a couple of days and then at the weekend I will log the main UK football matches (as these will build like an hour before K/O as opposed to 10 minutes before, only have 3 brands within the market and will generally just be interesting to analyse the differences of).

I like that 5 step plan Offthelip, I've been reading a bit more about the level 2 stock market display also so will try and use as much of this new knowledge as possible when exploring patterns and trends.

[offthelip]

One advantage of the stock market over horses is that if you back all the stocks in the stock market on average you will win, unlike backing all the horses in a race.

[SamT]

Timing:
I think the Start time of updates is a more accurate reference than the last update. It doesn't have the tween-update Jitters that the Last update time has

The issue with placing all values updated in a single row is Column Count. Thirty Horses at only ten values is 300 Columns. Each Column must be labeled if anyone is to make sense of the data.

The total column count of the data BA provides is 36 + 10 Market Info Cells in a 5x6 range.
The layout I want to use is
Row 1 for labels
B2.resize(,11) holds Microtime and Market Data One Array in code
C2(Resize(C4, 36) = Horse's Data Table

After Logging, Series Fill Column A from 1 to lastrow. This is the original data index. One can return a sorted table to original form with it.

What this provides:
Sort by MicroTime column to copy all to another sheet
Re-Index
Sort by Horse Name to copy the horses entire Race data. Paste next to the new Microtime column and have a match set.
Re-Index back to original form
And, of course all other filters and sorts are still there.

Why code this way... This is throwaway code, it only has one specific use.
But it still must:
Rearrange a 5x6 array + 1 into a 1x11 array
It must place all the data onto the log sheet(s) in a usable form
It must know when it's running out of Rows. It might have to know when it's running out of Columns. How many updates are there in a race? One Excel XP sheet can handle around 16K updates of 30 Horses using all columns + separators
Speed is of the essence.
I'm lazy.

[SamT]

Note to all guests,

I don't know if you can, but if there is an attachment you want, know that all attachments have a built in expiration date and will disappear.

[SamT]

Each sheet is dedicated to a particular race (with each horse {brand}) occupying a row in the information field.

Too bad, that would have been ideal.

What are the possibilities with CSV files?

I'm worried about Size limits just using Excel sheets as a storage media. This is an ideal place for a data base. I'll deal with it.

[offthelip]

Sam, i agree time of update is better than time since last update. I suggest splitting the data into brands using vba and not using sort. Sorting large datasets takes ages.

[CPerry]

Todays races range from 7-14 horses with one race only having 5 horses running, at this point in time - granted 13:00 UK time is well before when the money hits the later races - only the front 6/7 horses have any kind of money traded on them. Even 10 minutes before the off, the 'higher odd' runners don't attract that much volume compared to the ones at the top of the information field. This is obviously still useful to our analysis, I'm just giving you the scope of these types of markets. Each horse i.e. 7 in a race, will have: back, 2nd back and third back price, lay, 2nd lay and third lay price, the money at each of these prices and runner volume. Totalling 7 x 13
Again, the number of runners is shown in cell C4.

[SamT]

Perry,

What data from BA is not useful? Logging all data is as easy ... Is easier than capturing part

otl,

I'll get the data set, what you and Perry do with it is up to you Financiers.




Everybody, 7 horses will make the data set 1/4 as large as 30. That's a good thing from my POV. But I'm not an analyst.



I'm going to look into various DB languages.

[SamT]

Horses Data is in two rows! Before the data can be used, the two rows must be merged into one row.

Using a Database or a CSV file, means that the Row Merging must take place between BA updates.

Using Excel Sheets for Logs means the Merging can take place at any convenient time. Excel it is.

I deleted a Request for DB Experts in post #213

[CPerry]

We won't need anything outside of the information field other than market volume (Cell C2) and some sort of timestamp like the previous log's micro timer. Each 10 minute 'pre-market' period can have a various numbers of horses running which is why I mention Cell C4 - there can be around 30 individual markets per day so depending how you can split up the individual runners, I can have each market on a separate sheet and a 'restricted refresh' of 10 minutes prior so the cells will be clear until the dedicated time where the macro can launch itself.

If the code can adjust itself based on how many horses are running via this cell before launching or recognise how much of the information field is being occupied then it will have less to do (as opposed to always tracking for the maximum number of horses every race, no point when there are only 7). Also, it means I can leave it running for the whole day without having to relaunch it every 10 minutes and potentially missing vital information. Yes each horse's information is spread over two rows isn't it. You have the 'Price' row with first, second and third back/lay prices then the row underneath with the money at each.

Other than the information field and total market volume, I don't think we will need anything else; unless I am mistaken Offthelip?

CPerry.

[SamT]

It matters not, it is easier to code for all Horse data than for just part of it. Also remember the Human Factor.

As to running all day. The data will be stored in Sheets in two workbooks, Sheets Named "Log" & (i = 1 to n.) Each Race will be stored in 2 different Workbooks Named "Race" & (i = 1 to n) & " "log & ".xls". For different Days, Change Folders. The current Default Save folder is "Logs". You create it to start with, then rename it by date each day. "Race Log_11-23-17"

All I need is the cell address that tells me when a new race is starting. Or the current one is ending. I can always tell by the Name of the First Horse changing, but that point is after the race starts.

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

[SamT]

CPerry, I'm giving you all the data from as many races as you want. What you do with the data is up to you. How I give it to you is up to me. In this Research Stage, I'm just an IT fella trying to keep my code fast enough to keep up with BA,.