Bet Angel and Excel

[CPerry]

20549

Hi there, new to VBA (Like completely new so please be detailed and bare with me)

I have prepared a template (please see attached) for a data capture workbook. I am using a program called BetAngel which streams data through to my spreadsheet live at various speeds, I am aiming to work at the fastest speed of 20m/s. If you scroll down to row 37 and below you will see what is normally supplied (the data I am using is the back odds in cells H45,H47,H49 and H51). The top of the sheet is where I will create live graphs and create a general dashboard area. Now what I need help with...

To the right, starting from cell AK1, I wish to create a 1/5 second timer that loops every 20m/s (so 1 to 10 and then start again) and then just to the right of that, every time a new number ticks, I'd like it to record the previous price which will shift everything down one row. The idea being that the first table captures 10 rows of data at 20m/s so every 200m/s I can turn that data into OHLC figures just to the right and then these will run down the sheet for as long as the sheet is active (BetAngel have a restrict refresh so the sheet will only populate 10 minutes before each horse race - so I may need a VBA command that just kick start the ticker and data capture commands off once something is in cell H45 (>=0). If it helps the spreadsheet already has a current time cell (but this only ticks in seconds and a countdown until race time cell; again only whole seconds).

I hope this all makes sense and you can see what I am trying to do. The other cells under the blue section will be for indicators which will just be simple IF functions that will automatically work once data begins to populate. I would really appreciate it if anybody could mop up some basic code to help me achieve this. Thanks again kind wizards of the internet.

[offthelip]

I don't think using a timer is going to work for what you want to do. For a start 1/5 second is not 20ms , it is 200 ms. One thing to understand about the timer fuction in Excel is that it is not that accurate, it depends on all sort of things, but you will be lucky to get it within 100 of ms. The way to do what you want to do is to use the the "onchange" worksheet" event to detect when betangel update the data on your worksheet. So when the data arrives you copy it to the correct place.
Also As I told you before , when designing this sort of a system, try to minimise the calculations required, so don't move all the data down every time you update it. just add the data to the bottom with a counter. ( you have to count anyway). Then once every 10 iterations ( 2seconds) you can start again.

If you call this sub from the worksheeet onchange event it will copy the data down.
Put a 1 into AP1 to start with to initialise the count.


Sub twenty()
Dim outarr(1 To 1, 1 To 4) As Variant
inarr = Range("H45:H51")
cnt = Cells(42, 1)
If cnt = 10 Then
cnt = 1
End If
indi = 1
For i = 1 To 7 Step 2
outarr(1, indi) = inarr(i, 1)
indi = indi + 1
Next i
Application.EnableEvents = False
Range(Cells(cnt + 1, 38), Cells(cnt + 1, 41)) = outarr
cnt = cnt + 1
Cells(42, 1) = cnt
Application.EnableEvents = True
End Sub

[CPerry]

Thank you for your replies thus far. I am currently in London so I apologise for my slow replies. I will be right back working on this as of Monday morning.

20594205952059620597

Please find above screenshots of a couple of things. Firstly the code I have written so far in a single module. Is this correct or should I be using a worksheet module or something else? Secondly, this is the interface for BetAngel. I simply click connect at cell B37 (as I moved the dashboard down) and it populates the sheet as shown in the third picture. Lastly, the fourth picture shows the table in my project just to the right of what is shown in picture 3 - which can and most likely will be extended in size. I'm trying to get this to be the space in which the VBA code runs first.

Just to reiterate, I'm trying to get the yellow table to fill up (as close to the 20m/s mark as possible) the back price and then reset and loop, so if it takes 10 rows of data at 20m/s then every 200m/s (5x a second) I will get new OHLC data which I then wish to display just to the right underneath the blue area and following Offthelip'srecommendation, I will simply let this tick down the sheet. If my system misses a few numbers at 20m/s that's fine as the price in horses doesn't move that quickly for it to affect my calculations.

Each race will only be monitored 10 minutes before the off (BetAngel offers a restrict refresh therefore the chart shown in picture 3 will be blank) but if some additional code needs be put in so that excel isn't trying to simultaneously do this for 30+ sheets at once, could you please include that in the code?

As previously discussed the call when something changes command would be the best for the yellow box but would you then also combine a timer for the blue box down to capture this more accurately at a rate of 200 m/s? I've had a little look at millisecond timers would this work do you think? It is important that the data I get moves down at exactly (or within a very, very close range of) 200m/s. Again clueless, overly ambitious noob here so could you please describe exactly what code in what order I should put where (am I correct sticking it all in one module as shown in picture 1?) How does it get called within excel (simply once the numbers start moving or do I need to type anything additional in specific cells?) And is there anything else I am missing?

I know there is a lot there, anything any of you can do to help me out would be amazing. I appreciate you all taking your time out to look over this for me.

Cheers,

CPerry.

[SamT]

You may be interested in this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1068

[CPerry]

Cheers SamT! If this works it will be exactly what I need! So the way I see it is I have a few codes now: 1) Timer function, 2) twenty () function.

Now if the timer is operating at exactly 20m/s and I assume VBA works through it’s code in a linear fashion, will it miss readings or generally bug if I just put the timer code first then at the bottom call the twenty () sub in a single module, like this:
(P.S Will this loop continuously, not just do one second and then stop?)

Insert code


Private Declare Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long

Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\0"
Private Const HKEY_LOCAL_MACHINE As Long = &H80000002
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long

Function MicroTimer() As Double
'
' returns seconds
'
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
If cyFrequency = 0 Then getFrequency cyFrequency ' get ticks/sec
getTickCount cyTicks1 ' get ticks
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency ' calc seconds

Call twenty

End Function

'Calling macro
Sub Test()
Dim i As Long
Dim Tim As Double
Dim Result1 As Double, Result2 As Double
Dim Factor As Long
Factor = 10000 '<== adjust to show clear result
Tim = MicroTimer
For i = 1 To 100000
DoEvents
Next
Result1 = MicroTimer - Tim
Tim = MicroTimer
For i = 1 To 1000 ‘or perhaps 50?
DoEvents
Next
Result2 = MicroTimer - Tim
Call twenty ()
End Sub


Sub twenty() Dim outarr(1 To 1, 1 To 4) As Variant
inarr = Range("H45:H51")
cnt = Cells(42, 1)
If cnt = 10 Then
cnt = 1
End If
indi = 1
For i = 1 To 7 Step 2
outarr(1, indi) = inarr(i, 1)
indi = indi + 1
Next i
Application.EnableEvents = False
Range(Cells(cnt + 1, 38), Cells(cnt + 1, 41)) = outarr
cnt = cnt + 1
Cells(42, 1) = cnt
Application.EnableEvents = True
End Sub



How would you suggest to best get around this? (Bearing in mind, the rest of the blue section will be used for indicators which will be constantly completing functions as the data pours in).

Then, to get around it not simultaneously ticking every sheet in my workbook (as I only need it to focus on one at a time), could I start with the code:


Private Declare Function ()
If (1,1) = “1”, Call Sub Timer

AND THEN… if all of that works, that will simply give me 10 iterations in the yellow section. I then need to calculate the Open (first in this list), High (biggest number), Low (smallest number) and Close (last number) and then have an extension to my vba code for this to then tick down under the blue section at 200m/s.

I hope that all made sense and somebody can get their heads around what I’m trying to do. I have quite a lot going on there but I think the code is slowly starting to piece together now. Thank you all very much for your help so far!

Cheers,
CPerry.

[SamT]

I think there's something wrong with your logic...

You want to run a sub 50 times a second (20 ms) but that sub may take longer than 20 ms itself. In any case (Even at 5 times a second (200 ms),) the code takes some varying amount of time.

I would set a variable to NOW, call the main sub, and at its end, call the timer sub, which would re-call the main sub 200 ms after the variable, or immediately if 200 ms has already passed. Windows Excel can measure 0.2 seconds accurately down to 5 or so decimal places.

Something like this


Dim StartTime As Double

Sub Delay
'Maybe use Microtimer for "Now"

Do While Now < starttime + 2.31481E-06 '1/5 second according to Excel
Loop

StartTime = Now
Call MainSub
End Sub


MainSub
'Blah
'Blah
'Blah
Sub Delay
End sub

[CPerry]

I will be streaming data from the programme BetAngel at 20m/s so I'm just trying to get as close to that number as possible. The thing that is getting recorded at that price is simply the back price (10 iterations) at 20m/s to create OHLC figures every 200m/s. If the data doesn't come as through as fast and I only get 7/8 iterations per 200m/s instead of 10 that is fine as my system will be consolidating it's calculations with larger average movements. It's ok if it misses a few signals as prices don't move that quickly, as close to 10 is what I'm trying to achieve. As long as I am getting substantial numbers to build my OHLC table up I'm happy. So all of the code as follows in this order in one single module?:

P.s. Again being new and stupid, how does this code know which cells I want the calculations to take place in exactly? Other than the Sub twenty code shown below, non of the others have cell/range commands, do they? And also how does the sheet know to only do it when it's a specific time? I have a countdown (in seconds) timer in cell G40 and a start time shown in cell G39. Do any triggers need to be made in the spreadsheet itself? And is the following code all going in one single module? How does this module know that it is to be present in EVERY sheet I have, is that just default?


Dim StartTime As Double

Sub Delay


Private Declare Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long

Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\0"
Private Const HKEY_LOCAL_MACHINE As Long = &H80000002
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long


Function MicroTimer() As Double
'
' returns seconds
'[
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
If cyFrequency = 0 Then getFrequency cyFrequency ' get ticks/sec
getTickCount cyTicks1 ' get ticks
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency ' calc seconds

Call twenty

End Function


'Calling macro
Sub Test()
Dim i As Long
Dim Tim As Double
Dim Result1 As Double, Result2 As Double
Dim Factor As Long

Factor = 10000 '<== adjust to show clear result

Tim = MicroTimer
For i = 1 To 100000
DoEvents
Next
Result1 = MicroTimer - Tim

Tim = MicroTimer
For i = 1 To 1000 ‘or perhaps 50?
DoEvents
Next
Result2 = MicroTimer - Tim

Call twenty ()

End Sub


Do While Now < starttime + 2.31481E-06 '1/5 second according to Excel Loop
StartTime = Now
Call MainSub
End Sub

Sub twenty() Dim outarr(1 To 1, 1 To 4) As Variant
inarr = Range("H45:H51")
cnt = Cells(42, 1)
If cnt = 10 Then
cnt = 1
End If

indi = 1
For i = 1 To 7 Step 2
outarr(1, indi) = inarr(i, 1)
indi = indi + 1
Next i

Application.EnableEvents = False
Range(Cells(cnt + 1, 38), Cells(cnt + 1, 41)) = outarr
cnt = cnt + 1
Cells(42, 1) = cnt
Application.EnableEvents = True

End Sub

[SamT]

If the data doesn't come as through as fast and I only get 7/8 iterations per 200m/s instead of 10 that is fine as my system will be consolidating it's calculations with larger average movements. It's ok if it misses a few signals as prices don't move that quickly, as close to 10 is what I'm trying to achieve.
I suspect that even without a timer, just using a continuous loop, you may not get that fast. Why not try to get the code to work as fast as possible, first, then if it is too fast, build in a delay.

BTW I edited your post to add Code Formatting Tags. You can do the same with the # icon.


External link: Bet Angel Excel Guide (https://www.betangel.com/user-guide/microsoft_excel_integration.html?ms=AAAAAAA%3D&st=MA%3D%3D&sct=MA%3D%3D&mw=MzIw)

I have looked at the BetAngel help file linked above, but I still don't have a clue what OHLC means or how to compute it.

Let's go back to the beginning...
Your excel sheet, "Bet Angel," gets data from an outside source, in Column "H", starting in Row 45 and every odd number Row below, about 50 times a second, filling the Table down. There is other irrelevant data in the even number Rows.

You want to "grab" every ten (odd numbered cells) and compute OHLC (Whatever that is) and place that value somewhere else. About 5 times a second.

Since 5x/second is too fast for the brain to keep up with, you must be planning to do something else with this OHLC. What are you planning. It may be best to use VBA from start to finish, rather than having a (slow) Spreadsheet in the middle.

One of the issues we here at VBAX are having is that you have already decided how to use VBA and are asking how to code what you want. BUT... You don't understand the ins and outs of VBA and the way you want the code to work may not be practical. Tell us what final results you want from VBA and we can come up with the most efficient method for VBA to accomplish those goals.

As clueless as I am ATT, I still keep thinking it might be best to keep the VBA Computed results on a different sheet that the incoming data.. Would this be OK with you?

[CPerry]

You're bang on SamT! So as you said, I'll cut to what I want it to do:

I want data to flood into my worksheet as quickly as possible in the yellow area so that I can calculate OHLC (Open - the first value every 200m/s, High - the largest number in the table, Low - the smallest number in the table and Close - the last number in the yellow section). This OHLC data will go down my sheet along with other indicators I need to programme to trigger events - so you are correct, no brain all computer power here. Let me give you an example with a MACD indicator (OHLC and MACD are financial tools, these formulas are the foundation of my system).

MACD = a 'previous 12 readings' moving average and a 'previous 26 readings' moving average along with a 'previous 9 readings' average as a signal line. As a very simple example, if both the 12 average and 24 average crossed the signal line i.e. the numbers in cells AU3 and AV3 were both greater than the number in cell AW3, a trigger cell would show the number 1. If E3 = 1 then BetAngel will place an order in the market for me (via the green section starting at M45 down). OHLC will be used for other indicators similar to this one to define a market's current position.
There is a very similar spreadsheet (but only ticking in whole seconds) if you follow this link here... excelbettingbots (.com)

I was planning on using an index function or something to continually identify the lowest 12 numbers and calculate a mean average (every 200m/s, live as the data pours in). Now once I have data flowing that is when I will trial and examine different indicators so if you could kindly show me how you would code a simple formula like the following into a vba code, that would help tremendously...

=AVERAGE(AU3:AU11)

The problem I'm going to face with it constantly ticking down is the next line formula for the next 9 point average would have to be =AVERAGE(AU4:AU12), I need a ticking range also. Any ideas how to get around this?

With regard to having the data come through on a separate sheet, this is fine as long as the triggers don't get confused which sheet they are reading from and the scrolling of data uses the HOME sheet start times and countdown to start times in cells G39 and G40 respectively. That way I don't have 40 or so sheets trying to trigger their VBA code at the same time and shutting down the entire internet! You're talking about 3,000 lines of 200m/s before the race goes in-play which means 30,000 loops in the original yellow section at 10x that speed. That's disregarding letting my spreadsheet going in-play which I will test to see if it can do both.

So to summarise...
1) Data comes in very quickly,
2) Data gets crunched in 200m/s segments (first number, last, highest, lowest and several indicators - each requiring approx 2/3 cells each thus me leaving 12 cells across in the blue section),
3) These indictors every so often produce a 1 or some other 'GO' command to trigger a back/lay bet which then goes through the green striped section into BetAngel and places real money in the live market (That is already programmed to happen, trust me. I just need to put something like =IF(E5=1,"BACK","").

Again, there's a lot there so thank you so much for taking the time to look over this!
CPerry.

[SamT]

Do us a favor, place this code in the Main ("Bet Angel") sheet's code module and run Bet Angel. Take note of what address the message box gives,
Private Sub Worksheet_Change(ByVal Target As Range)

'This line exits only on changes in Column H
'If Target.Column = 8 Then Exit Sub

'This line exits on all other changes
If Target.Column <> 8 Then Exit Sub


MsgBox "Cell " & Target.Address & " Just changed"

End Sub

I am hoping that Bet Angel triggers a Change event in Column "H" by itself, some programs do and some don't. If you don't see a message box, change the two "Exit Sub" lines to use the other. We are looking for something to use as a timer or input counter.

Hopefully, we can count 10 changes/Inputs, then run the VBA that will fill your other Data Cells with values. How we do it will depend on how Bet Angel works with Excel. It may be as simple as reading the values into an array and extracting first, last, Min, Max, and Average, all of which operations are very fast with arrays.

Also note: It may be required to keep all results in memory for a bit and only update the Visual aspects of the WorkSheet once a second. That will depend on the accuracy the rest of your Project requires.


This OHLC data will go down my sheet along with other indicators I need to programme to trigger events Why not do all that with VBA in superfast RAM?

Only show on the slow spreadsheets what you personally need to know in order to follow the automated process and override it if needed. Granted it might be too late by the time you notice a problem.

[SamT]

Outside links:
https://en.wikipedia.org/wiki/MACD
https://en.wikipedia.org/wiki/Moving_average#Exponential_moving_average

Do you intend to use a simple average over time or an actual Exponential moving average when computing MACD?

Also, I recommend using Constants for the Number of periods used in averages so that you can easily tweak the "Program."
macdA = 12
macdB = 26
macdC = 9

The A and B average arrays can be simple FIFO arrangements, but if you use Exponential averages you would need two more to hold the weighted values to compute averages from. Don't worry too much about the number of Arrays invovled. A gazillion Arrays are still faster than a spreadsheet doing 100 calculations. :nervous:

[SamT]

On your attachment, are the Rows in sheet Bet Angel, from 45 to 88 one 10 ms dump from the Bet Angel Program? Is this typical? Does this data need to be "spread over" 22, (or more) worksheets for processing?

Here's what I'm thimking,:D, (if all the above are true,) the input sheet should be hidden so that it takes no real time updating. it should only contain new inputted data, nothing else. At each update, the new data is Arrayed and split as needed among the rest of the sheets, which are never Activated so they also take no real time updating. Each "working" sheet is self contained and all calculations are restricted to that sheet. There should only be one sheet that gets visually update just so you can follow the top level progress.

The only faster method I envision would be to use Class Modules and not actual Excel Worksheets.

[SamT]

As soon as I see your next response, I am going to move this Thread to the Other Applications Forum and change the Title to "Bet Angel and Excel"

[CPerry]

I'll try and respond to all of your messages in one reply. Yes I'm fine with you moving this thread. I get the message box displaying: "Cell$H$37:$I$37JustChanged" and every time I press ok it keeps popping up so it must be constantly recognising new data coming in which is what you were hoping for?

I am also fine with keeping the visuals delayed as I don't need them all that often. I just could do with seeing the triggers in the purple cells at the top of my sheet so I can code simple functions for BetAngel to take my order (I mean if I can do it solely through VBA then that's the perfect end goal).

