Bet Angel and Excel

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.

Quote:

---

If Betangel give an accurate update time with an accuracy of at least 10 ms

---

While 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/Knowledge...-practice-mode
http://betangel.kayako.com/Knowledge...tions-settings
"The expected response times inside the UK can be anywhere between 50ms - 200ms."
End Note

Maybe something like:
BA Cell C3 = LastUpdateTime

Code:

---

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

---

clsUpdatesPerSecond

Code:

---

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

---

The Latest, for your comments and suggestions.

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

Attachment 20789Attachment 20790Attachment 20791

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:

Code:

---

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.

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

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.

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

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

Quote:

---

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.

Now I need the Value Function for that Class Module







:devil2:

The functions should be:

Code:

---

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

---

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

Code:

---

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:

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

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:

Quote:

---

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:

Code:

---

' 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

---

Quote:

---

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:

Quote:

---

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

---

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

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.

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.

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.

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

Hi Sam,

Quote:

---

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)