Bet Angel and Excel

Attachment 20963

Firstly, please find attached the logging information for the 13:10 and 13:25 races today, 50,000 records taken approximately over two separate races (I think it was about the last 2 minutes of the first race and the rest of the time – after a 5 minute complete standstill between the markets switching – was race number 2. You can see this ‘change’ of markets from row 9640 which I have highlighted, interestingly the 12 rows above were empty. Strangely the original file wouldn't upload so I copy and pasted just the data tab and changed the file to a .xlsb so hopefully it's got through to you fine.

Now for the business rules. For each of the following, I have given each output as either: 1 = “BACK/CLOSE_TRADE” or 2 = “LAY/CLOSE_TRADE”. This is because it depends on what the previous order was i.e. If previous = CLOSE_TRADE, then 1 would mean “BACK” but if previous = BACK, then 1 would mean “CLOSE_TRADE” if no constants can be used for outputs then can “BACK/CLOSE_TRADE” be used and then we would just need one more rule i.e.:
FINAL_SIGNAL: If all three indicators show “BACK/CLOSE_TRADE” and previous was “BACK”, then = “CLOSE_TRADE”. Same logic with “LAY/CLOSE_TRADE”.

Whilst unsure of how to best implement the final trigger, here are the first drafts of the business rules for each indicator. Only half in developer language because of the underlying “WAS but IS NOW” I’m getting a little confused with. I’m assuming the way around it has something to do with the ‘Stores previous Macd Formula values’ you included in your example of business rules. Tell me, how does this fit in to get around the “WAS but IS NOW” problem I’m having with the following?:

MACD Business Rules:
If macd_C_SeriesCount was less than (macd_A_SeriesCount – macd_B_SeriesCount) but is now greater than, then “1”
If (macd_A_SeriesCount – macd_B_SeriesCount) was greater than macd_C_SeriesCount but is now less than, then “2”

RSI Business Rules:
If rsi_SeriesCount was less than RSI_Low_and_Rising_trigger_point and is now greater than RSI_Low_and_Rising_trigger_point, then “1”
If rsi_SeriesCount was greater than RSI_High_and_Falling_trigger_point and is now less than RSI_High_and_Falling_trigger_point, then “2”

‘Close Trades
If previous command was “LAY” and rsi_SeriesCount was greater than RSI_MID_and_Falling_trigger_point and is now less than RSI_MID_and_Falling_trigger_point, then “1?”
If previous command was “BACK” and rsi_SeriesCount was less than RSI_MID_and_Rising_trigger_point and is now greater than RSI_MID_and_Rising_trigger_point, then “2?”

WOM Business Rules:
If wom_SeriesCount was less than WOM_Low_and_Rising_trigger_point and is now greater than WOM_Low_and_Rising_trigger_point, then “1”
If wom_SeriesCount was greater than WOM_High_and_Falling_trigger_point and is now less than WOM_High_and_Falling_trigger_point, then “2”

‘Close Trades
If previous command was “LAY” and wom_SeriesCount was greater than WOM_MID_and_Falling_trigger_point and is now less than WOM_MID_and_Falling_trigger_point, then “1?”

If previous command was “BACK” and wom_SeriesCount was less than WOM_MID_and_Rising_trigger_point and is now greater than WOM_MID_and_Rising_trigger_point, then “2?”
And then I assume some other part of the code is checking that these values are occurring within ‘look back period’ of each other. And then the final signal is getting sent to the instruction fields and everything should be ticking until 10 seconds before start time, pausing and then resuming after this 10 second period once the next market is connected.

Cheers,
CPerry

Cperry,
Just a suggestion for your business rules: I suggest you change the "2" to "-1", To me this makes more sense in that every "1" is a "buy" signal to my way of thinking, and every "2" is a "sell" signal. To most people postive numbers are "buys" or credits and negative numbers are " sells" or deficits. It will also make it very easy to do the look back check because all we do is sum that 3 seconds worth of all the triggers and if it is 3 we back and if it is -3 we lay.
Thanks for the data file it looks good for some back testing

Damn, I hate using magic numbers for values of any kind, I don't care if it's 1, 2, 3, 0,-1,-2,-3, they're all meaningless. Why not 42? although I do use 999999999999 as an error indicator in bookkeeping and accounting apps

Quote:

---

if no constants can be used for outputs then can “BACK/CLOSE_TRADE” be used and then we would just need one more rule

---

“BACK/CLOSE_TRADE” All three signals combined into one? What does that mean, because it looks like a Name, but If so it is undefined,

Quote:

---

Only half in developer language because of the underlying “WAS but IS NOW” I’m getting a little confused with. I’m assuming the way around it has something to do with the ‘Stores previous Macd Formula values’ you included in your example of business rules. Tell me, how does this fit in to get around the “WAS but IS NOW” problem I’m having with the following?:

---

Uhmmm... Business Rules belong to the User.The Developer should keep his nose out. With one exception, if the Boolean logic of the rule set is broken, then the Developer needs to point this out to the user. IOW, no VBA CODE words. Nothing under the bonnet should the User see or use.

Using Value Names from the Excel sheets is good, since the User not only can see them, but is responsible for them.

All the "SeriesCount" Names on the Excel Sheets represent the length of the series of values sent to the Calculators and Formulas. They are User adjustable (relative) Constants. IOW you want a running average of the last 12 Updated Values, that Series Count is 12

I think you mean the EMA vs SeriesCount.
I think the SeriesCount Values are what you might be refering to as ‘look back period’, and maybe not.

Since Financials are not my domain, I have been letting you and OTL hash them out. I'm not ready for them ATT anyway. What I am ready is to start teaching you to write the Formula UDOs and the Calculator UDOs. But I need the Business Rules to begin that.

BTW, did I tell you that I only found ONE typo in all four DataStorage UDOs you wrote?

My analysis of the data on the second race you recorded has come up with a major problem if this data is typical of what happens.
The fundamental problem is that the price changes infrequently and when it does it changes in big steps.
If you look at the attached file I have picked out every line where the price actually changed. First thing to note is that there are only 97 price changes in the file, this is from 40361 rows of data in the original file.
The second thing to notice is that there are only 10 different prices that occur in the whole file.
I have calculated the time interval between price changes and plotted in on a graph, one plot is as they occurred and the second plot is sorted into time intervals. From this plot you can see the over half the time the interval between price changes is over 2 seconds. The Average time between updates is 4925 ms and the standard deviation of this is 7973 ms.
How does this effect the system are trying to build:
All of the indicators you have chosen MACD, RSI, WOM are indicators that have some sort of “smooothing” in the them, ie they have some sort of time filter which is designed to make sense of fast moving prices where there is a lot of “noise” in the price. These indicators do not work at all for the sort of movement which you are getting in the data that you sent me.
I have calculated the EMA12 and 26 for MACD and the MACD signal line and the RSI for the whole series, taking into account the update time since the last price update.
The following plots are showing roughly the last minute of the race, when there is the most movement.
The fundamental problem I see with the price series is that there is insufficient movement to have any realistic possibility of making a profit from it. The total range of movement from 4.3 to 5.3 is sufficient, the problem is the coarseness and infrequency of movement.
Attachment 20968
Attachment 20969Attachment 20970

Perhaps I didn’t word it clearly, I meant that “BACK/CLOSE_TRADE” could imply either but to get around this I have instead used just “BACK” or “LAY” in the Business Rules and then to determine whether it should indeed be a “BACK/CLOSE_TRADE” I have stated a few rules in a ‘Final Indicator For All Three Indicators” as you can see below, do you this this kind of set up would work? What I was unsure of last time, and still am, is how it recognises past numbers. Before I go any further here is another draft of Business Rules, hopefully you can make out what I’m trying to get the system to do here:
Ps. I used the names from the excel sheets rather than the VBA code words in this second draft like you said J

MACD Business Rules:

Uses, MACD_A, MACD_B, and MACD_Signal Formulas
MACD_A Output - Double
MACD_B Output = Single
MACD_Signal output = String

Input Properties:
X as Array
Y as Double
Z as String

Output Properties:
Value As String
MACD_A As Double
MACD_B As Double
MACD_Signal as Double


‘ *** \/ ??? \/
Stores previous MACD Formula values
Stores Count of MACD_A Formulas above MACD_C
Stores Count of MACD_D Formulas Below MACD_E

Business Rules:
If MACD_Signal < (Previous MACD_A – Previous MACD_B) AND =>(MACD_A – MACD_B), Then Output “BACK”
If (MACD_A – MACD_B) => Previous MACD_Signal AND < MACD_Signal, Then Output “LAY”
If Something Else = “NO_TRADE” Then Output = “No Trade”

RSI Business Rules:
Uses, RSI Signal, RSI 30, RSI 45, RSI 55 and RSI 70 Formulas
RSI Signal = Single
RSI 30 Output = Single
RSI 45 Output = Single
RSI 55 Output = Single
RSI 70 Output = Single

Input Properties:
X as Array
Y as Double
Z as String

Output Properties:
Value As String
RSI Signal As Double
RSI 30 As Double
RSI 45 As Double
RSI 55 as Double
RSI 70 As Double

‘ *** \/ ??? \/
Stores previous RSI Formula values
Stores Count of RSI Formulas above RSI
Stores Count of RSI Formulas Below RSI

Business Rules:


If previous RSI Signal < RSI 30 AND RSI Signal => RSI 30, Then Output “BACK”
If previous RSI Signal => RSI 70 AND RSI Signal < RSI 70, Then Output “LAY”

‘Close Trades
If PreviousCommand was “LAY” and previous RSI Signal => RSI 55 AND RSI Signal < RSI 55, Then Output “BACK”
If PreviousCommand was “BACK” and previous RSI Signal was < RSI 45 AND RSI Signal => RSI 45, Then Output “LAY”

WOM Business Rules:
Uses WOM Formulas
WOM = Single


Input Properties:
X as Array
Y as Double
Z as String

Output Properties:
Value As String
WOM As Double

‘ *** \/ ??? \/
Stores previous WOM Formula values
Stores Count of WOM Formulas above WOM
Stores Count of WOM Formulas Below WOM

Business Rules:
If WOM => 0 Then Output “LAY”
If WOM <0 Then Output “BACK”

Final Signal For All Three Indicators
If Output = “BACK” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “BACK”
If Output = “BACK” AND PreviousOutput = “BACK” Then Final Output = “BACK”
If Output = “BACK” AND PreviousOutput = “LAY” Then Final Output = “No Trade”
If Output = “LAY” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “LAY”
If Output = “LAY” AND PreviousOutput = “BACK” Then Final Output = “No Trade”
If Output = “LAY” AND PreviousOutput = “LAY” Then Final Output = “LAY”
If Output = “CLOSE_TRADE” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “LAY”
If Output = “CLOSE_TRADE” AND PreviousOutput = “BACK” Then Final Output = “CLOSE_TRADE”
If Output = “CLOSE_TRADE” AND PreviousOutput = “LAY” Then Final Output = “CLOSE_TRADE”
If Output = “CLOSE_TRADE” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “No Trade”
If Output = “NO_TRADE” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “No Trade”
If Output = “NO_TRADE” AND PreviousOutput = “BACK” Then Final Output = “No Trade”
If Output = “NO_TRADE” AND PreviousOutput = “LAY” Then Final Output = “No Trade”

So for the very first example, where the MACD gives a “BACK” command:
If MACD_Signal < (Previous MACD_A – Previous MACD_B) AND =>(MACD_A – MACD_B), Then Output “BACK”

It uses the previous MACD_A and previous MACD_B values to make a decision but I don’t know where these are coming from, how is the system remembering past values and how do we call upon them for this formula to make the decision at the key moments? Do you see what I mean?

From here, say the system displays “BACK” and also the other two indicators do within the given look back period, I need it to either make a “BACK” or a “CLOSE_TRADE” order depending on what my last command was; this is where the ‘Final Signal’ part is used. It can be one of two things:
1) Previous command was a “CLOSE_TRADE” so I want to open up a new order therefore it was indeed be “BACK”
2) Previous command would be “LAY” so instead of making a new order (“BACK”) I would want to close the current order “CLOSE_TRADE”
This has taken a lot of visualising and crude sketches in my notepad to get my head around, I hope I worded what I’m at least trying to get the system to do well enough for you both to understand. I’m glad to read there was only one typo in all four DataStorage UDOs, I was super careful when writing them!