I will be using exponential moving averages with my MACD calculations as well as about 4/5 other indicators (I have the formulas for these, so if you could just direct me in how to adapt simple formulas into vba language, I can go away and learn this first once what we're working on now is complete). Now your last message...

BetAngel only recognises sheets called BetAngel 1, BetAngel 2 etc to link up to each market so these sheets would be the ones that include the green and white rows where you place orders from, this is the section from rows 45-104. The programme displays: the horse name, price, volume and other details here in the same format each race but once the next race starts, it gets wiped for the new race data (if you choose to only use one single sheet), the problem is keeping tab of Profit/Loss records with reusable sheets.

I need approx 30-40 races per day but each starts at a different time (10 mins apart) so I could either have 30 separate sheets or keep using the same two or three over and over and have VBA dump my results each race somewhere so not to clog up my system? In either case, if there were a separate sheet they could be put in-between every other 'Main' sheet as long as the code recognises: BetAngel 1, Data 1, Bet Angel 2, Data 2 (That Data 1 is linked to BetAngel 1). That would work.

[mdmackillop]

so if you could just direct me in how to adapt simple formulas into vba language

Two ways.
=Q24+R24+S24-Q25
filling down becomes
Cells(24,24).Resize(6) = "=Q24+R24+S24-Q25"

or
If you want to substitute with variables
Start the macro recorder
Select the cell
Click into the formula in the Formula Bar
Press Enter

This =Q24+R24+S24-Q25 should appear as something like

ActiveCell.FormulaR1C1 = "=RC[-11]+RC[-10]+RC[-9]-R[1]C[-11]"

which you can adjust to suit.

[SamT]

On your attachment, are the Rows in sheet Bet Angel, from 45 to 88 one 10 ms dump from the Bet Angel Program? Is this typical? Does this data need to be "spread over" 22, (or more) worksheets for processing? Does BA do the data "Spreading" over all the other Bet Angel(n) sheets for you?




so if you could just direct me in how to adapt simple formulas into vba language,Not so simple a formula. Recursive. Also formatted (Coded) for multiple uses. Ypu could, for innstance, use different smoothing factors for each curve's values

From the Link above:

The EMA for a series Y may be calculated recursively:
https://wikimedia.org/api/rest_v1/media/math/render/svg/54ece5e7437b18e4955d3e8894426eb819f9eda6
Where:


The coefficient α represents the degree of weighting decrease, a constant smoothing factor between 0 and 1. A higher α discounts older observations faster.
Yt is the value at a time period t.
St is the value of the EMA at any time period t.


By repeated application of this formula for different times, we can eventually write St as a weighted sum of the datum points Yt, as: https://wikimedia.org/api/rest_v1/media/math/render/svg/6bc1b4ba47fbc9c5a2421c2b332b806398ade8b3
for any suitable k ∈ {0, 1, 2, …}

The weight of the general datum point
https://wikimedia.org/api/rest_v1/media/math/render/svg/f66d8599c7945d855fad4fc663af94bbd7b8d226 is https://wikimedia.org/api/rest_v1/media/math/render/svg/d8cf6c18c8aba8978708356ef42e02d79d56ab0e.


Standard Module, named "Globals," Code

'Placed here for easy Code maintainencs and modification of formula parameters

'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

Worksheet Module 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

[CPerry]

If you mean the section from cells AG45:AK88, this does update at 20m/s the same as the rest of the sheet yes. It is: Stall number, Reduction Factor, Actual SP, Projected SP and Volume Weighted Average Price in that order.

To get this working to the point I can use it in the market then I will choose two indicators and a confirmation indicator. The MACD previously mentioned will be the momentum indicator along with the RSI to show over/under sold prices.

RSI: 100 - (100/1+RS), whereby RS = an exponential moving average (14 day average is common, although if I will be using this inlay with extremely volatile markets, something less like a 10-day-EMA may be more approproate). This will return a number between 0-100. A figure of 30 or less shows undersold and 70 or above shows overpriced so we have two triggers. Alternatively, gains/losses were recommended as follows:

100
RSI = 100 - --------
1 + RS

RS = Average Gain / Average Loss
To simplify the calculation explanation, RSI has been broken down into its basic components: RS, Average Gain and Average Loss. This RSI calculation is based on 14 periods, which is the default suggested by Wilder in his book. Losses are expressed as positive values, not negative values.
The very first calculations for average gain and average loss are simple 14-period averages.


First Average Gain = Sum of Gains over the past 14 periods / 14.
First Average Loss = Sum of Losses over the past 14 periods / 14

The second, and subsequent, calculations are based on the prior averages and the current gain loss:


Average Gain = [(previous Average Gain) x 13 + current Gain] / 14.
Average Loss = [(previous Average Loss) x 13 + current Loss] / 14.


I'd also use the Weight of Money (WOM) to confirm any trend, which is: Best three lay prices / (Best three lay prices + Best three back prices), so for Horse 1 would be =SUM(I46:K46)/(Sum(I46:K46)+SUM(F46:H46)-50%.
I added the -50% part on myself, this gives readings of -50:50. Anything negative hints at a price drop, anything plus hints at a price increase, anything near 0 hints at no change.

So if I use these two triggers (+ WOM as a confirmation trigger), my next challenge becomes including these into the VBA code. Originally I was just going to type the function for the formula into the rows under the blue section updating at 200m/s but you said it would be more efficient sticking it straight into the code?

This leads me back to my biggest misconception of how the code knows where to put everything? If it's all running via code now that adverts the problem but at some stage I still need to show the 'triggers' in specific cells, I was thinking it appearing in the purple section so that the triggers can be called in the green and white section from here with a simple =IF("1",BACK,"") kind of formulas.

And then absolutely finally, each time a bet is placed, it stays in that green section as confirmation so you need to constantly clear it. I have previously been suggested the following code to do this, would this work? If so, could it be added in to the optimal place in the master code please?:


Private Sub Worksheet_Calculate()
If Range("P45:P103").Value = "PLACED" Then Range("PLACED").ClearContents
End Sub

I have deleted all my current macros in this project to start fresh (well copied them into a word document for now I should say) so what kind of macros do I put where and what do I fill them with exactly please? There's so many things going on now, it just needs sorting before anything else I think. SamT you are literally a VBAX Wizard in every way!

[SamT]

Ne:what kind of macros do I put where and what do I fill them with exactly please?
Three Questions...What kind: Functions, that Return a Value of some sort, Like the Function in my last Post. Some Subs to do things with Data Like the Sub in my last. Albeit, you don't yet know how to "read" Sub Test_GetMACD.

Where: I don't know yet. I still don't know enough about the restrictions on Data Structure.

Filling? Well, see? There's the problem. VBAX is a club, we're all volunteers and some have the responsibility to manage the membership. But no one on the Forum gets paid, we all work for the love of it. I love teaching. And Programming. I've been doing one or both for 47 years. IMO, VBA for Excel is the ultimate Programmming teaching tool. I do hope you want to learn VBA for Excel and Bet Angel and are not just looking for code.


how the code knows where to put everything? If it's all running via code now that adverts the problem but at some stage I still need to show the 'triggers' in specific cells We haven't gotten that far yet.






Me: On your attachment, are the Rows in sheet Bet Angel, from 45 to 88 one 10 ms dump from the Bet Angel Program? Is this typical? Does this data need to be "spread over" 22, (or more) worksheets for processing?

If you mean the section from cells AG45:AK88, this does update at 20m/s the same as the rest of the sheet yes.
What do you mean, "The rest of the sheet?"

Talk to me about Data Flow. All I know is that it comes from Get Angel and goes :dunno: somewhere? Where does it go? Each and every bit, please :bow:

Edit to add: Never mind... Outside link to Data Flow (https://www.betangel.com/user-guide/using_the__bet_angel__spreadsheet.html?ms=AAAAAAA%3D&st=MA%3D%3D&sct=MA%3D%3D&mw=MzIw)

[SamT]

Interesting read: https://www.betangel.com/forum/viewtopic.php?f=19&t=14857&sid=3614e62f82c4c5e6d0e9d7d3ad294be6

Also I have now read the entirety of BA's Excel help. Have you? I have more questions for tomorrow.
Think some about those equations/curves/signals you want to incorporate into this little Excel BA App we're developing. Think about explaining how the equations work to a child and what you gonna name 'em.

[CPerry]

20646

Yes 100%, I have bought myself a few books which should arrive early next week and if anybody has any others they can recommend I am fully up for investing time and money into learning this new language. And 100% up for paying back any way I can by replying in other forums once I have a good amount of knowledge in this area.

Yes I have previously read all of the BA's Excel help document when I first purchased the product and have just had a quick second scan over it now, it is basic from an excel point of view though as it simply says BetAngel will 'scan the instruction fields (green and white stripe area) upon each refresh (which I have mine set at 20m/s)' It doesn't really say how it does this, all I know is that it is 'streaming' at high speed.

I shall start from the beginning then, I have attached a slightly updated version of the sheet I will be using. Every other tab is a 'Data' Sheet which you recommended using purely for the flow of data OHLC. Then in the BetAngel tab I have the four front horses separated by the colours: red, yellow, green and purple. Horse 1 will be taking it's data from the BACK price in cell H45 (turning it into OHLC data in the 'Data 1' tab) and then appearing in the red section (unless what I'm about to propose next can be done entirely in the background via VBA).

In terms of the indicators, I shall start basic for now so we will look at MACD, RSI and WOM.
MACD (12EMA, 26EMA, Signal Line):
Initial SMA: 10-period sum / 10

Multiplier: (2 / (Time periods + 1) ) = (2 / (10 + 1) ) = 0.1818 (18.18%)

EMA: {Close - EMA(previous day)} x multiplier + EMA(previous day).
RSI (Horse 1):
=100-(100/12EMA)

WOM (Horse 1):
=SUM(I46:K46)/(SUM(I46:K46)+SUM(F46:H46))-50%

So in the 15 red cells under 12EMA, I will show the last 15 (3 seconds worth) of numbers, same with the 26EMA, Signal line, RSI and WOM. These numbers will allow me then to create triggers.

In the instruction fields I would have something like:
IF the SUM of the last 5 WOM readings is positive, The MACD trigger has activated at least once within the last 3 seconds (15 cells) and the RSI trigger has activated at least once in the last 15 seconds THEN "BACK".
IF the SUM of the last 5 WOM readings is negative, The MACD trigger has activated at least once within the last 3 seconds (15 cells) and the RSI trigger has activated at least once in the last 15 seconds THEN "LAY".
IF neither, then do nothing.

Now that section is where I need to experiment, I will need to add some more advanced parameters and things and account for other kinds of movements but basically I just need to be able to see the last 3 seconds worth of numbers to be able to formulate some "Back" and "Lay" triggers as well as "Close Trade" to feed back into the instruction fields so BetAngel can pass these orders to BetFair. I will need some IF formulas to determine whether the price is coming in or pushing away to determine when best to jump on/get off a price.

Hopefully that all makes sense, at least the general flow of how the calculations should occur. Whilst waiting for a reply I will come up with the specific trigger formulas I think it will need for each of the indicators.

Kind regards,
CPerry.

[SamT]

The more I research the farther back my Questions go. We still need to know the answers to all my previous, but the decisions also depend on how you use BA. You use Guardian, correct? Because its multi sheet sync sounded good?

First, allow me to introduce you to an Excel Application Object, the WorksheetFunction Object. When you click the fx icon on the Menu Bar, you are opening the WorksheetFunction's DialogBox. This DialogBox lists all Excel Builtin Functions, Including a slew of Financial Functions. It can also list all UserDefined Functions,which are written in VBA.

VBA can use all Builtin Functions by invoking a WorksheetFunction Object. Most commonly by just calling the Application's WsF Object. However, because Speed Is Of The Essence in this situation, I will introduce the concept of New Objects, that only reside in RAM.


Dim WsF As Object
Set WsF = New Application.WorksheetFunction



Depending on exactly where we place each of those two lines, we can use any Excel Function from RAM in any code by merely


X = WsF.Function Name(Function Parameters)




Can we call this App you're working on, "An Automated Betting App with Custom Functions and Rules Based on Get Angel and MS Excel?" The Rules system of BA is based on Cell String Values. Excel soars on Numerical analysis, VBA on Data Manipulation.

All that VBA needs to put the appropriate Values in some Excel Cells is the main Get Angel Sheet. If you want to archive the BA stream, the "Interesting Reading" link above mentions a fast method.


I see...
Two sheets, the main BA Sheet and the "Overview" Sheet they you stare at... Uh... I meant study. Also on that sheet VBA needs a block of Cells to "talk" to BA with and a block to "read" what Parameters you give it. At this Time, I only see three Standard VBA Code Modules; Globals, Math Functions, and The Works. The ThisWorkbook Code Module will count BA Updates and Call "The Works" when desired. "The Works" will have to set a Flag when it is ready to start over.

For now, I, we, still need more information about Ba's update style. Drop this code into the Bet Angel Code page. 9 to 4 minutes before a monitored Race, Run StartLogging from the Macro menu or the VBA Editor.
Option Explicit

Const RecordingLength = 1000 'Records. 'Adjust to suit
Const RecordingTime = 3 'Minutes 'Adjust to suit

Dim Running As Boolean
Dim Recording(1 To RecordingLength, 1 To 2)
Dim i As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then Exit Sub
If Target.Row < 45 Then Exit Sub

Recording(i, 1) = Target.Address
Recording(i, 2) = Now

i = i + 1
If i > RecordingLength Then
Running = False
SaveRecording
End If
End Sub

Private Sub SaveRecording()
Worksheets.Add Before:=Sheets("Bet Angel")
ActiveSheet.Range("A1:B" & CStr(RecordingLength)) = Recording
End Sub


Private Sub RecordingTimer()
Application.OnTime Now + TimeValue("00:" & CStr(RecordingTime) & ":00"), "StopLogging"
End Sub

Public Sub StopLogging()
If Running Then
Running = False
SaveRecording
End If
End Sub

Public Sub StartLogging()
Recording(1, 1) = "Changed Address"
Recording(1, 2) = "Time of Change"
i = 2

RecordingTimer
Running = True
End Sub


As soon as you're done with this, Change the Name of "Private Sub Worksheet_Change." Something as simple as "Private Sub ***Worksheet_Change" will work. You may want to save this code somewhere. Write Comments so you remember how and why to use it. :D

[SamT]

Here's an example of what I think is the minimum sized Workbook. Sheet "Control Panel" has three sections (across.) The first has an example layout, but it is really for you to decide what data you want to see and what command buttons you want to control BA with.

The Next section, "Parameter Input" needs to contain all the variables of all the Signal Formulas you need. I layed the examples out in a well considered style of colors and names. Please use this style as you develop this section to fit your needs. The reason for this section is to allow easy experimentation with various parameters. As you use this App you will want to tweak it for profit. This section is where you do that.


Assume that VBA will perform all calculations and filling of Excel Data Cells.

In this example, Pale Green cells are for User Inputs, Pale Yellow Cells are for VBA outputs and BA Inputs. Pink cells are for VBA outputs and for the User to read.


The first Code hurdle is reading and manipulating the Bet Angel stream. For that I await the output of my previous post's code. While I'm working on that, you can work on the Control Panel sheet and the Signals formulas, referring to my example of GetMACD.
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.

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(MACDSignal, Smoothing)

Trigger = (MACDShortAverage - MACDLongAverage) > MACDSignalAverage
End Sub
Be sure and use the Parameter Names from the Control Panel Sheet, Parameter Inputs section. Not the initial Variable names I used here.

[SamT]

Link to remember: http://www.vbaexpress.com/forum/showthread.php?61011-Convert-C-to-VBA&p=371012&viewfull=1#post371012

Let me know when you have digested all the above. I'm ready to throw some hard stuff at you.



:devil2:

[CPerry]

Hi Sam, firstly apologies for my delayed response, apparently my internet provider wants me to pay extra for nothing at all with no warning so shut off the internet for the weekend until now – 11:00 at night!! Anyways back now and…

Yes I will be using guardian to cycle through each market throughout the day. I will leave it set at 10 minutes before a race until 0 seconds before exactly then the next one will start ticking.

I will be sure to give the financial functions a good look at tomorrow as they will be extremely useful, cheers for that! The only reason I want to archive the most recent data is so that the triggers actually activate because it is highly unlikely that multiple indicators will activate at the exact same 20m/s point. I simply created that table to show the last three seconds for each indicator and if each indicator had activated at least once within that period then we activate a trigger. I mean if this can be done purely within VBA that would be brilliant.

Side note – my books arrived this morning so I will spend a few hours tomorrow thoroughly learning this new language as much as I can.

The first race doesn’t start until 2 tomorrow so I’ll log the first one and attach the results into my next message. I will also manually look into how exactly BetAngel deals with all the data it transfers but what do you need to know?

I’ve also attached some screenshots of that data logging code you sent previously, I’m having a little problem with it…
Picture 1: I have placed the code in the BetAngel sheet code area and pressed play.
Picture 2: I get the following error message
Picture 3: This follows picture 2, so I change the 'lower case L' to an 'i'
Picture 4: I then get this error message which I don't know how to correct.
Picture 5: I close the previous box and go to press play on a market which is a good 15 hours away from running, the top banner flashes "Running" every so often but nothing happens after 3 minutes. When I close the box, still nothing happens. By that I mean no new tabs opened in my workbook with any kind of data, any idea what's up?

I’ve just had a look at template you sent, looks good. Can’t wait to hear the hard stuff you have for me next!

Looking forward to your reply tomorrow.

CPerry.
2067020671206722067420673

[SamT]

Picture 4: I then get this error message which I don't know how to correct.
Hard to tell from here but I think if you change the
TimeValue(...) to
TimeValue("00:10:00")


Can’t wait to hear the hard stuff you have for me next!I've started writing the "horse" Object. or, if you will, the "Stock" Object, although I am calling it a "Brand" Object, since "Brand" can include horses and Stocks. Technically, a VBA Class Module.

The Main Code will Instantiate a new Brand Object for each horse in the BA list. Each new Brand Object contains all the code needed to provide, for that Brand, the outputs you want and BA needs. The beauty of this type coding is that you code once and use many times.

[CPerry]

I've amended the RecordingTimer Sub to show:


Application.OnTime Now + TimeValue("00:10:00"), "StopLogging"

but no luck! It continues to come up with "Subscript out of range". I have tried pressing play just before and just after the 10 minute mark on two different markets now with no luck and I've taken the line out completely so it just shows:


Private Sub Recording Timer ()
Application.OnTime Now, "StopLogging"
End Sub

Again, pressing play just before and just after the 10 minute mark but I continue to get the same dialogue box!

[SamT]

Subscript out of Range indicates an issue with Collections(Sheets) Arrays (Recording) or Ranges (Range("A1:B" & CStr(RecordingLength))). I have added some message boxes to let you know what sub is running and redesigned this code so that is it started and stopped manually or when the RecordingLength number of updates have been recorded.
Option Explicit

Const RecordingLength = 1000 'Records. 'Adjust to suit

Dim Running As Boolean
Dim Recording
Dim i As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then Exit Sub
If Target.Row < 45 Then Exit Sub

Recording(i, 1) = Target.Address
Recording(i, 2) = Now

i = i + 1
If i Mod 100 = 0 Then MsgBox "Recorded " And i & " Worksheet Changes"

If i > RecordingLength Then
MsgBox "Recorded " & i - 1 & " Updates"
Running = False
SaveRecording
End If
End Sub

Private Sub SaveRecording()
MsgBox "Saving " & RecordingLength & " records, with " & i & " Updates"

Worksheets.Add Before:=Sheets("Bet Angel") 'Insure correct speelng of " Bad angle "

ActiveSheet.Range("A1:B" & CStr(RecordingLength)) = Recording
End Sub

Public Sub StopLogging()
If Running Then
Running = False
SaveRecording
End If
MsgBox "Stopping Recording"
End Sub

Public Sub StartLogging()
ReDim Recording(1 To RecordingLength, 1 To 2)
Recording(1, 1) = "Changed Address" 'Recording(One, One) =...
Recording(1, 2) = "Time of Change" 'Recording(One, Two) =...
i = 2

If UBound(Recording) = RecordingLength Then
MsgBox "Recording Array is defined"
Else
MsgBox "Failed to set recording Array"
End If

Running = True
End Sub

See next post before proceeding.

[offthelip]

RSI: 100 - (100/1+RS), whereby RS = an exponential moving average (14 day average is common, although if I will be using this inlay with extremely volatile markets, something less like a 10-day-EMA may be more approproate). This will return a number between 0-100. A figure of 30 or less shows undersold and 70 or above shows overpriced so we have two triggers. Alternatively, gains/losses were recommended as follows:

100
RSI = 100 - --------
1 + RS

RS = Average Gain / Average Loss
To simplify the calculation explanation, RSI has been broken down into its basic components: RS, Average Gain and Average Loss. This RSI calculation is based on 14 periods, which is the default suggested by Wilder in his book. Losses are expressed as positive values, not negative values.
The very first calculations for average gain and average loss are simple 14-period averages.


RSI (Horse 1):
=100-(100/12EMA)

Your description and equations for RSI are not the Welles wilder calculations, or anything like them.
Firstly the way the average gain is calculated is using a exponential moving average, not using the standard Excel Average function. Your equations are not calculating the average gain or the average loss. you just seem to be using a moving average of the price.
I have programmed the Welles Wilder RSI equations into VBA functions, there are three of them.
One thing to realised about exponential moving averages is that in theory you need an infinitely long set of data to get the correct value. In practice that values will settle down within about two times the time constant you are using, unless the values are changing very rapidly .

I have written functions to calcualate the average gains (UPS) the average loss (Downs) and the RSI (RSI)

the functions are :

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




To illustrate how to use them I have used them in this worksheet . Notice in particular the the first value for the RSI is calculated directly from the prices not the ups and downs.
20677

[offthelip]

I believe that you will have problem with trying to get this to run fast enough. One suggestion I have which will save a lot of time is :
Do not write the data every 20ms into you spread sheet.
In order to calculate the OHLC ( open , High, Low, Close) , At the first change event of a 200ms period you save the value in a global variable called "openprice", and you copy this value to two other variables called "Highprice" and "Lowprice", then every time you get an update you just check whether the latest price is above the "highprice" , if it is you save the new high price, similarly for the lowprice. When you detect that a new 200ms period has started , you write out the 4 prices and start again.

Note I have successfully programmed similar systems which did automatic trading of financial systems.
I think you are going to have a problem doing all the calculations which you need to do every 200 ms.
Another suggestion I have is, do not use any graphs at all . Excel will take a long time to redraw those:

finally, speed up your computer as much as possible , get rid of all unnecessary services and startup programs. A useful website to help with this is Black vipers website. (google black viper)
Make sure any other program that uses the internet is not running, e.g emails, dropbox, onedrive, updates.
Best of luck

[SamT]

Before you code...



On the VBIDE, (AKA, the VBA Editor,) Menu >> Tools >> Options >> Docking Tab, Check the first 5 Checkboxes.
In the VBIDE Press F2 and F4 to insure that the Project Explorer and Properties Windows are open.
Move the Project Explorer and Properties Windows to the side of the VBIDE Window.




On the VBIDE Menu >> Tools >> Options
On the Editor Tab, Check all Boxes, Except that the Drag and Drop Text Editing box is optional.
On the General Tab, check

Notify Before State Loss.
Break On All Errors.
Compile On Demand.
Background Compile.
And Show ToolTips.



The rest are up to you


BTW, Bookmark this link: http://www.snb-vba.eu/index_en.html for advanced examples of all sorts of code. If we're lucky, the author will help us optimize the code in your Project. He's a member of VBAX :)

[CPerry]

All very good suggestions offthelip, that sounds like a good idea to speed up my code. Anything as close to 200m/s or quicker is all I'm after. I will have BetAngel as the only thing running, redirecting all of my processing power to it continuously and running through an ethernet cable. Once I make my first bit of money, I'll reinvest it in getting a private VPN server set up. Thank you for identifying the mistake with the RSI calculation also.

SamT, I'm just looking over what you put in your last message and I will reply as swiftly as I can.

[CPerry]

20680
5th tick box was unticked so I have now ticked that. Everything else was as you specified. I still get a dialogue box which says “Recording Array is defined” and then it takes me back to the VBA portal and a few seconds pass then I get another pop up box which says:

“Run-time error ‘13’:
Type mismatch:”
Continue is greyed out, I can either ‘end’, ‘debug’ or ‘help’

When I press debug I get a line turn yellow as shown in the attached image. I don’t know whether it is relevant but I asked what may be wrong in the BetAngel forum whilst researching and it seems the spreadsheet is populated in 6 separate lots of data in each refresh:

$A$1:$B$1
$G$1:$H$1
$C$2:$C$6
$F$2:$F$4
$A$9:$K$18
$T$9:$AE$18

Apparently the Betfair version populates $C$2:$C$6 as it last set. I'd guess the spreadsheet is getting caught up in a continual loop as the Worksheet_Change event is continually firing on each refresh . One blokes recommended restricting the code to only fire when the last set of data is written to the sheet and turn events off whilst it fires (Application.EnableEvents = False/True) etc

Something to do with the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
End Sub

Hopefully this makes sense to you, what amendments should I make to the code exactly to test what you are looking for? If you could send an updated code to scan, there are races going on until about 8:30 tonight.

CPerry.

[offthelip]

I think you need to think about what you are trying to do:

Sample items necessary for a MACD
26 sample moving average ( should be exponential moving average)
minus 12 sample moving avarage

Thus First possible value is after 26 values
Then to create the MACD you need to take a 9 sample moving average of this so the first MACD value will be after a minimum 35 samples . i.e 7 minutes into your 10 minute prerace time. This is not using exponential moving averages, if you use these then 15 minutes sample lead in taime would be more accurate.
Is this acceptable??

[CPerry]

OHLC readings are taken every 200m/s though so the first 35 samples will come after 7 seconds won't they?

[SamT]

"And" is a Boolean Constructive Keyword. "&" is the Concatenate Strings Keyword symbol.

Many, (most,) of these little snippets of one time use code I provide will have obvious, (to an experienced coder,) errors in them. You are expected to try resolve them before asking for help. This helps me determine your coding level so I know what to "throw at you" next.

I will incorporate offthelip's RSI into the Brand Object. Always with the caveat; Speed is of the Essence.

[offthelip]

yes you are right, mental block!!

[SamT]

More Information in re RSI (Relative strength index)
https://en.wikipedia.org/wiki/Relative_strength_index
http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:relative_strength_index_rsi


See also


Cutler's RSI (https://www.forexrealm.com/technical-analysis/technical-indicators/cutler-rsi.html)
Stochastic RSI (https://www.forexrealm.com/technical-analysis/technical-indicators/stochastic-rsi.html)
Relative Strength Index (RSI) (https://www.forexrealm.com/technical-analysis/technical-indicators/relative-strength-index.html)
Welles Wilder Volatility Index (https://www.forexrealm.com/technical-analysis/technical-indicators/wilders-volatility-index.html)
Welles Wilder Smoothing (https://www.forexrealm.com/technical-analysis/technical-indicators/wilders-smoothing.html)

[CPerry]

20684

I see, just changed it to '&' and it appears to have worked. I went for the 20:15 race around 8 minutes before the off and it took 1,000 readings in around a minute or so with 100 reading updates appearing just under every 10 seconds approximately. Please find attached the spreadsheet with the captured data.

CPerry.

[SamT]

I see, just changed it to '&' and it appears to have worked.:beerchug:

[SamT]

Attached is a crude analysis of the Timing Report.

What we learned...
Excel's "NOW()" clock updates once per.second
BA updates about every 51 ms
It seems that the Worksheet Change caught most updates. About 20 per second.

This next code should catch 1000 updates of one horse so that we can see how often each horse is updated/changed


Const RecordingLength = 1000 'Records. 'Adjust to suit

Dim Running As Boolean
Dim Recording

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

If Not Running Then Exit Sub
If Target.Row <> 45 Then Exit Sub
If Target.Columns(1).Column <> 2 Then Exit Sub

Recording(i) = Range("F45:K45").Value '?

i = i + 1
If i > RecordingLength Then
Running = False
SaveRecording
End If
End Sub

Private Sub SaveRecording()
Worksheets.Add Before:=Sheets("Bet Angel") 'Insure correct speelng of " Bad angle "
ActiveSheet.Range("A1:F" & CStr(RecordingLength)) = Recording
End Sub

Public Sub StopLogging()
If Running Then
Running = False
SaveRecording
End If
MsgBox "Stopping Recording"
End Sub

Public Sub StartLogging()
ReDim Recording(1 To RecordingLength, 1 To 6) '?
Running = True
End Sub

[CPerry]

Great to see the code is ticking along at a good speed. I've just sat down from work and started looking at this. I see the first mistake in the line:

Recording(i) = Range("H45:K45").Value
It has shown 'error 9' when I click debug it says 'Subscript out of range'. I have tried troubleshooting and reading Microsoft Excel's help page but nothing relevant, I have tried about 10 other pages but non give a relevant example similar to this of which part is incorrect in that line. I have tried putting ActiveSheet.Range but this is pointless as it's a sheet1 module so it knows that range. H45:K45 is the range I am trying to collect, so I'm confused as to how any part can be out of range? I have tried .variable, .array, .range instead of .value hoping to stumble upon something. Please can I have a clue as to which part of the code on that line is at fault so I can research further? I've hit a dead end else. I understand the basic structure of VBA now, still getting to the debugging specific terms.

By the way, the actual system will only need the current back price in it's calculations so just H45. Are the second and third best back price and the three best lay prices other than the Weight of Money indicator?

I shall continue to look until I get a reply as I see there is also another challenge you have set for me in this code and the races are happening only until 21:00.

[CPerry]

I have also looked at other lines prior, for instance adding (1 to RecordLength, 1 To 2) before 'Dim Recording' as you have done in a previous code. I'm looking for context clues in past codes but still no dice, it just comes up then with "Compile error: Wrong number of dimensions". Debug shows that it is something to do with the part 'Range("H45:K45").Value
But then I clicked end and it's working past that section now and is stuck at 'Public Sub StartLogging??? I have no idea how I got around it but I'm thinking it may still not work. Still researching!

[CPerry]

Wait...

[CPerry]

20693
Ok, it took me just over an hour or so to figure out but here’s the data (attached) for 7 minutes out from the 19:15 race. I think I debugged it fine in the end, here’s the code I used (in case I accidentally made a typo which would affect the data you’re trying to collate):


Option Explicit Const RecordingLength = 1000 'Records. 'Adjust to suit
Dim Running As Boolean
Dim Recording
Dim i As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then Exit Sub
If Target.Row < 45 Then Exit Sub
Recording(i, 1) = Target.Address
Recording(i, 2) = Now
i = i + 1
If i Mod 100 = 0 Then MsgBox "Recorded" & i & " Worksheet Changes"
If i > RecordingLength Then MsgBox "Recorded " & i - 1 & " Updates" Running = False SaveRecording
End If
End Sub

Private Sub SaveRecording() MsgBox "Saving " & RecordingLength & " records, with " & i & " Updates" Worksheets.Add Before:=Sheets("Bet Angel") 'Insure correct spelling of "Bet Angel"
ActiveSheet.Range("A1:B" & CStr(RecordingLength)) = Recording End Sub Public Sub StopLogging()
If Running Then Running = False SaveRecording
End If MsgBox "Stopping Recording"
End Sub

Public Sub StartLogging() ReDim Recording(1 To RecordingLength, 1 To 2) Recording(1, 1) = "Changed Address" 'Recording(One, One) =... Recording(1, 2) = "Time of Change" 'Recording(One, Two) =... i = 2 If UBound(Recording) = RecordingLength Then MsgBox "Recording Array is defined" Else MsgBox "Failed to set recording Array"
End If Running = True
End Sub

Also, with regard to what I said earlier about the Weight of Money. I found a formula for a Money Flow Index which may be more useful in gauging the price:

Typical Price = (High + Low + Close)/3
Raw Money Flow = Typical Price x Volume
Money Flow Ratio = (14-period Positive Money Flow)/(14-period Negative Money Flow)
Money Flow Index = 100 - 100/(1 + Money Flow Ratio)

This site shows the VBA for MFI (Money Flow Index), it shows two methods. A is a function and B is a sub procedure to be quicker and more flexible.
http://finance4traders.blogspot.co.uk/2009/06/chaikin-money-flow-cmf-and-chaikin.html

Once you have shown me the full code, I will see if I can add this in so once done could you briefly explain me how to add indicators/functions to tweak my system and try to make it even more efficient by myself please? Again I really appreciate the help you have supplied thus far. I have an idea on how I want to triggers to call back/lay/close trade/green up commands. I will try and translate this into ‘excel talk’ and then from there dabble with ‘VBA talk’ but this will most possibly be a question soon in the near future. For now, I have the data you requested and eagerly await your reply.

Cheers,

CPerry.

[SamT]

It has shown 'error 9' when I click debug it says 'Subscript out of range'.
Yeah, I was afraid of that. That's why there are Question Mark Comments next to those to lines.
The report you just uploaded is the same as the first, just different values.

I'll get back soon on that

I still need a list of all the variables for all the formulas (even those that you might use in the future,) and what VBA Type values they are.Adding those variables now, sure makes it a lot easier to code the formulas later, and they take no time away from calculations.

[SamT]

Also, with regard to what I said earlier about the Weight of Money. I found a formula for a Money Flow Index which may be more useful in gauging the price:
I am designing this to be very easy to change the formula for WoM, or any other metric, without touching the rest of the code. I am assuming that you will tweak this as you get experience with it.

[SamT]

I am using lots of Class Modules (User Defined Objects) in this code. The advantage of UDOs is that they only use CPU cycles wile they are actually calculating, else they just sit there waiting for something to input or request a Property value. They also enable parallel processing, which speeds up the App if your computer can use it. Another utility of UDOs is that you only have to code one, and it can be duplicated and used in many different places. If you need to change the code inside a UDO, it does not affect any code outside the UDO. The only way to "communicate with a UDO is thru its Properties. This is the main reason why I always code all foreseeable Properties, although, they can be added without affecting any previous code.

This is the simplest UDO I have made so far. All it does is calculate a single EMA value from an array of values. This means, for example, that you would use it three times for MACD.
Option Explicit
'Class Module Name = "clsEMA_Calculator"

Private pSmoother As Double
Private pValuesSeries As Variant
Private pValue As Variant


Public Property Let CurveSmoothingFactor(SmoothingFactor As Double)
pSmoother = SmoothingFactor
End Property

Public Property Let ValuesSeries(FIFO_ArrayOfValues_ThisSeries As Variant)
pValuesSeries = FIFO_ArrayOfValues_ThisSeries
CalculateEMA
End Property

Public Property Get Value() As Double
'This Class's Default Property 'Thanks, 'http://www.cpearson.com/Excel/DefaultMember.aspx
'Note: Absolutely no leading/trailing spaces in Pearson's line of code
'Attribute Value.VB_UserMemId = 0

Value = pValue
End Property



Private Function CalculateEMA() As Double
Dim x As Double
Dim i As Integer

x = 1 - pSmoother 'Used for speed
CalculateEMA = pValuesSeries(LBound(pValuesSeries))

For i = LBound(pValuesSeries) + 1 To UBound(pValuesSeries)
CalculateEMA = pSmoother * (pValuesSeries(i) - x) * CalculateEMA
Next i

pValue = CalculateEMA
End Function

Private Sub Class_Initialize()
'I always start by placing Initiate and Terminate subs in all classes
'whether or not I foresee a use for them.
End Sub

Private Sub Class_Terminate()
End Sub



This is an example of how one might use the UDO clsEMA_Calculator

Dim ShortValue as New clsEMA_Calculator 'Creates a new Calculator in the ShortValue Object
Dim LongValue as New clsEMA_Calculator
Dim SignalValue as new clsEMA_Calculator

'Initilize each smoothing factor. only needs to be done once, since the UDO stores the value
'If you change the CurveSmoothingFactor for one or more UDOs, the next value it/they calculate/s
' will be based on the new CurveSmoothingFactor.
SignalValue.CurveSmoothingFactor = .9
LongValue.CurveSmoothingFactor = .9
ShortValue.CurveSmoothingFactor = .9

'For each set of Values, input an array of BA data for each Calculation
ShortValue.ValuesSeries = ArrayA
LongValue.ValuesSeries = ArrayB
SignalValue.ValuesSeries = ArrayC'
'Each time you input a new ValuesSeries, the next UDO.Value is instantly caculated based on the new series of data

'Later, someplace else, but after the ValuesSeries Arrays have been entered.
X = LongValue
'The above line is the same as
'X = LongValue.Value 'Value is the Default Property.
Y = SignalValue
Z = ShortValue

Any Questions?

You need at least a basic understanding of how VBA Class Modules work. I will be showing/explaining particular details of the ones I build for you.

[offthelip]

Hi Sam,
While it is a very good idea to program this using object classes, I don't think for a "real time" application that it is very sensible because the way you have coded this means that the comuter will be recalculating the entire ema series whenever a new value is added, this is going to gobble up processing time unnecessarily.

For i = LBound(pValuesSeries) + 1 To UBound(pValuesSeries)
CalculateEMA = pSmoother * (pValuesSeries(i) - x) * CalculateEMA
Next i

This loop is going to get longer and longer as the data keeps coming in.

One of the reason Welles Wilder used exponential moving averages when he wrote his book about various financial indicators ( RSI being one of them) was the calculation to update an EMA each day is very very simple, he could do it by hand since in those days there weren't any computers to do it.

The Op requirements seem to be that once every 200ms there are a whole series of indicators which need to be updated for each one of horses. There is probably only about 20ms to get this done before the next change event comes in. Thus recalculating the indicators must be done in the most efficient way. This means always saving the last time value of the indicator and recalculating the indicator using last time Indicator, new value and the time constant.

Finally Welles Wilder used a different definition of the time constant for his exponential moving average from the standard:

EMA formula = price today * K + EMA yesterday * (1-K)
where K = 2 / (N+1)


Wilder EMA formula = price today * K + EMA yesterday (1-K)
where K =1/N


Where N = the number of periods.

I have written a function to do the stand ema calcualtions:


Function EMA(Period, Lasttime, Drive)
If Not (IsNumeric(Period)) Then
EMA = "This functions calculates the Expontial Moving average, it has three parameters: Period (days), The last time Value of the EMA, the latest value of variable"
Else
TC = 2 / (Period + 1)
EMA = Lasttime + TC * (Drive - Lasttime)
End If
End Function

[SamT]

recalculating the entire ema series
That is a requirement of the App's sponser

Yes it would be marginally faster to
X = ((X*13) + X)/14
And I will probably use that in a slightly advanced form of the EMA Calculator, But I don't want to swamp CPerry right now.

Yes, I am well aware that Speed is of the Essence. Both the Sponsor and I must accept that some BA values will be missed. I am working to minimize that effect.

What Wilder uses is irrelevant to me. I only care what CPerry wants to use. That is one of the reasons for using UDOs as calculators, Each can easily be customized without affecting anything else.


I have written a function to do the stand ema calcualtions:
Function EMA(Period, Lasttime, Drive)
If Not (IsNumeric(Period)) Then
EMA = "This functions calculates the Expontial Moving average, it has three parameters: Period (days), The last time Value of the EMA, the latest value of variable"
Else
TC = 2 / (Period + 1)
EMA = Lasttime + TC * (Drive - Lasttime)
End If
End Function


That fails to meet the Timing Standards of the App I'm developing:
static values must only be entered at App.Initialization, Ie one time only.
LastTime is already stored in the UDO as pValue
Drive*, (*Not = "C:\",) is entered via the Property ValueSeries, but can be one value instead of an Array. I understand that you are using standard Financial terminology, but they don't make sense to anyone only familiar with VBA and or Bet Angel.

Because Speed is of the Essence, I am NOT including any error checking. We must be careful to not encode any wrong inputs/outputs. One reason I am Naming Let Property Sub Parameters with such long winded explanatory names. I may put some in temporarily as we get more advanced. Maybe with Compiler Directives. :dunno:

otl, you are much more experienced with Financial algorithms than I. If you want to to write a UDO for this Project just add your name and a link to the Post number as a comment. I don't promise that CPerry will use it, but I will place it in the Project.

Note that the naming convention I am using is in consideration of a new coder who understands and is using Bet Angel. The only times I deviate from this is when something out of that Domain is used, such as the clsEMA_Calculator name. The "EMA" should send any coder to Google to research EMA. The same with the up and coming clsMACD_Calculator UDO. The Property and Variable names will be taken from Bet Angel, but, again, the "MACD' should send any coder to Google. PLease! Use the same convention as I have started.

This will not be a highly polished App unless CPerry continues its development after we get it barely working properly with Bet Angel horse races.

[offthelip]

Hi Sam
First can I say that I am very pleased that you have picked this up because I didn't want to be helping Cperry on my own.
All I have been trying to do is to review how things are going and to make suggestions based on my experience in doing something very very similar for financial trading.
My main concern right from the start has been about time, see post #2. The requirements as specified by Cperry are going to be difficult to meet. 20 ms update rate, how many horses?? 10?? 15?? how many indicators ??
Thus I would design the whole system to maximise the throughput.
For example, I would recalculate one of the indicators every 20 ms( or update) and then cycle through the indicators in turn, thus if there are 10 indicators they would all get updated every 200ms but not all at the same time. This would then minimise the maximum calculation time. If there are more than 10 indicators then they won't update every 200 ms but the system will still run ok. i.e share the available time between them
Alternatively I would cycle each horse and calculate all the indicators for that horse every 20 ms, thus each horse gets upated every 20*N ms where N is the number of horses.

Minimising the Maximum calculation time is one aspect of maximising throughput without loss of updates.


I note that it is quite possible to use your object in support of this, since passing a single value as pvalueseries will do the calculation based on the three minimum variables. The way it is coded doesn't really suggest using it like this.


In response to you last post:

recalculating the entire ema series
The fact that the sponsor has asked for this does not make it the best way of doing this. Specially as it is unnessary


If you want to to write a UDO for this Project just add your name and a link to the Post number as a comment.

Thanks for asking, but I don't want to take this on, I am happy to make suggestions and happy for them to be ignored.
I am interested is seeing how this turns out.


What Wilder uses is irrelevant to me. I only care what CPerry wants to use.
I should have specifically addressed those comments about Wilder to Cperry because I totally agree he/she needs to decide what EMA calculation should be used.

[CPerry]

I will use the standard (9,12,26) EMA calculations to begin with and then explore changed values later; for the most part I want to keep with the default settings of these indicators before I start looking for my ‘edge’ in the market. I will do further reading in the basics of class modules so have a solid understanding of how these work. Again, thank you for your input offthelip! With how quick my system will hopefully be running, the odd missed numeral will not affect my trading strategy too much – as well I will be using very small stakes to start until I have fully tested the system to confirm that this wont prove to be too much of a downfall.

Until I get skilled with VBA, the system will only look at the front four horses and use 3 indicators, the next update maybe gradually increasing this to about 8 at an absolute maximum – it all ultimately comes down to running time for me. So the best system in my opinion would be one that is as accurate as can be, without sacrificing ‘number crunch time’ and it monitors all 4 horses (and each of their respective 3 indicators) simultaneously. The trades will only activate if all three of the triggers (one each from three different indicators) for one horse activates at least once within around 3 seconds of each other (a look back period of 15). I think I have worked out the value in which these indicators should trigger which will be coming up soon I presume. What additional further reading do you recommend I look at next? Also, are there any more tests on the live markets you need me to do at this moment in time, SamT?

Cheers,

CPerry.

[offthelip]

With 4 horses and 3 indicators, I would suggest that you calculate just one horse and one indicator on each iteration, this will give you a cycle of 12 which would repeat once every 240ms ( assuiming a 20ms update rate). I would guess that this should work ok.
Missing the odd numeral is not really the issue when it comes to running out of time. If the "onchange" event triggers before the last one has finished the code, the software will start running the same code again, this can cause all sorts of problems; mainly getting into an infinite loop where it can never "catchup". so the whole app justs hangs up!! It is possible to add code to prevent this by using a "code" started" and a "code Finished" flag. If the onchange event triggers when "code finished" is not set then exit the onchange event immediately. ( i.e. forget that update)


One think you might want to consider before you go "live" with real money, is doing some testing of the system using a demo account ( if Betangel provide that facility). If they don't provide that facility what you could consider is setting up your own "demo" system. You already have a very good system for capturing real live data from Betangel. What you could do is write a bit of software which will feed this data into the same cells a Betangel does in a loop. Where this has real advantage is that you can repeat exactly the same test many many times. This is particuarly useful for sorting out the overall integration of the indicators and the triggers. If you go "live" is is very difficult to work out afterwards what should have happened because every race is different.

[CPerry]

There is a practise mode present so I've just been spending a bit of time developing a way of presenting P/L in a way that's easy to identify pros and cons of this system i.e. which indicators are proving most useful, occurrences that get missed etc. Quite a lengthly process like all of this aha but hopefully soon all of this hard work will pay off. I'm doing my reading each day, watching trading videos, learning the terminology and trying to perfect every aspect I can think of. Looking forward to your next reply!

CPerry.

[SamT]

otl, I value your input and do carefully consider it.

I am thinking to capture the BA output into a Data Array UDO, This will be done as fast as the Worksheet Change Event can handle it. This is a simple DataArrayUDO.Add RangeArray operation, which should be pretty fast.

My thoughts are to have an "Accumulator" UDO that separates all the Brands from the Data Array into Brand Series. The DataArrayUDO will Push the data to the Accumulator UDO

As each Brand UDO is ready, it can access the AccumulatorUDO Dictionary by Brand Name to Pull only that Brands series.

My thoughts are to "touch" the BA sheet as little as possible. That might mean combining the DataArrayUDO and the AccumulatorUDO into one Object. We'll see.



Until I get skilled with VBA, the system will only look at the front four horses and use 3 indicators,Just another Cell on the "Settings" Worksheet.

External Links on Multithreading:
https://stackoverflow.com/questions/32848304/parallel-processing-in-excel-using-vba
https://msdn.microsoft.com/en-us/library/office/bb687899.aspx
https://msdn.microsoft.com/en-us/library/office/bb687868.aspx

To enable Multithreading, we will have to convert the internals of some UDOs to call VBScripts. This can, and should, be done after we get the BA Helper App working, however slow. And You will have to upgrade to Excel 2007.

Or we can move the BA Helper app to a different Workbook. The only interface between the two Workbooks would be access to the data and a "Writer" object to write the outputs from the App the the BA Sheet. This is actually a minor code change. Which is faster must be determined after both versions are working... After some slight consideration, I plan to convert the BA Helper App to a separate Workbook ATT. It will still work in Excel <= 2003, but will make a significant speed difference in Excel >= 2007

The goal is to minimize the interruptions to the BA Data Stream and allow the App access to the very latest BA Data, being aware that each Brand UDO will not ever see all the BA Data.

CPerry, can you arrange the BA Data Columns to suit the BA Helper App? I would like to see all the Data you want to process being in the closest columns to the Brand (Horse) Name Column ("B".) It's not strictly necessary, but the less work the code does, the faster it runs.

So... What do you think about that UDO I posted, the EMA Calculator? Do you see how the Property Subs Work?

BTW, C. You now have your own Folder on my HD :)

I have started designing and laying out a BA Helper App Help file (html) using a free version of PageBreeze. I will leave it to you to add most of the text to the file, and I will check it for errors and omissions after you do (or Don't) finish it.

My immediate task is to go back over this entire thread and exrtact the restrictions and requirements of this Project. Bleh

Do not worry if I miss a day or two here, I have other things going on... First, re-plumb my house, I have no inside running water at the moment:( See the Doctor about muh bum knee. Next, fix muh truck. Next ..., Next..., Next..., Etc, etc, etc.

Sam

[SamT]

Here is the crude first draft of the Tread's most salient points, from the viewpoints of a Project Designer and a Coder. It is just snips of Posts, Some with "Sam's Note:"s after the Snippet. Please note any significant changes you see, and answer any "Sam's Note" Questions.

[offthelip]

Cperry:
Looking at Sam's excellent summary of this thread, I have spotted that the indicator "Money Flow Index" is exactly the same calculation as the RSI calculation the only difference is the input variable is Typical price*Volume instead of just price. So you only need two indicator calculations EMA and RSI the others (MACD, MFI) are just combinations of these.

[offthelip]

Hi Sam,
Your "data array object" looks like it could be a very useful object for storing some of the intermediate values that don't need to be written out to the worksheet.
In particular the "ups" and "downs" needed to calculate the RSI. Also the first two EMa that are used to calculate the MACD, probably don't need to be displayed (obviously CPerry needs to make that descision)

Since the "look back" time required is only 15 samples, the object will only need give access to maximum of 15 samples.
So I see the data structure being
top level the DATAArray object, capturing the BA data
Next level the Accumulator object splitting this into Brand price data series
Next level Indicator objects that calculate and store the indicator data series

[SamT]

So I see the data structure being
top level the DATAArray object, capturing the BA data
Next level the Accumulator object splitting this into Brand price data series
Next level Indicator objects that calculate and store the indicator data seriesYep. Me 2

I'm thinking of putting a DataArray Object in the BA workBook With...

pValue as Variant
Property Get Value() As Variant 'Called from Helper Workbook
Value = pValue
End Property
Property Let Value(By Val LatestDataFromBA As Variant) ' Set by BA Workbook
pvalue = LatestDataFromBA
End Property Note: the size of the LatestDataFromBA array will be set by the User's Custom Settings.

... To further isolate the BA book from the Helper book. This really needs a multli-core processor and Windows 7 and Excel 7, But not Excel 10. MS Rewrote Excel 10 to make it a PITA to open multiple instances of Excel, which is best for utilizing Multiple CPU cores. However, the Project will run on a single core machine, but much slower.

@ CPerry, Buy a top of the line Gaming Computer, but you can substitute a Business grade Video card. Gaming Machines are all about computing speed.

Attached is a very crude drawing of the Physical Object Model of this Project. Zipped to overcome VBAX limits.
20713
Thumbnail, smaller than Attached Zip File.

@ otl, IYO would it be faster to use the current system with two workbooks?
OR, Would it be faster to Move all the BA Helper Workbook code to an XLA and have the XLA read the Custom Settings from the DashBoard and Settings Workbook and Write the outputs to both Workbooks. I know that it will be easier for me to use the current schema, but if the speed difference is significant... I'll try to suffer thru.

[SamT]

For Timing, I'm putting a "Ready" Property (RO) in Each UDO. As each UDO is finished calculating it sets it's Ready to True and "Raises an Event"/"calls" a Main Method, "getReady"

Dim pReady As Boolean
Sub GetReady()
pReady = UDO1.Ready And Udo2.Ready And UDOx.Ready
If pReady then
Write All outputs
Start Over
End If
End Sub

[SamT]

@ CPerry,

For ease of Testing, regardless of how we eventually organize the Project, we will keep it all in one Workbook for now. I am designing the Project so as to easily separate it into as many parcels as needed for speed and stability.

[offthelip]

Hi Sam:


IYO would it be faster to use the current system with two workbooks?

I used an addin in the application that I wrote, but I have no comparison with using two instantiations of EXCEL. At a guess I wouldn't think it would make much difference. I would hope that EXCEL would work out the multi threading as best it could either way.

I think the way you have designed it; keeping everything in memory, gives this a good chance of working either way .

I am still not sure whether a "free for all" with all the objects competing for processor time is the optimal way of organising this, or whether some sort of scheduler is going to be necessary. Is there going to be some way fo preventing one UDO getting priority over the the others. We don't need to recalculate everything all the time. It this going to be part of the EXCEL Helper??

[SamT]

This will be part of the Helper Book

Only new data will "raise" a Calculation. Since all Calculations occur in Child UDOs, the Main "Brand" Class can regulate when new data is added to a Child Object. This can easily be done with each Objects Ready Property. If needed.

"Ready" merely idicates that an Object is done computing it's outputs.

For Example the MACD Object uses three child Calculator Objects. When each Child is "Ready," the MACD is "Ready."

What do you think? Should those Caculator Objects have a single input value that can be either an array or a single value, or should they have two (SeriesIn* is W/O) Properties, an Array and a singleton? I'm thinking that a "Initialized" R/O Property can tell the Main Parent Object(s) which to use.

If UDO.Initialized THen
UDO = Singleton 'Value is Default UDO Property
Else
UDO.SeriesIn = Series
End If
Inside the Calculator

Property Let SeriesIn(*)
pValue = CalculateSeries(SeriesIn)
End Property
Property Let Value(*)
pValue = CalculateSingleton(Value)
End Property
Property Get Value()
Value = pValue
End Property


I am thinking that an If... Then... Else... that assumes Initialized is a very fast method of decision making

[CPerry]

20716
Hi lads. I look forward to helping develop the BA help app once it’s up and running, I should be able to assist in some small way from the start and even more as I grow in confidence and ability. I know all about never ending lists, I am a Year 4 teacher in a local primary school and there just aren’t enough hours in the day! I hope you get your water running soon and good luck with the knee!

I am currently running excel on a 13-inch mid 2010 MacBook Pro with a 2.4GHz Intel Core 2 Duo processor and 2GB 1067 MHz DDR3 memory (I have bigger memory modules ready to transfer in). I am running Windows on a partitioned hard drive (with about 80GB of free space). With regard to excel, I am currently running Microsoft Excel 2016 MSO 32-bit. Is anything after 2007 i.e. 2016 fine for multithreading? For quicker speeds, safety and efficiency I was thinking of eventually, or rather really soon, upgrading to a private VPS/VPN. Would this work or are there implications that arise with these? BA offer a standard one by the looks of things but only with average speeds of 30ms, I’m sure there are better online…
https://www.betangel.com/vps/

Apologies if I have missed anything, here are my replies to Sam’s notes:
No need to keep the results, I have been working on a way of recording P/L via BetAngel after the races have ended.

If Range("P45:P103").Value = "PLACED" Then Range("PLACED").ClearContents
Very important!

Yes, whenever I say 3 seconds I mean a look period of 15 iterations.

Money flow index (Uses the difference of money flow (volume at best three back and best three lay prices in it’s calculation so it’s a more detailed formula to show volatility than WOM I assumed, its purpose is to show false signals and again be a ‘confirmation’ trigger but if WOM does this adequately IYO then that’s fine.

By front four runners I mean the ones that are trading the most prior to the 10 minute out period when my system will start. These will be locked in position (Rows H, J, L, N) to avoid confusion.

Regarding back/lay (see the attached picture):

Please excuse the crude sketch of a random oscillating wave. This example follows a RSI. The first signal is when the RSI line (Check it’s only one line) crosses above the 30 mark so I will ‘Buy’ or ‘Back’ when it crosses the 50 line, I will ‘Sell’ or ‘Lay’. Then Lay when it crosses from above the 70 line, Back when it crosses the 50 line. On top of this, if a signal reverts back to it’s original position i.e. in the first example, the crosses above the 30 mark but it could then have dropped back below 30 instead of crossing the 50, in this instance I would ‘Close Trade’. I believe this is the simplest system that I can then tweak, add to and develop further in the future for all indicators. The only difference between this and a conventional RSI is I don’t just buy at over/under sold levels but exit trades when they reach stability again – this level can and will be tweaked with. Now once these ‘triggers’ activate they need to be ‘remembered’ within a look back period of 15 so if all three triggers activate within 15 intervals of one another, a ‘Back’, ‘Lay’, or ‘Close Trade’ will commence.

To open a trade at all, all three must trigger. To close a trade, whether that be ‘Back’, ‘Lay’ or ‘Close trade’, only two out of the three indicators will need to cross the trigger threshold within a 15 period of each other. Hopefully this simple trigger system will help in your decision regarding your last post. I understand the first part of having separate child calculation objects running independently but not whether it should have a single input value or have two, sorry; hopefully something offthelip knows the answer to.

The MFI (If used) will work the same way. The MACD will simply be when the lines cross above the signal (as default) then a “Back” will trigger, if the line crosses below the signal line then “Lay”.

Just to clarify before I do this wrong, what do you mean by you want me to arrange BA data columns? You want me to make a new template sheet like the one BetAngel have supplied with the green and white stripes but with information most relevant to my system? How will we direct information to go into the correct cells. Do I need to use/manipulate the current set up or am I missing the point altogether?

Cheers,

CPerry.

[offthelip]

Hi Cperry:

I am running Windows on a partitioned hard drive (with about 80GB of free space). With regard to excel, I am currently running Microsoft Excel 2016 MSO 32-bit.
Does this mean that you are running Microsoft office (EXCEL) under windows as a virtual Machine on your Macbook Pro, or are you running the Mac version of Microsoft Office?


2.4GHz Intel Core 2 Duo processor and 2GB 1067 MHz DDR3 memory

Is a fairly slow machine by modern standards. Also I notice you are using the 32 bit version of EXCEL. the 64bit version is much faster (in theory).

The good news about this is that you have got plenty of scope to improve perfomance by upgrading to a faster machine. I agree with Sam's suggestion get yourself a gaming desktop machine, with a cheap video card.
Just as an aside I always use a gaming keyboard with my desktop because the response is so much better and faster. ( I never play games on the computer!!)

[offthelip]

Hi Sam:

What do you think? Should those Caculator Objects have a single input value that can be either an array or a single value, or should they have two (SeriesIn* is W/O) Properties, an Array and a singleton? I'm thinking that a "Initialized" R/O Property can tell the Main Parent Object(s) which to use.

I think that when initialising a calculator using it is useful to be able to pass an array, ( see the initialisation in my RSI function) however when running I think we should always pass a single variable, since this is all that is necessary and it will minimise the calculations. The only exception to this is if Cperry adds any indicators that are not based on EMA type calculations, currently all the indicators are based on EMA calculations.
Also I think the calculator object should store the whole series from when it starts ( it is all in RAM anyway so won't take any time or significant space.) this can be useful when trying to debug the system. i.e when looking at it, we think it should have triggered and we find it doesn't or vice versa. By having a record of every calculation will allow us to go over it in detail to find where the code has gone wrong. So I think the calculator object should have three gets for values , get single value ( the latest), get 15 values ( last 3 seconds) , get the whole lot, however many it is.

[SamT]

Is anything after 2007 i.e. 2016 fine for multithreading? They will all Multi-thread, but I haven't found a way to put the Helper Excel into it's own process after 2010. IMO, the fastest will be keeping each instance of Excel as separate as possible.

Keep in mind that Excel is a native Microsoft Application. MACs must jump thru loops to run it, thus slowing it down. I think that a Windows 7 machine with Excel 7 will provide more ROE than a VPN. On Ebay, today, I saw Office 7 suites for $25us and up and many Windows 7 CD's for less than $50us. On Craig's List, here in Missouri, there are many high end Gaming Computers from $500us to $2500us.


Just to clarify before I do this wrong, what do you mean by you want me to arrange BA data columns?
The less interactivity BA has with the Excel Worksheet, the faster the entire system will be. Interacting with a Worksheet always takes a relatively large amount of time and CPU cycles.

The more BA Data you can remove from the BA sheet, the better. All I, as a coder, want to see on that worksheet, is the Data my Code needs to work with. Do the best Bet Angel will let you get away with. Keeping in mind that you will want to refine your system in the future, however, just keep it to Races for the nonce.
e
The "Custom Settings" sheet, on the third hand, should be developed with a cell for every possible current and future setting. This is where you define what possible values to use in the Projects calculations. For example, You might use WOM or Monet Flow; Define all settings that either may use, Remember that I defined different Smoothing and Series Length inputs for all three WOM series thus allowing for future use right at the beginning. I will try to get a sample "Custom Settings" Sheet for you to start with.


How will we direct information to go into the correct cells.Via the "Custom Settings" worksheet.


WOM does this adequately IYO then that’s fine.That is a decision you must make. I just design the App so it will be "easy" for you to experiment.

[SamT]

I think that when initialising a calculator using it is useful to be able to pass an array, ( see the initialisation in my RSI function) however when running I think we should always pass a single variable, since this is all that is necessary and it will minimise the calculations.Oh, yeah!


Also I think the calculator object should store the whole series from when it starts
I'm thinking to keep all that in the Data Accumulator Object, with a possible Upper limit set on the Custom Settings Sheet. This also allows for simple Whole Race Logging if the User wants. It still allows for testing of all calculations that need any length series.

I see the Data Flow as:


The BA Worksheet_Change Pushes the entire BA Data Table to the BA.Output Object
The Helper.Accumulator, Pulls the latest BA Data Table from the Output Object

The Acumulator parses the Data and appends it into a FIFO Object, by Brand Name.


Each Helper.Brand Object Pulls it's data, by name and series length, from the Accumulator.
Each Brand object parses this data by Fields and Pushes it to the Calculator Objects.

The Calculators only store the last input and the output




#3 Acculumator Method:
Public Function Value(BrandName As String, Optional SeriesLength As Long = 1) As Variant
'Code TBD
End Function
I will see if one can set a Let .Value Sub as the Default Property and if that sets the .Value Method as the default Output Property. It would be nice
Accumulator = BA.OutPut
Me.SeriesInput = DataAccumulator(Me.Name, SeriesLength)

I am starting to think that the DataAccumulator Object will be doing as much work as the rest of the Helper book put together. See 2.a above.

NOTE TO SELF: Each Objects inputs set pReady = False. Each Object has IsReady Procedure to check all Child Objects Ready Property. If all Child's are True, sets Parent's pReady to True.

[offthelip]

I'm thinking to keep all that in the Data Accumulator Object, with a possible Upper limit set on the Custom Settings Sheet. This also allows for simple Whole Race Logging if the User wants. It still allows for testing of all calculations that need any length series.

I like the idea of this, it keeps all of the series in one place, it will make it easier to spot timing issues between different parts of the system.


Each Brand object parses this data by Fields and Pushes it to the Calculator Objects.The Calculators only store the last input and the output



So I presume that the Brand object is going to push the new indicator values back to the Data accumulator object.

Is the Brand object also going to be the one that tests last 15 samples of the three indicators to see is a trigger has occured, or will you do that in another object. I would favour a separate object for doing this partly to make the Brands object simpler ( but not simple!!) and also to make changing it easier.

I am not sure where the split between a 20ms update rate and a 200ms update rate is occuring in your model, is it the Helper.Accumulator that is going to do this, so the FIFO per brand name is updated every 200 ms (10 frames) of is the Brand object going to do this, so it only call the calculator units once every 10 updates?

[offthelip]

Hi Cperry,
You need to expand on your set of rules for the RSI trigger: because I can see a number of conditions that you have not catered for, eg:
1: What happens if the RSI drops below 50, so you open a sell, and then it goes up to 70 without going below 30 ( this is very likely to happen). do you open a second sell.
2: If the rSI goes above 50 so you open a buy and then drop below 50, do you sell immediately? If the indicator is oscillating around 50 you could burn up a lot of money being "whipsawed"
3: If the RSI drops below 30 and the comes above 30 but the drop through 30 again do you open 2nd buy position ?

I am not sure I have catered for all conditions here, but you must think about the full set of rules you need, computers are really dumb, they only do what you tell them,

[SamT]

So I presume that the Brand object is going to push the new indicator values back to the Data accumulator object. I hadn't considered that. I would probably Push the results to a different Object, just for debugging logging. Turn it off for speed during Production runs. Just another Custom Setting.

I am not worried about Startup and Initialization times. I consider that to be a Capital Expense. I want to minimise Operating Expenses, CPU load and Time. The only OE I want is the Calculator Objects, and, data Passage and Parsing. Everything else should be a Capital Expense.


Is the Brand object also going to be the one that tests last 15 samples of the three indicators to see is a trigger has occurred, Do that in another object.


where the split between a 20ms update rate and a 200ms update rate is occuring in your model, I'm ignoring CPU Timing. The Brand Object is all based on when an object is ready. Since the Main Brand Object is "Ready=True" as soon as all its Children are ready, It will trigger another round of Calculating and data Transfer as often as programmatically possible. I think that as soon as the Brand Object Pushes its Decisions to the Excel Helper Application's CellPrinter UDO, the Brand starts over with the latest BA output.

In Fact, the Excel Helper Application's CellPrinter UDO might be the one to log that data stream. It will be in a different Process.

NOTES TO SELF: All Parent or Container, Objects need Ready=True Event
pReady = Child1.Ready And Child2.Ready And...


Do While Not pReady 'Any good ideas for this?
DoEvents
Loop

'Ouput all data, Main Brand Object only
Application.CellPrinterObj = Me.Data 'Array
pReady = False

'Or, Output Data, then
Do While pReady 'OutputValue Property Gets cumulatively reset pReady to False when read
DoEvents
Loop

Me.Start Over





Local and Global Reset Methods
Method ResetGlobal() Passes ReSetGlobal to all Childs
Method Reset("Child") Passes ResetGlobal To Named Object
Need good ChildCollection Object
Need pPArent Object in each Child









@ All
Given good Child Collections and pParent Objects, can a grandChild and a grandParent use each others Properties?

As simple as GloballyUniquePropertyName = X?
Or something like Me.Parent.Parent.Property = X?



Can a Cell in one instantiation of Excel refer to anything in an different Instantiation of Excel?
How?



Thanks. Off to DuckDuckGo now

[SamT]

From my Previous:
Can a Cell in one instantiation of Excel refer to anything in an different Instantiation of Excel?
How?
DuckDuckGo Says, https://support.microsoft.com/en-us/help/555159

[SamT]

From my Pre-Previous
From grandParent to grandChild
Duckduckgo says:
https://stackoverflow.com/questions/21767307/sub-property-in-vba-excel-class-module
http://vbi.org/Items/article.asp?id=78
http://www.vbaexpress.com/forum/showthread.php?21358-Implements-Keyword-in-VBA



@ CPerry, you might want to look at this since we are doing a lot of talking about, and your Project is being built with, Class Modules.
https://excelmacromastery.com/vba-class-modules/

[SamT]

This might be a good computer for you, 549 € and up, depending on selected internals
https://slimbook.es/en/power-minipc-one

They're in Spain, if that means anything,
I'll bet they do customs.

[offthelip]

I'm ignoring CPU Timing. The Brand Object is all based on when an object is ready. Since the Main Brand Object is "Ready=True" as soon as all its Children are ready, It will trigger another round of Calculating and data Transfer as often as programmatically possible
I am worried about this, it is what I have been worried about right from the start. It is unneccessary to calculate everything as fast as possible and it will limit the thoughput if you do. I am not talking about CPU timings I am talking about update rates and iterations rates.

The data update from BA comes in every 20 to 50 ms.
Cperry has clearly stated that he would like to see an update every 200ms, which seems a reasonable update rate.
To do the OHLC calculation the BRAND needs to check every update, but it doesn't need to recalculate the indicators or save the OHLC and start a new one.
It only needs to do this once every 200ms. Also we do not need to upate all the Brand simulataneously.

If you recalculate every Brand calculation as fast as you can, you are wasting processor time because you could well be doing the recalculations more frequently than once every 200ms. There is no requirement to calcualte the indicators that fast.
This means that once you have two or three "brands" running you might run out of power. While if you recalculate each Brand only once every 200ms you can calculate far more of them.

As an example assume it takes 10 ms to update one brand. If we recalculate two brands we will just about get this done in between data updates and everything is then running flat out.

What happens if we now add a third brand? How is the processor time shared between the three brands? We know that we will get a data update before they are finished, what happens if one the Brands always finishes in less time that the others does that mean it gets calculated more frequently? What happens if the brand does a recalculation before the data had been updated.

An EMA time series only works correctly if we have fixed (or at least known) time intervals between each data update. How are we supposed to calculate the Time constants for EMA if we don't know how often the series is being calculated.


There is another issuse with this in that the requirement for a trigger is to look at the last 3 seconds worth of data and indicators. If you are writing data out as fast as you can, how are you going to tell how long the the array is going to be for 3 seconds worth?

I still favour a deterministic approach where we control what is calculated and when as I outlined in my post #50.
This will give us update for the calculated arrays every 200ms. The only issue being how we determine when the 200ms is due.

[SamT]

It is unneccessary to calculate everything as fast as possible and it will limit the thoughput if you do.
First A Question: Which is easier? Slow down an APP or Speed it up?

As I see it, you are saying that is is faster to do a sequential calculation that takes a few thousand CPU tiks every 500 million CPU tiks than do perform the same sequential calculations on demand every several thousand CPU Ticks.

This App is designed to be optimized for speed by both the VBA Compiler and the Windows Operating system. It has plenty of all needed delays built in, in fact I'm putting in too much delay, but all delays are fine tunable. That is something CPerry can do AFTER I have a FrameWork for him. The first such Delay is that the BA Application only outputs one table of Updated Data every ten updates. The Main Helper Controller Object cannot even Read the parsed Data until the Helper's DataParser gets done splitting all the BA data into Brand size Bites and turned its own (Read) Ready Property to True. Another built-in Delay is that the Main Controller Object must wait for every Brand Object to be Ready = True before it can Write new Data to it. Below that, The Brand Object is not Ready = True until A: it has completed all computations and B: it has passed all its outputs and is idle .

You do brring up a thought about ThroughPut Timing snafuus, For Maximum speed of Throughput, I see a need for a Boolean Dictionary, keyed by Brand.Name and whose values are Brand.Readys, The Main Controller BrandUpdater UDO will use

For Each Brnd in Brands Collection
If BrandReadyDict (Brnd.Name).Ready
Then Data Dump Brnd
Maybe later if it's an issue


An EMA time series only works correctly if we have fixed (or at least known) time intervals between each data update. There is no fixed time interval available, That is provided by an out Object over the internet.


How are we supposed to calculate the Time constants for EMA if we don't know how often the series is being calculated. By using BA Update tiks, or as close to a constant fraction thereof as we can manage. Everybody involed undertands that an Update tic is as close a timing mark as is available. We cannot Force BA to follow any timing system we use. Our Timing must be based on the BA. The Curves the outputs are based on will be a little fuzzy, but close enough and a whole lot faster than using a TI Scientific calculator. The Prices in a Race just don't move that fast.


If you are writing data out as fast as you can, how are you going to tell how long the the array is going to be for 3 seconds worth? When the BAUpdateTikCounter reaches about 150? 1500? Hmmmn. I think that Wild Turkey is getting between me and my brain.

Please : pray2: keep your comments coming, they're really helping me see things more clearly.

Just what are we talking about besides Data Flow? Each Brand is only tracking three to six curves. There is a stated limit of About 30 Brands. 180 Calculations on a fast MultiCore with Multithreading? Plus the Boolean Decision Tree. Writing the data to Cells is NOT a Brand UDO function.

@ CPerry, Think of a Class Module's (a UDO's) Property pVariable as the name of a particular address in RAM. The beauty of VBA's Classes is that a Child and a Parent can share the same piece of Memory with different Address Names. There is no Throughput overhead in that case. WAIT! I think that Turkey interpreted what I tried to say.

Good night
Sam

[offthelip]

The first such Delay is that the BA Application only outputs one table of Updated Data every ten updates. The Main Helper Controller Object cannot even Read the parsed Data until the Helper's DataParser gets done splitting all the BA data into Brand size Bites and turned its own (Read) Ready Property to True.


This answers my question:


I am not sure where the split between a 20ms update rate and a 200ms update rate is occurring in your model, is it the Helper.Accumulator that is going to do this, so the FIFO per brand name is updated every 200 ms (10 frames) of is the Brand object going to do this, so it only call the calculator units once every 10 updates?


However it immediately raises another question:
Who is doing the OHLC calculation.
I presume from what you have said that the BA Application is not doing this, what this means is that once every 10 updates the BA application write out 10 updates, the Main helper unit process this and then the Brand unit goes through the 10 updates to do the OHLC calculation before it can start the indicator calculation.
I sure you can see that if the BA application did the OHLC check on every update then when it came to do the update on the tenth update, there would only a single comparison to make and so the OHLC check will take less time on that particular update, also it would only need to output 4 variables instead of 10.
Note I am not suggesting the BA application does the OHLC check, I am just using this to illustrate that controlling when calculations are done can improve throughput enormously.
As I stated in my earlier post #50:

Minimising the Maximum calculation time is one aspect of maximising throughput without loss of updates.



There is no fixed time interval available, That is provided by an out Object over the internet.


How are we supposed to calculate the Time constants for EMA if we don't know how often the series is being calculated.
By using BA Update tiks, or as close to a constant fraction thereof as we can manage. Everybody involved understands that an Update tic is as close a timing mark as is available. We cannot Force BA to follow any timing system we use. Our Timing must be based on the BA. The Curves the outputs are based on will be a little fuzzy, but close enough and a whole lot faster than using a TI Scientific calculator. The Prices in a Race just don't move that fast.


If we are going to use the BA updates as our “clock” we need to find out how accurate that is and how much it varies. Does the update rate vary depending on the race?, the number of horses?, the internet connection??, how long it is to the start of the race, does it vary during the 10 minutes up to the start of the race.
Since one of the requirements is to get a reasonably accurate time dependent calculation, we need to be able to adjust our “smoothing” constants in the calculator objects depending on what the actual update rate is. We also need to cater for the last 3 seconds of data being a variable number of items.

One thought I had for CPerry : “ Does betangel time stamp the updates?” if they do that would be great. ( Note in the financial application I did the data was time stamped, so problem solved)
So if we are going for a variable recalculation rate ( max) we are going to need to do a lot of extra processing to sort out the time. Is this really going to give us a greater throughput than building it as a deterministic system where we control what gets updates and when?

[offthelip]

I have been thinking further about how to solve the variable update rate problem which the system design has.
We know that the Now() function gives an update every second.
The BA Application could use this to count how many updates arrive every second. We could then use this as the "update rate" for the next second.
Hopefully it won't change much between one second and the next.
We can then use this factor to modify the "smoothing" constants" in all the calculation objects

One way where we can test how accurate our timing is, is to creat a test object that just counts our "time" and then outputs it so that we can check it against a time recorded from the computer.

[SamT]

Taking them in order, as I am answering before reading both posts.

I presume from what you have said that the BA Application is not doing this, what this means is that once every 10 updates the BA application write out 10 updates, the Main helper unit process this and then the Brand unit goes through the 10 updates to do the OHLC calculation before it can start the indicator calculation.Every ten pdates the BA Outputs one update.


e need to find out how accurate that is and how much it varies. Does the update rate vary depending on the race?, the number of horses?, the internet connection??, It is what it is, but, it's in one of my previously attached Zipped text files. Average about 51 ms, occasionally, 102ms. Ballpark.


We also need to cater for the last 3 seconds of data being a variable number of items.Thei Items count is specific = 180seconds / (~51ms x 10 / Other delays) or 15 (or) 140 items.Ask CPerry for specific number. It totally depends on BA over the web, the Worksheet_Change Event, and all other delays. The change event test log says updates @ ~ 50 ms.


We know that the Now() function gives an update every second.
The BA Application could use this to count how many updates arrive every second. We could then use this as the "update rate" for the next second.
Hopefully it won't change much between one second and the next.BTDT. See The change event test log.

"Perfect is the enemy of good." due to all things considered, we can't do perfect. We're aiming for goodest. :)

[offthelip]

Every ten pdates the BA Outputs one update.
So Ba must do the OHLC calculation.


It is what it is, but, it's in one of my previously attached Zipped text files. Average about 51 ms, occasionally, 102ms. Ballpark.

So you are making assumptions that it is going to be good enough. What are you going to do if it isn't accurate enough?? How are you going to determine whether it is accurate enough?

What I have been trying to do by "reviewing" the design is to draw attention to possible problem areas particularly where a modification at this stage will allow for greater flexibility later when any possible problems appear.

You are obviously welcome to ignore my comments about time and how I believe that keeping accurate time is going to be important for this application.

I notice from your post #18:


I love teaching. And Programming. I've been doing one or both for 47 years.

Unfortunately I have to admit that I have been doing it even longer, I started as a trainee programmer at IBM in 1968 in my first vacation at University
More significant , I specialised in high tech realtime applications and I have been working on real time systems since 1974. (The very first one I worked on had an update every 50ms, which was darn quick in those days!!)

I think Cperry should be grateful that there is nearly 100 years of experience going into developing this app!!

[CPerry]

20725
Wow a lot here which is excellent so let me just reply as I read through each post in turn.
I have asked my dad’s friend to get me a good gaming computer with the specs you have mentioned previously and a 64-bit version of Windows Excel 7, this running on a Windows 7 machine should be with me very soon.

Again, please excuse yet another crude drawing of an oscillator but if you see my drawing (attached file) I think I have shown what you correctly pointed out I had missed last time. That being, when the line crosses back above areas that don’t really benefit me.
The purpose to this is to have more customisation capability, instead of just one/off at 30 and 70 levels, or having a single kill at 50 (for both directions), I can experiment with changing these variables.

I will now go through each cross over point in turn:
1) Crossover below 70 so "Lay"
2) Crosses 50 "Back" or "Close Trade" Woo hoo profit, hopefully!
3) Crosses below 70 once more so "Lay"
4) Crosses above 70 (either ignore being so soon after the first cross or cancel - which would you say?)
5) Crosses below 70 again. (Depending on the last cross, we now either have a double stake or a fresh "Lay")
6) Crosses 50, woo profit!
7) Crosses above 30, "Back"
8) Crosses 50, "Close Trade"
9) "Lay"
10) "Close Trade"
11) "Back"
12) Polar opposite as crossover 4, either "Back again" or ignore as being so close to the previous cross point??
13) "Back"
14) "Close Trade"

