Bet Angel and Excel

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

[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/show...l=1#post371012

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

[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.
22561129_10155817645162767_568002171_o.jpg22556250_10155817784897767_1684681855_o.jpg22553842_10155817785027767_1820909820_o.jpg22641817_10155817784712767_1691973258_o.jpg22555823_10155817784767767_1509521098_o.jpg

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

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

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

1. On the VBIDE Menu >> Tools >> Options
2. On the Editor Tab, Check all Boxes, Except that the Drag and Drop Text Editing box is optional.
3. 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]

22561107_10155819788862767_447517682_o.jpg
5^th 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.p...ngth_index_rsi


See also

• Cutler's RSI
• Stochastic RSI
• Relative Strength Index (RSI)
• Welles Wilder Volatility Index
• Welles Wilder Smoothing

[CPerry]

Data_For_SamT.xls

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.

[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