Bet Angel and Excel

Quote:

---

Statistically, are those time differences large enough to worry about, or can they be treated in calculations as fixed intervals?

---

My testing would appear to show that the variation in update rate will have a significant effect on the the timing of a trigger, unless we take account of the time since last update in which case it is significantly improved. So I don't believe we can treat them as fixed intervals. However it shoudl be easy enough with your micro timer to take the time since last update into account, I thought we had settled on this.

Quote:

---

If the Helper App can do this in >=66ms, we're golden for calculating all BA Updates:

---

I agree , we don't have to worrry about a scheduler if we can get the all the calculations done in 66 ms. We just do it all, every update.
If we can't get it all done in 66ms , we have a relatively easy way round it by introducing a scheduler. The helper app keeps a counter and calls different UDO depending on the count.

Quote:

---

unless we take account of the time since last update in which case it is significantly improved.

---

Can one UDO recieve the LastUpdatedCell Update Time and distribute some value to all the Formula and Calculator UDOs?

Or does it need to the the last BrandData Update time, or does the value need to be specific to each ormula and Calculator?.

Another scenario. Can we use the Bowling Average time of the last 20 or so updates, instead of every Update time?
I'm just throwing ideas o the table. Not making decision until I have to.

The races start at 12:40 tomorrow. They end at 16:00 tomorrow, must have finished at the time today so I’ll do that test for you tomorrow Offthelip! I hope you’ve had a pleasant week away. I’ll try and do it for three consecutive races as they would flow when my system is operational and two single ones just in case this has any implications that I am not thinking about. I will also x10 the amount it records compared to normal so it should collate approx. 25/30mins of logging over two or three separate races.

Next, I see what you mean DanT with the ‘DataFields’ class module, how you’ve arranged it all. I will do the same to the other three class modules now but I will copy and paste the code (inc. the ‘DataFields’ code) directly into version 2.1 of the document and rename the new version, version 3 because the document you most recently sent is missing the ‘Control Panel’ and the ‘Bet Angel’ tab.

Already, I have taken out the ‘Smoothing’ variables for each of the MACD settings and the WOM adjuster within the ‘Formula Parameters’ class module but I have kept the BACK/LAY trigger counts and added in the following as Offthelip provided:
RSI_Low_and_Rising_trigger_point
RSI_High_and_Falling_trigger_point
RSI_MID_and_Rising_trigger_point
RSI_MID_and_Falling_trigger_point
MACD_Signal_Line_Cross_Zero_And_Rising_Trigger
MACD_Signal_Line_Cross_Zero_And_Falling_Trigger
MACD_Histogram_Cross_Zero_And_Rising_Trigger
MACD_Histogram_Cross_Zero_And_Falling_Trigger
WOM_Low_and_Rising_trigger_point
WOM_High_and_Falling_trigger_point

WOM_MID_and_Rising_trigger_point
WOM_MID_and_Falling_trigger_point

Though I haven’t given each of these dedicated cells in the ‘Control Panel’ tab, do you think this would be best, to have a cell for each of these trigger points? I’ll let you ‘wire’ this up once I have the code typed up and sent across to you.

Expect another message from me tonight once I've gone through everything,
CPerry.

Quote:

---

Though I haven’t given each of these dedicated cells in the ‘Control Panel’ tab

---

I mean the 'Formula Parameters' tab but nonetheless my point still stands, wiring up the code to the sheets is something I'm unsure of. Ok back to work!

Unused inputs are not a problem to code for. Missing an input that you might want in the future means that much of the preexisting code would need to be edited to add the new inputs. As I am designing the App, unused preexisting inputs can be implemented by merely adding an Atomic (lowest and smallest) level UDO. Ie, a Formula UDO or two and maybe a Calculator UDO. FYI What I am calling a Calculator is the Object that takes the outputs of various Formulas and develops a Back/Lay signal output

Quote:

---

Though I haven’t given each of these dedicated cells in the ‘Control Panel’ tab, do you think this would be best, to have a cell for each of these trigger points? I’ll let you ‘wire’ this up once I have the code typed up and sent across to you.

---

All inputs from Bet Angel and the Control Panel need their own Cell. The 4 Data Storage UDOs you are writing are what allows the rest o the code to "wire" itself "up" to the Worksheets... so to speak.