So the question is, double the back/lays each time there is a cross or put an ignore command in if so close? We also need to consider the impact this will have on the bigger picture, is it worth 'ignoring' if it doesn't trigger anyway? No bet will activate unless all three indicators react so, what do you think? Martingale double triggers, or ignore them if they don’t activate within 3 seconds of the other two triggers? This format can easily be replicated in other indicator models that I may use.

Other indicators? Black Scholes, Fibonacci, Stochastic Oscillators, CCI etc. Most of what I’m starting out with follows basic moving averages but then gets more complex. At the moment, I’d just like to get something working that I can build upon. As you said in your last post, perfection is unlikely – great will do for now until I have time to really build upon it. I can not wait to get this running and really get into the knitty grittiness of the real markets.

I can take away from the main sheet: Stall number, Reduction Factor, Projected Starting Price and Actual Starting Price. This is the area to the right of the green and white stripes. I am keeping ‘Volume Weighted Average Price’ as this I expect will be useful for future calculations. I am looking into stripping away even more, i’ve posed the question in the forum so hopefully a more experienced user will give me the most efficient way of doing this. I have also asked the question about time stamps just for future reference.

The other cells are useful as I still need to include a "Green_All" about 10 seconds before the start, so this is when the VBA code needs to end. I was simply going to do this via a function as there will be no other processes commencing at this time for the CPU to fight for.