CPerry.

@ Offthelip

Strange.. It's common practise to use these indicators in the BetAngel forum after speaking to a few of the most influential members in there and there are videos online of people using them on these markets as they work so similarly to the actual exchanges. The money doesn't start flooding in until close to the off time and some races vary drastically. I'm just popping out to a meeting now but will look into this immediately later when I get back to see if there's any easy workaround.

CPerry.

Perry, forget everything you know about coding and the Helper App, Business Rules are purely the domain of the USER.

Modified, with questions,:

MACD Business Rules:
Note: Currently only uses one MACD. Provisions for different Parameters for all three MACD calculations

Uses, MACD_A, MACD_B, and MACD_Signal Formulas

Inputs:
macd_A_SeriesCount
macd_A_Smoothing
ema_Multiplier

Outputs:
Value
MACD_A
MACD_B
MACD_Signal

Business Rules:
If MACD_Signal < (Previous MACD_A – Previous MACD_B) AND MACD_Signal=>(MACD_A – MACD_B), Then Output “BACK”
If (MACD_A – MACD_B) => Previous MACD_Signal AND (MACD_A – MACD_B) < MACD_Signal, Then Output “LAY”

RSI Business Rules:
Uses, RSI Signal, RSI 30, RSI 45, RSI 55 and RSI 70 Formulas
Coders Question: Are RSI 30, RSI 45, RSI 55 and RSI 70 Formulas going to be fixed formulas with no change in PArameters? Ever?
If the User foresees a possible change in the "Trigger? Values" The Names of the Formulas should change. IOW, If the User may want to move the RSI_30 line to 35, then the name "RSI_30" no longer fits. Suggest the User review the Finacial Formulas and use the technical names of these four RSI lines as Formula Names. If these will be fixed Values, then The USer will have no need to input them.