What will you, or the Helper App, be using the Instructions section of the BA sheet for?

I am assuming that the Helper output is to be sent to the Brand "Signal" Column, (the one after the last Brand Data Column.) Is this correct?

Ok this is taking a little longer than I assumed aha I have completed three out of four class modules so far, just the 'Formula Parameters' one to go. I will resume this around early lunchtime tomorrow with the adding of cells on the control panel for the additional parameters and their codes in this module. Then I will send the finished file across along with the file of data for Offthelip to analyse.

Speaking of which, taking the code from the logger, to capture the: price, money (the money at current price, the money traded on the brand in total or the market volume?) and update time of Brand 1 (Cells G9, G10, {C3, K9 or C2 depending on your answer to the choices in bold}) do I have to change the red parts in the following code?

Code:

---

Option Explicit
Const RecordingLength = 10000 this number will be x10 bigger for tomorrow's logging 'Records.
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
    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:B" & 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 2)
    Set Timer = New clsMicroTimer
        Recording(1, 1) = "Address"
    Recording(1, 2) = "MicroTime Time"
   
Recording(2, 1) = Now
    Recording(2, 2) = Timer
  i = 3
        MsgBox "Logging Changes"
    Running = True
End Sub

---

Finally for tonight, with regard to the 'Instructions Section' of the app, this is where orders get sent back through to BetFair via BetAngel so for example if all of the triggers went off on brand 1, I would need the instruction section to fill a couple of cells to place the order.
L9: The keywords "BACK/LAY/CLOSE TRADE"
M9: Current odds (Which would be G9 for a "BACK" or H9 for a "LAY")
N9: Stake which would be a static 2 (to indicate £2 to begin with and I would scale this up if my overall balance grew consistently)

As far as I'm aware, the order has to go through this section.

CPerry.

Do me a favor, Send me back just the two original sheets, edited, of course, and the Code modules. Re-version the Name

Then put in all the sheets you want, then change the name. Remember, we are still in the Design Stage of this Project. There will be many changes by the time we get Finished.

The first step in a Project like this: The Developer and the User design the User Interface. That Interface is Excel and the Three sheets in one of the examples I sent you, and a Bet Angel template.

Look at the code in ThisWorkbook of Perry's Data Storage UDOs. Put that code in all ongoing Workbooks.

When you're writing code, Compile often and after a good compile, BackUp the book. When satisfied with all the code in the book, manually SaveAs it to a new name or Version number. Those Are the Books I share with you. The ones without any Timestamp in their Names. I have an Archive older that I move all books I'm done with into.

Hi Cperry,
I suggest you record all of the money terms. I have updatedthe code to show what I think will do the trick, but I haven't compiled it so i have probably made a silly error ( I usually do!!)

Code:

---

Option Explicit Const RecordingLength = 10000 this number will be x10 bigger For tomorrow 's logging 'Records.
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
' add in the extra bits to record
    Recording(i, 3) = Cells("G9")
    Recording(i, 4) = Cells("G10")
    Recording(i, 5) = Cells("C3")
    Recording(i, 6) = Cells("K9")
    Recording(i, 7) = Cells("C2")
' end of the new bit
    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")
' change the number of columns written out
    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 7) ' Change this to 7 columns to record everything
        Set Timer = New clsMicroTimer
        Recording(1, 1) = "Address"
        Recording(1, 2) = "MicroTime Time"
' Add in the new headers
        Recording(1, 3) = "Price"
        Recording(1, 4) = "Money"
        Recording(1, 5) = "Money at Current price"
        Recording(1, 6) = " the money traded on the brand in total"
        Recording(1, 7) = "market volume"
' end of the new bit       
        Recording(2, 1) = Now
        Recording(2, 2) = Timer
        i = 3
        MsgBox "Logging Changes"
        Running = True
    End Sub

---

Which of these Money terms are you plannin to use to calculate the WOM indicator?

Quote:

---

Though I haven’t given each of these dedicated cells in the ‘Control Panel’ tab, do you think this would be best, to have a cell for each of these trigger points? I’ll let you ‘wire’ this up once I have the code typed up and sent across to you.

---

I do very much suggest that you have a dedicated cell for each of these terms on the control panel. this will allow you to easily change the trigger points during testing and between each race without neeeding to change the code. You really don't want to change code while it is running. It is possible, but it is difficult to do without causing it to stop due to compile error.