One thing I am still unsure of, and you may very well know the answer offthelip, is in regard to quicker and more volatile markets. For example, those instances when the market shoots up with money still waiting, if I don’t get matched at 4.5 and the market suddenly drops to 3.5, I have missed the opportunity to make some decent profit. any tips for getting ahead of the price in volume-heavy volatile markets?

Cheers,
CPerry.

[CPerry]

Your last message was sent as I was constructing mine so I missed it. I am eternally grateful that I have two genius blokes with over 100 years of programming expertise developing this app. The amount of detail that has been discussed is truly impressive. Again, thank you both very very much.

[offthelip]

I have just done a quick analysis of the recorded data in "data_for_samT2.xls"
What I see does worry me:
At 19:06:59 there is a big gap in the data the next update doesn't come in until 19:07:02

Unfortunately this is the only tick which goes over the minute.
There are 8 seconds out of the 54 seconds recorded when fewer than 10 updates arrive. That is considerable variation.

I would suggest doing more recording, including recording the whole 10 minutes of a race, to determine whether the "over the minute" problem appears all the time or whether it was just a one off. Also to see how much the update rate does vary.

[SamT]

So Ba must do the OHLC calculation.I think that is our disconnect.
It seems to me that you want to base the math on capturing every updated value, but the OP has said that using 10% of all updates is sufficient. He has also said that some additional "dropped" Updates aren't significant, all due to the slow rate of change in all inputs.