Inputs:
rsi_SeriesCount

Outputs:
Value
RSI Signal
RSI 30
RSI 45
RSI 55
RSI 70

Business Rules:

If previous RSI Signal < RSI 30 AND RSI Signal => RSI 30, Then Output “BACK”
If previous RSI Signal => RSI 70 AND RSI Signal < RSI 70, Then Output “LAY”

‘Close Trades
If ??? was “LAY” and previous RSI Signal => RSI 55 AND RSI Signal < RSI 55, Then Output “BACK”
If ??? was “BACK” and previous RSI Signal was < RSI 45 AND RSI Signal => RSI 45, Then Output “LAY”

WOM Business Rules:
Uses WOM Formulas

Inputs:
wom_SeriesCount
wom_Adjuster
wom_BACK_TriggerCount
wom_LAY_TriggerCount

Outputs:
Value
WOM

Business Rules:
If WOM => 0 Then Output “LAY”
If WOM <0 Then Output “BACK”



Final Signal For All Three Indicators
@The User: CLOSE_TRADE and NO_TRADE outputs not mentioned above

Coder's Note: Assume all "Output"s mentioned refer to the "Value" Outputs above? Are all the outer "Output"s above not to be used?

If Output = “BACK” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “BACK”
If Output = “BACK” AND PreviousOutput = “BACK” Then Final Output = “BACK”
If Output = “BACK” AND PreviousOutput = “LAY” Then Final Output = “No Trade”
If Output = “LAY” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “LAY”
If Output = “LAY” AND PreviousOutput = “BACK” Then Final Output = “No Trade”
If Output = “LAY” AND PreviousOutput = “LAY” Then Final Output = “LAY”
If Output = “CLOSE_TRADE” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “LAY”
If Output = “CLOSE_TRADE” AND PreviousOutput = “BACK” Then Final Output = “CLOSE_TRADE”
If Output = “CLOSE_TRADE” AND PreviousOutput = “LAY” Then Final Output = “CLOSE_TRADE”
If Output = “CLOSE_TRADE” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “No Trade”
If Output = “NO_TRADE” AND PreviousOutput = “CLOSE_TRADE” Then Final Output = “No Trade”
If Output = “NO_TRADE” AND PreviousOutput = “BACK” Then Final Output = “No Trade”
If Output = “NO_TRADE” AND PreviousOutput = “LAY” Then Final Output = “No Trade”