Hi Samt

Quote:

---

Can one UDO recieve the LastUpdatedCell Update Time and distribute some value to all the Formula and Calculator UDOs?

---

Yes it can be done this way. However the value to be distributed is simply the number of milliseconds since that brand was last updated. So to me the two bits of information associated with an update go together, Price and the time since last update.
So I think that while we are teaching Cperry good practices in coding, which should also stick to the good principles associated with cohesion and coupling and keep these two bits of data together. So every call for a bit of data from the BAangel UDO should include the value and the time since last update.
Note the time since last update is going to be Brand specific, i:e it is the time since that Brand was updated.

I can see a sequence of updates being something like this

0.00 Brand 1 Price
0.10 Brand 2 Price
0.15 Brand 1 Price Brand 1 delta = 0.15
0.25 Brand 2 Price Brand 2 delta = 0.15
0.35 Brand 2 Price Brand 2 delta = 0.1
0.36 Brand 1 price Brand 1 delta = 0.21
0.50 Brand 2 price Brand 2 Delta = 0.15
0.51 Brand 1 price Brand 1 delta = 0.15
etc
It is the Brand 1 and Brand 2 delta that should be distributed with the updated data.

Quote:

---

Or does it need to the the last BrandData Update time, or does the value need to be specific to each ormula and Calculator?.

---

The same value is used in all formula, all the indicators have some sort of "lag" in them this is just a scaling factor for the lag constant.

Quote:

---

Another scenario. Can we use the Bowling Average time of the last 20 or so updates, instead of every Update time?

---

Doing this will seriously reduce the accuracy. Since you have found an excellent way of getting a reasonably accurate "time since last update" let's use it.
Note: I think it is easier and faster to calculate the exact time since last upate than calculating the average over the last 20!!!

Quote:

---

So every call for a bit of data from the BAangel UDO should include the value and the time since last update.
Note the time since last update is going to be Brand specific, i:e it is the time since that Brand was updated.

---

"Time since last update" Uh., I don't think so. The time of this update, yes. The Brand UDO can keep track of the time since last update. The BA Output UDO, all the BA UDOs, will be kept as streamlined as possible, since all their activity is included in the Update cycle time.

Therre are 7 "Pastes" in an Update, which take from ~30 to ~60ms. One of those "Pastes" is the entire Data Set or all Brands. IOW, all Brands have the same Update Time.

The last Update "Paste" of the series is C2:C6, This update is what will trigger the Output UDO to pass the Updates to the Helper.

As these values get passed down to the bottom of the stack, they trigger the Formula UDOS, When all relevant Formulas pass their results up to their Calculator UDOs,(Side bar: The Calculators are what I am calling the decision trees that decide which Signal to pass up,) it Triggers the Calculator. As soon as the Calulator makes a Decision, it passes it's choice to the Helper SheetWriter UDO. When one of the SheetWriter's Output Arrays is full, it writes to the ControlPanel sheet.

Since all Brands, and all Formulas, will be using the same "Time Since Last Update" The Helper DataParser Object, (AKA Input class,) must break all the Brands apart and pass that data separately to each Brand. It can calculate Elapsed time since last update and pass that along also. I suspect that this operation, Breaking down Data By Brand will be the main bottleneck.

At the moment, I am working on building a stub that will contain only the BAOutput UDO and the SheetWriter UDO. Those will need to be tested and timed, then perfected, before I will go any deeper into the Project. For those, I need Perrys DataStorage UDOs and I need to write the trigger code into the Bet Angel Sheet's Code Module.

Quote:

---

Therre are 7 "Pastes" in an Update, which take from ~30 to ~60ms. One of those "Pastes" is the entire Data Set or all Brands. IOW, all Brands have the same Update Time.

---

I was a bit puzzled by this, probably because I haven't been following this entire thread closely. Why are there 7 pastes? what are they? Do they really take 30 to 60ms? Since "pasting" is one of the slowest things to occur, I think we need to design a system that only does one paste per brand or one paste per update, which comes to much the same thing. Are you minimizing the size of the "pasted" array too?


I believe that Betangel updates each Brand separately, so an worksheet change event will be triggered when when that Brand is updated. The time we need to record is the time that the Betangel update of that brand occured, not the time that we wrote the whole array out.