We don't know the Rate of Change, although I asked CPerry to run some code that will provide that data. I will worry about it when I see it.

All we really know about the effective Update Timing is that when we use the Worksheet_Change Event to count Updates, it averages about 51ms, with a dropped update(s) about every 200-500ms.

All "fixed" calculating Parameters (Series Lengths, et al) and Sampling Rate parameters will be set by the User.
'SamplingRate Eample
Sub BA_Worksheet_Change(...)
UpdateCount UpdateCount + 1

If UpdateCount >= UserSamplingRate Then
EnableEvents: Off
Push(UpdatedTable) to BAOutPutObject
UpdateCount = 0
EnableEvents: On
End If
EndSub
Right now, I am still developing the Object Model for the FrameWork of the App. After the OP understands the code and has experience using it with BA, he can upgrade it to using hard coded parameters vice User inputs, if he desires. This is as much a tutorial as an App.

[offthelip]

Cperry:

One thing I am still unsure of, and you may very well know the answer offthelip, is in regard to quicker and more volatile markets. For example, those instances when the market shoots up with money still waiting, if I don’t get matched at 4.5 and the market suddenly drops to 3.5, I have missed the opportunity to make some decent profit. any tips for getting ahead of the price in volume-heavy volatile markets?

Answering this really depends on the market you are betting on, I know nothing about horses at all.
I can answer in terms of financial markets in that one way this can be accomodated is by using Adaptive EMA. By this I mean when the marktet is more volatile ( often detected by looking at the standard deviation of the price) you can reduce the EMA for a MACD by a factor, e.g for example change MACD (9,12,26) to the same numbers time 0.8 which gives MACD (7.2, 9.6,20.8)

[SamT]

CPerry

Most of your post #80 is way too early for me. However:
Other indicators?Start building those particular Calculator Objects and the particular "formula" Objects they need to use. You need the coding experience, and I will refine and add them to the project for future use as needed. Note that we can add many, many Class Modules (UDOs) to the code but they will not be used until they are Instantiated by some bit of Code elsewhere.


i’ve posed the question in the forum so hopefully a more experienced user will give me the most efficient way of doing this.Great!


need to include a "Green_All" about 10 seconds before the start, Can be automated with timing set at start of Helper App.

CPerry, there are many "Hats" involved in creating an App like this, The User Hat, The Developer or Designer Hat and the Coder Hat. The Developer sits between the User and the Coder.

The User: That's you
The Developer: That's me, but training you to take over
The Coder: That's me, for now

The User is the only Hat that knows BA and what the Helper needs to output and what it needs From BA to calculate those outputs. The User Hat understands the Language of BA and Finances

The Developer Hat must translate the Users BA Language into Excel Language (cell Addresses and so forth) for the Coder, because the Coder only cares about Excel and Code. The Coder Hat doesn't even care what BA is or does. All the Coder Hat understands is Excel and Code.

Cperry, you need to start talking to us like a Developer Hat to a Coder Hat. Instead of saying, "BA Oldest Price," say. Excel BA sheet Column "F" of Table "C35:K & LastRow) by Brand, (horse.) Note that you only need to define the BA Inputs table once, then name it and thereafter refer to it as "The BA Data. Then you can say something like "BA Data", Column F



As an example, Consider
1) Crossover below 70 so "Lay"
2) Crosses 50 "Back" or "Close Trade" Woo hoo profit, hopefully!
3) Crosses below 70 once more so "Lay"
4) Crosses above 70 (either ignore being so soon after the first cross or cancel - which would you say?)
5) Crosses below 70 again. (Depending on the last cross, we now either have a double stake or a fresh "Lay")
6) Crosses 50, woo profit!
7) Crosses above 30, "Back"
8) Crosses 50, "Close Trade"
9) "Lay"
10) "Close Trade"
11) "Back"
12) Polar opposite as crossover 4, either "Back again" or ignore as being so close to the previous cross point??
13) "Back"
14) "Close Trade"


As a Coder Hat, I understand zero of that. I can surmise that "BACK," "LAY," and "CLOSETRADE" are outputs of some Calculator UDO or some Decision Tree UDO to BA, But that's about it, and I only get that because I have been working with you and BA for some time.

As one Developer talking to another or a Coder, saying something similar to what you said...

The MACD Decision making UDO, (already talked about) has Inputs: CrossOver, Lower, and Higher: Price1 BA Data, Column C; Price2, BA Data ColumnD; and Price 3, BA Data ColumnE

This UDO uses three EMA UDOs, (already named and code) to provide three numbers, Long, Short and Signal.

The UDO compares the Long, Short, and Signal to create a Value

When this Value Rises above CrossOver, it Sets the output to "BACK"
When the Value Drops below CrossOver, it Sets the output to ""
When the value Drops below Lower, it Sets the output to "CLOSETRADE"
When Value Rises above Lower, Sets the output to "LAY"
The output value must be set to the same value 3 times consecutively before an Output is actually done
Etc,Etc,Etc,
Yeah, I know that doesn't make sense (it's actually wrong) to you as a User Hat, but, it needs to make sense to you when you wear your Developer or your Coder hat. Even though it's wrong for BA, I can write a UDO that will do exactly what that says.

In order for you to learn how to continue developing and refining this APP, you need to learn how to think like a developer, not like a User.

Homework Assignment: Try like the dickens to use Developer type language in VBA Express posts.

[SamT]

CPerry
Customizing BA Columns

https://www.betangel.com/user-guide/custom_columns.html?ms=QgAAAAABQA%3D%3D&st=MA%3D%3D&sct=OTQ%3D&mw=MzIw