______________________________________________________________________

CPerry, The User doesn't need to know how the code will count previous signals or outputs. He just has to tell the Developer and Coder how to use them. The Coder will determine how to implement the counting.

Quote:

---

This has taken a lot of visualising and crude sketches in my notepad to get my head around,

---

Oh, Yeah. Me too, as well as a lot of research and experimental coding. I also installed and am learning two new programs so I can build a half arsed help file for this

I laid in bed about an hour last night thinking about this

Got a problem...

• BA updates about 20 times a second
• That means that the longest series average, (MACD_A at Series_Count = 24,) will be Ready in about 1 second
• That means that about 3 updates after the first price change in any Brand, the Back or Lay command for that brand will be placed.

All numbers Ballparked, IOW aproximated:
BA Update Tiks = 20/sec, 1200/Min, or 12000/10min
Price Changes for any given Brand = ???, once/min, once/3min, or what.

Solution?
Perry and otl, must provide, outside my domain.

• Increase series Lengths by a factor of 1000? ie MACD_A_SeriesCount = 24000, etc
• Trigger the Command Calculators only on Price Changes?
• Design the Helper App so that it Tracks Price changes and adjusts those for Time Since previous Change per otl's Adjustment Factor equation?
• Some combination of all those?

I understand that not all Values used in the Helper App are prices, but you get the idea.

_______________________________________________________

It is obvious to me that Calculating BA Commands on every update will not work. BA updates the Brand Data Table many thousands of times faster than prices change.


Designing the Helper App to use Value Changes has the advantage that only those Brands that incur a Change will be Active, all other Brands will be Idle, using no CPU cycles.

___________________________________________________________

Also consider a Brand that has little to no Price activity. Before the deadline for placing Commands, the Helper should issue what Command? Is this even a Concern given the way Bet Angel operates?

Your analysis is correct, and I agree with you. There is one small detail where I had made a different assumption. I had assumed that the update interval associated with a series count of 12, 26 and 9 was 200ms. Thus I used this as the denominator when calculating the time series in my analysis of the race data.
This is only a factor of 4 where you have correctly pointed out any adjustment needs to be in the thousands.
My analysis of the actual price movement would appear to show that price changes occured in less than 1 second only 24 times in the whole race!!

Can you guesstimate how many times the top three or five brands changed in the entire race?

If we calculate based on Changes, how many Changes are required to get usable results?

I am guessing that not too many Brands see a Change in the same BA Update, which means the Helper App has some Time available for other things.

Another possibility is waiting til the last minute to output the BA Commands. That decision is waaaaaaay out of my bailiwick.

I think the Helper App can keep a check of last times data, and see if any item of data for a brand has changed, if is has, it recalculates. The recalculation can take account of the time since the last price change ( not the last update) This throws the whole idea of doing MACD and RSI on a series count out the window, the calculatoin will need to be based on Time. This is perfectly possible.
I am not sure how many price changes we need to get usable results, I will try a bit of testing to see if I can get some useful results in this area.
The results of this testing might be the way round the problem I highlighted with the lack of movement in the Race data that Cperry posted. The helper app can quite easily count the number of price changes per minute and if it is insufficient, it can avoid triggering a nonsensical bet.

Be aware that Changes are on a per Brand consideration. One Brand may have sufficient data long before a different Brand.