Quote:

---

"Time since last update" Uh., I don't think so. The time of this update, yes. The Brand UDO can keep track of the time since last update. The BA Output UDO, all the BA UDOs, will be kept as streamlined as possible, since all their activity is included in the Update cycle time.

---

This makes perfect sense, the brand should keep track of when the last update occured and calculate the delta

I said "Paste" in quotes, but, I am assuming it is really the Bet Angel Application assigning Array Values to a Range

Update Order on Bet Angel Sheet:

Data Category	Address	MicroTime Time	Single Update duration
Not Used	$A$1:$B$1	81667.2883672192
Info Fields	$G$1:$H$1	81667.2980212744	0.0096540552
Info Fields	$F$2:$F$4	81667.3010967205	0.0030754461
Data Fields	$A$9:$K$24	81667.3085517632	0.0074550426
Instruction Fields	$T$9:$AE$24	81667.3120553546	0.0035035914
Instruction Fields	$AF$9:$AJ$24	81667.3168056675	0.0047503129
Info Fields	$C$2:$C$6	81667.3204661169	0.0036604495

Table from 'Race 2 3-0 Mins - Truncated_SamT.xls,' Sheets(Timing Data). "Data Fields" is all the Brands' Financial data.

Quote:

---

I think we need to design a system that only does one paste per brand or one paste per update, which comes to much the same thing. Are you minimizing the size of the "pasted" array too?

---

ATT, the Helper App is only interested in the BA All Brands Data fields. ATT, The Helper will only update the Control Panels 'Brands' Table, and it will be by assigning the values of one array to that entire table all at once.

When we have completed and tested the Helper App, CPerry should Know enough to be able to add features as he pleases.

I made a few changes to the code Offthelip and got it working with 10 updates just fine but when I tried doing it for 100000 updates (approx 30 minutes across 3 races) it came up with:

Run-time error ‘1004’”
Application-defined or object-defined error

And highlighted the following line:

Code:

---

ActiveSheet.Range(“A1:G” & CStr(RecordingLength)) = Recording

---

Now I was able to debug around 3 parts to get it working with 10 updates but nothing I seem to do fixes this line without returning a error. Any ideas?

Quote:

---

When we have completed and tested the Helper App, CPerry should Know enough to be able to add features as he pleases.

---

Yes, I already have a few ideas on how to develop this further once things are wired up and ticking. Can't wait to invest even more time into this project!

it looks to me that some value in the array gets some corrupt data in it or something that causes an error when written to the worksheet.
What I tend to do when I run it to this problem like this, is to write the array out a line at atime and see where it falls over.

Add this code instead of the failing line:

Code:

---

Dim dummy(1 to 1 , 1 To 7) As Variant
For i = 1 To recordinglength
 For j = 1 To 7
  dummy(1, j) = recording(i, j)
 Next j
 
 ActiveSheet.Range(Cells(i, 1), Cells(i, 7)) = dummy
Next i

---

Before you do this change recording length to something smaller than 1000000 because otherwise it will take all night!!
Try it with 1000 and see if it falls over, if that works try it with 5000 , etc.
This is another normal part of learning to code: writing code to try and debug the problem you encounter.

Awesome! I'll give this a try in a bit, just going to grab some food and have a small break. In the meantime, TA_DAHHH. Please find attached the newest workbook with the coding complete for all 4 class modules and added cells on the 'Formula Parameters' tab :D

Attachment 20940

CPerry.

CPerry,

I introduce you to the Coders concept of "Business Rules.'

Very simple Business Rules read like If this then that. For example "If Three Consecutive Backs from Formula, then Signal = Back"

The Calculator UDOs I have been mentioning are actually Business Rule implementing UDOs.

The textual description of one of these Calculator Objects will include the Formulas it uses, by name, and the values they put out, the decision tree or Business Rules it uses and the output values it generates.

A crude, unworkable, imaginary example, using what I remember right now about the MACD Calculator

Quote:

---

Uses, Macd_A, Macd_B, and Macd_C Formulas
MACD_A Outpurt - Double
MACD_B output = Single
MACD_C 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_C 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 CountMACD_A_AboveC => 42 then Output "BUY"
If CountMACD_B_BelowC => 42 Then Output "LAYOFF"
If Something Else = "No TRade" Then OutPut = "No Trade"
If PreviousOutPut = "BUY" or = "LAYOFF" or = "NO TRADE" Then OutPut = PreviousOutput