Drag 'n' drop columns (https://www.betangel.com/user-guide/drag__n__drop_columns.html)
Saving Custom Layout (https://www.betangel.com/user-guide/saving_custom_layout.html)

[offthelip]

Cperry,
I notice that Betangel does supply an "update time" which appears to be D39 on your Betangel spread sheet.
I would be very interested to know when this gets updated and what the granularity of it is, so could you modify SAM excellent logging routine and run some more tests to record this as well.

I have made the modification the code below which I copied from Sam's post 21 so it might not be your latest code, howeveer this should show you what changes are necessary to record this additional item.


Option Explicit
Const RecordingLength = 3000 'Records. 'Adjust to suit Adjusted to get a bit mnore data
Const RecordingTime = 3 'Minutes 'Adjust to suit

Dim Running As Boolean
Dim Recording(1 To RecordingLength, 1 To 3) ' add additional column to output array
Dim i As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then Exit Sub
If Target.Row < 39 Then Exit Sub ' changed to check when the update time changes

Recording(i, 1) = Target.Address
Recording(i, 2) = Now
Recording(i, 3) = Cells(39,4) ' ( cells D39)

i = i + 1
If i > RecordingLength Then
Running = False
SaveRecording
End If
End Sub

Private Sub SaveRecording()
Worksheets.Add Before:=Sheets("Bet Angel")
ActiveSheet.Range("A1:B" & CStr(RecordingLength)) = Recording
End Sub


Private Sub RecordingTimer()
Application.OnTime Now + TimeValue("00:" & CStr(RecordingTime) & ":00"), "StopLogging"
End Sub

Public Sub StopLogging()
If Running Then
Running = False
SaveRecording
End If
End Sub

Public Sub StartLogging()
Recording(1, 1) = "Changed Address"
Recording(1, 2) = "Time of Change"
Recording(1, 3) = "Betangel update time" ' put in the header
i = 2

RecordingTimer
Running = True
End Sub

[SamT]

Attached is an example of a custom Settings Worksheet.

CPerry, you need to start work on this ASAP. It's holding me up.

Afterwards, you can make the sheet pretty and pleasing an easy for the User to understand and use. (You might sell this, who knows. Plan ahead and be prepared.)



You will need all three Hats to work on this
The developer, (you in your Developers Hat,) in consultation with the User, (you in your User's Hat,) will decide what goes in the User Columns on the sheet

The Developer, checking with Bet Angel's Documentation, decides what BA addresses go in the Input Addresses Columns.

For testing, The Developer or Coder decides what goes in the Helper Output Addresses. The User, consulting with the Developer, makes those decisions after the APP is in production

The Developer, in consultation with the Coder(s,) (that is you in your Coder's Hat and me,) decide what Name to put in the Developer's Column.

For now, don't worry about how the code will interact with this sheet, I got that ATT.

Well thought out Variable Names are a very important part of good Coding, one that is ignored by all amateur and many professional coders. Please. before you start, review all the code samples I have provided and get a good feel for how I name variables.

Q&A: No. I don't think Hungarian Notation is applicable to VBA. Hungarian, AKA, Reverse Polish Notation or RPN, was created for code that might contain one or more "Modules" or Procedures many thousands of lines long written in C or ADA. VBA Coders rarely see even 100 line long Procedures, and Modules are usually broken down into manageable chunks.

Compare:
RPN

sEmpNm As String
iEmpAg As Integer
dtEmpBrth As Date

VBa

EmployeeName As String
EmplyeeAge As Integer
EmployeeBirthDate As Date

[CPerry]

20739
20740

It is past 00:30 here in England, I have been working all day non-stop and am incredibly tired. I have looked at this on and off where I could today and have just managed to get some work some now. Will I continue to moan? Most likely. I am English after all ;)

Please find attached two files:
1) A test I did for offthelip using the code he provided in his most recent post. I changed the first two variables to have a Const RecordingLength of 10,000 and a Const RecordingTime of 10.

2) A mostly completed Custom Settings Worksheet with only a couple of cells missing. I have used a combination of what you originally sent, a previous model you have constructed and the BA template on three separate tabs. The flow between them should be self-explanatory. Would you take a look at this and just let me know if this is completely wrong as I am very tired and have most likely made a rateeeeee hash of it or if there's anything else I can do to improve it please.

Still no reply from the BA forum about shaving anything off the spreadsheet but still looking into it. In the meantime, once you've had chance to look at my attached documents and interpret them, would you kindly let me know what you still require on my end or supply me with the next task and I will look at it with fresh eyes when I can tomorrow morning :) I think I have a free period just after lunch.

Thank you kindly and goodnight gentlemen.
CPerry.

[SamT]

Still no reply from the BA forum about shaving anything off the spreadsheet
No need to Shave anything off. Just group the data you want to the left so that the used Data Table is compact. That link I posted tells what and how. do the best you can, but we don't need a miracle.

[offthelip]

Cperry,
Thanks for the data, unfortunately it only has two columns in it, I don't know what has happened to the modification I suggested to try and recorded the BEtangel Update time from D39, have I got the cell wrong. Is it possible for you to investigatge as to why it is not recording it?

The good news from the data that you have sent me, is that the 3 seconds gap in previous data as the minute ticked over appears to be a one off. This data doesn't have that.
What is quite interested is that the update rate gets slower as th e minutes tick by:
this is the average number of updates per second for each minute of the data recorded:
20.02
18.75
17.78
16.63
16.64
17.17
16.73
14.89
15.24

[offthelip]

I have spotted the error in my modification I didn't changethe number of columns written out

can you change this one letter:

Private Sub SaveRecording()
Worksheets.Add Before:=Sheets("Bet Angel")
ActiveSheet.Range("A1:C" & CStr(RecordingLength)) = Recording
End Sub

[SamT]

is that the 3 seconds gap in previous data as the minute ticked over appears to be a one off.
I ignored it since I considered that some other program was running on that computer at the same time. Loading a Program is a CPU and RAM intensive time


@ CPerry

otl said:
can you change this one letter:

Private Sub SaveRecording()
Worksheets.Add Before:=Sheets("Bet Angel")
ActiveSheet.Range("A1:C" & CStr(RecordingLength)) = Recording
End Sub

If you would modify that code even farther, you can create a set of Real World Data that we will need for testing the entire Helper App.

It does not need, (Or want,) any time stamp. It does need all Data columns, two rows, for one Horse/Brand. This snippet should help

Static DataTable As Range
Set DataTable = Range("C45:L46")

Dim Recording(1 To RecordingLength) As Variant

If Target.Cells(1).Address <> "$B$45" Then Exit Sub 'B45 from the BA timing logs you made

Recording(i) = DataTable.Value


Post the code when it works. I think we will use a version of it in the Helper App. What Changes do you think need to be made to this code to make it output all the info you want the Helper App to have?

[offthelip]

When developing trading or betting strategies one of the objectives is to come up with a defined set of rules that give consistent results.
I have put together this spreadsheet to demonstrate what the effect of have variable update times on the calculations for a MACD.
I have made an assumption that the input price goes up in a steady ramp from 1 to 5 and then back down to 1. I know this is unrealistic but it is very good for this demonstration
In the first set of columns the assumption is that there is a regular update every 200ms
The first graph shows results of this with a cross over of the difference between the two emas at 2.8 seconds after the peak in the price, ( Rows 28 to 410
In the second set of columns I have generated random update intervals , and then calculated the EMA using the same function. This means that the EMA calculation is NOT done at equal time intervals but done when the updates come in. This causes an error in the EMA calculation because the “smoothing factor” does not take account of the timing difference.
The effect of this is that the crossover point of the difference between the two EMA (Column K) varies with the errors in the two emas. In column O I have calculated the time after the Peak in the input where the cross over occurs. If you keep clicking the update button you can see that this varies by over 0.5 seconds.
I think this is going to make it difficult to generate consistent results.

[SamT]

What was your assumption of the Rate of Change at 200ms timing? At 50ms? (Rhetorical question)

What is the maximum Helper Data droppage percentage, at what Rate of Change in BA Data, that will still allow a reasonably usable output curve at 20ms - 50ms BA update timing?

[offthelip]

My assumed rate of chage was 1 unit per second in both cases. Just because it is easy.
The drop percentage doesn't really make much difference in the calculation, the point I have been making is it is the variability in the update rate that causes the problem

[SamT]

IMO, it is the Rate of Change of the data captured by the Helper that will make or break the Project.

To pull some numbers out of the air...
the RoC of BA data updates is .01%
The BA Helper successfully captures 1:10 BA Updates. (1:10 Sample Rate is a variable set by the User.)
The RoC of Helper input Data is .1% per unit "time" where "time" is actually Update capture tiks or about 200-500ms

Even changing RoC by x10 still means that the Helper data is only changing 1% per tik.

[offthelip]

I modifed my demo to to pick an input value to make the rate of change 0.02% and it makes absolutely no difference at all, I as Expected.
What really does make a difference is changing the limits on the RANDBetween() function, by making the limit slightly wider it really increases the error
Limit the variation in updates between 10 and 30 ms the error is usually less than .5 sec
changing this to between 10 and 40 ms it is usually less than 1 sec
changing this to betwee 10 and 50 ms is is less than 2 sec but averages 1.2 seconds.

So the error increases non linearly with the variation in update rate.

[offthelip]

A bit more experimenting and the solution is as I had anticipated,.

I changed the EMA calculation so that the "smoothing factor" is dependent on the time since the last update.
Once I had done this the error is greatly reduced At the 10 to 50 ms variation the average error is 0.25 secs
For the 10 40ms it is 0.2 sec
for the 10 3ms it is .135 sec

So hopefully BEtangel do update the "update time" everytime there is an update. If they do then this can be recorded with price data and used to greatly reduce the error induced by the variable update rate.
I have attached the two latest versions,
V2 has the fixed smoothing calculation and easily allows the variation of hte random limits (G5 and G6)
V3 has the smoothing calcualtion which takes account of the update time.

@cperry, can you run the test I suggested to check whether they update the "update time" in a suitable manner. I believe the accuracy of your system depends on this.

[SamT]

:banghead:
What I keep seeing is that IYO, this can't be done with Excel and VBA.

[offthelip]

No I think it can be done but we must take account of the variation in update time. If Betangel give an accurate update time with an accuracy of at least 10 ms then all we need to do is record this with updates and use it in the EMA calculations. If Betangel don't provide this then we will need to build our own algorhithm to allow us to take account of it. My first suggestion was to count the number of updates in the last minute, the trouble with this sort of solution is we will never know how well it works.
Note: I have succeeded in building a very similar system on Financial markets. That system did provide update times.

[SamT]

If Betangel give an accurate update time with an accuracy of at least 10 msWhile Excel can display time in ms, ("[h]:mm:ss.000",) Neither the Bet Angel Sheet, last update time cell nor the Update Times log we got from CPerry recorded Time in less than whole seconds.

Developer Note: http://betangel.kayako.com/Knowledgebase/Article/View/140/0/pl-reports-in-practice-mode
http://betangel.kayako.com/Knowledgebase/Article/View/31/0/recommended-communications-settings
"The expected response times inside the UK can be anywhere between 50ms - 200ms."
End Note

Maybe something like:
BA Cell C3 = LastUpdateTime


Sub Worksheet_Change
If Target = C3 then
UpdatePerSecond.Tik = C3
'
'
'
End Sub

clsUpdatesPerSecond

Dim LastSecond As Date
Dim CurrentSecond As Date

Dim TikCount As Long
Dim Tik As Date

Dim PreviousValue As Double
Private pValue As Double

Property Let Tik(UpdateTime As Date)
CurrentSecond = UpdateTime

If CurrentSecond = LastSecond Then
TikCount = TikCount + 1
Else
LastSecond = UpdateTime
End If

CalculateValue
End Property

Private Sub CalculateValue
If CurrentSecond = LastSecond Then
PreviousValue = 1000 /TikCount 'ms per update in this second
Else
pValue = PreviousValue 'ms per update in last whole second

'Reset times and counts
TikCount = 0
LastSecond = CurrentSecond
End Sub

Property Get Value()
'Used as Modifier for Certain Formulas per otl
Value = FunctionBytOffTheLip(pValue)
End Property

[SamT]

The Latest, for your comments and suggestions.

20787


As you can tell, I'm still learning this Drawing thing. I thought I put in lines and renamed Module1 a couple of times.

[CPerry]

207892079020791

Hi gents,

Please find attached:
1) An updated version of the custom settings sheet (I have turned bold the new information that I have added since my last reply, I think this is the final version of this sheet with everything you requested SamT).
2) The spreadsheet with the altered code as requested by you Offthelip, I just tried to extend it with that bit you also typed up SamT but I’m afraid I just missed the last race of the evening! I tried just pasting it underneath the first part like so:


Private Sub SaveRecording()
Worksheets.Add Before:=Sheets("Bet Angel")
ActiveSheet.Range("A1:C" & CStr(RecordingLength)) = Recording

Static DataTable As Range
Set DataTable = Range("C45:L46")
Dim Recording(1 To RecordingLength) As Variant
If Target.Cells(1).Address <> "$B$45" Then Exit Sub
Recording(i) = DataTable.Value

End Sub

But it resulted in the following:
Compile error:
Duplicate declaration in current scope

I didn’t have time debug it and work out the, I’m assuming, painfully obvious error I have made constructing this little add-on so before I reattempt it tomorrow, any idea what needs to be altered? I am travelling to London first thing in the morning so may struggle to capture any markets tomorrow but I shall try my best if either/both of you instruct me to collate any more information. Again, I shall try and reply in the late evening (work has been a pain this week) but I will be in London until Sunday. As of Monday I will continue to give this my absolute, undivided attention once more.

Furthermore, I have been thinking. I assume the system works automatically as soon as it detects movement on the main sheet which is fantastic as this can easily be controlled by the restrict refresh function the BA app supplies; it also means it can start at any time not just 10 minutes before market start but when I choose. To hedge all my profits, I shall also use the GREEN_ALL function BA supplies so can the system stop placing orders once the market countdown has reached 15 seconds or less, that is to say when BACountdownToStart (F4) is <= 00:00:15, Then (No new orders), If (0rders are still open) then Close_Trade within 5 seconds. Also, if the market suddenly creates a bounce I may miss an opportunity and in a fully automated system consequently some money. As previously mentioned you join a queue of money so sometimes the market can move faster than you can get an order made...