The Helper InputDataParser UDO is already tasked with separating each Brand's data stream. It won't be hard to have it Check for changes before only distributing Data Streams that have changed. With only a few Brand UDOs active at any time, there should be enough BA Idle time to still complete a Control Console update before the next BA update.

I still plan on running significant timing tests as this comes together. Now I see why Perry says that missing a few BA updates is not critical. The BA update rate is about 1% of 5 seconds. That's if the Helper misses a BA Update.

Quote:

---

Be aware that Changes are on a per Brand consideration. One Brand may have sufficient data long before a different Brand.

---

I appreciate this, I think what this means is that the "price change count per minute" validation needs to be done on a per brand basis.

The Helper InputParser will be sending a Data Stream, (Or I guess Data Block is a better term since it will not be an actual Stream of data,) to each Brand that will include a TimeStamp.

ATT, I think that if each Brand's BACommand Caclulator maintained an array with 8196 slots, the array would be big enough to hold all Changes over a ten minute period.

For speed, I would rather use static arrays, ice Dyanic arrays, ReDim Preserve takes a bit of CPU Cycles and might result in non contiguous memory blocks The size difference factor is 2.5 to 5. That is pretty significant when there are 17 changing values in the BA Data Table. My bad math says 4M Array Cells to handle 30 Brands

My question is: Do I have to remember all Changes, or just all the Formula outputs when a Change occurs?

I think we may be using the wrong paradigm. I have been wanting to accommodate using Bet Angel for Sports Venues and for Stock Markets. but it's now obvious that The Helper can't do all that.

Basic Question: How does one make a Profit from betting at Horse Races?

• Compare the Price fluctuations between all Brands/Horses?
• Preselect which brands to watch?
• Bias certain Horses over others?
• :dunno:

Quote:

---

ATT, I think that if each Brand's BACommand Caclulator maintained an array with 8196 slots, the array would be big enough to hold all Changes over a ten minute period.

---

With rates of change that I have found I would expect this to be plenty big enough. I looked at the first race in the data that Cperry posted and this only last 2 minutes, in that time there were only 60 changes in price, with only 6 different values.

Quote:

---

For speed, I would rather use static arrays,

---

I totally agree,

Quote:

---

My question is: Do I have to remember all Changes, or just all the Formula outputs when a Change occurs?

---

when this system is all up and running and working you only need to save the formula outputs for the last time and the trigger for the "look back" period.
However when trying to debug what went wrong it is almost impossible to do this unless we have a record of what actually happened. So initially we need to store every change and every calculated value of the formula outputs, and all the triggers. Doing this may limit our throughput, but we can stop recording bits as we get confident that it is working correctly which could improve our througput.

Quote:

---

How does one make a Profit from betting at Horse Races?

---

This is exactly the question that I am asking probably for a different reason, I am used to stock markets where prices move fairly rapidly but also move over a long time, e.g. on Day trading , there are 8 hours in the day which is 28800 seconds, with a 1 second update rate the price of an index (e.g. DOW, FTSE100) is very rarely static between updates. Movement is often 30 to 60 points in a day sometimes far more. with a spread of 1 point there is plenty of opportunity to make a profit
Over a week there is far more movement,
.
I can't see how anybody can predict what is going to happen with only 90 updates between start and finish and a price movement that only takes up 10 different values. Trends have got no time to get established. top and bottom ranges of movement have got insufficient data to establish. There is not enough data to establish any common pattern

I suspect people make a profit on horses by knowing about horses. It is similar with stocks you can make profits on Stock purely by researching the Finance and balance sheets, or you can make a profit on stock purely by plotting indicators; MACD, RSI, etc and just using those. Using the two together, is particularly effect, e.g investigate which stock you want to buy, and then use the indicators and charts to work out the best time and or price to buy at. I wonder whether this is what the people on Betangel who are making a profit are doing. It is still a mystery to me as to how they can use MACD and RSI on this sort of data.
( Assuming the two races cperry recorded were typical)

Hi gents,

Apologies again for lack of replies over the weekend (I often work away and find it difficult to message) In fact right now I need to head off again but wanted message before doing so - should be back to normal as of tomorrow!

Just skimming over the last few messages it looks like this system needs some rethinking. I'll jot down a few notes now to ponder before tomorrow but basically, it is my understanding that the best of the best in these markets know very little about horse racing. The guy who owns BetAngel is called Peter Webb and I have watched hours of his videos on YouTube, read articles involving him over the last nearly two decades and he regularly says that he knows very little about horse racing, in fact it fogs judgements; he simply understands how the markets behave. The way people make money is by simply watching the whole market i.e. more than one horse at the same time.

I assumed the secret lay in looking at them individually with these indicators but perhaps I was thinking too simply. The biggest factor that affects the price movements is volume, where the money is. Maybe a MFI instead of a RSI would work better as there's a bigger focus on the volume. The difference is we look at all horses (brands) simultaneously.

