Adding to a Cell Which Time Decays

[CPerry]

I just tried to use the single buffer to be honest as I’m struggling to visualise the final thing but perhaps I’m getting ahead of myself so I had a go at creating a buffer for each price. I have been scratching my head over this all weekend.

Ok so breaking it down to the bare basics, I firstly want Sub AddAmount to identify the price in (9, 11) and use this as an index you say, so the array can place the money in the correct buffer? Save having 351 separate buffer subs, there will be a single array which accounts for each of the 351 possible prices. So I just need to modify the AddAmount sub routine not the Decrement one yet?

I thought that makes sense and I began reading on how to use an index with an array in VBA:
https://stackoverflow.com/questions/...rray-excel-vba

I thought the first example in this link would fit nicely in but then I got confused with the part you typed after. I thought arrays were behind the scenes, so why do I need rows from AA onwards for the buffers. I assumed the final figure at each price would be written only to column AQ for the first selection and there wouldn’t be anything else being written to the sheet at this stage?

If I understand that the: ‘Amount, Time Added and Fraction’ columns are what you are referring to when you say I need 3 values for each entry and the Current Value is in fact column AQ when these figures get sorted then I don’t understand why there would be more than 3 columns (50 to 100 you said?). If I use (9, 11) as an index, how am I referring to an array with that figure? Does it precede the part already in the AddAmount routine? If so wont this just fill in 3 columns?

I think I’ve over thought a lot of this, just need some clarity please.

[offthelip]

Hi Cperry,
Sorry for delay in reply I have been away. I have written some code which will do the add amount and decrement using 351 separate buffers as defined on your "previous _Working_exp " workbook
I haven' actually writtne any information back to the workbook because I didn't know what you wanted or where.
You were absolutely correct about my comment about writing to the columns to the right of Q. it is entirely unnecessary I was thinking you needed to record them.
Not the msgbox, is just for debug so you know where to look for the values in the 3 dimensional array G3darr

You must run "Init" first, you could do this on workbook open

Public G3darr(1 To 351, 1 To 30, 1 To 4) As Variant
Public prices() As Variant


Sub init()
For i = 1 To 351
For j = 1 To 30
 For k = 1 To 4
G3darr(1, j, k) = ""
 Next k
Next j
Next i
prices = Range(Cells(1, 13), Cells(351, 13))


End Sub
Sub Decrement()


timenow = Time()
sec30 = 1 / (24 * 60 * 2)
For p1 = 2 To 351
For i = 2 To 30
    'Check Time
    If G3darr(p1, i, 2) <> "" Then
    timediff = Abs(timenow - G3darr(p1, i, 2))


    If timediff > sec30 Then
        For k = 1 To 4
            G3darr(p1, i, k) = ""
        Next k
    Else
    'Decrement
        If G3darr(p1, i, 4) = "" Then
        'First Iteration
            G3darr(p1, i, 4) = G3darr(p1, i, 1) - G3darr(p1, i, 3)
        Else
            G3darr(p1, i, 4) = G3darr(p1, i, 4) - G3darr(p1, i, 3)
        End If
    End If
    End If


Next i
Next p1
    
End Sub
Sub AddAmount()
amount = Cells(10, 11)
Price = Cells(9, 11)
' find buffer
For k = 1 To 351
   If Price = prices(k, 1) Then
    p1 = k
    MsgBox (k)
    Exit For
   End If
  Next k
For i = 2 To 30
    If G3darr(p1, i, 1) = "" Then
        'This is a blank row so populate it from j1
        G3darr(p1, i, 1) = amount
        G3darr(p1, i, 2) = Time()
        G3darr(p1, i, 3) = G3darr(p1, i, 1) / 30
        Exit For
    End If


Next i
    
End Sub

[CPerry]

Hi offthelip, hope you’ve been away somewhere nice!

