Consulting

Page 1 of 13 1 2 3 11 ... LastLast
Results 1 to 20 of 244

Thread: Bet Angel and Excel

  1. #1
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location

    Bet Angel and Excel

    Data_Scroll_Test.xls

    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.

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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
    Last edited by offthelip; 10-03-2017 at 11:12 AM. Reason: Corrected to add enable events

  3. #3
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location

    Cool Nearly there.

    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.

    22323650_10155792491257767_665361950_o.jpg22323569_10155792491437767_1391447544_o.jpg22323913_10155792491462767_30472609_o.jpg22368861_10155792491507767_267998208_o.jpg

    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.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    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.
    Last edited by SamT; 10-10-2017 at 06:37 AM. Reason: added Code Formattig Tags with # icon

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    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
    Last edited by SamT; 10-11-2017 at 10:10 AM. Reason: Added Code Formatting Tags with # Icon

  8. #8
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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

    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?
    Last edited by SamT; 10-11-2017 at 03:30 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  9. #9
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    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.

  10. #10
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    Last edited by SamT; 10-11-2017 at 03:13 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  11. #11
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Outside links:
    https://en.wikipedia.org/wiki/MACD
    https://en.wikipedia.org/wiki/Moving...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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  12. #12
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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,, (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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  13. #13
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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"
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  14. #14
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    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.

  15. #15
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  16. #16
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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:

    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:
    for any suitable k ∈ {0, 1, 2, …}

    The weight of the general datum point
    is .
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  17. #17
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    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!
    Last edited by SamT; 10-12-2017 at 04:56 PM.

  18. #18
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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 : somewhere? Where does it go? Each and every bit, please

    Edit to add: Never mind... Outside link to Data Flow
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  19. #19
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Interesting read: https://www.betangel.com/forum/viewt...e9d7d3ad294be6

    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  20. #20
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Data_Scroll_Test.xls

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •