Multiple Linear Progression, logging and VBA

[CPerry]

Ok I've had a good think, spent time learning and have come up with the idea that the hexagon on a centroid won't work because the nearest points will make the angle of the line of the Brand I am trying to find skew across the perpendicular dimension amongst other reasons. However, the same principle should work in theory with a simpler See Saw design.

5 Brands are on one side of the See Saw, each emitting an up and a downforce along various lengths of the left side. The Brand in question will exert an up and a down pressure on the right side and it's total elevation will be the strength of it's momentum. I have spent the last few days calculating this equation and I think I am near. I also have past logs that I can use with this equation to generate some tests which I will post on here once complete.

I would appreciate if you could look at this and see if one of my several equation theories seems to work. Just going out for a few hour snow but I'll get back on it tonight and get it done ASAP!

Just keeping you updated and updating this 'diary' of a thread (not sure if this is the correct use of the forum but I assume that people with similar ideas can see where I have failed and take a short cut to learn)

As always, Cheers!
CPerry

[offthelip]

I think my idea was much simpler, so I thought I would quickly put down the calculations, see attached workbook. My idea was to calculate the position and angle of the centroid and then track the movement in the that postion. This will move dependent on all of the brands, not just the strongest. One problem which I can forsee is that the position of the centroid is dependent on which brand is placed where on the hexagon. So I think this probably means the order of the brands needs to be sorted, possibly round the hexagon of possilby right to left. Another possiblity is to have an irregular hexagon where the angle is proportional to the price rather than the length!! or even both length and angle!!
Just some ideas to hopefully help you along

[CPerry]

One problem which I can forsee is that the position of the centroid is dependent on which brand is placed where on the hexagon

This was my biggest concern when visualising it, why does the nearest point's weight bring the whole thing towards the Brand being examined? Which Brand should fit here? Why should it go in a clockwise order in order of the odds? It didn't make sense to me which is why I've spent all of today pretty much looking over all of the variables required. I have accounted for every aspect now I believe and I will be completing my calculations for the first race at least by tomorrow lunchtime with all hope. I will then post my findings on here and begin calculating numbers for 2 further races so if there's any kind of hint of an edge emerging it can be tested on other races asap.

I've taken many ideas from the Hexagon example and it's really helped me picture how all of the variables weigh on each other, thank you very much for your help thus far! I really think I'm getting close to potentially finding something very useful! Anyways back to the spreadsheet, more to come tomorrow!

CPerry

[CPerry]

I have just accidentally proven and found a way to express that the BACK side of Brand 1 is equal to the opposite FORCE created by the LAY side of the other 5 brands. Not sure how this helps me at the moment, further analysing now...

[CPerry]

Screen Shot 2018-03-26 at 14.50.06.jpgSeeSawTestExp.xlsb

So I have come up with two different theories (indicators) that take into account a bunch of different aspects from around the market. In the attached document I have created two different formulas for each of the 12 Horses. As you can see from the picture attached the black line (indicator) correctly moves ahead of the blue line (Last Traded Price) a few times for Horse 1 which I'm hoping is a positive sign. This was achieved by a really smoothed out moving average (something mad like a 150 MA).

OTL, could you please take a look to see if there are any good ways of using this indicator using some trading methods you're familiar with please? Crossover MA's or some more advanced stuff that I'm not yet familiar with.

Much appreciated and sorry it's taken a few days, these formulas are the most detailed and complex that I have created to date!
Cheers,
CPerry.

[offthelip]

Unfortunately I couldn't open the file, I am using Excel 2007 on Win 7 and win 10.
( I never upgraded because the later version of excel never offred me anything I wanted!!)

[CPerry]

SeeSawTestExp.csv

Try this one! The files are big so I've had to save it as a .csv but it is just 12 x 3 columns (Last Traded Price, Theory 1 for 12 horses, Theory 2 for 12 horses) so it should work just fine like this. If not, how is it best to send across to you?

p.s. forgot to mention in my last message but some cells shown a #NUM message because the formula takes a number and uses the ASIN function but occasionally it slips above 1 or -1??? For now, just treat any that goes over as ASIN(1) and any that go below as ASIN(-1) you can tell as the cells above and below are nearing each of the extremes.

[offthelip]

Hi Cperry,
I have been having a look at your data, and I have added some macros and some calculations which hopefully will show you the sort of approach that I would take with this. I have added some calculations ( including a exponential moving average function) to do a moving average cross over. ( I like EMA because it is easier to change the period) I then generated a buy and sell triggers from these. I then have a subroutine (stdema) that runs down the data and calculates the percentage profit from these buy and sell triggers the results of these are displayed at the top cells AZ2 to BC2. What I then do by using the index function in columns An to AP I run through each of the horses in turn. What I then do is put a double loop around this inner loop which will vary the values of the EMA to try out different values of the two ema to see if this makes any difference. The results of these three loops is stored in a variant array which writes the results out to the worksheet "results".
I then write out some equations in the rows 2 to 7 of the results sheet to summarize the results.
I have put a filter on the results sheets ( Make sure you deselect it before running the macro otherwise the rsults are all garbage)
Hopefully this will give you some ideas about how to test the possible trading signals
One thing that I notice from the tests I have run is that Horse 1 seems to be profitable all the time but most of the other horses aren't. This would concern me because how did you select horse 1? Would this be repeated in another race?
If I had more data which covered multiple races I would add another loop round the outside looping round each race

[CPerry]

Evening OTL!

I have had to keep away from my laptop for a few days with an eye problem but I've had a quick look at your number crunching (will properly explore the macros in the morning) but already love the idea of using EMA crossovers as I am already familiar with these!

Looking at the positives, I'm glad Brand 1 is always profitable. Now I have something concrete to work from. I think I know why that is different from other Brands also, my formulas work like a bell shape (imagine a standard deviation bell), Brand 1 is far left so all the other brands step down nicely 1/2(Brand 2)+1/3(Brand 3)+1/4(Brand 4)etc except I've used smarter calculations. Now I guess I just need to find a suitable way of placing the other brands on this bell and I should be on to a winning edge.

I've just come up with another few theories, the 3 new ones come after the two existing ones you've already looked at (Theory 3 is for Brand 1 only as it is super lengthly) at this stage could you give your opinion on which you think will be most accurate overall please? I've supplied the numbers for the first 4 brands.

This would concern me because how did you select horse 1?

I ranked the horses in order of odds (Smaller odds come first so is Brand 1 in this case, which is most often the most traded selection)

Tomorrow I will use these same 5 theory formulas on another 3 or so 12-horse race and a few 6 selection markets; maybe even an 18/19 selection market so you will have more to analyse. If you could just let me know which you think works best please I can focus on typing these up as the formulas I am using are complex and not easy to just copy and paste (results fit on a bell so have different weights and I've not figured out a clever way of having the computer work this out itself yet). As soon as I can narrow down the winning formula I can create a master app that chooses the strategy based on the number of runners (A LOADDD OF TYPING) but then run this for a whole day and have dozens of markets to analyse! Looking forward to that part already

Anyways, I shouldn't be spending too much time on this screen tonight so I'm going to turn off now. More tomorrow,
Cheers,
CPerry

SeeSaw5TheoryTest.zip

[offthelip]

I had a quick look at your latest file, and the first thing I notice is that the Price data is identical to the previous file. So there is not much point in me looking any further at that.
I think your a making a big mistake in trying to develop a system looking at data from a single race. You need hundreds of races to be able to develop a reliable system. With the race data you have sent me, there are probably hundreds of different equations you can use to select horse 1 at particular times and create a winner. This is because if you look at the price of horse 1 in this race it is trending up most of the time. If you bought at the beginning and sold at the end you would be in profit. You can't expect this to happen all the time.
I know nothing about horse, but I know a little about markets ( which this is, just buying and selling bets), markets are sometimes trending and sometimes they are reverting to mean. If a market is trending and the price goes up, it is a good strategy to buy on the expectation the price continues upwards. If the price is reverting to mean and the price goes up it is a good strategy to sell on the expectation that the price will revert to mean. Any strategy that you are testing which works in a trending market should work on this race. But what happens if the next race is reverting mean?? How is your strategy going to cope? The only way you can test this is to capture a lot of data so that you have a large sample of each type of horse race, ( trending and reverting to mean and combinations of the two where it swaps mid race). It is very important to understand where your strategy will fail and what the level of losses you can expect when it does fail. Hopefully these are going to be less than the winners
So I suggest you forget the development of fantastically complicated equations and concentrate on capturing enough suitable race data to build up a data base which you can use to run tests to allow you to get statistically significant results.

[SamT]

concentrate on capturing enough suitable race data to build up a data base which you can use to run tests to allow you to get statistically significant results.

Ditto that.

I would guess at least a ~hundred for preliminary inspiration and development, and a ~thousand for real testing.

I would also include the BookMaker's preliminary odds when computing Risk Factors... If you actually let a bet ride to the finish.

Right up til I read OTL's last, I thought your were merely betting, not buying and selling bets.

[CPerry]

https://www.dropbox.com/s/0e22efmbdv...ogOne.xls?dl=0

Ok I've taken your advice on board and today I have logged around 130 greyhound races. I have chosen this as there are always 6 runners so it was easier to code just one set of rules at this stage and the mini markets happen more frequently so it was easier to mine enough data in a single day to start testing my immediate theories. In the attached link I have the same 4 proposed theories as before, I was hoping you could check for which would 'perform' better using your buy/sell triggers that you already have coded please to narrow down which works best so I know which to move forward with?

Greyhounds are spaced every few minutes throughout the whole day, because of this they trade differently (thought the same efficient balancing codes are in place) from horses and football. The money can be at £0 and then up to a few thousand in two minutes which is all I've captured for each race. I'm hoping to have ultra fast 'get in' and 'get out' moments flag up to accumulate for several dogs perhaps and get a small profit over the course of each race, each day (refreshing at 20ms but captured every second for EMA cross overs and stuff).

I have also included the 'Average Weighted Price' in columns R:W which is the price weighted by the previously traded volume at each price. This means it can be used as an average to check for convergence and divergence from/to the mean for each Brand. Also included are the BACK and LAY %'s to see how unmatched money on each side affects it and I have included the whole market volume and the volume on each brand that can be used as additional indicators (Volume and Volatility) since the formulas are mainly Trend and Momentum themed.

Race details are in column A, the dogs are ranked based on their odds (Smallest first), I think that's everything for now. I will log even more races tomorrow and maybe even some football early in the week for even more markets. Hopefully that dropbox link works (as the .xls/xlsm/xlsb/xlsx files are all way too limiting).

Cheers,
CPerry

[offthelip]

I am away at the moment so i will respond next week

[CPerry]

Ok mate Have a good trip!

[offthelip]

Hi Cperry,
I have just downloaded your latest spreadsheet, The data you have captured has a totally different nature to the single race that you sent before. The original "horse" race had 1792 lines of data, so using moving averages of 25 and 50 periods was quite reasonable. This new data; you have captured 130 different races each of which only has 119 or 117 lines of data ,using the same 25 or 50 period moving averages is not going to work because half the race is needed to stabilise the moving averages. This sort of data would need a different sort of approach.
the way the data is organised does not make it at all easy to analyse, any moving average has to be reset when ever a race starts.
Also you have quite happily calculating your Theory 1 to 4 values, which is all very well. what you have really got to do is calculate some buy and sell triggers from these values. I gave you the software for a doing a very basic buy and sell test when I posted my analysis of the last race.
I think that you might find that the data you capture for each type of race ( greyhounds, Horses, Football teams , pigeons?) is going to be very different, so you are going to need to use a different analysis technique for each type of race and also quite likely a different strategy.
You need to look at the VBA macros that I posted in post #28, so that you can modify them to do your own testing of your theories. Once you have grasped how they work you can then get your data capture and organisation to fit in with the strategy testing.

There are a number of separate stages that you need to cover:
1: data capture ( done as far as I can see)
2: data organisation to make it easy to run a standard test template
3: Develop indicators for testing ( done)
4: create triggers from the indiactors
5: run standard template (done by offthelip)
6: loop through standard template 100+ times using different race data
7: Capture the results of the testing (done by offthe lip)
8: Analyse the results of the testing
9: Improve triggers to hopefully improve results,
10: go round the process again from item 3

[CPerry]

Hi OTL

Instead of just calculating the final two minutes of every race running (So I get the most amount of data as fast as possible) I have captured 5 minutes worth of data for 93 races so far (I will type up the ’theory’ formulas tonight and attach a new document before I go to bed) so there is enough data to use for each race. This new data should be much more like the previous data which worked with the EMA crossovers. 5 minutes out most races only have a trickle of money coming in so by the time the race hits a target amount i.e. £2,000 the EMA readings should already have an emerging ‘image’ of the market. I think this will work better. Each of the 93 races just needs to be broken up so the moving averages don’t have to be reset and then it can be analysed from there, I will properly take a look at how to do this tomorrow along with your previous software you sent across recently.

With regard to buy/sell or back/lay triggers the formulas will simply be something along the lines of backing when the line crosses above a specific value much like a RSI but I will need to visually see these numbers before I make a decision on what values to use. The ‘theory’ readings are all % ranging from 100%-100% so the most accurate theory will need to be tested somehow?

So that accounts for 2 and 4 on your list, when I can properly have a look at the results tomorrow I will work on steps 6:10!

Message again tonight
CPerry.