Consulting

Page 5 of 13 FirstFirst ... 3 4 5 6 7 ... LastLast
Results 81 to 100 of 244

Thread: Bet Angel and Excel

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

  2. #82
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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.

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

  4. #84
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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)

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

  6. #86
    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

  7. #87
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

  8. #88
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location

    Custom Settings Worksheet

    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
    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. #89
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Data_For_Offthelip.xls
    Example Custom Settings Sheet.xls

    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.

  10. #90
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    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. #91
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

  12. #92
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

  13. #93
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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?
    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. #94
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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.
    Attached Files Attached Files

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

  16. #96
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

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

  18. #98
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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.

  19. #99
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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.
    Attached Files Attached Files

  20. #100
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location

    What I keep seeing is that IYO, this can't be done with Excel and VBA.
    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

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
  •