---

Basically, everything a Coder must know, without referencing any other documents, to code a Calculator UDO

Your homework: Define and Refine the Business rules of your Project into UDO Descriptions. I'll help you polish the first one's rough draft, then you can do the rest by yourself

Hi Cperry:
I just checked your Control panel sheet on your latest workbook and I saw this rule:

Quote:

---

if total back/lay triggers = 2, AND previous command = BACK or LAY, Then "CLOSE TRADE"

---

This doesn't make sense to me: you wait until you have three Back triggers before going Back, then the second that one of those triggers disappears which is going to be just about immediately you close the Trade.
I think what you really want is
If previous command is "Back" and if total "LAY" triggers=2 then "CLOSE TRADE".
If previous command is "Lay" and if total "Back" triggers=2 then "CLOSE TRADE".

Well I completely forgot that the races ended at 4:00 today so I’ll have to try logging again tomorrow morning now, as for the “Business Rules”, does this area need to include the actual code for the indicators? For example these formulas previously typed up for MACD and RSI? (WOM would follow a very similar coding to RSI too I imagine):

Quote:

---

Post 16
Standard Module, named "Globals," Code
'Placed here for easy Code maintainencs and modification of formula parameters

Code:

---

'EMA Series Constants
Const macd_A As Integer = 12 'Number of Items in series
Const macd_B As Integer = 26
Const macd_C As Integer = 9
Const Smoothing As Double = 0.9

---

Formatting tags added by mark007

Worksheet Module Code:

Code:

---

Dim MACDShort(1 To macd_A), MACDLong(1 To macd_B), MACDSignal(1 To macd_C) 'FIFO Arrays
 'Assume all three FIFO arrays are filled. Can't start until Long Series array is filled
 
Function GetMACD(Values As Variant, Smoother As Double) As Double
    Dim X As Double
    Dim i As Integer
   
    X = 1 - Smoother 'Used for speed
    GetMACD = Values(LBound(Values))
   
    For i = LBound(Values) + 1 To UBound(Values)
        GetMACD = Smoother * (Values(i) - X) * GetMACD
    Next i
   
End Function
 
Sub test_GetMACD()
    MACDShortAverage = GetMACD(MACDShort, Smoothing)
    MACDLongAverage = GetMACD(MACDLong, Smoothing)
    MACDSignalAverage = GetMACD(MACDShort, Smoothing)
   
    Trigger = (MACDShortAverage - MACDLongAverage) > MACDSignalAverage
End Sub

---

Formatting tags added by mark007

---

Post 28

Quote:

---

Code:

---