I have been looking through the code and just have a few questions. Before that though, the information that needs to be written back to the sheet is just the numbers in column R in “Previous_30_Working_Exp” workbook. So after the figures have ‘decayed’ they go in R alongside the value in M (which value exactly is taken from K9). For example, If K9 = 1.4 then 1.4 that can be found in M41 therefore the decayed value will be added to R41 as this is 5 cells to the right. 30 seconds worth of data will occupy a mini range within column R and this is the only information which needs to be written back to the sheet.

My first question is regarding the Sub init. I’ve been stepping through to see how it works and it appears it cycles through each letter I, j, k in turn and does a total of 30,000 to 40,000+ loops. Luckily I did a bit of maths and didn’t click through that many times aha but please tell me what is this for? How many times does it need to be called, just once as the workbook opens or as each new market opens?

I ask because the first bug I get is in the Sub AddAmount (). After

For k  = 1 to 351
            If Price = Price(k, 1) Then

It returns a type mismatch and shows Price = 1.1 (Makes sense as this is the value taken from Cell K9) but then hovering the mouse after the = it shows Price(k, 1) <Type Mismatch>.

Before I start messing around I wanted to know what the purpose of the init sub was please. I understand the letters having to be established for later on but why do this cycle of 4 inside a cycle of 30 inside a cycle of 351? It is the letter k I assume that is the problem at the moment and may be the other letters after this line has been debugged. Why the mismatch?

Looking like it’s not that far away now!

Thank you as always for taking a look for me,
CPerry.

[offthelip]

The purpose of the sub init is to initialise the 3 dimensional array with blank because we start using the numbers in the decrement subrotine so we need test if they are zero to see if they are in use or expired.
This sub also loads the Prices array with the prices in column M of your spreadsheet.
You have to decide when to call this. Are the prices of the odds the same for every race? what do you want to do about values which still remain in the array after the end of the race? will you still keep calling decrement every second between races? Would it be easier to reinitialise for every race?


The reason you get a type mismatch is because the first value in the array prices is the header in colum M which is the word "Odds". Obviously when you try to comare 1.1 to a word you get a type mismatch. thus the index in your loop should start at 2
e.g

For k = 2 To 351

   If Price = prices(k, 1) Then

I think what you want written back to worksheet in column R will be the sum of decrementing values. although I am not usre from you explanation of what you want:
I think this code will do what you want: (untested)

Sub Decrement()
outarr = Range(Cells(1, 18), Cells(351, 18))
timenow = Time()
sec30 = 1 / (24 * 60 * 2)
For p1 = 2 To 351
p1sum = 0
For i = 2 To 30
    'Check Time
    If G3darr(p1, i, 2) <> "" Then
    timediff = Abs(timenow - G3darr(p1, i, 2))


    If timediff > sec30 Then
        For k = 1 To 4
            G3darr(p1, i, k) = ""
        Next k
    Else
    'Decrement
        If G3darr(p1, i, 4) = "" Then
        'First Iteration
            G3darr(p1, i, 4) = G3darr(p1, i, 1) - G3darr(p1, i, 3)
            p1sum = G3darr(p1, i, 4)
        Else
            G3darr(p1, i, 4) = G3darr(p1, i, 4) - G3darr(p1, i, 3)
            p1sum = p1sum + G3darr(p1, i, 4)
        End If
    End If
    End If


Next i
outarr(p1, 1) = p1sum


Next p1
 Range(Cells(1, 18), Cells(351, 18)) = outarr
 End Sub

[CPerry]

I think it would work best if it reinitialises for each market. At the moment my sheet 1 code looks like this and I’ve written notes with what I think should be included:

Private Sub Worksheet_Change(ByVal Target As Range)
 
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Range(“C4”).Value > 1 Then ‘Check the sheet had loaded before returning an error
 
‘Variable = B1 (Name of market), If B1 changes then reinitialise sub init and clear my rolling logger (call two separate subs)
 
‘Every update
Call AddAmount
 
Call StateMachineOne
Call StateMachineTwo
Call StateMachineThree
Call StateMachineFour
Call StateMachineFive
Call StateMachineSix
 
‘Every second
Call Decrement
Call RollingLogger
Call DataTab
 
End if
 
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
 
End Sub