If the favourite horse's odds went up (favourite always placed as brand 1) then the second/third/maybe even fourth favourite would likely go down. The opposite is also true. If the second favourite and third favourite's odds both went up, it may be a sign that the first favourites is about to drop. Instead of looking at each brand independently of one another at looking for their own support/resistance levels maybe the secret lies in the price and volume of each brand in relation to each other. In this case, we need a system that looks at where the money is being traded (if the 5th/20th - in some races there can be loads of brands! - horses are trading pennies then they won't affect the prices of the other horses) watches those heavily traded brands and looks for where the money is forcing other prices to forecast up/down/reversebounce trends.

I think before we explore this, I should log some more information on multiple brands and prices simultaneously. If I could capture ALL the price changes, first:third back prices, first:third lay prices, total market volume and each brand's traded volume for 4 brands over a succession of several races (using the refresh option I will be using when our system actually goes live) then we may get a more detailed picture to analyse before making our next move. I will capture around an hour's worth of information over the next day or two and get back to you!

Would something like this do the job would you imagine?:

Code:

---

Option Explicit

Const RecordingLength = 5000 'per individual race of which I'll record several
Dim Timer As Object
Dim Running As Boolean
Dim Recording
Dim i As Long
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Running
    Then
    Exit Sub
 
    Recording(i, 1) = Target.Address
    Recording(i, 2) = Timer
    Recording(i, 3) = Range ("G9")
    Recording(i, 4) = Range ("G10")
    Recording(i, 5) = Range ("F9")
    Recording(i, 6) = Range ("F10")
    Recording(i, 7) = Range ("E9")
    Recording(i, 8) = Range ("E10")
    Recording(i, 9) = Range ("H9")
    Recording(i, 10) = Range ("H10")
    Recording(i, 11) = Range ("I9")
    Recording(i, 12) = Range ("I10")
    Recording(i, 13) = Range ("J9")
    Recording(i, 14) = Range ("J10")
    Recording(i, 15) = Range ("K9")
    Recording(i, 16) = Range ("K10")
    Recording(i, 17) = Range ("G11")
    Recording(i, 18) = Range ("G12")
    Recording(i, 19) = Range ("F11")
    Recording(i, 20) = Range ("F12")
    Recording(i, 21) = Range ("E11")
    Recording(i, 22) = Range ("E12")
    Recording(i, 23) = Range ("H11")
    Recording(i, 24) = Range ("H12")
    Recording(i, 25) = Range ("I11")
    Recording(i, 26) = Range ("I12")
    Recording(i, 27) = Range ("J11")
    Recording(i, 28) = Range ("J12")
    Recording(i, 29) = Range ("K11")
    Recording(i, 30) = Range ("K12")
    Recording(i, 31) = Range ("G13")
    Recording(i, 32) = Range ("G14")
    Recording(i, 33) = Range ("F13")
    Recording(i, 34) = Range ("F14")
    Recording(i, 35) = Range ("E13")
    Recording(i, 36) = Range ("E14")
    Recording(i, 37) = Range ("H13")
    Recording(i, 38) = Range ("H14")
    Recording(i, 39) = Range ("I13")
    Recording(i, 40) = Range ("I14")
    Recording(i, 41) = Range ("J13")
    Recording(i, 42) = Range ("J14")
    Recording(i, 43) = Range ("K13")
    Recording(i, 44) = Range ("K14")
    Recording(i, 45) = Range ("G15")
    Recording(i, 46) = Range ("G16")
    Recording(i, 47) = Range ("F15")
    Recording(i, 48) = Range ("F16")
    Recording(i, 49) = Range ("E15")
    Recording(i, 50) = Range ("E16")
    Recording(i, 51) = Range ("H15")
    Recording(i, 52) = Range ("H16")
    Recording(i, 53) = Range ("I15")
    Recording(i, 54) = Range ("I16")
    Recording(i, 55) = Range ("J15")
    Recording(i, 56) = Range ("J16")
    Recording(i, 57) = Range ("K15")
    Recording(i, 58) = Range ("K16") 
    Recording(i, 59) = Range ("C2")
   
    i = i + 1
    If i > RecordingLength Then
        MsgBox "Recorded " & i - 1 & " Updates"
        Running = False
        SaveRecording
    End If
End Sub
 
Private Sub SaveRecording()
    Worksheets.Add Before:=Sheets("Bet Angel")
    ActiveSheet.Range("A1:G" & CStr(RecordingLength)) = Recording
    Set Timer = Nothing
    If i >= RecordingLength
    Then
        MsgBox "Logging Complete"
    End Sub
   
    Public Sub StopLogging()
        If Running Then
            Running = False
           
            SaveRecording
        End If
        Set Timer = Nothing
        MsgBox "Stopping Logging"
    End Sub
   
    Public Sub StartLogging()
        ReDim Recording(1 To RecordingLength, 1 To 59)
        Set Timer = New clsMicroTimer
        Recording(1, 1) = Range "Address"
        Recording(1, 2) = Range "MicroTime Time"
        Recording(1, 3) = Range "B1 First Back Price"
        Recording(1, 4) = Range "B1 First Back Money"
        Recording(1, 5) = Range "B1 Second Back Price"
        Recording(1, 6) = Range "B1 Second Back Money"
        Recording(1, 7) = Range "B1 Third Back Price"
        Recording(1, 8) = Range "B1 Third Back Money"
        Recording(1, 9) = Range "B1 First Lay Price"
        Recording(1, 10) = Range "B1 First Lay Money"
        Recording(1, 11) = Range "B1 Second Lay Price"
        Recording(1, 12) = Range "B1 Second Lay Money"
        Recording(1, 13) = Range "B1 Third Lay Price"
        Recording(1, 14) = Range "B1 Third Lay Money"
        Recording(1, 15) = Range "B1 Last Traded Price"
        Recording(1, 16) = Range "B1 Total Volume at Last Traded Price"
        Recording(1, 17) = Range "B2 First Back Price"
        Recording(1, 18) = Range "B2 First Back Money"
        Recording(1, 19) = Range "B2 Second Back Price"
        Recording(1, 20) = Range "B2 Second Back Money"
        Recording(1, 21) = Range "B2 Third Back Price"
        Recording(1, 22) = Range "B2 Third Back Money"
        Recording(1, 23) = Range "B2 First Lay Price"
        Recording(1, 24) = Range "B2 First Lay Money"
        Recording(1, 25) = Range "B2 Second Lay Price"
        Recording(1, 26) = Range "B2 Second Lay Money"
        Recording(1, 27) = Range "B2 Third Lay Price"
        Recording(1, 28) = Range "B2 Third Lay Money"
        Recording(1, 29) = Range "B2 Last Traded Price"
        Recording(1, 30) = Range "B2 Total Volume at Last Traded Price"
        Recording(1, 31) = Range "B3 First Back Price"
        Recording(1, 32) = Range "B3 First Back Money"
        Recording(1, 33) = Range "B3 Second Back Price"
        Recording(1, 34) = Range "B3 Second Back Money"
        Recording(1, 35) = Range "B3 Third Back Price"
        Recording(1, 36) = Range "B3 Third Back Money"
        Recording(1, 37) = Range "B3 First Lay Price"
        Recording(1, 38) = Range "B3 First Lay Money"
        Recording(1, 39) = Range "B3 Second Lay Price"
        Recording(1, 40) = Range "B3 Second Lay Money"
        Recording(1, 41) = Range "B3 Third Lay Price"
        Recording(1, 42) = Range "B3 Third Lay Money"
        Recording(1, 43) = Range "B3 Last Traded Price"
        Recording(1, 44) = Range "B3 Total Volume at Last Traded Price"
        Recording(1, 45) = Range "B4 First Back Price"
        Recording(1, 46) = Range "B4 First Back Money"
        Recording(1, 47) = Range "B4 Second Back Price"
        Recording(1, 48) = Range "B4 Second Back Money"
        Recording(1, 49) = Range "B4 Third Back Price"
        Recording(1, 50) = Range "B4 Third Back Money"
        Recording(1, 51) = Range "B4 First Lay Price"
        Recording(1, 52) = Range "B4 First Lay Money"
        Recording(1, 53) = Range "B4 Second Lay Price"
        Recording(1, 54) = Range "B4 Second Lay Money"
        Recording(1, 55) = Range "B4 Third Lay Price"
        Recording(1, 56) = Range "B4 Third Lay Money"
        Recording(1, 57) = Range "B4 Last Traded Price"
        Recording(1, 58) = Range "B4 Total Volume at Last Traded Price"
        Recording(1, 59) = Range "Total Market Volume"
        Recording(2, 1) = Now
        Recording(2, 2) = Timer
        i = 3
        MsgBox "Logging Changes"
        Running = True
        End Sub

---

Useful videos/articles on trading horse markets if you're interested:
https://www.ukfootballtrading.com/pr...ng-on-betfair/
https://www.youtube.com/watch?time_c...&v=b8Av8-Dvgb4
https://www.youtube.com/watch?v=_TKe...t_BoD&index=10
https://www.guidetomatchedbetting.co...g/horse-races/

CPerry

Hi Cperry,
I watched the videos with interest. One thing that is immediately apparent is the way the market works in horse racing is similar to stock markets and the display is similar to a level 2 stock market dispaly which shows , orders at bid and ask and the volume associated with eahc and all the trade as they hap-pen. see this video for an expanation;
https://www.youtube.com/watch?v=LlezVCHvjBU

One major difference in the horse racing is that the price of one horse affects the other horses. ( because the sum of all the prices is always going to be less than certainty because the book makers make sure that happens.)
The videos I looks at all showed the operator using the "ladder" or what I would call the "level 2" to determine the trend or the lack of a trend in a horse.
They were not using RSI , MACD. I did notice the display in the second video looked like it was display PSAR. (Parabolic Stop and Reverse Indicator)
but he wasn't using it.
The way the price was moving was very similar to the recordings that you have already done. There was very little movement in price often oscillating between the two or three prices. Then may be a small trend establishes, but not over a great range.

looking at the way these markets move, I can think of a number of things which are going to be very difficult to automate:

1: The price of one horse is affected by the what is happening in the other horses
2: The Movement is in coarse steps and it would appear that the only useful indication of a significant movement is by detecting complicated changes in the bid/ask ladder ( back/lay ladder to you)
3: At least one of the videos was talking about using "research" ie papers, pundits etc. An automated system can't do this.

@cperry
There was another item that caught my eye in your links:

Quote:

---

This is because the price increments change in the following way:Between prices 1.01 and 2.0, odds movements occur in increments of 0.01
Between prices 2.0 and 3.0, odds movements occur in increments of 0.02
Between prices 3.0 and 4.0, odds movements occur in increments of 0.05
Between prices 4.0 and 6.0, odds movements occur in increments of 0.10
Between prices 6.0 and 10.0, odds movements occur in increments of 0.20
Between prices 10.0 and 20.0, odds movements occur in increments of 0.50
Between prices 20.0 and 30.0, odds movements occur in increments of 1.00
Between prices 30.0 and 50.0, odds movements occur in increments of 2.00

---

I made this into a table and calculated the percentage increment that this relates to. This is also the "spread" between buying and selling. or backing and laying

Price between	Price between	Increment	Spread %	Spread %
1	2	0.01	1.00	0.50
2	3	0.02	1.00	0.67
3	4	0.05	1.67	1.25
4	6	0.1	2.50	1.67
6	10	0.2	3.33	2.00
10	20	0.5	5.00	2.50
20	30	1	5.00	3.33
30	50	2	6.67	4.00

A minmum spread and increment of 0.5% seems quite a high margin to get over, the the race data that you posted the spread/increment was over 1.5%

CPerry
I forgot to answer your query about the recording code,
One thing you need to learn is how to write efficient code. Thie is particularly true for your application where time is of the essence.
Every access to the spreadsheet takes a lot of time. with your solution you are accessing the spreadhseet 57 times because you are loading each variable in sequence. This is going to take ages ( in a real time situation) and so should be avoided.
The way to speed this up is to load all the data into a variant array and then copy it from the variant array to "Recording"

Code:

---


datar = Range("C1:K16")




      recording(i, 4) = datar(9, 5)
        recording(i, 5) = datar(9, 4)
        recording(i, 6) = datar(9, 4)
        recording(i, 7) = datar(9, 3)
        recording(i, 8) = datar(9, 3)
        recording(i, 9) = datar(9, 6)
        recording(i, 10) = datar(9, 6)
        recording(i, 11) = datar(10, 7)
        recording(i, 12) = datar(10, 7)
        recording(i, 13) = datar(10, 8)
        recording(i, 14) = datar(10, 8)
        recording(i, 15) = datar(10, 9)
        recording(i, 16) = datar(10, 9)
        recording(i, 17) = datar(10, 5)
        recording(i, 18) = datar(11, 5)
        recording(i, 19) = datar(11, 4)
        recording(i, 20) = datar(11, 4)
        recording(i, 21) = datar(11, 3)
        recording(i, 22) = datar(11, 3)
        recording(i, 23) = datar(11, 6)
        recording(i, 24) = datar(11, 6)
        recording(i, 25) = datar(12, 7)
        recording(i, 26) = datar(12, 7)
        recording(i, 27) = datar(12, 8)
        recording(i, 28) = datar(12, 8)
        recording(i, 29) = datar(12, 9)
        recording(i, 30) = datar(12, 9)
        recording(i, 31) = datar(12, 5)
        recording(i, 32) = datar(13, 5)
        recording(i, 33) = datar(13, 4)
        recording(i, 34) = datar(13, 4)
        recording(i, 35) = datar(13, 3)
        recording(i, 36) = datar(13, 3)
        recording(i, 37) = datar(13, 6)
        recording(i, 38) = datar(13, 6)
        recording(i, 39) = datar(14, 7)
        recording(i, 40) = datar(14, 7)
        recording(i, 41) = datar(14, 8)
        recording(i, 42) = datar(14, 8)
        recording(i, 43) = datar(14, 9)
        recording(i, 44) = datar(14, 9)
        recording(i, 45) = datar(14, 5)
        recording(i, 46) = datar(15, 5)
        recording(i, 47) = datar(15, 4)
        recording(i, 48) = datar(15, 4)
        recording(i, 49) = datar(15, 3)
        recording(i, 50) = datar(15, 3)
        recording(i, 51) = datar(15, 6)
        recording(i, 52) = datar(15, 6)
        recording(i, 53) = datar(16, 7)
        recording(i, 54) = datar(16, 7)
        recording(i, 55) = datar(16, 8)
        recording(i, 56) = datar(16, 8)
        recording(i, 57) = datar(16, 9)
        recording(i, 58) = datar(16, 9)
        recording(i, 59) = datar(16, 1)

---

You might be interested in a thread I started about how to use variant arrays instead of ranges:

see:
http://www.vbaexpress.com/forum/show...+variant+array