Function RSI(Pricearr As Range)          Dim Rarr As Variant    If Pricearr.Columns.Count > 1 Then          ' Note this is the welles wilder calculation for the ongoing period                If Pricearr.Columns.Count > 2 Then            RSI = "Error More than one column in range"        Else            Browcount = Pricearr.Rows.Count            If Browcount = 1 Then                Upssum = Pricearr.Value2(1, 1)                Downssum = Pricearr.Value2(1, 2)                rs = Upssum / Downssum                RSI = 100 - 100 / (1 + rs)            Else                RSI = "Error More than one row in range"            End If                      End If                  Exit Function    End If      ' Note this is the welles wilder calculation for the initial period                        Browcount = Pricearr.Rows.Count    startrow = Pricearr.Row    startcol = Pricearr.Column    ReDim Rarr(1 To Browcount, 1 To 2) As Variant    For mm = 1 To Browcount        kk = 1        Rarr(mm, kk) = Pricearr.Value2(mm, kk)    Next mm                'Rarr = Range(Cells(startrow, startcol), Cells(startrow + browcount, startcol)).Value    gain = 0    loss = 0    rs = 0    If Browcount > 2 Then          ' Use this for the start value of RSI        For ii = 2 To Browcount Step 1                          Change = Rarr(ii, 1) - Rarr(ii - 1, 1)            If Change > 0 Then                  'up day                gain = gain + Change            Else                  'down day                loss = loss - Change            End If        Next ii        avgain = gain / Browcount        avloss = loss / Browcount        If avloss > 0 Then            rs = avgain / avloss            RSI = 100 - 100 / (1 + rs)        Else            RSI = 100        End If    Else        If Browcount = 1 Then            RSI = "FORMAT: 'RSI(RANGE) where RANGE must be a single column of numbers with at least two rows or two columns (ups then downs)with a single row '"        Else            RSI = " Error Less than three rows in range"        End If    End If      End Function  Function ups(Pricearr As Range, Lastime As Variant, TC As Variant)    If Pricearr.Columns.Count <> 1 Or Pricearr.Rows.Count <> 2 Then        ups = "'ups(RANGE, Lastime, TC) where RANGE must be 1 columns of numbers in 2 rows,  Last Time value , Time Constant"        Exit Function    End If    ReDim Rarr(1 To 2, 1) As Variant    For kk = 1 To 2        Rarr(kk, 1) = Pricearr.Value2(kk, 1)    Next kk    up = Rarr(2, 1) - Rarr(1, 1)    If up < 0 Then        up = 0    End If    ups = (up + (TC - 1) * Lastime) / TC End Function Function downs(Pricearr As Range, Lastime As Variant, TC As Variant)    If Pricearr.Columns.Count <> 1 Or Pricearr.Rows.Count <> 2 Then        downs = "'Downs(RANGE, Lastime, TC) where RANGE must be 1 columns of numbers in 2 rows,  Last Time value , Time Constant"        Exit Function    End If    ReDim Rarr(1 To 2, 1) As Variant    For kk = 1 To 2        Rarr(kk, 1) = Pricearr.Value2(kk, 1)    Next kk    down = Rarr(1, 1) - Rarr(2, 1)    If down < 0 Then        down = 0    End If    downs = (down + (TC - 1) * Lastime) / TC End Function

---

---

Or has this already been accounted for and this area is just the ‘rules’ for the indicators to trigger?

Quote:

---

If previous command is "Back" and if total "LAY" triggers=2 then "CLOSE TRADE".
If previous command is "Lay" and if total "Back" triggers=2 then "CLOSE TRADE".

---

Yes Offthelip, you’re absolutely right. I want all three indicators to confirm a BACK/LAY opportunity and then when two out of three of them say the opposite (confirming an exit position), close the trade.

Quote:

---

Function GetMACD(Values As Variant, Smoother As Double) As Double Dim X As Double
Dim i As Integer

X = 1 - Smoother 'Used for speed
GetMACD = Values(LBound(Values))

For i = LBound(Values) + 1 To UBound(Values)
GetMACD = Smoother * (Values(i) - X) * GetMACD
Next i

End Function

---

This function is incorrectly named, this function is actually the EMA function and thus it should be called somethign like GETEMA.
the fact that you use this three times to create the MACD is irrelevant to the function.

Quote:

---

Sub test_GetMACD()
MACDShortAverage = GetMACD(MACDShort, Smoothing)
MACDLongAverage = GetMACD(MACDLong, Smoothing)
MACDSignalAverage = GetMACD(MACDShort, Smoothing)

Trigger = (MACDShortAverage - MACDLongAverage) > MACDSignalAverage
End Sub

---

This test function doesn't make sense either , you are using the same varaible "smoothing" when calling each function. You haven't taken into account the different period for the long and the short EMAs in a MACD. Also the signal line is calculated by subtracting the long ema from the short ema. It is this difference that is then put through another ema function to get the 9 period ema. then subtracting this from the signal line gives you the histogram.
We have then specified 4 triggers.

Also you haven't taken into account the "time since last update" which I have been banging on about since we started, but is vital to get the accuracy in the timing of the triggers.

Quote:

---

as for the “Business Rules”, does this area need to include the actual code for the indicators?

---

No. That code will be developed from the Description after we are all satisfied that the Description is accurate and complete.

Like the army says, "Failing to plan is planning to fail."

BTW, no code in the Helper uses Constants.
Those you listed will be Inputs, AKA Property Let Subs:
macd_A_SeriesCount
macd_A_Smoothing
macd_B_SeriesCount
macd_B_Smoothing
macd_C_SeriesCount
macd_C_Smoothing

For now, just use the plain Name of any Data. When Coding, we will edit the names to suit Private Property Variables and Property Subs

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