Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 31

Thread: Adding to a Cell Which Time Decays

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

    Adding to a Cell Which Time Decays

    I have a worksheet change event in my sheet one code that calls all of my subs each time there is a change to any of the cells. Once Sub MatchedMoneyTest () gets called I think I need something like the following to happen:

    Sub MatchedMoneyTest ()
    ‘establish a range of 30 seconds (Using the countdown in Cell C4 as a reference to note when a second has passed)
    This is really important as this sub routine can't just loop each second as my machine won't check for updates in other subs which need to run every second also.
    
    ‘Every time an even cell between K10:K20 increases the increased amount is added to the corresponding last traded price (K9:K19) – This information will appear in the column AQ for changes in K10, AR for changes in K12 etc
    For example, if £50 has been added to K10 since the last update and K9 is showing 4.0 then AQ171 will increase by £50. The problem is K9 will refresh much quicker than 1 second at a time and within that second K9 may flicker between two of three prices in really volatile situations so £20 may be at 4.0, £20 at 4.2 and £10 at 4.4???
    
    ‘The value time decays each second as it passes through the 30 second range so most recent figures have a weight of 30/465, a second later it’s weighting is 29/465, the second later 28/465 etc.
    As if the code wasn't complicated enough as it is, I then need the figures that appear in AQ (and AR:AV for the other 5 selections) to decay over a 30 second period so my machine doesn't have to 'remember' that much.
    
    End Sub
    Please help! I can't get my head around how the time decay fits into an already defined range and how the code for all 6 selections will work together and with the other sub routines which also get called every second. I really appreciate anybody taking their time to have a look at this for me and would really appreciate any kind of feedback.

    Thank you,

    CPerry.

    VBA Forum Matched Ladder Exp.xls

  2. #2
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    As you can see from the spreadsheet, it is obvious that K9 is matched with K10, this information goes in column AQ. K11 and K12 go in AR, so there needs to be 6 different cycles happening simultaneously. I was just hoping it could be simplified together for when I expand this to more than 6 for efficiency.

    So far I have...

    Sub MatchedTest ()
     
    Dim MatchedRange As Range
    Dim TimeDecay As Integer
     
    If K10, K12, K14, K16, K18 or K20 change then ‘If no change it should go straight to End if
     
    NewMoneyFormula = what K9, K11, K13, K15, K17 or K19 IS minus what it WAS
    New amount gets added to AQ, AR, AS, AT, AU or AV adjacent to reading in K9:K19 
     
    Set MatchedRange = ‘Currently active cells in column AQ
     
    MatchedRange = ‘Formula for what I want to do with the defined range goes here
     
    TimeDecay = Rank/465*MatchedRange – 1/465 ‘Where the rank = how many seconds have passed
     
    End If
     
    End Sub
    Please somebody help me fill in the blanks!

    I have attached yet another spreadsheet:

    TimeDecay.xlsx

    This shows what I'm hoping to achieve. I have a stationary 30-1 count in column A, random integers between 0:100 in column B and C is the time decay'd end result which I will be recording each second such as in the line graph.

  3. #3
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Hi Cperry,
    Still working on your Betangel project I see!!
    I don't fully understand what you are trying to do, but if you are trying to decay the values which create the values in column C of "timedecay.xlsx". I think all you have to do is once every second multiply each of the values in columns A and B by 29/30, this is very easily done using a macro triggered every second. You already have yoiur worksheet change event triggering frequently so just run the macor when time has increased by a second since it last ran.

  4. #4
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Hi offthelip, yeah still working hard around school terms on this project of mine!

    The software does this but the only information on the spreadsheet is the last traded price and the volume matched on the runner:

    https://www.youtube.com/watch?v=WYXa5K18cJ4&t=51s

    So basically I was hoping to recreate the pink 60 second range, or in this case 30 seconds. But I’ve had a think and I reckon instead of the whole formula having time decay I’d like to explore a few hundred or thousand of markets where just the money just decays over a rolling 30 seconds, got any ideas how this can be done please?

    As you can see in the video the price sometimes moves a few times before a second has passed and in some markets it moves incredibly slowly. The only information I have to go on is K9 the last traded price and K10 amount matched for that one specific selection. The recreation of the pink thing is happening in column AQ. Like I’ve typed before the 30 seconds will decay like the following: 30/465, 29/465, 28/265.


    The problem I am having is with piecing together my idea. Say I already have the following in AQ:

    4.0 = £100*30/465
    4.2 = £150*30/465
    4.4 = £220*30/465
    4.6 = £85*30/465

    If K10 increases by £30 and K9 is showing 4.4 for the entire second then AQ should now show:

    4.0 = £100*29/465
    4.2 = £150*29/465
    4.4 = £220*29/465 + £30*30/465
    4.6 = £85*29/465

    It get's even more difficult when a price moves a few times within a second, the time decay I still want to happen every second.

  5. #5
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I think what you say just confirms what I suggested that all you have to do is multiply one of the values in your equation by 29/30 every second, it doesn't matter whether you add other values in or not, they will all decay exponentially with 30 second time frame.

  6. #6
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    @ offthelip

    I have tried with some dummy values to multiply each next value by 29/30 but this doesn’t work, as you can see in the example spreadsheet it works if you multiply by 29/30, 28/29, 27/28 etc. This can be seen as: Multiplier = P*(R/R+1) Where P is the previous result in the range and R is the rank within the range, in other words, how many seconds left. For example, with 14 seconds left the multiplier will be P*14/15.

    As well as this though, I still have no clue how to take this dripping of new money (from Cell K10) and add it to cell AQ before putting the time decay range on. All I know is that I think the code should go a little something like this…

    Sub MatchedRange
     
    MoneyVariable = K10
     
    If MoneyVariable changes then
    Add New amount to column AQ ‘Do this by looking at the value in K9, matching it with Cell AL and then adding this new value to AQ by offsetting the identified (AL) cell.
    'Also multiply this new added amount by 30/465 to create the first weighted amount.
     
    If one second has passed, then
    ‘ What is present in the active range in column AQ will decay at the rate discussed at the beginning of this post.
     
    End Sub
    Is this possible? I don’t know how to add to a value of a cell, how to identify an active range where there’s not a clear beginning (a stationary Row A) or end and also this only works for the first selection (involving K9, K10 and column AQ). It does not account for any of the other selections.

    TimeDecayExp2.xlsx

  7. #7
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    What you are doing is subtracting a fixed amount for each amount that has been added from the sum each iteration,
    so to get the results you need you are going to need to keep a track of when an amount is added in and also how much.
    The easiest way is when an amount is added don't just add it into the sum, record it in a buffer with the time it was added. Then you subtract 1/30 of this amount from your rolling total every 1 sec. You do this for every value in the buffer. When an amount has been in the buffer for 30 seconds you delete it.

  8. #8
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    I'm not finding a lot of useful information about recording in a buffer other than a lot of guidance on buffer overflows which sounds like something I need to avoid clever I am. So, being serious again, how does a buffer come into play and why is it necessary? I'm guessing the code will start to look like this?...

    Start Sub
    
    New money *30/465 'so it's already 'weighted' when it enters the range
    New money get's placed in column AQ
    
    If a second has passed then
    Everything in the buffer * decay formula (P*(R/R+1))
    
    End Sub
    Please could you explain how to include a buffer in this respect?

  9. #9
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Hi Cperry
    I have written some code to show you how this should work.
    I am using 4 columns A to D , "Amount" "Time Added" , "Fraction" and "Current Value"
    I have allowed for a buffer with a maximum of 30 values but it can be any number you like.
    there are two subroutines
    "AddAmount" this add an amount ( this code using cell J1 as the source of the amount) to the buffer and records the time and the fractional amount to be decremented
    "Decrement" is the routine that will run every second and will keep the current value updated and decremented over 30 sec. 30 seconds after an amount is added this routine will delete the row making it available for the next amount which is added.

    It is all very simple code so should be self explanatory

    Sub Decrement()
    
    
    
    inarr = Range(Cells(1, 1), Cells(30, 4))
    timenow = Time()
    sec30 = 1 / (24 * 60 * 2)
    For i = 2 To 30
     ' Check time
     
      timediff = Abs(timenow - inarr(i, 2))
    
    
       If timediff > sec30 Then
        For k = 1 To 4
         inarr(i, k) = ""
        Next k
       Else
       ' decrement
        If inarr(i, 4) = "" Then
        ' first iteration
         inarr(i, 4) = inarr(i, 1) - inarr(i, 3)
        Else
         inarr(i, 4) = inarr(i, 4) - inarr(i, 3)
        End If
        
       End If
           
    Next i
     Range(Cells(1, 1), Cells(30, 4)) = inarr
      End Sub
    
    
    Sub addamount()
    inarr = Range(Cells(1, 1), Cells(30, 3))
    For i = 2 To 30
     If inarr(i, 1) = "" Then
     ' this is a blank row so populate it from j1
        inarr(i, 1) = Cells(1, 10)
        inarr(i, 2) = Time()
        inarr(i, 3) = inarr(i, 1) / 30
        Exit For
     End If
    Next i
     Range(Cells(1, 1), Cells(30, 3)) = inarr
     End Sub

  10. #10
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Ok I’ve had a little play with this code manually by just clicking a macro button (sometimes twice a second, sometimes missing a second) and I think I get how this works by stepping in turn with the code every line. Sometimes it is a bit sporadic where it starts but I think this is just down to me playing with the sheet manually. The only part I don’t understand is the fraction, I’m assuming this should go 29/30, 28/29, 27/28, 26/27 etc but it is only showing ½ for me. I was getting random fractions appear at first but there was no pattern.

    This is what I was getting...
    Sporadic.xlsx

    I know this was just a theoretical example piece of code and I get how the pieces (minus the fraction) can work but still unsure on how this is going to combine and materialise itself in column AQ with a moving range.

    Time Decay Template Exp.xls

    The differences each second in K10 need to go over to column AQ alongside their K9 readings each time the sheet updates. Worksheet 1 code shows the general structure of my subs. I've been trying really hard to improve since the beginning of the year but looks like I still have a long way to go, just got to try and learn as much every day as I can I suppose.

  11. #11
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    look back at your TimedecayExp2.xlsx, what you wanted was a linear time decay that went from whatever the original value was to zero in 30 seconds that is exactly what this code does.
    You have formatted the Fraction column incorrectly is should just be a decimal number. If you look at the code this is just the value in column A times 1/30. This is the amount that is subtracted off the "current value" every second. So it should be zero after 30 second having gone down in a linear manner.
    Your workbook "time decay template" is complicated and i haven't really looked at so i don't understand what you are trying to do with it.
    All i have shown you is how to do the time decays , where you apply it is a different matter.
    If you explain in terms of "the values in K10 are added into the Value in Aq30 whenever K9 changes, and each value should be decayed over 30 secs", then i might be able to help.
    I just need to know what cells values are moving or being added into what and what decays are needed.
    I forgot to point out that if subtract D6 from E6 on you TimedecayEXP2 worksheet and then copy the equation across the row you will find that the amount being deducted each time is exactly the same
    this is because: assume you start with a value of 30 then
    30*29/30= 29 now multiply this by 28/29 and you get 28 etc etc the decrements are always the same. That is why my "fraction" doesn't need to change it is simple 1/30 whic his deducted each time
    Last edited by offthelip; 08-27-2018 at 10:32 AM.

  12. #12
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Ok so the TimeDecay Template is the BA Template that you saw many many months ago, with the instruction field, the prices, the volume etc granted it was ages ago so the important variation is columns AL:AP. The other columns are used for something else so only AL is important for now.

    The problem with K10 is it just shows the volume on the selection, it does nothing for how this money is spread amongst varying prices of that selection.

    For example, in Time Decay Template Exp.xls the total volume is £51,936.30 (C4) of which the first selection Skin Deep has a volume of £40,386.30 (K10) but not all of this was at 1.56 (K9) so I am trying to have the latest 30 seconds worth of volume to be recorded in column AQ.

    To illustrate this, I have another example spreadsheet: TimeDecayExp3.xlsxwhere you can see A2 being most recent and A31 being 30 seconds ago. Column B shows the amounts added each second (this assumes that all the volume each second is at the same price). Column C shows the money was matched at each second and column D has the Time Decay formula in.

    I have then sorted this information in columns F and G and from that created the orange chart. My end goal is simply to analyse (over hundreds and thousands of markets whether there is an edge in observing the previous 30 seconds worth of movement alongside a momentum indicator that is currently is the recording phase).

    So to summarise, as money enters the market (Seen by K10 increasing) for the first selection I need my spreadsheet to take the new money and place is in column AQ. If the money entered at 1.56 (K9) it would need to appear in AQ57 as this is 5 cells right of 1.56 in column AL. As the new amount enters the market it is multiplied by 30/465 and then for every second it is present within the 30 second range, it decays until after 30 seconds that splash of money is ‘forgotten’. My spreadsheet only has to monitor 30 seconds rather than ‘remember’ hours of information.

    I hope this makes sense what I’m trying to do. Columns F and G in TimeDecayExp3 is what AL and AP will be in the final thing.

    CPerry.

  13. #13
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I think I understand what you are trying to do and what I create is exactly what you need. I have done the buffer vertically, you need to swap it to a horizontal buffer and you then have a separate buffer for each price point down Aq

  14. #14
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    The only problem with that is there are 351 possible cells in AQ, so each one would need a separate buffer? I'm trying to make this as efficient (and quick) as possible so was trying to figure out a way of only having active - and only having a buffer for - the cells in AQ that have been traded in the previous 30 seconds. For example if money has entered at 5.0 (as calculated by K10 increasing and K9 showing '5.0') then that increased amount would enter the buffer in the AQ cell adjacent to 5.0 in the AL column. This way 351 buffers don't need to exist, do you know what I mean?

  15. #15
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    Yes each one will need a separate buffer.
    The fact the buffers exist doesn't cost you any time, the only time taken is updating a buffer , it doesn't really matter which buffer you are updating, whether it is one buffer or one out of 351 buffers the time taken is the same

  16. #16
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Ok, thanks for this. I have a bit of time tomorrow dinner to sit down and look at this in detail for the first time in a few days. In the meantime, around Christmas time last year when I was looking at RSI and MACD indicators you said you'd take a look at some information to pinpoint good entry and exit points. May I just ask, was this a matter of using the solver plugin with some common adjustable variables or is your process a little more complex? I'm just planning ahead and thinking of how to analyse (and what I need to analyse) once I've worked out this stage and logged a few thousand markets.

    Cheers,
    CPerry.

  17. #17
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    was this a matter of using the solver plugin with some common adjustable variables or is your process a little more complex?
    I think what you are referring to is the system I set up for back testing strategies. The final system I ended up with was extremely complex although the basic principle was very simple.
    I started with worksheet with a long list of OHLC price data in columns A to E, (column A was the date or the time depending on whether it was daily data or intraday data). I then added columns from column F onwards which calculated all sorts of indicators such emas RSi , PSAR, ADX , candlestick patterns etc , etc. I wrote functions for some of these, others I just calculated using one or more columns, I then combined these columns to create "Buy" triggers and "Sell" triggers.
    Then I ran two macros a "Long" macro which would look first for "Buy" triggers and enter market on a Buy trigger and then hold the position til a "sell " Trigger" was encountered. The macro below does just this.
    I also developed a "short" macro which was virtually identical but going short
    However I developed the system further to take account of setting the initial stoploss and trailing the stoploss and using multiple closeout signals, and multiple positions and many other things as well. Including recording any other paramaters which I wanted to record.
    I used this system to test strategies, and I was able to run tests on enormous amounts of data e.g I could run tests which included all the stocks in the FTSE 350 over any period, although due to historical data problems I rarely ran test on stocks over more than 6 years. However on things like the FTSe 100 and DOW I was running over decades of years of data.
    Having found a strategy that seemed to work I would then test it on an entirely new set of data to ensure that I wasn't just "curve fitting" If it passed this stage, I then would look at how I could actually trade the strategy and how I could implement it automatically. Often the strategy would fall over at this stage. If I was able to get it passed this stage I would then test it on a dummy account. This step was essential because it is only when running on the real interface with the provider do you find out the timing quirks and the problems of what happens when your system or their system goes off line, and other things such my system and their system getting out of step.
    I also developed tools for analysing the results. Finding trades which are very profitable is not the only thing to be looking for, you might have system that only wins one in ten times but when it wins it makes £100, when it loses it loses £10. so after 20 entries you would expect it to have one twice (+£200) and lost 18 times (-£180) . How do you compare this to a system that wins 55% of the time but only wins £10 when it wins and loses £10 on losers After 20 entries you would expect 11 winners (+£110) and 9 losers (-£90) . The profit is the same.
    Another aspect is you might find a system that wins £100 and has win rate of 75%, absolutely brilliant you might think, however if this only occurs 6 times in 6 years it is going to be difficult to make much money using it.
    there were lots of other things I was looking at such maximum expected drawdown, maximum risk, capital needed, lots of other things
    All of the system and tools I developed were trying to ensure that the strategy was usable and generated sufficient trades to ensure the statistics were valid and profitable.

    I hope this helps

    Sub golong()
    
    
    buycol = 9
    sellcol = 10
    profitcol = 11
    firstrow = 16
    LastRow = 274
    bytrig = Range(Cells(firstrow, buycol), Cells(LastRow, buycol))
    selltrig = Range(Cells(firstrow, sellcol), Cells(LastRow, sellcol))
    Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol)) = ""
    
    
    Profit = Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol))
    closep = Range(Cells(firstrow, 5), Cells(LastRow, 5))
    Longf = False
    For I = 1 To (LastRow - firstrow)
     If Not (Longf) Then
       If bytrig(I, 1) Then
         buyp = closep(I, 1)
         Longf = True
       End If
      Else
    '    If bytrig(i - 5, 1) Then
        
        If selltrig(I, 1) Then
         Profit(I, 1) = 100 * (closep(I, 1) - buyp) / buyp
         Longf = False
         End If
      End If
    Next I
    Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol)) = Profit
    
    
    End Sub

  18. #18
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Hi offthelip.

    I have added your example code (But amended it so it now operates in T2:W2 and takes the added amount from K10) but I am still confused how this works if K9 changes more than once per one second update.

    Everything in this updated example spreadsheet: Previous_30_Working_Exp.xlsmis wired up correctly and works. If you look at A19:E48 you can see an example 30 seconds. Now if you change K9 to whatever is in column B (working up the list from the bottom to the top) and pressing the macro buttons in the correct order you will see the final outcome is well off. I was still using the timenow function so had to ‘play’ the buttons with the correct timings, took me a few times (thank god I’m a mediocre musician). You may have a smarter idea of how to run this.

    Macro button order:
    Check K9 is the next number in column B (B48 first reading) working from the bottom of the list to the top.

    Press £25/£50/£100 macro button based on reading in column C.
    Add Amount Macro Button
    Time Decay Button
    TickTock button (Counts down one second – now repeat the process again with B47 and ascend the list)

    On the second cycle you type 1.18 into K9, press £25, AddAmount, TimeDecay then type 1.19, press £25, AddAmount, TimeDecay and then press TickTock. This is where the problem is, when there’s more than one price (K9) per update. F19:G25 is what the final figures should show with this feux data and ultimately G19:G25 should be R14:R20. Just can't seem to get this final R14:R20 no matter what I try!!

    Cheers,
    CPerry.

  19. #19
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    ^^ That is all very interesting to read, you have given me a number of additional things to consider when I run my next lot of logs.

    I’m excited for the analysis part of this new system. The one big difference with these markets to the FTSE stocks is that they are much shorter and they end so I can’t have any long ranging indicators like for example a very slow EMA. I think the short nature of these markets means I’m going to have to go about my analysis a little bit different but I have some good ideas with how this can be done!

    If I collect enough data i.e. over 10,000 markets do you reckon splitting it into two 5,000’s (back test and new data) would be a wide enough range to sniff out any ‘curve fitting’ or would you rather have three 3,333’s (back test, new data, even more new data)?

    I have my practise account ready to go, playing practise mode first then opening up my funds with a max of £100 I’m willing to lose by trading £2 stakes many many times. Before any of this though I’m looking forward to really delving into the analytics and seeing if I have anything that looks promising.

    You make some very good points about strike rate and my win/loss ratio. I’m going to be analysing potential strategies’ ROI, strike rate etc just to see which kind of strategies work best with my ideas. This is more of a swing trading system than a scalper so I’m expecting my profits and losses to be a lot looser; bigger ROI’s but smaller strike rate’s I’m anticipating.

    CPerry.

  20. #20
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    I had a look at Previous_working_exp workbook and you have put my code in it, but my code was only a single buffer you need to modify it so that it creates a buffer for each one of the prices. So I am not in slightest bit surprised it doesn't do what you expect.
    As a clue you are not looking at cell(9,11) which is the one that holds the price. What you need to do is use the price as an index into the array of buffers. For convenience I would put the buffers in columns AA to some far column. remember you need 3 values for each entry so effectively you have three buffers offset along the same row, I would offset them by a large number which you know will be more than the maximum possible entries on a row. e.g 50 or 100

    the other thing I would do is set up your list of test data, to run automatically, remember that when testing you don't have to do it in real time, you can do it faster (preferable) or slower ( not preferable, but sometimes it happens)

    So with your data in A19 to C48, sort it into the order you want it to be "played" use a separate row for each item, reverse the order of time count so that 1 is the first to "played" 30 is the last , if two items have the same time that is fine.
    Then set up a loop running a count starting at 1 up to 30, if count is 1 call "addamount" with the values for each row with a 1 in the time col, then call decrement, then increment the count and do time slot 2, then decrement again.
    This should seconds at most to run, (Not 30 seconds)
    Writing code to do this should take no longer than putting all those buttons on the worksheet which you have put on to do it manually. #
    Try to start thinking about how to automate as much as possible of what you are doing. Everytime you develop a little system you have then got a little item for your toolbox you can modify to and reuse for something else and you teach yourself how to really advance in the game.

Posting Permissions

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