Now this is something I need to look into further but a simplistic quick fix I’d like to include now please would be as follows:
IF (BAFirstBack1) is (two ticks – need to work out a formula for this to obviously this won't be included immediately) away from (trigger price) AND (Order) has not been filled, THEN fill (What’s left of order) at (New price). I will look into this next. Also, I’d just like to clarify a couple of other things at this stage please. The system won’t get confused if there are blank columns or ‘0’s’ (Less than three brands) will it? Also, it shouldn’t get confused if I have multiple markets running simultaneously or at slower update speeds i.e. pre match football markets (5 at a time equals 1 second updates instead of 200m/s updates), I’m just trying to get to grips with the strengths and limitations of this system and where I can experiment with it once it’s up and running.

And finally, one last final tangent if either of you are interested. I had a really intense dream the other night that I wrote down because it fascinated me. I have copy and pasted it into a word document which is also attached to this comment if either of you are fascinated in the psychology of dreams or simply what is going on in my strange little head.

Cheers lads,
Goodnight.

CPerry.

[SamT]

I modified the Custom Sheet a bit. I think this makes more sense to the User, you will only have to deal with the "Sheet Descriptors" when you rearrange the Bet Angel Sheet or the "Control Panel" Sheet

You, as User, will mostly be editing the "Calculation Settings" sheet. In Fact, I would hide the Sheet Descriptors sheet once you put this in production. The Sheet Descriptors sheet is edited by you as Developer or Coder

[CPerry]

Brilliant!

This looks a lot simpler now and relatively straight forward to amend once the first version is operational. Are there any other tests either or you require (any idea why the other code resulted in "Duplicate declaration in current scope"?) before we move on. What is next?

Cheers,
CPerry.

[SamT]

"Duplicate declaration in current scope" means that the same variable was declared twice in the same sub.
It could mean that the same variable was declared in the Module level declarations in two modules.

Very Rarely someone will declare the same variable twice in the Module level declarations in the same Module or the same Property in a Class Module.

Scope means the applicability of a variable or constant to any particular code

Private or non-Public Variables, Constants, and Procedures in a Standard Module are only available to the Procedures in that module
Public Variables, Constants, and Procedures in a Standard Module are available to all procedures in the Project. All Public Names must be unique in the Project, except in re Class Modules.

Class Modules are a different Story. They should never have Public Variables or Constants. Public Procedures are actually Object Methods. All Names in a Class Module must be unique in the Class. Class Modules and associated Instantiated Objects are, or should always be, self contained, and refer to nothing outside themselves. That's what the Properties are for; An Interface with the rest of the Project.

[offthelip]

Hi Sam,
I have just got back from a weekend away, from your post 102:


Neither the Bet Angel Sheet, last update time cell nor the Update Times log we got from CPerry recorded Time in less than whole seconds.

Since this is the case the code you are proposing in post 102 is exactly what I was thinking of and should hopefully do the trick.
Good to see it all progressing.

[SamT]

Now I need the Value Function for that Class Module







:devil2:

[offthelip]

The functions should be:

Property Get Value() 'Used as Modifier for Certain Formulas per otl
' Assuming Ema Constants set for 200ms Update
Value = 200/pvalue

End Property

[SamT]

@ otl,

I incorporated your function, then refactored the Names for better self-documentation.

I think that the output will be unusable as designed. We have two Tiks to consider, the TimeTik of once per second and the UpdateTik at around 20-50 updates per second.

The issue I see ATT is that at the start of the Second, the UpdateTic count is 1 resulting in an output of 0.2
At the end of a Second, the Update Tik count may be as high as 50, resulting in an output of 4.0.


Option Explicit

'Sub Worksheet_Change()
'If Target = C3 Then
'MilliSecondsPerUpdate.Tik = C3 'C3.Value in whole Seconds
'End If
'etc..
'End Sub.



' clsMilliSecondsPerUpdate Code


Dim LastSecond As Date
Dim CurrentSecond As Date

Dim TikCount As Long
Dim Tik As Date

Dim PreviousMilliSecondsPerUpdate As Double

Private pMilliSecondsPerUpdate As Double

Public Property Let Tik(UpdateTime As Date)
CurrentSecond = UpdateTime

If CurrentSecond = LastSecond Then
TikCount = TikCount + 1
Else
LastSecond = UpdateTime
End If

CalculateMilliSecondsPerUpdate
End Property

Private Sub CalculateMilliSecondsPerUpdate()
If CurrentSecond = LastSecond Then
'ms per update in this second
PreviousMilliSecondsPerUpdate = 1000 / TikCount
Else
'ms per update in last whole second
pMilliSecondsPerUpdate = PreviousMilliSecondsPerUpdate

'Reset times and counts
TikCount = 0
LastSecond = CurrentSecond
End If
End Sub

Public Property Get EMAFormulaModifier()
'Used as Modifier for Certain Formulas per otl
' Assuming Ema Constants set for 200ms Update
EMAFormulaModifier = 200 / pMilliSecondsPerUpdate

'Ms/Update is a ramping Curve... /|/|/|
'This means that the modifier is the inverse... \|\|\
' The vertical bars represent TimeTiks or seconds
End Property

Option 1: Run the Helper App once per TimeTik
Option 2: :dunno:
I'm going to look at some micro-timers to see what I can do.
:reading:

[SamT]

Reading in re Speed and versions: http://www.decisionmodels.com/VersionCompare.htm

The VBA Micro Timer in the KB.Source: http://www.decisionmodels.com/downloads.htm & Timers.ZIP (http://www.decisionmodels.com/Downloads/Timers.zip)

[offthelip]

I think it will work with the provisions stated below;
the way I anticipate using it is as follows:

Newperiod=emaformulaModifier * Period
TC = 2 / (NewPeriod + 1)
EMA = Lasttime + TC * (Drive - Lasttime)
So when we get just one tik per second, the emaformulamodifer will be 200/1000 = 0.2
Assume the original period was 26 ( as per Cperry requirements)
The newperiod= 0.2* 26= 5.2
So the TC = 2/(6.2) = 0.322
When we get 50 tiks persec the modifier would be: 200/20=10
The newperiod= 10* 26= 260
equation would give a TC= 0.0076

The original equation would have given us a TC =0.074
Looking at this in a simplistic way, when we get one update per second the next update will take one third of the change and add it in the next time. This will allow the EMA to “catchup”
When we get lots of updates, each update provides a less than 1% change to the EMA.
What could be a problem using the count the tiks method is if we get very rapidly change rates of update, i.e 1 tik one second followed by 50 tiks the next second then back to 1 tik, per second. This is not going to work.
Thus if you can get a microtimer to work that would be superb. It will radically improve the accuracy.

I forgot to reply to yourquery:

The issue I see ATT is that at the start of the Second, the UpdateTic count is 1 resulting in an output of 0.2At the end of a Second, the Update Tik count may be as high as 50, resulting in an output of 4.0.



It is because of this that we can only update the emaformulamodifer when the second tick over from one second to the next, so we only need to recalculate ema

thus I think the code can be simplified as below:

' clsMilliSecondsPerUpdate Code

Dim LastSecond As Date
Dim CurrentSecond As Date

Dim TikCount As Long
Dim Tik As Date

Dim PreviousMilliSecondsPerUpdate As Double

Private pMilliSecondsPerUpdate As Double

Public Property Let Tik(UpdateTime As Date)
CurrentSecond = UpdateTime

If CurrentSecond = LastSecond Then
TikCount = TikCount + 1
Else
LastSecond = UpdateTime
CalculateMilliSecondsPerUpdate
End If


End Property

Private Sub CalculateMilliSecondsPerUpdate()
'ms per update in last whole second
pMilliSecondsPerUpdate = 1000 / TikCount
'Reset times and counts
TikCount = 0
LastSecond = CurrentSecond
End Sub

Public Property Get EMAFormulaModifier()
'Used as Modifier for Certain Formulas per otl
' Assuming Ema Constants set for 200ms Update
EMAFormulaModifier = 200 / pMilliSecondsPerUpdate

'Ms/Update is a ramping Curve... /|/|/|
'This means that the modifier is the inverse... \|\|\
' The vertical bars represent TimeTiks or seconds
End Property

[SamT]

Newperiod=emaformulaModifier * Period
TC = 2 / (NewPeriod + 1)
EMA = Lasttime + TC * (Drive - Lasttime)

Uhh...
Period / NewPeriod = ?
Drive = ?
LastTime = ?
TC = ?

Ok. I get that Period is a duration of time between two marks. But that looks like a count of Times or durations.

I'm not real smart, I need universally generic words.

Maybe then, I can understand this:
Assume the original period was 26 ( as per Cperry requirements)
The newperiod= 0.2* 26= 5.2
So the TC = 2/(6.2) = 0.322
When we get 50 tiks persec the modifier would be: 200/20=10
The newperiod= 10* 26= 260
equation would give a TC= 0.0076

[offthelip]

I suggest you have a look at the following link which explains the EMA calculation in relation to stock market charts where the interval between samples is one day.
In our application the interval is variable in length but the initial assumption is that it is 200 ms
My terminology is very similar to stocksharts terminology.
http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:moving_averages

TC=multiplier
Period = Time Period
Drive=Close
Lasttime=Ema(previous day)

"Period" is really just a count of how many values to use for the calculation. When the interval between samples is fixed it ends up being a fixed amount of time as well, and so is a "Time period".

[SamT]

Here is the Microtimer I am going to try to implement, The worksheets have some timing data and some analysis
Raw Data is with SamtTest V1 and Sheet 3 is with SamtTest V2. I did this in my Scratch Workbook, so there are unrelated ideas in the code.
The xls file was > 10 MB, so I saved it as an xlsb.

[offthelip]

Hi Sam,
The microtimer looks great, I think it will suit our purposes very well. I looked at the anomaly at line 960, it would appear that updates suddenly doubled in speed.
I don't think this will cause us a problem because we can use the actual time since the last update in the EMAformulamodifer which will be fine. With the previous method of counting tiks per second it would be a problem.

[SamT]

The Columns I called Differentials is based on the Average Delta. they are the difference between the Delta instance and the average Delta. The fact that the average Differential was 0 in both cases I calculated makes me think there is a correction mechanism somewhere.

In any case, during the short run, the Max Delta was l0.67ms, averaging 0.30ms, and during the long run was 6.8ms, averaging 5.0ms

Isn't Statistical Variance the same as what I call Differential? :dunno:

@ CPerry,

Run the SamT Test V1 in modMicroTime on your machine, just change the LoopFactor to 10000. Place the Mod in a new workbook. Keep that code around, since you will want to run it on all your bet angel computers, just to know how they act.

Can Bet Angel use a Sheet like the attached? I rearranged the InfoFields for Speed and ease of Coding.

My next step is to capture the micro times and addresses of all the BA updates.

[CPerry]

@ Offthelip
The sheet you attached in your previous message works fine when you connect from cell A1 but the cells are set to the original arrangement of the InfoFields so instead of occupying cells B3:K3 (C2:C6 and F2:F4), everything else links and looks clear.

[offthelip]

Hi Sam,


The Columns I called Differentials is based on the Average Delta. they are the difference between the Delta instance and the average Delta. The fact that the average Differential was 0 in both cases I calculated makes me think there is a correction mechanism somewhere.

In any case, during the short run, the Max Delta was l0.67ms, averaging 0.30ms, and during the long run was 6.8ms, averaging 5.0ms

Isn't Statistical Variance the same as what I call Differential?

Your calculation of average differential will always give zero as the outcome, whatever set of sample numbers you use. This is because in F11 you have got the average of column E, in column G you have subtracted each value in column E from the average in F11. Then in F14 you have averaged column G. By definition of "average" this will always be zero.
This is why statisticians use "standard Deviation" as the measure of Statistical variance, because it measures the deviation from the average regardless of the sign of the deviation. There is a EXCEL function for this ( 4 of them actually) change the equation in F14 to:
=STDEV(G11,G65526)

[offthelip]

Hi Cperry

@ Offthelip
The sheet you attached in your previous message works fine when you connect from cell A1 but the cells are set to the original arrangement of the InfoFields so instead of occupying cells B3:K3 (C2:C6 and F2:F4), everything else links and looks clear.
I don't understand what you are referring to here, I haven't attached any sheet for ages. Which workbook are you talking about?

[CPerry]

Apologies, the last message I was referring to was from SamT. That's what I get for typing up a quick reply.

[SamT]

By definition of "average" this will always be zero. Makes sense,


here is a EXCEL function for this ( 4 of them actually) change the equation in F14 to:
What did you get for SD?
I got 4.5us - 0.045ms
I bet I did it wrong :D

[offthelip]

I get:
0.002457

equation in F14 is:
=STDEV(G11,G65526)

[SamT]

Attached is The Bet Angel Sheet Code and the Micro-timer UDO, (Class Module,) which will be used all over this project

I tested it pretty good, but you will put it to the acid test. It will record every change the Bet Angel Program makes to Excel's Bet Angel Sheet. This will tell us the order BA updates the cllse and give us accurate enough timing data.

Manualy copy the Worksheet Code to the Bet Angel Worksheet, then Drag the MicroTimer Class Module to the Bet Angel Project's ThisWorkbook Label. Run it just like the previous timing tests.

I did add NOW() and MicroTimer Start times to Row two of the Log sheet.

[SamT]

The sheet you attached in your previous message works fine when you connect from cell A1 but the cells are set to the original arrangement of the InfoFields so instead of occupying cells B3:K3 (C2:C6 and F2:F4), everything else links and looks clear.
Does that mean that BA won't let you put the Info Cells like I want? (All in a Row.)

[CPerry]

Awesome! The British races start at 12:15 tomorrow so I will run it then and report back a saved copy at the end of a few races. I'll do a couple this time now we're getting a little closer just to be sure!

Yes, BA always puts the InfoFields in that specific order (probably getting directions from BA directly in some capacity but that information is no where to be seen in their standard documentation)

[SamT]

Ah, well. Just some more collating to do.

[CPerry]

2084120842208432084420845

Good afternoon gents,

Please find attached 5 different tests using that code. I had my second laptop playing a YouTube video whilst the trading laptop recorded: 'Race 1 10-7 Mins' and 'Race 2 20-17 Mins' just to see whether systems fighting over internet connection affected the results massively. Each test seemed to consistently take around 3.5 minutes or so.

I hope this is correct and what we are hoping for.

Cheers,
CPerry.:thumb

[SamT]

Excellent:
Here is one of those, truncated to 2 seconds of play, with some interesting analysis.

[offthelip]

Hi All,
Excellent recordings and analysis.
There was one extra item I put in which is the minimum time between batches ( Min(f10:f101) this was 0.066. I assume the units are seconds.
The way I interpret this is that we have 66 ms to do all the EMAs, RSI and trigger calculations and write the results back before we get into the "overrun" situation. That should be enough by my estimation.
All systems go!!

By the way I have just noticed that we have now become the longest thread on this forum and the EXCEL forum, do we get a prize for that??:hi:

[SamT]

Here ya go... :bggift:

:bigdance2 Till you :drunkard:



:haha:




the minimum time between batches (Min(f10:f101) this was 0.066. I assume the units are seconds.
The way I interpret this is that we have 66 ms to do all the EMAs, RSI and trigger calculations and write the results back before we get into the "overrun" situation.

I got the same 66ms. All the BA Excel is going to do is place one array (A1:AF & LastRow) into the Output Object to be read and parsed by the Helper Excel DataParser Object.

Maybe Excel is fast enough to do it all in one Workbook? What do you think? Is it worthwhile writing it that way first? It would be a bit simpler.

BTW, the original Log had 10000 records. Did you check the minimum of that log? Or do you think ~100 samples is enough? I didn't 'cuz I'm lazy and didn't feel like writing a sub to Paste an Array of Cell Formulas each 7? Rows. I made that sheet by hand.

[offthelip]

Each EMA takes about 5 arithmetic/ logic calculations
MACD need 3 EMAs plus a few more calculations , RSI need 2 EMA plus a bit of calculation
So about 25 calculations for the emas, double it to take account of the MACD and RSI calculations
So about 50 calculations per brand per update. With 4 horses that only makes about 200 calculations all done directly in memory.
I have just run a test looping round the EMA calculation 10000 times using the microtimer, my computer did it in 1.7ms
So I think it should easily fit in one workbook, just using whatever multithreading EXCEL can manage.
However I think writing out the array will take the time:
I wrote out an array 100 by 5000 rows and it took 0.3 seconds:
Writing out a smaller array one row by 100 columns only took 1ms.

So I would arrange the layout of the worksheet to allow just writing a single row or single column of data each iteration.
I haven’t looked carefully at the layout of Custom settings workbook, but with a quick look at layout on the Control panel, I am not sure what this is supposed to be doing, is this where the output is going to be written? because if it is it will need changing

[SamT]

How did you write the array? With a loop,or by an equivalency?
There are only three Ranges that need to be Read, if I understand the OP. The Row count is limited to 30 by 5, 11, and 12 columns. The entire possible used Range of the Bet Angel sheet is only 71x36

The information, or Financial. section of the Control Panel is a direct copy, maybe rearranged. I think that a simple equivalency with the right cell on the Bet Angel sheet should work. That is an Excel Calculation Event.

A single Brand's data is two Rows by 71 Columns, or 2Rx11C and 2R12C. There can be 30 Brands

As currently imagined, each Brand UDO can write it's own results to the Control Panel, or they can all send their results to a Data Accumulator/Sheet Writer. At one or two Brands, it might be faster to have them write to the sheet. At n Brands, it might be faster to use the Accumulator UDO.

[offthelip]

I am writing the array by assigning a previously defined and randomly populated variant array to it::


Range(Cells(1, 1), Cells(lastrow2, lastcol2)) = var2

Also I have modified the WIP custom settings/ control panel sheet to the layout which I think will work best.
Two things firstly setting it out so that outputs can be written out on a single line.
Secondly getting the correct columns laid out for what Cperry asked for which was MACD and RSI.
I also thought that record the time and price with each update line is going to make debugging much easier, and is very useful in the live situation when trying to develop the strategy.

[SamT]

Since we now have a decent MicroTimer, I was going to make the next to last version log times and activities for the Entire project. Might use Compiler Constants so the code itself would not have to be rewritten just to re-log that after it's in production and we need to find a bottleneck.

The Control Panel Sheet is still in a state of flux awaiting CPerry's final input. I think of it more as a Dashboard/Display Panel with a few Command buttons so the User can manually control each Brands BA Status.

@ CPerry, We still must needs to Name every possible Bet Angel Data Point, (or Input Field,) for every possible Market type that BA supports. Set up A Bet Angel sheet with all possible data inputs for each type of Market/Betting Event. We only need 1 or 2 Brands in each book. Then Run the MicroTime logger for 3 cycles. Save that Workbook named by Market Type,(ex: New Jersey Cattle Exchange.xls, BaseBall Games.xls.)

Go thru the Bet Angel sheets in the workbooks and name every data field in a style like the BA Sheet in the attached.

[CPerry]

208652086620867

Firstly, I have updated what I would like to be the main spreadsheet with a few minor tweaks (Offthellip has kindly added in the additional variables for the indicators that I will be running with Version 1 of the finished project). At this point I would just like to ask, do the trigger points i.e. MACD 30/70 levels need to be included in the control panel or will this go directly into the coding? I ask because these will be variables I play around with when testing, I would simply like them to be in the most efficient place possible and to know where they are to experiment with.

Next, I have been reading into market behaviour and have come up with some additional safety measures I think you will both agree are important to include.

Self-note: I feel this is slightly jumping the gun and wont go into the project just yet but I thought I’d type it up now to reference later with proper coding terminology:
1) When markets go berserk and fluctuate irrationally (only in the most extreme of instances does this concern). Do not close from a back order if price is now greater than 5 from back order, do not close from a lay order if price is now less than 5 from lay order.
2) When only partial orders get matched or when the price moves and my order doesn’t get matched, see and elaborate on small notepad sketch.

Now in response to your previous message. I now believe the control panel includes all of the info fields that need to be accounted for. I may be being really stupid here but what do you mean name every possible BA data point for every possible race market? The titles previously typed up include everything, don't they? Brand 1 accounts for whatever may be in that particular cell, whether it be a favourite horse, the 0-0 scoreline or a name of a country in an international competition. I have used today’s premier league football matches as an example, please see attached files. I have included the 2:15 and 4:30 matched, Tottenham was the correct score market so there were loads of brands and the Man City match was the Match Odds market so there was only 3; Man City, The Draw or Arsenal (cycled 3 times at different intervals - identified via the sheet tab names. In any case, brand 1 would be whatever is in that first row so what do you mean by every single data point? Again, probably just me being really thick...:doh:

Hope you've both had a good weekend, as ever looking forward to your next message(s).

Cheers,
CPerry.

[SamT]

When you use Bet Angel in the Stock market, do you still use Back and Lay?
I guess the real question is... Does the layout of the BA sheet change with any type of Market? Stocks, base ball, races etc, etc?


would just like to ask, do the trigger points i.e. MACD 30/70 levels need to be included in the control panel or will this go directly into the coding? That's your choice. How do you plan to calculate the BA commands?


these will be variables I play around with when testing, I would simply like them to be in the most efficient place possible and to know where they are to experiment with.
All the variables you plan to play with should be available to the User. Than means they must be inputted into a sheet.

I have changed the WIP Workbook to be more User Friendly. However I think this workbook will wind up being a Developers reference and the actual Helper App Sheets will be copied from this one. I had to put a lot in it that the User won't need, but the developer does need to know. It is also more code friendly this way.

I have to sleep now. CU L8R.

[offthelip]

Cperry: MACD does not have significant levels at 30 and 70, it is RSI that normally has trigger levels at 30 and 70. MACD is an indicator derived from price which retains a value proportional to price. This means that MACD values can be anything, they are not bounded between 0 and 100 which RSI is. Thus the only realistic trigger for MACD are the crossover at zero. I agree that the RSI trigger levels of 30 and 70 should be tunable from the custom settings sheet

[SamT]

If RSI 30, 50, and 70 are tunable, then rename them to their mathematical formula nomens.
RSI_30 = <> 30 is illogical

RSI_Bottom_Trigger_Level, makes sense to a code. Don't Know if it makes sense to the User and the Developer, but some Name will make sense to everybody. That is the Name to use.

You should be able to write out all formulas in VBA-English patois using only words and Variable names. No numbers at all.

Example ( an imaginary formula for this example)

'Inputs: RSI_series; RSI_Bottom_Trigger_Level; OTL_Time_Correction_Factor'
Output: RSI_Bottom_Trigger
'Note: May use last value of RSI_Series. TBD

Dim RSI_Above_Bottom_Trigger as Boolean

RSI_Value = Average(RSI_series * OTL_Time_Correction_Factor; RSI_Bottom_Trigger_Level

RSI_Bottom_Trigger = RSI_Value < RSI_Bottom_Trigger_Level And RSI_Bottom_Trigger_Level = True
RSI_Above_Bottom_Trigger_Level = False

[CPerry]

Apologies, my WiFi has been down for over 36 hours now and I've run out of 4G on my phone until Friday so just had to drive to my family's house to get online and read your comments, I will reply to the above messages shortly now I'm at a working desk. (This reoccurring problem over the past 2 months or so is edging me even closer to purchasing a VPN soon to ensure it doesn't affect my trading).

Right, I'm going to shut up, read, think and compose my replies...

[CPerry]

I have changed the WIP Workbook to be more User Friendly.
Did you mean to attach this or is it just a minor tweak for yourself? (Also, I'm using this as an excuse to try out the quote function as I'm aware I haven't done so yet :P).

Yes, at all times BA will use ‘Back and Lay’ instead of ‘Buy and Sell’ just because that’s the terminology of betting which this form of trading derived from. The layout will always be the same on the BA sheet which is perfect for us because the code can always follow the same template, all I will be doing is trading the difference in opinion of price of… whatever. Like I said previously the first line could be the score line 0-0, a horse name or anything really (as shown in my previous attachments).

I’d like the trigger points to go in the coding directly please as I imagine this will be more efficient. I imagine the BA commands to be triggered like so…. (Keeping it in the form as it falls out of my head before making a hash of communicating this idea in 'developer speak' just so you two can make sure my logic is...logical)...

If MACD, RSI & WOM have all triggered ‘1’ at least once within the previous (look back) period, then “BACK”, if not ‘0’ then do nothing. If all have triggered ‘2’ then "LAY”.

MACD –
IF Signal line (9EMA) was less than MACD line (12EMA-26EMA) but is now greater then “BACK”.
IF MACD line (12EMA-26EMA) was greater than Signal line (9EMA) but is now less then “LAY”.

RSI –
IF RSI was less than 30 and now greater then “1”.
IF RSI was greater than 70 and now less then “2”.

(We don’t need an upper and lower trigger limit. RSI will return a number 0-100. The trigger will be if the RSI reading is greater than 70 or lower than 30. If this can’t be detected in a function kind of thing then we could just have a constant i.e. RSI_Bottom_Trigger and in the control panel just type in the number ‘30’, keeps it nice at simple).

WOM – (Anything less than 0 then “1”. Anything equal to or greater than 0 then “2”)

So if we have the MACD line, smoothing factor, RSI etc on the sheet (as we already have) to be customisable and then within the code it is constantly checking these figures (in the given look back period) for these changes and when it notices all 3 changes within this period then BACK/LAY in cell L10 (BAReport). Intertwined with this, is the staking formula that will be incorporated later.

Cells B2:H33 on the control panel sheet? I assume there will not be need for 3x all of these settings as all brands will read from this same part of the spreadsheet i.e. all brands will have a smoothing factor of 0.9. If I were to change this single cell to 0.8 it would affect all the brands not just horse 1, am I correct?

Ok, I'm going to head back home now. Will commute to my family's place again tomorrow if the WiFi is still down so I don't miss out on, what has become, this big fun project! Feel free to leave me tons of messages and/or jobs in the meantime...

Cheers lads,
CPerry.

[SamT]

CPerry, I need 4 class modules. All they will be are repositories. They will only contain a list of Private Property Variables, and, the Public Get and Let Property of each variable.

The Variables will be named after the Developer Nomen in the tables on sheets "BA Descriptor Tables" and "Formula Parameters," in the attached. Be sure to prefix the Var names with a "p".

Add the Private Property Variable, "pName," and it's Properties to each Class.

Name the Class Modules, "clsInfoFields," "clsDataFields, "clsInstructionFields," and "clsFormulaParameters."

[CPerry]

I have created 4 class modules and named them as you requested and simply copied the variables as they are in the tables on the sheet. I’ve made a couple of comments on the ‘Control Panel’ tab on the document that I am attaching to this message regarding the information that should be shown in this section. A lot of the addresses on the Descriptor Tables tab were wrong (unless I am mistaken on how you had labelled them in which case just use the cells you typed in version 1 – the cells in red text though were completely missing either way so I have added these in too. I have made several tweaks around the entire document, nothing drastic.

Now am I correct in assuming the following cells on the ‘Control Panel’ tab will display the following?:
MACD_A: 12 EMA
MACD_B: 26 EMA
Signal: 9 EMA
EMA: Obviously the EMA
RSI Signal: Number
RSI 30: True/False if the RSI line crosses the 30 mark (the direction of the cross matters)
RSI 45: “”
RSI 55: “”
RSI 70: “”
WOM: Number (between -50 and 50)

If so, and the code is checking what the previous command given was (as I briefly discuss on that attached document) then I just need each of these indicators to show either BACK/LAY/CLOSE TRADE or nothing. If all three triggers display the same thing as each other (within the given look back period) then a BACK/LAY/CLOSE TRADE order will get placed in cell L9 on the Bet Angel tab.

If you look at the Control Panel tab I have presented this with an example in ‘Brand 2’. As the numbers tick in cells C15:L15, the rows underneath are checking to see if there are any crossovers i.e. RSI Signal WAS above 70 but is now below so “BACK” (this stays on screen for a count of whatever the look back period is set as). If cells D17, H17 and L17 all show “BACK” at any point simultaneously then M17 (signal) will show back or the command can go immediately to the instruction fields on the Bet Angel tab (Cell L11 for Brand 2) and voila the system has gone from start to finish and an order has been placed!! Getting close now!

For now, would it be best just to use Boolean outputs to keep it from getting messy? Any upward signal crosses returns a “0”, any downward signal crosses returns a “1”.

Cheers gents,
CPerry.

20911

[SamT]

1 and 0 might be Booleans, or they might not be, It depends on outside variables. True and False are always Booleans, Period.

Now, I'm off to see your attachment.

Back from the attachment.
in re the Class modules:
Where are the Property subs associated with the variables?
Where are the pName variables and Properties?

[SamT]

I see you changed all the Row/Column references in these Tables



Data Fields for All Brands
_______

Instruction Fields for All Brands


Developer Nomen

Column/Row


Developer Nomen

Column/Row



BAInputTable_FirstCell
B1

BAReport
L9


BAInputTable_LastColumn
10

BAOdds
M9


BABrandName
B9

BAStake
N9


BABrandPnL
C9

BAStatus
O9


BABrandClosePnL
D9

BAMatchedOdds
P9


BABrandGreenUpPnL
D10

BAAmountMatched
Q9


BAThirdBack
E9

BABetReference
R9


BAThirdBackVolume
E10

BATimeOfTrade
S9


BASecondBack
F9

BAUnmatchedAverageBack
T9


BASecondBackVolume
F10

BAUnmatchedBackstake
U9


BAFirstBack
G9

BAUnmatchedCount
V9


BAFirstBackVolume
G10

BAUnmatchedAverageLay
W9


BAFirstLay
H9

BAUnmatchedLaystake
X9


BAFirstLayVolume
H10

BAUnmatchedLayCount
Y9


BAThirdLay
J9

BAAverageBack
Z9


BAThirdLayVolume
J10

BATotalSelectionBack
AA9


BASecondLay
I9

BABackCount
AB9


BASecondLayVolume
I10

BAAverageLay
AC9


BAPreviousPrice
K9

BATotalSelectionLay
AD9


BABrandVolume
K10

BALayCount
AE9





BAAWP
AF9










I can live with 9 and 10 as row references, it's just a little bit more code. BrandRow1 and BrandRow2, was just easier to code for.

But, the BAInputTable_FirstCell is not Range("B1")

Also, the Instruction Fields table should have FirstCell and LastColumn variables.

You might as well change the LAst Column Reference to the actual Column Letter. It's too easy to make mistakes when counting columns to use the column number.

[SamT]

I would have liked to see a row inserted above the Data Tables on the BA sheet, But your addressing scheme means that everytime you change the layout of the BA Sheet, you would have to edit both the Descriptor tables Row numbers. If you don't like ColumnLetter& RelativeRowNumber, it is Code friendly to use:
ColumnLetter & ":" & "Upper|Lower"

For examples
BABrandClosePnL ------> D:Upper
BABrandGreenUpPnL ---> D:Lower

This schema provides for changing Layouts with minimal editing and minimal chance for editing errors. I think it also makes more sense to the BA Developer, (that be you,) who is the one who must set all these values in a code friendly style.

[SamT]

BTW, good job on adding those new Instruction fields. :beerchug:

[SamT]

How I did it



Data Fields for All Brands


Instruction Fields for All Brands


Developer Nomen
Column/Row

Developer Nomen
Column/Row


BADataTable_FirstCell

B9

BAIntructionsTable_FirstCell
L9


BADataTable_LastColumn
K

BAIntructionstTable_LastColumn
AF



BABrandName
B:Upper

BAReport
L:Upper


BABrandPnL
C:Upper


BAOdds
M:Upper


BABrandClosePnL
D:Upper

BAStake
N:Upper


BABrandGreenUpPnL
D:Lower

BAStatus
O:Upper



BAThirdBack
E:Upper

BAMatchedOdds
P:Upper


BAThirdBackVolume
E:Lower

BAAmountMatched
Q:Upper


BASecondBack
F:Upper

BABetReference
R:Upper


BASecondBackVolume
F:Lower

BATimeOfTrade
S:Upper


BAFirstBack
G:Upper

BAUnmatchedAverageBack
T:Upper


BAFirstBackVolume
G:Lower

BAUnmatchedBackstake
U:Upper


BAFirstLay
H:Upper

BAUnmatchedCount
V:Upper


BAFirstLayVolume
H:Lower

BAUnmatchedAverageLay
W:Upper


BASecondLay
I:Upper

BAUnmatchedLaystake
X:Upper


BASecondLayVolume
I:Lower

BAUnmatchedLayCount
Y:Upper


BAThirdLay
J:Upper

BAAverageBack
Z:Upper


BAThirdLayVolume
J:Lower

BATotalSelectionBack
AA:Upper


BAPreviousPrice
K:Upper

BABackCount
AB:Upper


BABrandVolume
K:Lower

BAAverageLay
AC:Upper





BATotalSelectionLay
AD:Upper





BALayCount
AE:Upper





BAAWP
AF:Upper

[CPerry]

Ahhhhhhh ok, if BrandRow 1 and BrandRow2 is easier then stick with that, was just a little unsure how everything was being read and wired up. If the previous was correct just stick with that. Also with regard to the schema, I am unfamiliar with “Upper/Lower”, keep it as is please; again I’m just getting to grips with how everything is wiring up. I had to get rid of that row above the Data Tables because the Instruction Fields were all one row off when linked through BA. If this row can be squeezed in anywhere else, feel free to re-add it. If it goes in the previous position, I’ll just have to move the Instruction Fields down a row is all.

Why thank you! Does all of that coloured area that I created make sense?

I don’t think I follow. What has got to go in the class modules exactly? Can you see the 4 I created with the list of Developer Nomens in each? I was meant to ask this in my previous message but what properties does each require? Is this where the individual indicators coding gets placed? I realise this stage is really starting to test my ability now so I apologise if I continue to make silly mistakes/comments. Please know I am trying my best to keep up, read, learn and grow every time I come back to these messages on a daily basis.

[SamT]

I am unfamiliar with “Upper/Lower”, keep it as is please; again I’m just getting to grips with how everything is wiring up
"Upper" stands for the upper data Row of this Brand. "Lower" stands for the lower data row of this brand. All Brands occupy 2 rows across the sheet. I think that "upper" and "Lower" are generic enough to be technically correct, where both "1 and 2" and "9 and 10" are too specific to fit all cases.


I had to get rid of that row above the Data Tables because the Instruction Fields were all one row off when linked through BA. If this row can be squeezed in anywhere else, feel free to re-add it. If it goes in the previous position, I’ll just have to move the Instruction Fields down a row is all.
Sorry, I thought it was apparent that both Data and Instructions had to move in tandem.


What has got to go in the class modules exactly? Can you see the 4 I created with the list of Developer Nomens in each? I was meant to ask this in my previous message but what properties does each require? Each Variable, including the pName Variable, needs a Let Property sub and a Get Property Sub

See this: http://www.cpearson.com/Excel/Classes.aspx
In this page, in the Class Basics section, he has three Private Property Variables and simple Get and Let Property Subs for each. That is all those four Classes need. It is also the absolute most basic coding of VBA Classes you will run into.

For more info in re Classes, DuckDuckGo "VBA Class Modules"


Is this where the individual indicators coding gets placed? That's where the indicators get stored for use throughout the Helper App. I haven't decided yet the best way to put the values in them. I want to keep RunTime overhead low. I have already HardCoded an offspring of the three Descriptor Classes to use while testing the BA Output and Helper SheetWriter.



I don't know your finances, but I strongly recommend UltraEdit for any serious coder/Programmer. Somewhere I have, or used to have, a UE macro that turns a list of Variables into a full set of Get/Let Subs.

[CPerry]

Evening SamT

So am I correct with what I'm doing with the following code, would you mind just proof reading this please?:
This is the clsDataFields class module. I start by identifying what I believe to be the variables all 'As String' in a list and then I've written the first bit of code for the first variable on the list (pBAInputTable_FirstCell). If this is correct I will repeat this kind of format underneath for the rest of the variables in this list as well as all of the other variables for the other 3 class modules. Is my example correct?



Private pBAInputTable_FirstCell As String
Private pBAInputTable_LastColumn As String
Private pBABrandName As String
Private pBABrandPnL As String
Private pBABrandClosePnL As String
Private pBABrandGreenUpPnL As String
Private pBAThirdBack As String
Private pBAThirdBackVolume As String
Private pBASecondBack As String
Private pBASecondBackVolume As String
Private pBAFirstBack As String
Private pBAFirstBackVolume As String
Private pBAFirstLay As String
Private pBAFirstLayVolume As String
Private pBAThirdLay As String
Private pBAThirdLayVolume As String
Private pBASecondLay As String
Private pBASecondLayVolume As String
Private pBAPreviousPrice As String
Private pBABrandVolume As String

‘First Cell Property
Public Property Get FirstCell() As String
FirstCell = pBAInputTable_FirstCell
End Property
Public Property Let FirstCell(Value As String)
pBAInputTable_FirstCell = Value
End Property


I've just has a little look at UltraEdit and for £100 if I can start making a bit of money this will be one of the first things I reinvest in, there is also a trial version which looks like good value.

Looking forward to your reply,
CPerry.

[SamT]

You've got the idea.

Now just Follow standard Naming Conventions. And declare all these as Ranges

Private pBAInputTable_FirstCell As Range

‘First Cell Property
Public Property Get BAInputTable_FirstCell() As Range
Set BAInputTable_FirstCell = pBAInputTable_FirstCell
End Property

Public Property Set BAInputTable_FirstCell(FirstCell_In_BA_InputTable As Range)
'When the Coder uses this property, the input Parameter Variable Name is visible to him/her, So I use it as a hint.
'Set Property Sub are used when the Property is an Object of some Sort
Set pBAInputTable_FirstCell = FirstCell_In_BA_InputTable
End Property

Later we will need to add Cell Address Get Subs. These will not need their own Private Property Value

Public Property Get BAInputTable_FirstCellAddress() As String
BAInputTable_FirstCellAddress = pBAInputTable_FirstCell.Address
End Property


Note how all the names relating to this property are so similar to each other. Also note how each Name exactly describes what it's talking about, ie a Cell or a Cell Address. This is one way to make wrong code stand out to the eye. It also means that one only has to remember the Naming Convention to correctly use the property in any code.

I really like writing my code as if it was an instruction manual. It takes too long, otherwise, to figure out what I was thinking 6 months after I wrote it. The verbosity of the Source Code, (VBA) does not effect the size of the Compiled Code, (hidden in the xls(m) file,) and may actually improve it's efficiency.

[CPerry]

2092520926

Please find attached two documents:
1) The most up to date WIP master file with all four class modules completed.
2) A word document of 'Get Subs' that I made separately as you said we didn't need them yet - when we do they can just be copy and pasted from this document with ease.

It took a long time to go through all of that code but I actually quite enjoyed it!

Cheers,
CPerry.

[SamT]

It took a long time to go through all of that code but I actually quite enjoyed it!

That's very good to know. It means you're worth teaching. And believe me, this is a crash course in advanced VBA and Excel Programming.

The Address Property Subs , all the property subs looked good. I Refactored the Data Fields Class, adding in the Address Subs.

Since you are now very familiar with simple Properties, I drastically changed the layout of the code in the module. You already understand how Properties work, as a coder, all you're interested in now is that they work. This style of layout is only applicable to this simple type of Data Storage Class.

The only thing left to add is the Terminate Event Handler, but that is nothing to a good search and replace editor like UE.

Making a Property like these into oneliners just means replacing all End of lines in a Sub with ColonSpace, (": "). I did it by hand, With C&P. Then I used Excel to sort them alphabetically, then Cut and pasted them into the order I wanted.

I also found one Set Property with the pVariable name. Well VBA found it when I Compiled the project. Lucky me it was in the Class I was working on.

Anyway, you can add the Address Property Subs. Change the layout if you want. Then these will be ready to use.

Note that using the layout in the One I did made the bad Name stand out, since it was the only one that started with "pBA..."

[offthelip]

Hi,
I have been away for a week and so I haven't been following progress on the workbook. Mosty of it look Ok, however the Parameter Input section doesn't make sense:

macd_A_SeriesCountmacd_A_Smoothing
macd_B_SeriesCount
macd_B_Smoothing
macd_C_SeriesCount
macd_C_Smoothing


rsi_SeriesCount


wom_SeriesCount
wom_Adjuster
wom_BACK_TriggerCount
wom_LAY_TriggerCount


ema_Multiplier

According to the original definition on MACD, RSI and WOM, there is a direct relationship between the "series count" and " the "Smoothing" so you don't need both.

In the original definition of the calculation the assumption is that interval betwen updates is fixed, so the "smoothing" is calculated by the equation:
smoothing= 2/(seriescount +1)
Because we don't have a fixed update interval we need to modify this equation by our " time since last update modifier" which I assume will be specific for each Brand. This value comes from the Brandupdate with each new value, and so is not a parameter

So the only parameters we need for these calculations are :
macd_A_SeriesCount
macd_B_SeriesCount
macd_C_SeriesCount


rsi_SeriesCount


wom_SeriesCount

I am not sure what:

wom_BACK_TriggerCount
wom_LAY_TriggerCount

are supposed to represent so I can't comment, is this supposed to define the "lookback period" for all triggers??


What does appear to be missing from this section is the trigger points for each of these three indicators. I would expect to see:
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

I hope this helps.

[offthelip]

I have another query about the input parameters: the first field is:

Number of Updates before triggering VBA 10 Update_Start_Trigger
This doesn't make sense to me at all:
To get the maximum throughput before you start losing updates you need to "Minimise that maximum calculation time" as I stated before.
Assume we get an update every 20ms, and that the update takes, 5ms to do the update. If you wait until every 10th update to do the calculation you will be "wasting" 15ms of possible calculation time every update. After 10 updates you then have only 15ms to do all the calculations otherwise you will miss the next update. The way to use the available time most efficiently is to "spread" the calculation out into 10 different calculation bundles:
For example:
1st iteration; you do the MACD 12ema calculation
2nd interation: MACD 26 ema calc
3rd MACD signal line calculation and trigger
4th MACD 9ema calcaultion and trigger
5th RSI Ups calculation
6th RSI Downs calculatiom
7th RSI calcual and trigger
8th WOM Up calcs
9th WOM Downs calcs
10th WOM calc and trigger


This is the standard way of maximising throughput in realtime systems. Commonly it is much more complicated than this where different parts of the calculation are at different rates varying from once every 0.5ms to once every 50ms

So my conclusin is we need to trigger the VBA every update and schedule the appropriate part of the calculation using a scheduler.

[offthelip]

@Cperry:
One thing which is worth trying before you go live is to do some back testing of your "rules". To do this all you need is record the price and time data for at least one horse and then add the lindicator calculations to it afterwards and see what would have happened.
Can you modify Sam logger to record just the price, Money , and the update time for one horse during a race sometime. If you post the data I can add in the indicator values easily using my existing functions.

[SamT]

Perry wanted the additional MACD Smoothing.

You, and I, think there is enough time between update Sessions to perform all the calculations, so I don't think the Update_Start_Trigger will be used.

Each UDO has one Write Property that Triggers the UDO.

I did copy some of your posts to the Code Notes file I keep.

wom_BACK_Trigger, Count wom_LAY_TriggerCount, and similar, are used to keep track of the number of Backs or Lays in a row, since Perry wants to insure a trend before Instructing BA with a Back or Lay Command


the assumption is that interval between updates is fixed,



Batch UpDate Duration
Idle between Batch updates
Duration + Idle (Batch)


Averages -->
0.0395726139

0.0901257803
0.1296983942


STDEV -->
0.01113843
0.01966182
0.021211519



0.0263427219

0.066185092
<-- Mins




0.0629596455
0.148812422
<--Maxes


Times in Seconds

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

Only the Info Data is Updated in a roughly singular fashion, the Data Update and the Instructions Update are pure Batch updates, ie the updates are for all Brands/Horses at once. The Update Times for all Brands are identical.

[SamT]

My thoughts on timing:

When $C$2:$C$6 changes, it triggers the BA Worksheet Module to load data into the BAOutput UDO.

The BAOutput UDO Pushes the Data Store to the HelperInput UDO.

Each subsequent UDO in the Helper App is triggered by certain input Properties being accessed by the HelperInput UDO. Basically, I am designing the UDOs to have a LastInput_Received Event

When the HelperSheetWriter UDO receives it's last input from the previous Helper UDOs, it batch Updates the entire Control Panel Sheet.

If the Helper App can do this in >=66ms, we're golden for calculating all BA Updates: And the Helper App and the Bet Angel App can live in one Excel Workbook. I am maximizing Helper App Throughput as best I know.

[offthelip]

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.


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.

[SamT]

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.

[CPerry]

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.

[CPerry]

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!

[SamT]

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


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?

[CPerry]

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?



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.

[SamT]

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.

[offthelip]

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!!)

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?



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.

[offthelip]

Hi Samt

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.


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.


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!!!

[SamT]

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.

[offthelip]

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.


"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

[SamT]

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.


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.

[CPerry]

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:


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?


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!

[offthelip]

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:


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.

[CPerry]

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

20940

CPerry.

[SamT]

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

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

[offthelip]

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

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".

[CPerry]

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):


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


'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:

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



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?


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.

[offthelip]

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.


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.

[SamT]

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

[CPerry]

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

[offthelip]

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

[SamT]

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

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,


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?

[offthelip]

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.
20968
2096920970

[CPerry]

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.

[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.

[SamT]

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.


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

[SamT]

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?

[offthelip]

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!!

[SamT]

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.

[offthelip]

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.

[SamT]

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.

[offthelip]

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.

[SamT]

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?

[SamT]

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:

[offthelip]

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.

For speed, I would rather use static arrays,
I totally agree,


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.


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)

[CPerry]

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?:



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/pre-race-trading-horse-racing-on-betfair/
https://www.youtube.com/watch?time_continue=18&v=b8Av8-Dvgb4
https://www.youtube.com/watch?v=_TKeoraX_YU&list=PLVgQjBRi6AcnrMZXZCfInKPZUDAot_BoD&index=10
https://www.guidetomatchedbetting.co.uk/sports-trading/horse-races/

CPerry

[offthelip]

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.

[offthelip]

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


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%

[offthelip]

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"



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/showthread.php?60306-loading-a-variant-array&highlight=loading+variant+array

[SamT]

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

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

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

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

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

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

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

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

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

[SamT]

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

[offthelip]

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

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



Brands per race
30


Data items per brand
10


Changes per minute per data item
60


Duration of race mins
30


Total Number data words
540000


Bytes per dataword
4


Megabytes
2.16




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





Stocks
350


Days
260


data items
6


Total Number data words
546000


Bytes per dataword
4


Megabytes
2.184

[SamT]

Changes per minute per data item = 60
Updates per Second per data item = 50 =~190MB :dunno

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

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

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

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

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

Thoughts? Suggestions?

[offthelip]

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

[CPerry]

Afternoon gents,

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

@SamT

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

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

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

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

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

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


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

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


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

I am a little unsure what you been by this ^

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

Cheers,
CPerry.

[SamT]

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


‘Bet Angel’ sheets in a workbook and then BA can dedicate each race to a separate sheet I thought BA dedicated each sheet to a particular Horse.
Therefore:
Perry can make 30 Race WkBks, with 30 Shts, with a DWord of fields, > 50K of Records, all 30 WkBks with an identical structure and names.


I will also record some football matches tooI would follow otl's lead on that.

[offthelip]

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

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

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

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

[CPerry]

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

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

[offthelip]

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

[SamT]

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

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

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

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

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

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

[SamT]

Note to all guests,

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

[SamT]

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

Too bad, that would have been ideal.

What are the possibilities with CSV files?

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

[offthelip]

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

[CPerry]

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

[SamT]

Perry,

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

otl,

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




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



I'm going to look into various DB languages.

[SamT]

:banghead:

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

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

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

I deleted a Request for DB Experts in post #213

[CPerry]

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

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

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

CPerry.

[SamT]

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

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

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

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

[SamT]

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

[CPerry]

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


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

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

CPerry.

[SamT]

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

F4 indicates start time - 10:00.

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


see if there are any trends that occur at super high-speeds when the odds fluctuate like helCan you bet during that time? Isn't there a cell that indicate close of betting?

[CPerry]

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

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

CPerry.

[SamT]

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


you can bet along as Cell H1 doesn't show "Suspended" as it comes towards the end of the race the winners odds will drop towards 1 so we could just say if odds are =< 1.3 (can be altered if this is too tight of a window) then wrap up shop.
That's for the Helper App. I'm only considering Data Mining at the moment.

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

[CPerry]

Good morning gents,

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

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

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

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

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

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

CPerry.

[SamT]

VBA is not suitable for AI.

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

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

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

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



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

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

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

[CPerry]

@ SamT

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

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

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

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

Cheers,
CPerry.

[SamT]

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

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

[offthelip]

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

[CPerry]

@ SamT

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

@ Offthelip

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

CPerry.

[SamT]

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

[CPerry]

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

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

CPerry.

[CPerry]

21102

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

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

CPerry.

[SamT]

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

This is what I am basing all code on.

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

[CPerry]

21122

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

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

CPerry.

[SamT]

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

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

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

[SamT]

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

Theory of Operation
All Code uses Worksheet CodeNames

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

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

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

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

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

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


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



______________________________________________


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

[CPerry]

Happy Monday lads!

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


Run-time error ‘13’:
Type mismatch

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

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

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

Brand 1

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

Brand 2

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

etc etc for 6 brands.

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

CPerry :)

[CPerry]

21152

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

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

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

Now the problem.

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

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



Option Compare Text

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

End If

End Sub


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

Whilst I eagerly await your reply, back to the logger :)

CPerry :)

[offthelip]

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

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

[offthelip]

looking at the state machine diagram that I quickly sketched, highlights another problem you need to think about how to handle:
What happens if when you try to close a bet, you get an error back due to that price disappearing before you can trade it?
This is a bit different from when you are entering the market because somehow you must get out of the bet. This makes it a bit more difficult to get the logic right because you also want to avoid closing the bet twice which effectively means going short if you were long or vice versa (back/lay to you)

[CPerry]

@ Offthelip

This is true! :/ I think I'll open up a new thread specifically just for this using a standard BA sheet for cell references and see if anyone else in the community has ever created something similar using vba.

As for the logger, I'm still working on getting it running but with the snow and work I think it will take me a few days - I'll get back to the both of you by early next week at this rate; just giving you both a heads up!

CPerry.

[CPerry]

Just an update for the both of you. Still debugging the log but having a few problems. What hasn't helped today is BetFair has been completely down :S Not looking as if it's going to turn back on until tomorrow either so looks like an early evening for me! Christmas rush has been mad too but hopefully will have a few hours tomorrow to look at it properly again and then spend the Christmas week next week logging as much as I can whilst you two enjoy time with your families. That's the plan anyways! Will get back on it tomorrow and let you know how I get on :)

CPerry.

[CPerry]

Good evening/morning gents,

Firstly I hope you have both had a great Christmas break! I was making progress with debugging the DataRecorder until I got to a stage where I think I fiddled around too much and messed it up so I'm having to track back to the original version instead of retracing my steps.

Because of this I want to make sure I understand each step properly so…
The first error I get is in the Private Sub Class_Initialize(). I get:


Run-time error '13':
Type mismatch

I have had a thorough read of the following link:
https://msdn.microsoft.com/en-us/library/aa264979(v=vs.60).aspx

Which states that the error is because the variable/property isn’t of the correct type or something was passed through the procedure and the code is expecting a different property or value?

I see above in the code OutputData is defined as a variant and the yellow highlighted section in the debugger is ‘Erase OutputData’, when I hover over it it says “OutputData = Empty”. Because of this I think I originally either redim’d it as something else or fiddled around with the part after the = to make it recognise what it was supposed to be identifying (the output data) but like I said, this returned further problems so what would be the correct solution? Am I thinking along the right lines?

I tried after this:
Dim OutputData As Single/Double/Integer or Long (I was just guessing honestly at this stage) which returned the error: ‘Expected array’

Dim OutputData As Short returned:
Compile error:
User-defined type not defined

CPerry.