So there are clearly different areas for subs which need to run each time a cell changes, subs which need to update once a second and subs which need to run each time a new market loads into the sheet.

The prices are different for every selection for every market. At the start of a new market column R needs to be emptied. The next race will load immediately after the previous until the end of the day so decrement can continue to be called every second, it doesn’t really matter. Not if it falls into the above code how I’ve laid it out.

Ahhhh. It’s mixing text with numbers, I get it! Yes that’s exactly what I want I column R, the sum of the decremented values. I will try this code out right now and see if it works!

[CPerry]

Update:

All three subs now run smoothly with no errors and column R fills up with 0’s (These would be more beneficial to remain blank, also better if doing this speeds up the process). The big problem is the cell next to 1.1 in the R column is also 0. I’ve tried adding an amount to K10 multiple times and then pressing the add difference button and time decay button each time but nothing happens. The msgbox appears so I know the subs are running fine but the cell doesn’t increase by a decremented amount. I’ve attached the spreadsheet below. Ran init at the start then keep adding amounts and pressing the buttons. Nothing is increasing in column R.

Previous_30_Working_Exp_V2.xlsm

[offthelip]

you must be much more careful about copying code: also try and understand how it works, if you do it will obvious why it doesn't work. , it took me seconds to see why you code didn't work,
you have missed out two vital lines of code:
your code:

    Else    'Decrement
        If G3darr(p1, i, 4) = "" Then
        'First Iteration
            G3darr(p1, i, 4) = G3darr(p1, i, 1) - G3darr(p1, i, 3)
        Else
            G3darr(p1, i, 4) = G3darr(p1, i, 4) - G3darr(p1, i, 3)
        End If
    End If

look back at post 24 and note the difference.

[CPerry]

Hi offthelip,

Yes, very stupid mistake! To cut a long story short, the coding happens on my brother’s laptop (Windows - but I use the internet (inc this forum on my MacBook)) so I manually copy the code over and try it out a line at a time usually to learn how it works, completely overlooked two vital lines this time round obviously.

That has sorted the problem of only returning 0’s but now there’s another problem, it’s decrementing the entire volume in K10 so if K10 increases from £1,000 to £1,025 it should only show 0.05376 (£25 * 1/465) and then this amount will decrement by 1/465 every second. Instead it’s doing £1025 * 1/465. It should only do the most recent addition of money not the entire volume on the runner if that makes any sense.

Not sure how to test manually so at the moment I’ve been using formulas to come up with what a final set of numbers should look like. Please take a look at yet another example spreadsheet (sorry it’s how I work things out) and note how each second decrements independently on top of additional money being added to each Price each second before being summed to create what column R should look like after the first 30 seconds. This time the list is reversed so prices at the top (red number 1 in A2) is the most recent.

Ps, this example naively plays with the idea that there is only one possible price each second, remember it could be multiple but I believe the system already accounts for this. Just making it apparent that this is a very basic screenshot.

CPerry.

Decrement_Exp.xlsx

[CPerry]

Decrement_Exp.xlsx

Another stupid mistake! Please look at this spreadsheet example instead! In the previous one I first multiplied everything in column C by 1/465 when it should have been 30/465. Fixed now. Minor error, need to stop making so many!

[offthelip]

As I stated right at the beginning of this thread I am not clear what you are trying to do.
What I thought you were trying to do was something like this:
Data comes in from betangel giving details of the latest bets which are placed. This data includes the amount and the odds they are placed
You want to create a dynamic view which updates every second which shows where the money has been placed in the last 30 seconds
You want to use a linear decay for each amount which is placed so that each amount decays to zero over 30 seconds
you want to display the total decayed amount placed at each price.

Is this correct?

your latest worksheet doesn't help me to understand at all.

[CPerry]

Screen Shot 2018-09-24 at 14.30.42.jpg

Not that it was vital, but here is the picture I was referring to in the last message. All it's showing is the chunks gradually decrease in size equally and altogether equal 1. That's how I got the denominator as 465 as 30+29+28+27 etc = 465. That's why the big chunk is 30/465 and I was on about using this as a multiplier in the decrement sub.